MDX filter on cube values for different dimension

Post Reply
User avatar
vovanenok
Posts: 88
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: Office 365
Location: Toronto, Canada
Contact:

MDX filter on cube values for different dimension

Post by vovanenok »

Hi all
To make it simple:
I have 2 dimensions: dim1 and dim2
I also have a settings cube1 containing just 2 dimensions:
- dim1
- cube1_measure dimensions

cube1_measure dimension contains just one element: value


In dim2 I have attribute attr_dim1_elm_name which contains dim1 element name related to dim2 element

I need to build an MDX statement to select all elements from dim2 which satisfy the condition:

Code: Select all

{FILTER(
    {TM1SUBSETALL( [dim2] )},
    [cube1].( [dim2].[attr_dim1_elm_name], [cub1_measure].[value] ) > 0
)}
Applying that MDX I get an error (which as usually is not informative, saying error at or near "[cub1_measure]"). I think I cannot use dim2 when referencing cube1, because dim2 is not from that cube

In that case I would need some MDX like:

Code: Select all

{FILTER(
    {TM1SUBSETALL( [dim2] )},
    [cube1].( [dim1].[     [dim2].[attr_dim1_elm_name]    ], [cube1_measure].[value] ) > 0
)}
Appreciate any help
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: MDX filter on cube values for different dimension

Post by Willi »

My first approach would be:

Code: Select all

{FILTER(
    {TM1SUBSETALL( [dim2] )},
    [cube1].( [dim1].[([}ElementAttribute_dim2].([dim2].CurrentMember, [}ElementAttributes_dim2].[attr_dim1_elm_name]))], [cube1_measure].[value] ) > 0
)}
untestetd especially with the brackets. Idea is to extract the Elementname as Element from the current Element from dim2
User avatar
vovanenok
Posts: 88
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: Office 365
Location: Toronto, Canada
Contact:

Re: MDX filter on cube values for different dimension

Post by vovanenok »

Willi wrote: Fri Mar 16, 2018 2:40 pm My first approach would be:

Code: Select all

{FILTER(
    {TM1SUBSETALL( [dim2] )},
    [cube1].( [dim1].[([}ElementAttribute_dim2].([dim2].CurrentMember, [}ElementAttributes_dim2].[attr_dim1_elm_name]))], [cube1_measure].[value] ) > 0
)}
untestetd especially with the brackets. Idea is to extract the Elementname as Element from the current Element from dim2
Thanks Willi , tried your solution, but it doesn't like nested attr_cube.value statement neither.
Can we use any expression inside: cube1.([dim1].[ DYNAMIC_ELM_NAME_EXPRESSION ])
?
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX filter on cube values for different dimension

Post by PavoGa »

You'll have to use StrToMember to do this. I've got code for it, but will have to get it to you a little later if no one else provides it.

Ty
Ty
Cleveland, TN
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: MDX filter on cube values for different dimension

Post by declanr »

The below code first filters to get all the elements in dim1 that match your criteria of exceeding a value of 1 in the cube.
It then uses the generate to do a while loop of those elements and for each of those; dim2 is filtered to where the attribute matches the dim1 element name.

Note that due to the loop style nature of this query if your first element and last element in dim2 have attributes that match the first element returned in the dim1 query - they will appear next to each other at the top of the end subset. So you may want to wrap some sorting around it.

Code: Select all

{GENERATE(
	{Filter({[dim1].members},[cub1].([cub1_measure].[value])>1)},
	{Filter({[dim2].members},[dim2].[attr_dim1_elm_name]=[dim1].CurrentMember.Name)}
	)}
Declan Rodger
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: MDX filter on cube values for different dimension

Post by declanr »

Noticed this again this morning and realised that PavoGa was on to something, the below will work and be more efficient that generate if your dimensions are large.

Code: Select all

{Filter(
	{[dim2].members},
	[cub1].( StrToMember ( "[dim1].[" + [dim2].[attr_dim1_elm_name] + "]" ),[cub1_measure].[value])>1
	)}
Declan Rodger
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX filter on cube values for different dimension

Post by PavoGa »

declanr wrote: Mon Mar 19, 2018 11:26 am Noticed this again this morning and realised that PavoGa was on to something, the below will work and be more efficient that generate if your dimensions are large.

Code: Select all

{Filter(
	{[dim2].members},
	[cub1].( StrToMember ( "[dim1].[" + [dim2].[attr_dim1_elm_name] + "]" ),[cub1_measure].[value])>1
	)}
Thanks, Declan. Just got in this morning from IBM Think in Las Vegas and did not even turn my laptop on while I was there. You hit the nail on the head, large dimensions will perform poorly with the GENERATE and filters (like a calendar dimension running back to 1900 or so) and this executes much faster by avoiding a filter query against the target dimension for each for element returned by the outer filter and Generate statement. This technique works with the GENERATE function as well, which is actually how I used it first.
Ty
Cleveland, TN
Post Reply