Bug in CurrentMember.Name

94 views
Skip to first unread message

Wilhe...@hotmail.com

unread,
Sep 21, 2006, 7:01:37 PM9/21/06
to
Why does CurrentMember.Name in some circumstances return the name of
the member which is NOT the current member? This occurs when two
hierarchies of the same dimension are crossjoined in the query like
this

WITH
MEMBER [Measures].[Reseller Bank] AS 'LTRIM([Reseller].[Reseller
Bank].CurrentMember.Name)'
MEMBER [Measures].[Reseller Order Frequency] AS
'TRIM([Reseller].[Reseller Order Frequency].CurrentMember.Name)'
SELECT {([Measures].[Reseller Sales Amount]), ([Measures].[Reseller
Bank]), ([Measures].[Reseller Order Frequency])} ON COLUMNS,
NON EMPTY {FILTER({[Reseller].[Reseller Bank].[All Resellers],
DESCENDANTS([Reseller].[Reseller Bank].[All
Resellers],[Reseller].[Reseller Bank].[Reseller Name],SELF_AND_BEFORE)}
* {[Reseller].[Reseller Order Frequency].[All Resellers]},
[Measures].[Reseller Sales Amount] <> 0 )} ON ROWS
FROM [Adventure Works]

The result is fine for combinations scoming from the upper level
members of Reseller Bank hierarchy but for combinations from the bottom
level of Reseller Bank [Measures].[Reseller Order Frequency] will
contain something else than "All Resellers". As you can clearly see,
the member [Reseller].[Reseller Order Frequency].[All Resellers] is
explicitely selected in the query, so on every single row of the result
set [Measures].[Reseller Order Frequency] should contain value 'All
Results'.

This looks like a bug to me. Multiple hierarchies of a single
dimension should work as hierarchies of different dimensions do in MDX
queries. If a developer can't rely on this simple rule, then
application development will become quite a nightmare using SS2k5 and
MDX.

I hope someone can tell me the good news that there is some way to make
CurrentMember.Name work as it is supposed to work.

Many thanks
Will

Chris Webb

unread,
Sep 22, 2006, 6:40:02 AM9/22/06
to
Hi Wilhelm,

This isn't a bug, this is expected behaviour ('strong hierachies'). There's
a good article explaining this at
http://www.sqlserveranalysisservices.com/default.htm
called 'Attribute Relationships Explained'. It can be a bit confusing
sometimes, but the way I think you're expecting it to work was tried in the
beta phase for AS2005 and was even worse...

HTH,

Chris
--
Chris Webb, MVP
Analysis Services and MDX Consultancy: http://www.crossjoin.co.uk
Blog: http://cwebbbi.spaces.live.com/

·

unread,
Sep 25, 2006, 3:02:28 PM9/25/06
to
Thanks for the reply and support. I already thought I was going crazy.
Perhaps MS let some relational people on the project and they messed up the
good thing...

Returning to my problem, the current unexpected behaviour of CurrentMember
plays havoc with my reporting project. Does anyone know a workaround which
would give me the CurrentMember.Name according to the traditional
multidimensional principles? Is there an undocumented MDX function like
'RealCurrentMember'?

Will

"Chris Webb" <onlyforpostin...@crossjoin.co.uk> wrote in message
news:9F9CBA2B-7FDD-4499...@microsoft.com...
>I agree with you. The situation you've described in your email is fairly
> straightforward - I've come across some MDX recently where I really had no
> idea what was going on. The problem is, though, as I said that making
> hierarchies independent of each other leads to other problems - take a
> look
> at this MSDN white paper which details how MDX Script assignments worked
> in
> this way during the beta:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/imdxsmss05.asp
> Note also the overwhelmingly negative feedback rating at the end, which I
> think is more of a reflection on the concepts described rather than the
> quality of the paper itself. Someone needs to sit down and figure out a
> way
> for this to behave that 'just works'... I just wish I knew what that would
> involve!


>
> Chris
>
> --
> Chris Webb, MVP
> Analysis Services and MDX Consultancy: http://www.crossjoin.co.uk
> Blog: http://cwebbbi.spaces.live.com/
>
>

> "·" wrote:
>
>> It's most unexpected behaviour for me. After working over 20 years with
>> multidimensional analysis reading the article that you refer to makes me
>> feel sick. One basic rule of multidimensional analysis is that
>> hierarchies
>> of one dimension are independent of each others as if they were
>> hierarchies
>> of different dimensions. It appears that this rule has been violated in
>> AS2k5 so that the selection in one hierarchy can affect the selection in
>> another hierarchy.
>>
>> In the sample query I've explicitely specified that the current member of
>> the [Reseller].[Reseller Order Frequency] hierarchy is
>> [Reseller].[Reseller
>> Order Frequency].[All Resellers]. Implicitly overwriting this explicit
>> selection is just not on!
>>
>> Also, when the sample MDX query is run in SS Management Studio, the
>> member
>> caption column for Reseller Order Frequency hierarchy contains 'All
>> Resellers' for every row while the calculated measure Reseller Order
>> Frequency contains the corrupt data from CurrentMember.Name function.
>> Doesn't this go to show that even the programmers of SS Management Studio
>> think that the current member name is 'All Resellers' for every row? So
>> why
>> does CurrentMember.Name return something else than the name of the
>> current
>> member in cases where the other hierarchy is at the bottom level?
>>
>> Will
>>
>> "Chris Webb" <onlyforpostin...@crossjoin.co.uk> wrote in
>> message
>> news:8D5553FA-5009-4FF7...@microsoft.com...

Akshai Mirchandani [MS]

unread,
Sep 25, 2006, 3:09:13 PM9/25/06
to
I'd also suggest to keep in mind that it is an absolutely acceptable design
to simply create multiple dimensions for each hierarchy if you want your
hierarchies to be truly independent.

If you want an attribute-based model (which has proven to be natural for
most customers), then you get the behavioral change that you are referring
to. But it is simple enough to introduce the isolation between hierarchies
by splitting them into separate dimensions just like AS 2000 did.

Obviously Adventure Works is not designed that way because as a sample it is
intended to show the power of the attribute-based system. But if you
disagree with the behavior of that system then the option is always open.

I do agree that calculations in the attribute-space takes a little wrapping
your head around but once the basic rules are clear, I think it makes sense.

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Chris Webb" <onlyforpostin...@crossjoin.co.uk> wrote in message
news:9F9CBA2B-7FDD-4499...@microsoft.com...
>I agree with you. The situation you've described in your email is fairly
> straightforward - I've come across some MDX recently where I really had no
> idea what was going on. The problem is, though, as I said that making
> hierarchies independent of each other leads to other problems - take a
> look
> at this MSDN white paper which details how MDX Script assignments worked
> in
> this way during the beta:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/imdxsmss05.asp
> Note also the overwhelmingly negative feedback rating at the end, which I
> think is more of a reflection on the concepts described rather than the
> quality of the paper itself. Someone needs to sit down and figure out a
> way
> for this to behave that 'just works'... I just wish I knew what that would
> involve!
>

> Chris
>
> --
> Chris Webb, MVP
> Analysis Services and MDX Consultancy: http://www.crossjoin.co.uk
> Blog: http://cwebbbi.spaces.live.com/
>
>

> "·" wrote:
>
>> It's most unexpected behaviour for me. After working over 20 years with
>> multidimensional analysis reading the article that you refer to makes me
>> feel sick. One basic rule of multidimensional analysis is that
>> hierarchies
>> of one dimension are independent of each others as if they were
>> hierarchies
>> of different dimensions. It appears that this rule has been violated in
>> AS2k5 so that the selection in one hierarchy can affect the selection in
>> another hierarchy.
>>
>> In the sample query I've explicitely specified that the current member of

>> the [Reseller].[Reseller Order Frequency] hierarchy is
>> [Reseller].[Reseller


>> Order Frequency].[All Resellers]. Implicitly overwriting this explicit
>> selection is just not on!
>>
>> Also, when the sample MDX query is run in SS Management Studio, the
>> member
>> caption column for Reseller Order Frequency hierarchy contains 'All
>> Resellers' for every row while the calculated measure Reseller Order
>> Frequency contains the corrupt data from CurrentMember.Name function.
>> Doesn't this go to show that even the programmers of SS Management Studio
>> think that the current member name is 'All Resellers' for every row? So
>> why
>> does CurrentMember.Name return something else than the name of the
>> current
>> member in cases where the other hierarchy is at the bottom level?
>>
>> Will
>>
>> "Chris Webb" <onlyforpostin...@crossjoin.co.uk> wrote in
>> message
>> news:8D5553FA-5009-4FF7...@microsoft.com...

·

unread,
Sep 25, 2006, 6:41:55 AM9/25/06
to
It's most unexpected behaviour for me. After working over 20 years with
multidimensional analysis reading the article that you refer to makes me
feel sick. One basic rule of multidimensional analysis is that hierarchies
of one dimension are independent of each others as if they were hierarchies
of different dimensions. It appears that this rule has been violated in
AS2k5 so that the selection in one hierarchy can affect the selection in
another hierarchy.

In the sample query I've explicitely specified that the current member of

the [Reseller].[Reseller Order Frequency] hierarchy is [Reseller].[Reseller

Order Frequency].[All Resellers]. Implicitly overwriting this explicit
selection is just not on!

Also, when the sample MDX query is run in SS Management Studio, the member
caption column for Reseller Order Frequency hierarchy contains 'All
Resellers' for every row while the calculated measure Reseller Order
Frequency contains the corrupt data from CurrentMember.Name function.
Doesn't this go to show that even the programmers of SS Management Studio
think that the current member name is 'All Resellers' for every row? So why
does CurrentMember.Name return something else than the name of the current
member in cases where the other hierarchy is at the bottom level?

Will

"Chris Webb" <onlyforpostin...@crossjoin.co.uk> wrote in message
news:8D5553FA-5009-4FF7...@microsoft.com...

Chris Webb

unread,
Sep 25, 2006, 8:29:02 AM9/25/06
to
I agree with you. The situation you've described in your email is fairly
straightforward - I've come across some MDX recently where I really had no
idea what was going on. The problem is, though, as I said that making
hierarchies independent of each other leads to other problems - take a look
at this MSDN white paper which details how MDX Script assignments worked in
this way during the beta:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/imdxsmss05.asp
Note also the overwhelmingly negative feedback rating at the end, which I
think is more of a reflection on the concepts described rather than the
quality of the paper itself. Someone needs to sit down and figure out a way
for this to behave that 'just works'... I just wish I knew what that would
involve!

Chris

--
Chris Webb, MVP
Analysis Services and MDX Consultancy: http://www.crossjoin.co.uk
Blog: http://cwebbbi.spaces.live.com/

·

unread,
Sep 25, 2006, 3:54:05 PM9/25/06
to
It is not an acceptable option for me to ask my customers to change their
MSAS cubes to get my MSAS and MSRS based solution working in correct manner
just because MS decided to deviate from the traditional multidimensional
principles. Do you have any other workaround to offer to get the
'attribute-based system' to imitate a true multidimensional system?

Will

"Akshai Mirchandani [MS]" <aks...@online.microsoft.com> wrote in message
news:%23vxtXYN...@TK2MSFTNGP03.phx.gbl...

Wilhelm

unread,
Sep 26, 2006, 12:49:02 AM9/26/06
to
I've been trying to find a way to get the attributes of the real current
member but it appears that your implementation is pretty watertight, ie
there is no way to get the real current member if the other hierarchy member
at the bottom level has overwritten it. Please, add ASAP a new function
like CurrentHierarchyPosition to the product to make it again possible to
build multidimensional browsing applications based on the query results
containing multiple hierarchies of one dimension.

PS. To be consistent, why did you not implement the same idea of returning
the attributes of the overwriting member also in ADOMD?


"Akshai Mirchandani [MS]" <aks...@online.microsoft.com> wrote in message
news:%23vxtXYN...@TK2MSFTNGP03.phx.gbl...

·

unread,
Sep 26, 2006, 2:47:26 PM9/26/06
to
While I was trying to simplify the sample MDX which was included in the
first mail of this thread, I came up with this one:

WITH MEMBER [Measures].[Reseller Order Frequency] AS

'TRIM([Reseller].[Reseller Order Frequency].CurrentMember.Name)'

SELECT {([Measures].[Reseller Sales Amount]), ([Measures].[Reseller Order
Frequency])} ON COLUMNS,

{[Reseller].[Reseller Order Frequency].[All Resellers],
[Reseller].[Reseller Order Frequency].[Annual],
[Reseller].[Reseller Order Frequency].[Reseller Name].&[238]} on ROWS

FROM [Adventure Works]

WHERE ([Reseller].[Reseller Bank].[Reseller Name].&[238])

and the results are:

Reseller Sales Amount Reseller Order Frequency
All Resellers $80,450,596.98 All Resellers
Annual $6,756,166.18 Annual
A Great Bicycle Company $9,055.29 A Great Bicycle Company

To my satisfaction, the attribute overwrite has not hit in this case, ie you
get 'All Resellers', 'Annual' and 'A Great Bicycle Company' as the result
from the calculated member. But look at the numbers! The WHERE clause has
no effect whatsoever! In fact, you get the same result without the WHERE
clause.

Moving the [Reseller].[Reseller Bank].[Reseller Name].&[238] from the WHERE
clause to a crossjoin with the ROWS dimension gives you the numbers you
expect but then the calculated member gets mucked up by the attribute
overwrite.

In the light of these results, MSAS2k5 may be an 'attribute-based system',
but it surely isn't a multidimensional one any more. Does anyone know
whether MS have any plans to come back to the multidimensional OLAP
marketplace any time soon ;)

Will

PS. If you try to run my MDX against AdWorks, you may need to change
'Reseller Name' to just 'Reseller'. There seem to be two versions of the
database around.


"Akshai Mirchandani [MS]" <aks...@online.microsoft.com> wrote in message
news:%23vxtXYN...@TK2MSFTNGP03.phx.gbl...

Akshai Mirchandani [MS]

unread,
Sep 26, 2006, 10:02:03 PM9/26/06
to
If your customers truly want the behavior you are asking for, then they
would put their hierarchies in separate dimensions. But if they want the
attribute-based calculation model, then they will expect the results that
are returned today. Keep in mind, that the customer cubes are going to have
their own calculations and those calculations are probably going to want the
new behavior.

Yes, there are some interesting cases that return results that may not be
intuitive at first glance, but in your own words, the model is
multi-dimensional -- not multi-hierarchical. As soon as you truly introduce
multiple hierarchies into a single dimension, I would argue that a model
where each hierarchy is a separate dimension is no longer a useful paradigm
and simply makes the most sense to treat them the way AS 2005 does. AS 2005
allows both models by allowing you to add multiple dimensions if you want
the AS 2000 behavior.

And there is a very low chance of adding a function like
CurrentHierarchyPosition -- this information is just not available in the
attribute model. An overwrite gets rid of that information. It may be
possible to define that some attributes should not be automatically updated
when attributes related to them change -- but that would have to be defined
in the model and not something that a query generator would use.

Thanks,
Akshai

--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"·" <Wilhe...@nospam.hotmail.com> wrote in message
news:4518...@news.orcon.net.nz...

Wilhelm

unread,
Sep 27, 2006, 1:55:23 AM9/27/06
to
Thank you Akshai for taking an interest in this issue as it is very important to us. 
 
I was doing some testing with Excel 2003 and 2007 Pivot tables.  In 2007 you get this:
Reseller Order Frequency Camping and Sports Store
Reseller Sales Amount Calendar Year
Bank Name Reseller Name CY 2003 CY 2004
Reserve Security Camping and Sports Store $194,133.16 $149,216.63
Reserve Security Total $194,133.16 $149,216.63
Grand Total $194,133.16 $149,216.63
 
while in 2003 you get this:
 

Reseller Order Frequency

Camping and Sports Store

 

 

 

 

 

 

Reseller Sales Amount

 

Calendar Year

 

Bank Name

Reseller Name

CY 2003

CY 2004

Reserve Security

Camping and Sports Store

194133.1555

149216.628

Reserve Security Total

 

4390086.129

2246361.967

Grand Total

 

32202669.43

16038062.6

 
Obviously also MS Excel developers have had difficulties understanding the new behaviour as they've made this change to 2007.
 
It's a shame if you've lost the track of the correct position in the hierarchy.  I was expecting that it would have been easy implement something like CurrentHierarchyPosition.  After all, ADOMD somehow still knows what the correct coordinates are to return the correct captions etc.  I'm still wondering why you weren't consistent and return the properties of the overwriting attribute in ADOMD too.
 
Thanks,
Will
 
 
"Akshai Mirchandani [MS]" <aks...@online.microsoft.com> wrote in message news:%2307gtjd...@TK2MSFTNGP03.phx.gbl...

·

unread,
Sep 27, 2006, 4:09:36 PM9/27/06
to
Perhaps I don't get an answer to the question about ADOMD because thinking through the consequences of returning the properties of the overwriting attribute in ADOMD (as opposed to returning the properties of the original member) makes everyone realize how catastrophical the consequences are.  And they are the same consequences which now affect developers relying on getting correct property information about the current member from CurrentMember function.
 
Will

Wilhelm

unread,
Sep 28, 2006, 6:47:16 PM9/28/06
to
Actually you don't even need to change the version of Excel.  You can get different results in different Pivot tables on a single spreadsheet:
 
Product Categories Bike Racks
Sales Amount      
Category Subcategory Model Name Total
Accessories Bike Racks Hitch Rack - 4-Bike 237096
  Bike Racks Total   237096
Accessories Total     1272058
Grand Total     109809274
Product Categories Bike Racks
Sales Amount    
Product Line Model Name Total
Accessory Hitch Rack - 4-Bike 237096
Accessory Total   237096
Grand Total   237096
 
Reply all
Reply to author
Forward
0 new messages