Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Data Warehouse Design Tip: How to model the many to many relationship between the fact and the dimension table?

Posted by Dylan Wan on June 8, 2007

Sometime we have a many to many relationship from a fact to its dimension and the dimension contains multiple elements which can be considered as a group or a set. How do we model this?

The above statement must be confusing. Let’s use an example. A people can have multiple skills and a skill can be held by multiple people.

One way is to treat this as a fact with both the employee and the skill dimension. You will be able to know how many employee with a given skill and how many skill an employee may have.

The above approach works fine if the business question is just that simple. However, let’s look at other related fact tables. You may have an employee capacity table, which include two dimensions, the employee dimension and date dimension. Each record of this fact table includes the number of hours available on a given day. So from this fact, you will know who are available for a given date or a range of dates. You will also be able to know what the period a employee are available and when will be the next available date.

The tricky question now comes. How can we know all employees with a given set of skills available in a given range of dates? How can we design our schema so it can be done very efficiently?

I will post my proposal in my next Data Warehouse Design Tip.

Advertisements

One Response to “Data Warehouse Design Tip: How to model the many to many relationship between the fact and the dimension table?”

  1. tomhaughey said

    Dylan, you say in your design tip on M-M on June 8, 2007 that you will present a solution in your next DW Design Tip. I cannot find this next tip. How can I get it?

    tom
    thaughey@gmail.com

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s