Le Post Infeeny

Les articles des consultants et experts Infeeny

[JSS 2013] Session : SSIS Design Patterns

Speakers : David Joubert, Thomas Ricquebourg

SSIS : petite chronologie de l’ETL chez Microsoft

  • SQL Server 7 : Data Transformation Services (DTS)
  • SQL Server 2005 : SQL Server Integration Services

L’importance du SSIS:

Les speakers commencent par nous dire que malgré qu’aujourd’hui l’accent est mis sur la BI Self-Service, SSIS reste notre principal allié dans la plupart des cas, par exemple pour alimenter un datawarehouse, ou si la volumétrie des données est très importante …

Parce qu’un projet BI, c’est 70% du temps consacré à l’importation, le nettoyage et la transformation des données, d’où vient l’importance du SSIS.

Pour quoi des design patterns?

« Design Pattern » c’est  les modèles de conception que nous utilisons pour optimiser et rationaliser nos packages, et ça se traduit en l’utilisation du bon composant au bon endroit selon la situation (gestion des MAJ, historisation … )

Ils donnent l’exemple de dessiner un smiley sur Paint :

  • On peut arriver à le faire à la main.
  • Mais avec des notions de patron, on utilisant les ronds et les formes de Paint, on peut arriver à faire quelque chose de plus conventionnel et beau 🙂 .

C’est pareil lorsqu’on est dans SSDT, on peut arriver à faire des packages qui marchent et qui donnent le résultat attendu (ils nous montrent un package très mal structuré ou on ne sait pas vraiment ce qu’il fait), mais on utilisant des design patterns on arrivera à faire un travail plus optimisé, organisé et mieux structuré (Ils donnent comme exemple un package de chargement de DWH parallélisé avec gestion d’erreur, et on comprend très vite qu’il s’agit d’un DFT de dimension SCD1 sans prise en charge de SCD2).

Critères à prendre en considération :

  • L’outil de développement : SSDT
  • Les données : volumétrie
  • La solution : l’architecture
  • Infrastructure : performance
  • Les gens : compétence

Points forts et points faibles de SSIS :

  • Points Forts :
    • Outil  complet
    • Forte maintenabilité
    • Travail en mémoire
    • Bonne ergonomie
  • Points Faibles :
    • Parfois moins performant que du script SQL
    • Gestion de métadonnées figée
    • Scénario

Il est important de connaître le caractère synchrone ou asynchrone des composants :

Non bloquant : Conditionnal Split  \  Data Conversion  \ Derived Column \ Lookup –> Le moteur fait l’opération au fur et au mesure de l’arrivée des données

Semi bloquant : Merge \ Merge Join  \ Union All  –> Le moteur peut commencer l’opération même avant le chargement total des données dans la mémoire.

Bloquant : Pivot \ Aggregate \ Sort –> Le moteur ne fait l’opération qu’une fois tout est chargé dans la mémoire.

Les speakers nous font une démo sur le Moteur SQL VS SSIS :

Ils nous montrent les variations des performances par rapport à l’utilisation de différents composants : Par exemple un simple chargement de 20millions de lignes avec un composant de tri prend 50 secondes, la même opération a mis 10 secondes si on fait le tri dans la requête SQL (Order By).

Parallélisation :

– La parallélisation peut être appliquée sur les 3 objets de SSIS :

  • Package
  • Control Flow
  • Data Flow

– Efficace pour gagner en performance.

– En utilisant les Propriétés : MaxConcurrentExecutables et EngineThreads

Un petit conseil : « Diviser votre solution en opérations simples permettra une plus grande facilité de parallélisation »

Une démo pour montrer comment on peut contrôler via la propriété  MaxConcurrentExecutables  le nombre d’objets que l’on veut lancer en parallèle.

Il a 10 DFT qui peuvent tous se lancer en parallèle, mais la capacité maximale du moteur ne le permet pas, donc il fixe la propriété MaxConcurrentExecutables à 2 (qui était à -1 par défaut) et maintenant les DFT vont s’exécuter 2 à 2.

L’inconvénient c’est que le choix des objets est totalement aléatoire, cela veut dire que dans le cas ou il y a des objets qui sont lents en exécution et d’autres courts, rien ne nous garantie que les lents vont être exécutés en parallèle afin de gagner en temps global d’exécution. L’alternative dans ces cas est de mettre les objets que l’on veut exécuter en parallèle dans des « sequence countainer ».

Après ils nous montrent un exemple de chargement de plusieurs fichiers avec une boucle « Foreach »avec une gestion des erreurs pour que les fichiers mal formatés ou erronés ne bloquent pas l’exécution.

SCD – Slowly Changing Dimension :

  • Concept de base en Business Intelligence.
  • Composant existant dans SSIS, optimisation à apporter à la configuration de base.
  • Composants tiers disponibles : Kimball Method SCD (payant sur 2012)

Une démo de l’utilisation du composant SCD de base dans SSIS et son optimisation, le composant n’est pas compliqué, suivre les étapes en mettant les paramètres en fonction des cas d’usages … et à la fin génération automatique de la chaine des composants par le composant SCD.

En termes d’optimisation, on peut passer l’insert oledb en fastload, et mettre la connection en retainmachin.

UPSERT :

  • Cas particulier du SCD 1
  • On insère ou on update les lignes
  • 3 possibilités  (en plus du composant SCD vu précédemment) :
    • Double lookup  (démo : Temps exéc 50 secondes)
    • Double lookup vers table temporaire (11 secondes)
    • Script SQL avec MERGE (9 secondes)

Range Lookup :

  • Le range lookup correspond à un lookup sur une theta-jointure
  • On attend la feature depuis 2005
  • 4 possiblités (en attendant) :
    • Lookup
    • Conditionnal split
    • Script
    • SQL

NULL Substitution :

Lorsqu’on récupère les lignes qui ne matchent pas sur un lookup on remplace le NULL par la clé inconnue.

Il est plus judicieux de configurer du « Ignore Error » et de remplacer les NULL en une seule fois avec un composant « drived column »

Foudhil – Consultant décisionnel MCNEXT

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

%d blogueurs aiment cette page :