How to handle parent child dimensions with SCDs
Page 1 of 1
How to handle parent child dimensions with SCDs
I am having hard time to model a parent-child dimension which is a type 2 scd.
I have dimensions Employee, Position, Department, Company. The employee is related to the other three dimensions in a many to one fashion.
An employee can only have one position, belong to one department and one company. All the relations should be captured as type2 with a startdate and enddate for the employee_position, employee_department, employee_company.
What I did was I made a DimEmployee table (HRID as pk) with all the personal details of employee (about 40 columns). Then I had a DimEmployee_association table (EmpID as pk, HRID as fk) with employeeposition, emp_positionstartdate, emp_positionenddate, departmentname, emp_departmentstartdate, emp_departmentenddate, companyname, emp_companystartdate, emp_companyenddate.
I used the EmpID to connect to the fact table (I know its a snow flake but I feel this might be needed in this scenario)
Please tell me if this works and it is OK to put all those type 2 SCDs like this in the same dimEmp_assosciation table?
The bigger question is how to make this employee_position as a parent-child dimenison. I am thinking of having a parent_EmpID that self refrences to the EmpID in the DimEmp_association table but I feel that might become a problem.. I am not even sure if I am in the right direction.
kindly help me with this...!
I have dimensions Employee, Position, Department, Company. The employee is related to the other three dimensions in a many to one fashion.
An employee can only have one position, belong to one department and one company. All the relations should be captured as type2 with a startdate and enddate for the employee_position, employee_department, employee_company.
What I did was I made a DimEmployee table (HRID as pk) with all the personal details of employee (about 40 columns). Then I had a DimEmployee_association table (EmpID as pk, HRID as fk) with employeeposition, emp_positionstartdate, emp_positionenddate, departmentname, emp_departmentstartdate, emp_departmentenddate, companyname, emp_companystartdate, emp_companyenddate.
I used the EmpID to connect to the fact table (I know its a snow flake but I feel this might be needed in this scenario)
Please tell me if this works and it is OK to put all those type 2 SCDs like this in the same dimEmp_assosciation table?
The bigger question is how to make this employee_position as a parent-child dimenison. I am thinking of having a parent_EmpID that self refrences to the EmpID in the DimEmp_association table but I feel that might become a problem.. I am not even sure if I am in the right direction.
kindly help me with this...!
VJ09- Posts : 11
Join date : 2012-07-02
Similar topics
» SCDs - Do they belong in the EDW? Or do they belong only on dimensions in the data mart?
» Advice on relationships between Type 2 SCDs ?
» Dealing with multiple many to many related type2 SCDs
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Question about Dimensions with SKeys to join with other dimensions.
» Advice on relationships between Type 2 SCDs ?
» Dealing with multiple many to many related type2 SCDs
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Question about Dimensions with SKeys to join with other dimensions.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum