Comparing tabular and multidimensional solutions

Applies to: SQL Server Analysis Services Azure Analysis Services Power BI Premium

SQL Server Analysis Services (SSAS) provides several approaches, or modes, for creating business intelligence semantic models: Tabular and Multidimensional.

Multidimensional mode is only available with SQL Server Analysis Services. If you want your models deployed to Azure Analysis Services or Power BI, you can stop reading now. Multidimensional models will not be supported in Azure Analysis Services or Power BI Premium datasets. If you want multidimensional models in the cloud, the only way is to deploy SQL Server Analysis Services in Multidimensional mode to an Azure VM.

Because multidimensional models are only supported in SQL Server Analysis Services, this article is not meant to be a comparison of Analysis Services platforms (SQL Server, Azure, Power BI). It is meant to provide a high-level comparison of multidimensional and tabular model constructs entirely in context of SQL Server Analysis Services.

SQL Server Analysis Services also includes Power Pivot for SharePoint mode, which remains supported for SharePoint 2016 and SharePoint 2013, however, Microsoft's BI strategy has shifted away from Power Pivot in Excel integration with SharePoint. Power BI and Power BI Report Server are now the recommended platforms to host Excel workbooks with Power Pivot models. As such, this article now excludes a Power Pivot for SharePoint comparison.

In SQL Server Analysis Services, having more than one approach enables a modeling experience tailored to different business and user requirements. Multidimensional is a mature technology built on open standards, embraced by numerous vendors of BI software, but can be hard to master. Tabular offers a relational modeling approach that many developers find more intuitive. In the long run, tabular models are easier to develop and easier to manage. While multidimensional models are still prevalent in many BI solutions, tabular models are now more widely accepted as the standard enterprise-grade BI semantic modeling solution on Microsoft platforms.

All models are deployed as databases that run on an Analysis Services instance, or with tabular models, deployed to a Power BI Premium capacity. Models are accessed by client applications using a single set of data providers, or services like Power BI. Model data is visualized in interactive and static reports via Excel, Reporting Services, Power BI, and BI tools from other vendors.

Tabular and multidimensional solutions created by using Visual Studio and are intended for corporate BI solutions that run on an SQL Server Analysis Services instance on-premises, and for tabular models, an Azure Analysis Services server resource or a Power BI Premium capacity. Each solution yields high performance analytical databases that integrate easily with clients applications and data visualizations services. Yet each solution differs in how they are created, used, and deployed. The bulk of this topic compares these two types so that you can identify the right approach for you.

Overview of modeling types

The following table enumerates the different models, summarizes the approach, initial release, and supported compatibility level.

Type Modeling description Initially released Compatibility level
Multidimensional OLAP modeling constructs (cubes, dimensions, measures). SQL Server 2000
SQL Server 2012 and later
1050
1100
Power Pivot Originally an add-in, but now fully integrated into Excel. Tabular model infrastructure. APIs and scripting not supported. SQL Server 2008 R2 N\A
Tabular Relational modeling constructs (model, tables, columns). Internally, metadata is inherited from OLAP modeling constructs (cubes, dimensions, measures). Code and script use OLAP metadata. SQL Server 2012
SQL Server 2014
1050
1103
Tabular in SQL Server 2016 and later Relational modeling constructs (model, tables, columns), articulated in tabular metadata object definitions in Tabular Model Scripting Language (TMSL) and Tabular Object Model (TOM) code. SQL Server 2016
SQL Server 2014
SQL Server 2019
1200
1400
1500
Tabular in Azure Analysis Services 1 Relational modeling constructs (model, tables, columns), articulated in tabular metadata object definitions in Tabular Model Scripting Language (TMSL) and Tabular Object Model (TOM) code. 2016 1200 and higher
Tabular in Power BI Premium 2 Relational modeling constructs (model, tables, columns), articulated in tabular metadata object definitions in Tabular Model Scripting Language (TMSL) and Tabular Object Model (TOM) code. 2020 1500 and higher

[1] Azure Analysis Services supports tabular models at the 1200 and higher compatibility levels. However, not all tabular modeling functionality described in this article is supported. While creating and deploying tabular models to Azure Analysis Services is much the same as it is for on-premises, it's important to understand the differences. To learn more, see What is Azure Analysis Services?

[2] Power BI Premium capacities support tabular models at the 1500 and higher compatibility levels. However, not all tabular modeling functionality described in this article is supported. While creating and deploying tabular models to Power BI Premium is much the same as it is for on-premises or Azure, it's important to understand the differences. To learn more, see Analysis Services in Power BI Premium

Compatibility level is important. It refers to release-specific behaviors in the Analysis Services engine. To learn more, see Tabular model compatibility level and Multidimensional model compatibility level

Model features

The following table summarizes feature availability at the model level. Review this list to ensure that the feature you want to use is available in the type of model you plan to build.

Feature Multidimensional Tabular
Actions Yes No
Aggregations Yes No
Calculated Column No Yes
Calculated Measures Yes Yes
Calculated Tables No Yes3
Custom Assemblies Yes No
Custom Rollups Yes No
Default Member Yes No
Display folders Yes Yes3
Distinct Count Yes Yes (via DAX)
Drillthrough Yes Yes (depends on client application)
Hierarchies Yes Yes
KPIs Yes Yes
Linked objects Yes Yes (linked tables)
M expressions No Yes3
Many-to-many relationships Yes No (but there is bi-directional cross filters at 1200 and higher compatibility levels)
Named sets Yes No
Ragged Hierarchies Yes Yes3
Parent-child Hierarchies Yes Yes (via DAX)
Partitions Yes Yes
Perspectives Yes Yes
Query interleaving No Yes4
Row-level Security Yes Yes
Object-level Security Yes Yes3
Semi-additive Measures Yes Yes
Translations Yes Yes
User-defined Hierarchies Yes Yes
Writeback Yes No

[3] For information about functional differences between compatibility levels, see Compatibility Level for tabular models in Analysis Services.

[4] - SQL Server 2019 and later Analysis Services, Azure Analysis Services.

Data Considerations

Tabular and multidimensional models use imported data from external sources. The amount and type of data you need to import can be a primary consideration when deciding which model type best fits your data.

Compression

Both tabular and multidimensional solutions use data compression that reduces the size of the Analysis Services database relative to the data warehouse from which you are importing data. Because actual compression will vary based on the characteristics of the underlying data, there is no way to know precisely how much disk and memory will be required by a solution after data is processed and used in queries.

An estimate used by many Analysis Services developers is that primary storage of a 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 (in-memory or disk)

The size of an Analysis Services database is constrained only by the resources available to run it. The model type and storage mode will also play a role in how large the database can grow.

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, which means you must have sufficient memory to not only load all the data, but also additional data structures created to support queries.

DirectQuery, revamped in SQL Server 2016, has fewer restrictions than before, and better performance. Leveraging the backend relational database for storage and query execution makes building a large scale Tabular model more feasible than was previously possible.

Historically, the largest databases in production are multidimensional, with processing and query workloads running independently on dedicated hardware, each one optimized for its respective use. Tabular databases are catching up quickly, and new advancements in DirectQuery will help close the gap even further.

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 guide customers to multidimensional solutions.

Under load, both disk and memory requirements for either solution type can be expected to increase as Analysis Services caches, stores, scans, and queries data. For more information about memory paging options, see Memory Properties. To learn more about scale, see High availability and Scalability in Analysis Services.

Power Pivot for Excel has an artificial file size limit of 2 gigabytes, which is imposed so that workbooks created in Power Pivot for Excel can be uploaded to SharePoint, which sets maximum limits on file upload size. One of the main reasons for migrating a Power Pivot workbook to a tabular solution on a standalone Analysis Services instance is to get around the file size limitation. For more information about configuring maximum file upload size, see Configure Maximum File Upload Size (Power Pivot for SharePoint).

Supported data sources

Tabular models can import data from relational data sources, data feeds, and some document formats. You can also use OLE DB for ODBC providers with tabular models. Tabular models at the 1400 compatibility level offer a significant increase in the variety of data sources from which you can import from. This is due to the introduction of the modern Get Data data query and import features in Visual Studio utilizing the M formula query language.

Multidimensional solutions can import data from relational data sources using OLE DB native and managed providers.

To view the list of external data sources that you can import to each model, see the following topics:

Query and scripting language support

Analysis Services includes MDX, DMX, DAX, XML/A, ASSL, and TMSL. Support for these languages can vary by model type. If query and scripting language requirements are a consideration, review the following list.

  • Tabular model databases support DAX calculations, DAX queries, and MDX queries. This is true at all compatibilities levels. Scripting languages are ASSL (over XMLA) for compatibility levels 1050-1103, and TMSL (over XMLA) for compatibility level 1200 and higher.

  • Multidimensional model databases support MDX calculations, MDX queries, DAX queries, and ASSL.

  • Analysis Services PowerShell is supported for tabular and multidimensional models and databases.

All databases support XMLA.

Security features

All Analysis Services solutions can be secured at the database level. More granular security options vary by mode. If granular security settings are requirement for your solution, review the following list to ensure the level of security you want is supported in the type of solution you want to build:

Design tools

Visual Studio with Analysis Services projects extension, also known as SQL Server Data Tools (SSDT), is the primary tool used to create both multidimensional and tabular solutions. This authoring environment uses the Visual Studio shell to provide designer workspaces, property panes, and object navigation. To learn more, see Analysis Services tools.

Client application support

In-general, tabular and multidimensional solutions support client applications using one or more of the Analysis Services client libraries (MSOLAP, AMOMD, ADOMD). For example, Excel, Power BI Desktop, and custom applications.

If you are using Reporting Services, report feature availability varies across editions and server modes. For this reason, the type of report that you want to build might influence which server mode you choose to install.

Power View, a Reporting Services authoring tool that runs in SharePoint, is available on a report server that is deployed in a SharePoint 2010 farm. The only type of data source that can be used with this report is an Analysis Services tabular model database or a Power Pivot workbook. This means that you must have a tabular mode server or a Power Pivot for SharePoint server to host the data source used by this type of report. You cannot use a multidimensional model as a data source for a Power View report. You must create a Power Pivot BI Semantic Model connection or a Reporting Services shared data source to use as the data source for a Power View report.

Report Builder and Report Designer can use any Analysis Services database, including Power Pivot workbooks that are hosted on Power Pivot for SharePoint.

Excel PivotTable reports are supported by all Analysis Services databases. Excel functionality is the same whether you use a tabular .database, multidimensional database, or Power Pivot workbook, although Writeback is only supported for multidimensional databases.

See also

Tabular model overview
Multidimensional models