SQL Server 2012: Tabular Models vs PowerPivot Models

In SQL Server 2012, there is a new data model, called tabular, that is part of the new feature called the Business Intelligence Semantic Model (BISM).  BISM also includes the multidimensional model (formally called the UDM).

The Tabular model is based on concepts like tables and relationships that are familiar to anyone who has a relational database background, making it easier to use than the multidimensional model.  The tabular model is a server mode you choose when installing Analysis Services.

The tabular model is an enhancement of the current PowerPivot data model experience, both of which use the xVelocity engine.  When opening a PowerPivot for SharePoint workbook, a SSAS cube is created behind the scenes, which is why PowerPivot for SharePoint requires SSAS to be installed.

So if tabular models and PowerPivot models use the same Analysis Services engine, why are tabular models necessary when we already have PowerPivot?

There are four things that tabular models offer that PowerPivot models does not:

  1. Scalability - PowerPivot has a 2 GB limit for the size of the Excel file and does not support partitions, but tabular models have no limit and support partitions.  Tabular models also support DirectQuery and table partitions
  2. Manageability – There are a lot of tools you can use with the tabular model that you can’t use with PowerPivot: SSMS, AMO, AMOMD, XMLA, Deployment Wizard, AMO for PowerShell, and Integration Services.  You can also use SSMS (SQL Server Management Studio) for backup and restore, adding and managing partitions, adding and managing security roles, processing databases, tables or partitions, restoring from PowerPivot workbook, etc.
  3. Securability – Tabular models can use row security and dynamic security, neither of which PowerPivot supports, only Excel workbook file security.  Also, with tabular models you can define roles which define permission sets for users and groups
  4. Professional development toolchain - Tabular models live in the Visual Studio shell via SQL Server Data Tools (SSDT).  Thus, they enjoy all the shell services such as integrated source control, msbuild integration, Deployment Wizard, and Team Build integration.  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

So Analysis Services can now be installed in one of three server modes: Multidimensional and Data Mining (default), PowerPivot for SharePoint, and Tabular.

You can see that the audiences for these two approaches are different: Tabular models are for IT professionals, while PowerPivot models are for business users.  And keep in mind you can import a PowerPivot workbook to create a new tabular product.  For example, a business user might create a solution using PowerPivot.  By restoring the workbook, IT can add features that are not supported in PowerPivot, such as roles to grant read permission to different groups of users.  Additionally, recognition by IT that the model is important to the organization may prompt them to import the workbook into a tabular project and take ongoing responsibility for developing the model.

More info:

When to choose tabular models over PowerPivot models

SQL Server 2012 Tabular Models vs. PowerPivot

Decisions: PowerPivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012

Comparing Analysis Services and PowerPivot

Feature by Server Mode or Solution Type (SSAS)

Converting a PowerPivot model to a 2012 SSAS Tabular Cube

The Fantastic 12 of 2012: Behind the Scenes of Managed Self-Service BI

Building an Ideal Tabular Model for Power View reports

Introducing the BI Semantic Model in Microsoft® SQL Server® 2012

Should you use a data warehouse with a tabular cube?

Getting started with the SQL Server 2012 Tabular Model – Part 2

About James Serra

James is SQL Server MVP and a independent consultant with the title of Business Intelligence/Data Warehouse/Master Data Management Architect and Developer, specializing in the Microsoft SQL Server BI stack.
This entry was posted in Power Pivot, SQL Server 2012, SQLServerPedia Syndication, Tabular. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>