Department or Organization/Company

Hi,

The DW that we're building will need to hold data for serveral different companies, all of which have different departments.

I'm not sure whether to have an Organization dimension, as Kimball shows in Ch7 of the DW toolkit, or whether to have seperate Department and Company dimensions.

I generally name a dimension at is most granular level, which would be Department. But the trouble with that is that Company is so key for the users that they may not realise that they have to go to the Department dimenson to start analysing by Company. The reverse is true also if I callled the dim Company, then they may not realise that they have to go to the Company dimension in order to start analysing by Department.

I'm curious to know what other people have done in this scenario?

Thank you

You have three options:

1. Two dimension tables with two FK's on the fact.

2. One dimension table whose NK is company/department and replicate the company information on the rows.

3. Implement a hierarchy with one dimension table contains rows for companies and departments with a bridge table to allow rollups.