Improve execution speed for distribution process

[TM1si]

I just started with TM1Py and I'm very satisfied with the functionality. I build a small process which dynamically creates a view by using subsets for each dimension.
For this source data I have a look in another cube for keys, lets say month. So for one record in source I get 12 values from the distribution cube. Now I write back to another cube the partly values of the one source value in relation to the sum of the 12 distribution values, e.g. 1/12 of the source value.
This works fine but slow.
My Question: How can I speed up the process?
When I get a record from the source view (lets assume I will get 10.000 records), I have to define a new view definition to get the corresponding values from the distribution cube. So if this cube has 5 dimensions, I have to define a Subset for this cube 10.000 (records) x 5 (dimensions) = 50.000 times.
After this I get only 12 records from this new created view, so it results in writing back 10.000 (source values) x 12 (distribution parts) = 120.000 destination records.
I'm writing it back by using this code value by value, to get informed, if something fails and if, when and where:

            try:
                tm1.cells.write(cube_name=actualCube, cellset_as_dict={tuple(dstElements): dstValue})

So how can I speed up with TM1PY functionality? I try to avoid unneccessary definitions, e.g. define a subset which is identical to the previous one, but it is still terribly slow.

[MariusWirtz]

When switching from TI to TM1py and Python you also need to change your approach slightly.

In Turbo Integrator, you can do individual cell-read cell-write operations (e.g., attribute value lookup) while looping through a view on a cell-by-cell basis. With TM1py, you must not do individual/small read and write operations

With TM1py, you need to approach the problem differently:

• first you need to read the entire source view in one operation (e.g. tm1.cells.execute_mdx_dataframe)
• Then you retrieve all potential lookup values (again tm1.cells.execute_mdx_dataframe or tm1.cells.execute_mdx)
• Then, you perform your calculation (e.g., a cost allocation) in Python! try to use vectorized pandas operations wherever you can. It's often very fast.
• Then you write back the results to a cube. Use tm1.cells.write with use_blob=True to speed up performance

If you follow this approach, there is a good chance that the py script is actually faster than the original TI script.

I hope this helps

[TM1si]

Ok. The source view I imported at once. No problem. I used that piece of code:

# Get all data defined by the subsets TM1Distr_...
def GetSubsetDefinedDataFromCube(tm1, cube_name, area):
    # Query data through MDX using standard subset name TM1Distr_...
    cube = tm1.cubes.get(cube_name)
    dimensionCount = len(cube.dimensions)
    # last dimension = measure dimension
    dimN = cube.dimensions[dimensionCount - 1]

    # Measure dimension for columns
    mdx = 'SELECT NON EMPTY {TM1SUBSETTOSET([' + dimN + '].[' + dimN + '],"TM1Distr_' + area + '")} DIMENSION PROPERTIES MEMBER_NAME ON COLUMNS, '
    # this option only once
    mdx = mdx + 'NON EMPTY '

    # all normal dimensions except the last one
    for index in range(dimensionCount - 2):
        dim = cube.dimensions[index]
        mdx = mdx + '{TM1SUBSETTOSET([' + dim + '].[' + dim + '],"TM1Distr_' + area + '")} * '

    # the last "normal" dimension
    dim = cube.dimensions[dimensionCount - 2]
    mdx = mdx + '{TM1SUBSETTOSET([' + dim + '].[' + dim + '],"TM1Distr_' + area + '")} DIMENSION PROPERTIES MEMBER_NAME ON ROWS '
    mdx = mdx + 'FROM [' + cube_name + ']'

    print("MDX to go: " + mdx)

    # get data
    # Useful options
    # element_unique_names = False
    # skip_cell_properties = True
    #data = tm1.cubes.cells.execute_mdx(mdx)
    try:
        data = tm1.cubes.cells.execute_mdx(mdx,element_unique_names = False, skip_cell_properties = True)
        return data

    except Exception as e:
        print(f"Fehler in ShowMDXOfView aufgetreten: {e}")
        print("MDX: " + mdx)
        return {}

But the distribution view is only defined after I have on record of the source. Is there a feature to export the entire distribution cube and then define Subset and build views based on the dataset "data" in Python itself?

To write back, should not be a problem, bc. I could collect them internally and then use one write statement. But when something goes wrong, e.g. a write attempt to a consolidated element or type mismatch or missing element, how can I manage this if I only get an "Error" for 120.000 records?