Using Aggregate Functions
This topic contains examples for using the aggregate functions (Sum, Min, Max, Count, and Distinct Count) in measures. The examples for the query are based on the same cube cells as the following examples so that you can see the effects of changing the function.
The cube that these examples use has a single measure, Sales, based on the Sales_Amount column in the Sales fact table. The cube has three dimensions:
Customers, based on the table Customers and containing these levels from highest to lowest:
(All)
Customer with Customer_Name as the member name column and Customer_ID as the member key column
Retail Stores, based on the table Retail_Stores and containing these levels from highest to lowest:
(All)
Retail Store with Retail_Store_Name as the member name column and Retail_Store_ID as the member key column
Products, based on the table Products and containing these levels from highest to lowest:
(All)
Product Category with Product_Category as the member name column and the member key column
Product with Product_Name as the member name column and Product_ID as the member key column
For more information about dimensions and levels, see Dimensions and Levels.
The cube's schema is shown here.
The cube's fact table, Sales, is shown here.
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 |
One of the cube's dimension tables, Customers, is shown here.
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 |
Another of the cube's dimension tables, Retail_Stores, is shown here.
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 |
The cube's final dimension table, Products, is shown here.
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
If a measure's Aggregate Function property value is Sum, the measure value for a cube cell is calculated by adding the values in the measure's source column from only the rows for the combination of members that defines the cell and the descendants of those members.
Examples
The following examples return values that represent accumulated Sales.
A: Querying One Atomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product A returns 800.
B: Querying One Nonatomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product category AB returns 900.
C: Querying Multiple Cube Cells
A query on the Sales measure places each retail store on the x-axis, nests products under product categories on the y-axis, and slices by All Customers. It returns the following dataset.
All Retail Stores |
A |
B |
C |
D |
|||
All Products |
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
If a measure's Aggregate Function property value is Min, the measure value for a cube cell is calculated by taking the lowest value in the measure's source column from only the rows for the combination of members that defines the cell and the descendants of those members.
Examples
The following examples return values that represent the lowest Sales price.
A: Querying One Atomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product A returns 250.
B: Querying One Nonatomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product category AB returns 100.
C: Querying Multiple Cube Cells
A query on the Sales measure places each retail store on the x-axis, nests products under product categories on the y-axis, and slices by All Customers. It returns the following dataset.
All Retail Stores |
A |
B |
C |
D |
|||
All Products |
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
If a measure's Aggregate Function property value is Max, the measure value for a cube cell is calculated by taking the highest value in the measure's source column from only the rows for the combination of members that defines the cell and the descendants of those members.
Examples
The following examples return values that represent the highest Sales price.
A: Querying One Atomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product A returns 300.
B: Querying One Nonatomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product category AB returns 300.
C: Querying Multiple Cube Cells
A query on the Sales measure places each retail store on the x-axis, nests products under product categories on the y-axis, and slices by All Customers. It returns the following dataset.
All Retail Stores |
A |
B |
C |
D |
|||
All Products |
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
If a measure's Aggregate Function property value is Count, the measure value for a cube cell is calculated by adding the number of values in the measure's source column from only the rows for the combination of members that defines the cell and the descendants of those members.
Examples
The following examples return values that represent the number of Sales transactions.
A: Querying One Atomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product A returns 3.
B: Querying One Nonatomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product category AB returns 4.
C: Querying Multiple Cube Cells
A query on the Sales measure places each retail store on the x-axis, nests products under product categories on the y-axis, and slices by All Customers. It returns the following dataset.
All Retail Stores |
A |
B |
C |
D |
|||
All Products |
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
If a measure's Aggregate Function property value is Distinct Count, the measure value for a cube cell is calculated by adding the number of unique values in the measure's source column from only the rows for the combination of members that defines the cell and the descendants of those members.
A measure with an Aggregate Function property value of Distinct Count is called a distinct count measure. A distinct count measure can be used to count occurrences of a dimension's lowest-level members in the fact table. Because the count is distinct, if a member occurs multiple times, it is counted only once.
Distinct count measures are commonly used to determine for each member of a dimension how many distinct, lowest-level members of another dimension share rows in the fact table. For example, in a Sales cube, for each customer and customer group, how many distinct products were purchased? (That is, for each member of the Customers dimension, how many distinct, lowest-level members of the Products dimension share rows in the fact table?) Or, for example, in an Internet Site Visits cube, for each site visitor and site visitor group, how many distinct pages on the Internet site were visited? (That is, for each member of the Site Visitors dimension, how many distinct, lowest-level members of the Pages dimension share rows in the fact table?) In each of these examples, the second dimension's lowest-level members are counted by a distinct count measure.
This kind of analysis need not be limited to two dimensions. In fact, a distinct count measure can be separated and sliced by any combination of dimensions in the cube, including the dimension that contains the counted members.
A distinct count measure that counts members is based on a foreign key column in the fact table. (That is, the measure's Source Column property identifies this column.) This column joins the dimension table column that identifies the members counted by the distinct count measure.
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft® SQL Server™ 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note
If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
Examples
The following examples return values that represent the number of Sales transactions with a unique Sales price.
A: Querying One Atomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product A returns 2.
B: Querying One Nonatomic Cube Cell
A query on the Sales measure for customer A, retail store A, and product category AB returns 3.
C: Querying Multiple Cube Cells
A query on the Sales measure places each retail store on the x-axis, nests products under product categories on the y-axis, and slices by All Customers. It returns the following dataset.
All Retail Stores |
A |
B |
C |
D |
|||
All Products |
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 |