Using Aggregate Functions

Analysis Services

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

See Also

MDX Function List

Measures

Virtual Cubes