MDX Essentials: Basic Member Functions: The .Item() Function

Monday Aug 9th 2004 by William Pearson
Share:

Return a member from a tuple, or a tuple from a set, with the useful .Item() function. MSAS Architect Bill Pearson introduces the .Item() function, and then provides practical exploration of its use with illustrative examples.

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.


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:


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.

Practice

Preparation

To reinforce our understanding of the basics we have covered so far, we will use the .Item() function in a manner that illustrates its operation. We will do so in simple scenarios that place .Item() within the context of meeting a business need.

To begin, we will construct a SELECT query with a clearly defined set, then put .Item() to use in manipulating that set to meet the business needs of a group of hypothetical information consumers. The intent is, of course, to demonstrate the operation of the .Item() function in a straightforward manner.

Let's return to the MDX Sample Application as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

Let's assume, for our practice example, that we have received a call from the Finance department of the FoodMart organization, requesting some information surrounding units shipped for a given time frame upon which they plan to perform some basic analysis. The Finance information consumers specifically wish to know the total units shipped figure for the US store states, for each of the quarters of 1998, having become focused on this state / time combination initially to do some per unit analysis of various logistics costs.

We will first create a basic query that returns the units shipped totals for each of the US store states, over the quarters under consideration. It is from the results dataset that is returned that the consumers state that they may want to narrow their request, once they get a look at overall figures, to more focused view(s) of specific state(s) only.

Let's construct a simple query, therefore, to return the units shipped information, presented by quarters (as columns) and the store states (as rows).

5.  Type the following query into the Query pane:

-- MDX022-1, Preparation for Use of .Item() Function, Numerical Index, 
-- in a    Basic Query
SELECT
   {[Time].[1998].Children} ON COLUMNS,
   {[Store].[All Stores].[USA].Children}  ON ROWS
FROM
   [Warehouse]
WHERE
   ([Measures].[Units Shipped])

6.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 2 appears.


Illustration 2: Result Dataset - Preparation for Use of .Item() Function

We see the four quarters of 1998 populating the columns across, and the Store States (from the Stores dimension) appearing on the row axis.

7.  Select File -> Save As, name the file MDX022-1, and place it in a meaningful location.

8.  Leave the query open for the next section.

Next, let's say that our information consumers are provided with the full set of data that we have generated. They examine the metrics, and then request that we narrow the report scope to "Oregon-only" stores.

We realize, from looking at our first results dataset, and perhaps in Analysis Manager, etc., that Oregon is the second of the three members of the tuple we have defined within our initial query. We decide that the .Item() function, with an index setting of 1 ("0" being the first member position, and "1" the second) will serve to generate the new limitation on the results.

We take the following actions to meet the new consumer request:

9.  Within the query we have saved as MDX022-1, replace the top comment line of the query with the following:

-- MDX022-2, Use of .Item() Function, Numerical Index, in a  Basic Query

10.  Save the query as MDX022-2, to prevent damaging MDX022-1.

11.  Append the .Item() function to the rows axis definition of the query, changing the following line:

   {[Store].[All Stores].[USA].Children}  ON ROWS

to the following

                 {[Store].[All Stores].[USA].Children.Item(1)} ON ROWS

The Query pane appears as shown in Illustration 3, with the new addition circled.


Illustration 3: The Query with Added .Item() Function on the Row Axis

12.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset depicted in Illustration 4 appears.


Illustration 4: Result Dataset - .Item() Function with Numerical Index

13.  Re-save the file as MDX022-2.

14.  Leave the query open for the next step.

We have used the .Item() function, in its member incarnation, with a simple numerical index. Let's see an example where we use a string within the index, based upon our original query above, where we selected all three store states.

15.  Within the query we have saved as MDX022-2, replace the top comment line of the query with the following:

-- MDX022-3, Use of .Item() Function, String Index, in a  Basic Query

16.  Save the query as MDX022-3.

17.  Inside the parentheses of .Item() function, replace the existing numeral ("1") with the following string:

            "WA"

The Query pane appears as shown in Illustration 5, with changes.


Illustration 5: The Query with Index modified to String

18.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset depicted in Illustration 6 appears.


Illustration 6: Result Dataset - .Item() Function with String Index

19.  Resave the query as MDX022-3, leaving the sample application open for the steps that follow.

We deliver the new results to the Finance consumers, who accept our work thus far with stated satisfaction. They promise to "get back to us" with follow-up requests in short order.

We next hear from a representative of Finance a couple of days later, with a request that contains elements similar to the specifications so far. The group needs the units shipped totals for the Washington stores, from the perspective of non-consumable goods sold only, for the first three quarters of 1998.

We recognize instantly the opportunity, in this case, to combine both the member and tuple versions of the .Item() function into a single query, and set out to do so in the following steps.

20.  Create a new query by selecting Query --> New on the Sample Application toolbar.

21.  Type the following into the Query pane:


-- MDX022-4, Use of .Item() Function, Member and Tuple (MultiString Index)
SELECT
   {[Time].[1998].Children.Item(0): [Time].[1998].Children.Item(2) } ON COLUMNS,
{CROSSJOIN([Store].[All Stores].[USA].Children, [Product].
   [Product Family].Members).Item("[WA]","[Non-Consumable]")} ON ROWS
FROM
   [Warehouse]
WHERE 
   ([Measures].[Units Shipped])

As we can see, we are using the member variation of the .Item() function to return the three quarters (indexes 0, 1, and 2, respectively), the range of which we manage conveniently with the colon (":") operator. In addition, we are using the tuple variation of .Item(), with multiple string indexing option, in the rows axis, where we are cross joining store states and product family. We comply with the rules for the multi-string index approach, in that we stipulate each dimension present in the tuple, in appropriate order, and so we obtain the desired results. (Try changing the order of the strings, or otherwise manipulate them, to see the results, as convenient).

NOTE: For more information on the CrossJoin() function, see my Database Journal article MDX Essentials: Basic Set Functions: The CrossJoin() Function .

22.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset shown in Illustration 7 appears.


Illustration 7: Result Dataset - Combined .Item() Functions

23.  Save the file as MDX22-4.

Summary ...

In this lesson, we explored the MDX .Item() function. We discussed the tandem general objectives of the function, to return a member from a tuple, or a tuple from a set, and then we examined the syntax for each of the two variations.

We then undertook illustrative practice examples of business uses for the .Item() function, generating queries that capitalized on its primary features. Finally, we briefly discussed the results datasets we obtained from each of the queries we constructed.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved