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.

Comments

• Anonymous
  January 16, 2014
  good one!