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

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

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

Thanks,
John

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….