SQL Server Data Tools (SSDT) est le nouveau nom de SQL Server Integration Services (SSIS).
Je ne suis pas un supporter des opérateurs T-SQL PIVOT et UNPIVOT, pour la simple et bonne raison qu’il ne sont pas ensemblistes, et servent seulement à la présentation de données. Néanmoins, on conçoit aisément que ce sont des opérateurs utiles pour du chargement de données, surtout dans un environnement d’entrepôt de données. J’ai donc décidé de savoir ce qu’il faut faire sous SSDT pour « pivoter » un jeu de données.
Pour avoir un jeu de données à pivoter, j’ai installé la base de données AdventureWorksDW2012, que l’on trouve sur CodePlex. Pour voir comment installer cette base de données dans une instance de SQL Server, suivez le guide.
Nous démarrons donc en ajoutant une Tâche de Flux de Données dans le Contrôle de Flux par simple glisser-déposer (je débute avec SSDT / SSIS !) :
Dans le panneau Flux de Données, nous ajoutons maintenant une source de données ADO.NET de la même façon, puis double-cliquons dessus pour la configurer.
Il nous faut tout d’abord créer une connexion à la base de données AdventureWorksDW2012. Pour cela, nous cliquons sur Nouveau …
Comme nous n’avons pas encore de connexion, nous cliquons une nouvelle fois sur le bouton Nouveau dans la fenêtre « Configurer un Gestionnaire de Connexion ADO.NET », et nous obtenons la fenêtre suivante :
Nous validons alors le tout, et nous retrouvons dans l’éditeur de Source ADO.NET, ou nous pouvons choisir de traiter les données extraites directement d’une table ou d’une vue, ou bien de spécifier une requête. Nous choisissons cette dernière option :
Voici donc la requête, que nous collons dans l’éditeur de Source ADO.NET, et son résultat sous SQL Server Management Studio :
1 2 3 4 5 6 7 8 9 10 11 | SELECT PC.EnglishProductCategoryName , LEFT(F.OrderDateKey, 4) AS OrderYear , SUM(F.SalesAmount) AS SalesAmount FROM dbo.FactResellerSales AS F INNER JOIN dbo.DimProduct AS P ON F.ProductKey = P.ProductKey INNER JOIN dbo.DimProductSubcategory AS PS ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey INNER JOIN dbo.DimProductCategory AS PC ON PS.ProductCategoryKey = PC.ProductCategoryKey GROUP BY PC.EnglishProductCategoryName, LEFT(F.OrderDateKey, 4) |
Notre but des donc d’obtenir une jeu de données avec les colonnes suivantes :
– 4 lignes, dont la colonne contiendra des valeurs uniques suivantes (Components, Bikes, Clothing et Accessories)
– 5 colonnes, dont 4 pour les années 2005 à 2008, et une pour le volume de vente (SalesAmount)
Nous obtenons l’interface suivante, qui nous permet de spécifier les caractéristiques de l’opération de pivot :
Nous validons par le bouton OK, puis passons sous l’éditeur avancé :
En basculant sous l’onglet Propriétés d’entrée / sortie, on s’aperçoit qu’il va manquer des colonnes. En effet, les colonnes des années ne sont pas dans la sortie du pivot :
Notons ici la valeur LineageID, qui nous permettra d’indiquer par la suite la colonne de la source de données pour la sortie du pivot. Dans cet exemple, c’est 22. Nous ajoutons donc 4 colonnes, 2005, 2006, 2007 et 2008, avec le modèle suivant :
Nous obtenons donc :
Comme nous ne souhaitons pas stocker les données en sortie, mais seulement voir ce que le moteur fait, nous pouvons ajouter comme destination un compteur de lignes :
Comme le libellé de l’erreur l’indique, nous devons créer une variable pour stocker le nombre de lignes retournés en sortie de l’opérateur pivot. Pour ce faire, il suffit d’utiliser la fenêtre des variables (Affichage > Autres fenêtres > Variables) du paquetage, que nous appelons rowCount.
Une fois fait, il nous suffit de double-cliquer sur la destination pour pouvoir spécifier la variable :
Après avoir validé par OK, nous pouvons activer la visionneuse de données sur les deux pipelines, par un simple clic-droit sur chacun d’entre-eux- Ceci nous permettra de visualiser la transformation :
Voici ce qui se passe à l’exécution du paquetage :
La visionneuse de gauche nous montre les données avant le pivot, et celle de droite après. On voit bien que la transformation est plus proche d’une matrice que d’un pivot. Ceci est dû au fait que l’on n’a pas ordonné les données suivant le libellé de la catégorie : dès lors, le groupement des lignes suivant ce critère n’est pas fait. Nous cliquons sur le bouton Lecture, et comme le tampon ne contient que quelques lignes, l’exécution du paquetage continue et se termine presque immédiatement, sans erreur.
Comme bien souvent dans l’environnement SQL Server, on dispose de plusieurs possibilités pour remédier à ceci : soit ajouter une clause ORDER BY PC.EnglishProductCategoryName à la requête, soit ajouter un opérateur de tri dans SSIS. C’est cette dernière option que nous choisissons, puisqu’en SQL, il n’y a normalement pas d’ordre.
Nous intercalons donc un opérateur de tri :
Et nous le configurons pour que le tri se fasse suivant la colonne EnglishProductCategoryName; par défaut le tri est ascendant :
Nous ré-exécutons le paquetage, dont il ne nous reste plus que la visionneuse de données en sortie du pivot. Nous obtenons alors le résultat escompté :
Bons pivots de données avec SSDT !
ElSüket.