MDX filter on cube values for different dimension

Post Reply
User avatar
vovanenok
Posts: 64
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.3
Excel Version: 2010
Location: Toronto, Canada
Contact:

MDX filter on cube values for different dimension

Post by vovanenok » Fri Mar 16, 2018 1:56 pm

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

Willi
Posts: 143
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 » 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

User avatar
vovanenok
Posts: 64
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.3
Excel Version: 2010
Location: Toronto, Canada
Contact:

Re: MDX filter on cube values for different dimension

Post by vovanenok » Fri Mar 16, 2018 3:00 pm

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 ])
?

User avatar
PavoGa
Community Contributor
Posts: 239
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: MDX filter on cube values for different dimension

Post by PavoGa » Fri Mar 16, 2018 7:49 pm

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: 1588
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 » Fri Mar 16, 2018 8:12 pm

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

declanr
MVP
Posts: 1588
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 » 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
	)}

User avatar
PavoGa
Community Contributor
Posts: 239
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: MDX filter on cube values for different dimension

Post by PavoGa » Thu Mar 22, 2018 4:35 pm

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