Designing Mini-Dimensions for Rapidly Changing Attributes

Hello! In the previous lesson, one billing event could have several diagnoses, so you kept one fact row and represented the multiple associations through a bridge. This lesson addresses a different cardinality problem: a customer has one profile at the time of a sale, but that profile may change so often that preserving every change in a large customer dimension becomes costly.

You will design a reusable mini-dimension for those changing attributes, attach the correct profile to each fact, and distinguish history observed at transactions from the complete history of profile changes.

Why ordinary Type 2 history may grow too quickly

Suppose dim_customer holds ten million customers. You need to retain the customer’s marketing segment and income band as they stood when each purchase occurred. If you put both attributes in the customer dimension and apply SCD Type 2 whenever either changes, six changes per customer in a year could generate roughly sixty million new customer-version rows. The exact growth depends on actual changes, but the architectural issue is clear: a small set of volatile attributes repeatedly duplicates an otherwise stable, wide customer row.

A mini-dimension separates those volatile attributes into profiles. Rather than representing a particular customer, each mini-dimension row represents a distinct combination of attribute values. Customers can share a profile, and the same customer can have different profiles on different facts.

Watch the relevant portions of Dimensional Modeling – Declaring Dimensions from Pragmatic Works. The demonstration makes the crucial structural point: the mini-dimension connects directly to the fact, not merely to the customer dimension.

This is not a replacement for all SCD Type 2 processing. A customer’s relatively stable attributes may still need Type 2 versions. The mini-dimension targets the subset whose change frequency would make that versioning disproportionately expensive.

Give the profile its own grain

For a running example, suppose the selected volatile attributes are segment and income_band. You might have these mini-dimension rows:

The grain of dim_customer_profile is one row per distinct segment-and-income-band combination. It is not one row per customer and not one row per change event. If two customers have the Basic/Low profile, they can both reference key 11. If a customer leaves that profile and later returns to it, facts from both periods can reference key 11.

The fact retains its original grain—say, one row per completed sale—and stores both identities:

dim_customer
  customer_key             -- surrogate key
  source_customer_id       -- retained business identifier
  stable customer attributes

dim_customer_profile
  profile_key              -- surrogate key
  segment
  income_band              -- unique combination with segment

sales_fact
  sale_id                  -- one completed sale
  customer_key             -- who made the purchase
  profile_key              -- profile effective for this sale
  sale_date_key
  sales_amount

customer_key and profile_key answer different questions. The first identifies who purchased; the second records which profile applied to that purchase. The ETL lookup must resolve the profile that was effective at the event’s business time, not simply whichever profile is current when a delayed batch happens to load.

There is no need to add a row to dim_customer solely because its income band changes. Nor should you replace an existing fact’s profile_key when the customer changes later: doing so would restate what the earlier transaction meant.

A mini-dimension can superficially resemble the junk dimension you met earlier: both may contain combinations of low-cardinality attributes. Their purposes differ. Here the attributes describe an entity’s changing profile and have a time-sensitive relationship to that entity. A junk dimension groups otherwise homeless indicators associated with an event. And unlike the diagnosis bridge from the previous lesson, a sale ordinarily points to one profile key, not several simultaneously valid profiles.

Select attributes carefully, then manage combinations

A useful candidate for a mini-dimension changes frequently, matters for historical analysis, and can be expressed as a manageable number of profile combinations. For example, exact income might have millions of possible values; agreed bands such as Low, Middle, and High may serve the analytical question while allowing profiles to be reused.

Banding is a loss of precision, not a free optimization. If the business must later ask for the exact historical income reported at a sale, an income band cannot supply it. Likewise, changing the definition of “High” without versioning or documenting that definition can silently change the interpretation of earlier facts. Agree on the bands, boundary rules, and treatment of unknown values before loading history.

If there are four segment values and five income bands, at most twenty combinations are possible under those definitions. You need not materialize all twenty in advance: insert distinct combinations actually encountered, and give each a stable surrogate key. With many attributes, however, possible combinations multiply quickly. A mini-dimension with hundreds of thousands of mostly unique profiles may not solve the growth problem. Reconsider which attributes belong together, whether useful bands are possible, and whether a separate profile design is warranted.

Kimball Group’s Design Tip #127 Creating and Managing Mini-Dimensions, by Warren Thornthwaite, gives the practical counterpart to this design: building distinct profiles, maintaining new combinations, and resolving profile keys during fact loading.

There are two separate maintenance tasks here. Profile maintenance finds any previously unseen combination and creates its mini-dimension row exactly once. Fact loading looks up the applicable customer key and profile key for each incoming business event. A unique constraint on the profile attributes—or an equivalent normalized lookup rule when values can be unknown—helps prevent two keys from representing the same combination. Neither task requires a fresh profile row for every customer change if the destination combination already exists.

Two legitimate histories—and one gap

Consider this simplified customer history:

The two sales facts retain keys 11 and 13. A report can therefore say accurately that the January sale happened under Basic/Low and the March sale under Premium/High:

SELECT
    p.segment,
    SUM(f.sales_amount) AS sales_at_event_profile
FROM sales_fact AS f
JOIN dim_customer_profile AS p
  ON p.profile_key = f.profile_key
GROUP BY p.segment;

But the facts alone do not record that this customer was Basic/High from February 3 until March 15. Profile 12 may exist in the mini-dimension, yet nothing there identifies which customer held it or when. A dimension full of possible profiles is not a history of customer-to-profile assignments.

Decide which question the warehouse must support:

• “What profile applied when a sale occurred?” The profile foreign key on each sale is sufficient, assuming the source supplies the correct event-time profile.
• “What profile did each customer hold on February 20, including customers who bought nothing?” You need a separately maintained relationship history, often a factless fact table, with customer key, profile key, effective time, and expiration time. Each row states that the relationship was valid for a defined interval, such as from its effective timestamp up to—but not including—its expiration timestamp.
• “How many customers have each profile now?” You can maintain a current_profile_key on the current customer record as a Type 1 overwrite. That supports a current-state count, not reconstruction of past no-sale periods.

For the example, a relationship-history table would contain assignments to profiles 11, 12, and 13 with boundaries on January 10, February 3, and March 15. It would answer the February 20 question even though there was no sale. Its grain is one customer-profile assignment for one effective interval. Keep intervals for a customer non-overlapping and retain expired rows when historical queries require them.

A current-profile key has a different reporting use. If the customer is now Premium/High, a report might deliberately classify all their historical sales under that current segment. That is not the same measure as sales at event-time segment; label the two views accordingly. If your primary customer dimension also has Type 2 versions, a “current profile” report must identify the customer’s current row rather than assume an old fact’s customer-version key points to it.

Source-data limit: None of these tables can recover a change that was never captured. If a nightly source extract contains only the latest profile, an intermediate change made and reversed between extracts may be unknowable. When every effective-time change matters, arrange an appropriate source history or change feed as well as a place to store the relationship.

Closing perspective

A mini-dimension gives rapidly changing attributes a reusable, combination-level grain. Each transaction or snapshot fact holds both its customer key and the profile key applicable at that event, preserving event-time analysis without repeatedly copying a large customer row. Its limit is equally important: fact rows record profiles when facts occur, not every intervening change. Add a dated factless relationship history when the business needs point-in-time customer profiling independent of transactions; use a clearly labelled current-profile pointer only for current-state questions.

Next, you will move into PostgreSQL implementation, beginning with how a normalized source model becomes a star schema while its source-to-target key mappings remain traceable.