A slowly changing dimension (SCD) is the data warehousing pattern for handling attribute changes in dimension tables over time — managing the tension between the need to see historical data in the context of how it was classified at the time (what account type was this account when that transaction was posted?) and the need to see all data in the context of current classifications (what does our total OPEX look like under the current account structure?). In finance BI, this tension is omnipresent: accounts are reclassified, cost centres are restructured, entities are renamed, legal entity hierarchies are reorganised. The SCD design determines whether those changes corrupt historical comparisons or are managed transparently.
How It Works
Three SCD types cover the majority of finance dimension attribute scenarios:
| SCD Type | Behaviour | History Preserved? | Finance Use Case |
|---|---|---|---|
| Type 1 | Overwrite the existing value | No | Corrections to errors (wrong account code, typo in name) — when the old value was wrong, not just different |
| Type 2 | Insert a new row with the new value; mark the old row as expired | Yes — via separate dimension rows | Accounting reclassifications, cost centre restructuring, entity reporting line changes — when historical transactions should be reported under the old classification |
| Type 3 | Add a new column for the new value; retain the old value in the original column | Yes — side by side in same row | Parallel reporting structures (current vs prior year account mapping) — when both structures are needed simultaneously |
Type 2 is the most common and most complex: each change to a tracked attribute creates a new surrogate key, and fact rows retain the surrogate key of the dimension version active at the time of the transaction. BI queries using the current dimension see current-classification totals; queries using date-bounded dimension versions see historically accurate totals.
Design Decisions and Trade-offs
The SCD strategy must be decided at the attribute level, not at the table level. Within the Account dimension, the account code and description may be Type 1 (corrections only), the management reporting line may be Type 2 (reclassifications affect historical comparisons), and the current year vs prior year mapping may be Type 3. Applying a single SCD type to an entire dimension is a simplification that produces incorrect historical analysis for the attributes where a different type was appropriate.
Common Implementation Errors
The most specific SCD implementation error in finance BI is applying Type 1 to management reporting hierarchy assignments because “it’s simpler” — overwriting the old hierarchy assignment when an account is moved to a different reporting line. The consequence is that all historical fact rows referencing that account now aggregate under the new reporting line, even for periods before the reclassification occurred. A year-over-year comparison that should show a like-for-like performance comparison instead reflects the reclassification, making prior year figures incomparable to current year. The finance team attributes the discrepancy to a data error and raises a support ticket; the root cause is an SCD Type 1 applied to an attribute that required Type 2.
How Loop Wise Solutions Designs for This
We produce an SCD attribute register for each dimension table — listing every tracked attribute, its SCD type, the rationale for that type, and the ETL mechanism used to detect and process changes. The register is reviewed with the finance team before ETL development begins, because the correct SCD type for management reporting hierarchy attributes is a finance reporting governance decision, not a technical choice.
