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.

Versions applicable:

PowerPivot 2012
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 Enterprise or BI Edition	SQL Server 2012 Enterprise, BI, or Standard Edition (limited features with Standard)
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 Multidimensional
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 Power View (as of SQL Server 2012 SP1 CU4) 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)	Yes (as of SQL Server 2012 SP1 CU4)
Type of Database Engine	xVelocity (all data is highly compressed & fits into memory)	xVelocity (all data is highly compressed & fits into memory)	xVelocity (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)	Extremely 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; row filter security only)	Yes (Cellset or Dimensional; 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 is not customizable; can use Tabular Actions Editor in BIDS Helper to customize columns or to create Report, Rowset & URL Actions)	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 (xVelocity structure is read only)	No (xVelocity structure is read only)	No (xVelocity structure is read only)	Yes
Language Translations	No	No	No	Yes
Ability to Use Named Sets	No	No	No	Yes
Ability to Use Role-Playing Dimensions	No	No	No	Yes
Ability to Apply Visual Totals (Security)	No	No	No	Yes
Ability to Use Ragged Hierarchies	No	No	No (loophole: HideMemberIf with BIDS Helper)	Yes
Ability to Override Default Member	No	No	No	Yes
Ability to Process Table Partitions in Parallel	N/A	N/A	No (partitions within each table are serially processed)	Yes
Ability to Create > 1 Cube within Database	N/A	N/A	No	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

Finding More Information

MSDN Technical Article – Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services

Marco Russo’s blog – Why to Use Tabular in Analysis Services 2012

Analysis Services and PowerPivot Team Blog – Comparing Analysis Services and PowerPivot

Cathy Dumas’ MSDN blog – When to Choose Tabular Models over PowerPivot Models <—really useful

Javier Guillen’s blog – Observations on Interoperability Between BISM Tabular and OLAP clients

Chris Webb’s blog – So, What is the BI Semantic Model?

Sample Chapter from Teo Lachev’s Book: Chapter 1 – Introducing Business Intelligence Semantic Model

Simran Jindal’s blog – So What is the BI Semantic Model or BISM Really?

Update on 2012-03-10 14:02 by Melissa Coates

Terminology change was made in the above entry: The Vertipaq engine has been rebranded as the xVelocity in-memory analytics engine.

Announcement from the PowerPivot and SSAS Team can be found here.

Update on 2012-05-13 21:12 by Melissa Coates

Added some additional content above after seeing Greg Galloway's excellent session at SQLRally called "Making Heads or Tails of Analysis Services Storage."