Page 1 of 1
MDX Subset has stopped retrieving elements
Posted: Fri Apr 20, 2012 10:36 am
by Ajay
Hi All,
Hope you can help with a strange problem
I have a dimension subset which selects the elements I need using an MDX statement. The statement merely pulls in, cost centres flagged with a certain attribute value, assigned to each cost centre...see below the statement which is looking for cost centres flagged with the value "B4" in the "Methodology b" attribute.
{FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Cost Centre].[Methodology B] = "B4")}
For some reason the subset is no longer retrieving the values, despite me being to able to manually select them using the Filter by/attribute command, from the EDIT menu.
What could be causing this ? and will a simple restart of the dataserver put things right ? do I need to restart the Admin Server or is there something else going on ?
Cheers
Ajay
Re: MDX Subset has stopped retrieving elements
Posted: Fri Apr 20, 2012 10:51 am
by asutcliffe
Ajay wrote:
...
{FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Cost Centre].[Methodology B] = "B4")}
For some reason the subset is no longer retrieving the values, despite me being to able to manually select them using the Filter by/attribute command, from the EDIT menu.
What could be causing this ? and will a simple restart of the dataserver put things right ? do I need to restart the Admin Server or is there something else going on ?
Cheers
Ajay
That MDX should work. If filter by attribute works, what happens if you do that and record a new expression and attach to a new subset?
Re: MDX Subset has stopped retrieving elements
Posted: Fri Apr 20, 2012 11:16 am
by Michel Zijlema
Hi Ajay,
Have you checked whether the expression is still actually there? Maybe accidentially added/deleted an element making the subset static?
Another thing to check is whether you're looking at the actual public subset and not at a (static) private subset with the same name.
Michel
Re: MDX Subset has stopped retrieving elements
Posted: Fri Apr 20, 2012 12:42 pm
by Ajay
Just found the issue.
Interestingly, a hierarchy had been built, this morning, within the dimension itself which used the attribute values exactly, to be the parents of the cost centres, so in my example below, lots of cost centres suddenly got a parent called "B4". I think the MDX has got a bit confused as I have now taken out the hierarchy and all of the MDX statements are now working.
Thanks for you help though.
Ajay
Re: MDX Subset has stopped retrieving elements
Posted: Thu Apr 26, 2012 10:17 pm
by rmackenzie
Ajay wrote:Interestingly, a hierarchy had been built, this morning, within the dimension itself which used the attribute values exactly, to be the parents of the cost centres, so in my example below, lots of cost centres suddenly got a parent called "B4". I think the MDX has got a bit confused as I have now taken out the hierarchy and all of the MDX statements are now working.
Part of the 'quirky' nature of TM1's MDX implementation is the following ambiguity:
Something can be an element, a subset, or an attribute depending on what context you're using the syntax...
If you apply a good naming convention system to your model then part of that should be trying to avoid having subsets and attributes as the same name as elements.
Re: MDX Subset has stopped retrieving elements
Posted: Mon May 21, 2012 10:36 am
by AmbPin
Hello,
I have just noticed that this is not working too.
If I do
Filter( {TM1SubsetAll( [Time Daily] ) }, [Time Daily].[Sage Alias] < "May_2012")
or
Filter( {TM1SubsetAll( [Time Daily] ) }, [Time Daily].[Sage Alias] > "May_2012")
These both return values but
Filter( {TM1SubsetAll( [Time Daily] ) }, [Time Daily].[Sage Alias] = "May_2012")
returns nothing although there is definitely a value to match
Re: MDX Subset has stopped retrieving elements
Posted: Mon May 21, 2012 11:18 am
by asutcliffe
AmbPin wrote:Hello,
I have just noticed that this is not working too.
If I do
Filter( {TM1SubsetAll( [Time Daily] ) }, [Time Daily].[Sage Alias] < "May_2012")
or
Filter( {TM1SubsetAll( [Time Daily] ) }, [Time Daily].[Sage Alias] > "May_2012")
These both return values but
Filter( {TM1SubsetAll( [Time Daily] ) }, [Time Daily].[Sage Alias] = "May_2012")
returns nothing although there is definitely a value to match
As with Ajay's example, this should work (if as you say, there is an element where the attribute "Sage Alias" is exactly "May_2012"). As per rmackenzie's suggestion, is there any ambiguity around what [Time Daily].[Sage Alias] means? I take it that the examples you've got working with less than and greater than return the expected elements?
Re: MDX Subset has stopped retrieving elements
Posted: Mon May 21, 2012 11:33 am
by AmbPin
Hello,
This was working fine before we applied FP2 to 9.5.2.
There is no ambiguity [Time Daily].[Sage Alias] is unique
I must admit I haven't checked the less or greater than values, just noticed that something gets returned. I would not want to use LT or GT but need to use =.
Re: MDX Subset has stopped retrieving elements
Posted: Mon May 21, 2012 11:43 am
by asutcliffe
AmbPin wrote:Hello,
This was working fine before we applied FP2 to 9.5.2.
Aha, sounds suspicious but we've not applied FP2, yet so can't shed any light.
AmbPin wrote:
There is no ambiguity [Time Daily].[Sage Alias] is unique
I must admit I haven't checked the less or greater than values, just noticed that something gets returned. I would not want to use LT or GT but need to use =.
Yeah, I too figured that given it was returning *something*, it was probably working. Assuming this MDX is used to drive a dynamic subset, I'd delete the subset and re-create it (but I'm guessing you've already tried that).
Re: MDX Subset has stopped retrieving elements
Posted: Tue May 22, 2012 10:54 am
by AmbPin
Hello,
I reworked my MDX statement and it is working now:-
Code: Select all
LastPeriods(
6,
TM1Member(
{
TM1FILTERBYPATTERN(
{ TM1DRILLDOWNMEMBER( { [Time Daily].[All Time] }, ALL, RECURSIVE )},
[System Information].([System Information Measures].[sysDate-Month], [System Information Values].[Value])
)
}.item(0)
, 0
)
)
I would prefer to have an equals rather than a pattern filter but hey-ho..
Re: MDX Subset has stopped retrieving elements
Posted: Tue May 22, 2012 12:08 pm
by asutcliffe
AmbPin wrote:Hello,
I reworked my MDX statement and it is working now:-
Code: Select all
LastPeriods(
6,
TM1Member(
{
TM1FILTERBYPATTERN(
{ TM1DRILLDOWNMEMBER( { [Time Daily].[All Time] }, ALL, RECURSIVE )},
[System Information].([System Information Measures].[sysDate-Month], [System Information Values].[Value])
)
}.item(0)
, 0
)
)
I would prefer to have an equals rather than a pattern filter but hey-ho..
I'm glad you got it working (I still don't understand why the original filter didn't work though).
FWIW, I might be tempted to build a subset like this via TI instead, especially if you've already got a monthly chore updating 'sysDate-Month'. I love playing with MDX but it's not always the easiest thing for support people to get their head around. I seem to remember LastPeriods having a few quirks too.