Transformation Pivot dans SSIS 2012

ssis

Dans nos différents projets décisionnels, nous pouvons être amenés à transformer des tableaux stockés en colonne en tableaux en ligne. Cette transformation s’appelle un pivot.

Nous allons voir dans ce blog comment procéder.

Voici un exemple de tableau contenant une entrée pour un pays et le chiffre d’affaire pour chaque mois de l’année 2016.

Nous souhaiterions n’avoir qu’une ligne par pays et année.

Tableau

Nous allons voir comment mettre en place cette fonctionnalité dans SSIS 2012.

Cette tâche n’était pas simple dans la version 2008 car il n’y avait pas de guide pour nous accompagner dans sa mise en place. Cela a été amélioré dans la version 2012.

Pour commencer nous créons la table cible qui va contenir le résultat attendu.

create_table

Dans SSIS, nous créons une tâche de flux de données. Le fichier Excel à traiter est défini en source.

La transformation en pivot se nomme « Tableau croisé dynamique » dans la boite à outils SSIS.

Lorsque nous faisons un double clic dessus, une fenêtre s’ouvre afin de nous guider dans la définition des différentes colonnes.

fenêtre_tableau_croisé

La clé de tableau croisé dynamique sera la colonne dont la valeur sera utilisée pour construire le nom des nouvelles colonnes, dans notre cas il s’agit de la colonne MOIS.

La clé du pivot correspond aux valeurs qui seront uniques en sortie (sur une ligne), dans notre exemple il s’agit des colonnes PAYS et ANNEE.

La partie valeur de tableau croisé dynamique contiendra la colonne dont les valeurs seront attribuées aux nouvelles colonnes. On utilisera la colonne CA.

 

La partie « générer des colonnes de sortie de tableau dynamique à partir des valeurs » permet de générer le nom des nouvelles colonnes avec les valeurs que l’on aura remplies.

Nous écrivons donc la liste des mois et générons les colonnes de sorties.

fenêtre_tableau_croisé_renseignée

Nous avons défini 3 colonnes alors que notre exemple contient une 4ème colonne ANNEE.

Pour la rajouter, il faut ouvrir l’éditeur avancé en faisant un clic droit sur le module « Tableau croisé dynamique »

editeur_avancé

Dans la nouvelle fenêtre, cocher la colonne ANNÉE afin de la rajouter dans la transformation.

Ensuite il faut aller sur l’onglet « propriétés d’entrée et de sortie ».

Dans les colonnes d’entrée, on retrouve notre nouvelle colonne ANNÉE.

Il faut définir sa fonction dans la transformation pivot, dans notre cas elle rentrera dans la composition de la clé. Le champ « PivotUsage »  aura la valeur 1.

Il existe 4 valeurs pour ce champ et ce n'est pas forcément très clair pour s’y retrouver.

Valeur 0 : la colonne n’est pas utilisée dans la transformation
Valeur 1 : devient la clé du pivot (PAYS, ANNEE)
Valeur 2 : les valeurs de la colonne deviennent les noms des nouvelles colonnes (MOIS)
Valeur 3 : les valeurs sont affectées dans les nouvelles colonnes (CA)

 

Il faudra aussi repérer la valeur du champ « LineageID » car elle servira dans l’étape suivante.

fenêtre_editeur_avancé

Ensuite nous ajoutons une colonne de sortie ANNEE. Dans les propriétés, dans le champ « SourceColumn » il faut renseigner la valeur récupérée juste avant (LineageId)

fenêtre_editeur_avancé_suite

Attention il est préférable de rajouter un tri entre le composant Excel et la transformation Pivot sur la clé du pivot pour que l’exécution se fasse correctement.

Après exécution, nous obtenons le résultat attendu :

résultat_requête

Un commentaire

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.