ExamFinch.com My Study

Study guide

1.1 Business Requirements, KPI Formulation & Data Granularity

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.1 Business Requirements, KPI Formulation & Data Granularity

Key Takeaways Explain This Topic AI

Architecting enterprise Qlik Sense data models requires deconstructing business stakeholder stories into discrete dimensional attributes and quantitative measures.

Facts must be classified by aggregation behavior into additive, semi-additive, and non-additive metrics to determine whether calculation occurs in the script or dynamically in the visualization layer.

Choosing between transaction-level, periodic snapshot, and accumulating snapshot fact grains balances analytical drill-down against QIX associative engine RAM consumption and symbol table size.

Ratios and percentages must never be pre-aggregated in source rows; they require additive component storage and dynamic evaluation using master measures.

Stakeholder personas dictate dimensional modeling strategies, ranging from high-level conformant summary stars for executives to atomic transaction logs for operational analysts.

Last updated: October 2026

Business Requirements, KPI Formulation & Data Granularity

In enterprise business intelligence, the primary responsibility of a Qlik Sense Data Architect is translating ambiguous business needs into a high-performance, associative data model. Designing an effective data architecture requires understanding how business users make decisions, formulating mathematically sound Key Performance Indicators (KPIs), selecting the appropriate data granularity, and optimizing the in-memory footprint of the Qlik associative engine (QIX).

1. Deconstructing Business Requirements into Data Architecture

Technical architecture begins with stakeholder requirements gathering. Business users typically articulate their needs through narrative user stories or operational pain points rather than dimensional specifications. For example, a stakeholder might state:

"Our regional sales directors need to track whether store managers are hitting their monthly gross margin targets, and if not, see which product categories or discount levels are eroding profitability."

A data architect deconstructs this statement into fundamental data components:

Business Entities (Dimensions) : Store, Regional Hierarchy, Sales Manager, Product Category, and Calendar Time (Month/Year). These represent the who , where , and what .

Quantitative Metrics (Facts/Measures) : Sales Revenue, Cost of Goods Sold (COGS), Discount Amount, and Margin Target. These represent the numerical measurements.

Derived KPIs : Gross Margin Amount ( Revenue - COGS ) and Gross Margin Percentage ( (Revenue - COGS) / Revenue ).

Associative Selection Scope : Slicing by region, filtering by month, and drilling down into specific product categories without pre-programmed navigational paths.

Requirements Translation Framework

To translate qualitative stakeholder stories into technical schemas, architects map requirements across four architectural tiers: Stakeholder User Story

  • │
  • ▼
  • Logical Entity Mapping (Dimensions vs. Facts)
  • │
  • ▼
  • Metric Categorization (Additive, Semi-Additive, Non-Additive)
  • │
  • ▼
  • Physical Grain Selection (Transactional, Periodic, Accumulating)
  • 2. Dimensional Attributes vs. Quantitative Measures

A common architectural failure is confusing descriptive attributes with quantitative measures. In Qlik Sense, this distinction dictates whether a field is loaded as a discrete symbol or aggregated inside a hypercube calculation.

Dimensions (Attributes) : Contextual, qualitative fields used to slice, filter, and group data. Dimensions populate list boxes, table rows, and chart axes. Examples include CustomerID , OrderDate , StoreRegion , and ProductSKU . In the QIX engine, every dimension is indexed into an in-memory symbol table containing distinct values, referenced by bit-stuffed pointers in the data table.

Measures (Facts) : Numeric values on which mathematical aggregations are evaluated dynamically across the user's active associative selection state. Examples include ExtendedPrice , FreightCost , and QuantitySold .

The Golden Rule of Qlik KPI Architecture

Architectural Rule : Never pre-calculate non-additive ratios, margins, or averages at the row level in extraction scripts if they are intended to be sliced dynamically across dimensions.

Consider an invoice containing two line items:

  • Line 1: Sales = $1,000, Profit = $500 (Margin = 50%)
  • Line 2: Sales = $100, Profit = $10 (Margin = 10%)

If the data architect calculates MarginPercent = Profit / Sales in the load script and stores 0.50 and 0.10 in the fact table, an end user placing Avg(MarginPercent) in a KPI object will see (0.50 + 0.10) / 2 = 30% . However, the true weighted business margin across both lines is:

True Gross Margin = Total Profit Total Sales = $ 500 + $ 10 $ 1 , 000 + $ 100 = $ 510 $ 1 , 100 = 46.36 % \text{True Gross Margin} = \frac{\text{Total Profit}}{\text{Total Sales}} = \frac{\$500 + \$10}{\$1,000 + \$100} = \frac{\$510}{\$1,100} = 46.36\% True Gross Margin = Total Sales Total Profit ​ = $1 , 000 + $100 $500 + $10 ​ = $1 , 100 $510 ​ = 46.36%

Pre-calculating the ratio produces a mathematically invalid metric. The architect must store raw additive components ( SalesAmount and ProfitAmount ) in the data table and define the KPI as a master measure using dynamic aggregation: // Master Measure Definition: Gross Margin %

  • Sum(ProfitAmount) / Sum(SalesAmount)
  • 3. Metric Taxonomy: Additive, Semi-Additive, and Non-Additive Facts

Facts loaded into Qlik Sense models fall into three distinct mathematical categories. Understanding these categories is mandatory for designing correct aggregation formulas and data structures.

Metric Category Mathematical Definition Real-World Examples Correct Qlik Aggregation Strategy

Fully Additive Can be summed across all dimensions, including time, geography, product, and customer. Sales revenue, quantity shipped, freight cost, marketing spend, invoice units. Sum(SalesAmount) or Sum(QuantityShipped)

Semi-Additive Can be summed across some dimensions (e.g., store, department), but never across time . Daily warehouse inventory balance, bank account balance, active headcounts, outstanding debt. Point-in-time set analysis: Sum({<Date={'$(=Max(Date))'}>} InventoryBalance)

Non-Additive Cannot be meaningfully summed across any dimension under any condition. Unit prices, discount rates, currency exchange rates, temperature, profit margin percentages. Ratio of additive sums: Sum(DiscountAmount) / Sum(GrossSales) or Only(ExchangeRate)

Handling Semi-Additive Metrics

Semi-additive facts represent point-in-time status rather than cumulative volume. For example, if a warehouse has 500 laptops in stock on Monday, 510 on Tuesday, and 495 on Wednesday, summing inventory across the three days yields 1,505 laptops—a completely fictitious figure.

In Qlik Sense, semi-additive facts require point-in-time constraints in expressions: // End-of-period inventory balance

Sum({<SnapshotDate = {"$(=Max(SnapshotDate))"}>} StockQuantity)

  • // Average daily balance across selected dates
  • Avg(Aggr(Sum(StockQuantity), SnapshotDate))
  • 4. Fact Table Granularity Levels

Data granularity (grain) defines the level of detail represented by a single record in a data table. Deciding the grain is the most critical technical compromise between analytical drill-down capability and QIX engine RAM consumption. Atomic Detail (Line Item) ◄── High RAM, High Cardinality, Full Drill-Down

  • │
  • Periodic Snapshot (Daily) ◄── Moderate RAM, Status Over Time
  • │
  • Accumulating Snapshot ◄── Lifecycle Milestone Tracking
  • │
  • Summary Aggregate (Monthly)◄── Low RAM, Fast Speed, Limited Drill-Down
  • 1. Transaction-Level Detail (Atomic Grain)

Definition : Each row corresponds to a single business event or atomic transaction, such as an e-commerce checkout line item, a cash register scan, or a bank ledger entry.

Strengths : Enables maximum analytical flexibility. Users can filter by individual invoice numbers, analyze basket composition, and drill down to individual customer transactions.

Architectural Trade-offs : High record volume and high cardinality of transactional identifiers (e.g., TransactionGUID , Timestamp ) expand QIX symbol tables and data tables, increasing memory usage and reload times.

2. Periodic Snapshot Grain

Definition : Records capture the state of business entities at regular, uniform intervals (e.g., daily stock levels, weekly customer account balances, monthly departmental headcount).

Strengths : Standardizes temporal trend analysis for semi-additive metrics. Allows straightforward period-over-period comparisons without calculating running totals from raw transaction logs.

Architectural Trade-offs : The table grows predictably with time (Number of Entities × \times × Time Intervals). Missing periods must be handled systematically in ETL to avoid gaps.

3. Accumulating Snapshot Grain

Definition : Records represent a distinct lifecycle or workflow process that has a clear beginning and end, featuring multiple milestone date/time columns that are updated as the entity progresses.

Examples : Insurance claim processing (Filed Date → \rightarrow → Evaluated Date → \rightarrow → Approved Date → \rightarrow → Paid Date), Order Fulfillment (Order Date → \rightarrow → Pick Date → \rightarrow → Pack Date → \rightarrow → Ship Date → \rightarrow → Delivery Date).

Strengths : Eliminates complex joins when calculating cycle times or stage durations ( DeliveryDate - OrderDate ).

Architectural Trade-offs : Records are updated over time rather than being strictly append-only, requiring incremental update pipelines in QVD generation.

Granularity Comparison Matrix

Grain Pattern Record Represents Temporal Behavior Typical Fact Attributes Best Qlik Use Case

Transactional Single atomic event Discrete timestamp Quantity, Extended Price, Line Discount Basket analysis, operational auditing, fraud detection

Periodic Snapshot Status at fixed interval Uniform recurring dates On-hand balance, account balance, pipeline stage value Working capital tracking, warehouse inventory, trending balances

Accumulating Snapshot Complete entity lifecycle Multiple milestone dates Cycle time, lag days, stage completion flags Order fulfillment pipelines, mortgage approvals, hiring funnels

5. QIX Associative Engine Memory Trade-Offs

Understanding how Qlik Sense stores data in RAM is vital when negotiating data granularity with business stakeholders. The QIX engine utilizes columnar, in-memory, associative storage composed of two primary internal structures:

Symbol Tables : For every distinct field in the data model, QIX builds a symbol table storing each unique value exactly once, assigned to an internal bit-width token.

Data Tables : The original table structure is stored as an array of bit-stuffed pointers that point to the memory addresses in the corresponding symbol tables. Raw Column Data Symbol Table (Distinct Values) Data Table (Bit Pointers)

  • ["USA", "100"] ──► Index 0: "USA" ──► [0, 0]
  • ["CAN", "200"] Index 1: "CAN" [1, 1]
  • ["USA", "100"] Index 0: 100 [0, 0]
  • ["USA", "200"] Index 1: 200 [0, 1]
  • The Cost of High Cardinality

Memory consumption in QIX is driven primarily by cardinality (the count of distinct values) rather than raw row counts. Consider loading 50,000,000 sales transactions:

Scenario A (Clean Date) : The architect truncates transaction timestamps to pure dates ( Floor(TransactionDate) ). Over 5 years, there are approximately 1,826 distinct dates. The symbol table contains only 1,826 entries, requiring 11 bits per row pointer.

Scenario B (Sub-Second Timestamp) : The architect retains raw source timestamps ( 2026-10-15 14:22:18.492 ). There are 45,000,000 distinct values. The symbol table balloons to gigabytes of RAM, and the data table pointer requires 26 bits per row.

When business users request transaction-level detail, the data architect must evaluate whether users genuinely need sub-second precision or if splitting timestamps into separate Date and Time fields—or aggregating to pure date grain—satisfies the analytical requirement while preserving engine performance.

  • 6. Stakeholders, Personas & Dimensional Modeling Strategies
  • Identifying Stakeholders in a Scenario

Two blueprint objectives sit together here: determining the primary requirements for business users and identifying stakeholders from a scenario . Scenario questions describe a project and ask whose input drives a decision. Separate the people who define requirements from the people who supply, secure, run, or consume the data:

Stakeholder What they contribute Requirement you capture from them

Business sponsor / process owner Owns the business question and success criteria Which KPIs matter, how they are defined, how fresh the data must be

Subject matter experts and power users Explain business rules and edge cases How returns, cancellations, adjustments, or transfers are counted

Source system owners and DBAs Grant access; describe tables, keys, and change tracking Read-only accounts, modification timestamps, extraction windows

Security, compliance, or data governance Decide who may see which rows and fields Section Access reduction fields, OMIT columns, retention rules

Qlik administrators Run streams or spaces, data connections, and reload schedules Where QVDs live, reload windows, service accounts

Consumers (executives, analysts, front-line staff) Use the finished app Grain, response time, navigation, and device needs

Primary requirements come from the sponsor, the subject matter experts, and the consumers; data-access and security constraints come from source owners, governance, and administrators. Requirements gathering therefore starts with the business question and its KPIs (including their grain), because source selection, model type, and security design all follow from those answers.

Consumption Personas

Data models must support different consumption behaviors across stakeholder personas:

1. The Executive Persona

Focus : High-level corporate KPIs, regional performance, period-over-period comparisons (YTD, QTD, YoY).

Behavior : Opens the dashboard for brief 2-minute sessions on mobile or tablets; requires sub-second load times; rarely drills to line items.

Architectural Solution : High-level aggregated summary tables, conformant dimensions, pre-calculated calendar periods, and curated Master Items.

2. The Operational Analyst Persona

Focus : Root-cause analysis, variance explanation, customer segmentation, inventory balancing.

Behavior : Spends hours exploring associative white/gray states, combining unexpected dimensions, running ad-hoc set analysis queries.

Architectural Solution : Star schema with clean atomic or daily periodic snapshot grain; normalized dimension tables collapsed into flat dimensions to maximize associative speed.

3. The Front-Line Operational Worker Persona

Focus : Task execution, pending order fulfillment, specific invoice queries, stock replenishment tickets.

Behavior : Searches for specific alphanumeric keys ( OrderNumber , TrackingID ); requires real-time or low-latency operational data.

Architectural Solution : Chained application architectures or On-Demand App Generation (ODAG) / Dynamic Views, where summary models allow users to select a subset of records and spawn an atomic detail app on demand.

  • 7. Enterprise Scenario: OmniRetail Logistics Architecture
  • Business Scenario

OmniRetail operates 850 retail stores and 12 distribution centers across North America. The leadership team commissions a Qlik Sense application to analyze supply chain efficiency and retail store profitability. During interviews, stakeholders present three distinct requirements:

Executive VP of Retail : Wants to monitor overall monthly store revenue, gross margin %, and compare performance against monthly budget targets.

Distribution Center Director : Needs to view daily warehouse inventory on hand by SKU, identifying stockouts and overstock aging.

Fulfillment Manager : Needs to track the elapsed turnaround hours between when an online customer places an order, when the warehouse picks it, when the carrier scans it, and when it arrives at the customer's doorstep.

Architectural Evaluation and Design

A novice architect might attempt to force all three requirements into a single monolithic transaction table, resulting in severe data fan-outs, synthetic keys, and massive memory bloat. The enterprise architect implements a multi-fact schema with tailored grains:

Store Sales Fact : Transactional or daily store/SKU grain containing additive SalesAmount , DiscountAmount , and CostAmount . Revenue and Margin KPIs are computed dynamically as master measures.

Inventory Fact : Periodic daily snapshot grain containing WarehouseKey , ProductKey , SnapshotDate , and semi-additive UnitsOnHand . Time aggregation uses point-in-time set analysis.

Fulfillment Fact : Accumulating snapshot grain containing OrderKey , CustomerKey , and milestone timestamps ( OrderDate , PickedDate , ShippedDate , DeliveredDate ). Turnaround metrics are calculated via duration expressions: Interval(DeliveredDate - OrderDate, 'd') .

Common dimensions ( Dim_Product , Dim_Store , Dim_MasterCalendar ) are conformed across all three facts, enabling associative exploration across sales, stock levels, and fulfillment performance without circular loops.

8. Common Exam Pitfalls & Traps

Warning

Pitfall 1: Pre-aggregating Ratios in ETL Storing pre-computed ratios like Margin % or Discount % at the row level in extraction scripts is a classic exam failure. Always store the numerator and denominator as separate additive facts and calculate the ratio in the visualization layer.

Warning

Pitfall 2: Timestamp Cardinality Inflation Retaining time components in date fields loaded into the associative engine. A date field with times contains up to 86,400 distinct values per day, multiplying symbol table size. Always truncate dates using Floor(DateField) or split them into distinct DateKey and TimeKey fields.

Warning

Pitfall 3: Summing Semi-Additive Balances Over Time Using simple Sum(Balance) on periodic snapshot data. This produces invalid multiplied numbers when users select multiple months or years. Set analysis must restrict the calculation to the latest snapshot date.

Warning

Pitfall 4: Misapplying Transactional Grain to Workflow Pipelines Attempting to model multi-stage processes with multiple event dates in a standard transactional table. This causes disconnected date filtering or requires complex link tables. Use an accumulating snapshot model with multiple milestone date fields.