ETL et entrepôt de données

[email protected]
 ETL et entrepôt de données

Objectifs:

- Comprendre la différence entre le monde opérationnel et décisionnel .

- Etudier les composantes d’une architecture de BI.
- Définir les concepts fondamentaux ( datawarehouse, datamart, dimension, fait …).
- Utiliser des outils ETL: Extraction, Transformation, Loading.
- Démarrer notre projet talend

[email protected]
 ETL et entrepôt de données

Plan du cours: Modélisation en étoile (cas, analyse et solution)

Introduction Modélisation en flocon (cas, analyse et solution)
- Maturité de l’informatique décisionnelle Le serveur d’analyse OLAP (OnLine Analytical
(BI) Processing)
- Décisionnel/Opérationnel Débuter avec un projet talend
- Les composants d’une architecture de BI - Environnement de travail
Les concepts fondamentaux - Ajout de variables de contexte
- Entrepôt de données (datawarehouse) - Définition des types de variables
- Magasin de données (datamart) - Gestion des métadonnées
- Dimension - Gestion des logs
- Fait
- ETL (Extract Transform Load)

[email protected]
 ETL et entrepôt de données

Organisation du travail:

- Séances de cours chaque Jeudi après-midi

- Séances de TP en 3 groupes

Examen+ Comptes rendus avec respect des délais

[email protected]
 Introduction et contextualisation
- Maturité de l’informatique décisionnelle (BI)
Les centres de
Le commencement Le reporting
données
(Les années 70) Les années 90
(Les années 80)
- L’informatique est un luxe - Les entreprises continuent à s’informatiser. - Les informaticiens pensent à des
amusant aux grandes boites. - Les plus malignes commencent à accumuler logiciels de génération de rapports
- Le besoin en information ses données de production. paramétrables (à base de menus)
commence à apparaitre suite à - Des départements informatiques gérant ces  Forte demande d’information:
la concurrence. historiques apparaissent. les utilisateurs indépendants et surcharge
- Les entreprises assuraient leur - Avec le cumul, les patrons et les analystes la base de production et son système qui
gestion à la main. veulent fouiller dans les données afin sont conçues pour des opérations
- Les patrons commencent à d’améliorer la rentabilité. élémentaires (ajouter, supprimer,
comprendre qui détient un processus question réponse voit le jour modifier)
l’information, détient le marché. entre patrons et informaticiens.  Insatisfaction des décideurs:
 Surcharge des informaticiens / systèmes de L’information est plus disponible mais
production difficile à exploiter.
 Rapports trop grand public/
personnalisation
 Introduction et contextualisation
- Maturité de l’informatique décisionnelle (BI)

- Comment aider les décideurs à prendre des décisions ?

- Comment assouvir leur soif d'information et leur permettre de fouiller eux même dans les
données pour trouver ce qu'ils cherchent?

 Il fallait un environnement, et non un système.

Les analystes et décideurs ne savent pas ce qu'ils cherchent.

Ils analysent l'entreprise pour l'améliorer, ils peuvent avoir des pistes, des doutes, des
points de départ, mais jamais rien de concret.
Un processus de input - output ne serait donc pas pertinent pour eux.
 Introduction et contextualisation
- Maturité de l’informatique décisionnelle (BI)
Que doit avoir cet environnement pour aider les décideurs à décider ?

• simple : les décideurs ne sont pas doués en informatique. L'environnement doit donc être simple et intuitif pour
être manipulé par des non-informaticiens ;

• rapide : le temps des décideurs est précieux. La prise de décision au niveau des analystes et des patrons se fait
à un très haut niveau d'abstraction. Ils analysent les tendances sur de longues durées pour déterminer des
actions à entreprendre. L'environnement doit pouvoir gérer de très gros volumes de données ;

• indépendant du système de production : plus question de faire planter le système de production à cause d'une
requête faite par un analyste ou décideur ;

• pour un membre restreint d'utilisateurs : en effet, la prise de décision n'est la responsabilité que de quelques
personnes dans l'entreprise. Le sommet de la pyramide ;

• fiable et hétérogène : l'environnement doit pouvoir compiler toutes les sources de données que possède
l'entreprise tout en assurant la fiabilité.
 Introduction et contextualisation
- Maturité de l’informatique décisionnelle (BI)

- Séparation du décisionnel et du transactionnel.

- Les systèmes d'opération sont faits pour opérer et non pour prendre des décisions stratégiques.

 Naissance du BI

--- Pour un meilleur sens d’analyse, listons les caractéristiques de chaque mode
 Introduction et contextualisation
- Décisionnel / Opérationnel
- OLTP (Online Transactional Processing)

• Les systèmes informatiques opérationnels (OLTP pour OnLine Transactional Processing) sont faits pour assister les
opérations d'une entreprise.
• Ce sont des systèmes de gestion ou de production qui relatent la vie quotidienne de l'entreprise (les opérations) dans
un environnement informatique, plus restreint, mieux gérable et plus flexible.

Grand public
• destinés à toute personne participant à la vie quotidienne de l'entreprise.
• décideurs sont exclus du groupe, car ils participent à un niveau plus élevé que la gestion quotidienne.

Données atomiques
• Ce sont des éléments avec un grain très fin. (Ajouter, modifier, supprimer)

Extrêmement rapides
• Leur réaction doit être rapide: (Système de gestion de panne, Système de génération de facture client…)
 Introduction et contextualisation
- Décisionnel / Opérationnel
- OLTP (Online Transactional Processing)

Petite volumétrie des données

• Ces systèmes s'intéressent à ce qui se passe maintenant.

Transactionnels
• Ces systèmes fonctionnent en utilisant le principe de transaction.

Projets comportant peu de risques

• Les fonctionnalités et les besoins sont évidents, il y'a moins de risque d'échecs.

Fragmentés
• on entend par ici décentralisés.

Hétérogènes
les systèmes OLTP sont souvent des systèmes disparates en termes de technologie utilisée.
Il n'est pas rare d'avoir dans la même entreprise un système de gestion avec une base de données MySQL et développé en
Java et un système de production avec une BD oracle et développé en php.
 Introduction et contextualisation
- Décisionnel / Opérationnel
Tandis que les OLTP font rouler l'entreprise, l'informatique décisionnelle voit rouler
l'entreprise pour agir.

Les systèmes d'aide à la décision et de Business Intelligence incorporent l'ensemble des

méthodes, moyens et outils informatiques utilisés pour piloter une entreprise et aider à la
décision.

Les environnements d'aide à la décision s'intéressent aux:

tendances, moyennes, écarts types des principaux indicateurs de performance de l'entreprise, et
ce, à travers les mois ou les années.

N.B: une tendance se dégage avec le temps.

Le monde décisionnel analyse, prédit, conseille, regarde de haut les données de l'entreprise pour
mieux apprécier l'ensemble de l'activité.
 Introduction et contextualisation
- Décisionnel / Opérationnel
Les systèmes d'aide à la décision et de Business Intelligence

Petit nombre d'utilisateurs

l'aide à la décision stratégique est le lot de quelques personnes dans l'entreprise (décideurs, patrons)

Données générales et détaillées

L’intérêt s’orientent aux chiffres par mois, par année, par groupe de produit, etc. Les décideurs n'ont pas intérêt à voir la
commande de tel ou tel client. Ils veulent voir l'ensemble de l'activité. Par contre, les analystes ont tout intérêt à pouvoir
creuser dans les données pour trouver des fraudeurs par exemple.

Rapidité suggérée
Il est clair que plus c'est rapide et mieux c'est ! Mais dans la prise de décision stratégique, on ne calcule pas à la seconde.
Un décideur peut bien attendre quelques heures pour avoir une information très complexe à créer. Mais dans la plupart
des cas, les temps de réponse doivent être calculés en secondes.

Ouverts
libre cours à la curiosité des utilisateurs, les environnements de BI doivent permettre d'accéder le plus simplement
possible aux données et d'en faire tout ce qu'on veut !
 Introduction et contextualisation
- Décisionnel / Opérationnel
Les systèmes d'aide à la décision et de Business Intelligence

Gros volumes de données

les environnements de BI doivent regrouper toutes les données de l'entreprise, des années et des années d'accumulation de
données.

Non transactionnels
pas de processus rigide ici.
L'utilisateur doit pouvoir commencer une analyse, revenir en arrière, démarrer une autre analyse en parallèle.

Données en lecture seule

pas de perte de données dans le monde décisionnel.
Pas de suppression des données, pas de modification mais plutôt de l’archivage et la traçabilité.

Centralisés
toutes les données sont regroupées en un seul point. Une même source pour tout le monde.
 Introduction et contextualisation
- Les composantes d’une architecture de BI

Datawarhouse
Une architecture de BI est un ensemble de
concepts, outils, méthodes, et technologies
(logicielles et matérielles) qui, une fois mis en Outil
relation, permettent de créer de la end- ETL
connaissance et répondre aux besoins user
stratégiques de l'entreprise.
Serveur
d’analyse OLAP
 Introduction et contextualisation
- Les composantes d’une architecture de BI
Entrepôts de données (datawarehouse)

Un Datawarehouse est l'ensemble des données:

- historiées, nettoyées, valides, complètes et cohérentes d'une entreprise.
- organisées de telle façon à ce que des non-informaticiens puissent en comprendre la structure et
l'exploiter, sans l'intervention d'un informaticien.

Les grands du BI (Inmon, Kimball) définissent un entrepôt de données par ses caractéristiques.

Orienté métier
les données sont organisées par fonction dans l'entreprise (comptabilité, stocks, ventes, etc.).

En lecture seule
c'est le point crucial, pas de suppression dans un entrepôt puisque, sa raison d'exister est de
conserver tout changement.
 Introduction et contextualisation
- Les composantes d’une architecture de BI
Entrepôts de données (datawarehouse)

Organisé en axes
Les données sont organisées en axes d'analyses (dimensions) et objets d'analyse (fait).
Une dimension est un axe avec lequel un phénomène dans l'entreprise (fait) est analysé.

Intégrées
Tous les systèmes stockant des données dans l'entrepôt sont des sources potentielles de données.
(Feuilles de calculs, systèmes de production…)
L'entrepôt intégrera ces éléments pour former une vision unique de l'activité de l'entreprise.

Différents niveaux de granularité

L'entrepôt doit être capable de livrer des informations aussi détaillées (ligne de facture) que
générales (chiffre d'affaires pour une année), et ce de la façon la plus transparente possible.
 Introduction et contextualisation
- Les composantes d’une architecture de BI
ETL (Extract, Transform, Load)

E Extraction, T Transformation, L Loading.

C'est un système par lequel vont passer toutes les données des systèmes opérationnels avant d'arriver dans la forme
souhaitée dans l'entrepôt.

Les données en sortie seront:

- nettoyées, purifiées (les gros morceaux seront mis de côté)
- contextualisées (les données des différents systèmes s'homogénéiseront)
- prêtes à être reçues dans l'entrepôt.

 Le système d'ETL est la partie la plus importante d'un projet décisionnel.

 C'est avec l'ETL que les systèmes seront mis en relation, les erreurs détectées, les calculs complexes effectués, etc.
 Introduction et contextualisation
- Les composantes d’une architecture de BI
- OLAP: OnLine Analytical Processing

OLAP sont les bases de données multidimensionnelles (aussi appelées cubes ou hypercubes) destinées à des analyses
complexes sur ses données.

Ce terme a été défini par Ted Codd en 1993 au travers de 12 règles que doit respecter une base de données si elle veut
adhérer au concept OLAP.
Vue conceptuelle multidimensionnelle, Transparence , Accessibilité, Constance des temps de réponse
Architecture client-serveur , Indépendance des dimensions, Gestion des matrices creuses, Accès multiutilisateurs
Pas de restrictions sur les opérations inter et intra dimensions, Manipulation des données aisée,
Simplicité des rapports, Nombre illimité de dimensions et nombre illimité d'éléments sur les dimensions

OLAP est un mode de stockage optimisé pour les analyses de gros volumes de données.
 Introduction et contextualisation
- Les composantes d’une architecture de BI
- OLAP: OnLine Analytical Processing

OLAP se base sur les concepts de dimensions et de faits pour créer des représentations multidimensionnelles des données.

c'est-à-dire que les faits sont calculés par rapport à des axes.

L'appellation « cube » est utilisée pour faciliter la visualisation du concept.

Imaginez un cube (trois dimensions), qui représenterait les ventes selon trois axes : produit, client et date par exemple.
Imaginez maintenant les opérations trigonométriques que l'on pourrait faire dessus :
Découper une tranche du cube : reviendrait à faire une analyse par rapport à un seul axe ;
Changer l'échelle des axes pour mieux voir : c'est-à-dire zoomer ou dé zoomer sur le cube pour en apprécier le détail ou la
généralité ;
extraire une partie du cube : pour se concentrer sur une sélection de données ;

Ceux sont ces opérations, qui sont de la plus grande utilité pour n'importe quel analyste, qui font la force et la popularité de
cette technologie.
 Introduction et contextualisation
- Les composantes d’une architecture de BI
- Outils end-users

Ce sont les outils et logiciels que les utilisateurs auront pour manipuler les données et profiter des services offerts par
l'entrepôt de données et le serveur d'analyse.

Les avancées technologiques permettent maintenant d'avoir des outils très simples et très puissants à la portée de tout le
monde.

Voir par exemple les tableaux de bord d'entreprise, qui sont un exemple d'outils end-user BI, ou les serveurs de rapports, ou
les tableurs utilisant OLAP, etc.
 Les concepts fondamentaux
- Entrepôt de données (Datawarhouse)
- une vision centralisée et universelle de toutes les données de l'entreprise.
- 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.
- un gigantesque tas de données épurées, organisées, historisées et provenant de plusieurs
sources de données, servant aux analyses et à l'aide à la décision.

- Magasin de données (Datamart)

Les Data Warehouses étant, en général, très volumineux et très complexes à concevoir, ils sont
divisés en bouchées plus faciles à créer et entretenir. Ce sont les Data Marts.

La division par fonction (un data mart pour les ventes, pour les commandes, pour les ressources
humaines)
La division par sous-ensemble organisationnel (un data mart par succursale).
 Les concepts fondamentaux
- Entrepôt de données (Datawarhouse)
- une vision centralisée et universelle de toutes les données de l'entreprise.
- 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.
- un gigantesque tas de données épurées, organisées, historisées et provenant de plusieurs
sources de données, servant aux analyses et à l'aide à la décision.

- Magasin de données (Datamart)

Les Data Warehouses étant, en général, très volumineux et très complexes à concevoir, ils sont
divisés en bouchées plus faciles à créer et entretenir. Ce sont les Data Marts.

La division par fonction (un data mart pour les ventes, pour les commandes, pour les ressources
humaines)
La division par sous-ensemble organisationnel (un data mart par succursale).
 Les concepts fondamentaux
- Dimension
Les dimensions sont les axes avec lesquels on veut faire l'analyse.
Il peut y avoir une dimension client, une dimension produit, une dimension géographie etc.

Une dimension est tout ce qu'on utilisera pour faire nos analyses.
 Les concepts fondamentaux
- Dimension Temps
- Commune à l’ensemble du datawarehouse
- Reliée à toute table de faits

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.
 Les concepts fondamentaux
- Granularité d’une dimension

Une dimension contient des membres organisés en hiérarchie.

Chacun des membres appartient à un niveau hiérarchique (ou niveau de granularité) particulier.
Granularité d’une dimension = nombre de niveaux hiérarchiques
 Les concepts fondamentaux
- Les dimensions et leurs évolution
Dimensions à évolution lente:
- Un client peut se marier, avoir des enfants…
- Un produit peut changer de noms ou de formulation

Ecraser les anciennes valeurs Ajout d’un nouvel enregistrement Ajout d’un nouvel attribut

Facile à mettre en œuvre Permet de suivre l’évolution des Avoir deux visions simultanées des
Perte de la trace des valeurs attributs données :
antérieures des attributs Permet de segmenter la table de faits Voir les données récentes avec l’ancien
Perte de la cause de l’évolution dans en fonction de l’historique attribut
les faits mesurés Accroit le volume de la table Voir les données anciennes avec le
Clé nouvel attribut
Voir les données comme si le
changement n’avait pas eu lieu.
Inadapté pour suivre plusieurs valeurs
d’attributs intermédiaires.
 Les concepts fondamentaux
- Les dimensions et leurs évolution
Dimensions à évolution rapide:

Changements fréquents des attributs dont on garde l’historique.

Solution: Isoler les attributs qui évoluent vite.

N.B. Exemple à faire

 Les concepts fondamentaux

- Fait
Ce sont des tables qui contiennent des données opérationnelles et qui relatent la vie de l'entreprise.

Il peut y avoir des tables de faits pour les ventes (chiffre d'affaire net, quantités et montants commandés, quantités
facturées, quantités retournées, volumes des ventes, etc.)
ou pour les stocks (nombre d'exemplaires d'un produit en stock, niveau de remplissage du stock, taux de roulement
d'une zone, etc.),
ou peut être sur les ressources humaines
(nombre d’accidents, nombre de demandes
de congés, nombre de démissions,
taux de roulement des employés, etc.).

Un fait est tout ce qu'on voudra analyser/ mesurer.

 Les concepts fondamentaux

- Un fait est semi-additif s'il est additif sur une partie seulement des dimensions du
modèle.
- Pour analyser les faits semi-additifs sur les dimensions sur lesquelles ils ne sont pas
additifs, il faut faire des moyennes.
 Les concepts fondamentaux

- La granularité des faits

La granularité définit le niveau de détails de la table de faits:

Exemple: une ligne de commande par produit, par client et par jour.
 Les concepts fondamentaux
- ETL

L'ETL, sert à transposer le modèle entité-relation des bases de

données de production ainsi que les autres modèles utilisés
dans les opérations de l'entreprise, en modèle à base de
dimensions et de faits.

Quelles sont ces modèles?

 Les concepts fondamentaux
- Etoile
Une étoile est une façon de mettre en relation les dimensions et les faits dans un entrepôt de
données.

Le principe est que les dimensions sont directement reliées à un fait.

- Flocon
Un autre modèle de mise en relation des dimensions et des faits dans un entrepôt de données.

Le principe étant qu'il peut exister des hiérarchies de dimensions et qu'elles sont reliées aux
faits.

N.B: les flocons et les étoiles peuvent être vus comme une manière de diviser les entrepôts de
données et les magasins de données.
Ils constituent l'atome de l'informatique décisionnelle.
C’est-à-dire le plus petit élément avec lequel ont peut faire des analyses et avec lequel ont peut
faire des magasins de données qui, mis ensemble, forment un entrepôt de données.
 Les concepts fondamentaux

Modélisation en
étoile:
• Une table de
fait centrale et
des
dimensions
• Les
dimensions
n’ont pas de
liaison entre
elles

N.B. Les faits ici sont additifs.

 Les concepts fondamentaux
Modélisation en flocon:
Raffinement du schéma étoile
avec des tables normalisées par
dimensions.
Une table de fait et des
dimensions décomposées en
sous hiérarchies.
Un seul niveau hiérarchique dans
une table de dimension
La table de dimension de niveau
hiérarchique le plus bas est reliée
à la table de fait.
C’est la table à granularité la plus
fine.
 Les concepts fondamentaux

- Constellation

- Une constellation est une série d'étoiles ou de flocons reliés entre eux par des
dimensions.

- Il s'agit donc d'étoiles avec des dimensions en commun.

- Un environnement décisionnel idéal serait une place ou il serait possible de

naviguer d'étoile en étoile, de constellation en constellation et de Data Mart en
DataMart à la recherche de l'information si précieuse.
 Les concepts fondamentaux
- Exemple d’application
Créer un data Mart (une étoile/ un flocon) pour l'analyse de l'activité des
représentants d'une entreprise de vente d'imprimantes.
Le chef d'entreprise veut savoir ce qui se passe pour ses vendeurs.
- Les employés font ils leur travail?
- Quelle est la zone de couverture des vendeurs?
- Ou sont les endroits où les vendeurs sont le moins efficaces?
L'entreprise possède:
- un système de gestion de ressources humaines
- un système de gestion des ventes
- des feuilles de route avec des informations concernant les vendeurs :
kilomètres parcourus, litres d'essence utilisée, frais de voyage, ventes,
promesses de vente.