Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

UTBM – Département Informatique A2011 BD51 - Data warehouse Boris Ahodikpe - Re

UTBM – Département Informatique A2011 BD51 - Data warehouse Boris Ahodikpe - Renaud Joly - Delphine Lacour B D 5 1 - D a t a w a r e h o u s e Boris Ahodikpe - Renaud Joly - Delphine Lacour 1 Table des matières Introduction ............................................................................................................................................. 2 Partie 1 : Fonctions d’ETL .................................................................................................................... 3 I. Vérification de la validité des données ....................................................................................... 3 1. But ........................................................................................................................................... 3 2. Tables de rejet ......................................................................................................................... 3 3. Identification des données corrompues.................................................................................. 3 II. Transfert des données ................................................................................................................. 6 1. Création des tables sous SQLServer ........................................................................................ 6 2. Transfert des données ............................................................................................................. 7 3. Automatisation de la procédure ........................................................................................... 16 Partie 2 : Optimisation du Data Warehouse ..................................................................................... 19 I. Mise en place du partitionnement des tables de faits .............................................................. 19 II. Création d'un projet Analysis Services ...................................................................................... 24 Partie 3 : Mise en place du reporting ............................................................................................... 29 I. Création d’un projet Reporting Services ................................................................................... 29 1. Tableau simple....................................................................................................................... 29 2. Graphique .............................................................................................................................. 29 3. Tableau croisé........................................................................................................................ 30 II. Création d’un univers Business Objects .................................................................................... 31 1. Structure générale de l’univers ............................................................................................. 31 2. Définition d’un objet ............................................................................................................. 32 3. Gestion des tables d’agrégats ............................................................................................... 32 III. Edition de tableaux de bord Web Intelligence ...................................................................... 34 1. Sélection des données ........................................................................................................... 34 2. Mise en forme ....................................................................................................................... 35 3. Ajout de contrôles d’entrée .................................................................................................. 36 IV. Edition de tableaux de bord Excel ......................................................................................... 37 1. A partir d’un cube Analysis Services ...................................................................................... 37 2. Utilisation de filtres ............................................................................................................... 37 Conclusion ............................................................................................................................................. 39 Table des illustrations ............................................................................................................................ 40 B D 5 1 - D a t a w a r e h o u s e Boris Ahodikpe - Renaud Joly - Delphine Lacour 2 Introduction Pour valider l’acquisition des compétences de l’UV BD51, nous devons développer un système décisionnel pour la gestion des ventes par magasins pour la base de données Emode. Cette base est volumineuse et il est donc nécessaire de créer des outils pour permettre une exploration facilitée de ses données. Une base existe déjà sous Oracle et notre travail consiste à créer une autre base de données sous SQLServer. Celle-ci récupère l’ensemble des informations se trouvant dans la base Oracle. Après avoir mis en place le transfert des données entre ces deux bases, nous devons construire plusieurs outils (rapports, cube …). Dans un premier temps, nous expliquerons comment nous avons mis en œuvre la vérification puis le transfert des données ainsi que l’automatisation de ce transfert. Dans un deuxième temps, nous montrerons comment optimiser le Data Warehouse via la mise en place d’un partitionnement et la création d’un projet Analysis Services. Finalement, nous créerons différents rapports organisant les données de la base. B D 5 1 - D a t a w a r e h o u s e Boris Ahodikpe - Renaud Joly - Delphine Lacour 3 Partie 1 : Fonctions d’ETL I. Vérification de la validité des données 1. But Le schéma de la base Oracle n’indique aucune contrainte de clé primaire ou étrangère. Des données ne respectant pas ces contraintes peuvent donc avoir été introduites. Il faut donc identifier ces données que nous mettrons dans des tables de rejet. 2. Tables de rejet Afin de conserver une trace des données corrompues, nous créons d’abord pour chaque table du schéma une table de rejet. Une table de rejet reprend les mêmes colonnes que la table d’origine. Exemple : création d’une table de rejet pour la table des articles. create table article_reject ( article_code number(6,0) , article_label varchar2(45 byte) , category varchar2(25 byte) , sale_price number , family_name varchar2(20 byte) , family_code varchar2(3 byte) ); Cette syntaxe est celle d’Oracle et n’est pas forcément valable pour SQL Server. Il faudra donc faire attention à cela si on veut créer des tables de rejet sous plusieurs SGBD. 3. Identification des données corrompues Pour chaque table, une vérification d’unicité doit être faite sur la ou les colonnes correspondant à la clé primaire de la table. Pour chaque table pointant sur d’autres tables (par exemple SHOP_FACTS), on vérifie si les identifiants utilisés sont bien présents dans la table pointée (vérification des clés étrangères). a. Vérification de clé primaire Pour être clé primaire d’une table, un champ doit être unique. Une simple requête permet d’afficher les identifiants en double. B D 5 1 - D a t a w a r e h o u s e Boris Ahodikpe - Renaud Joly - Delphine Lacour 4 Exemple : affichage des codes d’articles en double dans la table des articles. select article_code, count(*) from article_lookup group by article_code having count(*) > 1; Cette requête permet ensuite de recopier les données corrompues dans la table de rejet correspondante. Exemple : copie des lignes correspondant aux codes d’articles en double dans la table de rejet. insert into article_reject(article_code, article_label, category, sale_price, family_name, family_code) select a1.article_code, a1.article_label, a1.category, a1.sale_price, a1.family_name, a1.family_code from article_lookup a1 inner join (select a2.article_code, count(*) from article_lookup a2 group by a2.article_code having count(*) > 1) a3 on a1.article_code = a3.article_code ; b. Vérification de clé étrangère Pour être clé étrangère d’une table, toutes les valeurs d’une colonne doivent exister dans la table correspondante. Une simple requête permet d’afficher les identifiants des lignes ne respectant pas cette contrainte. Exemple : affichage des codes d’articles présents dans la table destination (critère article) mais non présents dans la table source (article). select distinct alc.article_code from article_lookup_criteria alc where alc.article_code not in (select a.article_code from article_lookup a); Cette requête permet ensuite de recopier les données corrompues dans la table de rejet correspondante. B D 5 1 - D a t a w a r e h o u s e Boris Ahodikpe - Renaud Joly - Delphine Lacour 5 Exemple : copie des lignes correspondantes dans la table de rejet. insert into article_lookup_criteria_reject(id, article_code, criteria_type, criteria, criteria_type_label, criteria_label) select alc1.id, alc1.article_code, alc1.criteria_type, alc1.criteria, alc1.criteria_type_label, alc1.criteria_label from article_lookup_criteria alc1 inner join (select distinct alc2.id from article_lookup_criteria alc2 where alc2.article_code not in (select a.article_code from article_lookup a)) alc3 on alc1.id = alc3.id ; B D 5 1 - D a t a w a r e h o u s e Boris Ahodikpe - Renaud Joly - Delphine Lacour 6 II. Transfert des données 1. Création des tables sous SQLServer La base de données Emode contient 7 tables : 5 tables de dimension et 2 tables de faits. Voici le modèle de la base de données, pouvant conduire à deux modèles en étoile, l’un autour de la table SHOP_FACTS et l’autour de la table PRODUCT_PROMOTION_FACTS. Figure 1 - ETL : Modèle de la base Vous trouverez joins au rapport le script SQL permettant la création de ces tables et des contraintes qui les lient. Vous trouverez également le script SQL qui crée les tables de rejet qui contiendront les données non valides. Il existe une table de rejet par table dans Emode. Pour assurer le suivi des futurs transferts, nous avons créé deux tables d’audit. Celles-ci permettent de stocker les différents éléments qui tracent les opérations effectuées. Elles seront remplies lors des opérations de transfert dans les packages SSIS. La table AUDIT contient : - un identifiant généré automatiquement - le numéro de la tâche (transfert) - la date de début de la tâche - statut de l’alimentation La table AUDIT_ERROR contient : - un identifiant généré automatiquement - le numéro de la tâche (transfert) - la date de début de la tâche - l’ID de la ligne qui a rencontré un problème lors du transfert - le nom de la table dans laquelle il y a eu le problème - un champ d’information sur l’erreur B D 5 1 - D a t a w a r e h o u s e Boris Ahodikpe - Renaud Joly - Delphine Lacour 7 2. Transfert des données Le transfert de toutes les données contenu dans les tables de la base Emode sous Oracle vers une base sous SQLServer est réalisé à l’aide de packages SSIS développés sous Business Intelligence Development Studio. Pour optimiser les fonctionnalités de ce logiciel nous avons réglé la propriété maxconcurrentexecutables au maximum. Nous avons réalisé trois packages, chacun ayant une tâche différente. a. Premier package : transfert de la totalité des données Le premier package se nomme transfertDonnees.dtsx. Il permet de transférer la totalité des données de toutes les tables du serveur Oracle vers le serveur SQL Server. Ce package nécessite deux connexions : l’une à la base Oracle (SourceConnectionOLEDB) et l’autre à la base SQL Server (DestinationConnectionOLEDB). Figure 2 - ETL : Connexions requises dans le premier package Ce package comporte plusieurs étapes que vous pourrez voir sur la capture d’écran suivante. Tout d’abord, le package supprime toutes les données dans les tables de SQL Server à l’aide d’une commande SQL dans un « Execute SQL Task ». Nous supprimons les contraintes de clé étrangère, nous supprimons les données puis nous recréons les contraintes. Ensuite, nous transférons les données en plusieurs étapes grâce à des « Data Flow Task ». Les contraintes de clé étrangère nous forcent à entrer les uploads/S4/ bd51-projet-bi-datawarehouse-g5-lacour-v6.pdf