Enhancements: 2 Functions to retrieve multiple parents from an element

[adytry]

get_direct_parents_of_element retrieves ALL direct parents of an element of the next higher level in a dimension

get_parents_tree_of_element loops the function get_direct_parents_of_element until the top level of the dimension and creates a tree containing all multiple parents of a respective element or element list

[adscheevel]

How would the return of get_direct_parents_of_element differ from what get_parents returns?

[adytry]

From my understanding get_parents returns list of only ONE direct parent in each level along the dimension, isn’t that correct? The newly added function would return all direct parents in case there is more than one, so multiple parents and also the multiple parents of those ones.

[adytry]

Sorry, just checked, you are right - it's already covered by get_parents. But is the second function also covered somehow? I could adjust it to use get_parents...

[MariusWirtz]

Yes. I think we can use the existing get_parents function to get the immediate parents.

Regarding the MDX, it might be more efficient to make use of the ASCENDATS function and call it on each member unique name (e.g. [Product].[red^product A]). So it would be only one MDX query per member.

here is a prototype. I hope this helps :)

from TM1py import TM1Service

tm1params = {...}

tm1 = TM1Service(**tm1params)

dimension_name = "_a"
hierarchy_name = "_a"
element_name = "e1"

# determine direct parents
parents = tm1.elements.get_parents(
    dimension_name=dimension_name,
    hierarchy_name=hierarchy_name,
    element_name=element_name)

# Use ascendants with each member unique name
for parent in parents:
    mdx = f"{{ASCENDANTS([{dimension_name}].[{hierarchy_name}].[{parent}^{element_name}])}}"

    ascendants = tm1.elements.execute_set_mdx(
        mdx=mdx,
        element_properties=None,
        member_properties=["Name"],
        parent_properties=None)

    print([ascendant[0]["Name"] for ascendant in ascendants])

Out

['e1', 'c2', 'c1']
['e1', 'c7', 'c6', 'c5', 'c4', 'c3']
['e1', 'c11', 'c10', 'c9', 'c8']

[rclapp]

You can also do this with a generate statement.

[adscheevel]

I like this prototype @MariusWirtz and you could combine the output into one list. This method will need additional work if we also want to recursively see the tree of every possible parent of every parent. An example would be to create a new consolidation "c12" and make "c5" a child of that so it has 2 parents: "c4" and "c12". Below is modified prototype to return same format but all all possible parents from each level (mdx is updated to not need to first grab immediate parents).

from TM1py import TM1Service

tm1params = {...}

tm1 = TM1Service(**tm1params)

dimension_name = "_a"
hierarchy_name = "_a"
element_name = "e1"

mdx = f'Generate( Filter( Distinct({{[{dimension_name}].[{hierarchy_name}].Members, \
    {{[{dimension_name}].[{hierarchy_name}].[{element_name}]}}}}), \
    [{dimension_name}].[{hierarchy_name}].CurrentMember.Name = "{element_name}"), \
    Ascendants([{dimension_name}].[{hierarchy_name}].CurrentMember))'

ascendants = tm1.elements.execute_set_mdx(
    mdx=mdx,
    element_properties=None,
    member_properties=["Name"],
    parent_properties=None)

ascendant_names = [ascendant[0]["Name"] for ascendant in ascendants]

[list(itertools.chain(*([element_name], list(group)))) for k, 
    group in
    itertools.groupby(ascendant_names, lambda x: x==element_name) if not k]

Out:

[['e1', 'c2', 'c1'],
 ['e1', 'c7', 'c6', 'c5', 'c12'],
 ['e1', 'c7', 'c6', 'c5', 'c4', 'c3'],
 ['e1', 'c11', 'c10', 'c9', 'c8']]

I was struggling yesterday to understand how @adytry plans to traverse the output of get_parents_tree_of_elements. The format in Marius's prototype makes more sense to me.

Here is the output of Marius's test dim using get_parents_tree_of_elements:

 ['c2', 'c7', 'c11'], 
 ['c2', ['c1'], ['c1', []]],
 ['c7', ['c6'], ['c6', ['c5'], ['c5', ['c4'], ['c4', ['c3'], ['c3', []]]]]],
 ['c11', ['c10'], ['c10', ['c9'], ['c9', ['c8'], ['c8', []]]]]]

[adytry]

Hi, I opened a new pull request. I deleted the get_direct_parents_of_element as it's already covered by "get_parents". I think the result of which Marius was speaking can be also reached by using the second function "get_parents_tree_of_element" in which I just replaced the above named function by "get_parents". Please let me know, thx!

[MariusWirtz]

Not sure about two things

1. the recursive approach

Each additional back and forth between py and TM1 slows down the execution. To optimize speed, we should try to minimize the number of calls of get_parents. Running the current get_parents_tree_of_element on the above dimension causes 12 executions of get_parents. Can we get that number down? The perfect solution would require only 1 MDX, IMO.

2. the output format

For the dimension below, I got this list. It's not obvious how to consume it, IMO. Maybe a dictionary could be more suitable to model the "tree'ish nature" of the TM1 hierarchy.

[['e1'], ['e1', ['c2', 'c7', 'c11'], ['c2', ['c1'], ['c1', []]], ['c7', ['c6'], ['c6', ['c5'], ['c5', ['c4'], ['c4', ['c3'], ['c3', []]]]]], ['c11', ['c10'], ['c10', ['c9'], ['c9', ['c8'], ['c8', []]]]]]]

[adytry]

1: I agree about the slowness but at the moment I have no clue how to do it in a mdx 2: this was my first approach but at the end I thought its easier to put it into a list. reading would be like: the list contains the element as first value and the respective parents as list as second and the "grand"parents in the third level and grandgrandparents in the fourth as so on until top level as nested list.