Referencing Measures and Dimension members

Objectives: To understand how to access various members.

The typical way to access any member in MDX is
[Dimension].[Hierarchy].[Level].member
However there are some shortcuts to access members each having their pros and cons. Lets discuss them in detail.
Lets try a query

select
[Measures].[Internet Sales Amount]
on columns
from
[Adventure Works]

The above query was simple, we just reported “Internet Sales Amount”. However this was not accessed in the format specified above. One may think that since “Internet Sales Amount” is a measure therefore it is accessed differently.
Lets try another query.

select
[Measures].[Measures].[Internet Sales Amount]
on columns
from
[Adventure Works]

The above query is exactly in the format specified at the start. The reason is that Measures are also treated as Dimensions and the actual way to refer a Measure is [Measure].[Measure].[member]. However measures are always referred using the shortcut [Measures].[member].
Lets try another query

select
[Internet Sales Amount]
on columns
from
[Adventure Works]

The above query should have failed, right? Nope,turns out that if we refer a member by just its name, SSAS searches for it in every dimension and its attributes. The first instance that is found is returned. Lets see this happen.
Lets try another query.

select
[Measures].[Internet Sales Amount]
on rows,
[Delivery Date].[Calendar Year].[CY 2013]
on columns
from
[Adventure Works]

Now watch what happens when we use the shortcut.

select
[Measures].[Internet Sales Amount]
on rows,
[CY 2013]
on columns
from
[Adventure Works]

Notice the result value changed from “$16,044,747.30” to “$16,351,550.34”. The reason is that “CY 2013” is not a unique member, it is present in “Date”, “Ship Date” and “Delivery Date”. The result returned is for “Date”. As explained above SSAS would return the first occurrence it finds, which in case turned out to be in “Date”.
Apart from being ambiguous, this approach is inefficient. SSAS has to search the member in the entire Cube. Therefore it is advisable to use the original addressing method i.e. [Dimension].[Hierarchy].[Level].[member]
Lets try another query.

select
[Measures].[Internet Sales Amount]
on rows,
[Date].[Calendar Year].&[2013]
on columns
from
[Adventure Works]

The above query accessed date’s CY 2013, however there was a difference in how it was addressed. Earlier we were addressing it using the format
“[Date].[Calendar Year].[CY 2013]”. This addressing is based on “Member Caption” or name of the member. Now we addressed it using the “UniqueName” or the key. The key of a member should always be checked from the SSAS db, you should never base your application logic on predicting its value.
What is difference between a member being accessed by key or by name
Lets try another query.

select
[Customer].[City].[Berlin]
on rows,
[Measures].[Internet Sales Amount]
on columns
from
[Adventure Works]

The query is pretty simple, however when we take a look at the top left hand we see around five occurrences of “Berlin”. So how exactly did the query got resolved, as explained earlier, it returned the first occurrence of”Berlin” that it found.
Lets try another query

select
[Customer].[City].&[Berlin]&[HH]
on rows,
[Measures].[Internet Sales Amount]
on columns
from
[Adventure Works]

In the above query we accessed “Berlin” using the key value. This resulted in a different Berlin being returned.
It is safer to access a member using its key value rather then name to avoid ambiguous referencing.
Up till now we have been accessing a single member. What if we want more than one member.
Lets try another query.

select
[Measures].[Internet Sales Amount]
on rows,
{
[Date].[Calendar Year].[CY 2012],
[Date].[Calendar Year].&[2014]
}
on columns
from
[Adventure Works]

In the above query, we referred two members(we used both key based addressing and name based addressing). Notice the “,” in between them and the encapsulating “{}”.
To access multiple members of the same hierarchy, we separate them using “,” and encapsulated them within “{}”
The order of the members in the query will determine their order in the result. Try swapping their position in the above query.
What if we need to access a range of members, if that range comprises of multiple members typing it in the above format will be rather cumbersome.
Lets try another query

select
[Measures].[Internet Sales Amount]
on rows,
{
[Date].[Calendar Year].&[2011]:
[Date].[Calendar Year].&[2014]
}
on columns
from
[Adventure Works]

In the above query we queried all members between 2011 and 2014 and including the boundary members. Here the members are returned in their default order(as they exist in their database). It doesn’t matter which member you put before the “:” and which member you put after it. Try swapping the values in the above query.
To access a range of members, we use “:” between the first member of the range and the last member, encapsulating them between “{}”.
What if we want to access all members of a hierarchy. One way would be to address it using the : format, putting the first member before the : and the last member after it. However there is a better way of doing it.
Lets try another query.

select
[Measures].[Internet Sales Amount]
on rows,
{
[Date].[Calendar Year].[Calendar Year].members
}
on columns
from
[Adventure Works]

In the above query we returned all the members of “Calendar Year” attribute using the “members” function. The members function returns all the members for that particular Hierarchy/level in default order. However if you observe closely, you will notice that there is an additional “Calendar Year”, where did this additional “Calendar Year” come from? This is the name of the level. Each attribute hierarchy has a singular level, which is of the same name as the attribute hierarchy. The question arises why to add a level within an attribute hierarchy. The benefit of Level will be highlighted when we discuss USER Hierarchy. For now just understand that each attribute hierarchy has at least one level.
Lets try another query

select
[Measures].[Internet Sales Amount]
on rows,
{
[Date].[Calendar Year].members
}
on columns
from
[Adventure Works]

In the above query we removed the level part of the hierarchy and used the .member function directly on the hierarchy part. Notice the result contains the “All Period” member. By default each hierarchy has a “All” member in it. It returns the sum for all underlying members.
Lets try another query

select
[Measures].[Internet Sales Amount]
on rows,
{
[Date].[Calendar Year].children
}
on columns
from
[Adventure Works]

In the above query we replaced the “members” function with “children” function. The children function returns all the children of a particular hierarchy in their default order. We will discuss more about members function and children function when we discuss UserHierarchies

Summary

In this topic we explored the following concepts.

  1. The default way to access a member is [Dimension].[Hierarchy].[Level].member
  2. We can refer a member just by its name, but this will result in a Cube wide search for that member. The first match will be returned. Which in case of duplicate members will be an issue.
  3. A member may be accessed by its name or key, referring by key is safer to avoid ambiguous reference.
  4. To access multiple members within a hierarchy we separate them by a comma “,” and enclose them within “{}”
  5. To access a range of members in their default order we mention the first member and the last member with a colon “:” in between them.
  6. The members function returns all members of hierarchy or level in their default order
  7. The children function return all children of a hierarchy in their default order.