How to handle parent child dimensions with SCDs

View previous topic View next topic Go down

How to handle parent child dimensions with SCDs

Post  VJ09 on 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...!

VJ09

Posts : 11
Join date : 2012-07-02

View user profile

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