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?
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.
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.
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.
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.
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.
Consider this before implementing a Tabular model.
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.
The new Direct Query advancements in Tabular model gives it a big boost.
Thanks gigi, the link has been fixed.
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.
."In many to many relationships, for Tabular DAX is faster."
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.