Agréger des données avec SSIS et le composant Aggregate

Agréger et faire la somme de données pour réaliser l’équivalent du GROUP BY en SQL avec le composant SSIS Aggregate.

Dans un projet ETL avec SSIS, il est courant de calculer des agrégations de données depuis une table SQL Server vers à un niveau supérieur, comme les magasins de données. C’est à dire, en pratique calculer la somme de montants. Pour cela, deux solutions possibles, écrire une requête SQL classique de type Group By ou utiliser le composant SSIS Aggregate. Ce tutoriel explique pas à pas comment utiliser le composant SSIS et regrouper des données sans écrire de requête SQL.

Table of Contents

1. Préparer les tables et les données avec SQL Server

L’objectif de ce tutoriel est de lire puis agréger des données depuis le niveau de la table des ventes, dbo.Sales:

Client
Mois
Année
Produit

Vers un niveau plus haut et les stocker dans la table agrégée des ventes, dbo.Sales_Grouped, c’est à dire:

Client
Année

1.1 Créer la table des ventes avec le script SQL

Se connecter à SSMS et exécuter ce script pour créer la table source.

CREATE TABLE [dbo].[Sales](
  [CustomerID]  [int] NOT NULL,
  [MonthID]     [nvarchar](20) NOT NULL,
  [YearID]      [nvarchar](20) NOT NULL,
  [Qty]         [numeric](10,2) NOT NULL,
  [Price]       [numeric](10,2) NOT NULL,
  [Sales]       [numeric](10,2) NOT NULL,
  [ProductID]   [int] NOT NULL,
  [ProductName] [nvarchar](100) NOT NULL,
  CONSTRAINT [SalesPK] 
    PRIMARY KEY CLUSTERED (
      [CustomerID], [MonthID], [YearID], [ProductID] ASC
    )
);
GO

Insérer ensuite les 44 lignes de ventes au niveau fin.

INSERT [dbo].[Sales] VALUES (1, N'1', N'2022', 10.00, 879.20, 8792.00, 31, N'Babka');
INSERT [dbo].[Sales] VALUES (1, N'3', N'2021', 5.00, 312.00, 1560.00, 40, N'Unreal');
INSERT [dbo].[Sales] VALUES (1, N'6', N'2021', 9.00, 627.00, 5643.00, 27, N'Qrunch');
INSERT [dbo].[Sales] VALUES (1, N'7', N'2022', 6.00, 621.70, 3730.20, 10, N'Ambrosi');
INSERT [dbo].[Sales] VALUES (1, N'7', N'2022', 4.00, 653.10, 2612.40, 47, N'Quickick');
INSERT [dbo].[Sales] VALUES (1, N'9', N'2021', 2.00, 734.90, 1469.80, 47, N'Quickick');
INSERT [dbo].[Sales] VALUES (2, N'2', N'2021', 9.00, 895.60, 8060.40, 47, N'Quickick');
INSERT [dbo].[Sales] VALUES (2, N'3', N'2022', 11.00, 51.80, 569.80, 49, N'Menudito');
INSERT [dbo].[Sales] VALUES (2, N'7', N'2021', 11.00, 112.20, 1234.20, 19, N'Chiavie');
INSERT [dbo].[Sales] VALUES (2, N'9', N'2022', 7.00, 581.80, 4072.60, 33, N'Jans');
INSERT [dbo].[Sales] VALUES (3, N'2', N'2022', 4.00, 576.60, 2306.40, 5, N'Amoy');
INSERT [dbo].[Sales] VALUES (3, N'6', N'2021', 6.00, 551.10, 3306.60, 8, N'Gilda');
INSERT [dbo].[Sales] VALUES (4, N'0', N'2021', 7.00, 15.40, 107.80, 26, N'Lasco');
INSERT [dbo].[Sales] VALUES (4, N'1', N'2022', 2.00, 706.60, 1413.20, 27, N'Qrunch');
INSERT [dbo].[Sales] VALUES (4, N'1', N'2022', 9.00, 491.70, 4425.30, 30, N'Paldo');
INSERT [dbo].[Sales] VALUES (4, N'3', N'2022', 4.00, 810.20, 3240.80, 22, N'Bourbon');
INSERT [dbo].[Sales] VALUES (4, N'4', N'2021', 9.00, 648.90, 5840.10, 42, N'Barbacoa');
INSERT [dbo].[Sales] VALUES (4, N'5', N'2021', 7.00, 284.90, 1994.30, 5, N'Amoy');
INSERT [dbo].[Sales] VALUES (5, N'11', N'2021', 3.00, 914.90, 2744.70, 45, N'Predue');
INSERT [dbo].[Sales] VALUES (5, N'11', N'2022', 9.00, 903.80, 8134.20, 44, N'Kemps');
INSERT [dbo].[Sales] VALUES (5, N'4', N'2022', 10.00, 133.90, 1339.00, 6, N'Haystack');
INSERT [dbo].[Sales] VALUES (5, N'6', N'2021', 10.00, 940.20, 9402.00, 34, N'Amazin');
INSERT [dbo].[Sales] VALUES (5, N'7', N'2021', 7.00, 352.90, 2470.30, 32, N'Maverik');
INSERT [dbo].[Sales] VALUES (5, N'7', N'2022', 3.00, 116.40, 349.20, 7, N'Kalamata');
INSERT [dbo].[Sales] VALUES (6, N'1', N'2021', 4.00, 283.30, 1133.20, 23, N'Oatsnack');
INSERT [dbo].[Sales] VALUES (6, N'4', N'2021', 4.00, 604.90, 2419.60, 10, N'Ambrosi');
INSERT [dbo].[Sales] VALUES (6, N'4', N'2022', 5.00, 184.30, 921.50, 6, N'Haystack');
INSERT [dbo].[Sales] VALUES (6, N'5', N'2022', 9.00, 464.40, 4179.60, 18, N'Lurpak');
INSERT [dbo].[Sales] VALUES (6, N'8', N'2021', 6.00, 836.10, 5016.60, 40, N'Unreal');
INSERT [dbo].[Sales] VALUES (6, N'9', N'2022', 4.00, 254.80, 1019.20, 1, N'Boncora');
INSERT [dbo].[Sales] VALUES (7, N'0', N'2021', 7.00, 100.90, 706.30, 45, N'Predue');
INSERT [dbo].[Sales] VALUES (7, N'10', N'2021', 4.00, 161.50, 646.00, 10, N'Ambrosi');
INSERT [dbo].[Sales] VALUES (7, N'10', N'2022', 7.00, 484.50, 3391.50, 50, N'Armanino');
INSERT [dbo].[Sales] VALUES (7, N'3', N'2022', 7.00, 748.80, 5241.60, 38, N'Exo');
INSERT [dbo].[Sales] VALUES (7, N'6', N'2021', 1.00, 623.50, 623.50, 45, N'Predue');
INSERT [dbo].[Sales] VALUES (7, N'7', N'2021', 6.00, 185.00, 1110.00, 28, N'Basilico');
INSERT [dbo].[Sales] VALUES (7, N'8', N'2022', 7.00, 625.50, 4378.50, 4, N'Sartori');
INSERT [dbo].[Sales] VALUES (7, N'8', N'2022', 3.00, 125.40, 376.20, 38, N'Exo');
INSERT [dbo].[Sales] VALUES (8, N'1', N'2021', 8.00, 949.80, 7598.40, 16, N'Homekist');
INSERT [dbo].[Sales] VALUES (8, N'10', N'2021', 9.00, 944.30, 8498.70, 41, N'Baked');
INSERT [dbo].[Sales] VALUES (8, N'10', N'2022', 7.00, 578.10, 4046.70, 2, N'Master');
INSERT [dbo].[Sales] VALUES (8, N'12', N'2022', 3.00, 309.20, 927.60, 42, N'Barbacoa');
INSERT [dbo].[Sales] VALUES (8, N'2', N'2022', 4.00, 417.80, 1671.20, 25, N'Scrapple');
INSERT [dbo].[Sales] VALUES (8, N'4', N'2021', 7.00, 509.20, 3564.40, 4, N'Sartori');

1.2 Créer la table des ventes groupées avec le script SQL

Créer ensuite la table des ventes regroupées par année et client. Cette table sera chargée par le package SSIS.

CREATE TABLE [dbo].[Sales_Grouped](
  [CustomerID] [int] NOT NULL,
  [YearID]     [nvarchar](20) NOT NULL,
  [Sales]      [numeric](10,2) NOT NULL,
  CONSTRAINT [SalesGroupedPK] 
    PRIMARY KEY CLUSTERED (
      [CustomerID], [YearID] ASC
    )
);
GO

2. Créer le flux de données SSIS pour agréger les données

Configurer maintenant le flux de données avec les composants nécessaires. Les prérequis sont d’avoir créés avec Visual Studio:

Un projet et un package SSIS
Une connexion à une base de données SQL Server avec le gestionnaire de connexions SSIS

2.1 Ajouter tous les composant SSIS au flux de données

Depuis un package SSIS, ajouter un flux de données. Ajouter ensuite trois composants:

Une source OLE DB
Une transformation SSIS Aggregate
Un composant OLE DB Destination

Ajouter les composants Source, Aggregate et Destination au flux de données SSIS

2.2 Configurer la table source avec SSIS

Ouvrir le composant OLE DB Source et sélectionner la table des ventes créé et alimentée à l’étape 1.1.

Choisir la table source à agréger avec le composant SSIS

Vérifier depuis l’onglet Colonnes (Columns) que les colonnes de la table sont mappées correctement.

Vérifier le mapping des colonnes sources depuis le package SSIS

2.3 Paramétrer le GROUP BY dans le composant d’agrégation SSIS

Relier le composant OLE DB Source au composant Aggregate SSIS. Ouvrir maintenant la transformation et sélectionner les colonnes suivantes:

CustomerID : avec une opération Group By.
YearID : avec une opération Group By.
Sales : avec une opération Sum pour calculer la somme des ventes en sortie du composant.

Le composant SSIS détecte les types de données et par défaut ne propose d’opérations d’agrégations que pour les colonnes numériques.

Configurer le composant d'agrégation SSIS pour agréger les données et calculer la somme — Configurer le composant d’agrégation SSIS pour agréger les données et calculer la somme

2.4 Configurer la table cible

Relier maintenant le composant Aggregate avec la table cible. Choisir simplement la table dbo.Sales_Grouped.

Choisir la table cible pour stocker les données à agréger avec le package SSIS — Choisir la table SQL cible pour stocker les données à agréger avec le package SSIS

Vérifier aussi le mapping depuis l’onglet Mappings.

Vérifier le mapping des 3 colonnes sources avec la cible

3. Exécuter le package SSIS et vérifier les données agrégées

Exécuter enfin le packager SSIS pour grouper les données de ventes à un niveau annuel et les stocker dans la deuxième table. Le package lit 44 lignes sources et regroupe les données de la table en 16 lignes.

Exécuter le package pour agréger les 44 lignes initiales en 16 lignes calculées

Pour contrôler les données, deux options, la première est d’utiliser la visionneuse de données SSIS directement depuis le composant OLE DB Destination, comme ceci:

Résultat avec les données de la table agrégées par clients et par années

La deuxième option est d’exécuter directement depuis SSMS une requête SQL pour vérifier que les données de la table sont conformes. Exécuter donc cette requête SQL avec la fonction GROUP BY qui est l’équivalent des opérations du package SSIS.

SELECT 
  [CustomerID],
  [YearID],
  SUM([Sales]) AS [Sales]
FROM [dbo].[Sales]
GROUP BY   
  [CustomerID], 
  [YearID]

Requête SQL GROUP BY équivalente à l'agrégation avec SSIS — Requête SQL GROUP BY équivalente à l’agrégation avec SSIS

4. Conclusion sur la transformation SSIS Aggregate

Ce tutoriel SSIS explique pas à pas comment agréger des données et calculer une somme avec un GROUP BY sur une table sans écrire de requête SQL. Le composant est pratique pour des requêtes simples, dans des cas plus complexes il est préférable d’écrire une requête SQL dans le composant OLE DB Source. Afin de conserver des packages simples et faciles à maintenir.