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

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.