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.
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:
- Aggregations
- Actions
- Custom Assemblies
- Custom Rollups
- Many to many relationships (you can create some queries with DAX)
- Writeback
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:
