1.2 Dimensionality Analysis & Slowly Changing Dimensions (SCD)

Dimensionality Analysis & Slowly Changing Dimensions (SCD)

In analytical systems, dimensions provide the business context for evaluating quantitative facts. However, business dimensions are rarely static: sales representatives transfer between sales territories, customers relocate to new postal codes, product categories are restructured, and cost centers are reorganized. Handling these temporal shifts requires understanding Slowly Changing Dimensions (SCD).

For a Qlik Sense Data Architect, choosing the proper SCD strategy directly impacts data consistency, historical auditability, and in-memory engine performance.

---

1. Dimensionality Analysis in Qlik Sense

Before implementing dimension tables, architects conduct dimensional analysis across four fundamental vectors:

1. Attribute Cardinality: The volume of unique values within a field. High-cardinality attributes (e.g., customer account numbers, serial identifiers) demand more symbol table memory and widen data table pointer bit-widths than low-cardinality attributes (e.g., country codes, marital status).
2. Hierarchical Relationships: Determining whether dimension attributes form fixed, balanced hierarchies (e.g., Year -> Quarter -> Month -> Day or Category -> SubCategory -> SKU) or ragged/parent-child hierarchies (e.g., managerial organizational charts). Fixed hierarchies are flattened into denormalized dimensions for maximum Qlik query speed.
3. Temporal Volatility: The frequency with which attribute values change over time:
   • Static Attributes: Date of birth, original order placement date.
   • Slowly Changing Attributes: Employee department, client address, product sales territory (changes infrequently over months or years).
   • Rapidly Changing Attributes: Real-time GPS coordinates, stock market bid prices (requires separate transactional or mini-dimension modeling).
4. Associative Linkage: Evaluating whether a dimension connects to a single fact table or acts as a conformed dimension shared across multiple facts (e.g., a shared Customer dimension linking both Sales and Support Tickets).

---

2. The Core Analytical Dilemma: As-Was vs. As-Is

When an attribute changes, business users require one of two reporting perspectives:

Historical Sales Event (March 2025: Rep Sarah in "Midwest")
                      │
         Rep Transfers to "West" (July 2025)
                      │
         ┌────────────┴────────────┐
         ▼                         ▼
  "As-Was" Reporting         "As-Is" Reporting
(Historical Truth)          (Current Reassignment)
March sale stays in Midwest  March sale re-credited to West
Used for commissions & audits Used for territory quota planning

• "As-Was" (Historical Reality): Transactions that occurred prior to the transfer remain credited to the Midwest territory. This perspective is essential for financial auditing, commission calculations, and operational compliance.
• "As-Is" (Current Reassignment): All historical transactions generated by Sarah are dynamically reassigned to her current West territory. This perspective is vital for organizational restructuring, trend benchmarking, and territory quota adjustments.

Supporting both perspectives requires selecting the appropriate Slowly Changing Dimension technique.

---

3. Slowly Changing Dimension Patterns: Types 1, 2, and 3

SCD Type 1: Overwrite
[CustID: 101 | Region: West]  ──► Update ──►  [CustID: 101 | Region: East]
(History erased)

SCD Type 2: Versioning / Intervals
[Key: 1 | CustID: 101 | Region: West | 2024-01-01 to 2025-06-30 | Active: 0]
[Key: 2 | CustID: 101 | Region: East | 2025-07-01 to 9999-12-31 | Active: 1]
(Full historical audit trail)

SCD Type 3: Current vs. Prior Columns
[CustID: 101 | CurrentRegion: East | PriorRegion: West | EffectiveDate: 2025-07-01]
(Dual perspective preserved; intermediate history lost)

SCD Type 1: Overwriting Historical Values

In an SCD Type 1 implementation, changes to dimension attributes overwrite existing values in place. No historical record is preserved.

• Mechanics: When Sarah moves from Midwest to West, the Territory field in her dimension record is simply updated from Midwest to West.
• Use Cases: Correcting data entry errors (e.g., fixing a misspelled customer name), updating non-critical descriptive details (e.g., contact phone number), or when business management explicitly mandates pure "As-Is" reporting.
• Advantages: Minimal memory usage, maintains a strict 1:1 relationship with the natural business key, simplest script logic.
• Disadvantages: Historical tracking is permanently lost. Rerunning a historical Q1 regional sales report will display different figures than the original published report.

SCD Type 2: Maintaining Full Historical Change Records

In an SCD Type 2 implementation, every attribute modification creates a new record in the dimension table. This preserves a complete, auditable timeline of all historical states.

• Mechanics: The dimension table includes a Surrogate Key (unique per version), the Natural Business Key, the descriptive attributes, validity date boundaries (ValidFrom / ValidTo or StartDate / EndDate), and an active status indicator (IsCurrent or ActiveFlag).
• Use Cases: Sales territory reorganizations, product category reclassifications, regulatory compliance tracking, and customer tier tracking.
• Advantages: Provides 100% historical accuracy; allows seamless "As-Was" reporting by linking facts to the surrogate key valid at the transaction timestamp.
• Disadvantages: Expands dimension row counts; introduces complexity when linking transactions that only possess the natural business key.

SCD Type 3: Preserving Limited History via Attribute Columns

In an SCD Type 3 implementation, limited historical tracking is achieved by adding dedicated columns to the dimension record to store the immediate predecessor value.

• Mechanics: The table retains columns such as CurrentTerritory, PriorTerritory, and TerritoryChangeDate. When a transfer occurs, the value in CurrentTerritory is moved to PriorTerritory, and CurrentTerritory is updated with the new value.
• Use Cases: Annual sales territory reorganizations where leadership wants to toggle between "Current Structure" and "Prior Year Structure" across the entire historical data set.
• Advantages: No row proliferation (maintains 1:1 natural key grain), avoids temporal joins, straightforward UI expressions.
• Disadvantages: Captures only one historical transition; subsequent changes overwrite intermediate history.

Comparative Architectural Matrix

---

4. Loading and Linking SCD Type 2 in Qlik Sense

Connecting transactional facts to an SCD Type 2 dimension in Qlik Sense depends on what keys exist in the source data.

Scenario A: Fact Table Already Contains the Dimension Surrogate Key

If the enterprise data warehouse (EDW) ETL pipeline has already resolved temporal validity and stamped each transaction row with the appropriate dimension surrogate key (e.g., DimSalesRepKey = 1042), modeling in Qlik is straightforward. The architect joins the tables directly on DimSalesRepKey in a pure star schema.

Scenario B: Fact Table Contains Only Natural Key and Transaction Date

In many operational architectures, transactional source systems only provide the natural business key (SalesRepID) and the transaction timestamp (OrderDate). The Qlik Data Architect must resolve the temporal relationship inside the Qlik load script using the IntervalMatch prefix.

Production Script Pattern: IntervalMatch with SCD Type 2

// Step 1: Load the SCD Type 2 Dimension Table
Dim_SalesRep:
LOAD
    SalesRepSurrogateKey,
    SalesRepID,            // Natural Key
    SalesRepName,
    TerritoryName,
    Floor(ValidFrom) as ValidFrom,
    // Ensure open-ended current records have an explicit future boundary
    Floor(Coalesce(ValidTo, MakeDate(9999, 12, 31))) as ValidTo,
    IsCurrentFlag
FROM [lib://DataFiles/Dim_SalesRep_SCD2.qvd] (qvd);

// Step 2: Load the Transactional Fact Table
Fact_Sales:
LOAD
    TransactionID,
    SalesRepID,            // Natural Key
    Floor(TransactionDate) as TransDate,
    SalesAmount
FROM [lib://DataFiles/Fact_Sales.qvd] (qvd);

// Step 3: Generate Temporal Bridge via IntervalMatch
// Maps TransDate between ValidFrom and ValidTo for each SalesRepID
Bridge_Interval:
IntervalMatch (TransDate, SalesRepID)
LOAD 
    ValidFrom, 
    ValidTo, 
    SalesRepID
RESIDENT Dim_SalesRep;

// Step 4: Eliminate Synthetic Keys by Flattening the Bridge
// Left join the Surrogate Key into the bridge table
LEFT JOIN (Bridge_Interval)
LOAD
    SalesRepSurrogateKey,
    SalesRepID,
    ValidFrom,
    ValidTo
RESIDENT Dim_SalesRep;

// Step 5: Join the Surrogate Key directly into Fact_Sales and cleanup
LEFT JOIN (Fact_Sales)
LOAD
    TransDate,
    SalesRepID,
    SalesRepSurrogateKey
RESIDENT Bridge_Interval;

// Drop temporary bridge and redundant interval fields from fact
DROP TABLE Bridge_Interval;
DROP FIELDS ValidFrom, ValidTo FROM Dim_SalesRep;

Architectural Breakdown of the Script

1. Boundary Handling: QIX IntervalMatch requires discrete numeric values for both lower and upper boundaries. If the source database represents the currently active record with a NULL in ValidTo, IntervalMatch will fail to match current transactions. Using Coalesce(ValidTo, MakeDate(9999, 12, 31)) guarantees all current transactions find an active interval.
2. Synthetic Key Elimination: Executing IntervalMatch (TransDate, SalesRepID) leaves a bridge table containing TransDate, SalesRepID, ValidFrom, and ValidTo. If left unflattened, Qlik creates a synthetic key ($Syn) between the fact, bridge, and dimension. Flattening the surrogate key into Fact_Sales and dropping the bridge establishes a high-speed, single-key star schema.

---

5. Associative Engine Risks: Temporal Joins & Synthetic Keys

When handling slowly changing dimensions, architects must guard against three primary architectural hazards:

1. Cartesian Interval Explosions

If ValidFrom and ValidTo intervals overlap for the same natural key in the dimension source data, IntervalMatch generates multiple matches for a single transaction date. When joined to facts, this duplicates transaction records, artificially multiplying sales revenue and margin metrics. Architects must audit dimension data for overlapping date ranges prior to executing IntervalMatch.

2. Rapidly Changing Dimension Bloat

If an attribute changes daily or weekly (e.g., customer credit scores, account balances), attempting to model it as an SCD Type 2 dimension will cause row counts to explode. In such cases, the volatile attribute should be separated into a distinct periodic snapshot fact table or modeled as a Junk / Mini-Dimension.

3. Date Disconnection from Master Calendar

When a model contains TransactionDate, ValidFrom, and ValidTo, linking multiple date fields directly to a single Master Calendar can create circular reference loops. Resolving the dimension surrogate key in the script (as shown above) allows TransactionDate to be the sole canonical link to the Master Calendar.

---

6. Enterprise Scenario: Global Logistics Fleet Reassignment

The Problem

A national trucking carrier operates 4,000 transport vehicles. Trucks are regularly reassigned among 15 regional hubs based on seasonal freight demand. A logistics analyst requires two core dashboards:

1. Maintenance & Fuel Auditing: Fuel expenses and maintenance costs must be assigned to the specific regional hub where the vehicle was stationed when the expense was incurred ("As-Was").
2. Asset Allocation & Fleet Planning: Leadership wants to view all historical maintenance costs under each truck's current hub assignment to evaluate regional asset wear ("As-Is").

The Architectural Solution

The architect designs an SCD Type 2 dimension with an embedded SCD Type 3 column:

Dim_Truck:
LOAD
    TruckSurrogateKey,     // Unique for each hub assignment interval
    TruckVIN,              // Natural business key
    HistoricalHubName,     // Valid during this interval ("As-Was")
    ValidFrom,
    ValidTo,
    IsCurrentFlag
FROM [lib://QVD/Dim_Truck_SCD2.qvd] (qvd);

// In a preceding load, add CurrentHubName to all rows for that VIN
LEFT JOIN (Dim_Truck)
LOAD
    TruckVIN,
    HistoricalHubName as CurrentHubName
RESIDENT Dim_Truck
WHERE IsCurrentFlag = 1;

By including both HistoricalHubName (tied to the interval surrogate key) and CurrentHubName (stamped across all versions of the natural key), the architect enables end users to toggle between "As-Was" and "As-Is" perspectives in visualizations with zero script reloads or circular loops.

---

7. Common Exam Pitfalls & Traps