Have you ever been in a situation when designing your warehouse where you had some measures that could not be grouped together due to dimensionality differences? A few measures that you can’t put into one Fact and of course, like me, you hate the idea of a Fact for each measure. I’ve been there and I found a simple effective design that you can use to combine all the unrelated measures together into one Fact. And “Let the Analysis Services Art handle the rest”.

Diagonal warehouse is based on a very known fact that

“NULL Aggregation is 0 “

Yes, nothing new. So why not use it to fill in the spaces between the measures? Weird, let’s see an example:

Measure 1  uses Time and Geography

Measure 2 uses Time, Geography and Dimension 2

Measure 3 uses Time and Dimension 3

How can we combine this Non-homogenous combination above into 1 Fact?

Simply put the dimensions data into diagonal form and fill the spaces with Nulls, keeping in mind of course to make the unknown member hidden in the dimension property … We’ll get to that later, now let’s look at the Fact table.

Looking at the above table, it will seem like strange input fields into the Fact. But come to think of it you’ll find that each and every dimension will drilldown correctly on its associated measure neglecting the other non-related measures due to the NULLs filled in.

When you build the cube in the Analysis Services, go to each and every dimension related the above Fact.  Don’t forget any dimensions and assign the unknown member as hidden like the below screenshot.

So think of it as a diagonal and start putting as much measures as you can to be combined. This will save you a lot of time and design headache.