Comparing Tabular and Multidimensional Solutions (SSAS)

Analysis Services provides three different approaches for creating a business intelligence semantic model: tabular, multidimensional, and PowerPivot. Tabular solutions use relational modeling constructs such as tables and relationships for modeling data, and the xVelocity in-memory analytics engine for storing and calculating data. Multidimensional and data mining solutions use OLAP modeling constructs (cubes and dimensions) and MOLAP, ROLAP, or HOLAP storage. PowerPivot is a self-service BI solution that lets business analysts build an analytical data model inside an Excel workbook using the PowerPivot for Excel add-in. PowerPivot also uses xVelocity in-memory analytics engine within Excel and on SharePoint. Because PowerPivot solutions use Excel for both data modeling and rendering, deploying a workbook on a server for centralized and controlled data access requires SharePoint and Excel Services.

Tabular and multidimensional solutions are built using SQL Server Data Tools and are intended for corporate BI projects that run on a standalone Analysis Services instance. Both solutions yield high performance analytical databases that integrate easily with Excel, Reporting Services reports, and other BI applications from Microsoft and third-party applications. Yet each solution differs in how they are created, used, and deployed. This topic explores the differences, allowing you to compare and identify the solution that best meets your project requirements.

Because tabular is the newer solution, you might think that migrating an existing multidimensional solution to a tabular format is the correct course of action, but this is usually not the case. Tabular does not supersede multidimensional, and the two formats are not interchangeable. Unless you have a specific reason to do so, do not rebuild an existing multidimensional solution if it is already meeting the needs of your organization. For new projects, consider the tabular approach. It will be faster to design, test, and deploy; and it will work better with the latest self-service BI applications from Microsoft.

This topic includes the following sections:

Data Source Support by Solution Type

Model Features

Model Size

Programmability and Extensibility Support

Query and Scripting Language Support

Security Feature Support

Design Tools

Client Application Support

SharePoint Requirements

Server Deployment Modes for Multidimensional and Tabular Solutions

Next Step: Build a Solution

Additional information can be found in this technical article on MSDN: Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services.

Data Source Support by Solution Type

Multidimensional, data mining, tabular, and PowerPivot models use imported data from external sources. The amount of data you need to import, data provider requirements, and data source type can be a primary consideration when deciding which solution is a best fit for your data.

Data Size

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. For tabular, memory requirements will be larger than the size of data on disk due to additional data structures that are created when the tabular database is loaded into memory. 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 some projects, data requirements might be so large as to become a factor in choosing between the model types. If the data you need to load is many terabytes in size, a tabular solution might not meet your requirements if available memory cannot accommodate the data. There is a paging option that swaps in-memory data to disk, but very large amounts of data are better accommodated in multidimensional solutions. The largest Analysis Services databases in production today are multidimensional. For more information about memory paging options for tabular solutions, see Memory Properties. For more information about scaling a multidimensional solution, see Scale-Out Querying for Analysis Services with Read-Only Databases.

PowerPivot for Excel has an artificial file size limit of 2 gigabytes, which is imposed so that workbooks created in PowerPivot for Excel can be uploaded to SharePoint, which sets maximum limits on file upload size. One of the main reasons for migrating a PowerPivot 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 (PowerPivot for SharePoint).

Data Providers

Multidimensional and data mining solutions can import data from relational data sources using OLE DB native and managed providers. Tabular and PowerPivot models can import data from relational data sources, data feeds, and some document formats. You can also use ODBC providers with tabular and PowerPivot models.

Supported Data Sources

If your tabular solution includes DirectQuery, the data source must be SQL Server relational database, version SQL Server 2005 or later.

PowerPivot workbooks have data source restrictions related to data refresh on a server. In the client workspace, you can import data from Office documents and Access databases by way of the Office data connectivity components (or ACE provider). If you later publish this workbook to a server, refreshing the data might be problematic. The ACE provider is not designed for server environments and therefore it is not supported except in the PowerPivot client application.

For all other models, especially those that use MOLAP storage or Tabular in-memory storage, you can use any of the supported data sources as import data. To view the list of external data sources that you can import to each model, see the following topics:

• Supported Data Source Types (SSAS Multidimensional)

• Data Sources Supported (SSAS Tabular)

• Data Sources Supported by PowerPivot Workbooks

Model Features

The following table summarizes feature availability at the model level. If you already installed Analysis Services, you can use this information to understand the capabilities of the server mode you installed. If you are already familiar with model features in Analysis Services and your business requirements include one or more of these features, you can review this list to ensure that the feature you want to use is available in the type of model you plan to build.

For more information about how features compare by modeling approach, see the Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services technical article on MSDN.

Model Size

The size of the model, in terms of total number of objects, does not vary by solution type. However, the design tools used to build each solution vary in how they support a large number of objects. A larger model is somewhat easier to build in SQL Server Data Tools because it provides more facilities for diagramming and listing objects by type in Object Explorer and Solution Explorer. In SQL Server 2012, PowerPivot for Excel has a diagram view that you can filter by object type, but proximity of objects will not be adjusted automatically. The visual layout stays the same, minus the objects you have filtered out. Within PowerPivot, you cannot view objects in a sequential list.

Very large models that consist of many hundreds of tables or dimensions are often built programmatically in Visual Studio, and not in the design tools. For more information about the maximum number of objects in a model, see Maximum Capacity Specifications (Analysis Services).

Programmability and Extensibility Support

There is no developer support for PowerPivot workbooks. If you are using PowerPivot workbooks, you must use the built-in client and server applications as part of your solution. Excel programming and SharePoint programming are the only options.

Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.

For tabular and multidimensional models, there is one object model shared for both modalities. AMO and ADOMD.NET support both modes. Neither client library was revised for tabular constructs so you will need to understand how multidimensional and tabular constructs and naming conventions relate to each other. As a first step, review the AMO-to-tabular programming sample to learn AMO programming against a tabular model. For more information, download the sample from the codeplex web site.

Query and Scripting Language Support

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

• PowerPivot workbooks use DAX for calculations and queries.

• Tabular model databases support DAX calculations, DAX queries, and MDX queries.

• Multidimensional model databases support MDX calculations and MDX queries as well as ASSL.

• Data mining models support DMX and ASSL.

• Analysis Services PowerShell is supported for tabular, multidimensional, and data mining models.

All databases support XML/A.

Security Feature Support

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:

• PowerPivot workbooks are secured at the file level, using SharePoint permissions.

• Tabular model databases can use row-level security, using role-based permissions in Analysis Services.

• Multidimensional model databases can use dimension and cell-level security, using role-based permissions in Analysis Services.

PowerPivot workbooks can be restored to a tabular mode server. Once the file is restored, it is decoupled from SharePoint, allowing you to use almost all of the tabular modeling features, including row-level security. The one tabular modeling feature that you cannot use on a restored workbook is linked tables.

Design Tools

Data modeling skills and technical expertise can vary widely among users who are tasked with building analytical models. If tool familiarity or user expertise is a consideration for your solution, compare the following experiences for model creation.

Client Application Support

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 PowerPivot workbook. This means that you must have a tabular mode server or a PowerPivot 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 PowerPivot 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 PowerPivot workbooks that are hosted on PowerPivot 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 PowerPivot workbook, although Writeback is only supported for multidimensional databases.

PerformancePoint dashboards can connect to all Analysis Services databases, including PowerPivot workbooks. For more information, see Create Data Connections (PerformancePoint Services).

Server Deployment Modes for Multidimensional and Tabular Solutions

An Analysis Services instance is installed in one of three modes that set the operational context of the server. The server mode you install will determine the type of solutions that can be deployed to that server. Storage and memory architecture are the primary differences among the modes, but additional differences apply. The three server modes are briefly described in the following table. For more information, see Determine the Server Mode of an Analysis Services Instance.

Server mode and its corollary, solution type, can affect the availability of some features, including support for external data sources, authoring tools, extensibility, query and scripting languages, the granularity of security features, and the kinds of client applications that will work with your data.

SharePoint Requirements

SQL Server integrates with SharePoint by adding support for PowerPivot data access and tabular data access. Investment in SharePoint and SQL Server integration grows when you maximize the number of features used from each product. If you have SharePoint, you can install SQL Server PowerPivot for SharePoint to enable PowerPivot data access and get the PowerPivot .bism connection files used to access tabular databases running on an external Analysis Services instance on a network server.

Power View reporting, which uses PowerPivot and tabular databases as a data source, is a SharePoint feature provided by SQL Server. Although the tabular databases run on an Analysis Services instance outside of SharePoint, that data is consumed by Power View reports that run in SharePoint.

If you do not use SharePoint, you can still use PowerPivot for Excel to create PowerPivot workbooks but you will not have a cohesive data visualization experience. Each person who uses the workbook must download and view each workbook in Excel using the PowerPivot for Excel add-in to get data interaction and exploration using slicers, filters, and pivots. Otherwise, workbook visualization is limited to static data as it appears when you open the workbook.

Tabular, multidimensional, and data mining solutions run on Analysis Services instances on a network, with no SharePoint dependency.

Next Step: Build a Solution

Now that you have a basic understanding of how the solutions compare, try out the following tutorials to learn the steps for creating each one. The following links take you to tutorials that explain the steps.

• Build a PowerPivot model using the PowerPivot for Excel Tutorial.

• Build a tabular model using the Tabular Modeling (Adventure Works Tutorial).

• Build a multidimensional model using the Multidimensional Modeling (Adventure Works Tutorial).

• Build a data mining model using the Basic Data Mining Tutorial.

See Also

Tasks

Create and Manage Shared Data Sources (Reporting Services in SharePoint Integrated Mode)

Concepts

Analysis Services Instance Management

What's New (Analysis Services)

What's New (Reporting Services)

PowerPivot BI Semantic Model Connection (.bism)

Other Resources

What’s New in PowerPivot

PowerPivot Help for SQL Server 2012