Datawarehouse - How to Link dimensions

Question

Hi Guys, Just got into datawarehousing and need your help to clarify a confusion. Lets say I have Employee dimension and Department Dimension. If I have a report that requires me to list fields from dimEmployee (Name, Salary, Position) and fields from department (DeptNo, Desc, Manager), how do I do that. Do I create a fact table(factless) that will be a joining table between these two dimensions ? Or do I need to design these two tables differently. Everyone talks about facts and dimension, but do we even considering linking dimension tables at all ?

Thanks for your insights.

RK

Answer 1

When you talk about Dimension and fact tables you usually refer to aggregated data. you may write a report that talks about count of employees per Department.

it sounds like you are dealing with lists of data. This may be better achieved using direct SQL. so something like

 SELECT person.Name, person.Salary, Person.Position,
 Department.DeptNo, Department.Desc, Department.Manager
 From person
  join Department on person.DeptId = Department.Id

Answer 2

There would have to be a relation between employee and department. This is typically done by adding a column DepartmentId to the Employee table.

Answer 3

Typical dimensions for any problem include Time (always), geography (e.g., Department, Territory, State, etc.), etc. In your case it sounds like salary ranges in a company ladder, positions, etc. might be pertinent.

Fact tables usually contain things that are additive (e.g., number of individuals within a given salary range, position, etc.).

I'm not sure that Employee would qualify as a dimension; sounds more like a fact to me.

Answer 4

Thank you all for your responses. So 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., the design may look like this

dimEmployee
EmpId
Dept
Name
etc...

dimDept
DeptId
Desc
Manager
etc....

fctEmpDept
empId
DeptId
Salary

When I read about datawarehouse, I see only dimensions and fact tables. I was really confused about how the tables will be structured if there are requirements to show lists for normal reporting purposes. Apparantly, there could be some dimensions that may be linked with foreign keys.

Answer 5

If you keep your dimensions independent of each other no department reference in the person dimension, no person reference in the department dimension, an assignment fact table can act as the bridge between the two.

For example

Person Table
------------

PersonID
Forename
Surname
EffectiveFromDate
EffectiveToDate


Department Table
----------------

DepartmentID
DepartmentName

etc


AssignmentFact Table
--------------------

AssignmentID (primary key)
PersonID (foreign key to person table)
ManagerID (foreign key to person table)
DepartmentID (foreign key to department table)
Salary
CostCentre
EffectiveFromDate
EffectiveToDate.

So a change to a persons assignment such as a change in line manager or a promotion requires no change to the dimensions but only to the fact table which will provide a nice simple historical record of changes. When you have multiple fact tables sharing these dimensions keeping them simple will pay dividends. Try a simple implementation of both and give them a try and I'm sure you'll find this to be more natural.

Answer 6

Option A:If each employee can belong to one and only one department. I would add the departmentID to the fact table.

Option B: If an employee can belong to multiple departments, I would go with a third alternative, setup metric tables (or aggregated fact tables). You would then do the aggregation of your employee's by department and store the aggregated number in this new table.

Option C: You could add the departmentID to the employee table and that would get the job done, but you would then be setting up a hierarchy. You can do this, but your joins get difficult and less efficient as your dataset grows.

I would go with one of the first 2 options, depending on your employee/department relationship.

I will admit there are other solutions available, but these would be your best bet.