Modeling Employee and Employee Role dimension.

View previous topic View next topic Go down

Modeling Employee and Employee Role dimension.

Post  amnist on Sun Nov 06, 2011 8:00 pm

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

amnist

Posts : 2
Join date : 2011-11-06

View user profile

Back to top Go down

Re: Modeling Employee and Employee Role dimension.

Post  ngalemmo on Tue Nov 08, 2011 12:04 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Modeling Employee and Employee Role dimension.

Post  amnist on Tue Nov 08, 2011 3:56 pm

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

amnist

Posts : 2
Join date : 2011-11-06

View user profile

Back to top Go down

Re: Modeling Employee and Employee Role dimension.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum