BI Semantic Model?

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

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.

Tagged with: , , , ,
Posted in Business Intelligence, PowerPivot, SSAS
2 comments on “BI Semantic Model?
  1. Grace says:

    I have one question on power view , You may help me out
    How to add parameters to power view report like drop down, radio button…etc
    Can you please let me know.

    Thank You,

  2. maleyd says:

    The parameters are built into the power view reports as filters or slicers (similar to Power Pivot slicers). With Power View, it’s all about building interactive reports and visualizations from an end-user perspective. You can build out filters in a few different ways. We can use the multiple graphs and charts to enable cross-filtering (selecting one item on an interactive chart will filter the other charts on our view), at a view-level, or at an interactive part-level. I’ll write up an article soon going more in-depth into the different examples, but in the meantime, you can checkout this article for more information:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: