Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Tabular vs Multidimensional models for SQL Server Analysis Services


By:   |   Updated: 2016-01-28   |   Comments (6)   |   Related: > Analysis Services Development

Problem

I would like to know if I should use a Tabular or a Multidimensional solution for my data analysis. Which one is the best solution?

Solution

As with most things, it depends.

In this tip, we will briefly explain the Tabular solution versus the Multidimensional solution and in which situations we need to use each solution. Many people think that it is necessary to migrate from Multidimensional to Tabular because it is newer and better, but in many cases it is not recommended or even worse, it is not possible. We will explain why.

tabular vs multidimensional

Multidimensional

The Multidimensional database is a very different structure than a relational database and allows us to generate reports very fast. The Multidimensional model was the only solution in the past to create multidimensional databases. This model has not change much from SQL Server 2005 to SQL Server 2016. If you review what is new in Analysis Services, you will notice that most of the new features are related to Tabular databases.

Tabular

The Tabular model was introduced in SQL Server 2012 and each new version includes new features. For a complete list of what is new in each version, please go to next steps at the end of this article.

The Tabular model uses a different engine (xVelocity) and it is designed to be faster for queries based in columns, because it uses columnar storage (multidimensional models use row storage) in addition to better data compression. The data is stored in memory, so it is very important to have a lot of memory in your server and very fast CPUs. The disks are not as important in a Tabular model.

Comparing Tabular and Multidimensional Databases

Hardware

It is important to clarify, that the hardware used for Multidimensional databases in many circumstances cannot be used in the Tabular model.  Tabular is a memory dependent solution. The more memory, the better performance. If you do not have enough memory, the Tabular model will simply fail.

The CPU core speed is also very important for Tabular databases.

If your database requires a lot of space (more than 5 terabytes), the Tabular model cannot be implemented and the only solution is the Multidimensional solution.

In Tabular databases, the hard disks are not as important, but the amount of RAM and CPU speed is very important.

Migration

If you already have a Multidimensional model and you are happy with it, it is recommended to stay with it. If you believe that the Tabular model can solve your problems, you can think about migrating. Migrating from Multidimensional to a Tabular solution is not an easy task. Basically, everything is manual and there is no easy way to do this migration. You could create a customized migration solution using SSIS, PowerShell or other tools of your preference.

The main advantage of the Tabular solution is that it is faster for some queries and it compresses the data even more than the Multidimensional solutions (the compression of multidimensional is a third of the size of the original database and the Tabular can be a tenth of the size).

For example, the Tabular is very fast for the distinct count measures. For more information about this, check our this article related to Different options for creating a distinct count measure in SSAS.

 If you heard about Tabular databases, you may have also heard about DAX. DAX is a simple language to query Multidimensional databases, Tabular databases, Power Pivot and Power BI. As you may know, it is easier than the older MDX language.  In many to many relationships, for Tabular DAX is faster.

You should also consider that there are some feature not available in Tabular models that are available in the Multidimensional models. Here are some:

Consider this before implementing a Tabular model.

Conclusions

As you can see in this tip, you should carefully consider the migration from Multidimensional to Tabular. In many cases, it is not necessary or may not even be possible.

If you already have a Multidimensional solution and everything is fine, there may be no need to change. Even though Tabular is easy to learn, it takes time to have solution ready for production. Be wise and careful with your decision.

Next Steps

There are several links that will be useful to you:



Last Updated: 2016-01-28


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 03, 2019 - 12:52:43 PM - A Peterson Back To Top

Multi-dimensional, and MDX are far more logical then Tabular.  I've found that once a Tabular project grows to the point where it has advanced business needs, it just falls flat.  Tabular might be great for all your quick, temporary projects.  But once it has an enterprise level need. Multi-dimensional is the way to go.  Even with smaller data sets.  And with SSD based systems, ROLAP may be an even better approach.  With Multi-dimensional, a fact table automatically has calculated measures.  With tabular, you have to tell it what is a fact.  Again, great for ad-hoc stuff, but not for well architected star schema tables.


Thursday, January 12, 2017 - 11:23:54 AM - Sid Back To Top

 

The new Direct Query advancements in Tabular model gives it a big boost.


Thursday, January 28, 2016 - 4:57:39 PM - Greg Robidoux Back To Top

Thanks gigi, the link has been fixed.


Thursday, January 28, 2016 - 4:33:04 PM - gigi Back To Top

tank you, very interesting.

the link https://www.mssqltips.com/sqlservertip/2821/getting-started-with-tabular-model-in-sql-server-2012--part-1/%7D

for tabular model isn't correct. Please, update with https://www.mssqltips.com/sqlservertip/2821/getting-started-with-tabular-model-in-sql-server-2012--part-1

 

bye

 


Thursday, January 28, 2016 - 8:42:33 AM - Daniel Back To Top

Thanks for you comments.

Yes, the many to many relationships can only be done with DAX. In our experience it is faster than the multidimensional model.

I agree that DAX is not a piece of cake, but I guess there is not easy to simplify  multidimensional queries no matter the query language used.


Thursday, January 28, 2016 - 5:05:01 AM - Koen Verbeeck Back To Top

."In many to many relationships, for Tabular DAX is faster."

But a few lines down to road you say many 2 many relationships are not available in Tabular. (they can be implemented through some very complex DAX though). So how are you so sure it's faster in Tabular? (M2M is available in Tabular 2016)

Also, do you really think DAX is easier than MDX? Sure, the basic stuff is easier, but once you have to use CALCULATE for the more advanced stuff, I don't find DAX that easy. Some simple stuff is ridiculously complex in DAX, like calculating a median. Luckily in SQL 2016 they introduce some new functions to make our lifes easier.


Learn more about SQL Server tools