SQL Server 2012: Tabular Models vs PowerPivot Models
We recently decided to a new development machine, Server 2012, SQL 2012, SharePoint 2013, Projects 2013 to showcase all the cool new stuff to the rest of our IT team. To use all the nice reporting functionality on SharePoint and SQL we installed everything on SQL 2012 Enterprise that we could tick off.
There is a new data model in SQL 2012 called tabular, it is part of the new feature called the Business Intelligence Semantic Model (BISM). It includes the multidimensional model (previously 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 without the need to understand MDX. You choose this when installing Analysis Services for SQL 2012.
The tabular model is an enhancement of the current PowerPivot data model experience. Sitting underneath all this it the very amazing Vertipaq engine. Whenever you use PowerPivot for SharePoint workbook the Vertipaq engine creates SSAS cube in memory. This is why PowerPivot for SharePoint requires SSAS to be installed and why you can use PowerPivot to create your cube and import that cube into SSAS, which you could not easily do in SQL2008R2 and PowerPivot 1.
There are some things that you find in tabular models that you wont get in PowerPivot:
Scalability – PowerPivot has a 2 GB limit for the size of the Excel file and does not support partitions, but tabular model have no limit and support partitions. Tabular models also support DirectQuery
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
Securability – Tabular models can use row security and dynamic security, neither of which PowerPivot supports, only Excel workbook file security
Professional development tools – Tabular models live in the Visual Studio shell. Thus, all the really nifty things the VS environments can be leveraged, compared to the limitations in Excel.
Analysis Services can now be installed in one of three server modes: Multidimensional and Data Mining (default), PowerPivot for SharePoint, and Tabular. And if you feel that you are now limited, just hang on, you can install multiple instances of SSAS on your server, so you can have all 3 modes running on the same machine. You will have to use a modular way of attending to different requirements.
Some more reading:
When to choose tabular models over PowerPivot models
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)
*Compiled from various sources.
Adrian