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
Publicité
• 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 (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 d’agré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 d’assurance « TuniAssure » développe trois branches d’activité : assurance automobile, assurance immobilier et assurance responsabilité civile. Elle possède une application transactionnelle de production permettant de gérer les polices d’assurance de ses clients ainsi que les sinistres déclarés par ces clients.
1. Gestion des polices d’assurance
Pour gérer les polices, les employés ou les agents de l’assurance peuvent effectuer les transactions suivantes :
• Créer, MAJ ou supprimer une police d’assurance • 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 d’informations, et notamment : date d’écriture (date de la transaction), date d’effet (date de début d’assurance), client (personne privée, personne morale), opérateur (employé, agent : chiffrage, vérificateur : validation), risque (produit vendu par la compagnie d’assurance), 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 d’assurance 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 • Clore le sinistre
Ces transactions comportent notamment : date d’écriture (date de la transaction), date d’effet (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 d’années : 3 • Taille d’une variable (clé ou indicateur) de table de faits : 8 octets • Pourcentage de biens assurés donnant lieu à un sinistre par an : 5% • Temps d’ouverture d’un 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 s’inté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 d’effet,
é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 d’effet, é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 l’activité sur le dossier (nombre de transactions, nombre de sinistres), du chiffre d’affaire, 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 l’ED.
Démarche à suivre :
Publicité
1. Commencer par tracer quelques tableaux de bord à titre d’exemple de ce que peut éditer l’ED : quelques (de l’ordre 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 d’un 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 d’un 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é à l’anné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 d’assurance vend seulement trois produits :
♦ l’assurance automobile (type de produit A) ♦ l’assurance habitation (type H) ♦ l’assurance responsabilité civile (type R)
3- indicateur : le chiffre d’affaires du mois. Il s’agit 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 l’anné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 l’année du CA par type de risque (3), - évolution au cours de l’année du CA tous risques confondus (1), Exemple de tableau pour Type_risque = R (responsabilité civile) avec 1 seule dimension, le mois et l’indicateur :
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 Lorsqu’il chiffre le risque, l’agent lui donne une note de 1 à 3 en estimant la probabilité de coût pour l’entreprise, à partir d’un 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 :
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 l’indicateur 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). L’ED 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
Publicité
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é d’un risque, la société d’assurance 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 …
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 n’ont pour objet que de faire des regroupements sur la même police ou le même sinistre.
Taille disque des tables de faits S’il y a une seule police par client, et un seul agent par bien couvert, on obtient pour la table de faits « Police » : Nombre d’enregistrements : 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 d’enregistrements : 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