Decisions: PowerPivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012 from Melissa Coates on DBPedias

A big thanks: First thing first. I need to thank my good friend Javier Guillen (Blog | Twitter) for proofreading the decision matrix below & providing additional input. If you are interested in PowerPivot or Analysis Services, be sure to follow Javier.

Overview: I’ve been learning about the new features coming out in SQL Server 2012, including Tabular Models & PowerPivot. One thing I wanted was a decision matrix – for a new project, which type of model is most suitable for the requirements I am presented? I wanted thoroughly understand why I might suggest to a client that we create a model other than traditional OLAP. So, I created the decision matrix below for the purpose of helping to decide which type of model to create in SQL Server 2012.

Level: 101

Versions applicable:

PowerPivot 2.0
SharePoint 2010
SQL Server 2012 (initial release)

What is BISM?

In SQL Server 2012, the concept of the BI Semantic Model (BISM) is introduced. It includes:

PowerPivot for Excel
PowerPivot for SharePoint
Analysis Services Tabular
Analysis Services Multidimensional

You can think of PowerPivot and SSAS as a graduating lifecycle of tools from Personal BI to Team BI to Corporate BI. This is a really exciting step forward in the Microsoft BI toolset – one that I’m personally very excited about because it allows Personal BI agility + the ability for a solution to mature over time.

Currently, PowerPivot and SSAS Tabular are similar structures "under the covers" and have a seamless upgrade path. However, SSAS Multidimensional is a completely different structure - I have no doubt further integration will develop over time.

Decision Matrix: How to Choose Which Type of Model Meets Your Needs?

There are lots of decision factors here. As the products evolve and mature, these factors will certainly get refined.

Feature or Business Need	PowerPivot for Excel	PowerPivot for SharePoint	Analysis Services Tabular	Analysis Services Multidimensional
# of users	One, or very small (Personal BI)	Small to Medium (Team BI)	Large (Corporate BI)	Large (Corporate BI)
Software versions & editions required	Office 2010 (PowerPivot is a free download)	SharePoint 2010 Enterprise & SQL Server 2012 BI or Enterprise Edition & PowerPivot for SharePoint	SQL Server 2012 BI or Enterprise Edition	SQL Server 2012 BI or Enterprise Edition
Design Environment	Excel 2010	Excel 2010	SQL Server Data Tools (formerly BIDS)	SQL Server Data Tools (or BIDS prior to 2012 version)
Query Language	DAX (if MDX is passed it is resolved internally as a DAX query plan)	DAX (if MDX is passed it is resolved internally as a DAX query plan)	DAX (if MDX is passed it is resolved internally as a DAX query plan; MDX not permitted on a DirectQuery model)	MDX
Location of Data Model	PowerPivot Add-in to Excel	PowerPivot for SharePoint (a dedicated Analysis Services PowerPivot instance)	Analysis Services Tabular	Analysis Services OLAP
Data Accessibility to Reporting Tools	Excel (plus non-MSFT tools like Tableau)	Excel Power View PerformancePoint Reporting Services (plus non-MSFT tools like Tableau)	Excel Power View PerformancePoint Reporting Services (plus non-MSFT tools like Tableau)	Excel PerformancePoint Reporting Services (plus Non-MSFT tools like Tableau)
Ability to use Power View (formerly Crescent)	No	Yes (because it uses DAX)	Yes (because it uses DAX)	No
Type of Database Engine	Vertipaq (all data is highly compressed & fits into memory)	Vertipaq (all data is highly compressed & fits into memory)	Vertipaq (all data is highly compressed)	OLAP
Size of Dataset	File size: 2gb limit (after compression) Memory limit: 2gb (32-bit) or 4gb (64-bit)	File size: 2gb limit (after compression) (SharePoint size limitation)	Large (can partition; can use DirectQuery)	Large (can partition; can use MOLAP & ROLAP)
Usage of Many Disparate Data Sources	Yes (very suitable)	Yes (very suitable)	Yes (very suitable)	Yes (less suitable without underlying DW or ETL processes to integrate)
Ability to Pass Through Query to Underlying Data Source	No	No	Yes (DirectQuery)	Yes (ROLAP)
Row Level Security Supported	No (loophole: a data refresh can utilize Windows authentication--if implemented on the underlying data source)	No (loophole: a data refresh can utilize Windows authentication--if implemented on the underlying data source)	Yes (Windows authentication only)	Yes (Windows authentication only)
Ability to Manage Data Refreshes on a Schedule	No	Yes	Yes	Yes
Development Integrated with Visual Studio	No	No	Yes	Yes
Support for Source Control	No (loophole: can check an Excel file into source control)	No (loophole: versioning on SharePoint document library)	Yes	Yes
Support for IT Auditing & Management	No	Yes (PowerPivot Management Dashboard)	Yes	Yes
Many-to-Many Relationships Supported	Yes (created via DAX, not built into the model directly)	Yes (created via DAX, not built into the model directly)	Yes (created via DAX, not built into the model directly)	Yes (built in the model)
Ability to Use Actions	Drillthrough (default - not customizable)	Drillthrough (default - not customizable)	Drillthrough (default - not customizable)	Drillthrough Reporting Standard
Ability to Use Scope Assignments	No	No	No (loophole: within XMLA)	Yes
Extensible with .NET	No	No	No	Yes
Writeback Supported	No (Vertipaq structure is read only)	No (Vertipaq structure is read only)	No (Vertipaq structure is read only)	Yes
Upgrade / Maturity Path	To PowerPivot for SharePoint	To Analysis Services Tabular	N/A (not currently able to be upgraded into a Multidimensional model)	N/A