Ce document contient un examen du module SDED pour les étudiants de M2 IL à l'USTHB, Département d'Informatique, sous la direction de N. Selmoune. Il couvre les notions suivantes:
- Analyse des indicateurs de performance pour le département Finances.
- Affinement de la connaissance des abonnés et de leurs comportements pour le département Gestion de la relation client.
- Calcul du chiffre d’affaires effectif réalisé par abonné pour l’année 2011.
- Segmentation des abonnés en fonction du chiffre d’affaires réalisé en 2011.
Examen Module SDED M2 IL -BI - business intelligence
Télécharger PDFExercice N°1 (10 pts)
Considérons le MCD ci-dessous représentant une partie du système d’information d’un fournisseur d’accès à Internet :
Remarques :
- Les services représentent les différentes offres du fournisseur, chaque service étant proposé avec différents débits possibles.
- L’état de la facture peut être payée ou impayée.
- Le type de client peut être particulier ou entreprise.
1- Le département ‘Finances’ de l’entreprise veut disposer d’un magasin de données pour analyser ses indicateurs de performance.
a. Quelles mesures proposez-vous pour ce magasin ?
b. Quelles sont les dimensions à prendre en compte?
c. Identifiez les hiérarchies de chaque dimension.
d. Représentez le modèle conceptuel en étoile du magasin en précisant tous les attributs.
e. Donnez le script de création de la table de fait.
a) Chiffre d’affaires,
b) abonné, date, étatfacture (je n’ai pas opter pour service et débit car il est impossible de connaître le chiffre d’affaires par rapport à ces dimensions)
c) pour abonné : abonné->ville –Wilaya, abonné->typeabonné, pour date jour->mois->année
d) représentation graphique (voir cours)
e)
USTHB 2011 /2012 FEI
Département d’Informatique N. Selmoune
Create Materialized view
Build immediate
Refresh complete on demand as
Select CodeAb, dateFacture, EtatFacture,Sum( montantfact) as CA
Group by CodeAbonné, dateFacture , EtatFacture;
2- Le département ‘Gestion de la relation client’ veut également affiner sa connaissance des abonnés et de leurs comportements à travers un magasin de données.
a. Quelles mesures proposez-vous pour ce magasin ?
b. Quelles sont les dimensions à prendre en compte?
c. Identifiez les hiérarchies de chaque dimension.
d. Représentez le modèle conceptuel en flocon de neige du magasin en précisant tous les attributs.
a) nbsession, duréeconnexion, volumedetélchargement, nbsitesaccèdés
b) abonné, date, service, débit
c) même que 1-c
d) représentation graphique (voir cours)
Exercice N°2 (6 pts)
Etant donné le schéma de la base de données de l’exercice N°1, nous voulons calculer le chiffre d’affaire effectif réalisé par abonné, pour l’année 2011, ainsi que les tertiles (Ntile avec N=3) sur la base de ce chiffre, ainsi que la répartition cumulative du chiffre d’affaire par abonné. L’affichage se fera dans l’ordre des numéros d’abonnées.
1. Donnez la requête SQL permettant d’obtenir ce résultat (CodeAb, CA, Tertile, CumDistCA).
Select CodeAb, sum (MontantFacture) as CA,
NTILE(3) OVER (ORDER BY sum (MontantFacture)) AS Tertile,
Cume_dist() over (order by sum (MontantFacture))as CumDistCA
FROM facture
Where Tochar(datefacture,’YYYY’)=’2011’ and etatfacture=’Payé’
Group by CodeAb
order by Codeab ;
USTHB 2011 /2012 FEI
Département d’Informatique N. Selmoune
2. Compléter l’affichage du résultat de la requête (pour la dernière colonne mettez juste les fractions sans calcul).
| CodeAb | CA | Tertile | CumDistCA |
|---|---|---|---|
| 1 | 45000 | 2 | 5/8 |
| 2 | 100000 | 3 | 8/8 |
| 3 | 45500 | 2 | 6/8 |
| 4 | 25000 | 1 | 3/8 |
| 5 | 2000 | 1 | 1/8 |
| 6 | 14000 | 1 | 2/8 |
| 7 | 36000 | 2 | 4/8 |
| 8 | 50000 | 3 | 7/8 |
Exercice N°3 (4 pts)
On veut segmenter les abonnés en fonction chiffre d’affaires réalisé en 2011. En appliquant la méthode des k moyennes avec k=3, et la fonction de distance =( |différence|/ amplitude maximale), donnez le résultat de la segmentation en illustrant les différentes étapes (les données sont dans l’exercice N°2, on prendra les 3 premières valeurs comme point de départ).
Itération 1
| CA | 1 | 2 | 3 |
|---|---|---|---|
| 45000 | 0,000 | 0,561 | 0,005 |
| 100000 | 0,561 | 0,000 | 0,556 |
| 45500 | 0,005 | 0,556 | 0,000 |
| 25000 | 0,204 | 0,765 | 0,209 |
| 2000 | 0,439 | 1,000 | 0,444 |
| 14000 | 0,316 | 0,878 | 0,321 |
| 36000 | 0,092 | 0,653 | 0,097 |
| 50000 | 0,051 | 0,510 | 0,046 |
Affectation
| CodeAb | 1 | 2 | 3 |
|---|---|---|---|
| 1 | 1 | 2 | 3 |
| 2 | 1 | 2 | 3 |
| 3 | 1 | 2 | 3 |
| 4 | 1 | 2 | 3 |
| 5 | 1 | 2 | 3 |
| 6 | 1 | 2 | 3 |
| 7 | 1 | 2 | 3 |
| 8 | 1 | 2 | 3 |
Itération 2
| CA | 1 | 2 | 3 |
|---|---|---|---|
| 45000 | 0,210 | 0,771 | 0,215 |
| 100000 | 0,561 | 0,000 | 0,556 |
| 45500 | 0,028 | 0,533 | 0,023 |
| 25000 | 0,006 | 0,765 | 0,232 |
| 2000 | 0,229 | 1,000 | 0,467 |
| 14000 | 0,106 | 0,878 | 0,344 |
| 36000 | 0,118 | 0,653 | 0,120 |
| 50000 | 0,261 | 0,510 | 0,023 |
Affectation
| CodeAb | 1 | 2 | 3 |
|---|---|---|---|
| 1 | 3 | 2 | 3 |
| 2 | 3 | 2 | 3 |
| 3 | 3 | 2 | 3 |
| 4 | 1 | 2 | 3 |
| 5 | 1 | 2 | 3 |
| 6 | 1 | 2 | 3 |
| 7 | 1 | 2 | 3 |
| 8 | 3 | 2 | 3 |
Itération 3
| CA | 1 | 2 | 3 |
|---|---|---|---|
| 45000 | 0,263 | 0,824 | 0,268 |
| 100000 | 0,561 | 0,000 | 0,556 |
| 45500 | 0,019 | 0,543 | 0,014 |
| 25000 | 0,059 | 0,765 | 0,223 |
| 2000 | 0,176 | 1,000 | 0,457 |
| 14000 | 0,054 | 0,878 | 0,335 |
| 36000 | 0,171 | 0,653 | 0,111 |
| 50000 | 0,314 | 0,510 | 0,032 |
Affectation
| CodeAb | 1 | 2 | 3 |
|---|---|---|---|
| 1 | 3 | 2 | 3 |
| 2 | 3 | 2 | 3 |
| 3 | 3 | 2 | 3 |
| 4 | 1 | 2 | 3 |
| 5 | 1 | 2 | 3 |
| 6 | 1 | 2 | 3 |
| 7 | 3 | 2 | 3 |
| 8 | 3 | 2 | 3 |
Itération 4
| CA | 1 | 2 | 3 |
|---|---|---|---|
| 45000 | 0,320 | 0,881 | 0,325 |
| 100000 | 0,561 | 0,000 | 0,556 |
| 45500 | 0,009 | 0,570 | 0,014 |
| 25000 | 0,116 | 0,765 | 0,195 |
| 2000 | 0,119 | 1,000 | 0,430 |
| 14000 | 0,003 | 0,878 | 0,307 |
| 36000 | 0,228 | 0,653 | 0,083 |
| 50000 | 0,371 | 0,510 | 0,060 |
Affectation
| CodeAb | 1 | 2 | 3 |
|---|---|---|---|
| 1 | 3 | 2 | 3 |
| 2 | 3 | 2 | 3 |
| 3 | 3 | 2 | 3 |
| 4 | 1 | 2 | 3 |
| 5 | 1 | 2 | 3 |
| 6 | 1 | 2 | 3 |
| 7 | 3 | 2 | 3 |
| 8 | 3 | 2 | 3 |
FAQ
Qu'est-ce qu'un magasin de données ?
Un magasin de données est une base de données optimisée pour le reporting et l'analyse. Il stocke des données historiques et agrégées provenant de diverses sources pour permettre une analyse approfondie et des rapports détaillés.
Quelles sont les dimensions à prendre en compte pour un magasin de données ?
Les dimensions à prendre en compte pour un magasin de données incluent généralement des éléments tels que le temps, la géographie, les produits, les clients, et les canaux de vente. Ces dimensions permettent de segmenter et d'analyser les données de manière significative.
Comment créer une table de fait dans un magasin de données ?
Pour créer une table de fait dans un magasin de données, il faut définir les mesures (métriques) que l'on souhaite analyser, telles que le chiffre d'affaires, les ventes, ou les coûts. Ensuite, il faut identifier les dimensions associées à ces mesures, comme le temps, la géographie, et les produits. Enfin, il faut structurer la table de fait pour inclure les clés étrangères vers les tables de dimensions et les mesures agrégées.