# The Diagonal Warehouse Design

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.

 TimeID GeographyID Dimension2 Dimension3 Measure1 Measure2 Measure3 Time1 Geo1 NULL NULL Value11 NULL NULL Time2 Geo2 NULL NULL Value12 NULL NULL Time3 Geo3 Dim1 Null NULL Value21 NULL Time4 Geo4 Dim2 NULL NULL Value22 NULL Time5 Geo5 Dim3 NULL NULL Value23 NULL Time6 NULL NULL Dim1 NULL NULL Value31 Time7 NULL NULL Dim2 NULL NULL Value32

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.