MDX Essentials: Numeric Functions: Introduction to the AVG() Function

About the Series …

This article is a member of the series MDX Essentials.
The series is designed to provide hands-on application of the fundamentals of
the Multidimensional Expressions (MDX) language, with each tutorial
progressively adding features designed to meet specific real-world needs. For
more information about the series in general, as well as the software and
systems requirements needed for getting the most out of the lessons included,
please see the first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Service Pack 3 / 3a updates are assumed
for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and
the related Books Online and Samples.

Overview

In
this lesson, we will introduce a commonly used numeric function in the MDX
toolset, the AVG() function. The general purpose of the AVG() function,
as we shall discover, is to return the average of the tuples occupying a set. We
will consider elementary uses of the function in this article, and then explore
more sophisticated uses in subsequent articles. For now, we will build a
foundation in the basics.

The AVG()
function can be leveraged in activities that range from the simple to the
complex, as is the case with many other MDX functions. We will introduce the
function, commenting upon its operation and touching upon variations at a
general level, and then we will:

• Examine the syntax surrounding the function;

• Undertake illustrative examples of the uses of the function in
  practice exercises;

• Briefly discuss the results datasets we obtain in the practice
  examples.

The AVG() Function

Introduction

According to the Analysis Services Books
Online, the AVG() function "returns the average value of a
numeric expression evaluated over a set." The function uses a numeric
expression to indicate the base value for which the average will be
calculated. (An example of the numeric expression might be the measure Warehouse
Sales in the FoodMart 2000 Warehouse cube).

The AVG() function ignores empty
values found within the cells that are associated with the specified set. Its
behavior with regard to empty cells can be circumvented at least a couple of
ways, as we shall see in the next section.

After discussing its operation in the next
section, we will examine the syntax for the AVG() function. Next, we
will undertake practice examples constructed to support hypothetical business
needs that illustrate uses for the function. This will allow us to activate
what we explore in the Discussion and Syntax
sections, by getting some hands-on exposure in creating expressions that
leverage the function.

Discussion

To reword our initial explanation of its operation, the AVG()
function computes the average of the non-empty values populating the
cells of the set specified within the function. Mechanically, this
means that the total value (the sum) of the cells inhabiting the
set is divided by the number (or count) of populated cells.

A key concept here is that a behind-the-scenes count of the
populated cells is taking place for use as the "divisor" in the
calculation. Empty cells are not included in the divisor. In cases where we
wish to count the empty cells, as well, we can force the inclusion of these
cells by employing the COALESCEEMPTY() function. Another approach might
be to work around the normal exclusion of empty cells by simply approaching the
computation of the average in a different manner, such as by taking the results
obtained by subjecting the set to the SUM() function, which we would
then divide by the results returned by subjecting the same set to an
appropriate COUNT() function.

Subjecting
an empty set to the AVG() function returns the same result we
obtain within a scenario where we divide by zero (commonly denoted by "1.INF"
appearing within the affected parts of the dataset).

Let’s look at some syntax illustrations to further clarify
the operation of AVG().

Syntax

Syntactically, the set we use to specify the range of cells
for which we wish to calculate the average is placed within the parentheses to
the right of AVG, and separated by a comma from the numeric
expression we have described.

The syntax is shown in the
following string:

Avg(Set, [, Numeric Expression])

The following example expression illustrates a use of the AVG()
function. Let’s say that information consumers from the FoodMart Logistics
department, whose data is housed within the Warehouse cube, come to us
with a straightforward request: The consumers wish to see the total national
averages for Units Shipped for each of the Category groups
composing our Non-Consumable products line. They wish the totals for
the member Product Categories to be displayed, with the averages for the
three countries in which FoodMart operates, Canada, Mexico and
the USA, to be presented side by side.

The basic AVG() function involved, within a core
query that presents the information in the manner requested, would be constructed
as follows:

WITH

MEMBER

    [Store].[Nat’l Avg]

AS

   ‘AVG(  { [Store].[Store Country].Members}, [Measures].[Units Shipped])’

SELECT

{ [Store].[Store Country].Members, [Store].[Nat’l Avg] } ON COLUMNS,

    {[Product].[Product Family].[Non-Consumable].Children} ON ROWS

FROM

   [Warehouse]

WHERE [Measures].[Units Shipped]

Our query is simply expressing that we wish to retrieve the "average
total units shipped for each of our Non-Consumable product categories,
by country of store operation." We are ignoring time – the
consumers are aware that the cube contains data from two years, and want the
information to be based upon the cube as a whole.

The query we construct returns a dataset similar to that depicted in Table
1.

The above example serves to illustrate the treatment, within
the AVG() function, of empty cells within the specified set. The results
demonstrate clearly that the function excludes empty cells in calculating
the average we see.

We will practice the use of the AVG() function in the
section that follows. We will start with a relatively simple scenario, and
then construct a second, slightly more sophisticated query.