Home > SSAS > How to use ITEM and when ITEM(0).ITEM(0) is redundant

 

How to use ITEM and when ITEM(0).ITEM(0) is redundant

August 30th, 2010

In MDX we have the Item function which can be used in a number of ways. It is important to understand how it works and how it can be used to our advantage.

As a start, we can call Item over a set or over a tuple:

{set}.Item(0) or (tuple).Item(0)

It may be important to note that when we call Item over a set, we get a tuple out of it (sets are collections of tuples), while if we call it over a tuple we get a member.

If we use Item with a tuple, we must specify as an argument the integer position of the member within the tuple which we want. However, when we works with sets, we can either do the same, or specify a number of strings, which identify specific tuples. Some examples:

Item with a tuple:

(a,b).Item(0) = a

SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 ([Product].[Category].&[4], [Date].[Calendar].[Calendar Year].&[2008]).Item(0)
} ON 1
FROM [Adventure Works]

(a,b).Item(1) = b

SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 ([Product].[Category].&[4], [Date].[Calendar].[Calendar Year].&[2008]).Item(1)
} ON 1
FROM [Adventure Works]

Item with a set:

{a,b,c}.Item(0) = a

WITH
SET SET1 AS
 { ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
 }
SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 SET1.Item(0)
} ON 1
FROM [Adventure Works]

{a,b,c}.Item(“a”) = a

WITH
SET SET1 AS
 { ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
 }
SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 SET1.Item("([Product].[Category].&[4],
             [Date].[Calendar].[Calendar Year].&[2008])")
} ON 1
FROM [Adventure Works]

{(a1,b1),(a2,b2),(a3,b3)}.Item(“a1″,”b1″) = (a1,b1)

WITH
SET SET1 AS
 { ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
 }
SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 SET1.Item("[Product].[Category].&[4]",
           "[Date].[Calendar].[Calendar Year].&[2008]")
} ON 1
FROM [Adventure Works]

When we specify a number of strings as arguments, we get the tuple which is defined by these strings/coordinates.

Now, let’s see what happens when we have a set of tuples and we use Item on it with a single argument:

{(a1,b1),(a2,b2),(a3,b3)}.Item(0) = (a1,b1)

WITH
SET SET1 AS
 { ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
 }
SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 SET1.Item(0)
} ON 1
FROM [Adventure Works]

We get a tuple back. Therefore, if we use a second Item function over the first one, we will get the member on that position from the tuple:

{(a1,b1),(a2,b2),(a3,b3)}.Item(0).Item(0) = (a1,b1).Item(0) = a1

To illustrate the concept:

WITH
SET SET1 AS
 { ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
 }
SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 SET1.Item(0).Item(0)
} ON 1
FROM [Adventure Works]

This gives us the whole amount for Accessories, while:

WITH
SET SET1 AS
 { ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
 }
SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 SET1.Item(0).Item(1)
} ON 1
FROM [Adventure Works]

gives us the total amount for 2008.

Even if we do:

WITH
SET SET1 AS
 { ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
 }
SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 SET1.Item(0).Item(0).Item(0).Item(0).Item(0).Item(0).Item(0).Item(0)
} ON 1
FROM [Adventure Works]

we still get the amount for accessories.

What happens here  is:

  • With the first call of Item(0) over SET1 we get the first tuple from the set (in our case it is ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008])).
  • Then with the second call, we get the first member of this tuple – [Product].[Category].&[4].
  • Now, with the third call of Item(0) over this member, we get the first member from the implicitly converted to tuple member from the previous step. Therefore, we pull out the first member from it which is ([Product].[Category].&[4]).
  • From here onwards we flip between a tuple and a member as a result every time we call Item(0).

But if we do:

WITH
SET SET1 AS
 { ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
  ([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
 }
SELECT
{
 [Measures].[Internet Sales Amount]
} ON 0,
{
 SET1.Item(0).Item(1).Item(1).Item(0).Item(0).Item(0).Item(0).Item(0)
} ON 1
FROM [Adventure Works]

we get nothing back. This is because there is no element on the second position/coordinate of the tuple ([Date].[Calendar].[Calendar Year].&[2008]).

Therefore calling Item(0) after another Item(0) is rarely necessary and should be done only if we need it, because we could either get wrong results or possibly hurt our query performance.

Note: Please read the comments below, where you can find an in-depth discussion about the concepts in this article.

If you enjoyed this post, make sure you subscribe to my RSS feed!

 

SSAS , ,

  1. | #1

    Good post! :)

  2. | #2

    Hi Boyan, this is not strictly correct. .Item() returns a tuple and .Item returns a member. When you call .item(0).item(0) what happens is that the first item(0) returns a member, then in order to process the second .item(0) SSAS implicitly casts the member to a tuple, so all you are doing by chaining more than two .item() calls from a tuple is force SSAS to do a lot of implicit casting between member and tuple.

    The .Item(0).Item(0) syntax can be useful for explicitly returning a member when you have a set, but it’s often not necessary as SSAS can do the casting implicitly.

  3. | #3

    Hi Darren,

    With all due respect, the MDX function reference specifies two versions of Item – one which returns a tuple from a set and another one, which returns a member from a tuple.

    Both of these accept an Index argument and the version also accepts a string argument. There is no mention about Item with no argument.

    When you call .Item(0).Item(0) over a set, you get:

    set.Item(0) -> tuple
    tuple.Item(0) -> member
    member.Item(0) = tuple.Item(0) -> member

    I sort of skipped this part and said that the result is a set which is not strictly correct. However, yes – strictly speaking we are going from a tuple to a member and then again to a tuple, etc. I will amend the post to include this bit as well. When I am thinking now, I should explain also what gets returned from each version of Item().

    The idea of the post came after I saw a number of people doing Item(0).Item(0) over a set which was not really needed. It got me thinking – if we have a set of {(a,b), (c,d)} and we do Item(0).Item(0) on it we will get:

    {(a,b),(c,d)}.Item(0) -> (a,b) and then another .Item(0) -> a

    while the intention could be (a,b)

    Also, since there is no need to do:

    {(a),(b)}.Item(0).Item(0) to get a member out of the (a) tuple, I do not see why we would be calling it _ever_ in this scenario.

  4. | #4

    Sorry, I did not meant to imply that you could call .Item without an arguement I’m not sure if part of my post got chopped or if I mucked up something during an edit. I meant to say that the first item(0) will return a tuple and the second item(0) returns a member.

    It’s not so much that dropping the extra .item(0) will cause the code to fail (although I have seen that happen). It’s more about self-documentation. In an example like {(a),(b)}.Item(0).Item(0) I can tell by looking at the code that you explicitly wanted the first member of the first tuple. In most cases {(a),(b)}.Item(0) will work just as well as the implicit type casting will kick in and convert it for you but then if I have to read your code I have to figure out by the context what your intent was.

    It’s more of a style thing that a necessity.

    I think that the implicit conversion can make writting MDX easier, but it can make understanding it harder. For example when you chain more than two .item(0) calls together the return value does not flip between tuple and member, the implicit cast from a member to a tuple happens on the fly so a call like:

    {}.item(0).item(0).item(0).item(0)

    returns data types of:

    Tuple, Member, Member, Member, Member

  5. | #5

    I agree with your statement that it is a style thing rather than an issue of correctness/necessity. I have never seen code failing due to not using a second .Item(0), but I have seen code, which is wrong because people do call a second .Item(0) when it should have not been called, and I have also seen code full of Item(0).Item(0) when not necessary (and I am convinced they just use it by default without reasoning or understandning what it actually means).

    I am very happy to have surfaced this discussion here, because it may help some developers uderstand Item() calls better – thanks for the help.

    As for my statement that if we call member.Item(0) we go back to tuple – well, it does go to tuple -> member behind the scenes, doesn’t it? My understanding is that this is automatic and completely hidden to the developer, but it does happen. We do get a member with every subsequent Item() call, but in the background the member gets first cast to a tuple and then back to member as a result of the tuple.Item(0) call. I hope I am not mistaken here.

Comments are closed.