TM1py returns hierarchy nodes in wrong “level” fields

[carltoncheung]

Describe what did you try to do with TM1py
I am trying to get dimension data (with hierarchy) from TM1, to use in Power BI reports. The python script I used is:

with TM1Service(**config["tm1dev"]) as tm1:
    dimAccount = tm1.power_bi.get_member_properties(
        dimension_name="Accounts - AusG One",
        hierarchy_name="Accounts - AusG One",
        skip_consolidations=True
    )

Describe what's not working the way you expect
For each leaf node in the hierarchy, I expected each of its parent nodes to be returned in a field that corresponds to the level in the hierarchy the parent belongs to. E.g. the root node should be in "level000" field, the next level down should be in the "level001" field, and so on. This would allow me to easily filter data based on any hierarchy node in Power BI.

However it appears that get_member_properties returns the parent nodes in the wrong "levelNNN" fields. In the screenshot below, you can see that for each leaf node (account), it returns its parent in "level016", its parent's parent in "level015", and so on. But since the hierarchy is ragged, we end up with the root node (ARP_Statement of Financial Position) in various "levelNNN" fields (depending on how deep in the hierarchy the leaf node lives), instead of only in "level000".

The problem with this, is that for any given hierarchy node, it can appear in multiple "levelNNN" fields, instead of just appearing in the one level. And this makes it very difficult to filter data on any given hierarchy node.

Version

• TM1py 1.5.0
• TM1 Server Version: 11.x

Question
Is it possible to have get_member_properties populate the levelNNN fields in ascending order starting with the root node (ARP – Statement of Financial Position) in level000, followed by level001, and so on, until the leaf node is reached?

[MariusWirtz]

Hi @carltoncheung,

TM1py is just the messenger. It's forwarding the (MDX-) level names as they are defined in TM1.
Also, PowerBI expects the dataset to be in this kind of format (= element-name, level4, level3, level2, level1, level0) in order to be able to aggregate.

Is it possible to have get_member_properties populate the levelNNN fields in ascending order starting with the root node (ARP – Statement of Financial Position) in level000, followed by level001, and so on, until the leaf node is reached?

This wouldn't be understood by PBI, because PBI expects the element-name to be in one fixed column.
I think the core of the problem is that PBI is just not good at dealing with ragged dimensions.

Here are some alternative ideas

I'm not entirely sure if this is a viable suggestion for your situation, but you may have to break the dimension into consistent pieces.
Like you could bring in the dimension as multiple datasets, that are in itself consistent w.r.t. the levels.
So you could perhaps display the first few levels (e.g. level000 to level006) in an aggregated widget and retrieve and display more detailed views (e.g. LOB-INTINC) as individual datasets.

Perhaps you could also define custom attributes that reflect the structure that you would like to see in PBI. Then you could retrieve those attributes instead of the levels.

Perhaps when doing this kind of TM1-typical analysis over custom ragged dimensions you may want to use a different front-end.
I am sure you know about dedicated TM1 front ends like IBM PAW or Apliqo UX. Obvisouly you can work with ragged dimensions in those front-ends much more comfortably.

I hope this helps

Marius

[MariusWirtz]

Hi @carltoncheung,

I think there may be a better solution to dealing with ragged dimensions in PBI.
I'm curious to hear what you think. If you like the approach I could adjust the get_member_properties functions.

So in your example, in the first row, we would repeat the APR Statment of Financ... from level008 to level000.
Then it would like in this sample:

Cube

Dimension data set in PBI

Data widget

Would that suit your requirement?

EDIT: With this approach, the hierarchy is more in line with what we would expect (coming from TM1) but the numbers on the sub-nods are inconsistent. Like t1 can be 9, 4, and 15 at the same time. I suppose this is not a viable approach then.

[carltoncheung]

Hi @MariusWirtz ,

Thanks for taking the time to reply and for your suggestions. I really appreciate it!

Using the arc tool to test the TM1 APIs directly, we can see that the API follows the historical way that TM1 has labelled its hierarchy levels (level 0 = leaf node, and highest level is the top level root node):

However the TM1py function get_member_properties is not passing the hierarchy nodes into the correct levelNNN fields:

Screenshot above shows that it is treating the leaf node (105120 - Petty Cash) as level 17, putting the leaf's parent (ARP - Cash on Hand) into "level016", the leaf's parent's parent (ARP - Cash & Cash Equivalents) into "level015", and so on.

So there is definitely a discrepancy between the hierarchy level labels in TM1/API vs what TM1py function returns.

Assuming this discrepancy is resolved, it still doesn't solve the problem for Power BI and ragged hierarchies.

It appears that with Planning Analytics v2.x, TM1 is moving from the historical way of labelling hierarchy levels to the industry standard MDX way, which is to make the root node Level 0, and the leaf node the highest level number:
https://www.ibm.com/support/pages/new-hierarchy-level-behavior-planning-analytics-set-editor.

The good news is that Power BI can handle ragged hierarchies perfectly well, as long as the hierarchy levels are labelled according to industry standard MDX way - i.e. root = level 0. We have used Power BI for reporting SAP hierarchies for several years now, and because the SAP connector follows root = level 0, it works perfectly even for ragged hierarchies.

Now my question is, given that the function get_member_properties needs to be fixed (to address above discrepancy) anyway, and given that the primary purpose of the PowerBiService class is to support Power BI connectivity, do you think we could change this function to return hierarchy level names in line with industry standard MDX, i.e. root = level 0?

I had a quick look at the get_member_properties code, and I'm guessing the reason the function is putting hierarchy levels into the wrong fields is because the get_level_names function is returning level names in descending order by default:

Thanks again, and happy to have a conference call to discuss if required.

Kind regards,
Carlton.

[MariusWirtz]

Hi @carltoncheung,

thanks for your detailed answer. I appreciate the feedback on the PowerBI functionality.

With regard to ascending and descending levels, TM1py is currently bringing through the MDX levels.
I think we could make this dynamic and add an optional argument to the get_member_properties function to control if you want TM1-levels (ascending from leaves to root) or MDX-levels (ascending from root to leaves).
I will look into this!

The good news is that Power BI can handle ragged hierarchies perfectly well

I am happy to hear that! Let's change the functions so that it can fulfill its primary purpose to support Power BI connectivity.

do you think we could change this function to return hierarchy level names in line with industry standard MDX, i.e. root = level 0?

Please see my post above. #476 (comment)
t1 (the root of the hierarchy) is level000 in every line. Is this not what you are asking for?

I sent you a connection request on LinkedIn. Happy to continue this conversation on Microsoft Teams or Zoom.

[carltoncheung]

Hi @MariusWirtz

I think TM1py will bring back MDX levels (root=0) correctly, only if the hierarchy is balanced. If the hierarchy is ragged, like in my example above, the get_member_properties function doesn't return parents in the correct levelNNN fields.

Image below shows a ragged hierarchy where the leaf node (105120 - Petty Cash) is only 6 levels away from the root, but it is treated as level 17 because the hierarchy has other leaf nodes which are 17 levels away from the root.

The correct result would be that level005, level004, level003, level002, level001, level000 fields are populated with the parent nodes (and level016 - level006 fields are left blank).

Thanks,
Carlton.

[MariusWirtz]

Hi @carltoncheung,

I understand that the levels need to be aligned "on the right" in order for PBI to aggregate.
Please install the branch with pip and verify if the levels are now in the "right" fields.

pip install https://github.com/cubewise-code/tm1py/archive/issue/powerbi-levels.zip --upgrade

[carltoncheung]

Hi Marius,

Thanks a lot for the quick fix. The results look good. The hierarchy node levels are being put into the correct "levelNNN" fields according to the MDX standard (root = level 0).

There is only one small thing I've noticed. Previously the "Description" field would contain "ID - Description" (e.g. "105120 - Petty Cash"), for ALL members of the dimension:

Now, after the bug fix, the "Description" column is blank for all members, except those members that belong to the highest level of the hierarchy (i.e. level 17).

It appears the bug fix has "moved/shifted" the contents of the Description field into the highest "levelNNN" field:

I think to avoid confusion, it would be better to leave all the leaf nodes in the "Description" field (which is quite useful for reporting), and only have parent nodes in the "levelNNN" fields. So in the above example, I would expect level006 to be blank.

Kind regards,
Carlton

[MariusWirtz]

Hi @carltoncheung,

thanks for the catch. I shifted one column too much (in your case the last attribute: Description).

Please upgrade and test again.

[carltoncheung]

Hi Marius,

All fixed now. Thanks!

Happy to close this issue.

Kind regards,
Carlton.

[MariusWirtz]

Thanks