MDX to return elements that exists in another dimension, but as an alias

[MarenC]

Hi,

I was wondering if it's possible using mdx to return elements in one dimension that exists as an alias in another dimension? I know I can use other techniques for this but wanted to know if it could be done with MDX.

So for example Dimension A has elements El1 and El2 and Dimension B has elements Element1, Element2 and Element3.

In dimension A the alias of EL1 is Element1 and the alias of El2 is Element2.

Is it possible to create mdx in dimension B to just return Element1 and Element2 because only they exists in dimension A.

Maren

[Napshot]

Hey Maren
You can do it by adding an attribute to dimension B that checks for element in dimension A.

['Check'] =S: IF(DB('}ElementAttributes_DimensionA',!DimensionB,'AltName') @<> '', '1', '0');

And then do you MDX filter on that rule.

Not sure if you can do it by only MDX.

Morten

[PavoGa]

Yes, you can do this entirely in MDX using GENERATE and StrToMember.

For example:

Code: Select all

GENERATE( FILTER( TM1SUBSETALL( [dim2] ), LEN([dim2].[attribute]) > 0) 
    , {StrToMember("[dim1].[" +  [dim2].[attribute] + "]")});

One critical thing here. IF the value of the attribute is NOT a valid member of [dim1], some versions of PA will crash the server. We currently enjoy one of those versions, 11.3.00000.27. Of course, an attribute that validates the attribute is legit and using that as a filter prevent that. The LEN is for demonstration purposes.

In one of those, it may be safer to write a TI to build your subset.

[MarenC]

Thanks for the replies.

I am only needing this for debugging, so didn't want to go down the TI or new attribute route. I can just snapshot to excel and do lookups but thought MDX might be useful too.

PavoGa - in your mdx the attribute is in the same dimension as where the mdx is, but in my case the attribute is in the other dimension. Does this matter?

Please note: this could be academic as I tried a variation on your mdx and it said failed to compile and the server crashed!

[Wim Gielis]

For me, mixed feelings
With the latest 2.0.8, I couldn't get it to work but also the TM1 server did not crash.

[PavoGa]

Thanks for the replies.

PavoGa - in your mdx the attribute is in the same dimension as where the mdx is, but in my case the attribute is in the other dimension. Does this matter?

The query builds a subset on Dim1 using an attribute value of Dim2. Was that not what you were asking?

Please note: this could be academic as I tried a variation on your mdx and it said failed to compile and the server crashed!

As stated, in some versions, if a value is submitted to StrToMember that is bad, it will crash the server. The only sure way to avoid, AFAIK, is an attribute that confirms the attribute is valid and to use it in a filter to avoid submitting bad values.

[Wim Gielis]

The query builds a subset on Dim1 using an attribute value of Dim2. Was that not what you were asking?

A subset on dim2 whose values occur in an alias attribute on dim1.

[PavoGa]

The query builds a subset on Dim1 using an attribute value of Dim2. Was that not what you were asking?

A subset on dim2 whose values occur in an alias attribute on dim1.

I was hoping the OP would be able to use the example to figure out the principle...

[Wim Gielis]

I was hoping the OP would be able to use the example to figure out the principle...

I couldn't get it to work during the 5-10 minutes that I played with it. Maybe that was just too short to have a detailed look at it

[MarenC]

Hi,

I had read your mdx code as it needed to be put in dim2, I didn't realise that mdx could be put in dim1 (I assumed because the first part of the code referenced dim2 it had to be put in dim2).

I guess I have some way to go to reach your mdx knowledge!

But as I said, I did try a variation of your code, which was along the lines of working it out!

[PavoGa]

Okay, I see the potential source of the confusion. Yes, the code example I gave is designed to be executed in the subset editor for [Dim1]. In a TI, does not matter: SubsetCreateByMDX will produce a subset on Dim1.

I couldn't get it to work during the 5-10 minutes that I played with it. Maybe that was just too short to have a detailed look at it

Wim, if what I just said is not the problem, would need to see the example you're trying. I use this a good bit; not building from aliases or other attributes, but particularly in building subsets from mapping cubes.

[PavoGa]

Hi,

I had read your mdx code as it needed to be put in dim2, I didn't realise that mdx could be put in dim1 (I assumed because the first part of the code referenced dim2 it had to be put in dim2).

A tip: in a GENERATE, the second clause is the operative dimension in which one is creating the subset.

[Wim Gielis]

Thank you Ty, I got it working. I added this example to my MDX page.

So IBM should provide:
- some kind of 'on error resume next' in MDX
- a DIMIX kind of function in MDX. I noticed that Exists exists in MDX so maybe something along these lines.

[PavoGa]

Thank you Ty, I got it working. I added this example to my MDX page.

- a DIMIX kind of function in MDX. I noticed that Exists exists in MDX so maybe something along these lines.

Thought about the EXISTS function as well. Have not tried it, but I think that circles back around to the need to convert a string value to a member...

To avoid the server crash, I'm just adding DIMIX checks in an attribute or measure to prevent submitting a bad value to StrToMember.

[MarenC]

Thanks for the advice, especially the tip on the generate function, which seems more than a tip and more something fundamental!

But thanks anyway.

Maren