ExamFinch.com My Study

Study guide

1.2 Dimensionality Analysis & Slowly Changing Dimensions (SCD)

Practice this exam

QSDA Qlik Sense Data Architect Study Guide Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction: About the QSDA Exam

About the QSDA Exam: Current Facts, Objectives & How to Use This Guide

2 Chapter 1: Business Requirements, Data Granularity & Security

1.1 Business Requirements, KPI Formulation & Data Granularity 1.2 Dimensionality Analysis & Slowly Changing Dimensions (SCD) 1.3 Data Security Architecture & Section Access Implementation

3 Chapter 2: Data Connectivity & Source Integration

2.1 Relational Databases, ODBC/OLE DB & Cloud Data Warehouses 2.2 REST API Connectors, Pagination Strategies & Web Data 2.3 Data Connections, lib:// Pathing & Qlik Cloud Space Governance

4 Chapter 3: Dimensional Modeling & Schema Architecture

3.1 Dimensional Modeling: Star Schema vs. Snowflake in Qlik Sense 3.2 Multi-Fact Modeling: Fact Concatenation vs. Link Tables 3.3 Resolving Synthetic Keys ($Syn) and Circular References

5 Chapter 4: QIX Engine Architecture & Key Performance Optimization

4.1 The QIX In-Memory Associative Engine: Symbol Tables & Data Tables 4.2 Composite Keys, Cardinality & AutoNumber Memory Optimization 4.3 Field Scoping, QUALIFY / UNQUALIFY & Semantic Naming

6 Chapter 5: Load Scripting Fundamentals & Table Operations

5.1 Script Structure, Preceding Load & Resident Operations 5.2 Merging Tables: Joins, Keeps & Concatenation Semantics 5.3 Mapping Tables: ApplyMap vs. Lookup & Join Performance 5.4 Advanced Table Reshaping: Crosstable, Generic & Inter-Record Functions (Peek/Previous) 5.5 Script Documentation, Organization & Reusable Include Files

7 Chapter 6: QVD Architecture, Incremental Loads & Temporal Logic

6.1 QVD File Architecture: Optimized vs. Unoptimized Loads 6.2 Multi-Tier QVD Architecture: Extract, Transform & Presentation Layers 6.3 Incremental Load Patterns: Insert, Update & Delete Strategies 6.4 Master Calendars, Canonical Dates & Temporal Logic

8 Chapter 7: Advanced Transformations, Data Cleansing & Validation

7.1 IntervalMatch & Hierarchy Modeling (Hierarchy & HierarchyBelongsTo) 7.2 Data Cleansing, Null Handling & Script Control Flow 7.3 Data Model Validation, Source Reconciliation & Subset Ratios 7.4 Script Debugging, Trace Logging & Security Auditing

1.2 Dimensionality Analysis & Slowly Changing Dimensions (SCD)

Key Takeaways Explain This Topic AI

Dimensional analysis evaluates attribute cardinality, hierarchy depth, and temporal volatility to prevent memory bloat and model ambiguity in the QIX engine.

SCD Type 1 overwrites historical values, providing a simple and memory-efficient structure that permanently maps historical metrics to current attribute assignments.

SCD Type 2 maintains complete historical accuracy using effective date intervals (ValidFrom/ValidTo) or surrogate keys, capturing attribute state at the exact time of transaction.

SCD Type 3 preserves limited history by maintaining both current and previous attribute columns within the dimension row, enabling dual-perspective reporting without row proliferation.

Temporal joins in Qlik Sense using IntervalMatch must be flattened into bridge or fact tables to eliminate synthetic keys and prevent Cartesian multiplication.

Last updated: October 2026

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:

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).

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.

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).

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

  • Feature SCD Type 1 (Overwrite) SCD Type 2 (Versioning) SCD Type 3 (Prior Column)
  • Historical Integrity None (erased) Complete (unlimited history) Limited (current + 1 prior state)

Dimension Row Count Stays 1:1 with natural key Expands ( 1 + N 1 + N 1 + N versions) Stays 1:1 with natural key

Link to Fact Table Natural Key ( CustomerID ) Surrogate Key or Natural Key + Interval Natural Key ( CustomerID )

  • QIX Engine Complexity Lowest Highest (requires IntervalMatch or compound keys) Moderate
  • Memory Footprint Smallest Larger (more symbols and pointers) Slightly wider row width
  • Analytical Reporting Pure "As-Is" only Pure "As-Was" and "As-Is" Side-by-side "Current vs. Prior"
  • 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

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.

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:

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").

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

Warning

Pitfall 1: Nulls in IntervalMatch Boundaries Forgetting that Qlik Sense IntervalMatch cannot evaluate NULL in upper or lower bounds. If current records have NULL in ValidTo , those transactions fail to associate and disappear from historical reporting. Always apply Coalesce() or Alt() to provide a default future boundary.

Warning

Pitfall 2: Creating Synthetic Keys with Unflattened Bridge Tables Retaining the standalone IntervalMatch bridge table alongside the fact and dimension tables. This leaves multiple shared key fields ( ValidFrom , ValidTo , NaturalKey ), generating complex synthetic keys ( $Syn ) that slow hypercube computations.

Warning

Pitfall 3: Overwriting Attributes When Audits Require Historical State Implementing SCD Type 1 when business rules mandate compliance or commission auditing. Once overwritten, prior historical state cannot be recovered without restoring database backups.

Warning

Pitfall 4: Misinterpreting SCD Type 3 Scope Assuming SCD Type 3 can track multiple historical transitions. SCD Type 3 stores only the current and immediate previous value; any third change overwrites the previous value, destroying intermediate history.