Tm1py: PROPERTIES in MDX does not work

[yyzz1010]

Describe what did you try to do with TM1py
in cube view mdx query using PROPERTIES can show attribute name in column/row, but when using tm1py querying the same mdx, column/row name still showing principal name. is it possible for tm1y to show attribute name when querying cube view mdx?

Describe what's not working the way you expect
Didn't get the expected result? Describe:

1. I've got a mdx like this:

  NON EMPTY 
   {[Currency].[Currency].[1],[Currency].[Currency].[2]} 
   PROPERTIES [Currency].[Currency].[SALES_DESC_EN]  ON COLUMNS , 
  NON EMPTY 
   {[Year Month].[Year Month].[2015^2015-01],[Year Month].[Year Month].[2015^2015-02]} 
  ON ROWS 
FROM [Ex Rate Table] 

1. in cube view, column names are showing "SALES_DESC_EN" attribute
   

2. but in tm1py still showing principal name
   

[MariusWirtz]

The execute_mdx_dataframe_shaped function doesn't support the PROPERTIES syntax. Currently, it is simply ignored.
IMO this is something we should add to the function. I understand that in Arc the first property is displayed as the "Column Header". Is that how you would like it to work in the TM1py function as well?

For the time being, if you want to see attribute values on the column headers, you can do a small additional query like this:

from TM1py import TM1Service

with TM1Service(address="", port=12354, user="admin", password="apple", ssl=True) as tm1:
    mdx = """
    SELECT
    {Tm1SubsetAll([d1])} ON ROWS,
    {[d2].[e1], [d2].[e2], [d2].[e3]} ON COLUMNS
    FROM [c1]
    """

    df = tm1.cells.execute_mdx_dataframe_shaped(mdx)

    mdx = """
    SELECT
    {[}ElementAttributes_d2].[Alias]} ON ROWS,
    {[d2].[e1], [d2].[e2], [d2].[e3]} ON COLUMNS
    FROM [}ElementAttributes_d2]
    """
    values = list(tm1.cells.execute_mdx_values(mdx))

    df.columns = ["d1"] + values

    print(df)

[yyzz1010]

yes for workaround would be querying the }ElementAttributes cube and replacing the dataframe header... yes it would be nice if this can be done inside tm1py functions! as usually principal name is just a code but actually user wants to see the alias/attribute name.

[rclapp]

@MariusWirtz This is one thing that stopped working from v1.4 to v1.5. The content endpoint handled attributes but the new cellset parsing does not. I am trying to figure out a fix and I have enlisted Hubert to help me out. Just in my initial research I have found 2 potential solutions, neither of which I like very much. Since the server returns all attributes if none are specified, this becomes quite difficult

1) Augment the the MDX queries that are passed to the cellservice: We could add DIMENSION PROPERTIES MEMBER_NAME any axis that does not have a PROPERTIES statement. This would result in only the desired properties being returned and make building the return objects easier.

2) Prior to posting the MDX, we could parse it to find which properties should be included in the response object.

[MariusWirtz]

@rclapp thanks for raising the issue. I agree this is something we need to take care of in TM1py.
I hope we don't have to parse or augment the MDX.

Perhaps we can just leave it to the user. Like in the execute_mdx_dataframe or the execute_mdx_dataframe_shaped function we could add an optional boolean argument like display_attribute that controls if we use the element name in the data frame or the first attribute (from the response JSON).

Obviously, the user would have to specify the PROPERTIES in the MDX correctly and TM1py would have to retrieve the Attributes as part of the element properties and build the data frame accordingly.

The downside of this approach is that if the user does not specify the PROPERTIES in the MDX all attributes are retrieved and it gets really slow.

from TM1py import TM1Service

with TM1Service(address="", port=12354, user="admin", password="apple", ssl=True) as tm1:
    mdx = """
    SELECT
    {Tm1SubsetAll([d1])} PROPERTIES [d1].[Attribute Something] ON 0,
    {Tm1SubsetAll([d2])} ON 1
    FROM [c1]
    """

    data = tm1.cells.execute_mdx_dataframe(mdx, display_attribute=True)

[rclapp]

Agree.

Today, this works if attributes are introduced as a calculated member, maybe we just use that as our preferred path.

I'll update more as my analysis progresses.

Sent from my mobile phone

On Feb 1, 2021 5:28 AM, Marius Wirtz [e-mail] wrote:

@rclapphttps://github.com/rclapp thanks for raising the issue. I agree this is something we need to take care of in TM1py.
I hope we don't have to parse or augment the MDX.

Perhaps we can just leave it to the user. Like in the execute_mdx_dataframe or the execute_mdx_dataframe_shaped function we could add an optional boolean argument like display_attribute that controls if we use the element name in the data frame or the first attribute (from the response JSON).

Obviously, the user would have to specify the PROPERTIES in the MDX correctly and TM1py would have to retrieve the Attributes as part of the element properties and build the data frame accordingly.

The downside of this approach is that if the user does not specify the PROPERTIES in the MDX all attributes are retrieved and it gets really slow.

from TM1py import TM1Service

with TM1Service(address="", port=12354, user="admin", password="apple", ssl=True) as tm1:
mdx = """
SELECT
{Tm1SubsetAll([d1])} PROPERTIES [d1].[Attribute Something] ON 0,
{Tm1SubsetAll([d2])} ON 1
FROM [c1]
"""

data = tm1.cells.execute_mdx_dataframe(mdx, display_attribute=True)

-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/cubewise-code/tm1py/issues/470#issuecomment-770856458, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEK7GZXGB5GBJ43TQVCDE2DS42T6ZANCNFSM4WRKVWZQ.

[zsoltmoravcsik]

Guys, I am not a python expert but here is my 2 cents. we develop our Js
framework and several times we got the conclusion handle attributes in
calculated members much cleaner then somehow do work around with notations
or parsing.

On 2021. Feb 1., Mon at 18:58, Ryan Clapp [e-mail] wrote:

Agree.

Today, this works if attributes are introduced as a calculated member,
maybe we just use that as our preferred path.

I'll update more as my analysis progresses.

Sent from my mobile phone

On Feb 1, 2021 5:28 AM, Marius Wirtz [e-mail] wrote:

@rclapphttps://github.com/rclapp thanks for raising the issue. I agree
this is something we need to take care of in TM1py.
I hope we don't have to parse or augment the MDX.

Perhaps we can just leave it to the user. Like in the
execute_mdx_dataframe or the execute_mdx_dataframe_shaped function we could
add an optional boolean argument like display_attribute that controls if we
use the element name in the data frame or the first attribute (from the
response JSON).

Obviously, the user would have to specify the PROPERTIES in the MDX
correctly and TM1py would have to retrieve the Attributes as part of the
element properties and build the data frame accordingly.

The downside of this approach is that if the user does not specify the
PROPERTIES in the MDX all attributes are retrieved and it gets really slow.

from TM1py import TM1Service

with TM1Service(address="", port=12354, user="admin", password="apple",
ssl=True) as tm1:
mdx = """
SELECT
{Tm1SubsetAll([d1])} PROPERTIES [d1].[Attribute Something] ON 0,
{Tm1SubsetAll([d2])} ON 1
FROM [c1]
"""

data = tm1.cells.execute_mdx_dataframe(mdx, display_attribute=True)

-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<
https://github.com/cubewise-code/tm1py/issues/470#issuecomment-770856458>,
or unsubscribe<
https://github.com/notifications/unsubscribe-auth/AEK7GZXGB5GBJ43TQVCDE2DS42T6ZANCNFSM4WRKVWZQ

.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/cubewise-code/tm1py/issues/470#issuecomment-771041315,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AHNEK3DXUR4J25RM3O7UN5LS43TUPANCNFSM4WRKVWZQ
.

[rclapp]

Thanks Zsolt I agree.

The more I dig into this the more I realize that the Dimension Properties notation is not the best place to specify attributes anymore. It only helped when using the Watson Analytics connector and the content endpoint.

[MariusWirtz]

I wrote a first draft implementation of how we could support it in a way that leaves the TM1py user responsible.
https://github.com/cubewise-code/tm1py/pull/479

@rclapp
What do you think?

@yyzz1010
If we added the display_attribute to the execute_mdx_shaped function, would that satisfy your requirement?

[MariusWirtz]

Thanks Zsolt I agree. The more I dig into this the more I realize that the Dimension Properties notation is not the best place to specify attributes anymore. It only helped when using the Watson Analytics connector and the content endpoint.

I also used the Calculated Members to retrieve attributes together with data in my previous projects.
I heart rumors though that it is slower compared to using PROPERTIES. I will try to get some figures on this one.

[MariusWirtz]

Hi @yyzz1010,

the execute_mdx_dataframe_shaped function now supports MDX PROPERTIES.

Please upgrade to the feature branch and test if it works as expected:
pip install https://github.com/cubewise-code/tm1py/archive/feature/support-mdx-properties.zip --upgrade

Make sure you pass display_attribute=True to the function call and specify properties for each dimension.
If you want to see the element name for a dimension make sure you write PROPERTIES MEMBER_NAME in the MDX

from TM1py import TM1Service

with TM1Service(address="", port=12354, user="admin", password="apple", ssl=True) as tm1:
    mdx = """
    SELECT
    {Tm1SubsetAll([d2])} PROPERTIES [d2].[Number] ON 0,
    {Tm1SubsetAll([d1])} PROPERTIES [d1].[Attribute Something] ON 1
    FROM [c1]
    """

    data = tm1.cells.execute_mdx_dataframe_shaped(mdx, display_attribute=True)

    print(data.head())

[yyzz1010]

the display_attribute functionality working nicely! Thank you very much! @MariusWirtz