MEA Center of Expertise

We are a 120+ technology enthusiasts helping Microsoft customers around Middle-East & Africa region. We bridge Microsoft tools & technologies to their businesses.

The Diagonal Warehouse Design

The Diagonal Warehouse Design

  • Comments 1
  • Likes

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
  • good one!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment