Entrepôts de données TP N°2 Année 2015 - 2016 Note importante : Les séances de TP relatives à ce sujet feront l’objet d’une évaluation (Contrôle Continu). Les fichiers créés ou modifiés lors des TP seront demandés (envoyés par mail) à l’issue des séances de TP. Ce TP/Projet peut être réalisé en groupe de 3 étudiants maximum. Démarrage : Télécharger les bases de données FRINGUES_CLIENTS et BASE_GEO à l’adresse suivante : http://www.irit.fr/~Gilles.Hubert/supports. La base de données FRINGUES_CLIENTS est constituée des tables et 'relations' suivantes : La base de données BASE_GEO est constituée des tables et 'relations' suivantes : A) Création d’un entrepôt de données à partir de bases de données sources 1. A partir des bases de données FRINGUES_CLIENTS et BASE_GEO, nous devons créer l’entrepôt de données permettant d’effectuer l’analyse des ventes réalisées auprès des clients. Plus précisément, pour chaque vente de produit à un client, nous souhaitons disposer des deux critères d’analyse suivants : - le montant total TTC de l’achat, - la quantité de produits (nombre d’exemplaires) vendue. Cette analyse devra s’effectuer au travers des axes suivants : - l’axe PRODUIT regroupant la désignation, la marque, le Taux de TVA, la catégorie et le secteur - l’axe CLIENT regroupant le nom, le prénom, l’identifiant de la ville (ex. Pau-64), le code de département (ex. 2A) et la région (ex. Midi-Pyrénées) - l’axe TEMPS regroupant la date (jour+mois+année), l’identifiant de mois (mois+année) et l’année IUP IO – M1 1/3 Entrepôts de données TP N°2 Année 2015 - 2016 L’entrepôt de données est ainsi constitué des tables et 'relations' suivantes : 2. Créer une nouvelle base de données ACCESS (vide) sous le nom FRINGUES_ED. 3. Créer à l’aide du langage SQL les tables de la base de données FRINGUES_ED et les relations clés étrangères/clés primaires. 4. Définir les requêtes d’interrogation sur les bases sources FRINGUES_CLIENTS et BASE_GEO permettant d’alimenter les tables de l’entrepôt de données FRINGUES_ED. Vérifier que chaque requête fournit les éléments nécessaires. Remarque : L’accès à une base de données externe s’effectue en SQL ACCESS à l’aide de la clause IN combinée à l’indication du chemin d’accès vers la base externe comme dans l’exemple suivant : SELECT * FROM MaTable1 IN ".\BD1.mdb" 5. Transformer ces requêtes d’interrogation en requêtes d’ajout d’enregistrements dans l’entrepôt de données FRINGUES_ED. 6. Exécuter les requêtes et vérifier l’alimentation correcte d’entrepôt de données FRINGUES_ED. B) Requêtes liées au décisionnel sur un entrepôt de données Créer sur l’entrepôt de données FRINGUES_ED et à l'aide du langage SQL, les requêtes répondant aux questions suivantes : 1. Obtenir la quantité maximum et la quantité minimum vendue par produit (référence, désignation) et par mois pour l’année 2001 2. Obtenir la quantité totale des ventes par département et par catégorie de produit 3. Obtenir le meilleur client (Numéro, nom, prénom) en montant total acheté. 4. Obtenir le nombre de marques achetées par client. IUP IO – M1 2/3 Entrepôts de données TP N°2 Année 2015 - 2016 C) Modification d’un entrepôt de données La conception de l’entrepôt de données est maintenant modifiée afin de permettre l’analyse des achats effectués auprès des fournisseurs. Les achats auprès des fournisseurs sont gérés à l’aide d’une base de données nommée FRINGUES_FOURN. Plus précisément, pour chaque achat de produit auprès d’un fournisseur, nous souhaitons disposer du critère d’analyse suivant : - la quantité de produits achetée. Cette analyse devra s’effectuer au travers des axes suivants : - l’axe PRODUIT et TEMPS déjà présents dans l’entrepôt - l’axe FOURNISSEUR regroupant la raison sociale et le numéro de département 1. Modifier l’entrepôt de données FRINGUES_ED afin de prendre en compte l’évolution désirée (requêtes SQL pour créer les nouvelles tables pour le nouveau fait ACHAT et la nouvelle dimension FOURNISSEUR). 2. En utilisant la nouvelle base données FRINGUES_FOURN, créer la requête SQL pour ajouter les nouvelles dates liées aux approvisionnements de produits auprès des fournisseurs dans la table TEMPS existante. Créer ensuite la requête SQL pour ajouter les données dans la nouvelle table FOURNISSEUR. Enfin, créer la requête SQL pour ajouter les données dans la table nouvelle table ACHAT. Remarque : La base de données FRINGUES_FOURN est également téléchargeable à l’adresse suivante : http://www.irit.fr/~Gilles.Hubert/supports. La base de données FRINGUES_FOURN est constituée des tables et 'relations' suivantes : 3. En utilisant la nouvelle version de l’entrepôt de données FRINGUES_ED, créer la requête SQL permettant d’obtenir la quantité totale achetée auprès de chaque département de fournisseurs chaque année. A rendre avant le vendredi 18 décembre 2015 18h par mail à l’adresse [email protected] (fichier Access correspondant à l’entrepôt de données créé FRINGUES_ED nommé avec les noms des étudiants contenant la base de données avec les requêtes enregistrées en respectant les numéros et les sections (ex. B1, B2…)). Les noms des étudiants du groupe doivent apparaître dans le mail. IUP IO – M1 3/3