# 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

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

Line_1

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_

Retail_Store_

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