Optimiser le chargement des dimensions en SCD de type 1

Sur les différents projets MS BI sur lesquels il m’a été donné de travailler, j’ai souvent rencontré le même type d’implémentation pour alimenter une dimension en SCD de type 1.

Etant donnée la persistance des données dans les dimensions celle-ci sont chargées en grande partie en UPSERT en respectant le modèle suivant :

  1. Récupération des données sources (à partir d’une table de staging ou d’un ODS)
  2. Transformations des données (conversion, colonnes dérivées, dé doublonnage, etc…)
  3. Recherche d’existence des enregistrements sources dans la dimension à partir de(s) la(les) clés métiers (business keys)
  4. Insertion des enregistrements sans correspondance
  5. Mise à jour des enregistrements existants

Je rappelle au passage qu’il est reconnu de ne PAS utiliser le composant SCD (Slowly Changing Dimension) et de préférer le LOOKUP, ce qui nous donne le package suivant :

SCD1 - CommonImplementation

Ce type d’implémentation est correct, mais il est bien loin d’être optimisé. Cela n’a pas trop d’impact sur des dimensions de quelques centaines de lignes, mais plus la volumétrie augmente plus les temps de traitements explosent (la courbe étant plutôt exponentielle que logarithmique).

Certains diront que les traitements d’intégration étant exécutés la nuit, cela n’a pas beaucoup d’importance. Cette remarque est sans doute pertinente dans un contexte Franco-Français, avec une seule et unique solution sur votre serveur BI. Mais cela devient beaucoup plus critique lorsque votre serveur héberge plusieurs solutions BI et que vous devez travailler dans un contexte international. Par ailleurs, il n’est pas rare de devoir exécuter partiellement ou complètement des traitements en cours de journée.

Je préfère donc définir un Template qui sera utilisé sur toutes les dimensions un peu comme un Design-Pattern. J’en profite pour glisser une petite digression : dans toute implémentation de package SSIS les deux maîtres mots à respecter sont « performance » et « maintenance ». Voyons comment optimiser notre package de départ. 

Le premier problème de performance vient de l’utilisation du composant OLE DB Command permettant d’implémenter la mise à jour de votre dimension par un simple update. En effet, ce dernier va exécuter les requêtes séquentiellement.

Si vous mettez à jour 10 000 enregistrements, le composant va exécuter les 10 000 update les uns après les autres.

Il est donc préférable et plus performant, d’insérer dans une table de staging toutes les lignes à mettre à jour, et d’effectuer ensuite un update en masse. Et oui c’est comme en T-SQL, les curseurs sont à proscrire, il faut travailler en ensembliste (n’oubliez pas d’activer le bulk insert sur les composants OLE DB Destination : le data access mode doit être [Table or view – fast load]. Nous obtenons donc le package suivant :

SCD1 - Optimization 1 - Bulk Update

Mais là encore, le package est perfectible, car toutes les lignes existantes sont mises à jour. Ne serait-il pas plus performant de ne mettre à jour que les enregistrements modifiés ?

Si le système source dispose d’une date technique indiquant la date et l’heure de la dernière modification, il suffit juste de stocker cette date de modification dans la dimension, de la remonter au niveau du lookup (avec la surrogate key et les business keys) et de tester à l’aide d’un Conditionnal Split si la date de modification de l’enregistrement dans le système source est postérieure à la date de modification de l’enregistrement stockée dans l’entrepôt. Le nombre de ligne à mettre à jour est stocké dans la variable NbRecordsToUpdate. L’update en masse n’est déclenché que si la variable NbRecordsToUpdate >0. Ce qui nous donne la solution suivante :

SCD1 - Optimization 2 - SCD1 Managed By Modified Date

Si le système source ne dispose pas de date de dernière modification ou bien si celle-ci n’est pas bien gérée, il est toujours possible d’utiliser un deuxième lookup avec une recherche sur l’ensemble des colonnes de la dimension (hors business key car ces dernières sont utilisées lors du premier lookup pour récupérer l’identifiant technique). Les lignes sans correspondances sont donc les lignes à modifier. Comme précédemment, le nombre de ligne à mettre à jour est stocké dans la variable NbRecordsToUpdate, utilisée pour déclencher (ou pas) l’update en masse :

SCD1 - Optimization 3 - SCD1 Managed By Modified Lookup

Je ne saurait donc que vous conseiller les deux derniers types d’implémentation en fonction de vos cas d’usages. Le package exemple est disponible sur SkyDrive

2 réflexions sur “Optimiser le chargement des dimensions en SCD de type 1

  1. Vu l’augmentation croissante des volumétries, j’utiliserais même uniquement la fonction MERGE en T-SQL maintenant, je pense.
    Quand on voit que c’est ce que recommande Matt Masson qui est un des mecs qui a « fait » SSIs, ça laisse réfléchir🙂

    • Il est vrai qu’on peut aussi utiliser le Merge Statement maintenant je n’ai jamais été vraiment convaincu par le gain de performance lié au son utilisation (malgré les préconisations de Matt Masson et son article http://blogs.msdn.com/b/mattm/archive/2010/08/05/optimizing-the-slowly-changing-dimension-wizard.aspx).

      J’ajouterai quelques précisions sur l’utilisation du Merge Statement :

      1. le merge statement n’est disponible qu’à partir de SQL Server 2008. Donc pour ceux qui sont toujours sous 2005 : too bad !

      2. petite alerte si la dimension contient un index filtré, vous risqué d’avoir quelques petits soucis https://connect.microsoft.com/SQLServer/feedback/details/773895/merge-incorrectly-reports-unique-key-violations.
      Mais rassurez vous il y a une solution de contournement :
      http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

      3. concernant sur l’identification des lignes à mettre jour :

      – vous devez gérer manuellement les collations pour effectuer des comparaisons case sensitives et accent sensitives le cas échéant (en règle générale on utilise le CI_AS)

      – vous devez gérer manuellement les valeurs null (en utilisant le isnull ou le coalesce par exemple)

      De mon côté, je n’utiliserai le Merge Statement que si l’architecture me l’impose :
      – pas de date technique dans le système source indiquant la dernière date de modification
      – dimension trop large pour être montée en mémoire par SSIS via un lookup

      Pour faire un peu de teasing : suite à ton commentaire je suis en train de préparer un petit bench sur une dim de 10 millions d’enregistrements à charger en SCD de type 1 qui devrait voir le jour prochainement et qui semble confirmer mes propos.
      Désolé pour la faible volumétrie de la dimension, mais les tests sont effectués sur mon pc portable.

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