Get Dimension Names and Order from MDX View

[raeldor]

Hi,

Is there any way to get a list of dimension names in the order they are selected from an MDX view? For native views there is titles, rows, columns. Is there anything that parses the MDX to provide something similar? Surely PAW must do this to present the MDX view with the dimensions in the correct position and order.

Thanks!

[MariusWirtz]

Hi @raeldor,

If someone could write a python function to determine the "composition" of dimensions over the axes based on an MDX query that would be great.

Currently, we don't have anything in TM1py.
However, we have a function that can determine the composition based on a cell set!
The function works reliably but it has the downside that it needs to execute the MDX first. That means TM1 has to render the MDX/view to create a cell set. The cellset creation can be expensive in terms of performance.

mdx = """
SELECT
TM1SubsetAll([d1]) on 0,
TM1SubsetAll([d2]) on 1
FROM [c1]
"""
with TM1Service(**tm1params) as tm1:
    cellset_id = tm1.cells.create_cellset(mdx=mdx)
    composition = tm1.cells.extract_cellset_composition(cellset_id=cellset_id)

    print(composition)

[raeldor]

Thanks for the reply. In our case rendering that view would be extremely expensive. PAW must be doing this internally to display the view on the screen. Does the MDX alone even have enough information to describe what are titles vs rows vs columns? Is there external meta data somewhere in TM1 that describes this?

[raeldor]

I ended up writing a rudimentary MDX parser using lark to return the columns, rows and titles. I think it will probably only work for MDX from views defined inside PAW though, which is our use case.

[rclapp]

In theory, if MDXPy was used to generate the MDX then it should be quite simple as dimension/axis location is an explicit part of the query creation. From: Marius Wirtz ***@***.***> Reply-To: cubewise-code/tm1py ***@***.***> Date: Friday, June 7, 2024 at 10:15 AM To: cubewise-code/tm1py ***@***.***> Cc: Subscribed ***@***.***> Subject: Re: [cubewise-code/tm1py] Get Dimension Names and Order from MDX View (Discussion #1124) Hi @raeldor<https://github.com/raeldor>, If someone could write a python function to determine the "composition" of dimensions over the axes based on an MDX query that would be great. Currently, we don't have anything in TM1py. However, we have a function that can determine the composition based on a cell set! The function works reliably but it has the downside that it needs to execute the MDX first. That means TM1 has to render the MDX/view to create a cell set. The cellset creation can be expensive in terms of performance. mdx = """ SELECT TM1SubsetAll([d1]) on 0, TM1SubsetAll([d2]) on 1 FROM [c1] """ with TM1Service(**tm1params) as tm1: cellset_id = tm1.cells.create_cellset(mdx=mdx) composition = tm1.cells.extract_cellset_composition(cellset_id=cellset_id) print(composition) — Reply to this email directly, view it on GitHub<#1124 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AEK7GZW6J7HPP2OHM4Q246LZGG56RAVCNFSM6AAAAABI6XMDWCVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TOMBTGEYDK>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[rclapp]

Is it just dimension and hierarchy names and their locations, or is it actual lists of elements? From: Ray Price ***@***.***> Reply-To: cubewise-code/tm1py ***@***.***> Date: Friday, June 7, 2024 at 10:41 AM To: cubewise-code/tm1py ***@***.***> Cc: "Clapp, Ryan" ***@***.***>, Comment ***@***.***> Subject: Re: [cubewise-code/tm1py] Get Dimension Names and Order from MDX View (Discussion #1124) Thanks for the reply. In our case rendering that view would be extremely expensive. PAW must be doing this internally to display the view on the screen. Does the MDX alone even have enough information to describe what are titles vs rows vs columns? Is there external meta data somewhere in TM1 that describes this? — Reply to this email directly, view it on GitHub<#1124 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AEK7GZVCCUZYUHJVEP5TP6TZGHBA7AVCNFSM6AAAAABI6XMDWCVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TOMBTGQ4DA>. You are receiving this because you commented.Message ID: ***@***.***>

[raeldor]

It's dimension and hierarchy names, plus sets of elements by the looks of it.

[rclapp]

I looked at what is returned by TM1py and this seems to work for queries defined in MDXpy. What do you think? cubewise-code/mdxpy@master...rclapp:mdxpy:feature_composition From: Ray Price ***@***.***> Reply-To: cubewise-code/tm1py ***@***.***> Date: Friday, June 7, 2024 at 10:48 AM To: cubewise-code/tm1py ***@***.***> Cc: "Clapp, Ryan" ***@***.***>, Comment ***@***.***> Subject: Re: [cubewise-code/tm1py] Get Dimension Names and Order from MDX View (Discussion #1124) It's dimension and hierarchy names, plus sets of elements by the looks of it. — Reply to this email directly, view it on GitHub<#1124 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AEK7GZXCTWIMPBKNONCQ473ZGHB3DAVCNFSM6AAAAABI6XMDWCVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TOMBTGU3TK>. You are receiving this because you commented.Message ID: ***@***.***>

[raeldor]

Thank you. Does this work for views defined in PAW? I ended up writing a rudimentary MDX parser using lark to return the columns, rows and titles. I think it will probably only work for MDX from views defined inside PAW though, which is our use case.

[MariusWirtz]

Maybe open a PR to TM1py or MDXpy already and we can improve it over time collectively