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.

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 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 index input 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.

<<Set >>.Item(<< String Expression>>[,<< String Expression>>...] | << Index >>)

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:

([Units Shipped], [1998], [Units Shipped], [1999]).Item(0)

returns the following:

[Units Shipped]

and

([Product].[Product Family].Drink, [Product].[Product Family].Food).Item(1)

returns the following:

[Product].[Product Family].Food

To illustrate the uses of .Item() as a tuple function, the following expression:

(([Units Shipped], [1998]), ([Units Shipped], [1999])).Item(0)

returns the following:

([Units Shipped], [1998])

and

CrossJoin([Store Type].[Store Type]. Members, 
     [Product].[Product Family].Members).Item("([Supermarket], [Food])") 
     Family].Members).Item("[Supermarket]","[Food]") 

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.

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:

CrossJoin([Store Type].[Store Type]. Members, 
     [Product].[Product Family].Members).Item("([Supermarket], [Food])")

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.