SQL Server 2016 Analysis Servers - PowerPivot, Tabular or Multidimensional?

Having downloaded SQL Server 2016 Developer Edition (it’s free, click here to find out how), for the first time in years I went about a full install of the latest version of Analysis Services. 

There are three modes in which SSAS can be installed, so I thought I’d give a brief overview of each of them:

Multidimensional and Data Mining (often referred to just as Multidimensional), Tabular Mode and finally PowerPivot Mode are the three options given during the installation process.

Having different modes allows SSAS to offer a more customised solution to closely meet the needs of a wider range of of businesses and users. For example Multidimensional is well suited for large scale use - many users, large volumes of data, low latency etc, while PowerPivot is aimed more at the individual user and Tabular mode could be said to sit somewhere in between.

Multidimensional Mode

This is the “classic” mode for Analysis Services to be installed in, available in it's current form since SQL Server 2005 and is a mature product on an enterprise scale. If you’re already familiar with building OLAP databases then you can skip ahead to the next mode. Otherwise read on.

In Multidimensional mode cubes, measures, dimensions etc are designed in Visual Studio and deployed to the SSAS (Multidimensional Mode) server. Typically this process is a dedicated developer set of tasks - you would not expect business users to design the OLAP database.

Measure groups can be heavily partitioned to support very large volumes of data, only limited by the resources available. Parallel processing of partitions can be implemented to reduce data latency and improve performance, and security can be customised right down to the cell level. A lot of power and complexity can be built into the model to handle different kinds of relationships and aggregations, and additional enhancements can be made using MDX. Many previous posts on this blog have been written covering some of the more advanced features of Multidimensional mode. Also note that MDX is the primary language for querying databases designed in this mode.

PowerPivot Mode

To quote from MSDN:

“An Analysis Services server in Power Pivot mode provides server hosting of Power Pivot data in a SharePoint farm”

So that is basically the use-case for installing SSAS in PowerPivot mode - if you intend to use PowerPivot (an Excel-addin) to create models and then want to share those models, or schedule automated refreshes of them say to support regular reporting, then you need to have an SSAS PowerPivot instance.  In addition, this will need to be registered with an Office Online Server (a SharePoint 2016 feature, previously this was called Excel Services).

PowerPivot models are comparatively quick and easy to design, with far less complexity than the other two modes. Therefore power users on the business side (as opposed to developers) could be expected to develop and deploy these models themselves.

However, if you don't intend to distribute or perform server-style tasks (scheduling, security etc) on your models and they are really just for local use, it is unlikely you’ll need SSAS in PowerPivot mode.  You could instead just use PowerPivot as it comes - as a downloadable Add-in for Excel. 

PowerPivot is an end-user tool where users can source data from multiple sources and define their own relationships and calculations.

Important Note: While PowerPivot can support significant data compression, there is still a file size limitation of 2Gb, regardless of whether it is being used locally or uploaded and managed in SharePoint. So if you are expecting to handle large volumes of data, this might not be the best choice for you. You would do well then to consider Tabular Mode.

 Tabular Mode

Introduced in SQL 2012, Tabular Mode has similar functionality to PowerPivot, except that is more powerful and can handle significantly larger volumes of data. While Multidimensional mode is considered "matured", the list of new and improved components in SSAS consists almost entirely of updates to Tabular mode.  Expect more features to be added in future releases.

Tabular mode uses column storage (as opposed to row storage for Mutidimensional). It therefore compresses data using an entirely different engine, making possible higher compression rates. This can have significant improvement on things like distinct counts, which have often been a sore point in traditional Multidimensional models, but can be used to significant advantage in Tabular mode.

In Tabular mode, the data is held in memory so, instead of the 2Gb hard limit of PowerPivot, you are limited only by how much memory is available to you (rather than disk space in Multidimensional Mode), and compression rates are high.  While models are quick and fast to design the power of Tabular (and therefore complexity) is with the DAX language used to create formulas, relationships and quickly link data together. While Multidimensional (and therefore MDX) is very fast at aggregating data but poor when it comes to accessing leaf level, DAX can handle leaf level data with relative ease and performance, but designing aggregations and other features such as Custom Rollups are simply not possible. Other Multidimensional features such as parallel processing are also not available with Tabular, which could impact data latency times and performance as the database grows in size.

Note that as Tabular mode is still maturing, it can be expected that Microsoft will work to close the gap between Multidimensional and Tabular modes with each new release.

In making the decision of which mode is best for you, you will want to consider areas such as expected data volumes, data latency/refresh times, required features, hardware resources available etc.

For a more detailed feature comparison between Multidimensional and Tabular check out the MSDN article here:

https://msdn.microsoft.com/en-us/library/hh212940.aspx#Anchor_1