Datawarehouse Design Question

general discussion
1

Datawarehouse Design Question

Hi GUys

I have a data modelling challenge and request some of your thoughts…

The source system has two employee tables…one to hold SCD Type 1 information and the other to hold Type 2…

The two tables are:

Employee (with lots of attributes like Name, address, phone, email, etc that are SCD Type 1)
Employee History ( with lots of attributes like cost centre, manager, etc that are subject to change - SCD Type 2)

There is a one-many relationship between Employee and Employee History

Employee has Employee_ID as the primary key
Employee_History has a composite key as a primary key (Employee ID + ChangeDate) where ChangeDate is the date on which a new row has been added to the table for reasons such as manager change, cost center change, etc.

I have a fact_sales table with Foreign keys, Qty sold, Sales amt, etc

The design challenges are:

  1. As far as I am aware, a fact table cannot join on a dimension that is made up of composite keys so I have to change the Employee_History table. But how do I do that ensuring that I am still able to query all the rows in the Employee_history table through the fact table? I should not end up with one row in the fact table relating to multiple rows in the Employee_history table

  2. There is a reporting requirement on Total Sales by Employee Cost Center. I can do that for employee’s current cost center. However if employee changes cost centre, the report should show his earlier cost center. This again is not possible due to point 1.

  3. How do I uniquely identify one employee so as to maintain his entire history irrespective of whatever query?

Any thoughts on how to model this?

Thanks
Rajesh

zigzag :us: (BOB member since 2009-09-13)

2

Please do not cross-post. It leads to fragmented discussions and is against our forum rules.

The same question posted here:

This topic is locked now.

Marek Chladny :slovakia: (BOB member since 2003-11-27)