Ce document est un corrigé de travaux dirigés (TD) pour le cours de L3 ISIL au Département d’Informatique de l’Université SAAD DAHLEB – Blida 1, destiné aux étudiants de l’année académique 2022/2023.
Il couvre les notions suivantes:
- Modélisation des besoins exprimés par le DSI en flocons de neige.
- Écriture de requêtes SQL pour alimenter les tables de faits.
- Analyse des données pour calculer des indicateurs spécifiques.
Exercices TD Modélisation Flocons de Neige -BI - business intelligence
Télécharger PDFUniversité SAAD DAHLEB – Blida 1 2022/2023 Département d’Informatique Cycle : L3 ISIL
CORRIGE TD2-Bis
A) Modélisez en flocons de neige les besoins exprimés par le DSI. Notez les tables de faits TF1, TF2, …
1) Nombre d’équipements en panne (NB_EQP) par date/mois/ année d’acquisition, type d’équipement, marque, station de rattachement (stationID, frequency, CPU), marque station, localisation de l’équipement : bureauID, structure (StructureID, Nom), Niveau structure, site (SiteID, Adresse), Commune, Wilaya et Région.
TD_date
Date
TD_MarqueEQ MarqueEQ
TD_Bureau
BureauID
TD_mois Mois
TD_année année
TF1
Date
Type
MarqueEQ StationID NB_EQP
TD_type
Type
TD_Station
StationID
Fréquence
CPU
TD_marqueST
MarqueST
SiteID
Adresse
TD_Structure
StructureID
Nom
TD_Niveau Niveau
TD_Site
SiteID
Adresse
TD_Com Commune
TD_Wil Wilaya
TD_Reg Region
2) Nombre de stations (NB_STAT) par marque, CPU, frequency, RAM, date/mois/année d’acquisition ayant connu au moins une panne sur l’un de ses équipements
TD_CPU CPU
TD_RAM RAM
TF2
MarqueST CPU
Frequency RAM
Date
TD_marqueST MarqueST
TD_frequency Frequency
TD_date NB_STAT
Date
TD_mois
Mois
TD_année année
3) Nombre de problèmes résolus (NB_PBR) et nombre de problèmes en cours de traitement (NB_PBNR) par date/mois/année, severity, categoryPB, type et marque de l’équipement, station concernée (StationID, CPU), marque station, structure utilisatrice (structureID, nom), niveau de la structure, wilaya et région de localisation
TD_date
Date
TD_catPB
CategoryPB
TD_MarqueEQ
TD_mois Mois
TD_année
année
TF3
Date
Severity
CategoryPB
Type
TD_sev Severity
TD_type Type
MarqueEQTD_Station MarqueEQ
TD_Structure
StructureID
Nom
TD_Niveau
Niveau
TD_Wil Wilaya
StationID
NB_PBR
NB_PBNR
TD_Reg
Region
StationID
CPU
TD_marqueST MarqueST
4) Nombre d’utilisateurs ayant déclaré des problèmes par catégorie user (Category), catégorie socio-professionnelle (categorySP), date/mois/année de publication du problème, localisation de l’utilisateur : structure (StructureID, nom), niveau de la structure, site (SiteID, adresse) et région de localisation
TD_CSP
CategorySP
TD_date
Date
TD_mois
Mois
TD_année
année
TF4
Category CategorySP Date
StructureID NB_USERS
TD_CU
Category
TD_Structure
StructureID
Nom
TD_site
SiteID
Adresse
TD_REG
Region
TD_Niveau Niveau
5) Durée moyenne de résolution des problèmes (DUREE_MOY) en jours entre la publication du problème et la publication de la solution par année du problème, severity, categoryPb, type et marque équipement concerné par le pb, catégorie de l’employé ayant résolu le problème (Category), niveau de la structure utilisatrice de l’équipement.
TD_année Année
TD_catPB CategoryPB
TD_type Type
TF5
Année
severity
CategoryPB Type
MarqueEQ Category Niveau
TD_SEV
Severity
TD_MarqueEQ MarqueEQ
TD_category
Category TD_Niveau
Niveau
DUREE_MOY
B) Ecrire en langage SQL les requêtes permettant d’alimenter les tables de fait :
1) TF1
Use DESK Insert into DW_DESK.dbo.TF1 Select Date_acquisition as date, Type, Marque as MarqueEQ, StationID, count(equipID) as NB_EQP from Equipement Where etat=’en panne’ Group by Date_acquisition, Type, Marque, StationID
2) TF3
Insert into DW_DESK.dbo.TF3 Select Date, Severity, CategoryPb, Type, Marque, StationID, count(CASE WHEN solutionID<>’en cours’ THEN ProblèmeID END) as NB_PBR, count(CASE WHEN solutionID=’en cours’ THEN ProblèmeID END) as NB_PBNR from Problèmes P Join Equipement E on P.equipID=E.equipID Group by Date, Severity, CategoryPb, Type, Marque, StationID
3) TF5
Insert into DW_DESK.dbo.TF5 Select year (date) as Année, severity, CategoryPB, Type, E.Marque, Category, Niveau, avg(SL.date-P.date) as DUREE_MOY from Problèmes P Join Solutions SL on SL.solutionID= P.solutionID Join Equipement E on P.equipID= E.equipID Join Utilisateurs U on P.userID=U.userID Join Station S on E.stationID=S.stationID Join Bureau B on S.bureauID=B.bureauID Join Structure ST on B.structureID=ST.structureID Group by year (date), severity, Category, Type, E.Marque, Category, Niveau
C) Ecrire en langage SQL la requête d’analyse permettant de calculer :
1) Nombre d’équipements en panne (NB_EQP) (Voir TF1) de la région ‘sud’ par année d’acquisition, type d’équipement, marque, marque station, Niveau structure utilisatrice
Use DW_DESK Select year(Date_acquisition) as Année, Type, MarqueEQ, MarqueST, Niveau, SUM(NB_EQP) as NB_EQP from TF1 Join TD_Station S1 on TF1.stationID=S1.stationID Join TD_bureau B on B.bureauID=S1.bureauID Join TD_Structure S2 on B.structureID=S2. structureID Join TD_Sites S3 on S2.siteID=S3.siteID Join TD_com C on S3.commune=C.commune Join TD_wil W on C.wilaya=W.wilaya Where region=’sud’ Group by year(Date_acquisition), Type, MarqueEQ, MarqueST, Niveau
2) Le nombre de problèmes en cours de traitement (NB_PBNR) (voir TF3), de catégorie ‘Base de données’ sur des stations de marque ‘Dell’ ou ‘hp’, par année, severity, type de l’équipement, niveau de la structure utilisatrice et région de localisation
Select year(Date) as Année, Severity, Type, niveau, région, SUM(NB_PBNR) as NB_PBNR from TF3 Join TD_station S1 on TF3.stationID=S1.stationID Join TD_structure S2 on S1.structureID=S2.structureID Join TD_Sites S3 on S2.siteID=S3.siteID Join TD_com C on S3.commune=C.commune Join TD_wil W on C.wilaya=W.wilaya Where categoryPb=’Base de Données’ and (marqueST=’Dell’ OR marqueST=’hp’) Group by year(Date), Severity, Type, niveau, région
FAQ
1. Qu'est-ce qu'une table de faits (TF) ?
Une table de faits est une table centrale dans un schéma en flocon de neige ou en étoile, qui contient des mesures ou des métriques quantitatives, telles que les ventes, les coûts, ou les revenus. Elle est entourée de tables de dimensions qui fournissent le contexte pour les mesures.
2. Qu'est-ce qu'un schéma en flocon de neige ?
Un schéma en flocon de neige est une variante du schéma en étoile où les tables de dimensions sont normalisées en plusieurs tables liées entre elles. Cela permet de réduire la redondance des données et d'améliorer l'efficacité de la base de données.
3. Qu'est-ce que la durée moyenne de résolution des problèmes (DUREE_MOY) ?
La durée moyenne de résolution des problèmes (DUREE_MOY) est une mesure qui calcule le temps moyen en jours entre la publication d'un problème et la publication de sa solution. Elle est souvent utilisée pour évaluer l'efficacité des processus de résolution de problèmes.