Comparing SSAS Tabular and Multidimensional Models

Before you start any Business Intelligence (BI) Project with SQL Services Analysis Services (SSAS), you need to address the question of whether to use a tabular or a multidimensional model for your BI semantic layer. Although the tabular model is the most recent of the two technologies, Microsoft said that the tabular is not a replacement for the multidimensional model.

Offering two different technologies to create the same BI layer creates a lot of confusion for all different developers alike. Choosing whether to use a tabular or a multidimensional model is a multi-pronged decision, especially when you need to make this decision when the project requirements are still in its infancy. This guide will help you to unravel the confusion.

Having said that, the technology behind the multidimensional model is more mature and has a lot more features to offer than that of the tabular. So, if you need any features that the multidimensional offers that the tabular, doesn’t like Writeback (for more features, refer to the Microsoft documentation), you know which one to choose.

The Multidimensional models were the de facto model that came with SSAS, when it was first introduced. This model meets the traditional needs of corporate BI, where the IT teams created the semantic model and the reports for the business users.

With the rise of in-memory technologies and columnar databases, Microsoft announced Tabular modeling with SSAS 2012 that included the xVelocity (VertiPaq) engine, which was originally used with PowerPivot to satisfy the needs of the proponents of the self-service BI users. So, the engine behind tabular is used in PowerPivot and Power BI as well.

The business users usually create reports from data sources like Excel and flat files. So it is natural that the tabular model (which targets the self-service BI users) should import data from a variety of data sources, ranging from relational tables to flat files; whereas the multidimensional models (which serves the needs of corporate BI), you can import only from relational databases.

Since the VertiPaq engine is used in both tabular and PowerPivot, any self-service report that business users create with PowerPivot can easily be migrated to tabular. So, if the report is popular, the model can be imported into tabular to serve the needs of the entire organization. Whereas reports created in tabular, or PowerPivot, cannot be migrated to multidimensional models. The engine used behind tabular allows it to straddle the self-service BI users and corporate users.

Since the tabular model uses in-memory technology, the hardware specifications are different from that of multidimensional models. For multidimensional models, you need plenty of high-speed disks.

For tabular, the hard disk is not important. You need plenty of RAM, as the entire database sits in the memory. In case, your database size runs into terabytes that cannot be fit into the server’s memory, the only option is to go for multidimensional modeling. In addition to RAM, the CPU speed is also a very important consideration for tabular.

In tabular models, you create business logic in DAX (Data Analysis Expressions) whereas in multidimensional models you create the calculations in MDX (Multidimensional Expressions).

However, due to the need to support backward compatibility for reporting clients like Excel and Power BI, both the models support DAX and MDX queries.

DAX is very easy to learn and its very much Excel-like functions designed for business users who work with Excel.

With Multidimensional models, you can use either MOLAP storage (where the data and aggregations are stored in Analysis Services instance). The advantage of this storage method is that since all the aggregations are already created, the queries run faster. However, the data is stale and users can only view the data as of the last refresh date.

To overcome this, users can use ROLAP mode (where MDX queries are translated by Analysis Services into SQL queries) to fetch data from the underlying SQL database. ROLAP ensures that users get real-time or near real-time data. ROLAP requires that the relational database is optimized for performance to fetch results faster.

The tabular equivalent of MOLAP is in-memory storage, where all the data is stored in RAM, which provides faster querying. And the tabular equivalent of ROLAP is DirectQuery. So, you get similar features in tabular to achieve the same functionality as that of the multidimensional model.

For BI analysis, most of the queries involve some kind of aggregation. Since the tabular models use columnstore technology to store data, it can achieve better compression rates than the multidimensional model which uses a row storage format.

Some operations like distinct count (which is, honestly, a headache with multidimensional model), perform really well with tabular models.

And, if the query needs to return detailed rows (down to the lowest level of aggregation), the performance of tabular is much better than its counterpart.

But, obviously, to get the best performance from the tabular model, you need to query the model with DAX and not MDX. DAX is the default query language of tabular and Microsoft suggests using DAX when querying with tabular models.

While processing as well (which loads data from the data source to the database), tabular has several advantages compared to that of multidimensional.

One of the advantages is that processing a table in the tabular model has no impact on other tables; whereas, in the multidimensional model, processing a dimension has an impact on other dimensions which slows the processing operation.

Once you develop the model in the tabular/multidimensional model, there is no way to migrate the models from tabular to multidimensional or vice-versa. So, it makes sense to understand the differences between the two models, your project requirements and the dataset to make a decision that you will not regret later.

The general recommendation is that if you have already created a multidimensional model, stay with it. For new projects start with tabular, as the learning curve is less steep and it helps you to build a semantic model with the concepts that developers already know (like relation, tables, columns, etc.,).