Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PROPERTIES in MDX does not work #470

Closed
karenwky opened this issue Jan 25, 2021 · 11 comments
Closed

PROPERTIES in MDX does not work #470

karenwky opened this issue Jan 25, 2021 · 11 comments
Labels

Comments

@karenwky
Copy link
Contributor

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:
SELECT 
  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
    image

  2. but in tm1py still showing principal name
    image

@MariusWirtz
Copy link
Collaborator

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)

@karenwky
Copy link
Contributor Author

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
Copy link
Collaborator

rclapp commented Feb 1, 2021

@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
Copy link
Collaborator

@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
Copy link
Collaborator

rclapp commented Feb 1, 2021 via email

@zsoltmoravcsik
Copy link

zsoltmoravcsik commented Feb 1, 2021 via email

@rclapp
Copy link
Collaborator

rclapp commented Feb 1, 2021 via email

MariusWirtz added a commit that referenced this issue Feb 3, 2021
use `display_attribute=True` and user PROPERTIES in MDX query

Related to #470
@MariusWirtz
Copy link
Collaborator

I wrote a first draft implementation of how we could support it in a way that leaves the TM1py user responsible.
#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
Copy link
Collaborator

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
Copy link
Collaborator

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())

@karenwky
Copy link
Contributor Author

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

MariusWirtz added a commit that referenced this issue Feb 16, 2021
use `display_attribute=True` and user PROPERTIES in MDX query

Related to #470
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

4 participants