Location and department (n:m)

[hennie7863]

Hi,

My situation is this: i need to create a starmodel with locations and departments (n:m). multiple locations belong to a department and a department can have multiple locations. The grain of fact is on location level. A fact is at one location and not multiple.

I'm thinking about creating a bridge table for the departments and a normal dimension for the location. But the one thing i can not answer is which location belongs to a department (when there is no fact). How to solve this? Should i create a relation between location and department? That doesn't seem very kimball.

Gr,
Hennie

[hennie7863]

Okay as i already assumed. I need to create the bridge table variant.

Fact<-locationdim-> locationdepartmentbridge<-department

pg 203 kimball the datawarehouse toolkit second edition.

[ngalemmo]

If the department is related to the fact then it should be a dimension of the fact, along with location. Otherwise, the bridge, as you desribe it would need some means to allocate the measures of the fact to the multiple departments related to the location. It doesn't seem to me that is what you would want to do.

[hennie7863]

Location is the grain of the fact and location can be part of multiple departments. They want to add the measures at each department (in case of the same locations). Off course not preferable, as you said. I've tried to discourage the customer doing this, but they want it like this.

[Sponsored content]