Linking dimensions - reporting the list

[vermarajiv]

Guys, I see lot of talk about dimensions and facts(aggregations) and how they should be modelled. But in a datawarehouse, there are lots of requests to just display list of data from two different entities. How does that fit in the overall design. For eg I have two differen entities - Emp and Dept. If there is a requirement to find total employees in a dept in a certain salary range and also a requirement to list employees in a dept., I was thinking of a design that may look like this :


dimEmployee
EmpId
Dept
Name
etc...

dimDept
DeptId
Desc
Manager
etc....


fctEmpDept
empId
DeptId
Salary

But here, dept and Emp are linked with deptId in dimEmp table. Is that a valid design ?

 

[RiverGuy]

You don't really need a fact table at all unless you're wanting to store transactions of how much each person was paid per period, and then aggregate that up. If salary and department are attributes of Employee, then you can get by with doing a query on just the Employee dimension.

 

[vermarajiv]

Thanks RiverGuy for your response. If there is requirement to get salary per month(forgot to add date in fctEmpDept above), in that case, is this design ok?

 

[RiverGuy]

You'd need to add a foreign key to a date dimension in your fact table in that case. Other than that, it looks fine to me without knowing more about your requirements.

To get really technical, if an employee doesn't change departments very often, then you might have department as an attribute of the Employee dimension instead of it's own dimension. You would probably want to implement a Type-2 SCD in that case.

If, however, employees change departments very frequently, then two separate dimensions would be advisable.

 

[MDXer]

You Could Implement your employee Dimension as a parent child containing the department and manager attributes this would from a user standpoint handle an Type 2 change more neatly.