11 Oct 2012

Multidimensional vs Tabular - Making the correct decision

Max Kenney


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

LicensingSQL Server Standard EditionNot AvailableLimited Functionality i.e. no partitioning
SQL Server Business IntelligenceAvailableAvailable
SQL Server EnterpriseAvailableAvailable
Client toolsPower ViewAvailableNot Available
Performance PointAvailableAvailable
Reporting servicesAvailableAvailable


Technical SkillDeveloping ModelThe Tabular approach is easier to learn than MultidimensionalOLAP resource is rarer and more costly
Simple QueryingDAX – Easier to learn than MDX. MDX can also be used to queryMDX - More complicated to learn
Complex QueryingDAX – can become complicated to replicate complex MDXMDX - Advanced functionality for complex calculations
ScalabilityData volumes limited by available RAMMOLAP uses RAM and disk space, which is easier to expand
SQL Server 2008 Upgrade PathOLAPThere are no upgrade paths for existing Analysis Services 2008 solutions to TabularAvailable
PowerPivotAvailableNot Available


Advanced FunctionalityFor more detailed information on advanced functionality, please see the following link.
WritebackNot AvailableAvailable
TranslationsNot AvailableAvailable
 Cell level securityNot AvailableAvailable
 Ragged HierarchiesNot AvailableAvailable
 Role Playing DimensionsPartially Supported / Workarounds available / More difficultAvailable
 Parent/Child HierarchiesPartially Supported / Workarounds available / More difficultAvailable
 Many-to-ManyPartially Supported / Workarounds available / More difficultAvailable
 DrillthroughPartially Supported / Workarounds available / More difficultAvailable


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/

Max Kenney

About the author

MCSA – SQL Server 2012. Max is a Lead Business Intelligence developer at Concentra. He works with a range of technologies and specifically with Microsoft BI Stack for the last 6 years. As a keen motorsport fan, Max likes his data like his cars, "fast, clean and good-looking".