need to export all elements and their parent hierarchy in multiple columns where there are multiple rollups in a dimension

[jlobeltest]

I'm sure this is a common need - but i'm struggling with a clean solution.

My dimension has multiple rollups, so elements have multiple parents.

I am trying to create Level attributes for each element - Element, Lev1, Lev1 description, Lev2, Lev2 description, etc. within a "rollup".

I could start with the top of the rollup and recurse down, is that the best approach? seems like the dimension service might have a function there already.

I know someone has done this but can't locate the code.

I found similar code in the samples,(e.g. dim_get.py) but not quite right - e.g. i can get each element with all of it's parents, but not the tree that leads up from the element to each top.

I'm trying code like this:

from TM1py import TM1Service
import pandas as pd

#from tm1py import TM1Service

PAURL = https://myserver.planning-analytics.cloud.ibm.com/tm1/api/QUBEPROD/
tm1user = "myid01_tm1_automation"
tm1pswd = "my password"

 

# Connect to the TM1 server
# connect to tm1 and create service named: tm1

with TM1Service(
                                           base_url=PAURL,
                                           user=tm1user,
                                           namespace="LDAP",
                                           password=tm1pswd,
                                           ssl=True,
                                           verify=True,
                                           async_requests_mode=True) as tm1:

    dimension_name = 'Project Structure'

    # Retrieve all elements in the dimension along with their hierarchy details
    dimension = tm1.dimensions.get(dimension_name)

     for hierarchy in dimension:

        # Dictionary to hold elements and their parents
        hierarchy_tree = {}

        # Iterate through all elements and their attributes

    #    for element in dimension['elements']: # this gave an error not subscriptable

        for element in hierarchy:
            element_name = [element.name]  # this works
            element_parents = element.parents  # Get all parent elements - THIS DOESN"T WORK - no attribute parents

            # Store the hierarchy

            hierarchy_tree[element_name] = element_parents

           # Now you can export or print the hierarchy tree

        for element, parents in hierarchy_tree.items():
            print(f"Element: {element}, Parents: {', '.join(parents)}")

Describe what's not working the way you expect

I'm novice to say the least in understanding the tm1py services, but have been banging my head for a day.

As noted in code comments, i'm getting errors like "object is not subscriptable", no parents attribute on element.

also tried element.['parents'] - i don't think element is a list.

Any guidance is appreciated.

This is the kind of output I want:

Element Element Desc Lev1 Lev1 Desc Lev2 Lev2 Desc Lev3 Lev3 Desc... 6300 Office Exp DirExp Direct Expenses TotExp Total lExpenses NetInc Net Income

Version

• TM1py most recent
• TM1 Server Version: not sure

Additional context errors are my coding, not tm1py

[jlobeltest]

I did find a solution to the multiple "hierarchies/rollups" within one dimension. I can use the bedrock procedure to create a new temporary directory from a given rollup. Then I ran a TI process on that new dimension, which recurses down the dimension building up a "parent(s)" string for each element. But perhaps easier to do in tm1py?

[MariusWirtz]

I think you can produce the desired outcome using two TM1py functions and combine the results in a post-processing step using pandas.

1. Retrieve the elements and the hierarchy (as level columns) using the tm1.elements.get_element_dataframe
2. Retrieve the Description Alias with the element names in a dictionary using the get_attribute_of_elements function

kinda like this

with TM1Service(**tm1_params) as tm1:
    df = tm1.elements.get_elements_dataframe(
        dimension_name="Account",
        hierarchy_name="Account")


    element_attributes = tm1.elements.get_attribute_of_elements(
        dimension_name="Account",
        hierarchy_name="Account",
        attribute="Full Description")

    # now use dataframe functions to create additional description column for each level

Does that make sense? Do you need a more complete example?

[jlobeltest]

Hello Marius - thanks so much for your response. I knew there was an easy answer - I see level005 through level000 in the export from get_elements_dataframe. That will do the trick.

As I see it, the alternate rollups will be shown and I can just filter for the rollup that I need. Have you thought about adding the levels left to right and right to left in case of a ragged dimension. Probably not necessary but I think there could be a use case.

The resulting dataframe just from get_all_elements_data seems to include the attributes too. So perhaps the element_attributes clause isn't needed.

[Cubewise-JoeCHK]

hope this reply is not too late... I think I built a basic recursion #1243 for this case, probably just adding few lines of code as you expecting to get the attribute value should be good.