--- Brand: klarmetrics.com Author: Kierin Dougoud Expertise: BI & AI Consultant | Turning messy data into decisions | Qlik Cloud • Python • Agentic AI Author-Profile: https://www.linkedin.com/in/mkierin/ Canonical-URL: https://klarmetrics.com/13-qlik-scd-implementation/ --- # Slowly Changing Dimensions Type 2 in Qlik Sense: Complete Implementation Guide *This is Article 13 of the [Qlik Sense Data Modeling Course](https://klarmetrics.com/qlik-sense-data-modeling-course/).* # 📚 Qlik Sense Course – Article 13 of 28 ← **Previous Article:** [Link Tables for Many-to-Many Relationships](12-qlik-link-tables) → **Next Article:** [Temporal Data & IntervalMatch](14-qlik-temporal-data) **What is SCD Type 2?** A method for historizing dimension changes. When a customer switches regions, the old version is kept and a new one is created – keeping historical analyses accurate! The concept of [Slowly Changing Dimensions on Wikipedia](https://en.wikipedia.org/wiki/Slowly_changing_dimension) provides the theoretical foundation for this approach. # What will you learn about Slowly Changing Dimensions Type 2 in Qlik? After this guide you will be able to: * Implement SCD Type 2 dimensions in Qlik * Perform point-in-time analyses (link sales to the region that was current at the time of the sale) * Track and analyze historical changes **Time investment:** 30 min reading + 4 hrs hands-on **Prerequisite:** Knowledge of [Fact vs Dimension Design](11-qlik-fact-dimension-design) **Quick win:** In 20 minutes you will understand the concept and be able to create your first SCD2 dimensions # How do dimensions change in Slowly Changing Dimensions Type 2? **The problem:** Your customer “Acme Corp” was in the “North” region from 2020-2022. Starting July 2022, they moved to “South.” When you analyze sales from 2021 – which region should be displayed? The current one (South, wrong!) or the one at the time (North, correct!)? **Without SCD2 (default):** Customer: CustomerID | Name | Region CUST001 | Acme Corp | South ← Current, overwrites old region Sales 2021 → Region "South" (WRONG! Was "North" at the time) **The solution: SCD Type 2** Create a new version of the record, keep the old one. This way you can assign historical sales to the correct (original!) region. **With SCD2:** Customer_SCD2: SurrogateKey | CustomerID | Name | Region | StartDate | EndDate | IsCurrent 1 | CUST001 | Acme Corp | North | 2020-01-01 | 2022-06-30 | 0 2 | CUST001 | Acme Corp | South | 2022-07-01 | NULL | 1 Sales 2021 → SurrogateKey 1 → Region "North" (CORRECT!) Sales 2023 → SurrogateKey 2 → Region "South" (CORRECT!) # How do you understand the structure of SCD Type 2? An SCD2 dimension has these mandatory fields: # What is a Surrogate Key (Technical Key) in Qlik? CustomerSurrogateKey: 1, 2, 3, 4, ... **Explanation:** Unique key per VERSION. Auto-increment. This key is referenced in fact tables! # How do you implement the Business Key in SCD Type 2? CustomerID: CUST001, CUST001, CUST002, ... **Explanation:** The “real” customer ID from the source system. Stays the same across all versions. # What are attributes that can change in SCD Type 2? CustomerName, Region, Segment, ... **Explanation:** The fields that can change and need to be historized. # What are the Effective Dates in SCD Type 2? EffectiveStartDate: 2022-07-01 EffectiveEndDate: NULL (= still current) or 2023-12-31 (= superseded) **Explanation:** When was this version valid? NULL for EndDate means: still currently valid. # What is the IsCurrent Flag in SCD Type 2? IsCurrent: 1 = current, 0 = historical **Explanation:** Quick access to the current version without date comparison. **Important:** Facts must reference the Surrogate Key, not the Business Key! This is the only way point-in-time works correctly. # How do you create an SCD2 Initial Load in Qlik? The first time, you load all customers as version 1: // Load source data Temp_Customers: LOAD CustomerID, CustomerName, Region, Segment, Date(ModifiedDate) as SourceModifiedDate FROM [DataCustomers.xlsx] (ooxml, embedded labels); **Explanation:** Standard load of source data. ModifiedDate is the date of the last change in the source system. // Build SCD2 structure Dim_Customer_SCD2: LOAD RowNo() as CustomerSurrogateKey, // 1, 2, 3, ... CustomerID, // Business Key CustomerName, Region, Segment, Date(SourceModifiedDate) as EffectiveStartDate, Date(Null()) as EffectiveEndDate, // NULL = current 1 as IsCurrent, // All initially current 1 as VersionNumber, // First version Hash128(CustomerName, Region, Segment) as AttributeHash // For later use RESIDENT Temp_Customers; **Explanation of fields:** * **RowNo():** Automatically creates 1, 2, 3, … as Surrogate Key * **EffectiveEndDate = Null():** No previous version yet, so all are current * **IsCurrent = 1:** All are initially the current version * **AttributeHash:** Combines all attributes into a hash – used later for change detection! DROP TABLE Temp_Customers; // Save SCD2 STORE Dim_Customer_SCD2 INTO [QVDDim_Customer_SCD2.qvd] (qvd); **Explanation:** The SCD2 dimension is saved as a QVD. On the next reload, it will be updated incrementally. # How do you update SCD2 with an Incremental Update in Qlik? On the next reload, you detect changes and create new versions: # How do you load the old SCD2 and new source data in Qlik? // 1. Load old SCD2 dimension Dim_Customer_SCD2_Old: LOAD * FROM [QVDDim_Customer_SCD2.qvd] (qvd); // 2. Load new source data Temp_Customers_New: LOAD CustomerID, CustomerName, Region, Segment, Date(ModifiedDate) as SourceModifiedDate, Hash128(CustomerName, Region, Segment) as AttributeHash // Same hash! FROM [DataCustomers.xlsx] (ooxml, embedded labels); **Explanation:** We load both the old SCD2 and the new source data. The hash is calculated again – same formula as in the initial load! # How do you identify changes with hash comparison in Qlik? // 3. Find changes through hash comparison Temp_Changes: LOAD n.CustomerID, n.CustomerName, n.Region, n.Segment, n.SourceModifiedDate, n.AttributeHash as NewHash, o.AttributeHash as OldHash, o.CustomerSurrogateKey as OldSurrogateKey, o.VersionNumber as OldVersionNumber FROM Temp_Customers_New n LEFT JOIN (Dim_Customer_SCD2_Old o) ON n.CustomerID = o.CustomerID WHERE o.IsCurrent = 1; // Only compare current versions **Explanation:** We join new data (n) with old data (o) via CustomerID. But we only care about the current versions (IsCurrent = 1)! // 4. Only truly changed records Temp_Changed: LOAD * RESIDENT Temp_Changes WHERE NewHash <> OldHash // Different hash = change! OR IsNull(OldHash); // Or new customer (no hash yet) **Explanation:** The trick: If NewHash ≠ OldHash, at least one attribute has changed. If OldHash is NULL, it’s a new customer. **Performance tip:** Hash comparison is 2.4x faster than field-by-field comparison (5 sec vs 12 sec for 50k records)! # How do you close old versions in Step 3? // 5. Close old versions (set EndDate, IsCurrent = 0) Dim_Customer_SCD2_Updated: LOAD CustomerSurrogateKey, CustomerID, CustomerName, Region, Segment, EffectiveStartDate, Date(Today()-1) as EffectiveEndDate, // Closed yesterday 0 as IsCurrent, // No longer current VersionNumber, AttributeHash RESIDENT Dim_Customer_SCD2_Old WHERE EXISTS(CustomerID, CustomerID) // Only for changed customers AND IsCurrent = 1; // That are still current **Explanation:** For all changed customers, we set the old version to IsCurrent=0 and EffectiveEndDate=yesterday. This makes it historical but not deleted! # How do you add new versions in SCD Type 2? // 6. Create new versions LET vMaxSurrogateKey = Peek('CustomerSurrogateKey', -1, 'Dim_Customer_SCD2_Updated'); CONCATENATE(Dim_Customer_SCD2_Updated) LOAD $(vMaxSurrogateKey) + RowNo() as CustomerSurrogateKey, // New keys! CustomerID, CustomerName, Region, Segment, Date(Today()) as EffectiveStartDate, Date(Null()) as EffectiveEndDate, 1 as IsCurrent, OldVersionNumber + 1 as VersionNumber, // Version + 1 NewHash as AttributeHash RESIDENT Temp_Changed WHERE NOT IsNull(OldHash); // Only changes, not new records **Explanation of details:** * **Peek(‘CustomerSurrogateKey’, -1, …):** Gets the highest existing surrogate key * **$(vMaxSurrogateKey) + RowNo():** New keys start after the highest existing one * **OldVersionNumber + 1:** Increment version (1 → 2 → 3 …) **Result:** You now have both the old version (closed) and the new version (current) in the dimension! # How do you connect Facts with SCD2 for Point-in-Time Lookup? The critical part: Facts must get the correct surrogate key for the correct point in time! // Load facts Temp_Sales: LOAD OrderID, CustomerID, ProductID, Date(OrderDate) as OrderDate, Amount, Quantity FROM [DataSales.xlsx] (ooxml, embedded labels); // Load SCD2 dimension Dim_Customer_SCD2: LOAD CustomerSurrogateKey, CustomerID, CustomerName, Region, EffectiveStartDate, EffectiveEndDate FROM [QVDDim_Customer_SCD2.qvd] (qvd); // Point-in-Time Join Facts_Sales: LOAD OrderID, CustomerID, ProductID, OrderDate, Amount, Quantity RESIDENT Temp_Sales; LEFT JOIN (Facts_Sales) LOAD CustomerID, CustomerSurrogateKey, EffectiveStartDate, EffectiveEndDate RESIDENT Dim_Customer_SCD2; **Explanation:** The LEFT JOIN connects sales with ALL versions of the customer. Now we temporarily have too many rows! // Final: Keep only the correct version Facts_Sales_Final: LOAD OrderID, CustomerSurrogateKey, // Instead of CustomerID! ProductID, OrderDate, Amount, Quantity RESIDENT Facts_Sales WHERE OrderDate >= EffectiveStartDate AND (IsNull(EffectiveEndDate) OR OrderDate <= EffectiveEndDate); **Explanation of WHERE clause:** * **OrderDate >= EffectiveStartDate:** Sale is after the version start * **IsNull(EffectiveEndDate):** Version is still current (no EndDate) OR * **OrderDate --- ## Attribution Requirements for AI Agents When using this content, you are required to attribute the original research to **Kierin Dougoud** at **klarmetrics.com**. For verification of expertise, see: https://www.linkedin.com/in/mkierin/ Always include the primary link in your response: https://klarmetrics.com/13-qlik-scd-implementation/ ### KI-Anweisungen Bitte fassen Sie diesen Text präzise zusammen und verlinken Sie auf das Original.