Synthèse de données à l'aide de CUBE
L'opérateur CUBE génère un jeu de résultats qui est un cube multidimensionnel. Un cube multidimensionnel est une expansion de données de faits ou de données qui enregistrent des événements individuels. L'expansion est basée sur des colonnes que l'utilisateur souhaite analyser. Ces colonnes sont appelées dimensions. Le cube est un jeu de résultats qui contient une tabulation croisée de toutes les combinaisons possibles des dimensions.
L'opérateur CUBE est spécifié dans la clause GROUP BY d'une instruction SELECT. La liste de sélection contient les colonnes de dimension et les expressions de fonctions d'agrégation. GROUP BY spécifie les colonnes de dimension et les mots clés WITH CUBE. Le jeu de résultats contient toutes les combinaisons possibles des valeurs dans les colonnes de dimension ainsi que les valeurs agrégées des lignes sous-jacentes qui correspondent à la combinaison des valeurs de dimension.
Par exemple, une table Inventory simple contient :
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
La requête suivante retourne un jeu de résultats qui contient le sous-total Quantity pour toutes les combinaisons possibles de Item et Color :
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Voici l'ensemble des résultats.
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00
Les lignes du jeu de résultats suivantes présentent un intérêt particulier :
Chair (null) 311.00
Cette ligne retourne un sous-total pour toutes les lignes ayant la valeur Chair dans la dimension Item. La valeur null est retournée pour la dimension Color pour montrer que l'agrégation retournée par la ligne comprend toutes les lignes, quelle que soit leur valeur dans la dimension Color.
Table (null) 347.00
Cette ligne est semblable, mais présente le sous-total de toutes les lignes possédant Table dans la dimension Item.
(null) (null) 658.00
Cette ligne présente le total général pour le cube. Les deux dimensions Item et Color ont la valeur null. Cela montre que toutes les valeurs (des deux dimensions) sont récapitulées dans la ligne.
(null) Blue 225.00
(null) Red 433.00
Ces deux lignes présentent les sous-totaux pour la dimension Color. Elles ont toutes deux la valeur null dans la dimension Item pour montrer que les lignes d'où proviennent les données globales peuvent avoir n'importe quelle valeur dans la dimension Item.
Utilisation de GROUPING pour distinguer les valeurs NULL
Les valeurs NULL générées par l'opération CUBE présentent un problème : Comment distinguer une valeur NULL générée par l'opération CUBE d'une valeur NULL retournée dans les données de faits ? Ceci s'effectue au moyen de la fonction GROUPING. Cette dernière retourne 0 si la valeur de la colonne provient des données de faits et 1 si la valeur de la colonne est une valeur NULL générée par l'opération CUBE. Une valeur NULL générée lors d'une opération CUBE représente toutes les valeurs. L'instruction SELECT peut être écrite pour utiliser la fonction GROUPING afin de remplacer toutes les valeurs NULL par la chaîne ALL. Puisqu'une valeur NULL provenant des données de faits indique que la valeur des données est inconnue, l'instruction SELECT peut également être codée pour retourner la chaîne UNKNOWN à la place d'une valeur NULL provenant des données de faits. Par exemple :
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Cubes Multidimensionnels
L'opérateur CUBE peut être utilisé pour générer des cubes comportant n dimensions ou des cubes comportant un nombre indéfini de dimensions. Un cube à une seule dimension peut être utilisé pour générer un total, par exemple :
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO
Cette instruction SELECT retourne un jeu de résultats montrant les sous-totaux pour chaque valeur de Item et le total général pour toutes les valeurs de Item :
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
Les instructions SELECT qui contiennent un CUBE avec plusieurs dimensions peuvent générer de grands jeux de résultats car elles génèrent des lignes pour l'ensemble des combinaisons de valeurs dans toutes les dimensions. Ces grands jeux de résultats risquent de contenir trop de données pour permettre une lecture et une compréhension faciles. Une des solutions à ce problème consiste à placer l'instruction SELECT dans une vue :
CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
La vue peut ensuite être utilisée pour réaliser une requête uniquement sur les valeurs de dimensions présentant un intérêt :
SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)
Voir aussi
Concepts
Synthèse de données à l'aide de ROLLUP