SSAS Tabular vs Multidimensional model

Tabular vs multidimensional nodel

Analysis Services is an analytical data engine (VertiPaq) used in decision support and business analytics. It provides enterprise-grade semantic data models for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools. Two types of Analysis Services models are available; the Multidimensional model and the Tabular model. While both these models have significance, here is a quick comparison between SSAS Tabular vs. Multidimensional models.

SSAS Tabular vs Multidimensional

#Tabular ModelMultidimensional
AggregationsNoYes
Data CompressionUp to 1/10th of the original dataUp to 1/3th of the original data
Data Access ModesCached mode: All the data is loaded in memory, and all queries are answered from there.
Direct Query  (Pass-through): Bypass the in-memory model, allowing client applications to query data directly on the database

MOLAP:  T
he source data is pulled from the relational store, the required aggregation is performed within Analysis Services, and finally, the data is stored in the Analysis Services server in a compressed and optimized multidimensional format.
ROLAP: This does not pull data from the underlying relational database source to the OLAP server. Rather, the cube detail data and the aggregated data stay in the relational database source. To store the calculated aggregation, the database server creates additional database objects (indexed views).
ResourcesMore memory (RAM) & CPU will be required if the cached model is used.Limited memory and CPU can suffice the requirement. (Will depend on the workload and kind of computations)
Data Language LayerUses 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 with Multidimensional concepts. Even though using DAX is rather simple for basic computations (margin, ratio, etc.), it becomes more complex for advanced computations.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.
Custom AssembliesNoYes
composite key relationshipsNot supported directly, we need to define calculated column with composite columns data concatenatedSupported
Reasons to go for• If you want to use DAX (based on Excel formulas, but is often seen as having a steep learning curve)
• If you want to use Power View, PowerPivot, and SharePoint Excel Services
• If you need to use multiple data sources
• If you need extreme speed in your data queries
• If your development timeline is compressed
• If your dataset is extremely large (in terabytes)
• If you need to write back to support
• If you need complex calculations, scoping, and named sets
• If your solution requires complex modeling
Pros• More performant than OLAP in the majority of the case.
• Easier & faster to develop than a Multidimensional model.
• Technology for the future.
• Integration with Power Pivot.
• Matured Technology.
• Scalable Technology; able to handle very large volumes of data.
• Able to cope with advanced modeling/ computations requirements.
Cons• Limited to RAM available (= midsized project).
• Missing some advanced computations available with MDX.
• Cannot be used with Power View.
• No major innovations to expect in this product in the future.
• Higher complexity than Tabular

Which SSAS model is suitable for your reporting use case?

Based on the above comparison, below are some reporting scenarios that state which model will outperform another.

ScenarioMultidimensional MOLAPTabular In-MemoryMore Performant
Report on Low granularity dataRead atomic data from disk.Read columnar data from RAM.Tabular In–Memory.
Report on aggregated data with no predefined aggregationRead atomic data from disk. Aggregate data in MemoryRead columnar data from RAM. Aggregate data in MemoryTabular In–Memory.
Report on aggregated data with predefined aggregations on Cold CacheRead aggregated data from disk.Read columnar data from RAM. Aggregate data in MemoryComparable
Report on aggregated data with predefined aggregations on Warm CacheRead aggregated data from RAM.Read columnar data from RAM. Aggregate data in MemoryMultidimensional

We have seen a quick comparison between SSAS Tabular vs Multidimensional models.

See more

Kunal Rathi

Been working in the data engineering and analytics space for over a decade. Helping customers transform their data into insights, Cloud & DevOps enthusiast.