Alternative 2: Bridge Table with Separate Reports To Dimension
Tracking the history of changes in a variable depth hierarchy such as an employee Reports To hierarchy is especially challenging when the hierarchy changes are intermingled with other Type 2 changes in the dimension. An obvious solution is to separate the Employee dimension from the Reports To relationship. Simplify Employee by removing the self-referencing relationship, and create a new Reports To dimension, as illustrated in Figure 3.
The key elements that distinguish this design from the classic structure are:
- Eliminate the surrogate key for manager from the Employee dimension, and hence the recursive foreign key relationship.
- The Reports To dimension has very few columns: surrogate keys, personnel numbers, and names. The only Type 2 attribute is possibly the Manager Position Number.
- If you're exclusively using OLAP to query the schema, the bridge table is unnecessary.
If the business users don't need to track changes in the full Reports To hierarchy, this solution works neatly. Employee is a Type 2 dimension. We see the name of each employee's manager. If Employee.ManagerName is managed as Type 2, we can easily see the names of all past bosses from the Employee dimension. If Reports To is managed as Type 1 -- we're not tracking changes in the reporting structure -- it is no more difficult to populate and maintain than in the classic dimension structure.
If the business users absolutely must see the history of the reporting relationship, this solution will be challenging. We've simplified the management problem by separating out the Reports To and Employee dimensions, but if we get a major organizational change we're still going to have to propagate a lot of new rows in both Reports To and the bridge table.
Alternative 3: Bridge Table with Natural Keys
In order to track changes in a Reports To hierarchy for anything other than trivial data volumes, we need a solution that does not use surrogate keys. The classic structure described in Figure 2 works fine at query time, but it's a maintenance challenge. Our natural key alternative is illustrated in Figure 4.
The key elements of this design relative to the classic structure of Alternative 1 are:
- Eliminate the surrogate key for manager from the Employee dimension, and hence the recursive foreign key relationship.
- Include the Employee dimension twice in the schema, once as the employee (linked directly to the fact table), and once as the manager (linked via the bridge table). The Manager dimension table is simply a database view of the Employee dimension.
- The bridge table is built on employee numbers -- the natural key carried in the source systems -- rather than the data warehouse surrogate keys. It's like the classic bridge table except that we need start and end dates to uniquely identify each row.
- The propagation of new rows in the bridge table is substantially fewer than before since new rows are added when reporting relationships change, not when any Type 2 employee attribute is modified (as in Figure 2). A bridge table built on natural keys is an order of magnitude easier to manage -- though still quite challenging.
A primary design goal is to be able to find all the fact rows associated with a manager and her entire organization, as the organization was structured at the time of the event measured in the fact table. This is a complicated query:
- From the Manager view of the Employee dimension, find the manager we're interested in.
- Join to the bridge table to find the personnel numbers and row dates for the employees in her organization.
- Join again to the Employee dimension to find the surrogate Employee Key for the people in the organization.
- Finally, join to the fact table to pick up all facts associated with these employees.
- The joins to the Bridge table and Manager view of Employee must constrain to pick up only the one row that's in effect as of the time of the fact transaction.
OrganizationalSum
FROM FactTable
INNER JOIN Employee -- standard dimensional join
AND Fact.DateKey BETWEEN Bridge.RowStartDate and Bridge.RowEndDate)
AND Fact.DateKey BETWEEN Manager.RowStartDate AND Manager.RowEndDate)
GROUP BY Manager.ManagerName, Employee.EmployeeName
The natural key bridge table approach is unwieldy. Its main advantage is that it's feasible to maintain. It also avoids breaking out the reporting relationship into a separate dimension, as in Alternative2. Any queries that don't involve the Reports To structure can drop the bridge table and Manager dimension view. Disadvantages include:
- Query performance is a concern as the queries are complex and the bridge table will grow quite large over time.
- The technique is not appropriate for broad ad hoc use. Only a tiny percentage of power users could ever hope to master the complex query structure.
- The technique relies on dynamic "date-bracketed" joins between the tables, and hence cannot be implemented in OLAP technology.
