Rémy Choquet - Université Lyon 2 - Master 2 IIDEE - 2006-2007

ETL
Extract - Transform - Load
Concept général d’analyse en ligne (rappels)
Rémy Choquet - Université Lyon 2 - Master 2 IIDEE - 2006-2007
Plan
• Définitions
• La place d’OLAP dans une entreprise
• OLAP versus OLTP
Les
règles
de
Codd
•
• La construction d’un entrepôt de données
• Requêtes multi-dimensionnelles
Définitions
Source: Wikipédia
•
Online Analytical Processing (OLAP), désigne les bases de données multidimensionnelles (aussi appelées
cubes ou hypercubes) destinées à l'analyse et il s'oppose au terme OLTP qui désigne les systèmes
transactionnels. 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.
•
Ce concept est appliqué à un modèle virtuel de représentation de données appelé cube ou hypercube
OLAP. Il existe ensuite plusieurs déclinaisons semblables à des drivers qui permettent d'adapter le stockage
des données sur différents types de base de données pour implémenter le concept OLAP :
•
•
•
•
•
R-OLAP (Relational OLAP)
D-OLAP (Dynamic ou Desktop OLAP)
M-OLAP (Multidimensional OLAP)
H-OLAP (Hybrid OLAP)
S-OLAP (Spatial OLAP)
100
75
50
25
0
2004
2005
2006
2007
OLAP en entreprise (décisionnel) 1/3
Source: wikipédia
•
L’informatique décisionnelle (en anglais : DSS pour Decision Support System ou encore BI pour Business
Intelligence) désigne les moyens, les outils et les méthodes qui permettent de collecter, consolider,
modéliser et restituer les données d'une entreprise en vue d'offrir une aide à la décision et de
permettre aux responsables de la stratégie d'une entreprise d’avoir une vue d’ensemble de l’activité
traitée. Ce type d’application utilise en règle générale un datawarehouse (ou entrepôt de données) pour
stocker des données transverses provenant de plusieurs sources hétérogènes et fait appel à des
traitements lourds de type batch (sans ETL) pour la collecte de ces informations.
•
L’informatique décisionnelle s’insère dans l’architecture plus large d’un système d'information.
•
Les applications classiques « d’entreprise » permettent de stocker, restituer, modifier les données des
différents services opérationnels de l’entreprise (production, marketing, facturation comptabilité, etc.).
Ces différents services possèdent chacun une ou plusieurs applications propres, et les données y sont
rarement structurées ou codifiées de la même manière que dans les autres services. Chaque service
dispose le plus souvent de ses propres tableaux de bord et il est rare que les indicateurs (par exemple :
le chiffre d'affaires sur un segment de clientèle donné) soient mesurés partout de la même manière,
selon les mêmes règles et sur le même périmètre.
•
Pour pouvoir obtenir une vision synthétique de chaque service ou de l’ensemble de l’entreprise, il
convient donc que ces données soient filtrées, croisées et reclassées dans un entrepôt de données
central. Cet entrepôt de données va permettre aux responsables de l’entreprise et aux analystes de
prendre connaissance des données à un niveau global et ainsi prendre des décisions plus pertinentes,
d’où le nom d’informatique décisionnelle.
OLAP en entreprise (décisionnel) 2/3
•
•
•
•
•
•
Source: wikipédia
Les données applicatives métier sont stockées dans une (ou plusieurs) base(s) de données relationnelle(s) ou non
relationnelles.
Ces données sont extraites, transformées et chargées dans un entrepôt de données généralement par un outil de type
ETL (Extract-Tranform-Load) ou en français ETC (Extraction-Tranformation-Chargement)
Un entrepôt de données peut prendre la forme d’un datawarehouse ou d’un datamart. En règle générale, le
datawarehouse globalise toutes les données applicatives de l’entreprise, tandis que les datamarts (généralement alimentés
depuis les données du datawarehouse) sont des sous-ensembles d’informations concernant un métier particulier de
l’entreprise (marketing, risque, contrôle de gestion, ...). Le terme comptoir de données ou magasin de données est aussi
utilisé pour désigner un datamart.
Le reporting est vraisemblablement l'application la plus utilisée encore aujourd'hui de l’informatique décisionnelle, il
permet aux gestionnaires :
• de sélectionner des données relatives à telle période, telle production, tel secteur de clientèle, etc.,
• de trier, regrouper ou répartir ces données selon les critères de leur choix,
• de réaliser divers calculs (totaux, moyennes, écarts, comparatif d'une période à l'autre, ...),
• de présenter les résultats d’une manière synthétique ou détaillée, le plus souvent graphique selon leurs besoins ou
les attentes des dirigeants de l’entreprise.
Les programmes utilisés pour le reporting permettent bien sûr de reproduire de période en période les mêmes sélections
et les mêmes traitements et de faire varier certains critères pour affiner l’analyse. Mais le reporting n'est pas à
proprement parler une application d'aide à la décision. L'avenir appartient plutôt aux instruments de type tableau de bord
équipés de fonctions d'analyses multidimensionnelles de type Olap.
Les datamart et/ou les datawarehouses peuvent aussi alimenter des bases de données multidimensionnelles, qui
permettent l’analyse très approfondie de l’activité de l’entreprise, grâce à des statistiques recoupant des informations
relatives à des activités apparemment très différentes ou très éloignées les unes des autres, mais dont l’étude fait souvent
apparaître des dysfonctionnements, des corrélations ou des possibilités d’améliorations très sensibles.
L'interopérabilité entre les systèmes d'entrepôt de données, les applications informatiques ou de gestion de contenu, et les
systèmes de reporting est réalisée grâce à une gestion des métadonnées.
OLAP en entreprise (décisionnel) 3/3
OLAP vs OLTP
Caractéristiques
OLTP
OLAP
Utilisation
SGBD (production)
Datawarehouse
Opération typique
Mise à jour
Analyse
Type d’accès
Lecture/Ecriture
Lecture
Niveau d’analyse
Elémentaire
Global
Quantité d’information
échangées
Faible
Importante
Orientation
Ligne
Multidimension
Taille DB
Faible (max qq GB)
Importante (>TB)
Ancienneté des Données
Récente
Historique
L'objectif des bases OLTP est de pouvoi répondre rapidement à des réponses simples, exemple : les ventes du
produit X. Les bases OLAP permettent des requêtes plus complexes : les ventes du produit X par vendeur,
région et par mois.
Les règles de Codd 1/2
•
•
•
•
•
Règle 1 : La règle de l'information: Toute l'information dans la base de données est représentée d'une et une
seule manière, à savoir par des valeurs dans des champs de colonnes de tables.
Règle 2 : La règle garantie d'accès: Toutes les données doivent être accessibles sans ambiguïté. Cette règle est
essentiellement un ajustement de la condition fondamentale pour des clefs primaires. Elle indique que chaque
valeur scalaire individuelle dans la base de données doit être logiquement accessible en indiquant le nom de la
table contenante, le nom de la colonne contenante et la valeur principale primaire de la rangée contenante.
Règle 3 : Traitement systématique des valeurs nulles: Le système de gestion de bases de données doit permettre
à chaque champ de demeurer nul (ou vide). Spécifiquement, il doit soutenir une représentation "d'information
manquante et d'information inapplicable" qui est systématique, distincte de toutes les valeurs régulières (par
exemple, "distincte de zéro ou tous autres nombres," dans le cas des valeurs numériques), et ce indépendamment
du type de données. Cela implique également que de telles représentations doivent être gérées par le système de
gestion de bases de données d'une manière systématique.
Règle 4 : Catalogue en ligne actif basé sur le modèle relationnel: Le système doit supporter un catalogue en
ligne, intégré, relationnel, accessible aux utilisateurs autorisés au moyen de leur langage d'interrogation régulier.
Les utilisateurs doivent donc pouvoir accéder à la structure de la base de données (catalogue) employant le même
langage d'interrogation qu'ils emploient pour accéder aux données de la base de données.
Règle 5 : La règle complète de sous-langage de données: Le système doit soutenir au moins un langage
relationnel qui:
(a) a une syntaxe linéaire
(b) peut être employé interactivement et dans des programmes d'application,
(c) supporte des opération de définition d'informations supplémentaires (incluant des définitions de vues),
de manipulation de données (mise à jour aussi bien que la récupération), de contraintes de sécurité et
d'intégrité, et des opérations de gestion de transaction (commencer, valider et annuler une transaction).
•
•
•
Les règles de Codd 2/2
•
•
•
•
•
•
•
•
Règle 6 : La règle de mise à jour des vues : Toutes les vues pouvant théoriquement être mises à jour doit pouvoir
l'être par le système.
Règle 7 : Insertion, mise à jour, et effacement de haut niveau: Le système doit supporter les opération par lot
d'insertion, de mise à jour et de suppression. Ceci signifie que des données peuvent être extraites d'une base de
données relationnelle dans des ensembles constitués par des données issues de plusieurs tuples et/ou de multiples
table. Cette règle explique que l'insertion, la mise à jour, et les opérations d'effacement devraient être supportées
aussi bien pour des lots de tuples issues de plusieurs tables que juste pour un tuple unique issu d'une table unique.
Règle 8 : L'indépendance de donnée physique: Les modifications au niveau physique (comment les données sont
stockées, si dans les rangées ou les listes liées etc...) ne nécessitent pas un changement d'une application basée sur les
structures.
Règle 9 : L'indépendance de donnée logique: Les changements au niveau logique (tables, colonnes, rangées, etc) ne
doivent pas exiger un changement dans l'application basée sur les structures. L'indépendance de données logiques et
plus difficile a atteindre que l'indépendance de donnée physique.
Règle 10 : L'indépendance d'intégrité:
Des contraintes d'intégrité doivent être indiquées séparément des programmes d'application et être stockées dans le
catalogue. Il doit être possible de changer de telles contraintes au fur et à mesure sans affecter inutilement les
applications existantes.
Règle 11 : L'indépendance de distribution: La distribution des parties de la base de données à de diverses
localisations devrait être invisible aux utilisateurs de la base de données. Les applications existantes devraient
continuer à fonctionner avec succès :
(a) quand une version distribuée du système de gestion de bases de données est d'abord présentée ; et
(b) quant des données existantes sont redistribués dans le système.
Règle 12 : La règle de nonsubversion: Si le système fournit une interface de bas niveau , alors cette interface ne
peut pas être employée pour contourner le système, par exemple, pour contourner une contrainte relationnelle de
sécurité ou d'intégrité.
•
•
Construire un entrepôt de données
Construire un entrepôt de données
4 phases de construction, lesquelles?
Construire un entrepôt de données
4 phases de construction, lesquelles?
• Conception
Construire un entrepôt de données
4 phases de construction, lesquelles?
• Conception
• Construction
Construire un entrepôt de données
4 phases de construction, lesquelles?
• Conception
• Construction
Administration
•
Construire un entrepôt de données
4 phases de construction, lesquelles?
• Conception
• Construction
Administration
•
• Restitution
Construire un entrepôt de données: Conception
•
•
•
•
•
•
•
•
Définition de la finalité de l’entrepôt:
Piloter quelle activité de l’entreprise
Déterminer et recenser les données à entreposer
Définir les aspects techniques de la réalisation
Le modèle de données
Les démarches d’alimentation
Les stratégies d’administration
La définition de l’espace d’analyse
Le mode de restitution
Construire un entrepôt de données: Construction
• Extraction des données des différentes Bases de
production (internes ou externes)
• Nettoyage des données, règles d’homogéinisation des
données sous formes de métadonnées
• Technique d’alimentation:
Chargement
des
données
dans
l’entrepôt
•
Fréquence
de
rafraîchissement
•
Construire un entrepôt de données: Administration
Plusieurs tâches pour assurer:
• La qualité et la pérennité des données
• La maintenance
La
gestion
de
configuration
•
• Les mises à jour
• L’organisation, l’optimisation du SI
• La mise en sécurité du SI
Construire un entrepôt de données: Restitution
• C’est le but du processus d’entreposage des données
• Elle conditionne le choix de l’architecture de l’entrepôt
et de sa construction
•
Elle doit permettre toutes les analyses nécessaires pour
la construction des indicateurs recherchés
• Plusieurs outils d’aide à l’analyse sont disponibles sur le
marché, ex: Business Object
Requêtes multi-dimensionnelles
On crée, un cube:
• Utilisation de groupby donne naissance à un cube
Ensuite, on décline plusieurs axes de navigation:
• Le drill down: on précise un sous-valeur de dimension
Le
slice
and
dice:
on
fixe
une
valeur
d’une
des
•
dimensions
Requêtes multi-dimensionnelles: Exemples
Requêtes multi-dimensionnelles: Exemples
Schéma
Sales ( pid, sid, tid, volume)
Product (pid, pname, type, category)
Store (sid, sname, city, state, region)
Time (tid, day, month, quarter, year)
Requêtes multi-dimensionnelles: Exemples
Schéma
Sales ( pid, sid, tid, volume)
Product (pid, pname, type, category)
Store (sid, sname, city, state, region)
Time (tid, day, month, quarter, year)
Cube
Imaginons que l'utilisateur tiennent à connaitre le volume des ventes
en fonction de la ville du magasin, du type de produit et de l'année.
Nous aurons alors la requête suivante :
Select city,type,year, sum(volume)
from Sales,Product,Store,Time
where Sales.pid = Product.pid and
Sales.sid = Store.sid and
Sales.tid = Time.Tid
group by Store.city, Product.type, Time.Year
Requêtes multi-dimensionnelles: Exemples
Schéma
Sales ( pid, sid, tid, volume)
Product (pid, pname, type, category)
Store (sid, sname, city, state, region)
Time (tid, day, month, quarter, year)
Drill down
L'utilisateur remarque alors qu'il existe un problème sur
1996 et veut obtenir la même requête pour 1996 en
fonction de mois au lieu de l'année.
Select city,type, month, sum(volume)
from Sales,Product,Store,Time
where Sales.pid = Product.pid and
Sales.sid = Store.sid and
Sales.tid = Time.Tid and
Time.Year = "1996"
group by Store.city, Product.type, Time.Year, Time.Month
Cube
Imaginons que l'utilisateur tiennent à connaitre le volume des ventes
en fonction de la ville du magasin, du type de produit et de l'année.
Nous aurons alors la requête suivante :
Select city,type,year, sum(volume)
from Sales,Product,Store,Time
where Sales.pid = Product.pid and
Sales.sid = Store.sid and
Sales.tid = Time.Tid
group by Store.city, Product.type, Time.Year
Requêtes multi-dimensionnelles: Exemples
Schéma
Sales ( pid, sid, tid, volume)
Product (pid, pname, type, category)
Store (sid, sname, city, state, region)
Time (tid, day, month, quarter, year)
Drill down
L'utilisateur remarque alors qu'il existe un problème sur
1996 et veut obtenir la même requête pour 1996 en
fonction de mois au lieu de l'année.
Select city,type, month, sum(volume)
from Sales,Product,Store,Time
where Sales.pid = Product.pid and
Sales.sid = Store.sid and
Sales.tid = Time.Tid and
Time.Year = "1996"
group by Store.city, Product.type, Time.Year, Time.Month
Cube
Imaginons que l'utilisateur tiennent à connaitre le volume des ventes
en fonction de la ville du magasin, du type de produit et de l'année.
Nous aurons alors la requête suivante :
Select city,type,year, sum(volume)
from Sales,Product,Store,Time
where Sales.pid = Product.pid and
Sales.sid = Store.sid and
Sales.tid = Time.Tid
group by Store.city, Product.type, Time.Year
Slice & dice
L'utilisateur remarque alors que seule la ligne de produits
Pentium est intéressante pour l'analyse, il se restreint alors
à ce type de produits.
Select city , month, sum(volume)
from Sales,Product,Store,Time
where Product.type = "PentiumPC"
Sales.pid = Product.pid and
Sales.sid = Store.sid and
Sales.tid = Time.Tid and
Time.Year = "1996"
group by Store.city, Time.Month
TP 1