What is Tabular model and how it differs from Multidimensional in SQL Server Analysis Services
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
Microsoft on Analysis Services to build Business Intelligence (Data Warehouse) beyond usage Multidimensional Cubes provides and approach to Tabular model.
The Tabular models are new, faster to build and easier to use. Their use is based on memory RAM of the server. The more memory we have, the higher the performance. If there is not the necessary memory it will not work.
On the other hand Multidimensional (OLAP) models are proven years. They partition the data into a multidimensional format. Their use is based on high needs for large storage space. Aggregations are pre-calculated and stored in cells.
The Comparison of the two models
| Tabular Model | Multidimensional Model |
| It requires a lot of fast memory RAM as its performance is also affected by its speed CPU. This is because data is mostly stored in RAM. If our data reaches Terra-bytes we should use the Multidimensional Model. | It requires big and fast hard drives |
| In-Memory Mode The data should be done process to be informed. That is, we will need to have a planned process that will update the data from their sources. | MOLAP |
| DirectQuery Mode The data is near real-time. It sends a query to the database where the data comes from | ROLAP |
| To retrieve data you use the language DAX (Data Analysis Expressions). It is easy to use and resembles Excel in its way of working. | MDX (Multidimensional Expressions) It has a higher skill cap. |
| The data is still in the normalized tables. | The data should be divided into Fact Tables and Dimensional Tables. |
So which is the best model?
In Tabular vs Multidimensional there is no common answer. Depending on the needs we have, the amount of data and what hardware our server has, we should take them into account before making a choice.
