In the recently released version of SQL Server a lot has changed. With SQL Server 2012 (Denali) Microsoft is introducing BISM, the Business Intelligence Semantic Model, which is a new analysis server functionality. With the BI semantic model, Microsoft is providing one semantic layer to all different front end applications. This will make that this BI semantic model will become the source for Excel, Reporting Services, Power View (Project Crescent), dashboards, scorecards, ...
When creating a BI semantic model, you are able to choose between 2 types of projects:
- Multi-dimensional model: This is what we know today as Analysis Services. Creating a cube and storing it in OLAP architecture to finally query it with MDX.
- Tabular model: This is the new table based approach that can be stored "in memory" with the Vertipaq engine and can be queried using DAX.
Since the Multidimensional model is what we have today, it is more interesting to elaborate a bit more on the Tabular model. This completely new model can be compared with what you can do today with PowerPivot, but then on a more enterprise level. So with the tabular model, you create a model based on the tables or views preferably from a data warehouse. Create relations between these tables/views and add additional columns and calculated measures to the model. This model can then be deployed to the BISM server where you can choose to store it in memory with the Vertipaq engine or have Direct Query where you go directly to the source.
In the PowerPivot version you can have:
- Multiple relations between tables/views
- Relations between tables/views can consist of multiple columns
- Hierarchies can be created
- Security can be applied to the model
So a lot of the current standard functionality missing in PowerPivot has been added in the BISM tabular model. But the tabular model is not intended as a replacement for what we know today as Analysis Services. It wont replace the existing multidimensional implementations. The tabular model is an extension to the current 2008 offering and will have its place next to the existing multidimensional OLAP platform.
Contact us for more information on SQL Server 2012 (Denali) Analysis Services !