Agrégation et rapports consolidés

Agrégation et rapports consolidés

L'agrégation est la conversion des enregistrements détaillés en chiffres consolidés par coupe et par période. Consolidation - Regrouper les agrégats de différentes sources/personnes morales/produits en un seul rapport corrigé (monnaie, politique comptable, élimination). Voici une approche systémique : de la conception des vitrines à l'exploitation des rapports.

1) Défis et types de consolidation

Opération/produit : DAU/WAU/MAU, conversions, chiffre d'affaires par pays/canal/plate-forme.
Financier (Prof. et réglementaires) : P&L, Cash Flow, Balance Sheet, GGR/Net Gaming Revenue, impôts, rapports sur les juristes.
Risque/conformité : antifrod, indicateurs RG, SLA/disponibilité, dérive de données/modèles.
Stratégique : KPI consolidés par holding, portefeuille de marques, régions.

2) Architecture de la couche d'agrégation

Couches de données : 'raw' → 'staging' → 'core' (faits/mesures) → 'marts' (agrégats pour les rapports).
Couche sémantique : définitions de métriques, calendrier, devises, règles rollap.
Grain et time-grain : 'event', 'user _ day', 'txn', 'brand _ country _ day' → d'autres rouleaux jusqu'à la semaine/mois/trimestre.
Identités : clés stables (user/brand/legal_entity), tables de correspondance (mapping).

3) Rollaps et hiérarchies

Hiérarchies des dimensions : 'Jeu → Catégorie → Fournisseur', 'Canal → Campagne → Création', 'Ville → Région → Pays → Cluster'.
Règles de sommation : additive (somme), semi-additive (snapshot - moyenne/dernière par période), non additive (coefficients/intérêts).
Déduplication : utilisateurs uniques et événements par grain ; éviter la double comptabilisation dans la fusion des sources.

4) Multivaluta, temporisations, calendrier

Heure : stockez 'event _ time _ utc' et les tranches locales ; calendrier des jours fériés/esclaves.
Devise : conserver le montant « brut » + 'base _ ccy' au taux de change à la date de l'opération (ou le taux moyen de la période avec clause).
Normalisation : affichez des unités explicites/devise/date de change.

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 par source/juriste

Mapping Comptes Plans : Un seul CoA (Chart of Accounts) + mapping à partir de plans locaux.
Politiques comptables : IFRS/GAAP/règles fiscales → une couche de transformation (par exemple recettes brutes/nettes, reconnaissance des primes/commissions).
Élimination des flux intragroupe : exclusion des transactions entre les juristes de la holding.
Méthodes de consolidation : Total, actionnarial (provisionnel), comptabilité par actionnariat (equity method).
Minoritaires : attribution d'une part des actionnaires non contrôlés (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) Qualité et rapprochement (reconnaissance)

Rapprochements par source : somme par source = somme en vitrine (avec tolérance de taux de change/temps).
Invariants : 'DAU ≤ MAU', somme par jour = total mensuel (pour les métriques additives).
Contrôle de l'exhaustivité : part NULL, part des doublons, arriéré de fraîcheur.
Rapport sur les écarts : liste des incohérences et étapes d'élimination.

7) Performance et SLO

SLO de fraîcheur : unités opérationnelles - lag ≤ 15 min ; les journées sont jusqu'à 06 h heure locale ; les mensuels sont jusqu'à T + 1/T + 3.
Optimisation : avant-agrégation, recalculations incrémentales, partitionnement (par date/marque/pays), cache pour les tranches populaires.
Restrictions d'assurance-emploi : ≤ de 12 catégories par annexe ; pagination des tables ; lazy-load.

8) Gestion des définitions et des versions

Dictionnaire de métriques : code, définition, formule, source, unités, propriétaires, guardrails.
Versioning : 'METRIC _ vN' ; toute modification → la nouvelle version + backfill et changelog.
Couche sémantique : une source de vérité pour les BI/expériences/envois.

9) Sécurité et accès

RLS/CLS : accès par rôle (pays/marque/droit), masque PII.
Vérification : qui et quel rapport a déchargé ; contrôle des exportations (délais, jetons).
Minimisation : émission d'agrégats et non de données PII brutes.

10) Agrégats et visualisations types

Produit : Entonnoirs (step-bars), cohortes (heatmap), rétention de D7/D30, ARPU/ARPPU, GGR/Net.

Finances : P&L par hiérarchie, cascade (pont) des facteurs, structure des revenus par région, dynamique G&A

Opérations/ML : SLA, latitude p95/p99, PR- AUC/Recall@FPR≤x %, dérive PSI-heatmap.

11) Passeport du rapport consolidé (modèle)

Code/version : 'CONSOL _ PNL _ v3'

Objet : P&L de gestion par groupe de marques, multi-monnaie → monnaie de base EUR

Couverture : tous les juristes du groupe ; méthode - consolidation complète ; NCI - se distingue

Source/couche : 'mart _ fin _ pnl _ v3' (basé sur 'fact _ tx _ v2', 'bou _ legal', 'bou _ fx _ rates')

Granularité : mois (rollap du jour)

Éliminations : 'intercompany = true' - les tours intragroupe sont exclus

SLO : T + 1 06:00 lok. ; disponibilité ≥ 99. 9%

Rapprochement : rapport comptable 'BK _ PNL _ T + 1', écart ≤ 0. 3%

Propriétaires : Finance Analytics, Data Platform

Guardrails : table FX ne dépassant pas 24 h ; coverage des transactions ≥ 99. 5%

12) Erreurs fréquentes et comment les éviter

Changements de formules silencieux : toujours à travers les versions et changelog.
Double comptabilité : prises de sources/joyaux - contrôler les clés et le grain.
Mélange de zones temporelles : calendrier centralisé et stockage UTC.
Pourcentages incorrects : agrégez le numérateur/dénominateur plutôt que la « moyenne ».
Cours « crus » : date/source explicite FX, politique d'arrondissements constants.
Pas d'élimination : les mouvements inter-entreprises faussent les rapports.
Fraîcheur opaque : toujours montrer « mis à jour il y a N minutes ».

13) Pseudo-SQL : agrégat mensuel incrémental

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) Processus et fonctionnement

1. Conception : objectifs/auditoires, métriques, hiérarchies, devises/temporisations.
2. Données : contrats sources, schémas, tests de qualité.
3. Construction de vitrines : objets sémantiques, règles rollap, éliminations.
4. Rapprochements : rapports automatiques de divergence, tickets de correction.
5. Sortie : version, documentation, formation des utilisateurs.
6. Surveillance : fraîcheur, completeness, doublons, temps de réponse, incidents.
7. Vérification : vérification trimestrielle des définitions, des mappings, des politiques FX.

15) Chèque avant la publication du rapport consolidé

• Les définitions des métriques et des hiérarchies sont documentées dans la couche sémantique
• Mise en place de la conversion des devises et des temporisations ; les unités et la date du cours sont indiquées
• Élimination des rotations intragroupe/NCI (le cas échéant)
• Les invariants et les rapprochements avec les sources de référence se déroulent dans les tolérances
• Les recalculations incrémentielles et les partitions sont incluses
• SLO de fraîcheur/disponibilité spécifiée ; les statuts de mise à jour sont affichés
• RLS/CLS et le masquage PII sont configurés ; audit des exportations inclus
• La version/changelog et les propriétaires sont indiqués ; il y a un runbook d'incidents

Résultat

L'agrégation et la consolidation ne sont pas seulement un « GROUP BY », mais un système holistique : définitions cohérentes, rollaps corrects, multivalut et calendrier, éliminations et rapprochements, observabilité et SLO. En suivant l'architecture décrite, vous transformez des données hétérogènes en baies fiables pour la gestion des produits, des finances et des risques.