1 October 2014

When to choose SSAS over Tabular?

One of SQL Server 2012 new features was the introduction of a new analytical model: the Tabular. As any new technology, Tabular brought curiosity and desire to migrate a couple of multidimensional solutions but at the same time still causes some mistrust by being a very recent piece. In order to deal with these mixed-feelings, it’s essential to know the advantages and disadvantages of this new model and compare it with the old, but still very valid, Multidimensional model.
The Multidimensional was introduced in SQL Server 7.0 and it consists in cubes that contain metrics that can be analyzed through multiple dimensions. The engine is responsible for storing pre-aggregated data to disk (MOLAP) or in a relational database (ROLAP), thus allowing a rapid response to queries with aggregations (the most common ones).
Regarding to the tabular model, it’s based on xVelocity engine and consists of an in memory-database with column-oriented storage which enables a higher compression and faster data access.
Like the previous model, it supports two operation modes: Cached mode, that loads the entire model in memory, performing very fast responses without accessing the disk; and DirectQuery mode, in which the queries are made directly to database.
In order to select the analytic model that best fits the needs of each project/client, in the next table are highlighted the main differences between both models that probably will make you decide in first place about choosing one or another:

 

Multidimensional Tabular
Query Performance If queries are related with pre-aggregated data in the disk, the response performance is equivalent to the Tabular or even better in the case of being cached. Although aggregations are calculated at query time, the data is read from the memory which makes responses perform better either for aggregated data or more fine-grained data (with the exception mentioned on the left).
Processing Performance There are processing dependencies (first we have to process dimensions and then the measure groups); aggregations must be recalculated when dimensions processing occurs. There are no processing dependencies, since the model is made of “only tables”. Unlike multidimensional model, the processing of partitions can’t be done in parallel.
Performance Tuning Performance can be improved by defining aggregations and implementing mechanisms to warmup the cache. The best way to increase performance is to maximize the available server RAM.
Data Model Out-of-the-box features for implementing complex models: many-to-many and parent-child relationships, advanced business logic (through MDX). Supports models with low/medium complexity, without the need of create relationships between dimensions and facts. Some advanced modeling needs (such as many-to-many relationships) are possible via DAX but it’s harder to implement.
Development Experience The development takes longer and it requires more advanced skills (eg MDX). The development is faster and therefore is often recommended for proof of concepts. The learning curve is also easier because of the similarity between DAX and Excel formulas language, which becomes more accessible to business people. Moreover, you can directly import models built in PowerPivot.
Scalability Appropriate for models with a large volume of data. Conditioned by server’s memory, this approach is more suitableFor small/medium size models.
Exclusive Features Exclusive out-of-the-box features: Named-Sets, Actions, Translations, Write-back, Data Mining, Role-Playing Dimensions, Custom Assemblies, Custom Rollups, Display Folders, custom formatation of measures, default values for dimensions attributes. Although some features (as Actions, Translations and Display Folders) are not out-of-the-box, they are possible by editing the generated XMLA or by using BIDS Helper tool available at CodePlex.
Security Advanced security features that can go up to cell-level.  It’s possible to set drill through permission at role level. Row-level security.Drill through permission is set to all roles.
SQL Server Versions Available since SQL Server 7.0 (1999)In SQL Server 2012 is available in Enterprise, Business Intelligence and Standard editions. SQL Server 2012 Enterprise and Business Intelligence editions.
Client Applications Support Microsoft Excel, SQL Server Reporting Services, PerformancePoint, PowerView (since Multidimensional now supports DAX queries), etc. Tabular also supports both MDX and DAX queries. Therefore all client applications that support multidimensional model also supports tabular one.

 

If you still have doubts about tabular model, there is nothing like try it when you are starting a new project. If the model that you’re going to implement has simple business logic and security requirements and has a relatively low data volume, the tabular model can be the ideal solution for you as it wins in performance, especially in detailed queries where multidimensional model lose many points.
However, the migration of an existing multidimensional model to a tabular one should be carefully considered: first, migration is not straightforward, you have to build the model from scratch; and second, because there are some advanced features that are not supported by Tabular models (at least for now).
In some cases, as happened to me in a recent project, it’s advantageous to migrate an existing project to a tabular model because of the way it works and also the ease of modeling. In this particular case, I needed to add to an existing multidimensional model a large number of Distinct Count measures, which would force me to add a new measure group for each new measure. Thus, we decided to migrate part of the model to tabular which makes the model to be simplified (since it is not necessary to create and maintain the multiple measure groups that would be created in multidimensional), and also had gains in performance because of the way it reads the data from memory as well as it's faster with Distinct Counts calculations due to the column-oriented storage.
In conclusion and especially for large models, we still need the Multidimensional model where the Tabular one (Cached mode) is strongly limited by server memory. However, in small/medium solutions in which query performance is a critical requirement, the Tabular model should be our/your first choice.

.

.

.

.

     Helena Cabral
 Associate Manager
Solutions Development