Benchmark SSIS de chargement de dimension en SCD de type 1

Pour faire suite à l’article traitant de l’optimisation des chargements de dimensions en SCD de type 1 et au commentaire de David sur l’utilisation du MERGE T-SQL, je me suis dit qu’un benchmark permettant de comparer les différentes techniques serait intéressant (un petit complément à un certain article de Matt Mason).

Pour être impartial, il faut bien sûr que mon jeu de test soit en adéquation avec les spécifications de ma machine, de façon à ne pas avantager telle ou telle technique. J’ai donc créé une dimension Customer avec 18 colonnes contenant 10 000 000 lignes représentant une taille de  3 GB. Ma table contient une clé métier et la gestion du changement est de type 1 sur l’ensemble des colonnes.

Les techniques d’implémentation testées sont les suivantes :

• lignes nouvelles et modifiées identifiables à la source + utilisation lookup : je suppose les données sources disposent d’une date d’audit permettant de détecter les lignes récemment modifiées ou insérées. Il suffit de ne sélectionner à la source que les lignes dont la dernière date de modification est supérieure à la dernière date de chargement de l’entrepôt. J’utilise ensuite un lookup afin de détecter sur la dimension cible l’existence de chaque enregistrement. Les lignes sans correspondance sont insérées dans la dimension, les lignes avec correspondance sont insérées dans une table de staging pour exécuter dans le control flow un update en masse à l’aide d’un SQL Task.

• lignes nouvelles et modifiées identifiables à la source + utilisation du MERGE: comme précédemment je suppose que les données sources modifiées ou insérées sont identifiables à la source par l’intermédiaire d’une date d’audit. Ces lignes sont directement insérées dans une table de staging à partir de laquelle je réalise l’upsert via la commande MERGE.

MERGE dim
USING stg ON dim.BKEY = stg.BKEY
WHEN NOT MATCHED BY TARGET THEN
INSERT (COL1,...,COLn) VALUES (stg.COL1,...,stg.COLn)
WHEN MATCHED THEN
UPDATE SET
dim.COL1 = stg.COL1,
...
dim.COL1 = stg.COLn
;

• utilisation double lookup : je suppose ici que le système source ne contient pas d’information d’audit indiquant la dernière date de modification de l’enregistrement. L’implémentation est identique à la version précédente, mais l’identification des lignes à modifier s’effectue par l’intermédiaire d’un lookup montant en mémoire la clé technique et l’ensemble de champs sur lesquels doit être effectué la gestion du changement, seuls les enregistrements sans correspondance doivent alors être modifiés. On utilise ensuite la technique habituelle pour mettre à jour en masse les enregistrements à modifier (insert en fast-load dans une table de staging suivi d’un update en masse dans le Control Flow à l’aide d’un SQL Task).

• utilisation du MERGE T-SQL : comme précédemment, je suppose que le système source ne contient pas d’information d’audit indiquant la dernière date de modification. Ainsi, toutes les lignes sont insérées dans un table de staging, j’utilise ensuite un SQL Task implémentant le MERGE entre la dimension cible et la table de staging. Les lignes sans correspondance sont insérées, celles pour lesquelles au moins un des champs a été modifié sont mises à jour.

MERGE dim
USING stg ON dim.BKEY = stg.BKEY
WHEN NOT MATCHED BY TARGET THEN
INSERT (COL1,...,COLn) VALUES (stg.COL1,...,stg.COLn)
WHEN MATCHED AND (  dim.COL1 <> stg.COL1 OR ... OR dim.COLn <> stg.COLn) THEN
UPDATE SET
dim.COL1 = stg.COL1,
...
dim.COL1 = stg.COLn
;

• utilisation « optimisée » du composant SCD de SSIS : j’utilise le composant SCD fournit nativement par SSIS en prenant soin de modifier l’insertion des nouveaux enregistrements pour qu’elle s’effectue en « fast load » et je remplace l’OLE DB Command (généré à la fin de l’assistant du SCD) par un OLE DB Destination pour insérer, (en fast load) dans une table de staging, les enregistrements à modifier. La mise à jour s’effectue ensuite dans le Control Flow à l’aide d’un SQL Task

Les problèmes de performances étant situés sur la détection des modifications et la mise à jour, les tests seront les suivants :

• exécution à vide : aucune modification ni insertion
• 10%, 30%, 50% et 100% de lignes modifiées

Bon arrêtons de blablater et voyons les temps d’exécution des différentes implémentations :

Au vu des résultats, on comprend bien vite pourquoi le composant SCD de SSIS est à proscrire… J’enlève donc cette option de mon analyse pour ne me concentrer que sur celles qui sont viables :

Par conséquent, mes préconisations sont les suivantes :

• si vous disposez de la dernière date de modification des données sources et que la gestion du changement est à effectuer sur toutes les champs, ne vous posez pas de questions et utiliser la technique lookup. Assurez-vous néanmoins que votre lookup remontant la clé technique, la clé métier puisse être monté en mémoire sans problème.
• si vous ne disposez pas de date technique et/ou que la gestion du changement ne s’effectue par sur tous les champs vous avez deux alternatives :
  • implémentation du double lookup si l’ensemble des données peut être monté en mémoire. A noter qu’on peut limiter l’utilisation de la mémoire en attribuant à chaque clé métier une valeur de hashage sur l’ensemble des colonnes. Cela peut-être fait à l’aide de la fonction SQL HASHBYTES. Attention néanmoins au type de données car il vous faudrait « caster » en nvarchar, concaténer et gérer les valeurs nulles sur toute les colonnes cibles; vous devrez aussi choisir le bon algorithme. Cela revient à implémenter la technique lookup + conditionnal split mais en remplaçant la date de modification par la valeur de hashage
  • implémentation du MERGE T-SQL dans le cas contraire.

Remarque, vous pouvez aussi vous amusez à mettre en place du Change Data Capture sur les bases opérationnelles ou sur votre ODS. Cela vous permettra alors d’identifier et de manipuler directement les enregistrements qui ont été insérés, mis à jour ou supprimés.

Je finirai juste par une petite constatation; certes les volumétries sont croissantes, mais la tendance est à l’In-Memory et le Hardware évolue en conséquence (enfin c’est plutôt l’inverse: étant donné les capacités Hardware sans cesse croissantes, la tendance est à l’In-Memory). Il n’est donc pas rare d’avoir sur un serveur BI plusieurs centaines de GB de RAM. De ce fait, la quasi-totalité de vos dimensions pourront être chargées avec la technique du double lookup.

• business key
• Dimension
• SCD1
• SSIS
• surrogate key