[SSIS] Data Transformation : utilisation du composant PIVOT

Ce billet aborde l’utilisation du composant Pivot sous SSIS (avec focus sur SQL Server Data Tools).

Pour comprendre ce qu’est Pivot, faites un salto-arrière ici.

Contexte

Supposons que l’on dispose d’un fichier Excel XL_ProductOrder dont la structure est la suivante :


Et admettons que l’on souhaite charger son contenu dans une destination (fichier, base de données,…) avec les résultats transformés comme suit :


Modus operandi

    Avant de commencer…

On suppose qu’un data flow (renommé DTF – Pivot)
a été glissé au sein du control flow, et qu’un composant Excel Source (renommé XL_ProductOrder)
a été correctement configuré pour pointer vers le fichier XL_ProductOrder, et que toutes les colonnes dudit fichier ont été sélectionnée (onglet Columns de l’Excel Source Editor).


Pour un exemple de mode opératoire plus précis sur la configuration d’une source de données Excel (ou tout autre fichier s’en approchant), vous pouvez faire un saut ici.

Entrée dans le vif du sujet

Bien que le modus operandi soit sensiblement le même que dans le cas de Business Intelligence Development Studio (BIDS, pre-SQL Server 2012), quelques différences notables existent. En effet, la version avec SSDT est beaucoup plus simple (voir la suite du billet), tandis que dans le cas de BIDS, quelques ajustements supplémentaires sont nécessaires (i.e., paramétrage du PivotUsage dans l’Advanced Editor du composant de pivotement,…).²
  • Faites glisser un composant de transformation Pivot au sein de la zone de design du data flow, puis créez un connecteur allant de XL_ProductOrder vers ledit composant :


  • Ouvrez l’éditeur du composant Pivot (en double-cliquant dessus, par exemple) :


Au sein de l’éditeur se démarquent 3 types de colonnes qui seront utilisées en tant que colonnes d’entrée (i.e., issues de XL_ProductOrder) :

Type de colonne Identifiant (PivotUsage) Description
Pass column (à titre indicatif) 0 La colonne est prise en compte parmi les colonnes de sortie, mais sans être changée.
Set key 1 La colonne est choisie comme clé d’identifiant (ou une partie de ladite clé) pour l’ensemble des enregistrements traités.
Pivot key 2 Colonne-pivot. Les valeurs de cette colonne sont associées aux colonnes dans le jeu de résultats pivotés.
Pivot value 3 La colonne contient des valeurs prises en compte dans des colonnes créées par l’opération de pivotement.

A la lumière des explications ci-dessus, cela donnerait dans notre contexte :

  • Set key : Product.
  • Pivot key : IncomeDay.
  • Pivot value : Quantity.
  • Configuration du composant Pivot :


  • Après avoir spécifié les bons types de colonnes (voir section précédente ou figure ci-dessus), indiquez les valeurs exactes de colonnes à pivoter, selon 2 approches :
    • Approche 1 : « Je ne connais pas de tête mes valeurs à pivoter ».

    Dans la mesure où le composant de transformation Pivot travaille de manière statique, vu qu’il a besoin de connaître les valeurs exactes de la colonne faisant office de pivot key afin de générer les colonnes en sortie basées sur elle (zone Generate pivot output columns from values), le fait de cocher sur Ignore un-matched Pivot Key values and report them after DataFlow execution permet de s’assurer de l’unicité des valeurs de la pivot key que l’on pourra récupérer ensuite à partir de la fenêtre de progression après exécution du data flow.

Lancez le data flow, puis allez dans l’onglet relatif au résumé de son exécution :


Dans l’onglet Progress, le passage suivant nous intéressera :


Copiez/collez ensuite les valeurs pivotées ([Friday],…) dans la zone idoine de la fenêtre de configuration du composant Pivot (voir approche 2 en guise d’exemple).

  • Approche 2: « T’inquiète, je connais par cœur toutes mes valeurs à pivoter ».


  • Générez les colonnes pivotées en pressant sur Generate Columns Now :


  • Validez la configuration du composant Pivot, et surtout les colonnes qui seront pivotées et leur nomenclature (que nous laisserons préfixées d’un C_<Jour>_) :


  • Faites glisser un composant intermédiaire (UNION ALL, etc…) ou de destination (DataReader Destination, OLE DB Destination,…) et placez-y un connecteur allant du composant Pivot vers ladite destination, puis activez le data viewer via clic-droit sur le connecteur :


Le but du data viewer est de permettre d’avoir une idée des résultats après transformation par SSIS.

  • Lancez le data flow (ou package, selon), et appréciez la magie du pivot :


Pour aller plus loin…

Gardez un œil ici pour de plus amples transformations (sic).

Publicités

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