4.2 Composite Keys, Cardinality & AutoNumber Memory Optimization

Composite Keys, Cardinality & AutoNumber Memory Optimization

Quick Summary: In enterprise Qlik Sense architectures, linking tables with multi-attribute relationships—such as connecting multi-grain fact tables through a Link Table or resolving synthetic keys—requires creating composite keys. However, naive string concatenation (FieldA & '|' & FieldB) generates long, high-cardinality string keys that severely inflate Symbol Table RAM and widen Data Table bit pointers. The AutoNumber() function and its cryptographic hash variants (AutoNumberHash128(), AutoNumberHash256()) solve this bottleneck by replacing sprawling string keys with compact, 1-based sequential integers, sharply reducing symbol-table memory.

---

1. Composite Keys in Qlik Sense Data Models

A composite key (or compound key) is a surrogate identifier constructed by combining two or more distinct data fields into a single linkage field. In relational database design, composite primary and foreign keys are resolved through multi-column join clauses (ON a.Col1 = b.Col1 AND a.Col2 = b.Col2). However, the QIX associative engine operates under a strict relational law: tables associate if and only if they share an identically named field.

When two tables share multiple identical field names, Qlik does not execute a multi-column join; instead, it automatically spawns a Synthetic Key Table ($Syn). To prevent synthetic keys while preserving the multi-attribute relationship, the data architect must combine the shared attributes into a single composite key field.

The Mandatory Delimiter Rule

When concatenating fields to form a composite key, architects must always include an explicit delimiter that cannot appear within the underlying data values:

// DANGEROUS ANTI-PATTERN: No delimiter causes accidental key collision
CustomerID & ProductID as %CompositeKey

// Examples of Collision:
// CustomerID = '12'  and ProductID = '345'  --> '12345'
// CustomerID = '123' and ProductID = '45'   --> '12345' (COLLISION! Corrupts associative link)

// ENTERPRISE BEST PRACTICE: Explicit pipe or tilde delimiter guarantees uniqueness
CustomerID & '|' & ProductID as %CompositeKey

// Result:
// '12|345'  <> '123|45' (Distinct keys preserved)

---

2. The Severe Memory Penalty of Concatenated String Keys

While string concatenation (FieldA & '|' & FieldB & '|' & FieldC) solves relational ambiguity, it creates a massive performance and memory bottleneck in large enterprise datasets.

The Anatomy of String Key Bloat

Consider a supply chain data model containing 25,000,000 shipment fact rows and a shared Link Table connecting Shipments, Orders, and Invoices across three attributes: CompanyCode (4 chars), PlantID (8 chars), and DocumentNumber (18 chars).

String Composite Key Construction:
CompanyCode & '|' & PlantID & '|' & DocumentNumber as %ShipmentLinkKey
Example Key Value: "US10|PLANT-EAST-04|DOC-2026-9874102948"
Average String Length: 36 characters (36 bytes in UTF-8)

Assume there are 18,000,000 distinct combinations across the dataset:

1. Symbol Table Footprint:
   • Unique symbols ($N$): $18,000,000$.
   • String data: $18,000,000 \times 36\text{ bytes} \approx 648\text{ MB}$.
   • Symbol Table overhead (pointers, hash slots, tree nodes): $18,000,000 \times 32\text{ bytes} \approx 576\text{ MB}$.
   • Total Symbol Table RAM: $\approx 1.22\text{ GB}$ just to store the dictionary of string keys!
2. Data Table Pointer Array Footprint:
   • Distinct cardinality ($N$): $18,000,000$.
   • Pointer bit-width ($W$): $\text{ceil}(\log_2(18,000,000)) = \mathbf{25\text{ bits}}$ per cell ($2^{25} = 33,554,432$).
   • Fact Table Pointers: $25,000,000 \times 25\text{ bits} \approx 78.1\text{ MB}$.
   • Link Table Pointers: $18,000,000 \times 25\text{ bits} \approx 56.3\text{ MB}$.
   • Total Pointer RAM: $\approx 134.4\text{ MB}$.
3. Associative Join Hash Tables:
   • When a user filters by plant or company, the QIX engine must evaluate associative relationships across this 36-byte string key. String hashing, cache misses, and memory bus transfers significantly degrade CPU responsiveness.

---

3. The AutoNumber() Function: Engine Mechanics & Compression

The AutoNumber() function is the primary tool for optimizing composite keys in Qlik Sense. It evaluates an arbitrary string expression and replaces it with a compact, 1-based sequential integer ($1, 2, 3, \dots$) on the fly during script execution.

Syntax

AutoNumber(Expression [, AutoNumberId])

Where:

• Expression: The string or concatenated expression to convert into an integer.
• AutoNumberId (Optional): A distinct integer or string identifier representing an isolated lookup namespace. If omitted, the default namespace is used.

How AutoNumber() Operates Internally

During reload, Qlik maintains a dynamic in-memory hash table for each AutoNumberId namespace:

1. When AutoNumber() encounters a value for the first time (e.g., 'US10|PLANT-EAST-04|DOC-001'), it inserts the string into its temporary lookup table, assigns it the next integer in the sequence (1), and returns 1.
2. When it encounters a previously seen string value, it immediately retrieves the existing assigned integer (1) and returns it.
3. At the conclusion of the reload script, the temporary hash table is completely purged from RAM. Only the resulting integer keys remain in the data model.

Raw Concatenated String                   AutoNumber() Integer Key
'US10|PLANT-01|DOC-901'          ──►      1
'US10|PLANT-02|DOC-902'          ──►      2
'US10|PLANT-01|DOC-901' (Seen)   ──►      1 (Reused)
'CA20|PLANT-05|DOC-903'          ──►      3

The Memory Revolution of Integer Keys

Replacing the 36-byte string composite key with AutoNumber() transforms the memory architecture:

• Symbol Table Transformation: Instead of storing 18 million 36-byte string literals, the Symbol Table stores a dense sequence of 32-bit integers ($1$ to $18,000,000$). The QIX engine applies specialized bit-packing optimizations for dense sequential integers, shrinking symbol table overhead from $1.22\text{ GB}$ to under $75\text{ MB}$.
• Faster Comparisons: Compact integer keys are cheaper to store and compare than long string keys, which helps both reload time and selection response.

---

4. Cryptographic Hashing: AutoNumberHash128() and AutoNumberHash256()

Qlik also provides hash-based functions that accept the key fields as separate arguments, so you do not have to build a delimited string yourself.

The Hashing Function Family

Passing Fields Directly with AutoNumberHash128()

Instead of writing:

// Builds a delimited string first, then numbers it
AutoNumber(CompanyCode & '|' & PlantID & '|' & DocumentNumber) as %Key

Architects write:

// Passes the key fields directly; no delimiter needed
AutoNumberHash128(CompanyCode, PlantID, DocumentNumber) as %Key

Qlik documents AutoNumberHash128() as calculating a 128-bit hash of the combined input values and returning a unique integer for each distinct hash value. The result is the same kind of compact sequential key as AutoNumber(), and like AutoNumber() it is only stable within one reload.

The AutoNumber Statement

Qlik also provides an AutoNumber script statement that converts whole fields after they have been loaded:

// Load keys as readable strings or hashes; the QVD reads can stay optimized
Fact_Sales:
LOAD %SalesKey, SalesAmount FROM [lib://QVD/Fact_Sales.qvd] (qvd);

Dim_Store:
LOAD %SalesKey, StoreName FROM [lib://QVD/Dim_Store.qvd] (qvd);

// After the final LOAD: replace every value of matching fields with an integer
AutoNumber '%*Key';

Qlik notes that the AutoNumber() function inside a LOAD limits optimized QVD loads, whereas loading the QVD first and then running the statement keeps the load optimized. Place the statement after the last LOAD that touches those fields, quote field names when you use wildcards, and remember that each field gets its own key index unless you add Using namespace.

---

5. Cross-Reload Key Stability in Incremental Load Pipelines

Consider this failure scenario:

1. On Monday, AutoNumber('Customer-A') is the first row loaded, receiving integer key 1 in Fact_Sales.qvd.
2. On Tuesday, an incremental reload runs. Because of new incoming transactions, AutoNumber('Customer-Z') is processed first, receiving integer key 1. Meanwhile, Customer-A is loaded fifth, receiving integer key 5.
3. The historical QVD stores Customer-A with key 1, while the newly loaded record stores Customer-A with key 5. The associative model is now catastrophically corrupted!

Enterprise Solution: Persistent Key Mapping QVD

To safely deploy AutoNumber() across multi-tier QVD architectures and incremental load pipelines, architects must ensure key stability. Two primary enterprise patterns exist:

Pattern A: Deterministic Hash128() in QVDs, AutoNumber() in Data Mart

In this recommended pattern, incremental ETL layers store deterministic 128-bit hashes in QVD files. The presentation layer app loads all QVDs and applies AutoNumber() in a single pass:

Extract Tier (Incremental)  ──► Stores Hash128(Company, Plant, Doc) as %HashKey in QVD
                                (Stable across any reload, any day)
                                     │
                                     ▼
Data Mart Model             ──► LOAD AutoNumber(%HashKey) as %IntegerKey
                                (Runs across all data at once; keys match perfectly!)

Pattern B: Persistent Key Registry (Master Key QVD)

If the architect requires integer keys stored directly within persistent QVD files, a Master Key Registry must be maintained:

// Step 1: Load the existing Master Key Registry from disk
KeyRegistry:
LOAD
    NaturalKey,
    IntegerKey
FROM [lib://Governance/KeyRegistry.qvd] (qvd);

// Determine the current maximum integer assigned
Let vMaxKey = Peek('IntegerKey', -1, 'KeyRegistry');
If Len('$(vMaxKey)') = 0 Then
    Let vMaxKey = 0;
End If

// Step 2: Extract distinct new natural keys from today's incremental batch
NewKeys:
NoConcatenate LOAD DISTINCT
    NaturalKey
FROM [lib://Extract/TodayIncrementalTransactions.qvd] (qvd)
WHERE NOT Exists(NaturalKey);

// Step 3: Assign new sequential integers starting from vMaxKey + 1
Concatenate (KeyRegistry)
LOAD
    NaturalKey,
    RecNo() + $(vMaxKey) as IntegerKey   // RecNo() restarts at 1 for this resident source; RowNo() would continue counting the concatenated registry
RESIDENT NewKeys;

DROP TABLE NewKeys;

// Step 4: Save the updated Master Key Registry
STORE KeyRegistry INTO [lib://Governance/KeyRegistry.qvd] (qvd);

// Step 5: Map new integer keys into transactional facts using ApplyMap
Map_NaturalToInteger:
MAPPING LOAD NaturalKey, IntegerKey RESIDENT KeyRegistry;

Fact_Sales:
LOAD
    TransactionID,
    ApplyMap('Map_NaturalToInteger', NaturalKey) as %IntegerKey,
    SalesAmount
FROM [lib://Extract/TodayIncrementalTransactions.qvd] (qvd);

DROP TABLE KeyRegistry;

---

6. Enterprise Scenario: Global Logistics Multi-Fact Link Table

Business Problem

GlobalFreight Inc. operates a worldwide logistics platform processing 30,000,000 shipments, 25,000,000 freight invoices, and 12,000,000 customs declaration filings annually. Management requires an interactive executive dashboard where analysts can select any vessel, port of origin, or customs broker and seamlessly cross-analyze freight billing variance against transit milestone delays.

A novice developer constructed a Link Table using a raw concatenated string composite key:

VesselIMO & '|' & OriginPortCode & '|' & DestPortCode & '|' & CarrierID as %ShipmentLinkKey

Performance Symptoms:

• Server RAM consumption exceeded 42 GB on reload.
• Interacting with port filter panes produced a 4.5-second UI freeze as the engine resolved string pointer intersections.
• The data model viewer showed the Symbol Table for %ShipmentLinkKey consuming 2.1 GB alone.

Architectural Remediation:

1. Replaced the 4-attribute string concatenation with AutoNumberHash128(VesselIMO, OriginPortCode, DestPortCode, CarrierID) in all three fact tables and the Link Table, all in the same reload so the integers match.
2. Converted the Link Table from storing raw strings to pure dense sequential integers.

Illustrative Results (this scenario):

---

7. String Key vs. AutoNumber Memory Comparison Table

The following matrix contrasts the architectural properties of raw string keys versus AutoNumber() integer keys across enterprise dimensions:

---

8. Common Exam Pitfalls & Traps