Multidimensional vs Tabular - Making the correct decision
Which one should you choose?
Analysis services 2012 now comes in two flavours, one which we all know and love, the multidimensional model and an interesting new flavour called tabular. Just because there is a new flavour, it doesn't mean that the old one is dead, or that tabular is a replacement for multidimensional
As some of you may have already experienced, when you are installing Analysis Services 2012, you have to make a choice on what model to choose. There are plenty of blogs out there that explain what the Tabular model is, and some that compare it to the Multidimensional Analysis services that we know from previous versions. So I’m not going to bore you by repeating all of this. I want to summarise which model you should use for your project in an easy to reference Guide.
After attending a User group where Chris Webb kindly did a presentation and demo on the Tabular model, I came away wondering which model would I choose and why? I quickly came to my own summary that the Multidimensional model was for big corporate organisations that wanted the tried and tested solutions, and that Tabular was aimed at smaller organisations with smaller budgets as it looked easier to develop and maintain, but still had a few teething problems as its in its infant stage.
That doesn’t really help you decide what model to choose, and it’s not entirely true, as there are a lot of factors that could affect your decision regardless of the size of your organisation.
Russo, Ferrari and Webb (2012: 11) suggest that when choosing the correct model ‘At a rough guess, either model will work equally well for about 60 % - 70% of projects, but for the remaining 30% - 40%, the correct choice of model will be vital’.
Please note: some of these icons have tooltips that contain additional detail
Table 1. Comparison of Multidimensional and Tabular Models
|Licensing||SQL Server Standard Edition|
|SQL Server Business Intelligence|
|SQL Server Enterprise|
|Client tools||Power View|
|Technical Skill||Developing Model|
|SQL Server 2008 Upgrade Path||OLAP|
|Advanced Functionality||For more detailed information on advanced functionality, please see the following link.|
|Cell level security|
|Role Playing Dimensions|
For more information on comparing reporting tools, please read the 'Microsoft Business Intelligence Reporting Tools - Which should you use?' blog post.
So, which one to choose? I believe that if you are starting on a new BI project and not limited by RAM on your server and you don’t require any of the additional functionality mentioned above, the Tabular model is for you. Any staff with existing Analysis Services skills will be able to pick up the tabular model easily. The skills that you’ll need to develop a Tabular model will be easier to find and cheaper, these may also be in your organisation, i.e. a DBA with an understanding of DW design concepts could build a tabular model without too much up-skilling, whereas for a multidimensional model, you may require a specialist developer.
That said, I don’t want to deter you from the powerful multidimensional model. If you have existing Analysis Services solutions, you require some of the advanced functionality or you have terabytes of data, then the multidimensional model should be the choice for your organisation.
The choice you make should by no means be easy; this blog post should serve as a quick reference guide, if you are serious about choosing the correct Analysis Services 2012 model I suggest you look into the references used below for more detail, or contact us for more information.
Russo, M. Ferrari, A and Webb, C. (2012) SQL Server 2012 Analysis Services – The BISM Tabular Model, California, O’Reilly Media Inc.
Header image was taken from http://www.iconfinder.com/