TP Business Intelligence

#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 lentrep t

Une fois le sch ma en toile valid , il faut cr er lentrep t sous Oracle.

1) Travail r aliser :

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

lentrep 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 lentrep t sous Oracle en

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

Maintenant que les tables de lentrep 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 loption 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 doutils 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 lentrep t (composant Database). Un datamart (composant

Database) sera cr ensuite partir de lentrep t. Les utilisateurs

(composant Actor) acc deront lentrep 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 loption 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 navoir 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 lentrep 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 lentrep 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 loption tAccessInput.

• Choisir la table cible (Customer_Dim) et limporter dans le Design

Workspace avec loption 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 souvre 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 laide 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 den 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 lexemple 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 dexploitation.

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 lentrep 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 loption Extract the zip file

o Options par d faut

• Relancer le script de suppression puis cr ation des tables de

lentrep 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

lentrep 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 lentrep t
• Solution 2 : Vues mat rialis es de lentrep 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