Our Blog

where we write about the things we love



Adding cream to your BI cake

Business Intelligence (BI) applications have long been compared to cakes, with many layers needed to create a complete and flavoursome experience.

With SQL Server 2012, Microsoft has added a user-friendly end user interface to their BI solution that supports all end user querying tools. The advantage: SQL Server now provides a single, consistent, easy to use model across all tools.

Microsoft calls this the Business Intelligence Semantic Model, or BISM. The objective of the BISM is to have one model for all user experiences – reporting, analytics, scorecards, dashboards, and custom applications (the cream in the cake).


Adding cream to your BI cake


The BISM is a business translation layer that sits between the database and the end user. The primary goal of the BISM is to allow users to interact with their data using familiar business terminology instead of having to understand where the data resides, the naming conventions and what business rules to apply to the data. In other words, the BISM provides the linkages between the reporting tools and the physical data models.

The picture below is Microsoft’s representation of its BISM.


BI Semantic Model


With the introduction of the BISM, there are now two flavours of Analysis Services databases in Microsoft SQL Server: multidimensional (ROLAP, MOLAP) databases, which are the mainstay of SQL Server Analysis Services; and tabular (VertiPaq) which are the basis of Excel PowerPivot.

You can create a BISM using Excel PowerPivot or SQL Server Data Tools (formerly BIDS). You create one of three things: a multidimensional model, a tabular model, or a PowerPivot workbook. Each of these items is a BI semantic model.

The BI Semantic Model can be considered as a three-layer model as the above diagram suggests:

  • The Data Model layer is the conceptual data model that the model developer and the model consumer work with. The BI Semantic Model is a hybrid model supporting both multidimensional and tabular data modelling.  
  • The Business Logic layer that encapsulates the intelligence in the model. An author creates a business logic model using either DAX (Data Analysis Expressions) or MDX (Multidimensional Expressions). The beauty of the new model is that DAX is an expression language based on Excel formulas that was introduced in PowerPivot and built on relational concepts. While DAX doesn’t give the raw power and flexibility that MDX offers, it can be written by trained users, does not generally require a background in development, and infrequently requires tuning.
  • The Data Access layer integrates data from multiple sources, including relational databases, business applications, flat files, OData feeds and Cloud Services.

As many of the BI professionals agree, based on what we know and seen in PowerPivot, BISM has a huge potential and will bring welcome enhancements when using the tabular models. These benefits include:

  • Schema simplification – There is no need to define explicit cubes, dimensions, measures, eliminating the perceived complexity of implementing an OLAP solution.
  • Improved performance – At first glance it may not be obvious, but as the BISM utilises the VertiPac engine (a proprietary columnular data store engine) – which works well for BI-type querying.
  • Flexibility – There will be no distinction between measures and dimensions. Every attribute can be used for aggregating and slicing. This is one PowerPivot feature that I really like.
  • The DAX expression-based language removes much of the complexity of MDX.
  • Possibility of real-time data access.
  • It allows solutions to scale from tens of millions of rows (using Excel PowerPivot), to hundreds of millions of rows (using Microsoft SharePoint) to billions of rows (with Microsoft SQL Server 2012).

From a business perspective the BISM enables end users (both technical and non-technical) to use any Microsoft client tool to self-serve BI content via a consistent semantic model, reducing their requirements for training and support. It also enables subject matter experts to architect semantic models relevant to their end users, further removing the dependency on specialised IT skills which more often than not impacts a business’s ability to support the demand for information.

In summary the BISM that is coming with Microsoft SQL Server 2012 is a great evolution of the BI stack, adding capability while simplifying the process for creating and using BI content. We are looking forward to the impending release so we can have some more cream with our cake.

If you would like to find out more about any of these areas, or have specific questions, please leave a comment or contact us.

Posted by: Nick Draper, Enterprise Applications Consultant | 24 February 2012

Tags: Business Intelligence

Blog archive

Stay up to date with all insights from the Intergen blog