Data Modelling
Part of BI data modelling
Handling changing customer attributes in analytical models
Choose when to overwrite customer details, retain dated versions or show a current grouping, and check how each choice affects historical BI reports.
Decide how a changed customer attribute affects earlier results before choosing a storage pattern. Overwrite an old value when reports classify all periods by the latest known value. Keep dated versions when reports must show the classification that applied to each event. Some reports need both views: separate labels plus a way to connect historical facts to current customer attributes.
Define the question behind the grouping
Suppose a customer moves from Small Business to Enterprise in July. A report of last March's sales by current segment would put those sales under Enterprise. A report by segment at the time of sale would put them under Small Business.
Both views can be useful. Label the fields “current segment” and “segment at sale” rather than offering one unexplained segment filter.
Choose treatment attribute by attribute. A corrected spelling or contact detail may replace its old value. Segment, territory or account manager may need history if past performance is analysed by the assignment then in force. Versioning every edit adds noise; overwriting every edit can erase needed context.
Separate the customer from each version
A business key identifies a customer across changes. A version key identifies one historical row for that customer. In a type 2 design, several rows can share the business key while holding different version keys, validity periods and attribute values. A fact that needs the historical view must reference the version chosen under the model's event-time rule.
| Reporting need | Possible treatment | Main limitation |
|---|---|---|
| Correct an error or use only the latest value | Update the attribute, often called type 1 | Earlier groupings may change when a report is rerun. |
| Preserve the value that applied to past events | Add a dated version, often called type 2 | Loading and fact-to-version matching need more care. |
| Group past activity by today's classification | Map the stable customer identity to a separately defined current-attribute view | It answers a different question from the historical version view. |
A current flag identifies the latest version, but filtering historical facts to current version keys alone can exclude facts tied to earlier versions. To show past activity under today's grouping while retaining type 2 facts, map their stable customer identity to the current attributes through a deliberately designed view or measure. Check that this mapping does not multiply fact rows.
Set the effective-time rule
For each tracked attribute, identify the change source, effective timestamp and timezone used to compare it with a fact event. A clear convention is to include the version start time and exclude its end time: an event at the next version's start belongs to the next version. Check for overlaps and unintended gaps for each customer.
Late information needs a decision. A source may report today that a segment change took effect last month. If facts should reflect the revised effective date, affected facts may need to be remapped and earlier reports restated.
Reporting what was known at each publication date is a different history requirement and may need additional recorded history. State which interpretation the model supports.
A source containing only the latest value cannot establish earlier customer states from that value alone. Mark the start of reliable history. When rebuilding a dimension, preserve its version-key mapping to facts; regenerating those keys during a full replacement can break existing references.
Check both views
Use an approved example of a customer whose attribute changed across a fixed period. Check that the historical view assigns events to the intended versions and the current view groups the customer's events under the latest approved value.
Include an unchanged customer and a late-reported change. Compare fact IDs and totals around the joins to catch duplicated or unmatched rows. Document the attribute rule, reliable-history start, effective-time convention, owner and any restated periods.
