Featured Database Articles

MDX Essentials: Basic Member Functions: The .Item() 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.

Overview

In
this lesson, we will expose another popular function in the MDX toolset, the .Item()
function. The general purpose of the .Item() function, as we shall see,
is twofold: to return a member from a tuple, or to return a tuple from a set.
We will consider each variation, to which I will refer as the "member"
and "tuples" versions, respectively, based upon what each returns,
in the following sections.

As is
the case with many MDX functions, the .Item() function can be leveraged
in activities that range from the simple to the sophisticated, and, as is often
the case, its use should be tempered with an understanding of its potential
impact upon performance. We will introduce the function, commenting upon its
operation and touching upon performance considerations 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 of the results datasets we obtain in the practice
examples.

The .Item() Function

Introduction

According to the Analysis Services
Books Online, the .Item()function "returns
a member from a specified tuple" or "alternatively, returns a tuple
from a set." Its use, depending upon whether it is being applied to a
tuple or to a set, dictates the syntax involved, as we shall see. In either
scenario, the function uses a zero-based index, a feature we have seen in other
functions within this series, to indicate the position of the object upon which
it is enacted.

When using the .Item() function to
return a member from a tuple, we specify the tuple and supply the index to "locate"
the member within that tuple. When we apply the function to a set, we can
specify the tuple by identifying it with a string expression, or by providing
its position via the index, as we do in using it as a member function.

The indiscriminate use of the .Item()
function can degrade performance in some scenarios, just as it can increase
maintenance overhead. Examples include the performance hit that can accompany the
use of the function within aggregates, particularly in the context of large
dimensions, where using .Item() in leaf-level scenarios can mean less-than-desirable
results. Moreover, when an expression containing .Item() relies upon
changing data (is subject to incremental updates, etc.), more frequent update
evolutions in general might become necessary to keep the underlying data in
sync with components of the cube structure that are not automatically "refreshed"
to meet the underlying structure. The indexed positions for "last
occurrence" of something returned in a query using .Item(),in
common examples, witnesses a change, but the components supported by the query
do not reflect this until the query itself is updated. In short, more
maintenance overhead is induced.

We will examine the syntax for the .Item()
function for both tuple and set scenarios, and explore its behavior based upon indexinput we might provide. 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 restate our initial explanation of its operation, the .Item()
function, when acting upon a tuple, returns the member we
identify through the use of the index we provide. The alternative use
for the function, applied to a set, allows us to return the tuple
we specify in a string expression, or identify via an index such as we
use in the tuple variety of the function. In either the member
or tuple application of the .Item() function, the index is
zero-based, meaning that the first position is indicated by "0" and
not "1."

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

Syntax

Member Version

Syntactically, in using the .Item()
function to return members, the tuple upon which we seek to apply
the .Item() function is specified to the left of .Item(). The
function takes the tuple to which it is appended, together with the index
appearing to its right, as its argument, and returns the member at the +index;
position within the tuple specified. The syntax is shown in the
following string.

<<Tuple >>.Item(<< Index >>)

A member is thus returned from the tuple
specified in +Tuple;, based upon its position as dictated by the
zero-based +Index;.

Tuple Version

In the alternative tuple -
returning version of the function, the set upon which we wish to apply
the .Item() function is specified, again, to the left of .Item().
The function then takes the set to which it is appended, together with
the index appearing to its right, as its argument, and returns the tuple
at the +index; position within the set specified. The syntax is
shown in the following string.

While it is otherwise very similar to the member
version, the tuple version, as it is explained in the Books
Online, has a notable difference: In addition to returning the tuple
from the set specified in +Index; (in a manner similar to the
operation of the member version of the function), we can alternatively
specify the tuple to be returned by name using the +String
Expression;. (As we shall see in one of our practice examples, single
strings can apparently be used in the member-returning variety of .Item(),
as well.)

Let's look at some
representative expressions to illustrate the uses of the .Item()
function. First, when used as a member function, the following
expression:

returns a result set that might
resemble (depending upon the composition of the rest of the query containing
the excepted example) that shown in Illustration 1.

Illustration 1: Example Results Using Strings in Item()
Function

In the above illustration, we can see that the .Item()
function has retrieved the tuple matching a dual string expression. When we
use multiple strings, we are required to create a string for each dimension
in the set upon which we wish to apply the .Item() function. We
might restate our double-string expression above, using a single string, to
obtain the same results as depicted above, as follows:

When we use a single string in this manner, we are required
to completely specify the tuple within the string. Again, a primary difference
is that the multiple-string example addresses each dimension in the set,
one member per dimension, in the same order that the dimensions
appear in the set.

We will practice some uses of the .Item() function in
the section that follows.