BD51 Projet Bi Datawarehouse G5 Lacour v6

UTBM – UTBM – Département Département Informatique

BD51 - Data warehouse Boris Ahodikpe - Renaud Joly - Delphine Lacour

A2011

BD51 - Data warehouse

Table des matières Introduction .................................................................................................................. ............................................................................................................................................. ........................... 2 Partie 1 : I.

Fonctions d’ETL d’ETL .................................................................................................................. ................................................................................................................... .. 3

Vérification de la validité des données ............................................................ ....................................................................................... ........................... 3 1.

But ................................................................... ......................................................................................................................................... ........................................................................ .. 3

2.

Tables de rejet ............................................................ ......................................................................................................................... ............................................................. 3

3.

Identification des données corrompues............................................ 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 : I.

Mise en place du reporting ........................................................... ............................................................................................... .................................... 29

Création d’un projet Reporting Services ................................................................................... Services ................................................................................... 29 1.

Tableau simple...................................... simple......................................................................................................... ................................................................................. .............. 29

2.

Graphique .................................................................... .............................................................................................................................. .......................................................... 29

3.

Tableau croisé.................................................. croisé..................................................................................................................... ...................................................................... ... 30

II.

Création d’un univers Business Objects .................................................................................... Objects .................................................................................... 31 1.

Structure générale de l’univers .......................................................................................... l’univers ............................................................................................. ... 31

2.

Définition d’un objet ............................................................................................................. objet ............................................................................................................. 32

3.

Gestion des tables d’agrégats ............................................................................................... 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 .................................................................................................. d’entrée .................................................................................................. 36

IV.

Edition de tableaux de bord Excel ................................................................ ......................................................................................... ......................... 37

1.

A partir d’un cube Analysis Services Services ...................................................................................... ..................................................................................... 37

2.

Utilisation de filtres ............................................................... ............................................................................................................... ................................................ 37

Conclusion ............................................................................................................................................. ............................................................................................................................................. 39 Table des illustrations.................................................................. ............................................................................................................................ .......................................................... 40

Boris Ahodikpe - Renaud Joly - Delphine Lacour

1


Table des matières Introduction .................................................................................................................. ............................................................................................................................................. ........................... 2 Partie 1 : I.

Fonctions d’ETL d’ETL .................................................................................................................. ................................................................................................................... .. 3

Vérification de la validité des données ............................................................ ....................................................................................... ........................... 3 1.

But ................................................................... ......................................................................................................................................... ........................................................................ .. 3

2.

Tables de rejet ............................................................ ......................................................................................................................... ............................................................. 3

3.

Identification des données corrompues............................................ 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 : I.

Mise en place du reporting ........................................................... ............................................................................................... .................................... 29

Création d’un projet Reporting Services ................................................................................... Services ................................................................................... 29 1.

Tableau simple...................................... simple......................................................................................................... ................................................................................. .............. 29

2.

Graphique .................................................................... .............................................................................................................................. .......................................................... 29

3.

Tableau croisé.................................................. croisé..................................................................................................................... ...................................................................... ... 30

II.

Création d’un univers Business Objects .................................................................................... Objects .................................................................................... 31 1.

Structure générale de l’univers .......................................................................................... l’univers ............................................................................................. ... 31

2.

Définition d’un objet ............................................................................................................. objet ............................................................................................................. 32

3.

Gestion des tables d’agrégats ............................................................................................... 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 .................................................................................................. d’entrée .................................................................................................. 36

IV.

Edition de tableaux de bord Excel ................................................................ ......................................................................................... ......................... 37

1.

A partir d’un cube Analysis Services Services ...................................................................................... ..................................................................................... 37

2.

Utilisation de filtres ............................................................... ............................................................................................................... ................................................ 37

Conclusion ............................................................................................................................................. ............................................................................................................................................. 39 Table des illustrations.................................................................. ............................................................................................................................ .......................................................... 40


1


Introduction Pour valider l’acquisition des compétences de l’UV BD5, 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. 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 m is 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. Analysis Services. Finalement, nous créerons différents rapports organisant les données de la base.


2


Partie 1 :

I.

Fonctions d’ETL

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 création d’une table 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.


3

BD51 - Data warehouse Exemple : affichage des codes d’articles en double dans la t able 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, family_name, family_code)

article_label,

category,

sale_price,

select a1.article_code, a1.article_label, a1.family_name, a1.family_code

a1.category,

a1.sale_price,

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.


4


Exemple : copie des lignes correspondantes dans la table de rejet. insert into article_lookup_criteria_reject(id, criteria_type_label, criteria_label)

article_code,

criteria_type,

criteria,

select alc1.id, alc1.article_code, alc1.criteria_type, alc1.criteria_type_label, alc1.criteria_label

alc1.criteria,

from article_lookup_criteria alc1 inner join (select from

distinct alc2.id article_lookup_criteria alc2

where a.article_code from article_lookup a)) alc3

alc2.article_code

not

in

(select

on alc1.id = alc3.id ;


5


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


6

BD51 - Data warehouse 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 maxconcurrentexecutables au maximum.

ce

logiciel

nous

avons

réglé

la

propriété

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 données dans un ordre précis. Nous commençons par insérer les données dans les tables sans clé étrangère, ensuite dans shop_facts puis dans product_promotion_facts et, pour finir, dans les tables restantes avec des clés étrangères. Après ces transferts, nous appellons le package TransfertTablesAggregat.dtsx grâce à un « Execute Package Task ». Quand le package a fini de s’exécuter, nous affichons un message confirmant sa bonne exécut ion grâce à un « Script Task ».


7


Figure 3 - ETL : Control flow

Chaque tâche se déroule environ de la même façon. Un « OLEDDB Source » récupère les données de la table choisie dans la base Oracle. Un « Lookup » permet de vérifier s’il y a des doublons dans la table grâce à une requête SQL. Nous redirigeons les champs ne correspondant pas à la requête vers la table de rejet. Les champs répondant à la requête sont dirigés soit vers la table destination soit vers un autre « Lookup » qui vérifie une contrainte de clé étrangère. Le principe reste le même jusqu’à ce que toutes les contraintes soient validées. Les champs qui ont été rejeté par un « Lookup » sont réunis dans un unique flux grâce à un « Union All ». Ce flux va ensuite vers la table de rejet.


8


Figure 4 - ETL : Data Flow de la table Article_lookup_criteria

b. Deuxième package : alimentation incrémentale et traçabilité du transfert

Le second package se nomme transfertDonneesAvecTracabilite.dtsx. Il permet de transférer uniquement les nouvelles données, de modifier celles qui l’ont été et de supprimer celles qui doivent l’être. Il intègre de plus la traçabilité du transfert. C’est une alimentation incrémentale. Création du schéma Emode_inc

Avant de créer ce package SSIS, nous avons dû au préalable créer un nouveau schéma Oracle qui nous a permis de stocker les changements de données intervenant dans la base Emode. connect / as sysdba; create user emode_inc identified by emode_inc; grant connect, create table to emode_inc;

Le schéma Emode_inc contient les mêmes tables de dimensions et de faits que le schéma Emode. Ces tables se nomment de la même façon avant en suffixe _inc. Elles ont la même structure et contiennent un champ supplémentaire « type_modification » qui stocke « inc » si la modification est une insertion, « upd » si c’est une mise à jour et « del » si c’est une suppression. Dans chaque table, il ne peut y avoir qu’une seule entrée pour une clé primaire des tables dans Emode. Si on insère des données avec une clé primaire 45, elle est insérée dans la table Emode_inc correspondante avec type_modification = « ins ». Ensuite si on supprime l’entrée de clé primaire 45, on met à jour l’entrée dans la table d’Emode_inc avec type_modification = « del ». create table article_color_lookup_inc ( article_code number(6) not null, color_code number(4) not null, article_label VARCHAR2 (45) , color_label VARCHAR2 (30) ,


9

BD51 - Data warehouse category VARCHAR2 (25) , sale_price NUMBER(10,2) , family_name VARCHAR2 (20) , family_code VARCHAR2 (3), type_modification varchar(3) not null ); alter table article_color_lookup_inc add constraint pk_acl_inc primary key ( article_code, color_code ) ;

Création des trigger après une modification dans Emode

Pour remplir les tables dans Emode_inc, nous avons créé des triggers dans Emode qui se lancent dès qu’une modification a lieu dans la base. Pour chaque table, nous avons créé trois triggers : un « after insert », un « after update » et un « after delete », dont le principe reste le même. Nous récupérons la clé primaire de la modification. Si la clé primaire est déjà présente dans la table _inc, nous modifions l’entrée correspondante sinon nous insérons les données. Voici un exemple de trigger sur la table article_color_lookup : -- Table article_color_lookup -CREATE OR REPLACE TRIGGER TAI_acl AFTER INSERT ON article_color_lookup FOR EACH ROW declare numrows INTEGER; BEGIN --on vérifie si il y a un déjà un champ avec cette clé primaire select count(*) into numrows from emode_inc.article_color_lookup_inc where :new.article_code = emode_inc.article_color_lookup_inc.article_code and :new.color_code = emode_inc.article_color_lookup_inc.color_code; --si oui, on met a jour, sinon, on insert if(numrows = 0) then insert into emode_inc.article_color_lookup_inc (article_code, color_code, article_label, color_label, category, sale_price, family_name, family_code, type_modification) values (:new.article_code,:new.color_code, :new.article_label, :new.color_label, :new.category, :new.sale_price, :new.family_name, :new.family_code, 'ins'); else update emode_inc.article_color_lookup_inc set type_modification = 'ins',


10

BD51 - Data warehouse article_label =:new.article_label, color_label =:new.color_label, category =:new.category, sale_price =:new.sale_price, family_name =:new.family_name, family_code =:new.family_code where ARTICLE_CODE

= :new.article_code

and COLOR_CODE

= :new.color_code;

end if; END; /

Pour que ces triggers puissent s’exécuter sans encombre, le schéma Emode doit avoir le droit d’insérer et de modifier dans les tables d’Emode_inc. Déroulement du package

Ce package nécessite trois connexions : l’une à la base Emode (SourceConnectionOLEDB), l’autre à la base SQL Server (DestinationConnectionOLEDB) et la dernière à Emode_inc.

Figure 5 : Connexions requises dans le deuxième package

Ce package comporte plusieurs étapes que vous pourrez voir sur la capture d’écran suivante. Premièrement, le package vide les tables de rejets grâce à un « Execute SQL Task ». Ensuite nous traitons les tables une par une dans un ordre précis grâce à des « Data Flow Task ». Nous commençons par les tables de dimensions sans clé étrangère, ensuite celles avec clé étrangère et nous finissons par les tables de faits Après chaque transfert de données d’une table, nous vidons la table du schéma Emode_inc correspondante grâce à un « Execute SQL Task ». Nous finissons de la même manière que le premier package.


11




12

BD51 - Data warehouse Chaque tâche se déroule environ de la même façon. Un « OLEDB Source » récupère les données de la table choisie dans la base Emode_inc. Ensuite une tâche « Audit » permet d’ajouter au flux d’informations le numéro de transfert et la date de début de l’exécution. « Multicast » permet de dupliquer le flux. Nous effectuons alors deux traitements en parallèle. D’un côté, nous remplissons la table d’audit. Le flux passe à travers un « Aggregate » qui évite qu’on ait une entrée dans la table Audit pour chaque entrée de la table _inc. On ne souhaite qu’une entrée par table et par transfert dans la table AUDIT. De l’autre côté, nous remplissons la table dans Emode. Grâce à un « Conditionnal Split », le flux est divisé en trois en fonction du champ type_modifcation. Nous traitons encore trois flux en parallèle, un pour l’insertion, un pour la modification et un pour la suppression. Ces traitements sont similaires. Un « Lookup » permet de vérifier si la clé primaire existe déjà dans la table d’Emode. Si c’est le cas, pour l’insertion, l’entrée est rejetée contrairement à la mise à jour et à la suppression. Dans le cas d’une table avec des clés étrangère, nous véri fions ensuite les clés étrangères avec un « Lookup ». Nous redirigeons les champs ne correspondant pas à la requête vers la table de rejet. Les champs validant toutes les contraintes sont dirigés vers un « OLE DB Command » qui exécute une requête SQL paramétrée, soit INSERT INTO soit UPDATE soit DELETE dans la table du schéma Emode. Si cette requête a réussi, le transfert de ces données a bien été effectué sinon un flux d’erreur est créé. Les trois flux d’erreurs sont réunis dans un unique flux grâce à un « Union All ». Il contient l’identifiant de l’entrée et le code d’erreur. Nous ajoutons à ce flux le nom de la table qui est créé manuellement dans un « Derived Column ». Finalement, ce flux est envoyé vers la table ERROR_AUDIT.


13


Figure 6 - ETL : Data Flow de la table Article_color_lookup

c.

Troisième package : transfert des tables d’ agrégat

Le troisième package se nomme TransfertTablesaggregat.dtsx. Il permet de mettre à jour les tables d’agrégat de la base Emode dans SQL Server en fonction des nouvelles données insérées. Il est appelé depuis les deux premiers packages. Il ne nécessite qu’une seule connexion, celle à la base SQL Server (DestinationConnectionOLEDB). En effet, il n’utilise que les données de la base dans SQL Server. Vous pouvez voir le déroulement de ce package sur l’image suivante.



14

BD51 - Data warehouse Il contient deux « Execute SQL Task ». L’un vide les tables d’agrégat et l’autre insère les nouvelles données à partir de requêtes INSERT INTO … SELECT. Voici les deux requêtes d’insertion. --agg_yr_qt_rn_st_ln_ca_sr Insert into AGG_YR_QT_RN_ST_LN_CA_SR (YEAR,QUARTER,STATE,LINE,CATEGORY,SALES_REVENUE) Select cyl.year , 'Q' + convert(varchar(1),cyl.quarter) as quarter , ol.state , al.family_name , al.category , sum(sf.amount_sold) as sales_revenue From dbo.calendar_year_lookup cyl inner join shop_facts sf on cyl.week_key = sf.week_key inner join outlet_lookup ol on ol.shop_code = sf.shop_code inner join article_lookup al on al.article_code = sf.article_code Group by cyl.year , 'Q' + convert(varchar(1),cyl.quarter) , ol.state , al.family_name , al.category Order by 1,2,3,4,5,6; --agg_yr_thru_sn_sr_qt_ma Insert into AGG_YR_THRU_SN_SR_QT_MA (YEAR,QUARTER,MONTH,MONTH_NAME,WEEK,CITY,STORE_NAME,SALES_REVENUE,QUANTITY_SOLD,MA RGIN) Select cyl.year , 'Q' + convert(varchar(1),cyl.quarter) as quarter , cyl.month , cyl.month_name , cyl.week_in_year as week , ol.city , ol.shop_name as store_name


15

BD51 - Data warehouse , sum(sf.amount_sold) as sales_revenue , sum(sf.quantity_sold) as quantity_sold , sum(sf.margin) as margin From calendar_year_lookup cyl inner join shop_facts sf on cyl.week_key = sf.week_key inner join outlet_lookup ol on ol.shop_code = sf.shop_code Group by cyl.year , 'Q' + convert(varchar(1),cyl.quarter) , cyl.month , cyl.month_name , cyl.week_in_year , ol.city , ol.shop_name Order by 1,2,3,4,5,6,7,8,9,10;

3. Automatisation de la procédure

Une fois le package développé, il existe plusieurs manière d’automatiser l’exécution de celui -ci. L’exécution peut se faire de manière immédiate à partir du fichier dtsx. Nous pouvons aussi planifier l’exécution à l’aide SQL Agent ou d’une tâche au niveau du système d’exploitation. a. Exécution immédiate

Nous pouvons exécuter le package en l’ouvrant avec le logiciel SQLServer Business Intelligence Development Studio puis en cliquant sur Debug > Start debugging. Le logiciel permet de voir les erreurs qui ont pu se produire lors du transfert.

Figure 8 - ETL : Exécution immédiate du package


16

BD51 - Data warehouse b. Tâche planifiée au niveau système d’exploitation

Pour créer une tâche planifiée, il faut aller dans All programs > Accessories > System Tools > Scheduled tasks.

Figure 9 - ETL : Scheduled tasks

Dans cette fenêtre il suffit de créer une nouvelle tâche qui fera référence à notre package SSIS. Cette tâche est planifiée à un certain intervalle, selon le jour et l’heure. A partir de la fenêtre Scheduled Tasks, chaque tâche peut être exécutée manuellement.

Figure 10 - ETL : Propriétés tâche système


17

BD51 - Data warehouse Nous pouvons suivre l’exécution via la fenêtre de console qui trace le déroulement de l’exécution du package. c.

Travail dans SQL Agent

Dans SQL Server Management Studio, nous pouvons voir SQL Server Agent. Celui-ci nous permet de créer des « jobs » qui permettra de planifier l’exécution de notre packages SSIS. Comme toute tâche planifiée, une option permet de déterminer la fréquence d’exécution du packages (par mois, par semaine, par jour ainsi que l’heure d’exécution, …). Il est aussi possible de configurer la gestion des erreurs afin de l’améliorer. Les journaux d’erreurs vont apparaitre dan s le dossier Error logs, dernier dossier de l’arborescence.

Figure 11 - ETL : Job dans SQL Server Agent

Le package utilisé peut être directement indiqué dans le file system, ou bien il peut aussi être déployé sur le serveur.


18


Partie 2 : I.

Optimisation du Data Warehouse

Mise en place du partitionnement des tables de faits

Nous avons mis en place un système de partitionnement sur nos tables de fait. Ces tables possèdent de nombreux enregistrements, ainsi, le partitionnement répond à des problèmes de tables très volumineuses en permettant de les décomposer en partitions plus petites, ce qui facilite l’accès et de mise à jour des données. La création de table de partitionnement sur une base de données SQL Server n’est pas la même manière que celle d’une base Oracle. Sur une base Oracle, il suffit de créer nos tables de partition en rajoutant le mot clé partition, avec les paramètres de partitionnement. Il est possible d’utiliser différent mode de partitionnement. Avec SQL Server, seul le partitionnement par plage de valeur est possible. Pour pouvoir créer une table partitionnée, il faut commencer par créer sa fonction de partitionnement. Son rôle est de définir des "points de partitionnement" ou les valeurs qui délimitent les partitions. Ensuite, il est nécessaire de créer un schéma de partitionnement. Un schéma de partitionnement spécifie les zones de partitionnement physiques et les associent à la fonction de partitionnement, ces zones de partitionnement sont appelé filegroups. Il suffit de créer notre table partitionnée et lui associer le schéma de partitionnement crée précédemment avec les champs désirés comme paramètre de fonction de partitionnement. Pour notre base, nous avons décidé de découper la table SHOP_FACTS en 6 partitions. La valeur de notre champ WEEK_KEY permet de classifier les enregistrements. L’enregistrement possédant une valeur de 1 à 53 comme WEEK_KEY se retrouve dans la première table, de 53 à 105 pour la deuxième table etc… La découpe a été faite telle que chaque partition corresponde aux semaines d’une même année. USE EMODE_sauvegarde go alter database EMODE_sauvegarde add Filegroup emode_part_1997 go alter database EMODE_sauvegarde add Filegroup emode_part_1998 go alter database EMODE_sauvegarde add Filegroup emode_part_1999 go alter database EMODE_sauvegarde add Filegroup emode_part_2000


19

BD51 - Data warehouse go alter database EMODE_sauvegarde add Filegroup emode_part_2001 go alter database EMODE_sauvegarde add Filegroup emode_part_2010 go alter database EMODE_sauvegarde add Filegroup emode_part go alter database EMODE_sauvegarde ADD FILE ( NAME = emode_data_part_1997, FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_1997.ndf', SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 10MB ) TO FILEGROUP emode_part_1997; GO alter database EMODE_sauvegarde ADD FILE ( NAME = emode_data_part_1998, FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_1998.ndf', SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 10MB ) TO FILEGROUP emode_part_1998; GO alter database EMODE_sauvegarde ADD FILE ( NAME = emode_data_part_1999,


20

BD51 - Data warehouse FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_1999.ndf', SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 10MB ) TO FILEGROUP emode_part_1999; GO alter database EMODE_sauvegarde ADD FILE ( NAME = emode_data_part_2000, FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_2000.ndf', SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 10MB ) TO FILEGROUP emode_part_2000; GO alter database EMODE_sauvegarde ADD FILE ( NAME = emode_data_part_2001, FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_2001.ndf', SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 10MB ) TO FILEGROUP emode_part_2001; GO alter database EMODE_sauvegarde ADD FILE ( NAME = emode_data_part_2010, FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part_2010.ndf', SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 10MB ) TO FILEGROUP emode_part_2010; GO


21


alter database EMODE_sauvegarde ADD FILE ( NAME = emode_data_part, FILENAME = 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\emode_data_part.ndf', SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 10MB ) TO FILEGROUP emode_part; GO --------------------USE EMODE_sauvegarde GO create partition function FoncPart (NUMERIC(5,0)) as range left for values (53,105, 157,209,262,314); GO CREATE PARTITION SCHEME SchemaPart as partition FoncPart to (emode_part_1997,emode_part_1998,emode_part_1999,emode_part_2000,emode_part_2001,emo de_part_2010, emode_part) go CREATE TABLE SHOP_FACTS_PART ( ID NUMERIC (5,0) NOT NULL , ARTICLE_CODE NUMERIC (6,0) , COLOR_CODE NUMERIC (4,0) , WEEK_KEY NUMERIC (3,0), SHOP_CODE NUMERIC (4,0), MARGIN NUMERIC (18,0) , AMOUNT_SOLD NUMERIC (13,2) , QUANTITY_SOLD NUMERIC (13,2), CONSTRAINT SHOP_FACTS_PART_PK PRIMARY KEY NONCLUSTERED (ID) ) on SchemaPart(ID) GO ALTER TABLE SHOP_FACTS_PART


22

BD51 - Data warehouse ADD FOREIGN KEY(ARTICLE_CODE, COLOR_CODE) REFERENCES ARTICLE_COLOR_LOOKUP (ARTICLE_CODE, COLOR_CODE); ALTER TABLE SHOP_FACTS_PART ADD FOREIGN KEY(ARTICLE_CODE) REFERENCES ARTICLE_LOOKUP (ARTICLE_CODE); ALTER TABLE SHOP_FACTS_PART ADD FOREIGN KEY(WEEK_KEY) REFERENCES CALENDAR_YEAR_LOOKUP (WEEK_KEY); ALTER TABLE SHOP_FACTS_PART ADD FOREIGN KEY(SHOP_CODE) REFERENCES OUTLET_LOOKUP (SHOP_CODE); insert into SHOP_FACTS_PART select * from SHOP_FACTS; CREATE TABLE PRODUCT_PROMOTION_FACTS_PART ( ID NUMERIC (5,0) NOT NULL , ARTICLE_CODE NUMERIC (6,0) , WEEK_KEY NUMERIC (3,0) , DURATION NUMERIC (2,0) , PROMOTION_KEY NUMERIC (2,0) , PROMOTION_COST NUMERIC (18,0), CONSTRAINT PROD_PROM_FACTS_PART_PK PRIMARY KEY ( ID ) ) on SchemaPart(ID) go ALTER TABLE PRODUCT_PROMOTION_FACTS_PART ADD FOREIGN KEY(ARTICLE_CODE) REFERENCES ARTICLE_LOOKUP(ARTICLE_CODE); ALTER TABLE PRODUCT_PROMOTION_FACTS_PART ADD FOREIGN KEY(WEEK_KEY) REFERENCES CALENDAR_YEAR_LOOKUP(WEEK_KEY); insert into PRODUCT_PROMOTION_FACTS_PART select * from PRODUCT_PROMOTION_FACTS;


23


II.

Création d'un projet Analysis Services

SQL Server Analysis Services fournit des fonctions OLAP et d'exploration de données pour les applications décisionnelles. Analysis Services, à l’aide de ces fonctions, permet de créer et gérer des modèles multidimensionnels, ici de notre base de données relationnelles Emode ; Dans notre projet Analysis Services, plusieurs dimensions sont définies qui représentent les composants de notre cube. Ces dimensions organisent les données par domaine (Temps, géographie, Employé, Client …). Elles contiennent des attributs qui correspondent à des colonnes de tables de la base de données. Les attributs peuvent être mis dans des hiérarchies, qui sont utilisée pour organiser les mesures contenues dans notre cube. Dans notre cas, quatre hiérarchies ont été créées : -

Shops : liste des magasins hiérarchisés par l’état du magasin, puis la ville.

Figure 12 - SSAS : Hiérarchie des magasins

-

Date : dimension pour organiser les années, les trimestres, les mois et les semaines au sein d’une hiérarchie.

Figure 13 - SSAS : Hiérarchie des dates


24


-

Article : organisation des produits par famille et catégorie

Figure 14 - SSAS : Hiérarchie des articles

-

Article_color : même organisation que la précédente avec l’intégration de la couleur des produits en plus

Figure 15 - SSAS : Hiérarchie des articles et couleurs

Nous avons donc toutes les dimensions nécessaires à la création de notre cube. Sa structure est donc la suivante avec en jaune les faits et en bleu les dimensions :


25


Figure 16 - SSAS: Structure du cube

Nos données sont prêtes à être parcourues à l’aide des différentes dimensions. Pour plus de complétude, des mesures calculées (Calculations) ont été créées. Celles-ci permettent d’avoir, en plus des mesures d’origine, le pourcentage de marge par rapport au chiffre d’affaire, le pourcentage du chiffre d’affaire que représente une catégorie de produit par rapport au chiffre d’affaire total et pour finir, le pourcentage de la marge que représente une catégorie de produit par rapport à la marge total.

Figure 17 - SSAS : Exemple de mesure calculée

Ces nouvelles mesures sont ajoutées à celles déjà présentes. A leur côté, on peut y voir les différentes dimensions (hiérarchie et autres attributs) créées auparavant et qui vont nous servir pour parcourir les données.


26


Figure 18 - SSAS : Attributs du cube

Figure 19 - SSAS : Parcours cube, Chiffre d’affaire et Pourcentage chiffre d’affair e par famille de produit

L’illustration précédente montre le chiffre d’affaire ainsi que le pourcentage que représente le chiffre d’affaire de chacune des familles de produit. Ce parcours s’appuie sur deux hiérarchies (Product et Date) ainsi que sur la mesure « Revenue » et la mesure calculée « Revenue ratio to all articles ».


27


Figure 20 - SSAS : Parcours cube, Chiffre d'affaire par hiérarchie de produit et de magasin

Cette nouvelle illustration s’appuie sur les hiérarchies concernant les produits et celle concernant les magasins.


28


Partie 3 : I.

Mise en place du reporting

Création d’un projet Reporting Services

Microsoft SQL Server Reporting Services (SSRS) propose des outils pour créer des rapports sur le contenu d’un entrepôt de données. On peut, par exemple, éditer des tableaux simples, des graphiques ou encore des tableaux croisés. 1. Tableau simple

L’exemple ci-dessous montre un tableau simple, affichant les chiffres d’affaires des différents magasins de l’entreprise au cours des années 1999 à 2001. Les données numériques peuvent faire l’objet de totaux en pied de colonne.

Figure 21 – SSRS : Tableau simple

2. Graphique

L’exemple suivant utilise les mêmes données en les présentant sous forme de graphique. On peut ainsi, par exemple, facilement constater : -

l’évolution du chiffre d’affaires d’un magasin au fil des années la différence de chiffre d’affaires entre plusieurs magasins.


29


Figure 22 – SSRS : Graphique

3. Tableau croisé

Enfin, pour obtenir des informations plus détaillées, afficher des sous-totaux, on peut mettre en place des tableaux croisés. On verra ainsi, sur cet exemple, les différents États ainsi que les magasins dépendant de ces territoires en en-têtes de ligne et les années en en-têtes de colonnes. Cela permet notamment d’obtenir des sous-totaux par État et de pouvoir les comparer entre eux.


30


Figure 23 – SSRS : Tableau croisé

II.

Création d’un univers Business Objects

Pour générer des rapports Web Intelligence, il faut préalablement développer un « univers » BO. Cela permet de créer des classes et objets, utilisés dans les requêtes de rapports, et d’indiquer où sont les données correspondant à ces objets. 1. Structure générale de l’univers

Comme le montre le schéma suivant, le modèle de l’entrepôt de données est un modèle en étoile organisé autour de la table des ventes. Les différentes dimensions de l’univers (date, article, couleur, magasin) sont reliées directement à cette table. Les deux tables indépendantes sont des tables d’agrégats. L es informations contenues proviennent des cinq tables du modèle en étoile mais sont agrégées de manière à obtenir une granularité moins fine. Par exemple, dans la seconde table d’agrégat, les ventes d’un m ême État sont regroupées au lieu d’être divisées par ville et par magasin.


31


Figure 24 – BO : Univers

2. Définition d’un objet

Un objet classique (« Année/semaine ») est relié à une colonne d’une des tables du modèle. Un objet peut être indiqué comme un détail d’un autre objet (« Nom du mois » pour « Mois »). Une classe peut aussi regrouper des objets dans une sous-classe.

Figure 25 – BO : Définition d'objets

3. Gestion des tables d’agrégats

Enfin, comme évoqué plus haut, un objet provient à la base d’une des tables du modèle en étoile mais peut aussi se trouver dans une table d’agrégat. Il faut alors indiquer dans la définition de l’objet dans quelle table on peut aller chercher cet élément. Boris Ahodikpe - Renaud Joly - Delphine Lacour

32

BD51 - Data warehouse L’objet « État » provient de la dimension des magasins mais est aussi dans la première table d’agrégat. On indique alors cela dans le champ « Select » de sa définition grâce à la fonction « @Aggregate_Aware ». Le nom de la table d’agrégat doit figurer en première position dans la liste des tables sources. Un objet peut provenir de plusieurs tables d’agrégats.

Figure 26 – BO : Champs agrégés

Néanmoins, une table d’agrégat ne comprenant pas l’objet « Ville » ne pourra pas être utilisée dans une requête demandant cet objet. Il faut donc déclarer les incompatibilités d’une table d’agrégat avec les objets de l’univers. La fenêtre présentée ci-dessous permet de configurer cela.

Figure 27 – BO : Incompatibilités de tables d'agrégats


33


III.

Edition de tableaux de bord Web Intelligence

Maintenant que l’univers est en place, nous pouvons éditer des tableaux de bord à partir du contenu de l’entrepôt de données. Le procédé se déroule en trois étapes : -

sélection des données par une requête mise en forme des données à l’aide de tableaux ou de graphiques ajout de contrôles d’entrée.

1. Sélection des données

La première étape de construction d’un rapport consiste à sélectionner les données qui nous intéressent. On choisit parmi les objets de l’univers que l’on retrouve dans le panneau gauche de la fenêtre de l’éditeur de requêtes. Il suffit de glisser les objets dans le panneau « Result Objects » et de cliquer sur « Run Query ».

Figure 28 – BO : Editeur de requêtes


34

BD51 - Data warehouse 2. Mise en forme

Un rapport est ensuite généré automatiquement avec, par défaut, un tableau affichant les données sélectionnées. a. La première chose faite ici est de mettre l’année en section. Cela permet d’avoir un tableau par année et non pas les données de toutes les années dans le même tableau. b. Ensuite on choisit d’avoir un tableau croisé avec les États en lignes et les mois de l’année en colonnes, de façon à obtenir le total des ventes de chaque magasin pour chaque mois. On ajoute en bas de colonnes le total des ventes par mois et en bout de ligne le total des ventes par magasin. c. On ajoute ensuite, via l’onglet « Templates » du panneau de gauche, un graphique reprenant les mêmes données afin de bien illustrer. On affiche les noms des États en légende. d. Enfin on formate les nombres (l’année sans virgule, les ventes en format monnaie), on donne un titre au rapport et on met le tout au format paysage afin de pouvoir afficher les douze mois de chaque année. En évitant de couper les sections, on obtient une année (un tableau et un graphique) par page.

Figure 29 – BO : Mise en forme

Le même travail a été effectué pour visualiser graphiquement les quantités vendues par année et par État. Ces quantités ont été rapportées en pourcentage.


35

BD51 - Data warehouse 3. Ajout de contrôles d’entrée

BO permet d’ajouter des « contrôles d’entrée » à un rapport. Cela permet de filtrer les données à afficher. En fonction du type de données, plusieurs contrôles sont disponibles. Cela peut prendre la forme d’une liste déroulante, d’un champ de saisie simple ou encore de boutons radios pour saisir une valeur unique. Si on souhaite pouvoir saisir plusieurs valeurs dans un filtre, on peut choisir une liste de cases à cocher ou une liste de valeurs. Dans tous les cas, l’utilisateur peut choisir « All values ».

Figure 30 – BO : Contrôles d'entrée

Ces contrôles ont été ajoutés à tous les rapports affichant des données.


36


IV.

Edition de tableaux de bord Excel

Excel permet aussi de créer des tableaux de bord à partir d’une source de données externe.

1. A partir d’un cube Analysis Services

Ici on retrouve toutes les composantes du cube Analysis Services évoqué dans la partie 2. Le tableau croisé affiche les ventes de produits par année ainsi que des totaux. Cependant les dimensions du cube nous permettent de naviguer dans ce tableau à travers les hiérarchies de produit et de date. Cela permet d’avoir un résultat global que l’on peut affiner.

Figure 31 – Excel : Tableau croisé dynamique et cube SSAS

2. Utilisation de filtres

Power Pivot permet de créer des « Découpages » verticaux et horizontaux sur le tableau croisé dynamique. De la même façon que les contrôles d’entrée de BO, cet outil permet de filtrer les données. Sur l’exemple suivant, on peut par exemple choisir un seul État et visualiser les ventes de ses magasins de 1999 à 2001.


37


Figure 32 – Excel : Filtres


38


Conclusion Ce projet nous a permis de découvrir les différentes phases de la conception de systèmes décisionnels ainsi que les différents outils du marché disponibles pour développer ces applications.

Nous avons d’abord mis en place des procédures d’ ETL (« Extract-Transform-Load ») à l’aide de scripts SSIS (SQL Server Integration Services). Cela permet de vérifier la qualité des données de la base source, où elles sont enregistrées sous Oracle, et de les transférer dans une base destination sous SQL Server, utilisée comme data warehouse pour le développement d’outils statistiques. Ensuite, dans un souci d’optimisation des performances du data warehouse, nous avons partitionné les tables de faits (ventes et promotions), du fait qu’elles contiennent un très grand nombre de données. Nous avons aussi utilisé l’outil SSAS (SQL Server Analysis Services) pour créer un « cube » avec notamment la création de hiérarchies au sein des tables de dimensions. Enfin, nous avons pu manipuler différents outils permettant de créer des tableaux de bord à partir des données stockées. Nous avons dans un premier temps généré des r apports simples avec tableaux, tableaux croisés et graphiques grâce à SSRS SQL Server Reporting Services). Nous avons dans un deuxième temps créé un univers BO (Business Objects) et développé des rapports avec Web Intelligence. Nous avons, pour terminer, utilisé le tableur Excel et son complément Power Pivot pour créer des tableaux croisés dynamiques à partir du data warehouse.

On peut dire que ce projet a été une bonne introduction au domaine de la Business Intelligence.


39

BD51 Projet Bi Datawarehouse G5 Lacour v6

Recommend Documents