Examen de TP : Atelier Système d’Information Décisionnel

Ce document présente un examen de travaux pratiques portant sur l’atelier Système d’Information Décisionnel. Il évalue les compétences en extraction, transformation et chargement (ETL) de données à l’aide de SSIS (SQL Server Integration Services), ainsi que la gestion des flux de données, des conteneurs, des transactions et des techniques d’ETL incrémental.

Exercice 1

Il s'agit d'extraire tous les clients ayant effectué des achats par internet et de les stocker dans la base de données « Staging ».

Étapes et raisonnement :

• Créer un nouveau package SSIS.
• Ajouter un composant « Data Flow Task » dans ce package.
• Configurer ce composant pour se connecter à la base de données de production « InternetSales ».
• Dans le flux de données, lire toutes les données de la table « Customers » de « InternetSales ».
• Configurer la destination pour écrire ces données dans la table « Customers » de la base « Staging ».

Cette opération consiste à réaliser un simple transfert complet des données clients entre deux bases, sans transformation ni filtrage.

Réponse finale : Le package contient un Data Flow Task qui extrait toutes les données de la table « Customers » de la base « InternetSales » et les charge dans la table « Customers » de la base « Staging ».

Exercice 2

Il faut récupérer toutes les informations sur les ventes par internet dans la base « InternetSales » et ajouter une colonne calculée « MontantVente » égale au prix unitaire multiplié par la quantité commandée.

Étapes et raisonnement :

• Ajouter un nouveau composant « Data Flow Task » dans le package précédemment créé.
• Configurer la source pour récupérer toutes les données des ventes par internet à partir de la base « InternetSales » en utilisant la requête SQL fournie dans le fichier « InternetSales.sql ».
• Ajouter un composant « Derived Column Transformation » dans le flux de données.
• Dans ce composant, créer une nouvelle colonne calculée nommée « MontantVente » dont la formule est :

MontantVente = PrixUnitaire * QuantiteCommandee

• Configurer la destination pour stocker ces données enrichies dans la base « Staging ».

Cette étape ajoute une transformation calculée dans le flux de données, permettant d’enrichir les données de vente avec un montant total par ligne.

Réponse finale : Le Data Flow Task récupère les ventes via la requête SQL, ajoute la colonne « MontantVente » calculée comme le produit du prix unitaire par la quantité commandée, puis charge les données dans la base « Staging ».

Exercice 3

Il s'agit de joindre les données de ventes récupérées avec les informations sur les produits commandés, et de gérer les produits non commandés en les stockant dans un fichier CSV.

Étapes et raisonnement :

• Dans le Data Flow Task des ventes, ajouter un composant « Lookup Transformation ».
• Configurer ce composant pour se connecter à la base « Products » et récupérer toutes les informations sur les produits vendus, en utilisant la requête SQL du fichier « Products.sql ».
• Effectuer la jointure entre les données de ventes et les données produits via la clé produit.
• Configurer la gestion des produits sans correspondance (produits non commandés) pour qu’ils soient dirigés vers un composant « Flat File Destination » qui écrit dans le fichier « Orphaned Internet Sales.csv ».
• Configurer un autre composant « Flat File Destination » (probablement une destination de base de données, mais ici mentionné comme Flat File dans le texte) pour stocker les informations des produits commandés dans la table « InternetSales » de la base « Staging ».

Cette étape permet d’enrichir les données de ventes avec les informations produits, tout en isolant les produits qui n’ont pas été commandés.

Réponse finale : Le Data Flow Task des ventes intègre un Lookup sur la table « Products » via la clé produit, stocke les produits non commandés dans « Orphaned Internet Sales.csv » et charge les produits commandés enrichis dans la table « InternetSales » de la base « Staging ».

Exercice 4

Il est demandé d’encapsuler les tâches de flux de données créées dans la partie 1 dans un conteneur de séquence pour les gérer comme une seule unité.

Étapes et raisonnement :

• Dans le Control Flow du package, ajouter un conteneur de séquence (Sequence Container).
• Déplacer ou insérer les Data Flow Tasks correspondants à l’extraction des clients dans ce conteneur.
• Cette encapsulation permet de gérer les tâches comme un bloc unique, facilitant la gestion des erreurs, la réutilisation et la lisibilité du package.

Réponse finale : Les tâches de flux de données relatives à l’extraction des clients sont regroupées dans un conteneur de séquence dans le Control Flow du package.

Exercice 5

Il faut implémenter le principe des transactions dans le conteneur de séquence créé précédemment et décrire le paramétrage nécessaire.

Étapes et raisonnement :

• Dans les propriétés du conteneur de séquence, activer la gestion des transactions en réglant la propriété « TransactionOption » à « Required ».
• Dans les propriétés du package, s’assurer que la propriété « TransactionOption » est également configurée (généralement à « Supported » ou « Required » selon la hiérarchie).
• Configurer les connexions utilisées dans les Data Flow Tasks pour supporter les transactions (par exemple, en activant « RetainSameConnection » à true).
• Ce paramétrage garantit que toutes les tâches dans le conteneur s’exécutent dans une transaction unique : si une tâche échoue, toutes les modifications sont annulées.

Réponse finale : Le conteneur de séquence a sa propriété « TransactionOption » réglée sur « Required », les connexions sont configurées pour supporter la transaction, assurant ainsi une exécution atomique des tâches contenues.

Exercice 6

Il s'agit de créer un ETL incrémental qui identifie les clients modifiés ou insérés entre les cycles d’actualisation afin de limiter l’extraction aux seules lignes modifiées.

Techniques offertes par SSIS pour un ETL incrémental :

• Utilisation de colonnes de date de modification : On compare la date de dernière modification dans la source avec la date du dernier chargement pour extraire uniquement les lignes modifiées ou insérées.
• Utilisation de la transformation Lookup : On compare les données sources avec les données cibles pour détecter les nouvelles lignes ou celles mises à jour.
• Utilisation de Change Data Capture (CDC) : SSIS peut exploiter les fonctionnalités SQL Server CDC pour détecter automatiquement les modifications dans les tables sources.
• Utilisation de tables de journalisation ou de marqueurs : On stocke les identifiants des lignes déjà extraites et on filtre les nouvelles lignes en fonction de ces marqueurs.

Explication des grandes lignes :

• Colonnes de date de modification : nécessite que la source ait une colonne indiquant la dernière date de modification. L’ETL filtre les lignes où cette date est postérieure à la dernière extraction.
• Lookup : permet de comparer les données sources avec celles déjà chargées, et d’identifier les différences à traiter.
• CDC : mécanisme SQL Server qui capture les modifications au niveau de la base, fournissant un flux de changements exploitable par SSIS.
• Tables de journalisation : méthode manuelle consistant à mémoriser les identifiants traités pour ne pas les retraiter.

Question Bonus

Le document ne fournit pas de détails sur l’implémentation pratique d’une de ces techniques. Par conséquent, il n’est pas possible de présenter une implémentation complète ici.

Méthodologie recommandée et erreurs à éviter

Ce type d’examen valorise une démarche rigoureuse et progressive :

• Respecter les consignes exactes : utiliser les noms de bases, tables, colonnes et composants tels que spécifiés.
• Structurer clairement le package : séparer les tâches par Data Flow Tasks, utiliser des conteneurs pour regrouper les tâches logiquement.
• Documenter les transformations : expliquer la logique des colonnes calculées et des jointures.
• Configurer correctement les transactions : s’assurer que les propriétés sont bien réglées pour garantir l’intégrité des données.
• Ne pas inventer de données ou d’étapes : se limiter aux informations fournies, en cas de manque d’éléments, le signaler clairement.
• Vérifier les formules et les expressions : notamment pour les colonnes calculées, éviter les erreurs d’arithmétique ou de syntaxe.
• Tester les flux de données : s’assurer que les données circulent bien entre sources et destinations, et que les fichiers ou tables sont correctement alimentés.

Les erreurs fréquentes pénalisées sont :

• Oublier d’ajouter la colonne calculée ou mal la définir.
• Ne pas gérer les produits non commandés dans le Lookup.
• Ne pas encapsuler les tâches dans un conteneur de séquence quand demandé.
• Ne pas activer ou mal configurer les transactions.
• Confondre les bases de données ou tables source et destination.
• Ne pas expliquer les techniques d’ETL incrémental ou donner des réponses incomplètes.

En suivant ces principes, l’étudiant démontre sa maîtrise des concepts ETL avec SSIS et sa capacité à concevoir des flux de données robustes et efficaces.