Aggregation and Consolidated Reports

Aggregation and Consolidated Reporting

Aggregation is the transformation of detailed records into summary indicators for the required sections and periods. Consolidation - combining aggregates from different sources/legal entities/products into a single report with adjustments (currency, accounting policy, elimination). Below is a systematic approach: from designing shop windows to operating reporting.

1) Tasks and types of consolidation

Operating/Product: DAU/WAU/MAU, conversions, revenue by country/channel/platform.
Financial (ex. and regulatory): P&L, Cash Flow, Balance Sheet, GGR/Net Gaming Revenue, taxes, legal entity reports.
Risk/compliance: anti-fraud, RG indicators, SLA/availability, data/model drift.
Strategic: consolidated KPI by holding, brand portfolio, regions.

2) Architecture of the aggregation layer

Data layers: 'raw' → 'staging' → 'core' (facts/dimensions) → 'marts' (aggregates for reports).
Semantic layer: metric definitions, calendar, currencies, rollup rules.
Grain and time-grain: 'event', 'user _ day', 'txn', 'brand _ country _ day' → further rollups up to week/month/quarter.
Identities: stable keys (user/brand/legal_entity), mapping tables).

3) Rollups and hierarchies

The dimension hierarchies are Game → Category → Provider, Channel → Campaign → Creative, City → Region → Country → Cluster.
Summation rules: additive (sum), semi-additive (snapshot - average/last for the period), non-additive (coefficients/percentages).
Deduplication - unique users and grain events; avoid double counting when merging sources.

4) Multicurrency, timezones, calendar

Time: store 'event _ time _ utc' and local slices; calendar of holidays/working days.
Currency: Keep the "raw" amount + 'base _ ccy' at the exchange rate on the transaction date (or the average exchange rate of the period with a caveat).
Normalization: Show explicit units/currency/exchange rate date.

sql
WITH fx AS (
SELECT date, from_ccy, to_ccy, rate
FROM dim_fx_rates
WHERE to_ccy = 'EUR'
),
tx AS (
SELECT t. txn_id, t. amount, t. ccy, t. brand, t. country, t. event_date
FROM fact_tx
)
SELECT brand, country, DATE_TRUNC('month', event_date) AS month,
SUM(amount COALESCE(fx. rate, 1)) AS revenue_eur
FROM tx
LEFT JOIN fx
ON fx. date = tx. event_date AND fx. from_ccy = tx. ccy
GROUP BY 1,2,3;

5) Consolidation by source/legal entity

Mapping charts of accounts: single CoA (Chart of Accounts) + mapping from local plans.
Accounting policies: IFRS/GAAP/tax rules → transformation layer (for example, gross/net revenue, recognition of bonuses/commissions).
Elimination of intra-group turnover: exclusion of transactions between legal entities of the holding.
Consolidation methods: full, proportional, equity method.
Minority shareholders: allocation of non-controlling interest (NCI).

sql
WITH interco AS (
SELECT a. txn_id
FROM fact_tx a
JOIN dim_counterparty b ON a. counterparty_id = b. id
WHERE a. legal_entity IN (SELECT id FROM dim_legal WHERE group_id = 1)
AND b. legal_entity IN (SELECT id FROM dim_legal WHERE group_id = 1)
)
SELECT
FROM agg_pl_month
WHERE txn_id NOT IN (SELECT txn_id FROM interco);

6) Quality and reconciliation

Source reconciliations: amount by source = amount in the showcase (with exchange rate/time tolerance).
Invariants: 'DAU ≤ MAU', sum over days = monthly total (for additive metrics).
Completeness control: share of NULL, share of duplicates, lag in freshness.
Discrepancy Report - List of discrepancies and remediation steps.

7) Performance and SLO

Freshness SLO: operational units - lag ≤ 15 min; daytime - until 06:00 local time; monthly - up to T + 1/T + 3.
Optimization: pre-aggregations, incremental recalculations, partitioning (by date/brand/country), cache for popular slices.
UI limitations: ≤ 12 categories per schedule; pagination of tables; lazy-load.

8) Manage definitions and versions

Dictionary of metrics: code, definition, formula, source, units, owners, guardrails.
Versioning: 'METRIC _ vN'; any edits → new version + backfill and changelog.
Semantic layer: one source of truth for BI/experiments/shipments.

9) Security and access

RLS/CLS: access by role (country/brand/legal entity), PII masking.
Audit: who uploaded which report; export control (deadlines, tokens).
Minimization: issue aggregates rather than raw PII data.

10) Typical aggregates and visualizations

Product: funnels (step-bars), cohorts (heatmap), D7/D30 retention, ARPU/ARPPU, GGR/Net.
Finance: P&L by hierarchy, waterfall (bridge) factors, revenue structure by region, G&A dynamics.
Operations/ML: SLA, latency p95/p99, PR- AUC/Recall@FPR≤x%, PSI-heatmap drift.

11) Passport of the consolidated report (template)

Code/Version: 'CONSOL _ PNL _ v3'

Purpose: management P&L by brand group, multicurrency → base currency EUR

Coverage: all legal entities of the group; method - complete consolidation; NCI - Highlighted

Source/layer: 'mart _ fin _ pnl _ v3' (based on 'fact _ tx _ v2', 'dim _ legal', 'dim _ fx _ rates')

Granularity: month (rollup from day)

Eliminations: 'intercompany = true' - intra-group turnover excluded

SLO: T + 1 06:00 lock; availability ≥ 99. 9%

Reconciliations - accounting report'BK _ PNL _ T + 1 ', variance ≤ 0. 3%

Owners: Finance Analytics, Data Platform

Guardrails: FX table not older than 24 hours; coverage transactions ≥ 99. 5%

12) Frequent mistakes and how to avoid them

Quiet formula changes: always through versions and changelog.
Double counting: Duplicate springs/joynes - control keys and grain.
Mixing time zones: centralized calendar and UTC storage.

Incorrect percentages: Aggregate numerator/denominator, not "mean of averages."

"Raw" rates: explicit FX date/source, consistent rounding policy.
No eliminations: Inter-company turnovers skew reports.

Opaque freshness: Always show "updated N minutes ago."

13) Pseudo-SQL: Incremental Monthly Aggregate

sql
-- Recalculate only affected days/months
WITH changed_days AS (
SELECT DISTINCT DATE(event_time_utc) AS d
FROM fact_tx_delta -- new/modified per day
),
daily AS (
SELECT
DATE(event_time_utc) AS d,
brand, country,
SUM(net_revenue_eur) AS net_eur
FROM fact_tx
WHERE DATE(event_time_utc) IN (SELECT d FROM changed_days)
GROUP BY 1,2,3
)
MERGE INTO agg_month_brand_country m
USING (
SELECT DATE_TRUNC('month', d) AS month, brand, country, SUM(net_eur) AS net_eur
FROM daily
GROUP BY 1,2,3
) s
ON (m. month = s. month AND m. brand = s. brand AND m. country = s. country)
WHEN MATCHED THEN UPDATE SET m. net_eur = s. net_eur, m. updated_at = NOW()
WHEN NOT MATCHED THEN INSERT (month, brand, country, net_eur, updated_at)
VALUES (s. month, s. brand, s. country, s. net_eur, NOW());

14) Processes and operation

1. Design: goals/audiences, metrics, hierarchies, currency/timezones.
2. Data: source contracts, schemes, quality tests.
3. Window building: semantic objects, rollup rules, eliminations.
4. Reconciliations: automatic variance reports, correction tickets.
5. Release: version, documentation, user training.
6. Monitoring: freshness, completeness, duplicates, response time, incidents.
7. Revision: quarterly verification of definitions, mappings, FX policies.

15) Pre-Consolidated Statement Checklist

• Metric and hierarchy definitions documented in semantic layer
• Currency conversion and timezones are set up; shows the units and date of the course
• Intra-group revolutions/NCI eliminations implemented (if applicable)
• Invariants and reconciliations with reference sources are carried out in tolerances
• Incremental recalculations and partitioning enabled
• freshness/availability SLOs are set; update statuses are displayed
• RLS/CLS and PII masking are configured; export audit enabled
• Version/changelog and owners specified; have runbook incidents

Total

Aggregation and consolidation is not just 'GROUP BY', but a holistic system: consistent definitions, correct rollups, multi-currency and calendar, eliminations and reconciliations, observability and SLO. By following the described architecture, you turn heterogeneous data into reliable vaults for product, financial and risk management.