36 0 1MB
Business Intelligence Chapitre 5 -Les entrepôts des données et ETL avec PyGramETL D r. Z a h r a KO D I A A O U I N A 2 ÈME L F I G
19/11/2016
DR. ZAHRA KODIA AOUINA
1
Chapitre 5 -Les entrepôts des données et ETL avec PyGramETL
PARTIE I : MODÉLISATION ET CRÉATION DES ENTREPÔTS DES DONNÉES 19/11/2016
DR. ZAHRA KODIA AOUINA
2
DW : Définitions et notions de base Entrepôt de Données : Définition (Bill Inmon) Le Data Warehouse est une collection de données orientées sujet, intégrées, non volatiles et historisées, organisées pour la prise de décision. C’est est une vision centralisée et universelle de toutes les informations de l'entreprise. C'est une structure (comme une base de données) qui a pour but,
contrairement aux bases de données, de regrouper les données de l'entreprise pour des fins analytiques et pour aider à la décision stratégique.
19/11/2016
DR. ZAHRA KODIA AOUINA
3
DW : Définitions et notions de base Au cœur du système décisionnel
19/11/2016
DR. ZAHRA KODIA AOUINA
4
19/11/2016
DR. ZAHRA KODIA AOUINA
5
19/11/2016
DR. ZAHRA KODIA AOUINA
6
19/11/2016
DR. ZAHRA KODIA AOUINA
7
Caractéristiques d’un DW Données Orientées Sujets Le DW est orienté sujets, ce qui signifie que les données collectées doivent être orientées métier, et donc triées par thème
Bases de données 19/11/2016
Entrepôt de données DR. ZAHRA KODIA AOUINA
8
Caractéristiques d’un DW Données Intégrées Le DW est composé de données intégrées, c’est à dire qu’un « nettoyage » préalable des données est nécessaire dans un souci de rationalisation et de normalisation
Entrepôt de données
Bases de données 19/11/2016
DR. ZAHRA KODIA AOUINA
9
Caractéristiques d’un DW Données Historisées et Non Volatiles Les données du DW sont non volatiles, ce qui signifie qu’une donnée entrée dans l’entrepôt l’est pour de bon et n’a pas vocation à être supprimée
Les données du DW doivent être historisées, donc datées
Bases de données 19/11/2016
Entrepôt de données DR. ZAHRA KODIA AOUINA
10
DW : Définitions et notions de base Entreposage des données
19/11/2016
DR. ZAHRA KODIA AOUINA
11
DW : Définitions et notions de base Entreposage des données
ODS: Operational Data Store ◦ Collection de données orientées sujet, volatiles, organisées pour le support d’un processus de décision ponctuel, en support à une activité opérationnelle particulière ◦ Donne la vision immédiate et intégrée de l’état d’un ou plusieurs systèmes opérants Data Warehouse ◦ Entrepôt de données spécifique au monde décisionnel, destiné principalement à analyser les leviers business potentiels ◦ Collection de données orientées sujet, intégrées, non volatiles et historisées, organisées pour le support d’un processus d’aide à la décision Data Mart ◦ Magasin de données orienté sujet, non volatile, mis à la disposition des utilisateurs dans un contexte décisionnel décentralisé, ciblé pour un usage particulier 19/11/2016
DR. ZAHRA KODIA AOUINA
12
DW : Définitions et notions de base DW vs. ODS Data Warehouse ◦ Intégration des données hors ligne
ODS ◦ Intégration des données en ligne ◦ Sauvegarde des données récentes ◦ Utilisé quand les données sont dispersées sur plusieurs supports de stockage, et on a besoin de les rassembler
Exemple: ◦ On veut avoir une vue unique sur un patient qu’on pourra modifier en ligne ◦ Les données de ce patient sont disposées dans plusieurs bases de données (liste des hospitalisations, liste des diagnostics, liste des achats pharmaceutiques…) ◦ ODS peut être utilisé pour extraire ces données et les afficher 19/11/2016
DR. ZAHRA KODIA AOUINA
13
DW : Définitions et notions de base DW vs. Data Mart Data Warehouse ◦ ◦ ◦ ◦
Dépôt de données au niveau entreprise Combinaison de plusieurs Data Marts Contient toutes les mesures et dimensions nécessaires Assure l’intégrité de ces mêmes dimensions à travers tous les Data Marts
Data Mart ◦ Ensemble de dimensions et mesures limitées ◦ Utilisées pour des thèmes métier spécifiques ◦ Construites à partir des données des entrepôts
Exemple ◦ Dans une entreprise, il existe un seul entrepôt de données mais plusieurs magasins de données : Finance, Vente… 19/11/2016
DR. ZAHRA KODIA AOUINA
14
Modèles de Données
19/11/2016
DR. ZAHRA KODIA AOUINA
15
DW : Différence entre SGBD et DW SGBD et DW ◦ Ont des objectifs différents et font des traitement différents ◦ Stockent des données différentes ◦ Font l’objet de requêtes différentes Doivent être physiquement séparés
SGBD Mode de travail transactionnel (OLTP) Permettent d’insérer, modifier, interroger des informations rapidement, efficacement et en sécurité : Sélectionner, ajouter, mettre à jour et supprimer des tuples Opérations rapides, faites par plusieurs utilisateurs simultanément 19/11/2016
DR. ZAHRA KODIA AOUINA
16
DW : Structure (1/3) 1. Directe simple : ◦ On fait des mises à jour du DW avec des laps de temps importants.
19/11/2016
DR. ZAHRA KODIA AOUINA
17
DW : Structure (2/3) 2. Cumul simple : ◦
19/11/2016
On stocke les données de chaque mise à jour, les mises à jour étant fréquentes (par exemple tous les jours) on a un espace occupé important, mais on ne perd pas d’information.
DR. ZAHRA KODIA AOUINA
18
DW : Structure (3/3) 3. Résumé déroulant : ◦ À chaque mise à jour, on stocke des données détaillées, et on synthétise les anciennes données en fonction de leur âge. Plus une donnée est vieille, moins elle est détaillée.
19/11/2016
DR. ZAHRA KODIA AOUINA
19
Modélisation Multidimensionnelle Notions de Base Méthode de conception logique qui vise à présenter les données sous une forme standardisée, intuitive et qui permet des accès hautement performants Permet de considérer un sujet analysé comme point dans un espace à plusieurs dimensions Les données sont organisées de manière à mettre en évidence: ◦ Le Sujet ◦ Les perspectives de l’analyse
19/11/2016
Le Fait La table des dimensions
DR. ZAHRA KODIA AOUINA
20
Modélisation Multidimensionnelle Caractéristiques Lisibilité Performances (chargement + exécution des requêtes)
Évolutivité Redondances envisageables ◦ Pas de mise à jour en ligne (chargement uniquement) ◦ Pas de problème d’intégrité des données (contrôles à l’acquisition) ◦ Privilégier l’accessibilité plutôt que la normalisation
Requêtes ensemblistes, portant sur de gros volumes de données ◦ Projections, restrictions, regroupements, agrégations ◦ Adaptation du modèle pour des requêtes ad-hoc ◦ Techniques d’optimisation basées sur les chemins d’accès
Pré-calcul de certains agrégats + dénormalisation 19/11/2016
DR. ZAHRA KODIA AOUINA
21
Modélisation Multidimensionnelle Dimension Une dimension peut être définie comme un thème, ou un axe (attributs), selon lequel les données seront analysées. Dimensions : les axes avec lesquels on veut faire l'analyse.
Exemple :dimension client, une dimension produit, une dimension géographie (pour faire des analyses par secteur géographique), etc. Une dimension est tout ce qu'on utilisera pour faire nos analyses. Remarque : L'axe du temps (dimension Temps) est toujours présent dans un entrepôt de données, c'est le type d'analyse le plus commun et le plus fréquent en entreprise. 19/11/2016
DR. ZAHRA KODIA AOUINA
22
Modélisation Multidimensionnelle Dimension Table dimension contient une clé primaire unique qui correspond à l’un des composants de la clé multiple de la table des faits Les tables dimensionnelles sont les points d’entrée de l’entrepôt de données Les dimensions ◦ Thème (ou axe) selon lequel les données sont analysées ◦ En général sous forme textuelle ◦ Parfois discrète (ensemble limité de valeurs): couleurs, parfums
A en général plusieurs colonnes et peu de lignes 19/11/2016
DR. ZAHRA KODIA AOUINA
Produit Clé Produit Description produit Description marque Description catégorie Description type emballage Taille emballage Poids Unité de mesure du poids Type de stockage Type de durée rayon Largeur sur étagère Hauteur sur étagère Profondeur sur étagère
23
Modélisation Multidimensionnelle Dimension - Caractéristiques Dimension ◦ Temps, Produit, Géographie, ...
Niveau : hiérarchisation des dimensions ◦ Produit : Rayon, Catégorie, Nature,... ◦ Géographie : Région, Département, Ville, Magasin, …
Membres d'un Niveau ◦ Produit::Rayon ◦ Produit::Rayon.Catégorie ◦ Produit::Rayon.Catégorie.Nature
: Frais, Surgelé, ... , Liquide : Frais.Laitage, ... , Liquide.Jus : Frais.Laitage.Yaourt, ... , Liquide.Jus.Orange
Cellule ◦ Intersection des membres des différentes dimensions
Formule ◦ calcul, expression, règle, croisement des dimensions ◦ Somme(Qte), Somme(Qte*PrixVente), Moyenne(Qte*(PrixVente-PrixAchat)), ... 19/11/2016
DR. ZAHRA KODIA AOUINA
24
Modélisation Multidimensionnelle Dimension Temps Commune à tout entrepôt Reliée à toute table de fait 2 choix d’implantation ◦ Type SQL DATE ◦ Calendrier + Table Temps ◦ Informations supplémentaires ◦ Évènement (match de finale de coupe du monde) ◦ Jours fériés, vacances, période fiscale, ◦ saison haute ou basse, …
19/11/2016
DR. ZAHRA KODIA AOUINA
25
Modélisation Multidimensionnelle Faits Une mesure est un élément de donnée sur lequel portent les analyses, en fonction des différentes dimensions ◦ Ex : coût des travaux, nombre d’accidents, ventes
Un fait représente la valeur d’une mesure, calculée selon un membre de chacune des dimensions Exemple : ◦ « 250 000 euros » est un fait qui exprime la valeur de la mesure « coût des travaux » pour le membre « 2012 » du niveau année de la dimension « temps » et le membre « Versailles » du niveau « ville » de la dimension « découpage administratif »
19/11/2016
DR. ZAHRA KODIA AOUINA
26
Modélisation Multidimensionnelle Faits Les faits sont les données sur quoi va porter l'analyse. Ce sont des tables qui contiennent des informations opérationnelles et qui relatent la vie de l'entreprise.
Un fait est tout ce qu'on voudra analyser. Exemple : Tables de faits « Ventes »: chiffre d'affaire net, quantités et montants commandés, quantités facturées, quantités retournées, volumes des ventes, etc. Tables de faits « Stocks » : nombre d'exemplaires d'un produit en stock, niveau de remplissage du stock, taux de roulement d'une zone, etc., Tables de faits « ressources humaines » : performances des employés, nombre de demandes de congés, nombre de démissions, taux de roulement des employés, etc. 19/11/2016
DR. ZAHRA KODIA AOUINA
27
Modélisation Multidimensionnelle Faits Fait ◦ Sujet d’analyse ◦ Grain de mesure de l’activité ◦ Résultat d’une opération d’agrégation des données ◦ Exemple: Chiffre d’affaires, nombre de vente, gain, nombre de transaction… en général : une valeur numérique ◦ Les mesures sont stockées dans la table des faits
Table des faits ◦ Clé composite référencent des clés primaires des tables de dimensions ◦ Contient les valeurs des mesures et des clefs vers les tables de dimensions traduit une relation (n,m) entre les dimensions ◦ Plusieurs tables de fait dans un DW ◦ Les faits les plus utiles d’une table des faits sont numériques et additifs 19/11/2016
DR. ZAHRA KODIA AOUINA
28
Modélisation Multidimensionnelle Faits Fait additif : ◦ Additionnable suivant toutes les dimensions ◦ Exemples: quantité vendue, chiffre d’affaire, coût
Fait semi-additif : ◦ Additionnable selon certaines dimensions ◦ Exemples: Niveau de stock (excepté sur la dimension temps), Nombre de transactions, de clients (excepté sur la dimension produit)
Fait non-additif : ◦ Non additionnable ◦ Exemple: attribut ratio (marge brute = 1- Coût/CA) 19/11/2016
DR. ZAHRA KODIA AOUINA
29
Modélisation Multidimensionnelle Faits Exemple: ◦ Fait: Montant et quantité des ventes, chaque jour pour chaque produit dans chaque magasin
A en général plusieurs lignes et peu de colonnes Table des Faits Date
Dimensions Produit
19/11/2016
Ventes Journalières Clé Date Clé Produit Clé Magasin Quantité vendue Montant des ventes
DR. ZAHRA KODIA AOUINA
Magasin
Faits
30
Modèles d’un DataWarehouse Modèle en étoile
Modèle en flocon de neige Modèle en constellation
19/11/2016
DR. ZAHRA KODIA AOUINA
31
Modèles d’un DataWarehouse Modèle Étoile Une (ou plusieurs) table(s) de faits comprenant une ou plusieurs mesures Plusieurs tables de dimension dé-normalisées: descripteurs des dimensions. Les tables de dimension n'ont pas de lien entre elles.
Avantages ◦ Facilité de navigation. ◦ Performances : nombre de jointures limité ; gestion des données creuses. ◦ Gestion des agrégats
Inconvénients ◦ Redondances dans les dimensions. ◦ Alimentation complexe.. 19/11/2016
DR. ZAHRA KODIA AOUINA
32
Modèles d’un DataWarehouse Modèle en Étoile La syntaxe générale de ces requêtes est la suivante : SELECT FROM WHERE GROUP BY
19/11/2016
DR. ZAHRA KODIA AOUINA
33
Modèles d’un DataWarehouse Modèle en Étoile - Exemple Produit Code_pdt Description Couleur Marque Créateur
Période Code_per Année Trimestre Mois Jour
19/11/2016
Ventes Code_produit Code_période Code_Magasin
Unités_vendues Montant_ventes Montant_coût
DR. ZAHRA KODIA AOUINA
Magasin Code_mag Nom_mag Ville Téléphone Manager
34
Modèles d’un DataWarehouse Modèle en Flocon de Neige Dérivé du schéma en étoile où les tables de dimensions sont normalisées ◦ La table des faits reste inchangée
Chacune des dimensions est décomposée selon sa (ou ses) hiérarchie(s) Exemple : Commune, Département, Région, Pays, Continent Utilisé lorsque les tables sont très volumineuses Avantages ◦ Réduction du volume ◦ Permettre des analyses par pallier (drill down) sur la dimension hiérarchisée
Inconvénients ◦ Navigation difficile ◦ Nombreuses jointures
19/11/2016
DR. ZAHRA KODIA AOUINA
35
Modèles d’un DataWarehouse Modèle en Flocon de Neige - Exemple Marque Code_marque Nom Description Créateur
Produit Code_pdt Description Couleur Code_marque
Période Code_per Année Trimestre Mois Jour
19/11/2016
Ventes Code_produit Code_période Code_Magasin Unités_vendues Montant_ventes Montant_coût
DR. ZAHRA KODIA AOUINA
Magasin Code_mag Nom_mag Ville Téléphone Manager
36
Modèles d’un DataWarehouse Modèle en Constellation Fusionner plusieurs modèles en étoile qui utilisent des dimensions communes
Un modèle en constellation comprend donc : ◦ Plusieurs tables de faits ◦ Des tables de dimensions communes ou non à ces tables de faits.
19/11/2016
DR. ZAHRA KODIA AOUINA
37
Modèles d’un DataWarehouse Modèle en Constellation - Exemple Produit
Fournisseur
Achats
Code_four Nom Adresse Catégorie
Code_produit Code_période Code_fournisseur
Unités_achetées Montant_achats Montant_remises
19/11/2016
Code_pdt Description Couleur Marque Créateur
Période Code_per Année Trimestre Mois Jour
DR. ZAHRA KODIA AOUINA
Ventes
Code_produit Code_période Code_Magasin Unités_vendues Montant_ventes Montant_coût
Magasin Code_mag Nom_mag Ville Téléphone Manager
38
Modèles d’un DataWarehouse Synthèse Modèle en étoile ◦ Taille de dimension plus grosse
Modèle en flocon de neige ◦ Jointures pour reconstruire
Modèle en étoile >> Modèle en flocon ◦ car tables de dimension