When to choose tabular models over PowerPivot models

  • Article

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.