Tutoriel Etl Ssis 671a63

MIT820: Entrepôts de données et intelligence artificielle

Introduction aux outils BI de SQL Server 2014 Tutoriel sur SQL Server Integration Services (SSIS)

Description générale Ce tutoriel a pour objectif de vous familiariser avec les fonctionnalités de base de l’outil ETL de la suite BI de Microsoft : SQL Server Integration Services (SSIS). Dans le tutoriel, vous allez créer un projet d’intégration de données permettant d’insérer de nouvelles lignes dans une table de faits modélisant le taux de change journalier de différentes monnaies, à partir de fichiers textes. Pour insérer ces lignes, deux transformations devront être définies au processus d’intégration: des lookups qui récupèrent les clés dimensionnelles à partir de colonnes sources. Le concept de traitement en lot sera également abordé par la création d’un contenant itérateur (ForEach loop container) qui exécute la tâche d’intégration sur un ensemble de fichiers sources. Finalement, vous devrez définir un mécanisme de gestion d’erreurs, permettant de sauvegarder les erreurs rencontrées dans un fichier journal. Pour plus d’informations sur ce tutoriel, consultez le lien suivant : https://msdn.microsoft.com/en-us/library/ms169917.aspx

I. Étapes préliminaires Avant de pouvoir débuter le tutoriel, il est nécessaire d’effectuer quelques étapes pour configurer la machine virtuelle (VM).

Configurer Virtual Box: 1. Ouvrir Oracle Virtual Box : Menu démarrer → Tous les programmes → Oracle VM Virtual Box; 2. Ouvrir les paramètres de la machine virtuelle: Fichier (barre de menu) → Paramètres → Entrée (à gauche) → Machine virtuelle (onglet); 3. Changer la touche Hôte de Ctrl Droite à Alt Droite. À n’importe quel moment vous pouvez utiliser les raccourcis suivants : •

Alt Droite – c : er en mode redimensionné (pour sortir de la VM)

•

Alt Droite – f : er en mode plein écran.

Démarrer et configurer la machine virtuelle: 1. Dans Explorateur Windows : Disque local (c:) → MTI820 (répertoire) → MTI820 (cube bleu); 2. Connexion à la VM avec les paramètres suivants: •

Usager : sql

•

Mot de e : Tempo123

3. Afficher la barre de menu de la VM : Alt Droite – c; 4. Périphériques (barre de menu) → Insérer l’image CD des Additions Invité; 5. Suivre les instructions pour l’installation (environ 3 minutes); 6. Redémarrer la VM à la fin de l’installation. Dans Menu démarrer → Tous les programmes → Microsoft SQL Server 2014, vous trouverez les deux outils qui seront utilisés dans les tutoriels du cours : •

SQL Server 2014 Management Studio : pour voir les tables de la BD (AdventureWorks) et faire des requêtes;

•

SQL Server Data Tools for Visual Studio 2013: pour créer des projets SSIS, SSAS et SSRS.

II. Visualisation des données Tables de l'entrepôt de données: 1. Lancer l'application Microsoft SQL Server 2014 (attendre environ 1 minute si démarré pour la première fois); 2. Entrer les paramètres de connexion suivants: • •

Server Type : Database Engine Server Name : MTI820

3. Dans le Object Reporter à gauche: Databases → AdventureWorksDW2014 → Tables 4. Observer la structure de la table de faits dbo.FactCurrencyRate; 5. Afficher le contenu de la table : Clique-droit sur FactCurrencyRate → Select Top 1000 Rows; 6. Répéter la même chose pour les tables de dimension: dbo.DimDate et dbo.DimCurrency.

Fichiers sources (txt): 1. Dans l'Explorateur Windows, ouvrir le répertoire suivant : c:\Exercices\SQL2012.Integrations_Services.Create_Simple_ETL_Tutorial.Sample 2. Avec Wordpad, ouvrir le fichier suivant : SampleCurrencyData.txt 3. Signification des colonnes : •

AverageRate (float) : taux de change moyen de la monnaie;

•

CurrencyKey (nchar) : code représentant le type de monnaie;

•

CurrencyDate (Date) : estampille de date du taux de change;

•

EndOfDayRate (float) : taux à la clôture de la journée.

III. Simple projet SSIS Créer un projet SSIS:

1. À partir du menu démarrer : ouvrir l’outil SQL Server Data Tools for Visual Studio 2013; 2. Créer un projet: File → New → Project; 3. Choisir le type: Intergration Services Project; 4. Nommer le projet (ex : tutorielMTI820_SSIS).

Environnement de développement (IDE) : Observer les différents éléments de l’interface: •

Control flow: définir des tâches de contrôle sur le processus global d’intégration de données;

•

Data flow: définir un flot de données (ETL), comprenant l’extraction de données d’une ou plusieurs sources, l’application de diverses transformations, et le chargement des données transformées dans une destination (ex : entrepôt de données);

•

Event handler: définir les opérations de gestions d’évènements reliés au processus ETL;

•

Package explorer: visualiser les libraires (packages) d’intégration créés dans le projet;

•

Property editor: visualiser et modifier les propriétés de chaque composante du système ETL.

Créer un Flat File Connection Manager: 1. Dans le menu Connection Manager (en bas), cliquer-droit et choisir New Flat File Connection; 2. Renommer la connexion dans le Connection manager name : Sample Flat File Source Data; 3. Cliquer sur Browse et choisir le fichier SampleCurrencyData.txt; 4. S’assurer que l’option Locale est English (United States); 5. Dans le menu Avanced: •

Changer le nom des colonnes pour AverageRate, CurrencyID, CurrencyDate, EndOfDayRate;

•

Utiliser Suggest Type pour identifier les types à l'aide d'un échantillon;

•

Si ce n’est pas déjà fait, changer le type de AverageRate et EndOfDayRate pour float [DT_R4];

•

Changer le type de CurrencyID pour DT_WSTR et CurrencyDate pour DT_DBDATE;

6. Cliquer sur Preview pour voir le résultat; 7. Cliquer sur OK.

Créer un OLE DB Connection manager: 1. Dans le menu Connection Manager (en bas), cliquer-droit et choisir New OLE DB Connection. 2. Dans le menu Configure OLE DB Connection Manager, cliquer sur New. 3. Conserver les paramètres pour le serveur;

4. Choisir AdventureWorksDW pour la DB; 5. Tester la connexion avec TestConnection; 6. Cilquer sur OK.

Créer un Data Flow Task: 1. À partir du menu Control Flow Items (à gauche), glisser-déposer un Data Flow Task dans le canevas; 2. Optionnellement, renommer le Task en cliquant sur la boîte créée : Extract Sample Currency Data.

Ajouter un Flat File Source: 1. Aller dans l’onglet DataFlow (cliquer sur le Data Flow Task créé précédemment si nécessaire); 2. À partir du menu Data Flow Sources (à gauche), glisser-déposer un Flat File Source sur le canevas; 3. Ouvrir le Flat File Source en double-cliquant sur la boîte créée; 4. Dans le Flat file connection manager, sélectionner la connexion Sample Flat File Source Data créée précédemment; 5. Cliquer sur Columns et vérifier que les colonnes de la source sont toutes présentes; 6. Cliquer sur OK.

Créer les transformations de lookup pour les clés dimensionnelles: 1. À partir du menu Data Flow Transformations, glisser-déposer une transformation Lookup sur le canevas; 2. Renommer la transformation Lookup Currency Key en cliquant sur la boîte créée; 3. Connecter la flèche bleue (sortie sans erreur) sortant du Flat File Source au nouveau Lookup; 4. Ouvrir le Lookup en double cliquant sur sa boîte; 5. Dans le menu, choisir les options suivantes : • •

Full cache Connection type : OLE DB Manager

6. Dans le menu Connection, choisir : • •

Connection manager : localhost.AdventureWorksDW Table : [dbo].[DimCurrency]

7. Dans le menu Columns, associer CurrencyID avec CurrencyAlternateKey en cliquant sur la première colonne et déplaçant la souris vers la seconde. Cocher sur CurrencyKey. Le lookup convertira une valeur CurrencyID à la CurrencyKey de la valeur CurrencyAlternateKey correspondante; 8. Choisir Lookup Match Output pour la sortie; 9. Répéter les mêmes étapes pour le lookup de la dimension Date : • •

Nom : Lookup Date Key; Partial cache;

•

Connecter la sortie de Lookup Currency Key vers ce nouveau lookup.

Créer un OLE DB Destination: 1. À partir du menu Other Destinations (à gauche), glisser-déposer un OLE DB Destination sur le canevas; 2. Connecter la sortie (flèche bleue) du dernier au Lookup à celui-ci (choisir Look Match Output); 3. Ouvrir la OLE Destination en double cliquant sur sa boîte; 4. Choisir les paramètres suivants : • •

Connection manager : localhost.AdventureWorksDW; Table : [dbo].[FactCurrencyRate];

5. Dans Mappings, vérifier que les colonnes sont correctement associees; 6. Cliquer sur OK.

Tester le processus: Lancer le processus ETL (mode debug) en cliquant sur l’icône Start dans la barre de menu (triangle vert).

IV. Gestion du traitement en lot Créer le ForEach Loop container: 1. Revenir à l’onglet Control Flow;

2. À partir du menu Control Flow Items (à gauche), glisser-déposer un ForEach loop container dans le canevas Control Flow; 3. Ouvrir en double cliquant 4. Dans Collection: • •

ForEach File Enumerator Browse: Currency_*.txt

5. Dans Variable Mappings: ajouter une variable varFileName; 6. Déplacer la boîte du DataFlow créé précédemment à l'intérieur du container.

Modifier le Flat File Connection Manager : 1. Sélectionner le Flat File Connection Manager (Sample Flat File Source Data) dans le menu Connection Manager; 2. Dans le menu Properties (à droite), cliquer sur Expressions (...); 3. Choisir ConnectionString pour Property; 4. Pour Expression, trouver ::varFileName dans le menu Variables and Parameters, et glisser-déposer cette variable dans le champs Expression plus bas.

Tester le processus ETL :

Comme lors de l’étape précédente, cliquer sur l'icone Start (triangle vert).

IV. Gestion des erreurs Créer une erreur dans les données: 1. Dans l’Explorateur Windows, créer une copie du fichier Currency_CAD.txt et la renommer Currency_BAD.txt; 2. Dans le fichier Currency_BAD.txt, remplacer toutes les instances de CAD par BAD; 3. Dans SQL Developer Studio, cliquer à nouveau sur l'icone Start (triangle vert); 4. Une erreur devrait être causée par l'insertion de lignes existantes; 5. Ouvrir le OLE BD Destination Task en double-cliquant sur celui-ci; 6. Dans Error Output: choisir l'option Ignore failure; 7. Retester le processus en cliquant à nouveau sur l'icone Start (triangle vert); 8. Une erreur devrait survenir (fichier Currency_BAD.txt).

Ajouter un mécanisme de gestion d'erreur : 1. À partir du menu Other Destinations (à gauche), glisser-déposer un Flat File Destination dans le canevas Data Flow; 2. Connecter la flèche rouge du Lookup CurrencyKey vers le nouveau Flat File Destination; 3. Dans la colonne erreur, choisir Redirect Row; 4. Ouvrir le Flat File Destination en double-cliquant sur celui-ci; 5. Dans Connection manager, choisir New et Delimited; 6. Entrer le nom ErrorData 7. Cliquer sur Browse et entrer le nom de fichier ErrorOutput.txt; 8. S’assurer que l’option Locale est English (United States); 9. Dans Columns, observer que 2 nouvelles colonnes ont été créées; 10. Cliquer sur OK; 11. Dans le Flat File Destination Editor, Enlever l'option Overwrite Data; 12. Dans Mappings, observer les nouvelles colonnes ajoutées à la sortie (ErrorCode et ErrorColumn); 13. Cliquer sur OK; 14. Relancer le processus avec l’icône Start; 15. Vérifier que les lignes problématiques ont bien été ajoutées dans ErrorOutput.txt.

Ajouter fonctions de journalisation (logging) 1. Choisir Logging dans le menu SSIS; 2. Sélectionner le package; 3. Dans Providers and logs, Providers type: Text files; 4. Cliquer ajouter, et modifier le nom si nécessaire;

5. Cliquer sur Configuration et choisir ; 6. Usage type: Create file; 7. Cliquer sur Browse et entrer logTutoriel.log pour le nom du fichier; 8. Selectionner le DataFlow task dans Containers (à l'intérieur de Foreach Loop Container)

9. Dans Details, ajouter PipelineExecutionPlan et PipelineExecutionTrees; 10. Relancer le processus avec l’icône Start;

11. Vérifier que l’exécution a bien été journalisée dans logTutoriel.log.