When to choose tabular models over PowerPivot models

Sometimes people ask me for the slide deck to my PASS presentation. I send out the deck, but there is only one slide in there that is worth anything. This post summarizes the only useful slide in the entire deck.

Let’s think about this question – why are tabular models necessary when we already have PowerPivot? After all, tabular models and PowerPivot models use the same engine and have the same capabilities for defining rich business logic in DAX. When is it necessary to migrate to tabular models?

There are four things that tabular models offer that PowerPivot does not: scalability, manageability, securability, and professional development toolchain. The following table shows the differences in these feature areas.

Area	Pro Features
Scalability	PowerPivot has a 2 GB limit for the size of the Excel file. Tabular models do not have a hard upper size limit. Tabular models have partitions, which are used to manage processing of large data volumes. PowerPivot does not. Tabular models support DirectQuery. You can consider DirectQuery almost as a scaling feature as it allows you to avoid processing large data volumes altogether. PowerPivot only queries the VertiPaq cache.
Manageability	See my blog post on tabular model manageability for a description of the management tool chain. PowerPivot does not have such a manageability story. You can have daily scheduled data refresh in Sharepoint, and you can check on usage data, but that’s about it.
Securability	Tabular models can be secured using row security. Dynamic security is also supported. I owe you all a post on security, but for now you can use the MSDN documentation for roles. PowerPivot does not support row security or dynamic security. Security for a workbook is just a binary choice – either a user can read a workbook or she can’t.
Development Toolchain	Tabular models live in the VS shell. Thus, they enjoy all the shell services such as integrated source control, msbuild integration, and Team Build integration. The editing environment can be extended using Visual Studio extensions, such as the DAX Editor.  PowerPivot lives in the Excel environment, thus it is limited to the extensibility model provided in Excel (which doesn’t include source control or build configuration). Also, because tabular models live in the VS environment, build and deployment can be naturally separated. The deployment wizard can be used after building to change around connection strings, retain role members, etc before deploying changes in a model to production. PowerPivot doesn’t have similar functionality.

You don’t need to move to tabular models because you need BI features such as perspectives, KPIs, hierarchies, date tables, or complicated DAX. You can stay in Excel and write all kinds of rich models. If you have a small team, you can share these models using PowerPivot for Sharepoint. Move to tabular models if you need to scale out, secure your model, manage your model after deployment, or use a formal development process. You can also do tabular modeling if you just happen to like VS better than Excel or if you want to share without using SharePoint.

Now there is not any reason to visit the PASS site and look at the deck. Maybe next time I do a presentation I’ll just show my blog instead of using slides.

Comments

• Anonymous
  November 16, 2011
  I was hooked at "Let’s think about this question..." Pausing for perspective is exactly what's needed as we juggle Power View, Azure Federation, PivotViewer (!!), BISM, DAX, MDX, DMX, 1.37 million SharePoint configurations, etc. And we need it from the Product Teams, since they (even more than SQL CAT, for example) are in the most authoritative position to provide context.
• Anonymous
  March 28, 2014
  The comment has been removed
• Anonymous
  July 21, 2014
  Isn't the 2GB limit in Excel only for the 32bit version of office? I thought the 64Bit version removed this limit.