Analysis Services Provides Three Approaches to Develop Business Intelligence Semantic Models;

Introduction

Tabular Model is a new feature in SQL Server 2012. Tabular models are in-memory databases in Analysis Services. They use state-of-the-art compression algorithm and multi-threaded query processors named as VertpPaq (xVelocity in-memory analytics engine). This enable fast access to tabular objects and data by reporting applications such as MS Excel.

Tabular Models are created in SQL Server Data Tools (SSDT). SSDT is the upgraded version of BI developer. SSDT is also used to develop Reports, Integration Services and Multidimensional projects. Tabular Model project is deployed as Analysis Services database just like Multidimensional projects. In Tabular Model, we can add partitions for optimization purposes. Row-level security can also be implemented in Tabular Model. We can also import data from external sources to make it a part of the model. We can add relationships, measures and calculated columns on the tabular data in the model. Hierarchies are also supported in Tabular model as in multidimensional model. This makes it more do-able and easy to go with project type in SSAS. As Tabular Model is deployed as Analysis Services database, client applications can connect to the deployed model for reporting purposes.

Analysis services provides three approaches to develop business intelligence semantic models;

1. Tabular

2. Multidimensional

3. PowerPivot

Tabular Model is based on relational structure such as tables and relationships for modeling of data. Multidimensional and data mining are based on OLAP (cubes and dimensions). PowerPivot is a self-service BI tool.

Tabular Model support data access through two modes;

Cached ModeIn cached mode, you can integrate data from multiple sources including relational databases, data feeds, and flat text files.

Direct-Query ModeIn this mode, you can by-pass the In-Memory model and allow client applications to query directly from the relational database. In short, Tabular model is designed to query huge data with much lesser response time in different client application, especially in MS Excel.

Comparing With Multidimensional Model

With the release of Tabular Model, there are few decisions by Managers that has to be made. Some common questions are;

Question 1: When to use Tabular Model and Multidimensional Model?

Answer:Both Tabular and Multidimensional Models are the part of Analysis Services. Both are developed in SSDT. The answer lies within the data to be used in the model. If you have huge data, like in TBs, to process in the model, then Multidimensional Model is the best suite for you. Tabular Model are more compressed than Multidimensional but it also requires more memory to load and process the data in memory. PowerPivot Models are for even lesser data size.

Question 2: What do we mean by 'External Data' or 'External Source'?

Answer:We can import data in Tabular Model and PowerPivot fromExternal sources. These can be text files, excel files, data feeds and even some document formats. In multidimensional, you can only load data from relational sources using OLEDB and managed providers.

Question 3: What is the core benefit of Tabular Model?

Answer:The core benefit is that they are easy to implement as PowerPivot. You can develop more efficient and effective solution. The data processing is very fast and it is easy to understand.It uses DAX as programming language. DAX is quite similar to Excel formula language. Comparing with PowerPivot, Tabular Model is a centralized solution. The data is more organized and authenticated as compared in PowerPivot. PowerPivot is an ad-hoc pattern to access data from the source and is a part of Self-Service BI solution. Tabular Model is the Corporate-BI solution.

Question 4: Should we convert the current Multidimensional Models to Tabular?

Answer:NO. The project types are not interchangeable. And it will not be an effective decision to convert the multidimensional model into Tabular model. It is best to use the model for the new requirements.

Analysis Services 2012 Two in One Product

Most important aspect of Analysis Services 2012 is that it is two products in one. In SQL Server 2008 R2 and before, we only had Multidimensional Model option in Analysis services. But in SQL Server 2012, you can choose between Multidimensional and Tabular Model while installing SSAS instance. You need to have separate instance for both if you want to use both, multidimensional and tabular model. In the figure below, you can see that on Feature selection page, there is only one selection for Analysis Services;

But when you pass on to the Analysis configuration page, there you will see a radio button for Server Mode selection. The options are; a. Multidimensional and Data Mining Mode b. Tabular Mode

Licensing

Analysis services 2012 is available in the following editions of SQL Server;

SQL Server Standard Edition

SQL Server BI Edition

SQL Server Enterprise Edition

In SQL Server Standard edition, you can only have Multidimensional Mode. SQL Server BI edition and SQL Server Enterprise editions have both Multidimensional and Tabular Mode. In terms of Analysis Services functionality, these two editions are the same; the only difference between them is that SQL Server Business Intelligence edition licensing is based on buying a server license plus Client Access Licenses (CALs), whereas SQL Server Enterprise edition is licensed on a per-CPU core basis. (You can no longer license SQL Server Enterprise edition on a server-plus-CALs basis as was possible in the past.) In SQL Server Business Intelligence and SQL Server Enterprise editions, both Tabular and Multidimensional models contain all available features and can use as many cores as the operating system makes available. The upshot of this is that it could be more expensive in some situations to use Tabular than Multidimensional because Multidimensional is available in SQL Server Standard edition and Tabular is not. If you have a limited budget, already have existing Multidimensional skills, or are willing to learn them, and your data volumes mean that you do not need to use Multidimensional features such as partitioning, it might make sense to use Multidimensional and SQL Server Standard edition to save money. If you are willing to pay slightly more for SQL Server Business Intelligence edition or SQL Server Enterprise edition, however, then licensing costs should not be a consideration in your choice of model.

A hint to Self-Service BI and Corporate BI

Over the last few years, trends and demand of BI have significantly changed. Now, users are more interested in having the hold on their data and analyze it according to their own run-time requirement on the spot. Prior to that, they had to involve an IT professional to develop complex reports for them. This is Corporate BI. This scenario still exists but for analysis work, it becomes more cost and time consuming for engaging IT professional for the analysis work. This resulted in a new Self-Service BI. There are tools available in the market like QlikView and Tableu that enables users to query their own data in a user friendly interface. Analysis Services is a corporate-BI tool by MS as you need an IT professional to design and build an Analysis Services database. There are disadvantages of self-service BI like poor-quality data, lack of integration between multiple source systems, etc. Still, self-service BI is high on demand and the reason is that most of the data users think that in most cases, the time consuming step of creating a data warehouse is un-necessary in their initial scenario. MS, as a software company could not ignore this market demand, so in 2010, it released its own serf service BI tool, PowerPivot. Tabular Model resembles a lot to PowerPivot and a refined version of PowerPivot as you get the scalability and manageability in the Tabular Model. PowerPivot is more Agile and Self-Service BI tool.