SSAS Multidimensional and Tabular Models

SQL Server Analysis Server provides several approaches for creating a Business Intelligence semantic Model  : Tabular and Multidimensional. The amount and type of data you need to import can be primary consideration when deciding which model type best fits your data. Tabular and Multidimensional models use imported data from external sources. SSAS Tabular Model uses advanced technology, introduced by Microsoft with SQL Server 2012  

Multidimensional Model :

• Multidimensional model uses OLAP modelling standards such as cubes, dimensions and facts 
• It uses MOLAP, ROLAP or HOLAP storage for modelling 
• Multidimensional model databases can use dimensions and cell - level security, using role-based permissions 
• In multidimensional models, All logic and calculations use MDX (Multi-Dimensional Expressions)
• Multidimensional Models are useful in case of large amount of datasets (in terabytes), Many-to-Many relationships, require complex modeling and write - back support. 
• Multidimensional Models require plenty of high speed disks because it stores large of amount of data 

Tabular Model :

• Tabular Model are in-memory databases, uses relational modelling standards such as tables and relationships 
• Tabular Model uses xVelocity/Vertipaq mechanism, which is much faster than Multidimensional model
• Tabular Model databases can use row-level security, using role based permissions
• In Tabular Models, business logics are created in DAX (Data Analysis Expressions) , It is very easy as compare to MDX and very much Excel-like functions designed for business requirements
• Only tabular models are supported in Azure Analysis Service
• Tabular Model is not for a system that are going to load terabytes of data 
• Since tabular model uses in-memory technology , need plenty of RAM as the entire database sits in the memory. It has more efficient data compression as compare to Multidimensional models 

The following table summarizes features availability at the model level :

     

	Multidimensional	Tabular
Modeling	Multi-Dimensional   (Dimensional Modeling)	Relational Modeling
Storage	MOLAP, ROLAP, HOLAP	In-memory and Direct Query
Query	MDX (Multidimensional Expressions)	DAX    (Data Analysis Expressions)
Structure	Rigid Structure	Loose Structure
Complexity	Complex	Simple
Size	Larger	Small
Aggregations	Yes	No
Calculated Columns	No	Yes
Hierarchies	Yes	Yes
KPIs	Yes	Yes
Partitions	Yes	Yes
Perspectives	Yes	Yes