Point in time analysis perfected

A while ago I wrote about how you accomplish this using DAX in PowerPivot. You can read about it here. It is useful but I always thought there were two major drawbacks to that approach; Code length and Usability.

The expression I used was huge! No I mean really huge. This is due the number IF statements I used, one for each possible combination of user selections in the filter.

So I decided to rewrite the expression with these goals in mind; shorten the code, and thus make it easier to understand, and making it less error prone. This post is all about sharing this new-found knowledge with you 🙂

The business scenario
An organization wants to have insight in how and if planning assignments change for their employees, the goal is to minimize last-minute changes and thereby creating a more manageable process. The organization decides to harness the power of their data warehouse which tracks assignment changes using the Kimball type 2 approach.

Our goal for the user is to be able to select any period in time and get the current / valid planning for that period without the use of complex logic (technical or functional), aka “point and click”.

Getting in the details
Below a snippet of our large assignment fact table in our data warehouse

In short: Ian Smith’s planning has changes two times for the same assignment. Each change is marked with a valid from and expiry dat marking the period in which that assignment line was valid. John Snow’s planning changed one time.

Below a snippet of the separate period table I imported. It can be of any source in this case it is a copy of the data dimension table in our data warehouse.

No relationship is created between the two tables in PowerPivot. The period table is purely used for the user to select a period for which he/she wants to see the valid planning.

On to the DAX
This is the measure I created:

=CALCULATE(SUM(Assignment[Planned hours]);


Assignment[Valid from date] <= MAXX(VALUES(Period); Period[Date])


Assignment[Expiry date] >= MAXX(VALUES(Period); Period[Date])



The CALCULATE is there to be able to influence the FILTER context. I could also have used SUMX. The FILTER returns a table in which all assignments are filtered; each assignment-line returned has a “validfrom” date smaller or equal and a “expiry” date larger or equal to the max date of what the user selected in the table period. The VALUES function returns the distinct values of what the user selected, in combination with the function MAXX that is always one row. The result being that the FILTER returns 31-jan-2009 when the user only selects 2009 or 31-jan-2009 when the user selects 2009 and month january.

The point is the user is free to see valid planning at year level as well as on day level or any combination in between without the use of complex IF statements. Pretty nifty huh?


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.

Tabular versus Multi Dimensional projects, when to use which Part 1; Role playing dimensions

In Tabular projects in SQL denali CTP3 it is now possible to have more then 1 relationship between tables using the same attribute on the corresponding lookup table.

The question I want to answer in this blog is “Does a role played dimension in a tabular project offer the same functionalty as it does in a multi dimensional project?”

Role played what?

Example: Each transaction in a fact table has a order date and a ship date. Both attributes refer to the same table (dimension) date (DimDate).
An OLAP project would creates an user interface displaying the two dimensions:

  • Order date
  • Ship date

Phyisically they both represent the same base table DimDate. The user, however, sees the DimDate tabel in two different contexts; order and ship date.

A Tabular project offer the same kind of functionality, but is different in both technique and in the way the user eventually navigates the model. How this is done and what this is, is explained here by Kasper de Jonge.

An example to illustrate

In our earlier example a user might want to know the sales amount by order and ship date. Using the classic Multi Dimensional approach you would see the order date on the rows and the ship date on the columns (or vice versa). This would result in a classic matrix report.

Using the Tabular approach you would have to create a second measure like “Internet sales amount shipped” (again; really read this first). You would then place the two measures on your report putting the date dimension on the rows resulting in:

I think that in many scenario’s this last approach is preferable. However, I can think of one in which I would prefer a matrix overview. Let’s imagine a magazine company, they have a marketing department which send out different letters, packages etc. The goal is to get people to subscribe to their magazine. Each payment (transcation) they receive is tagged so that the company can track which campaign resulted in each payment. The tagging is done automatically, the thing you want to investigate is if the “tagging” process is working correctly. A matrix report in which the campaigns created are on the rows and the campaigns to which each transaction is tagged are on the columns, would help.


In spite of what you might expect I am not going to say that you should use one project type or the other (Tabular or Multi Dimensional). As always it depends on a lot more variables, like required flexibility, frequency and maintainability. In the scenario that I described a standard report would be perfect since they would frequently use the report but dont require much flexibility (the report would not change very often). However, it is good to know what tabular and multi dimensional projects support best. I recommend becoming a master of both so that you can advice the right solution. More to come on this subject…

Integrate your SSAS based Excel reports … A SSAS action story

 You see there is one constant. One universal.

  It is the real truth.


 , reaction. Cause and effect.

 – Matrix Reloaded, Merovingian

The other day I was working on a major data warehouse release for a client. One of the many improvements is that I have enabled the client to browse back to his main operational system (in this case Microsoft CRM 4.0) from within Excel. I have done this by configuring a feature called “Actions” in SSAS 2008 (SQL Server Analysis Services).

SSAS distinguishes three kind of action types:

  • Drillthrough action
  • URL action
  • Reporting action

The names of each of these types make them self explantory, however, I have a blog to fill so here we go 🙂

Drillthrough action
Used to define which attributes are visibile after you have drilled through. Example;  I have drilled through to the underlying assignments which make up my total planning for a certain week. Out – of – the – box SSAS will show you the details . In this case it consists of:

  • Employee name
  • Taks name
  • Project number
  • Planned date
  • Planned hours
  • Actual hours

But let’s say I want to see the project type and name as well. These attributes are in a seperate dimension. Defining a”Drillthrough action” will enable you to select other and / or more attributes which you want to see when you drill through. More information can be found here.

URL action
This enables the user to jump from any level in a SSAS – based report to a website. You may configure a static or dynamic URL. A dynamic URL is configured using MDX. Yes MDX folks and no in this case “using MDX” does not equal a headache, really.

There are loads of blogs out there which you can read to find out how URL action works. I just want to give you one important hint which many leave out (which I found out together with a colleague). Make sure that the attribute level you use in the URL is unique. For example; let’s say I want to jump from my Product level to a webiste which shows me the details of that product. The URL contains a unique identifier to identifiy the product.

In SSAS the product name on which you can click to go to the website should be unique as well. Product name should have the surogate or natural key defined as its key. This way SSAS will know which product name belongs to which any other attribute, in this example the corresponding unique identifier used in the URL.

Excel action navigation example

A reporting action
A reporting action is similar to a standard or URL type action. you basicly choose the output form (PDF, HTML etc) and configure the URL pointing to the report. This is very handy when u need a ‘hard copy pre formatted report’ based on an analaysis made using a self service BI client tool like Excel. More information can be found here.