Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

More Comments on attribute relations and calculations

Posted by thomasivarssonmalmo on March 8, 2008

My last blog entry was about attribut relations and I would only like to highlight some ideas that came across my mind after I wrote that.
 
I will follow two of Richard Tkatchuk’s examples in the same white paper that I have pointed to in the previous blog entry.
 
The first group of examples are about related attributes and how they affect each other, in calculated members, without the CurrentMember function.
 
City is related to state and here we place the state of California in the calculated member and the city New York in the slicer. In most cases of MDX Selects the slicer will control the values returned but not in this case.
 
With Member measures.x  as ([Measures].[Internet Sales Amount], [Customer].[State-Province].&[CA]&[US])
Select measures.x On columns
From [Adventure Works]
Where ([Customer].[City].&[New York]&[NY]);
 
The value that is returned in All Periods and  California(5 714 257.69). The calculated member will overwrite the slicer.
 
We can also put the city in the calculated member and the not related state in the slicer. New York have no sales in the Adventure Works cube so I have choosen another city in the same state with sales. We will get 50.94 that represents All Periods and Central Valley.
With Member measures.x  as ([Measures].[Internet Sales Amount], [Customer].[City].&[Central Valley]&[NY])
Select measures.x On columns
From [Adventure Works]
Where ([Customer].[State-Province].&[CA]&[US]);
 
You can try to modify the examples above and see what happens if you have a related city and state in the calculated member. The result will be the same and the calculated member will overwrite the slicer.
 
The point of the next example is to see what will happen if you put an unrelated attribute hierarchy in the slicer.
 
With Member measures.x  as ([Measures].[Internet Sales Amount],[Customer].[Country].&[United States])
Select {measures.x,[Measures].[Internet Sales Amount]} On columns
From [Adventure Works]
Where ([Customer].[Education].&[Bachelors]);
The result for the calculated member is 2 838,125.36(Bachelor, USA, All periods) and for the cube measure 9 900 142.76. The strange thing here is that the Internet Sales Amount for All Periods and  USA is 9 389 789.51? The calculated member is correct but the cube measure is not. (Edit: I was actually wrong here. The measure returns All Bachelors like Darren mentions in his comment). Thanks Darren!
 
 
The last part of examples are about the CurrentMember function and attribute relations.
 
In the first example the calculated member and the measure will return the same result for the city Seattle and All Periods.
With Member Measures.x as ([Measures].[Internet Sales Amount])
Select {Measures.x, [Measures].[Internet Sales Amount]} On Columns
From [Adventure Works]
Where [Customer].[City].&[Seattle]&[WA];
 
We use the same example but change the calculated member to use include an attribute hierarchy with the CurrentMember function.
The value that is returned(2 467 248,34) is for the whole state of Washington(and All Periods). The value for  Seattle,  All Periods, is 75 164,86
With Member Measures.x as ([Measures].[Internet Sales Amount],[Customer].[State-Province].CurrentMember), Format_String = "# ### ###.##"
Select {Measures.x, [Measures].[Internet Sales Amount]} On Columns
From [Adventure Works]
Where [Customer].[City].&[Seattle]&[WA];
 
What will happen if we use two attributes that are not related but from the same dimension?
 
With Member Measures.x as ([Measures].[Internet Sales Amount],[Customer].[Education].CurrentMember),Format_String = "# ### ###.##"
Select {Measures.x, [Measures].[Internet Sales Amount]} On Columns
From [Adventure Works]
Where [Customer].[City].&[Seattle]&[WA]
 
In this example, the value for Seattle , All periods(75 164,86), will be returned both for the calculated member and the measure.
 
If there is an attribute relation the CurrentMember function will overwrite the slicer but if there is no relation the slicer will control the values returned.
Advertisements

2 Responses to “More Comments on attribute relations and calculations”

  1. Darren said

    Hi Thomas, Where you say…
    "The result for the calculated member is 2 838,125.36(Bachelor, USA, All periods) and for the cube measure 9 900 142.76. The strange thing here is that the Internet Sales Amount for All Periods and  USA is 9 389 789.51? The calculated member is correct but the cube measure is not."
    … I think you will find that the cube measure is correct. The cube measure will be returning Internet Sales Amount for All Periods and All Countries, not Internet Sales Amount for All Periods and  USA. The USA restriction is only applied within the calculated measure.

  2. Thomas said

    Great comment Darren! I will have to check that!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: