1.1 Business Requirements, KPI Formulation & Data Granularity

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:

1. Business Entities (Dimensions): Store, Regional Hierarchy, Sales Manager, Product Category, and Calendar Time (Month/Year). These represent the who, where, and what.
2. Quantitative Metrics (Facts/Measures): Sales Revenue, Cost of Goods Sold (COGS), Discount Amount, and Margin Target. These represent the numerical measurements.
3. Derived KPIs: Gross Margin Amount (Revenue - COGS) and Gross Margin Percentage ((Revenue - COGS) / Revenue).
4. 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:

$\text{True Gross Margin} = \frac{\text{Total Profit}}{\text{Total Sales}} = \frac{\$500 + \$10}{\$1,000 + \$100} = \frac{\$510}{\$1,100} = 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.

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

---

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:

1. 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.
2. 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:

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:

1. Executive VP of Retail: Wants to monitor overall monthly store revenue, gross margin %, and compare performance against monthly budget targets.
2. Distribution Center Director: Needs to view daily warehouse inventory on hand by SKU, identifying stockouts and overstock aging.
3. 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:

1. 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.
2. Inventory Fact: Periodic daily snapshot grain containing WarehouseKey, ProductKey, SnapshotDate, and semi-additive UnitsOnHand. Time aggregation uses point-in-time set analysis.
3. 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