With the release of SQL Server 2012, Microsoft offers a second new data model to their BI solutions. They are calling the overall OLAP solution the BI Semantic Model. It’s a framework to give companies more options for offering their business users a front-end interface to interact with data and build their own insights.
Looking at the diagram above, we can see two different data models. One is the traditional multi-dimensional model. This has been what Microsoft has traditionally offered with previous versions of SSAS. This model supports MDX querying, and for data access, can support ROLAP, MOLAP and a hybrid version of the two as well. The newer data model introduced is the Tabular data model.
This is Microsoft’s In-memory OLAP solution. We can start building out a Tabular data model by opening up Excel, and building a Power Pivot model. The language supported is called DAX (Data Analysis Expressions) and is meant to be very similar to the excel language used. This is also how business users can start building out their own BI solutions and incorporating their own data feeds and insights. This is where we can see the advent of what Microsoft deems “Personal BI”. Moving this Power Pivot solution to SharePoint falls more in the range of “Team BI” and allows you to share this tabular model with others to be opened up in Excel. Last we can move the Power Pivot model into an SSAS Tabular model instance, and distribute for other users to connect to via SSRS, PerformancePoint, PowerView, and Excel. So to re-iterate, there are two ways to build a BI Semantic model using the tabular model, through Power Pivot, or through SQL Server Data Tools and loading it into SSAS.
A few quick notes on the difference between Tabular/Multi-dimensional:
- You cannot combine an SSAS instance to support both. If you have a multi-dimensional instance and you want to load a tabular model, you have to install a separate instance in tabular mode.
- PowerView can only connect to tabular
- Data mining can only be done on multi-dimensional
- Translations only apply to multi-dimensional
I’ll write more posts in the future breaking down the advantages/disadvantages of using the different models and when you might want to choose one over another.