Page 1 of 1
MDX Dynamic Subset using an attribute in another dimension
Posted: Mon Nov 05, 2012 4:05 pm
by Ajay
Hi All
I'm trying, not successfully, to drive a subset in a time dimension from an attribute value stored against an element in another dimension.
So I have a dimension called "LIVEINFO", and it has three elements, "YEAR", "MONTH" and "PERIOD". Within this dimension, I have added a text attribute called "RUNTIME VARIABLE", and the attribute values per the elements listed are currently shown as:
ELEMENT RUNTIME VARIABLE
---------- -----------------------
YEAR 2012
MONTH OCT
PERIOD OCT-12
The RUNTIME VARIABLE is updated by the user or automatically.
In a small cube containing a dimension called P&L (showing a sumarised P&L down the side) and TIME (showing a three-month time period across the top), I need to create a cubeview containing a subset which allows for the three months to start with the attribute against PERIOD above (OCT) and count back a further two months in the TIME dimension, ie showing OCT, SEP and AUG.
How do I write the MDX for this in the TIME dimension ?
The idea is basically when the RUNTIME VARIABLE attribute is changed, the cubeview should update too.
Have any of you managed to do this ?
Sorry for not posting what I've tried so far.....embarrassing I fear
Cheers
Ajay
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Mon Nov 05, 2012 4:28 pm
by tomok
I can't help you without knowing what your TIME dimension looks like. Your RUNTIME VARIABLE thingy is confusing and it is not clear how the data in this attribute matches the TIME dimension.
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Mon Nov 05, 2012 4:48 pm
by Ajay
The elements within the TIME dimension are :
JAN-12
FEB-12
MAR-12
APR-12
MAY-12
JUN-12
JUL-12
AUG-12
SEP-12
OCT-12
NOV-12
DEC-12
The RUNTIME VARIABLE attribute against the LIVEINFO dimension is used to control a host of other processes and so simply provides a mechanism for processes to identify what YEAR, MONTH or PERIOD to process against.
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Tue Nov 06, 2012 4:44 am
by rmackenzie
Ajay wrote:I'm trying, not successfully, to drive a subset in a time dimension from an attribute value stored against an element in another dimension.
I think
reading this might help.
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Tue Nov 06, 2012 8:27 am
by lotsaram
rmackenzie wrote:Ajay wrote:I'm trying, not successfully, to drive a subset in a time dimension from an attribute value stored against an element in another dimension.
I think
reading this might help.
That trick would be useful only if the "LIVEINFO" dimension was also part of the cube which I seriously doubt. Based on what Ajay said (assuming all dimension and attribute names are exactly as quoted) then this should work.
Code: Select all
{FILTER(
{TM1SUBSETALL( [Time] )},
[Time].CurrentMember.Name = }ElementAttributes_LIVEINFO.([LIVEINFO].[Period], [}ElementAttributes_LIVEINFO].[RUNTIME VARIABLE])
)}
There are probably more elegant ways to do it directly with member properties or StrToMember but I am pretty sure this will do the trick.
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Wed Nov 07, 2012 3:31 pm
by Ajay
Thanks Lotsaram....much appreciated.
I can see what you're doing here but I keep getting syntax error messages whenever I update the dimension with this expression.
{FILTER
(
{TM1SUBSETALL( [Time] ) },
[Time].CurrentMember.Name = }ElementAttributes_LIVEINFO.( [LIVEINFO].[Period], [}ElementAttributes_LIVEINFO].[RUNTIME VARIABLE] )
)
}
Does the part in red need to be in square brackets ?
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Wed Nov 07, 2012 4:20 pm
by lotsaram
Normally I just use a SYS_... cube for this not attributes. I just tried this out and what the MDX compiler seems not to like is the curly bracket at the start of the cube name. Enclose the cube name in square brackets and it should be fine. Same just worked for me in my Period dimension.
[Time].CurrentMember.Name = [}ElementAttributes_LIVEINFO].( [LIVEINFO].[Period], [}ElementAttributes_LIVEINFO].[RUNTIME VARIABLE] )
Only thing to watch out for using this is that your string value must equal the element principal name, it can't be an alias value.
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Wed Nov 07, 2012 4:48 pm
by Ajay
Hi Lotsaram
That has worked a treat. It has bought back into the dimension the correct element.
I've now got "Oct-12" in the subset, how would I amend the MDX to also include the two elements which precede the element, ie "Sep-12" and "Aug-12" ?
Also the TM1 version I am using is 9.5.1, and I am sure I read somewhere that there is a limitation on the MDX length.
Can I use a PREVMEMBER or would you advise another approach, like using a bit of TI to retrieve the attribute value and create a non-MDX generated subset ! (I just didn't want another bit of TI that needed to be run).
Unfortunately I do need to keep the LIVEINFO dimension and it's attributes.
Ajay
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Wed Nov 07, 2012 5:40 pm
by tomok
The problem with the suggested approach is it only returns the exact month. If it were me I would add a numeric attribute to the TIME dimension called "Index" and given each month a number, with each successive month being 1 higher than the last. Then I would add an Index attribute to your LIVEINFO dimension. You could then write an MDX to filter the TIME dimension wherever the Index attribute for TIME was <= the Index value for RUNTIME VARIABLE and >= the INDEX value for RUNTIME VARIABLE minus two.
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Mon Nov 12, 2012 9:50 am
by Ajay
Many thanks for your help. Been away for a few days and returned to find business requirement has shifted to only show the current month, so approach suggested by Lotsaram implemented.
For my own benefit will also be trying out the other approach from Tomok - just in case requirements move back !!
Thanks guys for your help.
Ajay
Re: MDX Dynamic Subset using an attribute in another dimensi
Posted: Wed May 01, 2013 9:57 am
by AmbPin
Hello,
I came across this thread whilst looking for help on a similar challenge.
I have a simple Assets cube and have created an MDX filer to show just those assets that have been disposed of this month.
Code: Select all
Filter(
{
TM1FilterByLevel(
{ TM1SubsetAll( [Asset] ) }
, 0
)
}
, [Assets].( [Asset Type].[????]
, [Company].[????]
, [Department].[????]
, StrToMember(
"[Months].["
+ [SI].([SIM].[simMonth], [SIV].[Value] )
+ "]"
)
, [Asset Measures].[Disposed]
) > 0
)
I have a System Information cube [SI] where I have a rule calculated string that corresponds with the value for this month in my months dimension; this works just fine.
What I wanted to do was create a similar filter to show assets disposed of last month. I should say that I have done this by adding another entry in my SI cube for last month so the filer is almost exactly the same as above. However, initially I tried to do this reading a "PriorMonth" attribute from this months element in my months dimension. So far I have not been able to generate a statement that would get past the compiler. I though this might be a useful technique to know so would like to resolve it if possible.