Can TM1py return Alias values rather than base member in MDX

[JoshLobel]

Hello,

I have been trying to populate a simple dataframe with }Client info on the rows. I have MDX that looks like this:

mdx = """SELECT
NON EMPTY
{TM1SUBSETALL([}Groups].[}Groups])}
ON COLUMNS ,
NON EMPTY
{TM1SUBSETALL([}Clients].[}Clients])}
ON ROWS
FROM [}ClientGroups] """

group_df= tm1.cubes.cells.execute_mdx_dataframe_pivot(mdx=mdx)

On PA on cloud with LDAP auth, }Client values are ugly. I have an alias called Email. I'd like that value to be in the rows.

I've tried syntax like this, which works in ARC, but doesn't use the alias in TM1py extract ( I tried to put the DIMENSION PROPERTIES in Bold):

mdx = """SELECT
NON EMPTY
{TM1SUBSETALL([}Groups].[}Groups])}
ON COLUMNS ,
NON EMPTY
{TM1SUBSETALL([}Clients].[}Clients])}
DIMENSION PROPERTIES [}Clients].[Email]
ON ROWS
FROM [}ClientGroups] """

group_df= tm1.cubes.cells.execute_mdx_dataframe_pivot(mdx=mdx)

This doesn't throw an error, but doesn't return alias either. I have also tried it without the word DIMENSION - just PROPERTIES[}Clients].[Email]

Am I missing something simple? Do I need to do a dataframe merge with an export of }ElementAttributes_}Clients?

Thanks for any advice.
Josh

[ascheevel]

edit: When I use your original mdx with execute_mdx_dataframe_shaped with argument display_attribute=True instead of execute_mdx_dataframe_pivot, I get the expected results. Might be an issue with the pivot function, but would need to look into it further.


original: You could use a WITH MEMBER statement before your SELECT and include the member in your columns set. That would return the email value in the 2nd column next to client name, but that isn't quite what you were asking for.

Code: Select all

mdx = """
WITH MEMBER [}Groups].[}Groups].[Email] AS [}Clients].[}Clients].CurrentMember.Properties("Email")
SELECT
NON EMPTY
{[}Groups].[}Groups].[Email], TM1SUBSETALL([}Groups].[}Groups])}
ON COLUMNS ,
NON EMPTY
{TM1SUBSETALL([}Clients].[}Clients])}
ON ROWS
FROM [}ClientGroups] """

[JoshLobel]

First of all, and not to clutter things further - sorry for posting this item in the Free and Open Source Tools. I misunderstood its purpose.

But second, now that I did, I just wanted to thank @asheevel for their response. using the execute_mdx_data_frame_shaped with the display_attribute=True did just what I wanted.

mdx = """SELECT
{
[}ElementAttributes_}Clients].[}ElementAttributes_}Clients].[Email],
[}ElementAttributes_}Clients].[}ElementAttributes_}Clients].[Firstname],
[}ElementAttributes_}Clients].[}ElementAttributes_}Clients].[MiddleInitial],
[}ElementAttributes_}Clients].[}ElementAttributes_}Clients].[Lastname],
[}ElementAttributes_}Clients].[}ElementAttributes_}Clients].[Supervisor],
[}ElementAttributes_}Clients].[}ElementAttributes_}Clients].[Maintenance Flag]
}
ON Columns ,
{ ORDER ( {TM1SUBSETALL([}Clients].[}Clients])}, [}Clients].CurrentMember.Properties('Username'),BASC ) }
DIMENSION PROPERTIES [}Clients].[UserName]
ON Rows
FROM [}ElementAttributes_}Clients] """

user_df = tm1.cubes.cells.execute_mdx_dataframe_shaped(mdx=mdx, display_attribute=True)

Note that I was also able to apply a sort on the Username alias in the ORDER phrase.

Thanks again for the tip, and for the broad functionality in the tm1py library.