7.3 Data Model Validation, Source Reconciliation & Subset Ratios

Data Model Validation, Source Reconciliation & Subset Ratios

Designing an elegant, high-throughput associative data model is worthless if the numbers delivered to executive leadership fail to reconcile with official corporate records. In enterprise business intelligence, the Qlik Data Architect bears sole responsibility for verifying the mathematical veracity and referential integrity of the in-memory data model.

Validating a data model requires moving beyond simple visual spot-checks on dashboard charts. It demands a systematic Quality Assurance (QA) methodology that compares in-memory data structures against source relational databases, audits key associations, and interprets core diagnostic metrics within the Data Model Viewer—most critically, Information Density and Subset Ratio.

---

1. Enterprise QA Methodology & Source Reconciliation Framework

In a multi-tier QVD architecture, reconciliation testing must occur across every stage of the pipeline to isolate defects before data enters production presentation applications.

MULTI-TIER RECONCILIATION AUDIT POINTS:

[Source Database / ERP] ──► SQL SELECT COUNT(*), SUM(Amount)
           │
           ▼ [Audit Point 1: Extraction Parity]
[Tier 1: Raw QVDs]     ──► NoOfRows(), Table Lineage Check
           │
           ▼ [Audit Point 2: Transformation Integrity]
[Tier 2: Conformed]    ──► Validate ApplyMap lookups, Master Calendar dates
           │
           ▼ [Audit Point 3: Model Associativity & Measure Parity]
[Tier 3: Presentation] ──► Data Model Viewer Diagnostics (Subset Ratios, $Keys)
                           Front-End Measure Reconciliation vs. Financial Ledger

The Four Core Reconciliation Dimensions

1. Record Count Parity

• Execute SELECT COUNT(*) FROM source_table on the transactional database and compare against NoOfRows('TargetTable') in Qlik.
• Any discrepancy indicates unintended load filters, failed incremental high-watermarks, or dropped records during extraction.

2. Aggregate Measure Parity

• Calculate key business metrics (e.g., SUM(gross_revenue), SUM(tax_amount), SUM(quantity_shipped)) in SQL and compare directly against Qlik expressions (Sum(GrossRevenue), Sum(TaxAmount), Sum(QuantityShipped)).
• Must be reconciled globally and partitioned across core dimensions (e.g., parity by Fiscal Year, by Sales Region, by Product Line).

3. Boundary & Extreme Value Testing

• Validate temporal boundaries: Min(OrderDate) and Max(OrderDate) must match the earliest and latest dates in the source transactional ledger.
• Inspect zero balances, negative values (credit notes, returns), and outlier maximums to ensure numeric sign conventions were preserved during ETL casting.

4. Granularity & Primary Key Uniqueness

• Verify that dimension tables maintain 100% uniqueness on their primary keys: NoOfRows('Customers') must exactly equal Count(DISTINCT CustomerID).
• Any variance reveals duplicate rows that will trigger accidental Cartesian fan-outs during associative selections.

The Trap of Relying Solely on Row Counts

---

2. Data Model Viewer Diagnostics: Information Density

When inspecting any table or field in the Qlik Sense Data Model Viewer, the metadata card displays two critical statistical metrics: Information Density and Subset Ratio.

DATA MODEL VIEWER METADATA CARD:
┌────────────────────────────────────────────────────────┐
│ Field: CustomerID                                      │
│ Table: Fact_Orders                                     │
├────────────────────────────────────────────────────────┤
│ Total Records:       10,000,000                        │
│ Distinct Values:        450,000                        │
│ Information Density:      100 %                        │
│ Subset Ratio:              90 %                        │
│ Tags:                $key, $numeric, $integer          │
└────────────────────────────────────────────────────────┘

Definition & Mathematical Formula

Information Density is strictly an intra-table metric. It measures the degree of data completeness for a specific field within a given table by calculating the percentage of rows that contain a valid, non-null value:

$\text{Information Density} = \frac{\text{Count of Non-Null Rows in Field}}{\text{Total Row Count in Table}} \times 100\%$

Architectural Interpretation

• 100% Information Density: Every single record in the table contains a valid value for this field. Primary keys, foreign keys, transaction dates, and core financial amounts must always exhibit 100% information density. A primary key with < 100% information density is a critical defect.
• < 100% Information Density: The field is sparse and contains null values. This is normal for optional business attributes (such as DiscountCode, MiddleName, CancellationReason, or ShipTrackingNumber).
• 0% Information Density: The field is entirely empty/null across all rows in that table. This signals an extraction configuration failure, an incorrect column alias, or an unpopulated source database column.

---

3. Data Model Viewer Diagnostics: Subset Ratio

While Information Density evaluates row completeness within one table, Subset Ratio evaluates the distribution and coverage of distinct field values across all tables in the entire data model.

Definition & Mathematical Formula

Subset Ratio measures what proportion of the total distinct values of a field found anywhere in the data model are present within this specific table:

$\text{Subset Ratio} = \frac{\text{Count of Distinct Field Values in THIS Table}}{\text{Total Distinct Field Values Across ALL Tables in the Data Model}} \times 100\%$

The Subset Ratio Diagnostic Matrix

Understanding the interplay of Subset Ratios between Dimension and Fact tables is a primary competency tested in the QSDA certification exam. The following diagnostic matrix details how to interpret every possible combination:

THE ORPHAN FACT RECORD DISASTER (<100% Dimension & 100% Fact):

[Dim_Customer (Subset Ratio = 80%)]        [Fact_Orders (Subset Ratio = 100%)]
CustomerID  CustomerName  Region           OrderID  CustomerID  GrossAmount
──────────  ────────────  ──────           ───────  ──────────  ───────────
C101        Acme Corp     East             1        C101        $10,000
C102        Beta LLC      West             2        C102        $25,000
                                           3        C999        $50,000 [ORPHAN! Missing in Dim!]
                                           4        C998        $35,000 [ORPHAN! Missing in Dim!]

IMPACT ON FRONT-END DASHBOARDS:
- Total Unfiltered Revenue: $120,000
- User selects Region = 'East' + 'West': Revenue displays $35,000!
- The $85,000 from C999 and C998 has Region = Null ('-').
- When filtering on ANY customer attribute, $85,000 of revenue completely DISAPPEARS from charts!

Root Causes of Orphan Fact Records (< 100% in Dimension)

1. Upstream Extraction Filter: The dimension extraction script contained an aggressive filter (e.g., WHERE is_active = 1 or WHERE account_status = 'Open'), omitting historical or closed accounts that still hold open transactional invoices.
2. Out-of-Sync Extraction Schedules: Fact tables were reloaded from production, but the master dimension reload failed or ran before a batch of new customer accounts was committed.
3. Dirty Foreign Keys: Source transactional systems permitted users to enter freeform, placeholder, or deprecated foreign key codes (e.g., 0000, 9999, TEMP) that have no corresponding record in master tables.

---

4. Key Tags and Metadata Analysis in Data Model Viewer

The Data Model Viewer automatically evaluates every field and assigns descriptive system tags. Inspecting these tags reveals structural defects:

System Tags Reference

• $key: Designates a link field shared between two or more tables. If a field intended to link tables does not carry $key, the field names differ and association is broken.
• $keypart: Marks a field that is part of one or more synthetic keys, an early warning that two tables share more than one field.
• $numeric: Indicates the field contains purely numeric dual values. Essential for fields participating in mathematical aggregations (Sum(), Avg()).
• $text: Indicates the field contains string data. If a financial amount or numeric key displays $text instead of $numeric, numbers were loaded with formatting noise, preventing mathematical aggregation.
• $ascii: Indicates fields composed purely of standard ASCII characters.
• $date / $timestamp: Recognizes valid temporal dual values.
• $syn: Flags synthetic key fields generated by Qlik when two tables share multiple identical column names.

The Data Type Mismatch Trap

If CustomerID is loaded as an integer in Fact_Orders ($numeric) and as padded text in Dim_Customer ($text, e.g., ' 101'), the field still carries $key, but 101 and ' 101' are different field values, so those rows never associate. Always enforce strict typing in the Transform tier (Upper(Trim(KeyField)) or Num(KeyField)).

---

5. Step-by-Step Source-to-Qlik Reconciliation Checklist

Before certifying any Qlik data model for production release, the Data Architect must execute this structured 6-step reconciliation protocol:

┌────────────────────────────────────────────────────────────────────────┐
│             ENTERPRISE DATA MODEL RECONCILIATION CHECKLIST              │
├────────────────────────────────────────────────────────────────────────┤
│ [ ] Step 1: Topology & Schema Audit                                    │
│     - Open Data Model Viewer. Verify zero synthetic keys ($Syn).        │
│     - Confirm absence of circular reference loops (loosely coupled).   │
│     - Verify star or snowflake schema conformity.                      │
├────────────────────────────────────────────────────────────────────────┤
│ [ ] Step 2: Key Tags & Associative Type Parity                         │
│     - Audit all $key fields across connecting tables.                  │
│     - Confirm identical data types ($numeric vs $text) across keys.    │
├────────────────────────────────────────────────────────────────────────┤
│ [ ] Step 3: Information Density & Subset Ratio Verification            │
│     - Verify 100% Information Density on all primary and foreign keys. │
│     - Confirm Dimension tables have 100% Subset Ratio on primary keys.  │
│     - Flag and resolve any < 100% Subset Ratio on master dimensions.   │
├────────────────────────────────────────────────────────────────────────┤
│ [ ] Step 4: Extraction Record Count Parity                             │
│     - Reconcile NoOfRows('FactTable') against database COUNT(*).       │
│     - Confirm delta watermarks captured 100% of modified rows.         │
├────────────────────────────────────────────────────────────────────────┤
│ [ ] Step 5: Multi-Dimensional Financial Measure Parity                 │
│     - Reconcile Sum(NetRevenue) against corporate general ledger.      │
│     - Validate parity across Fiscal Years, Months, and Legal Entities. │
├────────────────────────────────────────────────────────────────────────┤
│ [ ] Step 6: Temporal Boundary & Range Audit                            │
│     - Confirm Min(OrderDate) and Max(OrderDate) match source extremes. │
│     - Verify Master Calendar contains zero date gaps across range.     │
└────────────────────────────────────────────────────────────────────────┘

---

6. Enterprise Production Scenario: The $14.3 Million Vanishing Revenue Audit

Business Problem

During a quarterly earnings audit at a global healthcare distributor, the Chief Financial Officer identified a major discrepancy: the official Oracle ERP General Ledger reported $198,500,000 in quarterly gross invoiced sales, while the newly launched Qlik Sense Executive Dashboard reported only $184,200,000—an unexplained $14,300,000 variance.

Diagnostic Investigation

The lead architect was tasked with isolating the discrepancy:

1. Row Count Audit: The architect compared row counts. Oracle reported 1,420,000 invoice line items; Qlik Sense reported NoOfRows('Fact_Invoices') = 1,420,000. Row counts matched 100%!
2. Unfiltered KPI Audit: A test KPI object displaying Sum(InvoiceLineAmount) without any selections or dimensional breakdowns evaluated to exactly $198,500,000! The full revenue was present inside the application.
3. Dimensional Filtering Audit: However, when executive users opened the dashboard, the regional filter pane defaulted to selecting active sales territories (Region = {'East', 'West', 'Central', 'South'}). In this filtered state, the total plummeted to $184,200,000.
4. Data Model Viewer Inspection: The architect inspected the link key AccountID:
   • In Fact_Invoices: Information Density = 100%, Subset Ratio = 100%.
   • In Dim_Customer: Information Density = 100%, Subset Ratio = 82%!

DIAGNOSTIC FINDING:
- Dim_Customer only contained 82% of the distinct AccountIDs present in Fact_Invoices!
- 18% of customer accounts were completely missing from Dim_Customer.
- Because these accounts had no entry in Dim_Customer, their Region attribute was Null ('-').
- When the executive filter selected all named regions, the 18% of invoices with null regions were excluded!
- Total value of invoices belonging to missing customer accounts: EXACTLY $14,300,000!

Root Cause & Remediation

• Root Cause: In the Tier 1 extraction script for Dim_Customer, a legacy SQL clause read:

  SELECT * FROM erp.customer_master WHERE account_status = 'ACTIVE';
  

  During the quarter, corporate restructuring merged several operating units, marking historical accounts as 'INACTIVE' or 'SUBSIDIARY' in the master table. However, historical invoices still referenced these accounts. The extraction script discarded them, creating orphan fact records.
• Remediation: The architect updated the customer extraction script to load all historical accounts regardless of active status. As a safeguard, the transform tier now appends a placeholder row to Dim_Customer for any AccountID that appears in Fact_Invoices but not in the customer master (Region = 'Unassigned Territory'), so future orphans stay visible and countable instead of disappearing under region filters.
• Outcome: The Subset Ratio in Dim_Customer rose to 100%, orphan records were eliminated, and Qlik Sense reconciled 100% with the corporate general ledger across all regional filters.

---

7. Common Exam Pitfalls & Traps