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é.

Publicités