Three Data Aggregation Errors in BI Systems

# Why BI Systems Lie: Three Fatal Data Aggregation Errors

Metrics in BI tools often display illusory precision. In practice, as soon as data shifts from raw transactions to pre-aggregated marts, systematic distortions emerge—sometimes by tens of percent. This isn't a bug; it's an architectural trade-off of speed versus accuracy. Let's break down three key mathematical traps that make analytics dangerous for decision-making.

Error One: Averaging Averages — A Mathematical Catastrophe

Average receipt, average request processing time, average order cost—these metrics are non-additive. You can't just average already averaged values. Classic example: A BI system takes average receipts per store for January, February, and March, adds them up, and divides by three to get the quarterly figure. The formula looks like this:

SELECT 
    region_name,
    EXTRACT(QUARTER FROM year_month) AS quarter,
    AVG(avg_receipt_amount) AS total_average_check
FROM dwh.store_monthly_aggregated
WHERE EXTRACT(QUARTER FROM year_month) = 1
GROUP BY region_name, EXTRACT(QUARTER FROM year_month);

The problem is that A/B + C/D ≠ (A+B)/(C+D). A hypermarket with 10,000 receipts a day gets the same weight in the calculation as a kiosk with 50 receipts. A month with peak sales is equated with a 'dead' one. In real data, this leads to deviations from -6% to +7%. In retail, where every percentage point increase in average receipt means millions of rubles, such distortions are unacceptable. Worst of all, when a coincidence of denominators accidentally yields the correct result—the error gets cemented in the DWH as 'right'.

Error Two: Count Distinct When Changing Filter Context

Counting unique objects—customers, receipts, products—breaks down when changing the level of detail. Imagine three transactions:

• Receipt #101 | Groceries
• Receipt #101 | Beverages
• Receipt #102 | Beverages

Real number of unique receipts—2. But if the data is pre-aggregated by categories:

• Groceries: 1 receipt (#101)
• Beverages: 2 receipts (#101, #102)

Then when collapsing the hierarchy in Power BI or an SQL engine, DAX performs a simple sum: 1 + 2 = 3. Uniqueness is lost. The system double-counted receipt #101. This error instantly distorts average receipt, conversion, LTV—everything that depends on the count of unique entities. The user doesn't even realize they've changed the view—and ends up with fake numbers.

Error Three: Semi-Additive Measures and Rigid Calendars

Inventory balances, account balances, number of active users—these are snapshot metrics. They can be summed across space (stores, regions), but not time. To work around this, developers use functions like LASTNONBLANK in DAX or NonEmpty in MDX:

Balance on konets perioda = 
CALCULATE (SUM('Inventory'[Balance]),
    LASTNONBLANK ('Calendar'[Date], 
        CALCULATE(SUM('Inventory'[Balance]))
    ))

This creates three problems:

• Performance: The engine iterates over all dates in the hierarchy on every filter change. On large volumes—freezes.
• Real-life failures: If on the 30th the cash register fails to transmit data, the system shows zero balance on the 31st. Goods 'disappear' worth hundreds of thousands of rubles.
• Code complexity: The user must be a developer. With multiple business calendars, formulas become unreadable and brittle.

What Matters

• Aggregation kills accuracy. Pre-compressing data for speed is a trade-off often accepted implicitly without grasping the consequences.
• Non-additive metrics require raw data. Any averaging, median, unique count—must be calculated on the fly from the full dataset.
• Snapshot metrics don't tolerate rigid time boundaries. The system must flexibly find the current value, not blindly follow the calendar.
• BI users shouldn't write DAX/MDX. The architecture should hide complexity, not dump it on the analyst.
• New platforms ditch pre-aggregations. Dynamic linking of raw sources on the fly is the only path to mathematically accurate analytics.

Paradigm Shift: Analytics Without Compromises

Classic BI systems are built on an illusion: we can pre-calculate everything the business needs. In reality, business constantly changes its questions. Instead of writing DAX workarounds, modern platforms work with raw data directly. For example, calculating 'Stock Coverage'—the ratio of inventory to sales—can combine two different sources: billions of inventory rows and billions of receipt rows. Instead of building a rigid mart in the DWH, the system dynamically links sources on every user click, recalculating the metric with mathematical precision. The core itself determines time windows, finds current inventory levels, and loads into memory only the necessary data. This isn't 'faster'; it's 'correct'. When bank executives first saw true request processing time—without distortions from averaging averages—they started asking the right questions. The retail chain no longer wants to sacrifice meaning for a 5-million-row limit. Analytics should be a RAW file, not a JPEG. Only this preserves details and the ability to refine insights. The technology exists—now it's time to rethink our mental architecture.

— Editorial Team