How to get elements from a subset directly from mdx ?

[kaleming]

Hi,

I've been using mdx to extract values from cubes, however it's not very clear to me the best way to extract elements from a dimension's subset.

The only way I found is:

First:

Create a subset:

def build_subset(tm1, dimension_name, Funcionários):
    x = "{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Itens Headcount] )}, 0)},[PLA_PlanoAcao].([Métricas Plano].[Responsável]) = 10)}"
    subset = Subset('teste', dimension_name=dimension_name, hierarchy_name=dimension_name, expression=x)
    if not tm1.dimensions.hierarchies.subsets.exists('teste', dimension_name, dimension_name, private=False):
        tm1.dimensions.hierarchies.subsets.create(subset, private=False)
    else:
        tm1.dimensions.hierarchies.subsets.update(subset, private=False)

Then apply:

    subset_cost_center = tm1.dimensions.subsets.get(subset_name=''teste'',
                                                    dimension_name='Status',
                                                    hierarchy_name=dimension_name,
                                                    private=False)

Is there a good way to do this directly from mdx ?

Something like:

 x = "{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Itens Headcount] )}, 0}

mdx_result = tm1.cubes.cells.execute_mdx(x)

[MariusWirtz]

You can use the execute_mdx method from the DimensionService:

with TM1Service(address=ADDRESS, port=PORT, user=USER, password=PASSWORD, ssl=SSL, decode_b64=DECODE_B64) as tm1:
    elements = tm1.dimensions.execute_mdx(dimension_name="Date", mdx="{Tm1SubsetAll([Date])}")

[kaleming]

Thank you very much Marius

[FHoile]

I've come this page very late obviously. Seems the solution has now been deprecated, because when I try this I get the following response from Python:

:16: DeprecationWarning: execute_mdx() will be deprecated; use ElementService execute_set_mdx. elements = tm1.dimensions.execute_mdx(dimension_name="myDimensionNameWasHere", mdx=sMdxDim)

The first run it never executed. On a second run it did execute and return a list of elements. But given the deprecation warning, I guess I better look for different syntax.

[FHoile]

So searching around, I'm using this syntax instead (and I wanted it returned as a dataFrame anyway, so this is good) and not getting the deprecation warning with this one: elementsDf = tm1.elements.get_elements_dataframe(dimension_name="myDimName", hierarchy_name="myDimName", elements=sMdxDim)

Strangely, if I try to utilise the use_blob parameter specified in the documentation... ie: if I instead run this... elementsDf = tm1.elements.get_elements_dataframe(dimension_name="myDimName", hierarchy_name="myDimName", elements=sMdxDim, use_blob=True) ...then it always returns an error. So I'm just going to leave out the use_blob argument for now I guess.

[MariusWirtz]

@FHoile Please post the error you receive when using the use_blob mode.

Is the error still raised when you upgrade TM1py to the current master branch on GitHub?

Pip install https://github.com/cubewise-code/tm1py/archive/refs/heads/master.zip --upgrade

[FHoile]

Hi @MariusWirtz ,

I'm not overly worried about the use_blob=True error at the moment, as the function still works if I omit that argument.

Side note: I do use that argument in other functions successfully. An example is this one which works fine:
tm1.cubes.cells.write_dataframe(cube_name=myCube, data=myDataFrame, increment=False, use_blob=True)

I'm sure it is just an old version issue. I'm overdue to upgrade TM1 itself. Haven't been in a rush to upgrade TM1Py itself as haven't wanted to risk any backwards-compatibility issues in scripts that are currently working daily in production.

On the dev box, where I'm writing all this, the versions I'm running on are:

• TM1 = 11.6.00004.2
• TM1Py = 1.11.2

If I get a spare moment (which won't be for a while), I might try creating a virtual python environment and upgrading TM1Py there as you suggested, and retesting. I definitely know upgrades are overdue!

---

Error Message In Python: d:\program files\python38\lib\site-packages\TM1py\Utils\Utils.py in wrapper(self, *args, *kwargs) 59 try: 60 import pandas ---> 61 return func(self, args, **kwargs) 62 except ImportError: 63 raise ImportError(f"Function '{func.name}' requires pandas")

d:\program files\python38\lib\site-packages\TM1py\Services\ElementService.py in get_elements_dataframe(self, dimension_name, hierarchy_name, elements, skip_consolidations, attributes, attribute_column_prefix, skip_parents, level_names, parent_attribute, skip_weights, use_blob, kwargs) 219 # responses are similar but not equivalent. Therefor only use execute_mdx_dataframe when use_blob=True 220 if use_blob: --> 221 df_data = cell_service.execute_mdx_dataframe(mdx, shaped=True, use_blob=True, kwargs) 222 else: 223 df_data = cell_service.execute_mdx_dataframe_shaped(mdx, **kwargs)

d:\program files\python38\lib\site-packages\TM1py\Utils\Utils.py in wrapper(self, *args, *kwargs) 59 try: 60 import pandas ---> 61 return func(self, args, **kwargs) 62 except ImportError: 63 raise ImportError(f"Function '{func.name}' requires pandas")

d:\program files\python38\lib\site-packages\TM1py\Services\CellService.py in execute_mdx_dataframe(self, mdx, top, skip, skip_zeros, skip_consolidated_cells, skip_rule_derived_cells, sandbox_name, include_attributes, use_iterative_json, use_compact_json, use_blob, shaped, **kwargs) 1984 1985 if use_blob: -> 1986 raw_csv = self.execute_mdx_csv( 1987 mdx=mdx, 1988 top=top,

d:\program files\python38\lib\site-packages\TM1py\Services\CellService.py in execute_mdx_csv(self, mdx, top, skip, skip_zeros, skip_consolidated_cells, skip_rule_derived_cells, csv_dialect, line_separator, value_separator, sandbox_name, include_attributes, use_iterative_json, use_compact_json, use_blob, **kwargs) 1824 raise ValueError("'line_separator' must be '\r\n' to leverage 'use_blob' feature") 1825 -> 1826 return self._execute_mdx_csv_use_blob( 1827 mdx=mdx, 1828 top=top,

d:\program files\python38\lib\site-packages\TM1py\Utils\Utils.py in wrapper(self, *args, *kwargs) 33 if not self.is_admin: 34 raise TM1pyNotAdminException(func.name) ---> 35 return func(self, args, **kwargs) 36 37 return wrapper

d:\program files\python38\lib\site-packages\TM1py\Services\CellService.py in _execute_mdx_csv_use_blob(self, mdx, top, skip, skip_zeros, skip_consolidated_cells, skip_rule_derived_cells, value_separator, cube_dimensions, sandbox_name, include_headers, quote_character, kwargs) 3781 success, status, error_log_file = process_service.execute_process_with_return(process, kwargs) 3782 if not success: -> 3783 raise RuntimeError( 3784 f"Failed writing to blob with TI. " 3785 f"Status: '{status}' log: '{error_log_file}'")

RuntimeError: Failed writing to blob with TI. Status: 'Aborted' log: 'TM1ProcessError_2024012503501814913536.log'

---

Error Message In TM1: Error: Prolog procedure line (0): Unable to open data source "}ElementAttributes_myDimensionNameHere".

[FHoile]

Also noted that the function omitting use_blob also fails, if the MDX is valid but returns no results.

It fails with a "key error" message and aborts the Python script. Not sure if this is expected behaviour or not?

I'll get around this by doing a CellGet check first to make sure records have been loaded first, and only run the tm1.elements.get_elements_dataframe command if the cellGet first indicates that there are records to return. Or maybe just place the call within a Try-Catch block.

[MariusWirtz]

Regarding

RuntimeError: Failed writing to blob with TI. Status: 'Aborted' log: 'TM1ProcessError_20240125035018_14913536_.log'
Error Message In TM1:
Error: Prolog procedure line (0): Unable to open data source "}ElementAttributes_myDimensionNameHere".

Is this a scenario where the }ElementAttributes_ cube doesn't exist for a dimension?

Regarding

the function omitting use_blob also fails, if the MDX is valid but returns no results.

I will look into this and try to reproduce

[MariusWirtz]

This should resolve the issues related to use_blob https://github.com/cubewise-code/tm1py/pull/1040

[FHoile]

Regarding

RuntimeError: Failed writing to blob with TI. Status: 'Aborted' log: 'TM1ProcessError_20240125035018_14913536_.log'
Error Message In TM1:
Error: Prolog procedure line (0): Unable to open data source "}ElementAttributes_myDimensionNameHere".

Is this a scenario where the }ElementAttributes_ cube doesn't exist for a dimension?

Regarding

the function omitting use_blob also fails, if the MDX is valid but returns no results.

I will look into this and try to reproduce

Hi Marius.
Correct, there is no attributes cube for that dimension. (And no scope to add one as the dimension is massive).

Thanks for looking into this and resolving. I have all my code working nicely without use_blob = true and with a try/except to deal with empty result sets. But if I get a chance I might look to pip update TM1Py to the latest version and retest without my work-arounds.