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
|Data Compression||Up to 1/10th of the original data||Up to 1/3th of the original data|
|Data Access Modes||Cached 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: The 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).
|Resources||More 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 Layer||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 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.
|composite key relationships||Not supported directly, we need to define calculated column with composite columns data concatenated||Supported|
|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.
|Scenario||Multidimensional MOLAP||Tabular In-Memory||More Performant|
|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|
We have seen a quick comparison between SSAS Tabular vs Multidimensional models.