Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

How to handle parent child dimensions with SCDs

Go down

How to handle parent child dimensions with SCDs  Empty How to handle parent child dimensions with SCDs

Post  VJ09 Mon Jul 09, 2012 1:47 pm

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


Posts : 11
Join date : 2012-07-02

Back to top Go down

Back to top

- Similar topics

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