Type 5: Add Mini-Dimension and Type 1 Outrigger

Type 5 builds directly upon the Kimball Type 4 Mini-Dimension. It combines the historical tracking of a Mini-Dimension with a Type 1 outrigger (a foreign key from the Base Dimension to the Mini-Dimension). The numbering serves as a mnemonic: Type 5 = Type 4 + Type 1.

NOTE

An outrigger is a dimension table that is referenced by another dimension table rather than directly by the fact table.

This approach solves a specific reporting problem. In a pure Type 4 setup, finding an entity's current profile requires querying the large historical Fact table to determine their most recent profile. This makes queries that need to filter or group entities by their current profile unnecessarily complex. For example, generating a simple list of "all currently high-income customers" would require determining each customer's latest profile first.

To fix this, Type 5 adds a Current Mini-Dimension Key directly into the Base Dimension. Whenever the current profile changes, this reference is updated in place following the Type 1 approach. This dual design gives you the best of both worlds. Analysts can effortlessly filter entities by their current profile using just the dimension tables, while still preserving historical accuracy in the Fact table.

Example (Type 5)

Alice just aged into a new demographic bracket (Profile Y).

Base Dimension (dim_customer):

Her base record is updated in place (Type 1) to point to her new, current profile.

base_customer_key	customer_id	name	current_mini_demographic_key (Type 1)
101	C-101	Alice	Profile Y (Age 30-39, High Income)

Fact Table (fact_sales):

Her past purchases remain unchanged, preserving the demographics she had at the time of the sale.

fact_sales_id	base_customer_key	mini_demographic_key (Historical)	amount
9901	101	Profile X (Age 20-29, High Income)	$50.00