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

Post Reply
MarenC
Posts: 20
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

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

Post by MarenC » Fri Aug 02, 2019 10:17 am

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
Posts: 4
Joined: Thu Aug 02, 2018 9:13 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: Perspective

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

Post by Napshot » Fri Aug 02, 2019 10:59 am

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

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

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

Post by PavoGa » Fri Aug 02, 2019 12:29 pm

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.
Ty
Cleveland, TN

MarenC
Posts: 20
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

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

Post by MarenC » Fri Aug 02, 2019 1:16 pm

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
MVP
Posts: 2109
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Fri Aug 02, 2019 1:19 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

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

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

Post by PavoGa » Fri Aug 02, 2019 2:40 pm

MarenC wrote:
Fri Aug 02, 2019 1:16 pm
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.
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 2109
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Fri Aug 02, 2019 2:43 pm

PavoGa wrote:
Fri Aug 02, 2019 2:40 pm
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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

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

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

Post by PavoGa » Fri Aug 02, 2019 2:45 pm

Wim Gielis wrote:
Fri Aug 02, 2019 2:43 pm
PavoGa wrote:
Fri Aug 02, 2019 2:40 pm
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...
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 2109
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Fri Aug 02, 2019 3:22 pm

PavoGa wrote:
Fri Aug 02, 2019 2:45 pm
:) 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 ;-)
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

MarenC
Posts: 20
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

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

Post by MarenC » Fri Aug 02, 2019 3:39 pm

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!

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

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

Post by PavoGa » Fri Aug 02, 2019 6:39 pm

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.
Ty
Cleveland, TN

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

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

Post by PavoGa » Fri Aug 02, 2019 6:41 pm

MarenC wrote:
Fri Aug 02, 2019 3:39 pm
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.
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 2109
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Sat Aug 03, 2019 7:21 am

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

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

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

Post by PavoGa » Mon Aug 05, 2019 12:20 pm

Wim Gielis wrote:
Sat Aug 03, 2019 7:21 am
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.
Ty
Cleveland, TN

MarenC
Posts: 20
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

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

Post by MarenC » Mon Aug 05, 2019 4:08 pm

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

Post Reply