The Microsoft BI shuffle

With the introduction of BISM the Microsoft BI stack as become a little more complex to understand. In this blog post I attempt to clarify BISM. What does it mean, what does it hold and, more importantly, what does it do for me?

Business Intelligence Semantic Model

BISM stands for “Business Intelligence Semantic Model”. To understand why BISM exists I first have to explain that there are two main “engines” which make the Microsoft BI stack. Think of them as the foundation on which a Microsoft orientated BI solution rests.

  1. Vertipaq
  2. OLAP

OLAP is an abbreviation for “Online Analytical Processing”. The main idea is to deliver fast query response by “pre aggregating” frequently used queries. Some also refer to OLAP as a Cube in Analysis Services. Vertipaq, on the other hand, does no such thing. It relies on pure memory power. The more memory you have to more data you can handle. Because all queries are done “in memory” it is very fast. Vertipaq was first introduced with PowerPivot, a free add-in for Excel which enabled users to import large amounts of compressed data from different data sources in to
Excel. In SQL Server 2012 Vertipaq is also available as a “second” engine next to OLAP in analysis services.

Both OLAP and Vertipaq have their own programming language. Vertipaq uses DAX and OLAP uses MDX. OLAP is meant for a multi-dimensional approach whereas Vertipaq is more tabular by nature (which does not mean it does not support a multi-dimensional approach, but that’s a topic for a whole new blog post 🙂 ).

So where does BISM fit in all this?

BISM is designed to be a transparent layer towards client tools so that it doesn’t matter whether the developer has chosen Vertipaq or OLAP, the experience for the end user stays the same.

Every blog post should at least have one picture 🙂 . So, here we go; to sum it up:

So what does it do for me?

As an end user absolutely nothing 🙂 And that’s a good thing. The whole goal of BISM is to hide al the techno mumbo jumbo so that you just .. well, do what you normally do, work with information. For you as a BI Professional / Developer a lot 🙂 Things have shifted, you should know when to use Vertipaq and when to stick to OLAP. How Multidimensional Modeling fits in all this. My advice? Dive right in. Get to know this new world and make it your own. I know this is a rather short answer to give but I promise to write a different blog post covering that question more in depth, it really deserves more then a few lines.

Do you want to know more?

  • More information about how the old OLAP and the new BISM fit in Miscrosoft’s roadmap can be found here.
  • More information about PowerPivot can be found here.

Finally, you can download SQL 2012 CTP 3 here.


Data alerts in SQL #Denali

A new feature in SQL denali is “Data alerts”. Data alerts are a data driven alerting solution that helps you be informed about report data that is interesting or important to you, and at a relevant time. By using data alerts you no longer have to seek out information, it comes to you. More information can be found here.

Users can configure they’re own set of “data alerts” for they’re own set of reports. This means you only recieve a notification (email) when something “relevant” has happened within your report(s). To create a report alert go the the report for which you want to configure one, from the action menu select “New data alert”

This wil open a (silverlight based) new window in which you can cofigure your rule(s):

Once created you can view and manage all data alerts from “Manage Data alerts”:

I have not had the time to really play with it but at a glance I think it is an awesome feature.