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 a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior 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.

4 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.

  2. Venkatesh says:

    Thanks for this. May be another addition to the list is that the tabular model allows users to have more friendly names for entities and attributes which uplifts self-service for end users. Powerpivot and others would also allow but that’s not potentially the purpose of powerpivot. Please correct me if I am wrong.
    Regards, Venkatesh

  3. Karthi Premakumari says:

    Hi James, Thanks for the post. Question to you: I am involved in reading data from a Tabular data model (that I created using PowerPivot for Excel in Excel 2013 – let’s call this datamodel spreadsheet – and uploaded to SSAS Tabular using the Restore from PowerPivot feature in SSAS) by connecting to it using Excel 2013 – let’s call this report spreadsheet -“and it works fine.

    Now I need to be able host and open the report spreadsheet using SharePoint within a browser. For this we installed SSAS\PowerPivot running in SharePoint mode and configrued the Excel Services in SharePoint to connect to it. Now when I open the report spreadsheet from a SharePoint library, yes, it displays static data (which is some data that I stored when I safed the spreadsheet earlier) since there is no data in SSAS\PowerPivot yet. The option to restore from powerpivot is disabled for SSAS running in SharePoint mode.

    My DBA thinks installing the spPowerPivot.msi (ie PowerPivot for SharePoint 2013 add-in) in SharePoint should allow me to store the power pivot data model into SSAS\PowerPivot using the datamodel spreadsheet. I am thinking that even before I do this, I am going to try and open the datamodel spreadsheet from within SharePoint and see if saving it will actually create the cube on the SSAS\PowerPivot. I got this idea when I read the line in your post that says “Excel Service in Share Point create a cube behind the scenes”.

    Any thoughts?

Leave a Reply

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