SSIS – Charger une dimension hiérarchique style Employee (Initial, Incrémental, SCD Type 2)

Récemment on m’a demandé de me pencher sur ce cas. En prenant en compte certains pré requis

Le tout consiste à charger une dimension Dim_Employees avec prise en compte du Slowly Changing Dimension de Type 2. Le SCD s’active lorsque le manager change.

Considérations de l’exercice.

  • Utiliser le plus possible SSIS
  • Prendre en compte le composant SCD Natif
  • Le volume de données est faible (de l’ordre du millier)
  • Ne pas changer la structure des tables
  • Pas de tables temporaires

Ci-dessous la structure des tables. Rien d’extraordinaire, noté juste la présence des clés (EmployeeKey et ParentEmployeeKey) de la source dans la destination. Elles sont préfixé par src_ .

Comment on charge  ?

 » On charge les managers puis les autres » –> Bouing !
 » On charge les employées puis les managers » –> Presque !

Une voir LA réponse : On charge tout le monde sans les managers puis on affecte les managers.
La colonne manager acceptant les valeurs NULL on peut la laisser sans valeur.

Note : Dans le cas ou cette colonne était NOT NULL il aurait fallu mettre une valeur par défaut comme -1. Cela voudrait dire que chaque employé possède un manager, et voir la possibilité qu’un employé soit manager de lui même si il n’a pas de manager. Bref…

Le traitement s’effectuera en deux passes, on charge tous les employés (peu importe qu’ il soit manager ou pas) puis on assignera les managers via un Update.
Mais….
il faut prendre en compte du SCD sur le manager, si on fait notre UPDATE alors le  SCD va s’activer, cela aura pour conséquence de créer une nouvelle ligne. Oui c’est vrai mais ne l’oublions pas on a toujours un scénario de chargement Initial et un scénario Incrémental.

En effet lors du chargement initial le SCD ne sera pas appelé, par contre dans l’incrémental il faudra l’utiliser.

Ce qui donne un package de ce genre :

Explication rapide :
Le composant  SQL_Count_Row va faire un COUNT sur la table de destination et stocker la valeur du count dans une variable SSIS. Il y a des vérifications par la suite si le Count retourne 0 alors c’est un chargement Initial si le count est supérieur à 0 alors on est dans un chargement Incrémental.

Note : Dans un développement ETL avec SSIS, plusieurs manières de gérer le chargement initial et le chargement incrémental. On aurait pu développer deux packages différents pour chaque scénarios : Un package initial et un package incérmental. On aurait alors soit un package Master pour chaque type de chargement soit un seul package master et des tables de configurations pour gérer les types d’exécution des packages.

Chargement Initial

Le Sequence Container SEQ_Initial_Load, contient deux tâches de flux de données.

Le premier (DFT_Load_All_Employees) est tout simple il insère les employés en laissant la colonne Manager_Id à NULL.

Le second (DFT_Affect_Manager_To_Employee) est un peu plus complexe. Il y a un double Lookup pour assigner le bon Manager_Id en se basant sur le nouvel Employee_Id généré par la table.

Détail du premier Lookup (LKP_Employeed_Id)

Détail du second Lookup (LKP_Manager_Id)

Etat des données :

A la source :

Après l’exécution du DFT_Load_All_Employees

Après l’exécution du DFT_Affect_Manager_To_Employee

Chargement Incrémental

Si on exécute le package par la suite il ira dans la branche incrémentale.

Explications :

Le premier lookup (LKP_DESTINATION) va juste vérifier si l’enregistrement existe. Il va comparer les clés de la source avec la clé naturelle de la destination. Si il n’y a pas de correspondance alors c’est une insertion d’un nouveau record (DST_Insert_New_Employees).

Note du 7 Mars : Après revisite de la solution il manque un lookup avant d’insérer un nouvel enregistrement. Ce lookup va récupérer l’ID du manager un peu comme dans le chargement initial.

 Si il y a correspondance alors l’enregistrement existe dans ce cas là on laisse le SCD gérer le changement.

Configuration du composant SCD_Manage_Data :

Le dernier lookup (LKP_Manager_Id) est nécessaire ici car il re assigner le nouvel ID du manager en récupérer son Employee_ID.

Détail du Lookup :

Note : La sortie du composant SCD New Output peut être supprimé elle ne sert à rien étant donné que le premier lookup gère les nouvelles entrées.

Test

On change les données de la source.

On exécute le package.

Résultats dans la destination :

Conclusion

Un article assez long pour un sujet sensiblement facile aux premiers abords.

Le chargement de données dans un entrepôt de données est un art, pour réaliser cette oeuvre il faut jouer de différentes techniques.
La prodédure décrite dans cet article permet de remplir les objectifs dans un certain contexte. Différents facteurs peuvent intervenir et de ce fait l’approche peu changer. Tout de même certains principes demeureront à jamais. Un data warehouse connait un chargement Initial et un chargement incrémental, il faut donc adapter le développement pour chacune de ces phases. Les problématiques et les questionnements peuvent trouver leurs solutions tout simplement en leur mettant dans le contexte associé.

2 réflexions sur “SSIS – Charger une dimension hiérarchique style Employee (Initial, Incrémental, SCD Type 2)

  1. Merci Thomas pour tout ce détail🙂

    Personnellement j’ai un peu de mal avec le composant par défaut – tu le sais on en a déjà discuté – mais pour des volumétries légères c’est clair que ça fait bien le job.

    En ce moment j’utilise des triples lookups pour gérer tout ça:
    > Etape 1 : Un premier pour déterminer si le membre existe? Non on l’insert, oui on passe à l’étape 2
    > Etape 2 : Un deuxième pour déterminer les changements sur les colonnes en SCD2. Non on passe à l’étape 3, oui à l’étape 4
    > Etape 3 : Un troisième pour déterminer les changements sur les colonnes en SCD1. Non alors rien à faire, oui on update la dimension
    > Etape 4 : On archive les lignes périmées (et si besoin on update les attributs SCD1), on insert les nouvelles lignes (en UNION sur l’étape 1).

    Ca marche fort. Faut juste pas se rater et générer la startdate et la enddate dans le même Derived Column entre l’étape 2 et 4 pour éviter d’avoir des décallages sur plusieurs buffers.

    Tu fais comment toi quand la volumétrie monte? Tu passes sur le composant Codeplex ou tu fais ce genre de truc?

    • Salut Florian,

      alors oui cette exemple avec le composant natif dans un scénario avec peu de données.

      Quand la volumétrie monte méthode lookup, derived column et conditionnal split.
      Je vérifie les changement de type SCD1 avec un conditionnal split.
      Sinon globalement ce sont des lookups comme toi.

      Bien évidemment si le server possède de la mémoire.

      Sinon d’autres design pattern avec la requête MERGE, composant Ralph Kimball de codeplexe, checksum ou sinon pour vraiement optimiser des tables de staging contenant uniquement le delta.

      Bref cela donne une idée d’article🙂

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s