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 :
benchmark ssis dim scd1

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 :

benchmark ssis dim scd1 (hors scd)

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.

5 réflexions sur “Benchmark SSIS de chargement de dimension en SCD de type 1

    • Salut Ismaïl,
      J’avoue ne pas avoir testé ces différentes techniques sur d’autre SGBD. Mais bon le principe reste le même, les techniques les plus optimisées sont celles qui tendent à réduire les opérations d’I/O et à favoriser le travail effectué en mémoire.
      Reste que si ton serveur SSIS ne dispose pas assez de mémoire pour effectuer le traitement, tu n’auras pas d’autre choix que d’implémenter du MERGE qui est portable sur la plupart des SGBD du marché (Oracle et TERADATA entre autre).
      Si tu as effectué des tests de perf sur l’un des deux (ou les deux), je suis preneur.

  1. Je suis surpris des temps d’exécutions : ton merge est censé faire en natif ce que tu fais faire à SSIS, c’est bizarre.
    le « dim.COL1 stg.COL1 OR … OR dim.COLn stg.COLn » dans le MERGE n’est-il pas plus couteux que filtrant ? Pourquoi ne mets-tu pas le test sur les dates de modifications à ce niveau là d’ailleurs ?

    .

    • Salut David,

      La réponse est le coût des I/O.

      Je m’explique. Ma table source subit des transformations pour être chargée correctement dans mon entrepôt; par conséquent, si je veux faire du MERGE Statement, je suis obligé d’insérer toutes les données dans une table de staging soit 10 millions d’enregistrements. Et cela a un coût en termes de performance, car le temps d’exécution de cette opération est d’environ 02min20sec. Ainsi, quel que soit le scénario, j’ai déjà un temps d’exécution incompressible qui ne pourra pas descendre en dessous de ces 2 minutes.Le merge statement exécuté ensuite par un SQL Task a lui aussi des coûts en I/O et en mémoire (même si mes tables sont indexées correctement (indexes clustered sur la business key)) pour identifier les lignes à insérer ou à mettre à jour.

      Si je prends la technique lookup + conditionnal split sur date de modification : la mise en mémoire des données dans le lookup (réduit à la clé technique, clé métier, date de modification) ne prend pas plus de 8 secondes. A cela j’ajoute un conditionnal split qui permet de détecter uniquement les lignes modifiées dès la première lecture des données. Je n’insère ensuite dans la table de staging que les lignes modifiées (c’est là qu’est la réelle optimisation qui consiste à réduire le nombre d’I/O). L’update, qui suit, utilise les clés clustered et n’a aucun test à effectuer pour détecter les lignes à mettre à jour (uniquement une résolution de jointure). Par ailleurs, excepté le cas où la totalité des enregistrements sont à mettre à jour, l’update s’effectue sur une volumétrie moindre et sera donc toujours plus performant que le MERGE sur la totalité des enreg.

      Idem en adoptant la technique du double lookup, aux 8 secs du premier lookup il faut ajouter le temps de montée en mémoire du second lookup (toute la table avec l’ensemble des colonnes excepté la clé métier). En moyenne cette phase ne prend pas plus de 45 secs. Cela reste toujours plus performant que les 02min20sec incompressibles dans le cas de la technique du MERGE.

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