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 Vertipaq 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:
- 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
- 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 toolchain - Tabular models live in the Visual Studio shell. Thus, they enjoy all the shell services such as integrated source control, msbuild integration, 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.