Facts at different levels of hierarchy

[cridal]

Let's say I have an empoyee dimension consisting of the usual attributes describing a person, but also these two: Department and Company.
Let's also say I have budget numbers for the three levels (Employee, Department, and Company).

I would think that I need three different fact tables to store these facts at the appropriate grain: FactBudgetEmployee, FactBudgetDepartment, FactBudgetCompany.

So FactBudgetEmployee will be:
TimePeriodKey, EmployeeKey, BudgetAmount

That was pretty painless... What about the other two?

FactBudgetDepartment:
TimePeriodKey, Department, BudgetAmount

FactBudgetCompany:
TimePeriodKey, Company, BudgetAmount

As you see, Department and Company are just attributes on the employee dimension, not dimensions themselves (therefore not surrogate keys). Now both fact tables, which are supposed to consist of only surrogate keys and values, look quite ugly with textual attributes directly in them. I want to preserve the hierarchy in the Employee dimension and don't want to create separate dimensions for Department and Company. What's the appropriate course of action?

[ngalemmo]

As you see, Department and Company are just attributes on the employee dimension, not dimensions themselves

This is your basic problem. Department and Company need to be dimensions for this to work. Usually there is a catch-all dimension such as "organizational unit" which collect these various organizational entities allong with any hierarchies related to them. Department and Company would be roles within this dimension. This works well if all you have is an ID and description and maybe some other common attributes. If there is a wide disparity in attributes (such as with employee), they need to be their own dimension. You can still keep department and company attributes on the employee for other purposes.

The number of fact tables will depend on the number of dimensions you wind up with. It's possible to do it with one, but it requires abstracting employee which may be confusing.

[Jeff Smith]

I assume that the employee budget numbers don't add up to the Department and Company budget numbers. I think it's a ragged hierarchy.

A department's budget usually includes the employee budget figures as well as items not in the employee budget - office furniture, supplies, etc. This means that Department is a roll up from employee but is also a the lowest level of the hierarchy. I've handled this by creating a dimension with 4 levels - level 1 is the lowest level of the data and would contain Employee and Department. Users would never query this level. The second level would be Employee. At this level, the employee info would be blank for the department in Level 1. The 3rd level would be department and would be populated for every row.

This would enable a user to roll up data to Employee with the sum being the sum of all employees. At the next level - department, the sum would total everything.

It's a hard concept to grasp but I think there is some material available for ragged hierarchies. We used it at a large bank but it was cost center based with certain levels equating a department or division.

[Sponsored content]