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 currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in Power Pivot, SQL Server 2012, SQLServerPedia Syndication, Tabular. Bookmark the permalink.

2 Responses to SQL Server 2012: Tabular Models vs PowerPivot Models

  1. Linda says:

    In the section on scalability, reference is made to the 2 GB file size limitation. Is this not only applicable to 32-bit installations? My understanding is that the maximum file size limitation is gone if you are using 64-bit Excel and that the workbook size is then limited only by the availability of disk and memory resources on your computer

    • James Serra says:

      Hi Linda,

      Sorry for the confusion: The 32 Bit version can only address 2GB of memory which effectively limits the size of the PowerPivot workbook to about 500-700Mb. The 64 bit version used to be limited to 4Gb file size in Excel 2010 but in Excel 2013 that limitation has been removed which means that the size of the workbook is no longer limited by the software but rather by the physical configuration of one’s machine.

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>