Kimball University: Five Alternatives for Better Employee Dimension Modeling - InformationWeek
Software // Information Management
02:30 PM

Kimball University: Five Alternatives for Better Employee Dimension Modeling

The employee dimension presents one of the trickier challenges in data warehouse modeling. These five approaches ease the complication of designing and maintaining a 'Reports To' hierarchy for ever-changing reporting relationships and organizational structures.

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.
SELECT Manager.ManagerName, Employee.EmployeeName, SUM(FactTable.SomeFact) AS
FROM FactTable
INNER JOIN Employee -- standard dimensional join
ON (FactTable.EmployeeKey = Employee.EmployeeKey) INNER JOIN NKBridge -- needs a date constraint ON (Employee.PersonnelNum = Bridge.PersonnelNum
AND Fact.DateKey BETWEEN Bridge.RowStartDate and Bridge.RowEndDate)
INNER JOIN Manager -- needs a date constraint ON (Bridge.MgrPersonnelNum = Manager.MgrPersonnelNum
AND Fact.DateKey BETWEEN Manager.RowStartDate AND Manager.RowEndDate)
WHERE Manager.ManagerName = 'Name of specific person'
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.

2 of 3
Comment  | 
Print  | 
More Insights
Newest First  |  Oldest First  |  Threaded View
How Enterprises Are Attacking the IT Security Enterprise
How Enterprises Are Attacking the IT Security Enterprise
To learn more about what organizations are doing to tackle attacks and threats we surveyed a group of 300 IT and infosec professionals to find out what their biggest IT security challenges are and what they're doing to defend against today's threats. Download the report to see what they're saying.
Register for InformationWeek Newsletters
White Papers
Current Issue
2017 State of the Cloud Report
As the use of public cloud becomes a given, IT leaders must navigate the transition and advocate for management tools or architectures that allow them to realize the benefits they seek. Download this report to explore the issues and how to best leverage the cloud moving forward.
Twitter Feed
InformationWeek Radio
Archived InformationWeek Radio
Join us for a roundup of the top stories on for the week of November 6, 2016. We'll be talking with the editors and correspondents who brought you the top stories of the week to get the "story behind the story."
Sponsored Live Streaming Video
Everything You've Been Told About Mobility Is Wrong
Attend this video symposium with Sean Wisdom, Global Director of Mobility Solutions, and learn about how you can harness powerful new products to mobilize your business potential.
Flash Poll