SSAS - Multidimensional Model vs Tabular Model

In this blob we are going to compare SQL In Server analysis services 2012, Microsoft has introduced a new model called as Tabular model. At the time of installation of this service, one has to choose either of the services. Below are some of the considerations we can keep in mind before deciding between Multidimensional vs Tabular model.

	Tabular Model	Multidimensional
Aggregations	No	Yes
Many-to-many relationships	No (but there is bi-directional cross filters at 1200 compatibility level)	Yes
Data Compression	Upto 1/10th of  original data	Upto 1/3th of  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 then performed within Analysis Services and finally the data is stored in the Analysis Services server in a compressed and optimized multidimensional format. ROLAP:  Does not pull data from the underlying relational database source to the OLAP server but rather both the cube detail data and the aggregated data stay in the relational database source. In order to store the calculated aggregation the database server creates additional database objects (indexed views).
Resources	More memory (RAM) & CPU will be required if 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 the one of Excel formulas and DAX concepts are similar to relational database concepts and therefore easier to grasp for a developer without any experience of 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 Assemblies	No	Yes
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 the terabytes) • If you need write back support • If you need complex calculations, scoping, and named sets • If you need many-to-many relationships (can be done in Tabular, but not with ease) • If your solution requires complex modeling
Pros	• More performant than OLAP in majority of the case. • Easier & faster to develop than Multidimensional model. • Technology for the future. • Integration with Power Pivot.	• Mature Technology. • Scalable Technology able to handle very large volume of data. • Able to cope with advanced modeling/ computations requirements.
Cons	• New product so still enhancements to come. • 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

Based on above comparison, below are some of the reporting scenarios which state which model will outperform over other.

	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.

Thank you for reading this post. Will be happy to hear from you if you have any suggestion or opinion. Happy reading :)