Tabular vs Multidimensional models for SQL Server Analysis Services

By:   |   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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, July 3, 2019 - 12:52:43 PM - A Peterson Back To Top (81666)

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 (45237)

 

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 (40535)

Thanks gigi, the link has been fixed.

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

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 (40529)

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 (40524)

."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.














get free sql tips
agree to terms