Function cells.clear() can't deal with dimension hierarchies

[151N3]

Describe the bug

Given the example from clearing-cube-data-with-tm1py is quite convenient to use the function cells.clear() but if there is the need to clear some data from a special hierarchy the function won't work and successfully clears nothing.

To Reproduce
Lets say we have a cube Sales with 4 dimensions: product, year, month, age, category and the dimension category has more than one hierarchy and we want to clear the data from the some_category hiearchy.

from TM1py import TM1Service

with TM1Service(address="localhost", port=12354, user="admin", password="apple", ssl=True) as tm1:
    tm1.cells.clear(
        cube="Sales",
        product="[Product].[10]",
        year="[Year].[2023]",
        month="[Month].[11]")

It generates this MDX:

SELECT
NON EMPTY 
{[product].[10]} 
* {[Year].[2023]} 
* {[Month].[11]} 
* {TM1FILTERBYLEVEL({TM1SUBSETALL([age].[age])},0)} 
* {TM1FILTERBYLEVEL({TM1SUBSETALL([category].[category])},0)} 
DIMENSION PROPERTIES MEMBER_NAME ON 0
FROM [Sales]

There is no way to manipulate

Expected behavior
I think, the function can be extended with a mapping parameter like: mapping = {"category":"some_category"} if the hierarchy name differs from the dimension name. In this case we can still use the generic function cells.clear() instead of cells.clear_with_mdx() and we get:

SELECT
NON EMPTY 
{[product].[10]} 
* {[Year].[2023]} 
* {[Month].[11]} 
* {TM1FILTERBYLEVEL({TM1SUBSETALL([age].[age])},0)} 
* {TM1FILTERBYLEVEL({TM1SUBSETALL([category].[some_category])},0)} 
DIMENSION PROPERTIES MEMBER_NAME ON 0
FROM [Sales]

Version
TM1py: 1.11.3
TM1 Server Version: 11.8

[MariusWirtz]

Hi @151N3,

quick question. If you run the code below, does TM1py clear out the data as it should?

from TM1py import TM1Service

with TM1Service(address="localhost", port=12354, user="admin", password="apple", ssl=True) as tm1:
    tm1.cells.clear(
        cube="Sales",
        product="[Product].[10]",
        year="[Year].[2023]",
        month="[Month].[11]",
        category="{TM1FILTERBYLEVEL({TM1SUBSETALL([category].[some_category])},0)}")
   

[151N3]

Hi @MariusWirtz,
thank you for the fast reply and yes it does. I had modified the tm1.cells.clear() function to get the mdx statement and looks good. Thats how i came up with the suggestion.
But in our case we are working on a more generic way to be able to delete the same periods of a cube as the ones from a give dataframe. The function we have been working on is called clear_cube_cells_from_df(cube_name: str, df: pd.DataFrame) where we get a dataframe with distinct values for the dimensions that we need to provide to the tm1.cells.clear() function. For us providing the mapping to our custom function, but maybe it can be usefull at least to add some documentation for the function or at least here clearing-cube-data-with-tm1py. If you want, we can have a chat and I can show you our work.

[MariusWirtz]

I am interested to implement a kind of clear_from_df function in TM1py that takes a dataframe as input to perform a clear.
Please feel free to open a PR.

[151N3]

Hi @MariusWirtz,
i'm done with the new function clear_from_df and I spent most of the time with debbuging and I think there is a need for a functionallity like CaseAndSpaceInsensitiveList where you get a list of the the that is a join of the keys and the values of CaseAndSpaceInsensitiveSet so you don't deal with the sensitiveness of dimension or hierarchy names.

[MariusWirtz]

Please feel free to include the CaseAndSpaceInsensitiveList in the PR. I think it doesn't need to implement all functions at the beginning. We can implement it in an iterative and collaborative approach.

[151N3]

I think, before we have to have a chat where we could use CaseAndSpaceInsensitiveList. I try to make the PR for the function clear_from_df this week and I'll let you know.

[151N3]

Hi @MariusWirtz ,
I have just pushed the new function clear_from_df . Can you have a look at the code please? Thanks

[MariusWirtz]

Resolved with #1026 and #1032