the BI Sematic Model in SQL Server Denali ctp3

  • Article

Some sort of semantic model is needed in every BI solution but what is it and why do I need one? 

It’s a view of the data store(s) you want to work on for business intelligence which adds additional information that can be stored in the individual tables themselves.  For example how are the tables joined together, friendly names for the  columns in those tables that make sense, and security to define which parts of the data each users can work with .   This semantic model is then used as the source for the reports and analytics that comprise a BI solution.  

This layer of abstraction may seem unnecessary in some cases for example if there is only one source of data such as a data warehouse which already has this functionality all in place. However the sematic model in many solutions including Microsoft  provides the means to group and sum data across hierarchies such as summing by year down to month down to day, or from enterprise to division to department. 

The other advantage of this approach is that it can mask changes to the underlying schema form the reports that depend on it; the report will reference an object in the model which  can have it’s definition changed to reflect the change without having to touch the report.  Multiply this in a BI solution that has hundreds of reports and this can be a reason to use the model in its own right. Models can also span different sources of data e.g. database like SQL Server, Oracle DB2, to spreadsheets flat files and more recently data feeds from internal web services and the cloud.

Up until now Microsoft has had two semantic models in place one to reflect the relational world in reporting services, the report model and the Unified Dimensional Model (UDM) built into analysis services to model the complexities of olap cubes.  The problem I have with this is that you can end up having to do everything twice; setup security, add business logic and so  on in each model making change control difficult especially as by their very nature they don’t work in the same way.  To get around this one workaround I have used before is to lay a report model with no logic in it at all directly on top  of  a UDM and then the reports inherit the same security hierarchies as exist in the analysis services world.  That is a bit of fudge to be honest and so I am pleased to see the problem is going to go away with the introduction of a new model the BI Semantic Model in SQL Server Denali ctp3.  

Actually BISM v1 is in SQL Server 2008R2 as part of PowerPivot as it is the mechanism to describe how the sheets (tables) in the PowerPivot were related and also where the business logic you specified (in DAX) was stored.  as I mentioned in my last post,  PowerPivot in SQL Server Denali ctp3 now lets you use a table design view to create these relationships add calculations and now allows you to also create hierarchies, either in the PowerPivot viewer in Excel..

bism hierarchy

..or in the BI Development studio.

You can also see that some of the columns are greyed out, which indicates I have hidden them.

Once you have created a BISM, you can then use it much as you would analysis services:

  • in SharePoint Dashboard designer to create scorecards and  dashboards
  • in reporting services as a source for reports, and as a source for the new Crescent reporting tool
  • in any Excel version as a source of a pivot table (without using the PowerPivot add-in)

 

So why all this change apart from the need to just have one model?

For me the key thing that BISM provides is that it enables BI professionals to work with end users to scale up the self service tactical BI that they have built in PowerPivot. I think this is important because if the business have gone to the trouble of building a tactical BI solution it should be protected and possibly scaled.

 

This is available to try now you’ll need SQL Server Denali ctp 3 and SharePoint 2010 with sp1 installed and there’s matching sample databases, reports and tutorials for BISM on codeplex.