Ce document est une introduction académique destinée aux étudiants de l'Université SAAD DAHLEB – Blida 1 pour le cycle L3 ISIL. Il présente une application Help Desk gérée par des administrateurs et techniciens pour l'assistance technique aux utilisateurs, couvrant à la fois le matériel et le logiciel.
Il couvre les notions suivantes:
- La structure de la base de données DESK, incluant les tables Equipement, Stations, Utilisateurs, Problèmes, Solutions, Postes, Bureaux, Structures et Sites.
- Les besoins du Directeur du Système Informatique (DSI) pour exploiter les données relatives aux services desk offerts par son équipe.
- Les idées d'analyse qui intéressent le DSI, telles que le nombre d'équipements en panne, le nombre de stations ayant connu des pannes, et la durée moyenne de résolution des problèmes.
- La modélisation en flocons de neige des besoins exprimés par le DSI, avec des tables de faits TF1, TF2, TF3, TF4 et TF5.
- Les requêtes SQL pour alimenter les tables de faits et les requêtes d'analyse pour calculer des indicateurs spécifiques.
Exercices TD Base de Données -BI - business intelligence
Télécharger PDFUniversité SAAD DAHLEB – Blida 1 2023/2024 Département d’Informatique Cycle : L3 ISIL
TD2 BI
Nom et Prénom : Groupe :
Dans le cadre d’une application Help Desk, qui gère l’assistance technique apportée par les administrateurs et les techniciens aux utilisateurs et ce que ce soit pour le matériel ou le logiciel, une entreprise dispose de la base de données DESK suivante :
Equipement (EquipID, Nom, SerialNum, type, Marque, Etat, date_acquisition, #StationID)
Type : ‘logiciel’, ‘disque’, ‘carte mère’, ‘modem’, ‘écran’, ‘clavier’, ‘souris’, ‘imprimante’ ….
Etat : ‘opérationnel’, ‘en panne’, ‘en test’ …
Marque : ‘Intel’, ‘AMD’, ‘Motorola’, ‘Canon’, ‘Epson’, ‘Microsoft’, ‘Oracle’, ‘Google’ …
Stations (#StationID, date_acquisition, HDspace, Frequency, RAM, CPU, #BureauID, Marque)
Marque : ‘IBM’, ‘Dell’, ‘hp’, ‘Acer’, ‘Apple’, ‘Asus’, ‘Lenovo’ …
CPU: type processeur (plusieurs marques station peuvent utiliser la même CPU)
Utilisateurs (UserID, UserName, tel, Category, #PosteID, #BureauID)
Category = ‘end-user’, ‘admin’, ‘technicien’
Problèmes (#ProblèmeID, description, date, severity, #UserID, categoryPb, #EquipID, #solutionID)
Severity: ‘Critical’, ‘Hight’, ‘Medium’, ‘Low’
UserID: l’ID de l’utilisateur (end-user, tech ou admin) ayant soulevé le problème
CategoryPb : ‘installation’, ‘paramétrage’, ‘sauvegarde’, ‘restauration’, ‘impression’, ‘connexion LAN’, ‘connexion WAN’, ‘connexion Internet’, ‘Base de données’, ‘Interface GUI’, ‘Virus’, ‘piratage de données’ …..
SolutionID : prend la valeur ‘en cours’ lorsque le problème est en cours de traitement
Solutions (#SolutionID, description, date, #UserID)
La même solution peut résoudre plusieurs problèmes.
UserID : l’ID du technicien ou de l’administrateur ayant proposé la solution
Afin de préserver l’intégrité référentielle avec la table Problèmes, la table Solutions contient un tuple particulier : (‘en cours’, ‘aucune solution n’est encore identifiée’, ’01/01/1900’,’Admin’)
Postes (PosteID, Description, categorySP)
CategorySP = ‘exécutant’, ‘maitrise’, ’cadre’, ‘cadre supérieur’
Bureaux (BureauID, Description, #StructureID)
Structures (StructureID, Nom, Niveau, #SiteID)
Niveau : ‘Service’, ‘Département’, ‘sous/direction’, ‘direction’, ‘division’
Sites (SiteID, Nom, Adresse, commune, wilaya, Région)
Le directeur du système informatique de l’entreprise (DSI) aimerait exploiter toutes les données relatives aux services desk offerts par son équipe (administrateurs et techniciens) aux employés de l’entreprise. Pour ce faire, il sollicite une expertise dans le domaine des systèmes décisionnels qui aura pour mission de mettre en place un entrepôt de données, de mettre en œuvre un processus ETL pour le rafraichissement de l’entrepôt et une application d’analyse des données sur les services Desk.
1. A partir des données opérationnelles sur le Help Desk de cette organisation, proposez des idées d’analyse qui intéressent le DSI
2. Relevez à partir du schéma de la source Help Desk, les données n’ayant pas une valeur analytique
Université 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. Quelles sont les principales tables de la base de données DESK ?
Les principales tables de la base de données DESK sont : Equipement, Stations, Utilisateurs, Problèmes, Solutions, Postes, Bureaux, Structures, et Sites.
2. Quels sont les types d'équipements gérés par la base de données ?
Les types d'équipements gérés par la base de données incluent : logiciel, disque, carte mère, modem, écran, clavier, souris, imprimante, etc.
3. Quelles sont les catégories de problèmes gérés par le Help Desk ?
Les catégories de problèmes gérés par le Help Desk incluent : installation, paramétrage, sauvegarde, restauration, impression, connexion LAN, connexion WAN, connexion Internet, Base de données, Interface GUI, Virus, piratage de données, etc.