Utilisation des fonctions d'agrégation
Cette rubrique contient des exemples d'utilisation des fonctions d'agrégation (Sum, Min, Max, Count et Distinct Count) dans des mesures. Les exemples de la requête utilisent les mêmes cellules de cube que les exemples suivants afin que vous puissiez voir l'effet d'un changement de fonction.
Le cube qu'utilisent ces exemples possède une seule mesure, Sales, fondée sur la colonne Sales_Amount de la table de faits Sales. Le cube a trois dimensions :
Customers, reposant sur la table Customers et contenant ces niveaux, du plus élevé au plus bas :
(Tous) ;
Customer, avec Customer_Name comme colonne des noms de membre et Customer_ID comme colonne des clés de membre.
Retail Stores, d'après la table Retail_Stores et contenant ces niveaux, du plus élevé au plus bas :
(Tous) ;
Retail Store avec Retail_Store_Name comme colonne des noms de membre et Retail_Store_ID comme colonne des clés de membre.
Products, d'après la table Products et contenant ces niveaux, du plus élevé au plus bas :
(Tous) ;
Product Category avec Product_Category comme colonne des noms de membre et colonne des clés de membres ;
Product avec Product_Name comme colonne des noms de membre et Product_ID comme colonne des clés de membre.
Pour plus d'informations sur les dimensions et les niveaux, consultez Dimensions et niveaux
Le schéma du cube est présenté ici.
La table de faits du cube, Sales, est présentée ici.
Transaction_ID |
Customer_ID |
Product_ID |
Retail_Store_ID |
Sales_ Amount |
|---|---|---|---|---|
1 |
1 |
1 |
1 |
300 |
2 |
1 |
1 |
1 |
250 |
3 |
1 |
1 |
1 |
250 |
4 |
1 |
2 |
1 |
100 |
5 |
1 |
4 |
1 |
700 |
6 |
2 |
1 |
2 |
290 |
7 |
2 |
2 |
2 |
90 |
8 |
2 |
3 |
3 |
510 |
9 |
3 |
1 |
4 |
350 |
10 |
3 |
2 |
3 |
110 |
11 |
4 |
3 |
4 |
550 |
12 |
4 |
4 |
4 |
750 |
L'une des tables de dimensions du cube, Customers, est présentée ici.
Customer_ID |
Customer_Name |
Customer_Address_ Line_1 |
Customer_Address_ Line_2 |
|---|---|---|---|
1 |
A |
1 A Street |
Aville, AA 55555 |
2 |
B |
2 B Street |
Bville, BB 55555 |
3 |
C |
3 C Street |
Cville, CC 55555 |
4 |
D |
4 D Street |
Dville, DD 55555 |
Une autre table des dimensions du cube, Retail_Stores, est présentée ici.
Retail_ Store_ID |
Retail_Store_ Name |
Retail_Store_ Address_Line_1 |
Retail_Store_ Address_Line_2 |
|---|---|---|---|
1 |
A |
1 A Avenue |
Atown, AA 55555 |
2 |
B |
2 B Avenue |
Btown, BB 55555 |
3 |
C |
3 C Avenue |
Ctown, CC 55555 |
4 |
D |
4 D Avenue |
Dtown, DD 55555 |
La table des dimensions finale du cube, Products, est présentée ici.
Product_ID |
Product_Name |
Product_Description |
Product_Category |
|---|---|---|---|
1 |
A |
aaaa aaaa aaaa |
AB |
2 |
B |
bbbb bbbb bbbb |
AB |
3 |
C |
cccc cccc cccc |
CD |
4 |
D |
dddd dddd dddd |
CD |
SUM
Si la propriété Aggregate Function d'une mesure a la valeur Sum, la valeur de mesure d'une cellule du cube est calculée en additionnant les valeurs de la colonne source de la mesure uniquement à partir des lignes de la combinaison de membres qui définit la cellule et les descendants de ces membres.
Exemples
Les exemples suivants renvoient des valeurs représentant des ventes cumulées.
A : interrogation d'une cellule de cube atomique
Une requête dans la mesure Sales pour le client A, le magasin de détail A et le produit A renvoie 800.
B : interrogation d'une cellule de cube non atomique
Une requête sur la mesure Sales pour le client A, le magasin de détail A et la catégorie de produits AB renvoie 900.
C : interrogation de plusieurs cellules de cube
Une requête sur la mesure Sales place chaque magasin de détail sur l'axe X, imbrique les produits par catégorie sur l'axe Y et effectue un découpage sur tous les clients. Elle renvoie le dataset suivant.
Tous les magasins de détail |
A |
B |
C |
D |
|||
Tous les produits |
4250 |
1600 |
380 |
620 |
1650 |
||
AB |
1740 |
900 |
380 |
110 |
350 |
||
A |
1440 |
800 |
290 |
350 |
|||
B |
300 |
100 |
90 |
110 |
|||
CD |
2510 |
700 |
510 |
1300 |
|||
C |
1060 |
510 |
550 |
||||
D |
1450 |
700 |
750 |
Min
Si la propriété Aggregate Function d'une mesure a la valeur Min, la valeur de mesure d'une cellule du cube est calculée en prenant la valeur la plus faible dans la colonne source de la mesure uniquement à partir des lignes de la combinaison de membres qui définit la cellule et les descendants de ces membres.
Exemples
Les exemples suivants renvoient des valeurs qui représentent le prix de vente le plus bas.
A : interrogation d'une cellule de cube atomique
Une requête dans la mesure Sales pour le client A, le magasin de détail A et le produit A renvoie 250.
B : interrogation d'une cellule de cube non atomique
Une requête sur la mesure Sales pour le client A, le magasin de détail A et la catégorie de produits AB renvoie 100.
C : interrogation de plusieurs cellules de cube
Une requête sur la mesure Sales place chaque magasin de détail sur l'axe X, imbrique les produits par catégorie sur l'axe Y et effectue un découpage sur tous les clients. Elle renvoie le dataset suivant.
Tous les magasins de détail |
A |
B |
C |
D |
|||
Tous les produits |
90 |
100 |
90 |
110 |
350 |
||
AB |
90 |
100 |
90 |
110 |
350 |
||
A |
250 |
250 |
290 |
350 |
|||
B |
90 |
100 |
90 |
110 |
|||
CD |
510 |
700 |
510 |
550 |
|||
C |
510 |
510 |
550 |
||||
D |
700 |
700 |
750 |
Max
Si la propriété Aggregate Function d'une mesure a la valeur Max, la valeur de mesure d'une cellule du cube est calculée en prenant la valeur la plus élevée dans la colonne source de la mesure uniquement à partir des lignes de la combinaison de membres qui définit la cellule et les descendants de ces membres.
Exemples
Les exemples suivants renvoient des valeurs représentant le prix de vente le plus élevé.
A : interrogation d'une cellule de cube atomique
Une requête dans la mesure Sales pour le client A, le magasin de détail A et le produit A renvoie 300.
B : interrogation d'une cellule de cube non atomique
Une requête sur la mesure Sales pour le client A, le magasin de détail A et la catégorie de produits AB renvoie 300.
C : interrogation de plusieurs cellules de cube
Une requête sur la mesure Sales place chaque magasin de détail sur l'axe X, imbrique les produits par catégorie sur l'axe Y et effectue un découpage sur tous les clients. Elle renvoie le dataset suivant.
Tous les magasins de détail |
A |
B |
C |
D |
|||
Tous les produits |
750 |
700 |
290 |
510 |
750 |
||
AB |
350 |
300 |
290 |
110 |
350 |
||
A |
350 |
300 |
290 |
350 |
|||
B |
110 |
100 |
90 |
110 |
|||
CD |
750 |
700 |
510 |
750 |
|||
C |
550 |
510 |
550 |
||||
D |
750 |
700 |
750 |
Count
Si la propriété Aggregate Function d'une mesure a la valeur Count, la valeur de mesure d'une cellule du cube est calculée en additionnant le nombre de valeurs dans la colonne source de la mesure uniquement à partir des lignes de la combinaison de membres qui définit la cellule et les descendants de ces membres.
Exemples
Les exemples suivants renvoient des valeurs représentant le nombre de transactions Sales.
A : interrogation d'une cellule de cube atomique
Une requête dans la mesure Sales pour le client A, le magasin de détail A et le produit A renvoie 3.
B : interrogation d'une cellule de cube non atomique
Une requête sur la mesure Sales pour le client A, le magasin de détail A et la catégorie de produits AB renvoie 4.
C : interrogation de plusieurs cellules de cube
Une requête sur la mesure Sales place chaque magasin de détail sur l'axe X, imbrique les produits par catégorie sur l'axe Y et effectue un découpage sur tous les clients. Elle renvoie le dataset suivant.
Tous les magasins de détail |
A |
B |
C |
D |
|||
Tous les produits |
12 |
5 |
2 |
2 |
3 |
||
AB |
8 |
4 |
2 |
1 |
1 |
||
A |
5 |
3 |
1 |
1 |
|||
B |
3 |
1 |
1 |
1 |
|||
CD |
4 |
1 |
1 |
2 |
|||
C |
2 |
1 |
1 |
||||
D |
2 |
1 |
1 |
Distinct Count
Si la propriété Aggregate Function d'une mesure a la valeur Distinct Count, la valeur de mesure d'une cellule du cube est calculée en additionnant le nombre de valeurs uniques dans la colonne source de la mesure uniquement à partir des lignes de la combinaison de membres qui définit la cellule et les descendants de ces membres.
Une mesure dont la propriété Aggregate Function a la valeur Distinct Count est qualifiée de mesure de comptage distincte. Une mesure de comptage distincte peut être utilisée pour compter les occurrences des membres du niveau le plus bas d'une dimension dans la table de faits. Avec ce type de comptage, si un membre apparaît plusieurs fois, il n'est compté qu'une seule fois.
Les mesures de comptage distinctes s'utilisent généralement pour déterminer pour chaque membre d'une dimension combien de membres distincts du niveau le plus bas d'une autre dimension possèdent des lignes identiques à celles de la table de faits. Par exemple, dans un cube Sales, pour chaque client et groupe de clients, combien de produits différents ont été achetés ? C'est-à-dire, pour chaque membre de la dimension Customers, combien de membres différents du niveau le plus bas de la dimension Products ont en commun des lignes avec la table de faits ? Ou, par exemple, dans un cube de visites d'un site Internet, pour chaque visiteur et chaque groupe de visiteurs, combien de pages distinctes ont été consultées sur le site Internet ? C'est-à-dire, pour chaque membre de la dimension Site Visitors, combien de membres différents du niveau le plus bas de la dimension Pages ont en commun des lignes avec la table de faits ? Dans chacun de ces exemples, les membres du niveau le plus bas de la deuxième dimension sont comptés par une mesure de comptage distincte.
Ce type d'analyse ne se limite pas forcément à deux dimensions. En fait, une mesure de comptage distincte peut être séparée et découpée selon n'importe quelle combinaison de dimensions du cube, y compris la dimension qui contient les membres comptés.
Une mesure de comptage distincte qui compte les membres repose sur une colonne de clé étrangère de la table de faits. C'est-à-dire que la propriété Source Column de la mesure identifie cette colonne. Cette colonne est jointe à la colonne de table de dimensions qui identifie les membres comptés par la mesure de comptage distincte.
L'utilisation de mesures de comptage distinctes est limitée dans les cubes réguliers. Une seule mesure de comptage distincte est autorisée dans un cube régulier et ce, uniquement si le cube régulier ne contient pas une dimension avec des opérateurs de cumul personnalisés ou des formules de cumul personnalisées. Ces restrictions ne s'appliquent pas aux cubes virtuels. Un cube virtuel peut utiliser plusieurs mesures de comptage distinctes, ainsi que des opérateurs de cumul personnalisés et des formules de cumul personnalisées avec les mesures de comptage distinctes.
Puisque les mesures de comptage distinctes sont non additives, la présence d’une mesure de comptage distincte restreint notablement les possibilités de préagrégation du cube par Microsoft® SQL Server™ 2000 Analysis Services. Pour cette raison, il est recommandé de placer chaque comptage distinct dans son propre cube, sans aucune autre mesure. Les cubes avec des mesures de comptage distinctes peuvent être joints avec d’autres cubes dans un cube virtuel qui gère efficacement toutes les mesures.
[!REMARQUE]
Si un cube utilise une mesure de comptage distincte, la totalité du cube est traitée comme étant non additive. Les cubes non additifs ne prennent pas en charge les membres créés dynamiquement ; les fonctions MDX, telles que VisualTotals, qui créent dynamiquement des membres renvoient donc une erreur si elles sont utilisées sur un cube non additif. Cette limite affecte d'autres fonctionnalités telles que l'activation des valeurs visibles dans la sécurité des dimensions qui suppose des membres créés dynamiquement.
Exemples
Les exemples suivants renvoient des valeurs qui représentent le nombre de transactions de ventes ayant un prix de vente unique.
A : interrogation d'une cellule de cube atomique
Une requête dans la mesure Sales pour le client A, le magasin de détail A et le produit A renvoie 2.
B : interrogation d'une cellule de cube non atomique
Une requête sur la mesure Sales pour le client A, le magasin de détail A et la catégorie de produits AB renvoie 3.
C : interrogation de plusieurs cellules de cube
Une requête sur la mesure Sales place chaque magasin de détail sur l'axe X, imbrique les produits par catégorie sur l'axe Y et effectue un découpage sur tous les clients. Elle renvoie le dataset suivant.
Tous les magasins de détail |
A |
B |
C |
D |
|||
Tous les produits |
11 |
4 |
2 |
2 |
3 |
||
AB |
7 |
3 |
2 |
1 |
1 |
||
A |
4 |
2 |
1 |
1 |
|||
B |
3 |
1 |
1 |
1 |
|||
CD |
4 |
1 |
1 |
2 |
|||
C |
2 |
1 |
1 |
||||
D |
2 |
1 |
1 |
Voir aussi