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