The Unity Dimension

A saner solution for when differing entites share a role

I recently designed an enterprise data warehouse for a large food manufacturer. As I began my analysis and executive interviews, I realized that my preconceptions of the necessary dimensions for the model were off target. Having worked exclusively for other types of manufacturers in the past, I was unaware of the significant role logistics plays in the order fulfillment cycle for food makers.

This company tracks a complex set of movements among various entities. Several complex scenarios involving products and multiproduct display packages affect subcontractors, distribution centers, and customers: recalls, intercustomer transfers, and so on.

Following Orders

Operationally, the business creates orders to effect and track these different movements: transfer work, sales, and return orders.

But this aspect served only to muddle the problem. To support logistics, the model had to consider every movement regardless of the document that initiated it. Clearly, a single fact table had to record all inventory movements, yet with all the different possible origins and destinations, implementation was going to be challenging.

The enterprise model needed to treat customers, vendors, plants, and storage locations (distribution centers and warehouses) as distinct dimensions. These dimensions obviously represented significant business entities and were needed for other subject areas, such as revenue, cost, profitability, and production. The inventory movement fact needed to record the origin and destination of the movement.

Stating the Obvious

There are two obvious solutions. The first is to have eight foreign keys (FK): four for origin of customer, vendor, plant, and location; and four for each possible destination. The appropriate keys would be populated and the remaining six would be null. This solution fits into a dimensional modeling framework, but a query wanting to see all movements would challenge even a SQL savant.

The second obvious solution is to store two keys paired with a type code indicating to which dimension the key should be joined. Unfortunately, this method violates just about every rule we hold sacred in relational design and messes up the dimensional model as well. And looking at all movements joined with the proper dimension would take a SELECT UNION query with 16 separate SELECT clauses.

The obvious solutions are not solutions! A unity dimension is.

A Variant of Variant

A unity dimension is a spin on the variant dimension. I'll use Ralph Kimball's example of an Account dimension in a banking data warehouse. Such an application contains many types of accounts with widely varying attributes. Rather than a single dimension table with all the possible attributes for every type of account, create a primary dimension table with a common set of base attributes and multiple variant dimension tables containing unique attributes for each type of account.

Each variant table contains rows for certain account types. However, the variant and primary rows use the same surrogate key. This setup lets you store a single foreign key in the fact table that you can join to the primary dimension table, the variant table, or both, depending on query requirements.

The variant dimension addresses the problem of having a common entity with differing attributes. A unity dimension is the opposite, involving different entities that share a common role.