Study guide
3.2 Multi-Fact Modeling: Fact Concatenation vs. Link Tables
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
3.2 Multi-Fact Modeling: Fact Concatenation vs. Link Tables
Key Takeaways Explain This Topic AI
Enterprise analytics frequently requires combining multiple fact tables representing distinct business processes that operate at divergent granularities (e.g., Daily Actual Sales vs. Monthly Budgets).
Fact Concatenation unions multiple fact tables into a single unified table, preserving a single-hop star schema, maximizing QIX query speed, and completely avoiding synthetic keys.
Link Table architecture maintains separate physical fact tables by extracting shared dimension keys into a central bridge table indexed via AutoNumber() composite keys.
When constructing a Link Table, shared dimension fields must be removed from the individual fact tables to prevent the automatic generation of synthetic keys between facts and the link table.
The decision between Concatenation and Link Tables is governed by dimensional overlap, measure sparsity, row counts, and the frequency of cross-fact comparative calculations.
Last updated: October 2026
3.2 Multi-Fact Modeling: Fact Concatenation vs. Link Tables
Quick Summary: Enterprise analytical solutions rarely consist of a single business process. Most enterprise applications require integrating multiple fact tables—such as Actual Sales transactions versus Sales Quotas, or Orders versus Shipments—that share some common dimensions (Date, Customer, Product) while also possessing process-specific dimensions and distinct levels of granularity. Loading multiple fact tables naively into Qlik Sense causes either severe Synthetic Key explosions or ambiguous Circular References . Data architects must resolve multi-fact models using one of two proven architectural patterns: Fact Concatenation or Link Tables .
The Multi-Fact Modeling Challenge in Business Analytics
In transactional database systems, distinct business processes reside in separate tables to maintain operational integrity. For example:
Actual Sales Transactions: Captured at the line-item level with transactional timestamps, customer IDs, product IDs, sales representative IDs, quantity, and sales amount.
Sales Budget / Targets: Established at a monthly, quarterly, or annual summary level, often allocated by sales representative or geographic region, without customer or product line-item detail.
Shipments / Logistics: Recorded when goods depart a warehouse, containing shipping carrier IDs, tracking numbers, ship dates, and freight costs.
When a business user requests a dashboard that compares Actual Sales vs. Budget Targets or Order Value vs. Shipped Value , the data model must integrate these disparate facts.
The Failure of Naive Loading
If an architect loads Fact_Sales and Fact_Budget directly into Qlik Sense without architectural intervention, both tables contain shared field names such as Date , SalesRepID , and RegionID .
Synthetic Key Creation: Because the two fact tables share more than one field with identical names, Qlik's associative engine automatically generates a synthetic key table ( $Syn1Table ). If additional common fields exist across more tables, cascading synthetic keys form, resulting in degraded calculation speed and bloated memory.
Circular References (Loops): If independent dimension tables (such as Dim_Date and Dim_Region ) are loaded and connect to both Fact_Sales and Fact_Budget , a closed relational loop is formed ( Fact_Sales → \rightarrow → Dim_Date → \rightarrow → Fact_Budget → \rightarrow → Dim_Region → \rightarrow → Fact_Sales ). Qlik breaks this loop by turning one or more tables into loosely coupled tables , which breaks associative filtering across the severed path.
The SQL Trap (Chasm Traps and Fan Traps): Attempting to join fact tables together in SQL or via Qlik JOIN creates catastrophic fan traps. Joining a monthly budget record ($100,000) to 500 daily sales transactions duplicates the budget record 500 times, inflating the budget total to $50,000,000.
To avoid these traps, architects implement either Fact Concatenation or a Link Table .
Strategy 1: Fact Concatenation (The Unified Fact Table)
Fact Concatenation is the process of appending (unioning) multiple fact tables into a single consolidated fact table during the ETL load script. In Qlik Sense, this is achieved using the CONCATENATE statement.
Handling Disparate Granularity and Field Alignment
Because distinct business processes capture different attributes at different levels of detail, concatenating them requires careful alignment:
Aligning Shared Dimension Keys: Shared fields must be given identical names in both tables so they populate the same columns (e.g., %DateKey , %SalesRepKey , %RegionKey ).
Temporal Granularity Reconciliation: If Actual Sales is daily ( 2026-05-14 ) and Budget is monthly ( 2026-05 ), the architect must establish a canonical calendar key. In the budget load, the date is normalized to the first day of the month using MonthStart(BudgetDate) as %DateKey , allowing both facts to associate with a single unified Master Calendar.
Discriminator Flag: A mandatory field named FactType , SourceProcess , or MetricType is loaded to distinguish the origin of each row (e.g., 'Actual' vs. 'Budget' ). This flag enables clean Set Analysis in charts: Sum({<FactType={'Actual'}>} Amount) .
Handling Non-Shared Fields (Null Padding): Attributes or measures unique to one process are left unpopulated for the other. In Qlik's columnar engine, null values consume zero bytes in Symbol Tables and negligible bits in Data Tables, making sparse tables highly memory-efficient.
Measure Separation vs. Attribute-Value Representation: Measures can be loaded into dedicated columns ( ActualAmount , BudgetAmount ) or structured as metric name and value columns ( MetricName , MetricValue ). Dedicated columns are generally preferred because they allow direct numeric aggregation without filtering.
Load Script Implementation: Fact Concatenation // Step 1: Load Actual Sales Transactions
[Fact_Consolidated]:
- LOAD
- OrderID,
- OrderLineNumber,
- OrderDate as %DateKey,
- CustomerID as %CustomerKey,
- ProductID as %ProductKey,
- SalesRepID as %SalesRepKey,
- SalesQuantity,
- SalesRevenue as Amount,
- SalesRevenue as ActualAmount,
- null() as BudgetAmount,
- 'Actual' as FactType
- FROM [lib://Data/ActualSales.qvd] (qvd);
- // Step 2: Concatenate Monthly Budget Targets
- CONCATENATE ([Fact_Consolidated])
- LOAD
- null() as OrderID,
- null() as OrderLineNumber,
- MonthStart(BudgetDate) as %DateKey,
- null() as %CustomerKey,
- null() as %ProductKey,
- SalesRepID as %SalesRepKey,
- null() as SalesQuantity,
- TargetRevenue as Amount,
- null() as ActualAmount,
- TargetRevenue as BudgetAmount,
- 'Budget' as FactType
- FROM [lib://Data/BudgetTargets.qvd] (qvd);
- Architectural Evaluation: Fact Concatenation
Pros:
Peak Calculation Performance: All measures reside in a single table, enabling single-scan hypercube evaluations without cross-table pointer dereferencing.
Preserves Pure Star Schema: Directly links to all dimension tables via single-hop relationships.
Zero Synthetic Keys or Loops: Eliminates all multi-table association ambiguity.
Memory Efficiency: Columnar dictionary encoding ensures null cells consume virtually zero RAM.
Cons:
Table Sparsity: If combining five or more disparate facts that share almost no dimensions, the resulting table contains dozens of sparse null columns.
Potential Front-End Confusion: End users may inadvertently sum Amount across mixed fact types if they omit the FactType filter.
Strategy 2: Link Table Architecture (The Central Bridge Model)
When fact tables represent radically different business processes with little dimensional overlap, or when business governance requires keeping fact tables physically separate, the Link Table Architecture is the preferred design.
Link Table Mechanics
In a Link Table model:
The individual fact tables remain separate physical tables in memory.
All shared dimension keys are extracted from each fact table and concatenated into a single centralized bridge table called the Link Table .
A Composite Link Key is created in each fact table and in the Link Table by concatenating all active shared dimension keys.
CRITICAL STEP: The individual shared dimension fields are dropped or excluded from the individual fact tables. The fact tables retain ONLY their process-specific measures, transaction identifiers, and the composite link key.
The dimension tables associate directly to the Link Table via their individual primary keys.
The Role of AutoNumber() in Link Table Optimization
Creating a composite key by concatenating multiple string keys (e.g., Date & '|' & CustomerID & '|' & ProductID ) generates wide string representations (40 to 100+ bytes each). In large models with tens of millions of rows, storing these long strings in symbol tables consumes massive amounts of RAM.
The AutoNumber() function evaluates the concatenated string expression and converts each distinct combination into a sequential 32-bit integer (1, 2, 3...). This reduces the Symbol Table footprint to near zero and accelerates pointer comparison speed in the Data Table.
Load Script Implementation: Link Table Architecture // Step 1: Load Orders Fact Table (retrieving only measures and the link key)
[Fact_Orders]:
- LOAD
- OrderID,
- AutoNumber(OrderDate & '|' & CustomerID & '|' & ProductID & '|' & RegionID) as %Key_Link,
- OrderQuantity,
- OrderAmount
- FROM [lib://Data/Orders.qvd] (qvd);
// Step 2: Load Shipments Fact Table
[Fact_Shipments]:
- LOAD
- ShipmentID,
- CarrierID,
- AutoNumber(ShipDate & '|' & CustomerID & '|' & ProductID & '|' & RegionID) as %Key_Link,
- FreightCost,
- ShippedQuantity
- FROM [lib://Data/Shipments.qvd] (qvd);
// Step 3: Construct the Central Link Table
[Link_Table]:
- LOAD DISTINCT
- AutoNumber(OrderDate & '|' & CustomerID & '|' & ProductID & '|' & RegionID) as %Key_Link,
- OrderDate as %DateKey,
- CustomerID as %CustomerKey,
- ProductID as %ProductKey,
- RegionID as %RegionKey
- FROM [lib://Data/Orders.qvd] (qvd);
- CONCATENATE ([Link_Table])
- LOAD DISTINCT
- AutoNumber(ShipDate & '|' & CustomerID & '|' & ProductID & '|' & RegionID) as %Key_Link,
- ShipDate as %DateKey,
- CustomerID as %CustomerKey,
- ProductID as %ProductKey,
- RegionID as %RegionKey
- FROM [lib://Data/Shipments.qvd] (qvd);
- Architectural Evaluation: Link Table
Pros:
Physical Separation of Concerns: Keeps distinct transaction processes isolated, simplifying independent maintenance and measure definitions.
Eliminates Null Sparsity: No wide tables with dozens of empty columns.
Flexible Granularity: Readily accommodates processes that link to different subsets of dimensions without polluting fact tables with null foreign keys.
Cons:
Two-Hop Associative Latency: To calculate an aggregation of OrderAmount grouped by CustomerName , the QIX engine must navigate: Fact_Orders → \rightarrow → Link_Table → \rightarrow → Dim_Customer . This extra join hop increases CPU calculation time.
Link Table Row Explosion: In models with many shared dimensions, the distinct permutations of dimension keys can cause the Link Table to reach tens of millions of rows, increasing memory consumption.
Architectural Decision Matrix: Concatenation vs. Link Table
To select between Fact Concatenation and Link Tables on real-world projects and the QSDA certification exam, evaluate the following decision criteria:
Evaluation Criterion Choose Fact Concatenation Choose Link Table
Dimensional Overlap High overlap (>60% of dimensions shared across facts) Low to moderate overlap (<40% of dimensions shared)
Measure Sparsity Facts share similar metrics or have few non-overlapping measures Facts have numerous distinct, process-specific metrics
Query Performance Priority Maximum interactive speed required (sub-second SLA) Moderate speed acceptable; separation of data takes precedence
Row Volume High volume (single table scan is faster than multi-hop joins) High volume with divergent grains where concatenation creates extreme sparsity
Granularity Disparity Low to moderate disparity (e.g., Daily Actuals vs Monthly Budget) Radical disparity (e.g., Millisecond IoT Telemetry vs Annual Corporate Targets)
UI Measure Usability Formulas frequently compare metrics directly ( Actual - Budget ) Measures are reported independently on separate sheets/dashboards
Maintenance Complexity Simpler script (fewer tables and keys to manage) Requires rigorous management of composite keys and dropped fields
Common Modeling Pitfalls on the QSDA Exam
Retaining Shared Keys in Fact Tables When Building a Link Table: The most frequent exam error is extracting shared keys into a Link Table but failing to drop them from the individual fact tables. If DateKey remains in both Fact_Sales and Link_Table , Qlik detects multiple shared fields ( %Key_Link and DateKey ) and immediately creates a $Syn synthetic key table, completely defeating the purpose of the Link Table.
Using Unhashed String Keys Instead of AutoNumber(): Building composite link keys using raw string concatenation without AutoNumber() results in massive symbol tables that consume gigabytes of unnecessary RAM on large datasets.
Concatenating Mismatched Date Grains Without Canonical Alignment: Appending monthly budget records with BudgetDate formatted as '2026-05' into a fact table containing daily OrderDate formatted as '2026-05-14' without converting the budget date using MonthStart() severs the association with the Master Calendar.
Using SQL or Qlik JOIN on Multi-Fact Models: Executing a relational JOIN or LEFT JOIN between two fact tables produces Cartesian row multiplication (fan traps), creating mathematically false metric totals in front-end dashboards.