TP Business Intelligence

Order_Fact

#Customer_ID INTEGER #Employee_ID INTEGER #Street_ID INTEGER #Product_ID INTEGER #Order_Date DATE #Order_ID INTEGER Order_Type SMALLINT Delivery_Date DATE Quantity SMALLINT Total_Retail_Price DECIMAL(13,2) Costprice_Per_Unit DECIMAL(13,2) Discount DECIMAL(5,2)

Customer_Dim

Customer_ID INTEGER Customer_Country CHARACTER(2) Customer_Group CHARACTER(40) Customer_Type CHARACTER(40) Customer_Gender CHARACTER(1) Customer_Age_Group CHARACTER(12) Customer_Age SMALLINT Customer_Name CHARACTER(40) Customer_Firstname CHARACTER(20) Customer_Lastname CHARACTER(30) Customer_Birth_Date DATE

Organization_Dim

Employee_ID INTEGER Employee_Country CHARACTER(2) Company CHARACTER(30) Department VARCHAR(40) Section VARCHAR(40) Org_Group VARCHAR(40) Job_Title VARCHAR(25) Employee_Name VARCHAR(40) Employee_Gender CHARACTER(1) Salary DECIMAL(13) Employee_Birth_Date DATE Employee_Hire_Date DATE Employee_Term_Date DATE

Geography_Dim

Street_ID INTEGER Continent VARCHAR(30) Country CHARACTER(2) State_Code CHARACTER(2) State VARCHAR(25) Region VARCHAR(30) Province VARCHAR(30) County VARCHAR(60) City VARCHAR(30) Postal_Code CHARACTER(10) Street_Name VARCHAR(45)

Product_Dim

Product_ID INTEGER Product_Line CHARACTER(20) Product_Category CHARACTER(25) Product_Group CHARACTER(25) Product_Name CHARACTER(45) Supplier_Country CHARACTER(2) Supplier_Name CHARACTER(30) Supplier_ID INTEGER

Time_Dim

Date_ID DATE Year_ID CHARACTER(4) Quarter CHARACTER(6) Month_Name VARCHAR(20) Weekday_Name VARCHAR(20) Month_Num SMALLINT Weekday_Num SMALLINT

Création des tables de l’entrepôt

Une fois le schéma en étoile validé, il faut créer l’entrepôt sous Oracle.

1) Travail à réaliser :

- Créer un utilisateur nommé orion_DW_user qui sera le propriétaire de

l’entrepôt :

o CREATE USER orion_DW_user IDENTIFIED BY orion_DW_user; o GRANT ALL PRIVILEGES TO orion_DW_user;

- Implémenter la création des tables de l’entrepôt sous Oracle en

spécifiant bien les clés primaires et les clés étrangères.

Maintenant que les tables de l’entrepôt sont créées, il faut réaliser les

processus qui vont remplir ces tables à partir des données sources.

5

Découverte de Talend Open Studio

2) Travail à réaliser :

- Ouvrir Talend Open Studio. - Créer un nouveau projet nommé orion_project avec l’option Java. - Dans la fenêtre « Generation Engine Initialization in progress », cocher la case Always run in background puis cliquer sur Run in

background.

Avant de commencer à travailler sous Talend, toujours attendre que la

« Generation Engine Initialization in progress » (en bas à droite) soit

terminée.

La fenêtre de Talend Open Studio est composée des vues suivantes :

- Barres d’outils et menus (en haut) - Repository (en haut à gauche) : Ce référentiel contient tous les

éléments techniques du projet

- Design Workspace (au centre) : Cet espace de modélisation permet de

concevoir graphiquement les business model et les jobs.

- Palette (en haut à droite) : Cette palette graphique permet d'accéder

aux différents composants

- Différentes vues (en bas au centre) :

o Job : infos sur le job sélectionné o Component : configuration du composant sélectionné o Run job : exécution des jobs o Problems : erreurs

- Outline et Code Viewer (en bas à gauche) : Ces fenêtres fournissent

un aperçu du code et du schéma du job ou du business model.

Business Models

Un business model permet de modéliser avec des composants graphiques, le

processus à mettre en place.

Pour ce système décisionnel, voici le processus à mettre en place :

Des données sources, fichier Excel + base de données Access (composants

Input et Database), vont être traitées par différents jobs ETL (composant

Gear) pour remplir l’entrepôt (composant Database). Un datamart (composant

Database) sera créé ensuite à partir de l’entrepôt. Les utilisateurs

(composant Actor) accèderont à l’entrepôt ou au datamart par le biais de

leur PC grâce aux outils de restitution (composant Terminal).

6

3) Travail à réaliser :

- Créer un nouveau Business model nommé orion_model. - Créer le modèle en choisissant les différents composants graphiques

situés dans la palette.

Spécification des données sources

4) Travail à réaliser :

- Placer les différents fichiers Excel et Access dans un répertoire

C:/orion.

- Etablir une connexion orion_BD à la base access orion.mdb :

o Dans le Repository, clic droit sur Metadata / Db Connections /

Create connection - Récupérer les schémas des tables

o Clic droit sur la connexion orion_BD : Retrieve Schema o Cliquer sur Next. o Sélectionner les tables nécessaires au projet. o Cliquer sur Next. o Pour chaque table, il y a le type de chaque colonne dans la base de données sources (DB Type) et sa traduction dans Talend

(Type). Pour simplifier, seulement 3 types seront utilisés

ici : Double, String et Date. Modifier alors pour chaque table,

la traduction des DATETIME en Date (au lieu de String).

o Cliquer sur Finish.

- Récupérer le schéma du fichier staff.xls

o Dans le Repository, clic droit sur Metadata / File Excel /

Create file Excel

o Name : staff o Cliquer sur Next o File : C:/orion/staff.xls o Sélectionner la feuille _t1 du fichier staff.xls (Sheet) o Cliquer sur Next o Cocher l’option Set heading row as column names puis Refresh

Preview

o Cliquer sur Next o Name : staff o Modifier les types des colonnes de façon à n’avoir que des

Double, des String ou des Date.

o Cliquer sur Finish

7

Spécification des données cibles

5) Travail à réaliser :

- Etablir une connexion orion_DW à l’entrepôt de données : - Récupérer les schémas des tables

Les données sources et cibles sont maintenant disponibles dans le

Repository. Il faut alors construire les différents jobs pour remplir les

tables de l’entrepôt.

Remplissage de la table Customer_Dim

6) Travail à réaliser :

- Pour chaque colonne de la table Customer_Dim, spécifier de quelle(s)

donnée(s) source elle dépend.

Table cible Colonne cible Table(s) source Colonne(s) source Remarques

7) Travail à réaliser :

- Créer un job nommé Job01_Customer_Dim.

- Choisir les tables sources (Customer puis Customer_Type) et les

importer dans le Design Workspace avec l’option tAccessInput.

- Choisir la table cible (Customer_Dim) et l’importer dans le Design

Workspace avec l’option tOracleOutput.

8

- Ajouter ensuite, le composant Processing / tMap, pour faire le lien

entre les données sources et les données cibles. - Ajouter les liens entre les différents composants :

o A partir des composants tAccessInput, clic droit, Row, Main o Renommer les liens avec customer et customer_type. o A partir du composant tMap, clic droit, Row, *New output*

(Main)

o Donner un nom au lien : customer_dim. o Une fenêtre s’ouvre répondre yes, pour prendre en compte le

schéma de la table cible.

- Dans le Design workspace, vous afficherez un petit commentaire pour décrire le job à l’aide du composant Misc / Note (à faire pour tous

les jobs).

- Dans le composant tOracleOutput :

o Dans la vue Component, spécifier dans Action on table : Clear table, cela supprime les données de la table avant d’en insérer

de nouvelles (à faire pour tous les jobs).

- Dans le composant tMap :

o Double-clic sur le composant tMap. o Faire une jointure entre les deux tables sources. o Relier les colonnes sources aux colonnes cibles. o Créer une nouvelle variable avec l’âge des clients :

(cid:1) Expression :

Mathematical.INT(TalendDate.formatDate("yyyy",TalendDate.

getCurrentDate()))-

9

Mathematical.INT(TalendDate.formatDate("yyyy",customer.Bi

rth_Date)) (cid:1) Type : double (cid:1) Variable : age

o La colonne cible CUSTOMER_AGE est égale à cette variable age. o La colonne cible CUSTOMER_AGE_GROUP est définie de la façon

suivante :

(cid:1) Var.age<30?"<30 years":

Var.age<46?"30-45 years":

Var.age<61?"46-60 years":

Var.age<76?"61-75 years":

">75 years"

o Cliquer sur OK

- Dans la fenêtre Run, cocher la case Statistics, puis lancer le job en

cliquant sur Run (ou F6).

- Sous Oracle, vérifier le résultat du job en lançant les requêtes

suivantes :

SELECT COUNT(*)

FROM Customer_Dim;

SELECT *

FROM Customer_Dim

WHERE ROWNUM<10;

Remplissage de la table Product_Dim

8) Travail à réaliser :

- Pour chaque colonne de la table Product_Dim, spécifier de quelle(s)

donnée(s) source elle dépend.

- Créer un job nommé Job02_Product_Dim. - Choisir les tables sources et la table cible. - Ajouter le composant Processing / tMap puis ajouter les liens entre les différents composants (renommer les liens avec des noms

pertinents).

- Dans le premier composant tAccessInput (product_list par exemple) :

o Modifier la requête pour ne prendre en compte que les produits : Dans la vue Component, dans Query, modifier la

requête.

o Procéder de la même façon pour les autres composants.

- Dans le composant tMap :

o Faire les jointures entre les différentes tables sources. o Relier les colonnes sources aux colonnes cibles.

- Lancer le job avec les statistiques. - Vérifier le résultat du job sous Oracle.

10

Remplissage de la table Organization_Dim

9) Travail à réaliser :

- Pour chaque colonne de la table Organization_Dim, spécifier de

quelle(s) donnée(s) source elle dépend.

- Créer le job Job03_Organization_Dim. - Lancer le job et vérifier le résultat sous Oracle.

Remplissage de la table Time_Dim

Dans cette table, il faut rentrer toutes les dates du 01/01/1998 au

31/12/2002.

10)

Travail à réaliser :

- En vous aidant de l’exemple ci-dessous, créer un programme en PL/SQL,

qui remplit cette table.

DECLARE

…

vQuarter CHARACTER(6);

vMonth_Name VARCHAR(20);

…

BEGIN

…

WHILE …

LOOP

…

vQuarter := TO_CHAR(vDate_ID,'YYYY')||'Q'||TO_CHAR(vDate_ID,'Q');

vMonth_Num := TO_NUMBER(TO_CHAR(vDate_ID,'MM'));

…

INSERT INTO Time_Dim VALUES (…);

…

END LOOP;

END;

/

- Exécuter le programme sous Oracle et vérifier le résultat.

Remplissage de la table Geography_Dim

11)

Travail à réaliser :

- Pour chaque colonne de la table Geography_Dim, spécifier de quelle(s)

donnée(s) source elle dépend.

11

- Créer le job Job04_Geography_Dim. - Lancer le job et vérifier le résultat sous Oracle.

Remplissage de la table Order_Fact

12)

Travail à réaliser :

- Pour chaque colonne de la table Order_Fact, spécifier de quelle(s)

donnée(s) source elle dépend. - Créer le job Job05_Order_Fact. - Lancer le job et vérifier le résultat sous Oracle.

Lancement des jobs

Dans une étude réelle, les données sources évoluent en permanence. Les jobs

doivent donc être planifiés régulièrement. Le lancement des jobs pourra se

faire par exemple toutes les nuits pour prendre en compte les données

modifiées pendant la journée. La planification des jobs peut se faire grâce

au planificateur de tâches du système d’exploitation.

Remarque : Dans la solution (payante) Talend Integration Suite, la

planification peut se faire directement sous Talend. De plus, il est

possible de prendre en compte uniquement les données qui ont été modifiées

pour accélérer le temps de chargement de l’entrepôt.

13)

Travail à réaliser :

- Exporter chaque job dans le répertoire C:/orion. o Clic droit sur le job / Export Job Scripts o Export type : Autonomous job o Cocher l’option Extract the zip file o Options par défaut

- Relancer le script de suppression puis création des tables de

l’entrepôt.

- Relancer le script de remplissage de la table Time_Dim. - Relancer les jobs en cliquant sur les fichiers .bat. - Vérifier que tout a bien fonctionné.

Les jobs vont maintenant être planifiés grâce au planificateur de tâches de

Windows.

14)

Travail à réaliser :

- Ouvrir le planificateur de tâches de Windows

o Accessoires / Outils système / Planificateur de tâches - Cliquer sur Créer une tâche… et donner lui un nom : orion_jobsETL - Créer un déclencheur (dans 15 min par exemple)

12

- Créer une action :

o Action : Démarrer un programme o Programme :

C:\orion\Job01_Customer_Dim\Job01_Customer_Dim\Job01_Customer_D

im_run.bat

o Commencer dans : C:\orion\Job01_Customer_Dim\Job01_Customer_Dim

- Ajouter de la même façon une action pour chaque job. - Relancer le script de suppression puis création des tables de

l’entrepôt.

- Relancer le script de remplissage de la table Time_Dim. - Attendre que la tache se lance. - Vérifier que tout a bien fonctionné.

Création du datamart

Pour la suite de l’étude, un datamart sera construit avec uniquement les

clients membres du club Orion Gold et ayant acheté des vêtements ou des

chaussures (pour accélérer les requêtes).

Il existe 3 solutions pour créer un datamart : - Solution 1 : Vues logiques de l’entrepôt - Solution 2 : Vues matérialisées de l’entrepôt - Solution 3 : Base de données indépendante + jobs de remplissage

15)

Travail à réaliser : (Implémentation de la solution 3)

- Créer un nouvel utilisateur orion_DM_user. - Créer le script de création du datamart puis les jobs permettant de

remplir les différentes tables.

o Conditions à spécifier : o WHERE Customer_Group = 'Orion Club Gold members'; o WHERE Product_Line LIKE 'Clothes%'; o Cocher la case Inner join dans les jointures.

- Vérifier que tout a bien fonctionné.

16)

Travail à réaliser : (Implémentation de la solution 1)

- Créer un nouvel utilisateur orion_DM_V_user. - Créer le script de création des vues logiques. - Vérifier que tout a bien fonctionné.

17)

Travail à réaliser : (Implémentation de la solution 2)

- Créer un nouvel utilisateur orion_DM_MV_user. - Créer le script de création des vues matérialisées. - Vérifier que tout a bien fonctionné.

13