Fiche de révision : Introduction à la Business Intelligence et Data Warehouse

📋 Plan du Cours

1. Data Warehouse et Business Intelligence
2. OLTP et OLAP
3. Modélisation multidimensionnelle
4. Construction du Data Warehouse
5. Stockage, optimisation et cloud
6. SQL analytique et opérations OLAP
7. Outils de Business Intelligence
8. KPI et tableaux de bord
9. Visualisation de données
10. Gouvernance, qualité et métadonnées

📖 1. Data Warehouse et Business Intelligence

🔑 Notions clés & Définitions

• Data Warehouse : Un Data Warehouse est une plateforme destinée à collecter et analyser des données provenant de sources hétérogènes pour soutenir l’aide à la décision.
• Business Intelligence : La Business Intelligence regroupe des moyens et des outils pour transformer des données d’entreprise en informations exploitables afin d’aider les décisions.

📝 Points essentiels

• Le Data Warehouse regroupe des données orientées sujet, intégrées, historisées et non volatiles, organisées pour l’aide à la décision.
• L’intégration des sources multiples dans un projet Data Warehouse représente souvent 60 à 90 % de la charge totale du projet.
• Les données historisées du Data Warehouse conservent l’historique et nécessitent un référentiel temps pour exploiter correctement les évolutions.
• Un système décisionnel suit une chaîne avec collecte (datapumping), ETL, stockage (DW/Data Warehouse ou Data Mart selon le besoin), puis analyse et restitution pour les utilisateurs.
• La BI vise à transformer la masse de données en réponses à des questions sur l’activité et son évolution, notamment via des indicateurs de performance comme les KPI.
• La BI s’adresse à la direction générale et aux métiers, avec un ingénieur BI chargé de consolider, transformer et rendre les données exploitables via des outils et dashboards.

📖 2. OLTP et OLAP

🔑 Notions clés & Définitions

• OLTP : Système de traitement orienté transactions qui exécute en ligne des opérations courtes et met rapidement à jour les données opérationnelles.
• OLAP : Système de traitement analytique orienté décision qui sert à explorer de grands ensembles de données pour révéler tendances et modèles.
• Cube OLAP : Modèle multidimensionnel utilisé en OLAP pour organiser les données en axes (dimensions) et faciliter le découpage analytique.
• Systèmes OLTP : Environnements conçus pour gérer une forte concurrence et des temps de réponse faibles lors de transactions simultanées.

📝 Points essentiels

• Les systèmes OLTP privilégient la concurrence élevée, le temps de réponse faible, le traitement en temps réel et des transactions courtes (ajout, mise à jour, suppression).
• Les systèmes OLAP s’appuient sur des structures multidimensionnelles et des requêtes complexes pour analyser efficacement des données historiques et couvrir plusieurs dimensions.
• Dans OLTP, les requêtes sont fréquentes et simples pour des mises à jour rapides, alors qu’en OLAP elles sont moins fréquentes et complexes pour des analyses (ex. comparaison sur plusieurs périodes).
• La conception OLTP utilise des schémas normalisés, tandis que l’OLAP emploie des schémas dénormalisés et des cubes afin d’optimiser les requêtes analytiques.
• Exemples : OLTP avec MySQL, Oracle Database, PostgreSQL, Microsoft SQL Server ; OLAP avec Amazon Redshift, Google BigQuery, Microsoft Azure Analysis Services, Snowflake.
• OLTP et OLAP peuvent fonctionner ensemble en automatisant le passage du transactionnel vers l’analytique pour améliorer la décision et le délai d’obtention des informations.

💡 Astuce mémo

OLTP = Réal (Transactions rapides) ; OLAP = Révèle (Analyses complexes sur historique).

📖 3. Modélisation multidimensionnelle

🔑 Notions clés & Définitions

• Analyse multidimensionnelle : Approche d’analyse où les données sont structurées selon plusieurs axes (temps, géographie, produits, etc.) pour faciliter les décisions.
• Dimension : Axe d’analyse (temps, géographie, produits) composé d’attributs ou membres utilisés comme contexte pour construire les requêtes.
• Hiérarchie de dimension : Organisation des membres d’une dimension en niveaux de granularité, permettant de remonter ou descendre dans le niveau de détail.
• Fait : Sujet analysé exprimé via des mesures, associées à un membre de chaque dimension et identifiées par les clés des dimensions.

📝 Points essentiels

• Dans un schéma décisionnel multidimensionnel, les données sont croisées suivant des axes (temps, localisation, produits) plutôt que stockées uniquement en relations classiques.
• Une hiérarchie regroupe des membres à différents niveaux (exemples : jour/mois/année ; magasin/ville/région/pays) et la granularité correspond au niveau de détail représenté.
• Un fait contient des valeurs de mesures, calculées ou mesurées, et sa table est identifiée par une combinaison unique de clés étrangères vers les dimensions.
• MOLAP précalcule et stocke les données dans un cube (hypercube) pour des analyses rapides, tandis que ROLAP s’appuie sur des requêtes SQL sur une base relationnelle pour des analyses détaillées.
• HOLAP combine MOLAP et ROLAP pour obtenir des résultats rapides issus du cube et des extractions détaillées via la base relationnelle.
• En modélisation dimensionnelle, le modèle en étoile met la table de faits au centre et relie directement des tables de dimensions, tandis que le flocon décompose une dimension en plusieurs niveaux et la constellation regroupe plusieurs schémas en étoile avec dimensions partagées.

💡 Astuce mémo

Étoile = 1 jointure par dimension ; Flocon = on détaille les dimensions ; Constellation = plusieurs étoiles qui partagent des dimensions.

📖 4. Construction du Data Warehouse

🔑 Notions clés & Définitions

• Environnement décisionnel : Un Data Warehouse est un environnement dédié à l’aide à la décision, basé sur des informations historisées organisées par métiers plutôt qu’un simple produit logiciel.
• Étude préalable : L’étude préalable fixe les objectifs du Data Warehouse, son organisation et son contenu à partir des besoins utilisateurs et des projets à réaliser.
• Modélisation multidimensionnelle : La modélisation multidimensionnelle représente les données sous forme de cube avec une table de faits reliée à des tables de dimensions.
• Alimentation du Data Warehouse : L’alimentation transfère les données depuis les systèmes opérationnels vers le Data Warehouse en choisissant les données, les transformations et la périodicité des transferts.

📝 Points essentiels

• Le Data Warehouse contient des données historisées organisées par métiers pour l’aide à la décision et ne se limite pas à un achat de logiciel, car il se construit.
• La construction du Data Warehouse repose sur 3 phases interdépendantes : étude préalable, étude du modèle de données, puis étude de l’alimentation.
• L’étude préalable sélectionne les dimensions typiques (temps, client, produit, magasin) et choisit les mesures de faits, de préférence additives en quantités.
• L’étude de modélisation distingue un niveau conceptuel en cube (dimensions et table des faits) et un niveau logique avec des schémas types comme l’étoile et le flocon.
• L’alimentation est une tâche complexe représentant 60 à 90 % de la charge totale d’un projet DW et doit être faite avec l’administrateur des bases de production.
• Lors de l’alimentation, il faut décider précisément quelles données charger, quelles transformations/vérifications appliquer, ainsi que la périodicité et le moment des transferts.

💡 Astuce mémo

Prévoir–Modéliser–Alimenter : objectifs, cube (faits/dimensions), puis chargement depuis l’opérationnel.

📖 5. Stockage, optimisation et cloud

🔑 Notions clés & Définitions

• Indexation : Une indexation est l’ajout d’une structure d’index pour accélérer l’accès aux données et réduire le temps d’exécution des requêtes.
• Partitionnement : Un partitionnement est la division d’une relation en plusieurs partitions disjointes pour limiter le balayage et accélérer l’évaluation des requêtes.
• Agré gation de données : L’agrégation de données consiste à combiner des données provenant de sources multiples dans un format résumé pour faciliter l’analyse et la décision.
• Compression sans perte : La compression sans perte réduit la taille des données sans supprimer d’information, permettant une reconstruction exacte des données d’origine.
• Entrepôt de données cloud : Un entrepôt de données cloud est un entrepôt centralisé dans le cloud public pour stocker, traiter et intégrer de grands volumes de données, afin de supporter des requêtes BI complexes.

📝 Points essentiels

• L’optimisation d’un entrepôt de données vise à améliorer les performances, l’efficacité et l’évolutivité, notamment en réduisant les coûts de stockage et en améliorant la récupération des données.
• Dans un entrepôt décisionnel, les requêtes portent sur de très grands volumes (requêtes d’intervalle), donc des index comme B+ utilisés en OLTP sont moins adaptés.
• Le partitionnement horizontal découpe une relation en fragments horizontaux satisfaisant des prédicats, tandis que le partitionnement vertical segmente en fragments verticaux (projections) mais impose des jointures pour reconstituer la relation.
• La compression sans perte conserve l’intégralité de l’information (exemples de formats cités : ZIP et PNG), tandis que la compression avec perte réduit la taille en supprimant des données parfois imperceptibles (exemples cités : MP3 et JPEG).
• L’agrégation peut être de type somme, moyenne, comptage, ou maximum/minimum, avec l’objectif de réduire la taille et d’obtenir une vue exploitable pour l’analyse.
• Les entrepôts cloud offrent performance, intégration, sécurité (chiffrement et contrôles d’accès), gestion des coûts à l’usage, évolutivité et accessibilité via Internet.

💡 Astuce mémo

Index = Accès rapide (chemin accéléré), Partition = Moins de table à balayer, Agrégation = Résumé pour décider, Cloud = BI à l’échelle.

📖 6. SQL analytique et opérations OLAP

🔑 Notions clés & Définitions

• GROUP BY : Instruction SQL qui regroupe des lignes selon une ou plusieurs colonnes et produit des résultats agrégés via une fonction d’agrégation.
• ROLLUP : Extension de GROUP BY qui ajoute des lignes de sous-totaux en calculant des agrégats à des niveaux de détail successifs.
• CUBE : Extension de GROUP BY qui génère en plus des sous-totaux de tabulations croisées pour toutes les combinaisons de regroupements.
• Fonctions de fenêtre : Fonctions SQL calculées sur un ensemble de lignes défini par OVER, souvent combinées avec PARTITION BY et ORDER BY.
• Opérations OLAP : Ensemble d’actions sur les cubes pour passer du général au spécifique (drill-down/roll-up) et filtrer ou restructurer les vues (slice/dice/pivot).

📝 Points essentiels

• Les fonctions d’agrégation utilisées avec GROUP BY incluent COUNT, SUM, AVG, MIN et MAX pour produire une valeur par groupe.
• HAVING filtre les groupes produits par GROUP BY, alors que WHERE filtre les lignes avant regroupement.
• ROLLUP produit des lignes de sous-totaux qui apparaissent souvent avec des valeurs NULL dans les colonnes correspondant aux niveaux totaux.
• CUBE produit des tabulations croisées supplémentaires par rapport à ROLLUP pour couvrir toutes les combinaisons de regroupement sur les dimensions choisies.
• Les fonctions de fenêtre utilisent OVER() pour définir la fenêtre, avec une PARTITION BY optionnelle et un ORDER BY qui fixe l’ordre de calcul.
• En OLAP, SLICE fixe une seule dimension pour créer un sous-cube, DICE fixe plusieurs dimensions, et PIVOT fait pivoter la présentation en tableau croisé.

💡 Astuce mémo

GROUP BY = Agréger; ROLLUP = Sous-totaux successifs; CUBE = Croisements de tous les niveaux; Window = Calcul “dans la liste” grâce à OVER().

📖 7. Outils de Business Intelligence

🔑 Notions clés & Définitions

• Outil de Business Intelligence : Un outil BI collecte, traite et analyse des données structurées et non structurées pour produire des rapports et indicateurs exploitables.
• Power BI : Power BI est une suite de BI de Microsoft pour créer des rapports et tableaux de bord à partir de sources multiples, avec analyse et visualisation.
• Power Query : Power Query est la brique ETL de Power BI qui charge et nettoie des données via son langage M.
• QlikSense : QlikSense est un outil BI orienté libre-service, avec exploration associative et des analyses guidées ou personnalisées.
• Tableau : Tableau est un outil BI orienté visualisation, permettant d’identifier rapidement des tendances via une interface de type glisser-déposer.

📝 Points essentiels

• Un outil BI exploite des données structurées et non structurées issues de systèmes internes et externes et les présente sous forme de rapports, tableaux de bord et graphiques.
• Un outil BI permet notamment l’exploration/visualisation, le reporting, l’analyse et des tâches comme l’exploration de texte et l’analyse prédictive.
• La force des outils BI inclut la centralisation des données, l’autonomie des non-spécialistes, et la possibilité de générer des rapports automatisés au lieu de saisir manuellement.
• Un outil BI doit disposer de connecteurs variés et d’une capacité ETL pour charger et transformer les données dans l’outil.
• La pertinence d’un outil BI dépend aussi de son stockage pour comparer les performances sur plusieurs périodes.
• Power BI combine Power Pivot (DAX pour mesures multi- tables), Power Query (ETL en langage M) et Power BI Service pour publier et collaborer en mode cloud.

💡 Astuce mémo

BI = Données → (ETL + connecteurs) → Rapports/TDB; Power BI = Pivot(DAX) + Query(ETL M) + Service(cloud).

📖 8. KPI et tableaux de bord

🔑 Notions clés & Définitions

• Indicateur clé de performance : Un indicateur clé de performance est un indicateur lié à un objectif et utilisé pour mesurer la performance atteinte.
• Métrique : Une métrique est une valeur brute mesurable (exemples : nombre, vitesse) qui devient un KPI lorsqu’elle est reliée à une cible.
• Tableau de bord stratégique : Un tableau de bord stratégique sert à piloter des objectifs à moyen ou long terme en mettant en évidence des facteurs clés de réussite.
• Tableau de bord opérationnel : Un tableau de bord opérationnel suit l’avancement du plan d’action et reflète l’état d’exécution des décisions prises.

📝 Points essentiels

• Un KPI mesure une performance lorsque la métrique est reliée à une cible, par exemple atteindre 10 000 pas par jour ou viser une limite de vitesse.
• Les KPI peuvent éclairer une performance collective (service, département, direction) ou une performance individuelle (dossiers traités, taux de transformation).
• Les limites des KPI incluent notamment la surcharge d’indicateurs, le mauvais alignement avec la stratégie et la manipulation/gaming des chiffres.
• Pour choisir de bons KPI, ils doivent être quantifiables, actionnables, simples, fiables et contrôlables pour déclencher de vraies décisions.
• Un tableau de bord synthétise plusieurs KPI dans des visuels pour mesurer les performances, comparer des périodes, communiquer et réduire les réunions d’analyse.
• Les tableaux de bord se déclinent en stratégique, budgétaire (suivi des prévisions vs réel) et opérationnel (suivi du plan d’action), avec une fréquence de mise à jour adaptée au besoin (ex. commercial quotidien/hebdomadaire, financier trimestriel/semestre).

💡 Astuce mémo

KPI = Q-A-S-F-C : Quantifiables, Actionnables, Simples, Fiables, Contrôlables.

📖 9. Visualisation de données

🔑 Notions clés & Définitions

• Data visualisation : La data visualisation (dataviz) est une communication qui transforme des données brutes en représentations visuelles pour les rendre plus compréhensibles.
• Heatmap : Une heatmap est une carte de fréquentation qui affiche la relation entre deux variables via une matrice colorée selon le nombre d’occurrences.
• Série temporelle : Une série temporelle est un graphique où une donnée est tracée en fonction du temps pour mettre en évidence une évolution à la baisse ou à la hausse.
• Treemap : Un treemap est une représentation proportionnelle en rectangles utilisée pour visualiser la répartition d’une valeur quand le camembert devient peu lisible.

📝 Points essentiels

• Une visualisation vise à faciliter la prise de décision, à appuyer un discours avec des données concrètes et à communiquer clairement.
• Une visualisation réussie doit rester simple et claire, en explicitant la légende avant de commenter le graphique à l’oral.
• Un bon graphique doit porter une idée unique (ex. tendance, valeur négative, seuil) et peut utiliser labels, flèches ou couleurs pour guider l’interprétation.
• Une visualisation interactive permet par exemple de sélectionner des données, de modifier les échelles ou d’obtenir des détails en cliquant sur la légende.
• Pour une évolution, privilégie une courbe/graphique linéaire avec des intervalles de temps petits et beaucoup de données, sinon un diagramme à barres avec des intervalles plus grands et peu de données.
• Pour une relation entre variables, une heatmap place les variables en abscisse et ordonnée et intensifie la couleur quand le nombre d’occurrences augmente.

💡 Astuce mémo

Évolution = courbe (temps petit) ou barres (temps grand) ; Répartition = camembert ou treemap ; Relation = heatmap (couleur = fréquence).

📖 10. Gouvernance, qualité et métadonnées

🔑 Notions clés & Définitions

• Gouvernance des données : La gouvernance des données regroupe des règles, des rôles et des processus pour garantir disponibilité, fiabilité, sécurité et qualité des données dans une organisation.
• Qualité des données : La qualité des données décrit l’état d’un ensemble selon des critères comme l’exactitude, l’exhaustivité, la cohérence, la fiabilité et la validité.
• Gestion des métadonnées : La gestion des métadonnées organise et maintient les informations décrivant les données pour faciliter leur découverte, leur compréhension et leur utilisation correcte.
• Types de métadonnées : Les types de métadonnées regroupent les informations descriptives, structurelles, administratives et opérationnelles utilisées pour comprendre et gérer les données.

📝 Points essentiels

• La gouvernance impose une clarification des responsabilités, une documentation des données et des processus de qualité, sécurité et conformité sur tout le cycle de vie des données.
• La qualité des données repose notamment sur 6 caractéristiques : exactitude, cohérence, validité, exhaustivité, actualité et unicité.
• Les métadonnées servent à créer la confiance en indiquant comment et quand les données entrent dans le système et quelles règles métier ont été appliquées.
• Sans gestion des métadonnées, des silos incohérents apparaissent, avec des informations contradictoires et une difficulté à savoir s’il faut faire confiance aux données.
• La gestion des métadonnées suit un cycle : capture, catalogage/stockage, classification/normalisation puis application de protocoles de gouvernance et de sécurité.
• Les métadonnées se répartissent en descriptives, structurelles, administratives et opérationnelles pour répondre à des besoins distincts de recherche, de relations, de contrôle et de traçabilité.

💡 Astuce mémo

Qualité = E-C-V-E-A-U : Exactitude, Cohérence, Validité, Exhaustivité, Actualité, Unicité.

📅 Repères chronologiques

1. 1996Définition de Bill Inmon du Data Warehouse (Orientées sujet, intégrées, non volatiles et historisées).
2. 1997Présentation du langage MDX dans le cadre de la spécification OLE DB for OLAP (ODBO).
3. 2005Talend existe depuis "2005" (Talend Open Studio / Talend Studio).
4. 2006"Kettle" devient "Pentaho Data Integration" (rejoint la société Pentaho en "2006").
5. 2009Création du Data Quality Assessment Framework (DQAF) par Optum ( "2009" ).
6. 2010BigQuery est proposé par Google depuis "2010".
7. 2012Lancement d'"AWS Redshift" ( "2012" ).

📊 Tableaux de synthèse

Comparaison OLTP vs OLAP

⚠️ Pièges & confusions fréquents

1. Confondre métrique et KPI : une métrique devient KPI uniquement quand elle est reliée à une cible/objectif.
2. Inverser HAVING et WHERE : WHERE filtre les lignes avant GROUP BY, HAVING filtre les groupes produits par GROUP BY.
3. Croire que le DW est un logiciel à acheter : le DW est un environnement construit (données historisées + organisation par métiers).
4. Mélanger OLTP et OLAP : OLTP vise transactions courtes temps réel avec concurrence, OLAP vise analyse historique sur cubes/structures multidimensionnelles.
5. Se tromper sur les différences de modèles dimensionnels : étoile = table des faits au centre avec 1 jointure par dimension, flocon = dimensions décomposées, constellation = plusieurs étoiles avec dimensions partagées.
6. Oublier l’historisation et le référentiel temps du DW : sans date de référence, des évolutions ne peuvent pas être exploitées correctement.
7. Sous-estimer l’alimentation : elle représente une part majeure (60 à 90 %) et impose de décider quoi charger, transformer/vérifier, et à quelle périodicité.

✅ Checklist Examen

1. Définir Data Warehouse : orienté sujet, intégré, non volatil, historisé et organisé pour l’aide à la décision.
2. Expliquer la BI : transformer/consolider des données en informations exploitables pour dirigeants et métiers.
3. Comparer OLTP et OLAP : objectif, type de données, fréquence/complexité des requêtes, schéma (normalisé vs dénormalisé/cubes), critères de performance.
4. Décrire l’architecture décisionnelle : collecte (datapumping) → ETL → stockage (DW/Data Mart) → analyse/restitution.
5. Citer les concepts de modélisation multidimensionnelle : fait, dimension, hiérarchie, granularité, mesures, clés (fait identifié par clés étrangères).
6. Expliquer MOLAP vs ROLAP vs HOLAP : stockage/précalcul dans cube vs requêtes SQL, et compromis performance/détail.
7. Reconnaître les schémas : étoile (1 jointure par dimension), flocon (décomposition de dimensions), constellation (plusieurs étoiles avec dimensions partagées).
8. Lister les phases de construction d’un DW : étude préalable → modélisation (conceptuel cube puis schémas types) → alimentation (choix données, transformations/vérifs, périodicité, complexité).
9. Expliquer ETL (Extract-Transform-Load) et citer les modes d’extraction et de chargement : notification vs extraction progressive vs complète ; chargement initial vs progressif (et streaming vs lots) ; définir aussi ELT comme inversion ETL.
10. Savoir les mécanismes d’optimisation : indexation (limites pour requêtes intervalle), partitionnement (horizontale vs verticale), agrégation (types somme/moyenne/comptage/min-max).
11. Maîtriser SQL analytique : rôle de GROUP BY + fonctions COUNT/SUM/AVG/MIN/MAX, différence WHERE vs HAVING, et effets de ROLLUP et CUBE ; décrire WINDOW avec OVER, PARTITION BY (optionnel) et ORDER BY.
12. Décrire les opérations OLAP et requêtes associées : drill-down/roll-up, slice/dice/pivot (et notion de MDX) et leur but analytique.
13. Expliquer KPI : métrique vs KPI (cible), caractéristiques de bons KPI (Q-A-S-F-C) et limites (surcharge, gaming/manipulation, mauvais alignement).
14. Donner les bonnes pratiques de dataviz : visualisation simple/bonne légende, idée unique, courbe vs barres selon granularité temps, heatmap pour relation entre variables.