MDX Subset has stopped retrieving elements

[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

[asutcliffe]

...

{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?

[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

[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

[rmackenzie]

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:

Code: Select all

[dimension].[something]

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.

[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

[asutcliffe]

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?

[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 =.

[asutcliffe]

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.

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).

[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..

[asutcliffe]

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.