Tabular Models Vs Multidimensional Data Models


Introducing the Tabular and Multidimensional model


  • The Tabular model, otherwise known as In-Memory Cubes are in-memory databases in SQL Server Analysis Services. Using state-of-the-art compression algorithms and multi-threaded query processing, the Xvelocity™ engine delivers fast access to the tabular model objects and data which boost the performance of data retrieval in reporting tools like Power BI.
  • The Multidimensional model which is a traditional OLAP Cube organizes summary data into multidimensional structures, aggregations are stored in the multidimensional structure in cells at coordinates specified by the dimensions.

The ultimate goal of these two models is to provide a semantic layer on top of the data warehouse provided with high performance capabilities. Both data models have similar features and will give the users an impression that we can easily switch from one model to another, but both are two different entities, having different design experiences and underlying data-architectures.

Data Source Layer

We have zero differences between Tabular and Multidimensional data models. 

Data Access Layer

Tabular models support data access via Cached mode and DirectQuery mode.
  • In Cached mode, all the data is loaded in memory and all queries are accessed from there.
  • In DirectQuery mode, the in-memory model is bypassed, which allows the application to query directly on the data source (only supported for SQL-Server relational engine).
The Multidimensional model supports MOLAP & ROLAP:
  • MOLAP. This is the default and most frequently used storage mode. In this mode, when you process the cube, the source data is pulled from the relational store, the required aggregation is then performed within Analysis Services and finally, the data is stored in the Analysis Services server in a compressed and optimized multidimensional format.
  • ROLAP does not pull data from the underlying relational database source to the OLAP server but rather both the cube detail data and the aggregated data retained in the relational database source. For calculations, additional database objects (indexed views) are created.

Performance

ROLAP and Direct Query mode aren’t considered as most of the communications happens directly with the relational database itself but not with the models.

Scenarios
Multidimensional MOLAP
Tabular In-Memory
Performance
Report on Low granularity data
Read atomic data from disk.
Read columnar data from RAM.
Tabular In –Memory.
Report on aggregated data with no predefined aggregation
Read atomic data from disk. Aggregate data in Memory
Read columnar data from RAM. Aggregate data in Memory
Tabular In –Memory.
Report on aggregated data with predefined aggregations on Cold Cache
Read aggregated data from disk.
Read columnar data from RAM. Aggregate data in Memory
Comparable.
Report on aggregated data with predefined aggregations on Warm Cache
Read aggregated data from RAM.
Read columnar data from RAM. Aggregate data in Memory
Multidimensional.
Performance comparison between Tabular and Multidimensional Model

Data Language Layer

The Multidimensional model uses MDX (Multidimensional Expression) as its data language. MDX is a very powerful language but requires a thorough understanding of multidimensional concepts and, therefore, is often perceived as rather complex and difficult to learn.

The Tabular model uses DAX (Data Analysis Expression). The syntax of DAX is very similar to one of Excel formulas and DAX concepts are similar to relational database concepts and therefore easier to grasp for a developer without any experience of Multidimensional concepts. Even though using DAX is rather simple for basic computations (margin, ratio, etc.), it becomes more complex for advanced computations.

The Data Model Layer

Both Tabular and Multidimensional models work with the same concept where we create links between the underlying data set to define relationships. One important difference is that only one column can be used to establish relationships between tables in a Tabular project whereas in Multi-Dimensional projects multiple columns can be used.

Some limitations of Tabular compared to Multidimensional:
  •  Role-playing dimensions, Actions (Drill trough, Reporting, etc.) cannot be defined (this was especially useful for the Date dimension)
  • Many-to-Many Relationships cannot be defined (there are some workarounds to get the same results, but we will have to use very complex DAX formulas)
  • Parent-Child hierarchy cannot be defined
  • Custom Rollups are not supported (especially useful when aggregating data following a chart of accounts)
Overall, fewer properties are available in the Tabular model than in the Multidimensional model. Again this leads to the same conclusion, the Tabular model is easier to define but it contains some limitations compared to the Multidimensional model.  

Reporting Layer

Every reporting solution (Microsoft Power BI, SQL Server Reporting Services, etc.) that creates reports on top of the SSAS (SQL Server Analysis Services) Multidimensional model will be able to do the same using the Tabular model. They both use the same connection interface and they are both capable of interpreting MDX. And hence every reporting tool that can generate MDX will be able to use the Tabular model. The Tabular model engine is capable of translating MDX into DAX.

The Multidimensional model on the contrary is not capable of interpreting DAX languages. As a direct consequence, a reporting tool that would generate DAX queries will not be able to use a multi-dimensional project as a source. There is currently only one tool that generates DAX queries, which is Power View.

There are no noticeable differences between the Tabular model and the Multidimensional model regarding the administration & management aspects. Both models contain partitioning, perspectives, dynamic security (one will be written in DAX and the other will be written in MDX), Management Views, Backups, and AMO/XMLA API.

Conclusion


Tabular Model
Multidimensional Model
Pros
  • More performant than OLAP in the majority of the case.
  • Easier to develop than Multidimensional model.
  • Technology for the future.
  • Integration with BI applications
  • Mature Technology.
  • Scalable Technology able to handle a very large volume of data.
  • Able to cope with advanced modeling/ computations requirements.
Cons
  • New product so still enhancements to come.
  • Limited to RAM available (midsized project).
  • Missing some advanced computations available with MDX.
  • It cannot be used with Power View.
  • No major innovations to expect in this product in the future.
  • Higher complexity than Tabular.



Comments

Popular posts from this blog

Leveraging Power BI for better insights than Excel

Role of Analytics in Telecom Industry

The Role of Business Intelligence in BFSI