tabular model

Introducing the tabular model
This post introduces SQL Server Analysis Services (SSAS) 2016, provides a brief overview of what the tabular model is, and explores its relationship to the multidimensional model, to SSAS 2016 as a whole, and to the wider Microsoft business intelligence (BI) stack. This chapter will help you make what is probably the most important decision in your project’s life cycle: whether you should use a tabular model or a multidimensional model. Finally, it includes a short description of the main differences in tabular models between SSAS 2016 and previous versions.

EMANTIC MODELS IN ANALYSIS SERVICES
In the Microsoft ecosystem, BI is not a single product, but a set of features distributed across several products. The following sections explain the role of SSAS in this ecosystem. The tabular model is one of the two types of semantic models you can create in SSAS. (The other is the multidimensional model.)

What is Analysis Services and why should I use it?
SSAS is an online analytical data engine–a type of service that is highly optimized for the kinds of queries and calculations that are common in a business intelligence environment. It does many of the same things that a relational database can do, but it differs in many respects. In most cases, it will be easier to develop your BI solution by using SSAS in combination with a relational database, such as Microsoft SQL Server, than by using SQL Server alone. SSAS does not replace the need for a relational database or a properly designed data warehouse, however.

One way of thinking about SSAS is as an extra layer of metadata, or a semantic model, that sits on top of a data warehouse in a relational database. This extra layer contains information about how fact tables and dimension tables should be joined, how measures should aggregate up, how users should be able to explore the data through hierarchies, the definitions of common calculations, and so on. This layer also includes one or more models containing the business logic of your data warehouse. End usersquery these models rather than the underlying relational database. With all this information stored in a central place and shared by all users, the queries that users need to write become much simpler. In most cases, all a query needs to do is describe which columns and rows are required, and the model applies the appropriate business logic to ensure that the numbers that are returned make sense. Most important, it becomes impossible to write a query that returns “incorrect” results due to a mistake by end users. This, in turn, means that end-user reporting and analysis tools must do much less work and can provide a clearer visual interface for end users to build queries. It also means that different tools can connect to the same model and return consistent results.

Another way of thinking about SSAS is as a kind of cache that you can use to speed up reporting. In most scenarios in which SSAS is used, it is loaded with a copy of the data in the data warehouse. Subsequently, all reporting and analytic queries are run against SSAS instead of the relational database. Even though modern relational databases are highly optimized and contain many features specifically aimed at BI reporting, SSAS specifically designed for this type of workload and can, in most cases, achieve much better query performance. For end users, optimized query performance is extremely important because it allows them to browse through data without waiting a long time for reports to run and without any breaks in their chain of thought.

For the IT department, the biggest benefit of all this is that it becomes possible to transfer the burden of authoring reports to end users. A common problem with BI projects that do not use Online Analytical Processing (OLAP) is that the IT department must build not only a data warehouse but also a set of reports to go with it. This increases the amount of time and effort involved, and can be a cause of frustration for the business when it finds that IT is unable to understand its reporting requirements or to respond to them as quickly as is desirable. When an OLAP database such as SSAS is used, the IT department can expose the models it contains to end users and enable them to build reports themselves, using whatever tool with which they feel comfortable. By far the most popular client tool is Microsoft Excel. Ever since Office 2000, Excel PivotTables have been able to connect directly to SSAS multidimensional models (also known as cubes), and Excel 2016 has some extremely powerful capabilities as a client for SSAS.

All in all, Analysis Services not only reduces the IT department’s workload but also increases end-user satisfaction. Users now find they can build the reports they want and explore the data at their own pace, without having to go through an intermediary.

A short history of Analysis Services
SQL Server Analysis Services–or OLAP Services, as it was originally called when it was released in 1998 with SQL Server 7.0–was the first foray by Microsoft into the BI market. When it was released, many people saw it as an indicator that BI software was ready to break out of its niche and reach a mass market. Over the past 16 years, the success of Analysis Services and the rest of the Microsoft BI stack has proved them correct.

SQL Server Analysis Services 2000 was the first version of Analysis Services to gain significant traction in the marketplace. Analysis Services 2005 became the biggest-selling OLAP tool not long after its release. As Analysis Services 2008 and 2008 R2 improved scalability and performance still further, more and more companies started to adopt it as a cornerstone of their BI strategy. By 2010, terabyte-sized cubes were not uncommon. The famous example of the 24-terabyte (TB) cube Yahoo! built shows just what can be achieved.

Microsoft Analysis Services 2012 leveraged an existing infrastructure to introduce a new engine and a new type of data model, essentially becoming two products in one. It still contains Analysis Services from the SQL Server 2008 R2 release and before, but that has become known as the multidimensional model. Although Analysis Services has seen a few improvements since the 2008 R2 release, related to performance, scalability, and manageability, no new major functionality has been introduced since then. Meanwhile, Analysis Services 2012 has a new data-modeling experience and a new engine that closely resembles the Power Pivot and Power BI data modeling experience. This is called the tabular model.

The following version of SQL Server did not introduce new BI features, so there are no differences between Analysis Services 2012 and 2014, provided you run the latest service packs and cumulative updates. However, Analysis Services 2016 introduces many new features and improvements to the tabular model, to the point that we considered it necessary to write a new book about it.

The tabular model in Analysis Services 2016 is the subject of this book. We will cover migration issues from tabular models created in former versions of Analysis Services, but if you are not planning to upgrade to 2016, then we recommend you read our previous book, Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model.

UNDERSTANDING TABULAR AND MULTIDIMENSIONAL
This section explains a little about the architecture of Analysis Services, which since SQL Server 2012 has been split into two modes: Tabular and Multidimensional.

When installing Analysis Services, you must choose between installing an instance that runs in Tabular mode and one that runs in Multidimensional mode. (For more details on the installation process, see Chapter 2, “Getting started with the tabular model.”) A Tabular mode instance can support only databases containing tabular models, and a Multidimensional mode instance can support only databases containing multidimensional models. Although these two parts of Analysis Services share much of the same code underneath, in most respects they can be treated as separate products. The concepts involved in designing the two types of models are very different. You cannot convert a tabular database into a multidimensional database, or vice versa, without rebuilding everything from scratch. That said, it is important to emphasize the fact that, from an end user’s point of view, the two models do almost exactly the same things and appear almost identical when used through client tools such as Excel and Power BI.

More information here