[b]Need Help on Employee Data Mart Design[/b]

View previous topic View next topic Go down

[b]Need Help on Employee Data Mart Design[/b]

Post  Ajith on Fri Feb 18, 2011 11:30 am

I am new to dimension modeling and want to implement an employee data mart for my company. I modeled it based on the article http://www.kimballgroup.com/html/articles_search/articles1998/9802d05.html .

I request the members to give their valuable advice on the design. Will I be able to meet the requirements with this design?

Requirements
1. We want to track any changes to an employee’s organization, position, grade, nationality, qualification, etc..
2. Shall be able report on employee head counts for appointments, separations, transfer in, transfer out for each snap shot month on combinations of various dimensions like Organization , Nationality, Gender, Position, Grade, Qualification, Location, Age, etc
3. Salary, OT paid etc on combinations of various dimensions like Organization , Nationality, Gender, Position, Grade, Qualification, Location, Age, etc


The table structures are

Dimensions

Employee (SCD2)
1. Employee_Transaction_Id
2. Employee Number
3. Transaction Date
4. Transaction End Date
5. Transaction Description
6. Last Transaction Flag
7. Name
8. Date of Birth
9. Date of Joining
10. Date of Termination
11. Organization (Type-2)
12. Nationality (Type-2)
13. Position (Type-2)
14. Grade (Type-2)
15. Employee Type (Type-2)
16. Qualification (Type-2)
17. etc…


Organizations
1. Organization_Id
2. Organization_Name
3. Department Name
4. Directorate Name

Nationality
1. Nationality_Id
2. Nationality
3. Nationality Regional Group
4. Nationality International Group

Qualification
1. Qualification Id
2. Qualification
3. Qualification Group

Positions
1. Position Id
2. Position Name
3. Job Group

Date
1. Date_id
2. Date
3. Month
4. ….

Etc…

Facts

Employee Monthly Snap Shot
1. Employee_Transaction_Id
2. Date_Id
3. Position Id
4. Qualification Id
4. Nationality_Id
5. Organization_Id
6. Employee Type_Id
7. Age Bracket Id
8. Salary Components +
9. Repayment Components+
10. etc….


Thanks & Best Regards
Ajith

Ajith

Posts : 1
Join date : 2011-02-18

View user profile

Back to top Go down

Need Help on Employee Data Mart Design

Post  RoyalWulf on Wed Aug 03, 2011 12:42 am

Does anybody have any thoughts on this design? We were looking at implementing the same design.

Thanks,
John

RoyalWulf

Posts : 9
Join date : 2010-04-18

View user profile

Back to top Go down

Re: [b]Need Help on Employee Data Mart Design[/b]

Post  hang on Wed Aug 03, 2011 3:13 am

Transaction dimension is like a SCD2 dimension with capability of a factless fact table in a way that it stores the historical relationship between dimensions/attributes. It can be as deep as big fact tables, as a result of some fast changing attributes, so you should normalise (snowflake) it just as you do with fact tables. So basically it can have many FKs to other dimensions as well as high cardinality textual attributes because it is a dimension.

Let's go back to the Employee Transaction dimension in the initial post. Those Type-2 attributes are really separate dimension tables, therefore you would have it as follows:

Employee (SCD2)
1. Employee_Transaction_Id
2. Employee Number
3. Transaction Date
4. Transaction End Date
5. Transaction Description
6. Last Transaction Flag
7. Name
8. Date of Birth
9. Date of Joining
10. Date of Termination
11. Organization Key (Type-2)
12. Nationality Key (Type-2)
13. Position Key (Type-2)
14. Grade Key(Type-2)
15. Employee Type key (Type-2)
16. Qualification Key(Type-2)
17. etc…

Since the changes have been tracked by the transaction dimension, and in the snapshot fact, the Employee_Transaction_Id (FK) has carried all the correlations at the snapshot date, therefore you don’t need to include all those FKs covered by the transaction dimension. Otherwise you would potentially end up with conflicting correlations through two entries. So just store the necessary FKs and measures in the fact table as follows:

Employee Monthly Snap Shot
1. Employee_Transaction_Id
2. Date_Id
7. Age Bracket Id
8. Salary Components +
9. Repayment Components+
10. etc….

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: [b]Need Help on Employee Data Mart Design[/b]

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