Knowledge Base & Community/ZAP BI Knowledge Base/User Guides

Understanding the difference between SQL Server Analysis Services Multidimensional and Tabular models

Damien Zwillinger
posted this on July 09, 2013 09:09

SQL Server Analysis Services Tabular models are new models introduced in SQL Server 2012. They are completely different models compared to Analysis Services Multidimensional models (also known as OLAP models).

Tabular models are in-memory models and introduce a new query language: DAX, as opposed to MDX for Multidimensional models. Since the latest Service Pack of SQL Server 2012 and the upcoming SQL Server 2014 version, both the Tabular and Multidimensional model can now receive DAX or MDX queries (e.g. you can now query a Multidimensional model in DAX, or a Tabular model in MDX).

While the two models are somewhat similar when browsed in ZAP Business Intelligence, they are very different. The new Tabular models compress and store data in-memory using the new x-Velocity engine; Multidimensional models aggregate and compress data as XML files.

Although Tabular models look good on paper, they are not yet ready for the enterprise space. They are somewhat limited in terms of enterprise features and suffer from scalability issues compared to Multidimensional models that can scale to hundreds of TB cubes.

Even if ZAP CubeXpress can generate Tabular models (in a BETA form) since version 2012 and ZAP Business Intelligence can query Tabular models since version 2013 RTM (using MDX),  ZAP still recommend Dynamics users to generate and report on Multidimensional models.

Please refer to:

  • this Limitations article to understand query/design limitations connecting to a Tabular model using ZAP BI.
  • the CX 2012.2 Release Notes page of ZAP Online help to understand more about the limitations creating a Tabular model from ZAP CubeXpress

The below table goes through each main features of Analysis Services Multidimensional models, compares them against the new Tabular models and also adds information as to why some of these Multidimensional features are important for Dynamics and ZAP users:

  Multidimensional Tabular Why Important
Actions Yes No ZAP use URL Actions extensively from dimension attribute members to Dynamics CRM/AX forms; providing nice interactions between reports and live forms.
Aggregations Yes No Important for optimizing models so calculations are not done at high level granularities. This is more a Multidimensional concept, although it would benefit Tabular so it would not process everything in-memory and queries would be able to use pre-aggregated in-memory caches.
Calculated Measures Yes Yes  
Calculated Members Yes No This is a core concept in ZAP, everything can be an object and can be referenced elsewhere
Cell calculations Yes No This is important for overriding cell values at reporting time (i.e. currency conversion)
Cell-level & Advanced Security Yes No  
Custom Assemblies Yes No ZAP Business Intelligence extends standard MDX using its own custom assembly which provides users with more advanced System Content functions.
Custom Rollups Yes No

ZAP use custom rollups extensively with Unary Operators in Chart of Accounts hierarchies. 

Materialized Chart of Accounts hierarchies are critical for ZAP customers.

Data Mining Yes No

 

Distinct Count Yes Yes (via DAX)  
Drillthrough Yes Yes  
Dynamic Security Yes Yes  
Hierarchies Yes Yes  
KPIs Yes Yes  
Linked objects Yes No  
Many-to-many relationships Yes No ZAP's models currently don't have Many To Many (MTM) relationships built-in, as ZAP's models must be generic and work with both Standard and Enterprise Editions of SQL Server   (MTM is not supported in Standard Editions). MTM relationships are often a requirement for customers. Implementing Currency Conversion using MTM relationships is common.
Named Sets Yes No This is a core concept in ZAP, everything can be an object and can be referenced elsewhere. ZAP customers use ZAP Named Sets for their custom business rules or their slicers.
Parent-child Hierarchies Yes Yes (via DAX) Parent-child hierarchies are common in ZAP cubes and many Dynamics AX tables have been designed in a parent-child way.
Partitions Yes Yes  
Perspectives Yes Yes  
Real Time (ROLAP/DirectQuery) Yes Yes  Note: DirectQuery only supported using DAX, not MDX.
Row/Attribute-level security Yes Yes  
Role Playing Dimensions Yes No It is very common for Dynamics tables to require relationships to the same dimension multiple times and create role playing dimensions so those dimensions are not "aggregated" multiple times (i.e. Transaction Date, Start Date, End Date, Delivery date, due date, etc.. as well as From Inventory Warehouse, To Inventory Warehouse). Tabular models don't have this concept of Role Playing dimension, meaning tables have to be duplicated in the model and processed multiple times in memory, introducing performance, design and maintenance challenges.
Semi-additive Measures Yes Yes (some via DAX) Some semi-additive measures like "Last Non Empty" can only be achieved using complex DAX expressions that will have to be repeated in calculated measures.
Translations Yes No  
Unary Operator Yes No Unary operators are critical for ZAP customers, as this feature is used in ZAP's generated Chart of Accounts hierarchies.
Visual Totals Yes No  
Writeback Yes No This is important for any Financial budgeting/forecasting requirements using Excel for example. Although this can also be achieved with Tabular models using an Excel file as a source, Multidimensional models offer more control over data allocation and security. Dynamics customers can use the AX built-in budgeting solution however ZAP find that customers often want to customize this part and use writeback or SSIS to load Budget data into ZAP DataWarehouse.

Note: this above table has been updated based on the new features of SQL Server 2014 (only available as a CTP version).

 

For a more detail comparison of Multidimensional vs. Tabular models, feature and model-wise, please review this joint Hitachi/Microsoft white paper: Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services

 

For more information about the Features supported in SQL Server 2012 and/or 2014, please visit:

- For SQL Server 2012: http://msdn.microsoft.com/en-us/library/hh212940.aspx

- For SQL Server 2014: http://msdn.microsoft.com/en-us/library/hh212940(v=sql.120).aspx

 

 
Topic is closed for comments