Execute_MDX function when some elements in query might not exist in TM1? Does TM1Py have a DIMIX function?

[FHoile]

Hi guys. I'm just getting into TM1Py and really enjoying it.

SHORT VERSION OF QUESTION:

I just wanted to ask... does TM1Py have a DIMIX or HierarchyElementExists function equivalent?

CONTEXT OF QUESTION:

I'm trying to replicate TI data tab logic in my Python. I have a list of about 1600 items in my code already. On each item in the list I need to replicate a couple of CellGetS functions to read existing values for said item from TM1. The values of these reads will decide what CellPutS value I then post back to TM1 for that item.

I'm trying to write a best practice solution to do this. Rather than fire hundreds of tm1.cells.write requests at the server, I was going to fire a single tm1.cubes.cells.execute_mdx request and then have Python examine the returned Dictionary object for all the CellGet information.

The issue I have with this approach is some of the Items in my MDX might not have been created in TM1 yet. And if they don't exist, it is NOT this process's place to create them! Running my MDX with these items hard-coded in the query obviously results in an MDX error. Unfortunately the dimension in question has about 2 million (!!) elements in it, so I can't realistically put all elements on rows and do an MDX filter function.

What I need is a way ahead of time to tell if any of my items don't exist in TM1. Then I can remove those items from my MDX query before I send it.

I did toy with a tm1.dimensions.get(myDimension) function, thinking if I bring the entire dimension in then I can loop on my list and remove the ones not found in Python's copy of the dimension structure. But again, with 2 million elements, it's not at all practical to do this.

SOLUTION I WILL PROBABLY GO WITH (BUT IS THERE A BETTER WAY??):

What I think I will do is just a python for loop (simulating a TI's data tab). Within each loop I will place try / except / else blocks and put individual tm1.cells.write requests in there. I think that would get the job done.

But I just wanted to ask if there's any advice for doing this a better way.




Thanks in advance!!

[FHoile]

EDIT: After some more searching I see I can use:

if tm1.dimensions.hierarchies.elements.exists( 'myDim', 'myHier', 'myElem' ):
    sMdxDim += f"[{sDim}].[{sHier}].[{sElem}]," 

So I guess I've answered my own question. I'll try using that and carry on with my Execute_MDX approach.

Am curious if that's the way others would do it too as best practice with minimal overhead on TM1 server.

[MariusWirtz]

Hi @FHoile,

when you need to read data for a list of elements and you are not sure if they exist in TM1 I think there are two good solutions:

1. TM1IGNORE_BADTUPLES

In your MDX query, you can use the TM1IGNORE_BADTUPLES keyword on the axis so that TM1 doesn't raise an error when you provide invalid element names.

2. Determine not existing elements using a set operation

When you do this check you need to make sure to take into account that TM1 is case and space insensitive on element names and that alias values can be used to reference elements.

The code below covers all those edge cases. The set is case and space-insensitive and the get_all_element_identifiers retrieves a set of all element names and alias values

from TM1py import TM1Service
from TM1py.Utils import CaseAndSpaceInsensitiveSet

tm1_local_params = {
    'base_url': 'https://localhost:12354',
    'user': 'admin',
    'password': 'apple',
    "async_requests_mode": True
}

with TM1Service(**tm1_local_params) as tm1:
    products = CaseAndSpaceInsensitiveSet(["Model 3", "Model S", "Model Y", "Model X"])

    existing_product_identifiers = tm1.elements.get_all_element_identifiers(
        dimension_name="Product",
        hierarchy_name="Product")

    new_products = products - existing_product_identifiers
    for new_product in new_products:
        print(new_product)

Does that answer your question?

[FHoile]

Thanks Marius! Yes, I'd read somewhere that an argument like TM1IGNORE_BADTUPLES existed, as a parameter, but couldn't remember if I'd dreamed it or not. I finished my code last night using the elements.exists loop and it's fast and accurate. But I'll revisit my code today and use TM1IGNORE_BADTUPLES instead to make it faster and cleaner. As mentioned, I'm just getting started with TM1Py, but I'm really having fun with it!