Whenever we start a business intelligence project, the question that always comes up is whether we should implement the Tabular Model or the Multidimensional Model. Undoubtedly, the Tabular Model is newer and better, but it is not a replacement for the Multidimensional Model. Therefore, it always gets confusing when we have to choose between them.
In this blog, I will explain the difference between a Tabular cube and a Multidimensional cube. This will help you make the right choice and will unravel the confusion.
When SSAS was first introduced, the Multidimensional Model was introduced along with it. It is also known as an OLAP cube. It organizes data into multidimensional structures, and in that structure, aggregations are stored in cells. The Multi-dimensional Model uses row storage. The technology used by the Multidimensional model is more mature, meets the traditional needs of corporate BI, and is embraced by many vendors of BI software, but its implementation can be quite challenging.
The Tabular Model was introduced in 2012 and is also known as the In-memory cube. This model included the x-velocity (Vertipaq) engine. The Tabular Model uses columnar storage for better data compression and is much faster for queries based in columns. These models are also easier to develop and easier to manage. While implementing this model, a lot of memory and very fast CPUs are required because all the data is stored in memory.
Supported Data Sources
The Tabular Model can import data from various data sources; it can import data from flat files, relational tables, and some data feeds. With a Tabular Model, the OLE DB can also be used for ODBC providers.. All this is because of the GET DATA query and other import features introduced in it.
The Multidimensional Model can only import data from relational databases.
The Tabular Model is an in-memory solution, the hardware specifications used for the Tabular model cannot be used for the Multi-dimensional Model. It requires a lot of RAM to perform better and will fail if you do not have enough memory.
CPU core speed is also an important consideration for Tabular models. Multi-dimensional models are used when you have a large amount of data. If the size of your database is in terabytes, then the Multidimensional Model is the only option you have.
The Multi-dimensional Model requires a huge quantity of high-speed disks, whereas disks are not important in the Tabular Model.
The Tabular Model uses DAX (Data Analysis Expressions) as its data language whereas the Multi-dimensional Model use MDX (Multi-dimensional Expression).
MDX is a complex and difficult to learn language. A thorough understanding of the Multidimensional concepts is required for it. Some of its specifications like SCOPE Assignment or definition of Named set cannot be done in DAX.
On the other hand, DAX is an easy- to- learn language and is very much similar to Excel formulas. It doesn’t require any expertise and is very simple for basic calculations. However, it requires you to learn DAX and invest your time in it, as it might get complex for advanced calculations.
The Tabular Model reads data directly from the cache, whereas the Multi-dimensional Model reads the pre-aggregated data from the disk.
The Multidimensional Model is much faster for existing aggregations than the Tabular Model because it stores the query results in the cache. In contrast, DAX queries are not saved in the cache, they will always take the same time to execute. Whenever you need to report aggregated data with predefined aggregations on Warm Cache, the Multidimensional Model is more performant.
Tabular models outperform Multi-dimensional Models whenever you need a report on low granularity data. This is because they read columnar data from RAM.
There are some functions like ‘distinct count’ which get too complex with multidimensional models which in return effects its performance. But such functions can perform really well with tabular models.
During processing, the Tabular Model doesn’t impact other tables, while the Multidimensional Model impacts other dimensions. This slows down its processing speed. The Tabular Model has many advantages as compared to the Multi-dimensional Model.
Pros and Cons
- The technology used by Multi-dimensional Model is quite mature.
- This model is much more scalable as it can deal with a large amount of data.
- This model supports complex query constructions.
- The Multidimensional Model does not support the power view.
- In the future, no significant development is expected for the Multi-dimensional Model.
- It is much more complex when compared to the Tabular Model.
- In most of the cases, the Tabular Model is more performant than the Multi-dimensional Model.
- Its development is much easier.
- It allows Integration with Power pivot.
- Performance is much faster as compared to the Multidimensional Model.
- Further Improvements are expected as it is a new product.
- It totally depends on memory and will fail if not enough memory is provided.
- The Tabular Model does not support Write-back, Linked objects, Actions, or Translations.
There are some differences between models, such as their features, modeling experience, etc., although both models will suffice for most BI projects. The Multi-dimensional Model is the right choice for you if you are looking for some advanced and complex modelling. But if you want fast and better performance and the dataset is also not too large, the Tabular Model will suit you. There is no need to migrate from a Multi-dimensional Model to Tabular Model if your end-users are happy with it. The only reason for migration should be the use of the power view.
But when starting any new project, Tabular Model will be a better choice because of its better performance and easier development until and unless you are working with a large volume of data or advanced business requirements.