Architecture de référence : data warehouse / analyse

Les requêtes analytiques (reporting, agrégation, parcours larges) diffèrent fondamentalement des transactions OLTP : peu nombreuses mais très larges et longues. En partant de la décision de séparer l'analyse de l'OLTP, cet arbre parcourt pas à pas des composants comme la méthode de chargement (ETL vs ELT), le stockage en colonnes/columnstore, la modélisation en schéma en étoile, le partitionnement + la compression et le précalcul par vues matérialisées, tout en pesant le compromis entre concurrence et débit.

Votre charge analytique s'exécute-t-elle sur la même base que l'OLTP ?

L'erreur la plus courante consiste à exécuter des rapports lourds sur la base transactionnelle en production et à dégrader la latence OLTP. La première décision est la séparation : l'analyse s'exécute-t-elle encore sur l'instance OLTP, construisez-vous un environnement analytique distinct, ou décidez-vous comment y transférer les données ?

• Les rapports s'exécutent encore sur l'OLTP en production ; ils ralentissent l'OLTP
• Un entrepôt distinct existe ; comment charger les données ?
• Un entrepôt existe ; les requêtes restent lentes, comment modéliser ?

Où transformerez-vous : avant ou après le chargement ?

En transférant les données des sources vers l'entrepôt, ferez-vous le nettoyage/la transformation dans une couche de traitement distincte avant le chargement (ETL), ou chargerez-vous d'abord les données brutes dans l'entrepôt pour les transformer en SQL au sein du puissant moteur de l'entrepôt (ELT) ? La décision dépend de la puissance de traitement de l'entrepôt et du besoin de flexibilité du schéma.

• La transformation est complexe/externe ; faites-la avant le chargement
• L'entrepôt est puissant ; chargez en brut, transformez à l'intérieur en SQL

Le goulot des requêtes se situe-t-il dans l'organisation du stockage ou dans le modèle de données ?

Si l'analyse est lente, il existe deux leviers principaux. Les parcours larges lisent la ligne entière en stockage par lignes ; le stockage en colonnes ne lit que les colonnes nécessaires et se compresse bien. Côté modèle, une normalisation excessive multiplie les jointures qui ralentissent les requêtes ; un schéma en étoile (fait + dimension) simplifie les choses pour l'analyse.

• Les parcours d'agrégation larges sont lents ; peu de colonnes sont lues
• Trop de jointures, schéma normalisé complexe
• Le même rapport lourd produit le même résultat de façon répétée

Recommandation : séparez l'analyse de l'OLTP

Les charges transactionnelles et analytiques s'étouffent mutuellement sur le même moteur : les longs rapports retiennent verrous/E/S et retardent les transactions courtes. Déplacer l'analyse vers une cible distincte (réplica de lecture ou entrepôt analytique séparé) soulage les deux côtés.

Recommandation : ETL (transformer avant de charger)

En ETL, les données sont extraites des sources (Extract), nettoyées et transformées dans une couche de traitement distincte (Transform), puis seulement chargées dans l'entrepôt (Load). Seules des données propres et conformes au modèle entrent dans l'entrepôt.

Recommandation : ELT (transformer dans l'entrepôt)

En ELT, les données brutes sont d'abord chargées dans l'entrepôt et la transformation s'effectue dans le puissant moteur SQL de l'entrepôt lui-même. Comme les entrepôts en colonnes modernes ont une forte puissance de traitement, vous déplacez les données une fois et les traitez sur place au lieu de porter la transformation vers les données.

Recommandation : stockage en colonnes / columnstore + compression

Les requêtes analytiques parcourent généralement quelques colonnes sur toutes les lignes. Le stockage en colonnes conserve les données colonne par colonne ; il ne lit que les colonnes nécessaires, se compresse très bien car les valeurs similaires sont voisines, et les E/S chutent considérablement.

Recommandation : schéma en étoile (fait + dimension)

Dans un modèle analytique, une table de faits contenant les mesures se trouve au centre, entourée de tables de dimensions descriptives. Par rapport à un schéma sur-normalisé, le nombre de jointures diminue, les requêtes deviennent plus simples et le moteur produit de meilleurs plans.

Recommandation : précalcul avec des vues matérialisées

Si la même agrégation lourde (chiffre d'affaires quotidien, synthèse mensuelle) s'exécute de façon répétée et produit le même résultat, stockez physiquement le résultat dans une vue matérialisée et faites lire aux rapports le résultat précalculé. Le calcul lourd s'exécute une fois et les requêtes répondent instantanément.