Page 1 of 1
MDX filter on cube values for different dimension
Posted: Fri Mar 16, 2018 1:56 pm
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
Re: MDX filter on cube values for different dimension
Posted: Fri Mar 16, 2018 2:40 pm
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
Re: MDX filter on cube values for different dimension
Posted: Fri Mar 16, 2018 3:00 pm
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 ])
?
Re: MDX filter on cube values for different dimension
Posted: Fri Mar 16, 2018 7:49 pm
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
Re: MDX filter on cube values for different dimension
Posted: Fri Mar 16, 2018 8:12 pm
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)}
)}
Re: MDX filter on cube values for different dimension
Posted: Mon Mar 19, 2018 11:26 am
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
)}
Re: MDX filter on cube values for different dimension
Posted: Thu Mar 22, 2018 4:35 pm
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.