SSAS/KIMBALL: modeling a N:M relation between dimensions (part I)

Hi,

During the development of a dashboard for a customer i've a awkward problem. The situation is like this: There are events in the every daily business (fact) and these events can happen on certain locations (physical) locations. These locations can be shared by different (logical) departments.  So conceptually you have something like this:

And if you model this in a Bachman diagram (3NF) it will look like this:
 

So there are two standard entities and one associative entity has been added for handling the n:m relation. This way n:m relations can be easily modeled in 3NF. So the values for the department table will be :

1. Department A
2. Department B
3. Department C

The values for the location table are:

1. Location 1
2. Location 2
3. Location 3

And the associative table "Department_Location" contains :

1. 1 (Department A), 1 (Location 1)
2. 1 (Department A), 2 (Location 2)
3. 2 (Department B), 2 (Location 2)
4. 3 (Department C), 3 (Location 3)

And you could introduce a valid_from and a valid_to (as in temporal modeling) where you can check the validity whenever the record is valid or not. Relations exists and can no longer exist at some point in time.

A first assumption of me was modeling the location and the department seperately (the grain of the fact is at location level):

So the grain is at Location level and therefore a 'simple' dimension is enough. But the fact can happen at different departments (e.g. in case of location 1) and therefore i introduced a bridge table. So the key of the fact table is pushed to the bridge department table and the department dimension key is also pushed to the bidge department table. But wait a minute.....there is a drawback of this modeling solution. You can't see whether which location belongs to which department, unless a fact has happen. If a fact didn't happen you can't determine whether a location belongs to a department. So i needed a better solution than this.

The solution
So studying kimball's book : "the datawarehouse lifecycle toolkit, second edition" shows me a example on page 270, paragraph "many-values dimensions with bridge tables". In this example a account dimension and Customer dimension is used with a bridge table. A weighting factor can help adding facts to the right customer. But i'm my situation i don't know how the facts are related to the department. Sometimes 99% of the facts belongs to the departments and sometimes less facts and this is not sure.  So in my case a proper modeling solution is this :

In this case it's possible to determine which location belongs to which department.

Kimball recommends adding a valid_from and a valid_to column to the bridge table (as i already explained in the 3NF example) because a relation can exist at a certain point in time and they can no longer exist at some moment.

Next time i'll explain how to build this in SSAS.

Greetz,
Hennie