MDX to include aliases in result

Post Reply
andy8888
Posts: 4
Joined: Tue Apr 27, 2021 4:09 pm
OLAP Product: TM1 Planning Analytics
Version: 2.0.9.4
Excel Version: 2102

MDX to include aliases in result

Post by andy8888 » Wed Apr 28, 2021 7:36 am

Hi all,

I'm attempting to formulate some MDX in such a way as to return aliases rather than member captions.

I've found two issues around this subject on the tm1py github repo, but not much else!
https://github.com/cubewise-code/TM1py- ... /issues/28
https://github.com/cubewise-code/tm1py- ... /issues/31

Unfortunately, I just can't seem to figure it out from the information provided in those issues.


For more context... I'm using the PAX API (v 2.0.57 on 64 bit Excel) to call an ExecuteMDX command to create a cellset.
By default, the member names that are returned are the captions. Instead, I would like for a specific attribute/alias to be returned.

From the looks of the information presented in those github issues, it looks as though I need to look at using "WITH MEMBER" to try to achieve this, but I just can't figure out the syntax. Unfortunately I don't have access to the sample planning model, so can't just do as Marius suggests and try running his sample code there and then adapting.

Marius uses this in his example:
WITH MEMBER [plan_time].[AccountClass] AS
[}ElementAttributes_plan_chart_of_accounts].([}ElementAttributes_plan_chart_of_accounts].[AccountClass])

When I try to adapt this for my dimension, I just receive an error saying that the MDX is invalid.

My dimension is called ABC-Product. It has no hierarchies, other than the default (ABC-Product). I would like it's alias "Reporting" to be returned instead of "Caption".


My current MDX is as follows:

SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
ON 0,

[ABC-Period].[ABC-Period].[PER_1]

ON 1

FROM [ABC-MyCube]

WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)


I think I need to change it to something like the following:

WITH MEMBER [ABC-Product].[Caption] AS
[}ElementAttributes_ABC-Product].([}ElementAttributes_ABC-Product].[Reporting])

SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
ON 0,

[ABC-Period].[ABC-Period].[PER_1]

ON 1

FROM [ABC-MyCube]

WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)

Any advice would be very much appreciated. Thank you in advance!

User avatar
gtonkin
MVP
Posts: 913
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX to include aliases in result

Post by gtonkin » Wed Apr 28, 2021 11:03 am

Possibly off on a tangent but have you tried using the member properties in the query - something like the syntax below:
[Customer].CurrentMember.Properties("Country")

andy8888
Posts: 4
Joined: Tue Apr 27, 2021 4:09 pm
OLAP Product: TM1 Planning Analytics
Version: 2.0.9.4
Excel Version: 2102

Re: MDX to include aliases in result

Post by andy8888 » Wed Apr 28, 2021 9:10 pm

Hi, thanks for the idea. I haven't tried that. To be honest, I'm not sure how / where that would fit into my MDX. Please could you explain how that would fit together?
Thanks

User avatar
gtonkin
MVP
Posts: 913
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX to include aliases in result

Post by gtonkin » Thu Apr 29, 2021 2:44 am

Hi Andy, cannot explain how it would fit in, was just a wild idea of referencing the alias using the properties. Have not done enough cube view selections to be of more help.
The only other thing that comes to mind is with PAW 2.0.63 there is an MDX button for the exploration - maybe if you can create the view, clicking the button will give you something useful. Another stab in the dark.

andy8888
Posts: 4
Joined: Tue Apr 27, 2021 4:09 pm
OLAP Product: TM1 Planning Analytics
Version: 2.0.9.4
Excel Version: 2102

Re: MDX to include aliases in result

Post by andy8888 » Thu Apr 29, 2021 11:53 am

Thanks, yes that's the method I'm using to get the original MDX. It does show some additional lines that refer to the selected alias, but when run via execute MDX it seems to ignore these instructions.

I'm sure there must be a way some how!!

burnstripe
Posts: 32
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX to include aliases in result

Post by burnstripe » Thu Apr 29, 2021 11:14 pm

https://docs.microsoft.com/en-us/analys ... llversions

Have a read of this, it should help.

Your with member statement is creating a calculated member/set to use in your select query. So the calculated member / set should be specified within your select statement

So instead of this

WITH MEMBER [ABC-Product].[Caption] AS
[}ElementAttributes_ABC-Product].([}ElementAttributes_ABC-Product].[Reporting])

SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}

The selection of prod_1 and prod_2 needs to go after the AS in the With member statement. And the new calculated member/set you're creating (which you've called [ABC-Product].[Caption] needs to go after the select

Select { [ABC-Product].[Caption] }
....

That's my take on it. I'll try out the syntax tomorrow when I have a pc in front of me :)

burnstripe
Posts: 32
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX to include aliases in result

Post by burnstripe » Sat May 01, 2021 10:07 am

Taken a different approach but this should give you what you desire

SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
DIMENSION PROPERTIES
[ABC-Product].[Reporting]
ON 0,

[ABC-Period].[ABC-Period].[PER_1]

ON 1

FROM [ABC-MyCube]

WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)

If you have the GO_NEW_STORES sample database then you can break down this working syntax for your own use
SELECT
NON EMPTY
{ Tm1FilterByLevel ( {Tm1SubsetAll([Month])} , 0 )} *
{ [Exchange Rate].[Exchange Rate] }
DIMENSION PROPERTIES
[Month].[Mois_FR], [Month].[startdate], [Month].[enddate]
ON ROWS,
{ [Local Currency].[CAD], [Local Currency].[EUR] } ON COLUMNS
FROM [Exchange Rates]

It displays:
Column 1: Month Alias called Mois_Fr
Column 2: Month Attribute startdate
Column 3: Month Attribute enddate
Column 4: Displays measure value [Local Currency].[CAD]
Column 5: Displays measure value [Local Currency].[EUR]
Rows show All leaf months

The dimension properties allows use to specify what aliases/attributes you wish to display from the set you've selected.

Wim Gielis
MVP
Posts: 2689
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX to include aliases in result

Post by Wim Gielis » Sun May 02, 2021 9:02 am

Burnstripe, thank you for providing working syntax. It isn’t the easiest subject.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

andy8888
Posts: 4
Joined: Tue Apr 27, 2021 4:09 pm
OLAP Product: TM1 Planning Analytics
Version: 2.0.9.4
Excel Version: 2102

Re: MDX to include aliases in result

Post by andy8888 » Wed May 05, 2021 7:45 am

Thank you all for your contributions. I can see how that will work for creating views, but unfortunately I can't get it to return the aliases when using the API. The cellset axes always seem to just contain the member caption. Any ideas?

burnstripe
Posts: 32
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX to include aliases in result

Post by burnstripe » Wed May 05, 2021 6:31 pm

Sorry I've not had much experience with api (still relatively new to it myself), but the mdx is what's required, it might be you need to specify which alias when viewing the array.

There's some useful documentation which can be found here.
https://ibm.github.io/paxapi/#refreshbook

It would help us if you could show what coding you have to return.

It looks like the execute mdx returns a result that can be queried in the same way as a json. In the link there's a section "Common View Specification Schema" with mention of alias, perhaps you can specify what property/alias to return.

burnstripe
Posts: 32
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX to include aliases in result

Post by burnstripe » Wed May 05, 2021 7:40 pm

Stumbled across an old forum on the subject

Have a read
viewtopic.php?t=234

Adjust the Mdx as I suggested earlier and then try something along the lines of this to return the alias instead

oCellset.Axis(1).Positions(intPosition).Members(0).Properties(0).Value

Post Reply