Share via


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