Tabular Model is a new data model introduced in SQL Server 2012 Analysis services. Tabula Models are in-memory databases which uses analytic engine named (VertiPaq) which delivers fast access to data to client applications like Excel and Power View. This post describes the differences between Tabular Model and Multidimensional model and outlines which model to choose and when over the other. It is required to understand the new BISM(Business Intelligence Semantic Model) in SQL Server 2012. This model was designed for easy access of data to all client tools like Excel, Power View and Power Pivot.
Business Intelligence Semantic Model supports both Multidimensional and Tabular Models. BISM can take the data from various sources including relational databases, OData feeds , text files and excel. The BISM architecture in SQL Server 2012 shows as below
Developers can choose Multidimensional or Tabular models based on their business requirement and skill set but both can be consumed in client tools shown in the above picture. Multidimensional model requires the understanding of creating a model with dimensions and cubes based on Star\Snow-flake schema of a relational data warehouse. As Tabular Model is based on relational data sources and it is easier to understand and develop the model.
Query mechanism – Multi Dimensional Expression (MDX) can be used for calculations and querying OLAP. DAX(Data Analysis Expression) is an expression language can be used to support tabular models and to create calculated columns , measures and KPIs.
Data Access mechanism – Tabular Models supports data access through two modes Cached mode and Direct query mode. In Cached mode you can get the data from all data sources and it then uses compression algorithms for high-speed data access. It uses VertiPaq is an in-memory column store engine. In direct query mode query processing interacts with the underlying data sources.
Installation – You can install SQL Server 2012 Analysis Services in either Multi-dimensional or Tabular mode.
Development – SQL Server data tools IDE for creating new tabular model projects and Multidimensional models and for managing you can use SSMS(SQL Server Management studio).
Security- Role based security in multidimensional model and security can be provided at cell level where as in Tabular model it row-level security.
Choose Tabular model when
when your data source is relational database
- scalability – Tabular models used to manage processing of large data volumes.
- DAX expression language is easy compared to MDX
- use this model to create Power View reports
- to develop the models fast
- Choose Multi Dimensional models when
- when your data source is based on dimensional-modelling and has complex relationships
- when you want to write power full expressions to create complex calculations and named sets
- when the dataset is extremely large
- you want native support for many-many relation ship
Tabular models are simple to use and keeps the entire data in memory and provides a great performance. It does not support features such as complex calculations , named sets and many-many relation ships.
