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.

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:

1. Une source OLE DB
2. Une transformation SSIS Aggregate
3. Un composant OLE DB Destination

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.

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

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:

1. CustomerID : avec une opération Group By.
2. YearID : avec une opération Group By.
3. 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.

2.4 Configurer la table cible

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

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

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.

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:

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]

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.

Sur le thème des regroupements et agrégations de données SSIS

• Package pour grouper toutes les lignes de plusieurs tables
• Calculer le nombre de lignes distinctes d’une table avec SSIS