ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Le directeur d'une entreprise de la grande distribution souhaite analyser et suivre les ventes
des produits dans ses diff rents magasins. Il souhaite obtenir une r ponse aux questions
suivantes:
" Quels produits d gagent la plus forte rentabilit dans le temps?
" Existe-t-il des disparit s r gionales de consommation des produits?
" Quel est la r partition des ventes entre les produits de marque des fabricants et
ceux de la marque du distributeur?
" Quel est le chiffre d'affaire r alis avec les plus gros fournisseurs?
L'ensemble des informations seront issues des tickets de caisse.
Nous identifions un certain nombre d'axes d'analyse: l'axe produit, l'axe magasin, l'axe
temps, l'axe localit , l'axe fournisseur.
Il faut ensuite d crire la hi rarchie de chacun de ces axes:
" Pour l'axe produit : un produit appartient une sous-famille de produits, laquelle
appartient une famille de produits, laquelle appartient une gamme de produit.
" Pour l'axe magasin: un magasin est rattach une enseigne.
" Pour l'axe fournisseur: un fournisseur appartient un groupe de fournisseurs.
" Pour l'axe localit : un d partement est rattach une r gion, laquelle est rattach e
un pays.
" Pour l'axe temps: un mois est rattach un trimestre qui est rattach un semestre
qui est rattach une ann e.
On cherche alors d crire les indicateurs suivants: les ventes par produit (CA), par magasin
par fournisseur, par r gion et dans le temps.
A faire : Proposer un sch ma en toile et en flocon pour ce cas.
1/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
DimDate
Id_Date
Mois
Trimestre
Semestre
Ann e
DimProduit
Id_Produit
FactVente
Id_Produit
Id_Magasin
Id_Fournisseur
Id_Region
Id_Date
CA
DimRegion
Id_Region
Intitul
D partement
Pays
DimFournisseur
Id_Fournisseur
Intitul
Groupe
DimMagasin
Id_Magasin
Intitul
Enseigne
N.B. Le sch ma en flocon est le m me avec les dimensions normalis es
1. Mod le en toile :
2/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Remarque :
" La plupart des attributs dimensionnels ont un ID ainsi qu'un champ descriptif. Par
exemple, dans la table Date, le mot 'Novembre' n'est pas suffisant pour identifier
avec pr cision ce mois, car on le retrouve dans chacune des ann es. Il faut donc un
attribut idMois (ex: '11/2010') ainsi qu'un attribut descriptif descrMois (ex:
'Novembre'). C'est la m me chose pour l'attribut ville: le m me nom de ville peut se
trouver dans plusieurs pays ou m me plusieurs fois dans un m me pays;
la table TypeClient peut tre pr -g n r e (toutes
" Nous avons cr une table TypeClient selon la strat gie de mini-dimension.
L'avantage est que
les
combinaisons possibles de sexe, ville, groupe d' ge, etc.). De m me, les tables
Destination, Date, Forfait, Promotion et CanalVentes peuvent galement tre pr -
g n r e et ne sont (presque) jamais modifi es. Seule la table de dimension Client
est modifi e chaque fois qu'un client s'ajoute au syst me;
" La cl primaire de la table de faits Vente est une cl compos e car il est tr s rare que
l'on acc de individuellement les lignes de cette table. En revanche, les cl s primaires
des tables de dimension sont toujours des cl s artificielles simples (ex: NUMBER).
2. Niveaux de hi rarchies
Les niveaux d'une hi rarchie doivent avoir une relation 1 plusieurs : un parent peut avoir
plusieurs enfants (ex : une ann e a plusieurs mois) mais chaque enfant n'a qu'un seul parent
Publicité
(ex : le mois '11/2010' appartient uniquement l'ann e 2010).
Table de dimension Hi rarchies
Destination
Date
Forfait
Client
TypeClient
CanalVente
Promotion
ModePaiement
idDestination idVille idPays idR gion tous
idDate idMois ann e tous
idForfait tous
idClient tous
idTypeClient idVille idProvince idPays tous
idCanal tous
idPromotion tous
idModePaiement tous
3. Strat gie dagr gation :
Pour d finir la strat gie d'agr gation, il faut choisir, pour chaque dimension, un niveau
hi rarchique permettant de faciliter l'analyse. L'objectif est d'acc l rer les calculs en
pr calculant les agr gations faites dans les requ tes analytiques fr quentes.
Ainsi, on pr voit que les analyses se feront aux niveaux suivants:
Dimension
Destination
Date (achat)
Date (d part)
Forfait
Client
TypeClient
CanalVente
Promotion
ModePaiement
Niveau hi rarchique retenu
idPays
tous
idMois
idForfait
tous
idProvince
idCanal
idPromotion
tous
3/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Avec ces niveaux d'agr gation, on pourrait analyser les ventes par pays, mois de d part,
forfait, province de client, canal de vente et promotion utilis e.
On propose donc de cr er la table de faits agr g s, venteAgr g es, qui serait mise jour
incr mentalement chaque modification de la table de faits principale:
VentesAgregees
idPaysDestination
idMoisDepart
idForfait
idProvinceClient
idCanalVente
ventesAgregees
coutsAgreges
profitsAgreges
Exercice 3 :
Une compagnie dassurance TuniAssure d veloppe trois branches dactivit : assurance
automobile, assurance immobilier et assurance responsabilit civile. Elle poss de une
application transactionnelle de production permettant de g rer les polices dassurance de
ses clients ainsi que les sinistres d clar s par ces clients.
1. Gestion des polices dassurance
Pour g rer les polices, les employ s ou les agents de lassurance peuvent effectuer les
transactions suivantes :
" Cr er, MAJ ou supprimer une police dassurance
" Cr er, MAJ ou supprimer un risque (pour une police donn e)
" Cr er, MAJ ou supprimer des biens assur s (automobile, immobilier) sur un risque
" Chiffrer ou refuser le risque
" Valider ou refuser la police
On enregistre dans ces transactions un grand nombre dinformations, et notamment : date
d criture (date de la transaction), date deffet (date de d but dassurance), client
(personne priv e, personne morale), op rateur (employ , agent : chiffrage, v rificateur :
validation), risque (produit vendu par la compagnie dassurance), couverture (description
des biens assur s), police (num ro de police, note de la police ou du risque,&) ,
transaction (code transaction).
2. Gestion des sinistres
Pour g rer les sinistres d clar s par les clients, les employ s ou agents dassurance ont
leur disposition les transactions suivantes :
" Cr er, mettre jour ou supprimer une d claration de sinistre
" Cr er, mettre jour ou supprimer une expertise
" Cr er, mettre jour ou supprimer des paiements
Publicité
" Clore le sinistre
Ces transactions comportent notamment : date d criture (date de la transaction), date
deffet (date de d claration), client, op rateur, risque, biens sinistr s, police, les tiers
4/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
impliqu s dans le sinistre, les montants financiers (limites, d j pay , reste payer, &), code
transaction.
3. Indication sur les donn es
" Nombre de polices : 2 millions
" Moyenne de biens couverts par police : 10
" Nombre de transactions par an et par police : 12
" Nombre dann es : 3
" Taille dune variable (cl ou indicateur) de table de faits : 8 octets
" Pourcentage de biens assur s donnant lieu un sinistre par an : 5%
" Temps douverture dun sinistre : 1 an
A faire : A partir de cette application transactionnelle, on veut cr er un entrep t de
donn es permettant de r pondre aux questions suivantes :
" on ne sint resse qu la globalisation par mois des transactions.
" pour chaque bien assur , on veut conna tre le montant de la prime (somme annuelle
pay e par le client pour assurer le bien) associ e au bien assur , et le nombre de
transactions du mois pour ce bien.
" On veut aussi l tat de la police pour en sp cifier les phases particuli res : police
nouvellement cr e, nouvellement modifi e, sinistre en cours, sinistre juste clos.
" On veut naturellement sortir des tableaux par client, agent ou employ , date deffet,
tat, avec toutes les sommations possibles y compris par police et risque.
" De m me on veut pouvoir sortir des tableaux de bord par sinistre avec le total pay
dans le mois et le total re u dans le mois pour ce sinistre.
Les tableaux de bord sinistre doivent pouvoir tre dit s par client, agent ou employ ,
date deffet, tat, avec toutes les sommations possibles y compris par police et risque.
On veut pouvoir tablir des tableaux de bord par client et bien assur de lactivit sur le
dossier (nombre de transactions, nombre de sinistres), du chiffre daffaire, du taux de
sinistres et du rendement (ratio versements/prime), et tous les totaux et sous totaux
correspondants.
" On veut galement d terminer la taille sur disque de lED.
D marche suivre :
1. Commencer par tracer quelques tableaux de bord titre dexemple de ce que peut
diter lED : quelques (de lordre de 5) tableaux deux dimensions pour les polices
et quelques-uns pour les sinistres (toujours deux dimensions). Tracer au moins un
cube trois dimensions.
2. Faire le sch ma en toile dun magasin de donn es police ne prenant pas en
compte les sinistres. Tracer au moins un cube trois dimensions.
3. De m me, faire le sch ma en toile dun magasin sinistre
4. Faire un seul ED de ces deux magasins. Y a-t-il des dimensions conformes ? Quels
tableaux de bord nouveaux peut-on alors diter ?
EXERCICE 2 : Correction
5/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Commen ons par cr er une table de faits tr s simple, avec deux dimensions et un
indicateur.
1- premi re dimension : le mois de production concat n lann e. On appelle mois de
production le mois lors duquel le client a sign son contrat. Exemple de valeur : 1204
pour d cembre 2004.
2- deuxi me dimension : le type de risque (produit). On suppose pour simplifier que la
compagnie dassurance vend seulement trois produits :
f lassurance automobile (type de produit A)
f lassurance habitation (type H)
f lassurance responsabilit civile (type R)
3- indicateur : le chiffre daffaires du mois. Il sagit de la somme des montants des contrats
- pour un produit donn sign s dans le mois.
D but janvier 2005, un programme (ETL) charge dans cet entrep t de donn es une couche
compos e de 3 enregistrements seulement. Exemple de la couche charg e d but janvier
2005 :
Pour toute lann e 2004, cette base de faits comporte 36 enregistrements (3 par mois * 12
mois) Ils permettent d j , par exemple, d diter 4 tableaux :
- volution au cours de lann e du CA par type de risque (3),
- volution au cours de lann e du CA tous risques confondus (1),
Exemple de tableau pour Type_risque = R (responsabilit civile) avec 1 seule dimension, le
mois et lindicateur :
6/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Exemple de tableau avec les deux dimensions, le mois et le type de risque, et 1 indicateur,
le CA.
Ajoutons une dimension, la note du risque
Lorsquil chiffre le risque, lagent lui donne une note de 1 3 en estimant la probabilit de
co t pour lentreprise, partir dun certain nombre de crit res (classement du client,
caract ristiques des biens assur s, etc...).
1 probabilit de co t lev
2 probabilit de co t moyen
3 probabilit de co t faible
Les couches de novembre et de d cembre 2004 deviennent :
Publicité
7/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Figure 5
Et le cube peut tre repr sent par le cube (3,3,2) ci-dessous.
Dans chaque l ment du tableau, la valeur de lindicateur CA.
A ce stade, nous avons une base de faits du magasin police comportant les 4 variables
suivantes :
Mois||ann e dimension - l ment de la cl multiple
Type risque dimension - l ment de la cl multiple
Note dimension - l ment de la cl multiple
CA indicateur
Cr ons un deuxi me magasin (datamart) sinistre avec pour commencer la table de
faits suivante :
8/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Mois||ann e dimension - l ment de la cl multiple
Type risque dimension - l ment de la cl multiple
Paiement indicateur
Paiement est le montant total des paiements effectu s dans le mois consid r pour le
risque donn . Sur 1 an, cette table de faits comporte 36 enregistrements (comme police
page 4).
LED r unissant les deux magasins est le double sch ma en toile suivant :
DIM_Risque
Id_Type_risque
Libell _risque
DIM_Note
Id_Note
Libell _note
FACT_Police
Id_Date
Id_Type_risque
Id_Note
CA
FACT_Sinistre
Id_Date
Id_Type_risque
Paiement
DIM_Date
Id_Date
Mois
Ann e
Cherchons par exemple diter le tableau ci-dessous avec 1 dimension, 2 indicateurs :
Le SQL permettant d diter le tableau ci-dessus est le suivant :
Select libell _risque
From Risque
Where (risque.type_risque= R ) ;
Select libell _mois, CA, paiement
From Date, Police, Sinistre
Where (risque.type_risque= R ) and (police.type_risque=risque.type_risque) and
(risque.type_risque=sinistre.type_risque) and
(police.mois||ann e=date.mois||ann e)
and (date.mois||ann e=sinistre.mois||ann e) ;
9/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
A partir du m me ED, on peut aussi diter le tableau suivant (ici limit novembre et
d cembre), en ajoutant la note partir de la table de faits police et en calculant le ratio
paiement / CA du mois :
10/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Les ratios > 0.10 sont entour s d**, ceux < 0.05 sont entour s de Le tableau de bord des
ratios pour d cembre est :
Si la valeur 0.15 pour le ratio est le seuil de rentabilit dun risque, la soci t dassurance
peut conclure au rejet des risques not s 1.
On peut maintenant crire les tables de faits compl tes.
DIM_Risque
Id_Type_risque
Libell _risque
DIM_Note
Id_Note
Libell _note
DIM_ETAT
Id_Etat
Libelle
&
DIM_BIEN
Id_Bien
Libelle
Publicité
&
DIM_Date
Id_Date
Mois
Ann e
DIM_AGENT
Id_Agent
Nom
&
DIM_Client
Id_Client
Nom
&
FACT_Police
Id_Date (Pk,fk)
Date_Effet(Pk,fk)
Id_Type_risque(Pk,fk)
Id_Note(Pk,fk)
Id_Bien(Pk,fk)
Id_Etat(Pk,fk)
Id_Agent(Pk,fk)
Id_Client(Pk,fk)
Num_Police
CA
Nbr_Transac
FACT_Sinistre
Id_Date (Pk,fk)
Date_Effet(Pk,fk)
Id_Type_risque (Pk,fk)
Id_Bien(Pk,fk)
Id_Client(Pk,fk)
Id_Police
Id_Sinistre
Paiement
Re u
Remarque : Id_police et Id_sinistre sont des dimensions d g n r es qui nont pour objet
que de faire des regroupements sur la m me police ou le m me sinistre.
Taille disque des tables de faits
Sil y a une seule police par client, et un seul agent par bien couvert, on obtient pour la table
de faits Police :
Nombre denregistrements : 2 000 000 (nb polices) x 10 (biens) x 36 (mois) = 720 millions
Pour chaque enregistrement, 11 champs de 8 octets, soit : 720 millions x 11 x 8 = (environ)
64 Giga octets
Pour la table de faits Sinistre :
Nombre denregistrements : 720 millions x 5% = 36 millions
11/12
ISET Rades
Mast re MPBI-M1
I. BEN TARBOUT
Taille totale : 36 millions x 9 (champs) x 8 (octets) = (environ) 2.6 Giga-octets
Exercice 4 :
Un chef d'un grand groupe regroupant plusieurs compagnies situ es dans plusieurs pays
souhaite r aliser une tude sur ses employ s. Pour cela, il a sa disposition les donn es du
service des ressources humaines sur les employ s.
Voici quelles sont les donn es sa disposition et comment est organis e l'entreprise:
Pour chaque employ on m morise dans le SI son nom, sa date de naissance, son sexe et sa
situation familiale (mari , concubinage, pacs, c libataire, veuf, divorc ). Lorsqu'il est engag
dans le groupe chaque employ se voit attribuer un num ro d'employ , il est affect dans
un service d'une compagnie du groupe. On enregistre sa date d'engagement. Un employ
est engag avec un type de contrat particulier qui peut tre un CDD (contrat dur e
d termin e) ou un CDI (contrat dur e ind termin e). Chaque employ est engag un
grade particulier qui caract rise son niveau d'avancement dans l'entreprise; ce grade peut
voluer au cours de sa carri re. Les grades vont de 1 25. Un employ devient cadre lorsque
son grade est sup rieur 20. Chaque ann e les employ s peuvent recevoir une prime de
performance plus ou moins importante selon le travail qu'ils ont effectu .
Le d cideur de ce groupe souhaite analyser un certain nombre de variables de l'entreprise:
- Le nombre d'employ s
- Le % d'employ s (nombre d'employ consid r / nombre total d'employ )
- Le salaire moyen
- Le taux d'occupation moyen
- Le nombre de jours d'absence
- Les primes de performance moyennes
Il souhaite analyser ces variables en fonction de plusieurs param tres: le num ro
d'employ , le type de contrat, le sexe, l' ge, le grade, la situation familiale, l'anciennet .
Il souhaite pouvoir r pondre aux questions suivantes:
- Quelles pays et quelles compagnies ont le plus d'employ s, les plus hauts salaires & ?
- Quel tait le nombre d'employ de la compagnie X au premier trimestre de 2004 ?
- Quel tait le taux d'occupation moyen par service en 2003 ?
- Quel est le profil (sexe, ge, grade) des employ s les plus "dynamiques" ?
- Y a-t-il un rapport entre l'anciennet des employ s et leur performance ?
- Quels sont les mois de l'ann e o les employ s sont les plus absents ?
A faire :
1. Rechercher tout d'abord les diff rentes dimensions et proposer ventuellement une
hi rarchie pour ces dimensions (certaines dimensions n'auront pas de hi rarchie).
Exemple: Pour la dimension ge, on peut regrouper l' ge par groupe d' ges (20-30
ans, 30-40,&).
2. Pour chaque mesure, vous devez pr ciser pour chaque dimension quel type
d'agr gation sera fait lors du passage d'une granularit une autre.
Exemple: Pour la mesure salaire, pour la dimension organisation, on fera une
moyenne du salaire de chaque employ .
3. Proposer un mod le en toile pour cette application.
12/12