Modeling Employee and Employee Role dimension.

Good Day,

I am currently working on a sales data mart for our out side sales force which will contain employee dimension, role dimension, appointment dimension and a fact table of appointment counts per day. I need help.

The scenario is that, we have a regular Employee database which contains core employee information:

Name
Date of birth
Hire date
department
position
position start date
position end date
Termination date
address
gender

etc

and we have an MS Access database use by the sales group to managed who is an outside sales person and who is their internal resources:

Name
Role
Start date
End date
Group
Group leader
etc

The sales department wants us to model these two dimensions as one and maintaining all historical movements. However, I want to model it as two separate dimensions and two mini dimensions.

Employee dimension will have core employee data and a mini dimension of historical type 2 SCD data. Role dimension with core role data and a mini dimension with type 2 SCD. Finally, I will create an outrigger to connect between employee and roles as a one to many relationship.

Please see if this is a good design logic.

Thanks
Amara

The mini dimensions can only serve as point in time information relative to the fact. If you need to provide current information, the attributes in the mini dimension should also appear in the main dimension (current values only).

I don't see why you would need the outrigger table.

Thanks for your reply.

If i get it correct, create just two separate dimensions and connect directly to the fact tables and only create mini dimensions for the rapidly changing attributes (i.e. roles, address, sales territory etc)?

Thanks