How to handle parent child dimensions with SCDs
Page 1 of 1 • Share •
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» We found this child molester on facebook, Dawn E. Tyler is really Dawn Marie Tyler!
» How to handle parent child dimensions with SCDs
» Parent-Child FactTabletechnique
» Multiple grain in a parent-child-child relationship
» Parent/Child tutorial?
» How to handle parent child dimensions with SCDs
» Parent-Child FactTabletechnique
» Multiple grain in a parent-child-child relationship
» Parent/Child tutorial?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum