SSAS - Comparing Tabular and Multidimensional Solutions

Analysis Services is an online analytical data engine and SQL Server 2016 makes several enhancements to Analysis Services including improvements in enterprise decision support and business analytics, modeling platform, BI tools, SharePoint integration, and hybrid BI.

In SQL Server 2016, Microsoft development teams focused on tabular models in Analysis Services to enhancements and adds-on around 1200 new compatibility level for tabular model databases. Analysis Services 2016 is coming with a number of enhancements to multidimensional models also.

Comparing Tabular and Multidimensional Solutions

With the help of Analysis Services, we can create various business intelligence semantic models based on different business and user requirements such as Multidimensional, Tabular, and Power Pivot.

Key Features	Multidimensional	Tabular
Released on	Multidimensional solution was introduced in SQL Server 2000 which is a mature technology built on open standards, embraced by numerous vendors of BI software but can be hard to master.	Tabular solution was introduced in SQL Server 2012. Tabular models can be created at the default compatibility level 1200; using the latest functionality, or at the older 1103 compatibility level.
Modeling description	Multidimensional offers OLAP modeling constructs (cubes, dimensions, measures).	Tabular offers a relational modeling constructs (model, tables, columns) approach, articulated in tabular metadata object definitions in script and code.
Compression	This solution uses data compression that reduces the size of the Analysis Services database.	These solutions use data compression that reduces the size of the Analysis Services database.
Database Size	The roughly estimated size of Multidimensional database will be about one third size of the original data.	Tabular databases can sometimes get greater amounts of compression, about one tenth the size, especially if most of the data is imported from fact tables.
Size of the model and resource bias	For Multidimensional offloading data storage and query execution is available via ROLAP. On a query server, rowsets can be cached and stale ones paged out. Efficient and balanced use of memory and disk resources often guides customers to multidimensional solutions.	Tabular databases run either in-memory or in DirectQuery mode that offloads query execution to an external database.  For Tabular in-memory analytics, the database is stored entirely in memory to allow sufficient memory to not only loads all the data, but also additional data structures created to support queries.
Supported Data Sources	Multidimensional solutions can import data from relational data sources using OLE DB native and managed providers.	Tabular models can import data from relational data sources, data feeds, and some document formats, the data is imported from fact tables also.
Server Deployment Modes	Runs multidimensional and data mining solutions to a default instance of Analysis Services. Deployment mode 0 is the default for an Analysis Services installation.	Runs tabular solutions on a standalone instance of Analysis Services configured for deployment mode 2.
Security Features	Multidimensional model databases can use dimension and cell-level security, using role-based permissions in Analysis Services.	Tabular model databases can use row-level security, using role-based permissions in Analysis Services.