Checking for Unused Dimension Elements Before Deletion

[Sven-Bo]

I'm looking for a way to verify that a specific dimension element is unused (has no data) across all cubes before safely deleting it. I have the dimension name and the element name. Is there a straightforward method that can perform this check?

For example, if my dimension is "Product" and the element is "Product123", how can I use TM1py to ensure "Product123" is unused in all cubes?

Any guidance would be greatly appreciated. Thanks!

[wimgielis]

Hi Sven, In the past, I did that with a nifty TI process (using recursion and MDX views as the data source, quite advanced stuff). I also allow the user to provide a public subset name or an MDX, rather than just 1 element. An MDX like the orphans in a dimension (can they be deleted or not ?) In TM1py I haven't done that. The routine would be similar though: - get the cubes that contain the dimension - create an MDX view on the cube, filtering on the 1 element, zero suppressed typically, level 0 for other dimensions - get the first data point that is non-zero, if it exists you know that the element contains data and you can stop the processing. - if not, continue searching In my TI I also ordered the cubes in terms of number of dimensions, processing first the smaller cubes. Could save you some time although it's not an exact science obviously. Hope this helps.

…

------ Best regards / Beste groeten, Wim Gielis IBM Champion 2024 MS Excel MVP 2011-2014 https://www.wimgielis.com <http://www.wimgielis.be> Op wo 14 feb 2024 om 17:05 schreef Sven ***@***.***>:

I'm looking for a way to verify that a specific dimension element is unused (has no data) across all cubes before safely deleting it. I have the dimension name and the element name. Is there a straightforward method that can perform this check? For example, if my dimension is "Product" and the element is "Product123", how can I use TM1py to ensure "Product123" is unused in all cubes? Any guidance would be greatly appreciated. Thanks! — Reply to this email directly, view it on GitHub <#1055>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEDHULIUHOTTQO4XWMZYD4TYTTOC5AVCNFSM6AAAAABDIRPC5KVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZWGIYTQMJYGI> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[rclapp]

You can reduce iteration by suppressing 0s and getting the count of cells in the query response. If you write something it would be a valuable contribution. On Feb 14, 2024 11:19 AM, Wim Gielis ***@***.***> wrote: Hi Sven, In the past, I did that with a nifty TI process (using recursion and MDX views as the data source, quite advanced stuff). I also allow the user to provide a public subset name or an MDX, rather than just 1 element. An MDX like the orphans in a dimension (can they be deleted or not ?) In TM1py I haven't done that. The routine would be similar though: - get the cubes that contain the dimension - create an MDX view on the cube, filtering on the 1 element, zero suppressed typically, level 0 for other dimensions - get the first data point that is non-zero, if it exists you know that the element contains data and you can stop the processing. - if not, continue searching In my TI I also ordered the cubes in terms of number of dimensions, processing first the smaller cubes. Could save you some time although it's not an exact science obviously. Hope this helps.

------ Best regards / Beste groeten, Wim Gielis IBM Champion 2024 MS Excel MVP 2011-2014 https://www.wimgielis.com <http://www.wimgielis.be> Op wo 14 feb 2024 om 17:05 schreef Sven ***@***.***>:

I'm looking for a way to verify that a specific dimension element is unused (has no data) across all cubes before safely deleting it. I have the dimension name and the element name. Is there a straightforward method that can perform this check? For example, if my dimension is "Product" and the element is "Product123", how can I use TM1py to ensure "Product123" is unused in all cubes? Any guidance would be greatly appreciated. Thanks! — Reply to this email directly, view it on GitHub <#1055>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEDHULIUHOTTQO4XWMZYD4TYTTOC5AVCNFSM6AAAAABDIRPC5KVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZWGIYTQMJYGI> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

— Reply to this email directly, view it on GitHub<#1055 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AEK7GZWGGJXPPKUXPCVK5GLYTTPWBAVCNFSM6AAAAABDIRPC5KVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DINRYGA3DM>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[Sven-Bo]

Thank you, Wim and Ryan, for the hints. I will keep you updated once I have a working script.

[Sven-Bo]

I have a working script, but it's quite basic. It just checks for a specific dimension and element, counting how many values it finds in the cubes. However, it doesn't handle errors, such as if the dimension or element doesn't exist. Here's what I've put together:

from mdxpy import MdxBuilder, MdxHierarchySet, Member
from TM1py.Services import TM1Service


def build_mdx_query(cube_name, excluded_dimension, element_to_check, dimensions):
    """
    Constructs an MDX query to find usage of a specific element in a dimension.
    """
    query = MdxBuilder.from_cube(cube_name).columns_non_empty().rows_non_empty()
    query.add_hierarchy_set_to_axis(
        0, MdxHierarchySet.member(Member.of(excluded_dimension, element_to_check))
    )
    for dimension in dimensions:
        if dimension != excluded_dimension:
            query.add_hierarchy_set_to_axis(1, MdxHierarchySet.all_leaves(dimension))
    return query.to_mdx()


with TM1Service(address="localhost", port=12345, user="admin", password="apple", ssl=True) as tm1:
    dimension_to_check = "Period"
    element_to_check = "Jan 2023"
    skip_control_cubes = True

    # Retrieve cubes containing the dimension
    cube_names = tm1.cubes.search_for_dimension(dimension_to_check, skip_control_cubes)

    for cube_name in cube_names:
        dimensions = tm1.cubes.get_dimension_names(cube_name)
        mdx = build_mdx_query(cube_name, dimension_to_check, element_to_check, dimensions)

        # Execute MDX and get cell count to check if element is used
        cell_count = tm1.cells.execute_mdx_cellcount(mdx)
        print(f"Cube: {cube_name}, Cells with data for '{element_to_check}': {cell_count}")

The output looks something like this:

Cube: SalesPlanning, Cells with data for 'Jan 2023': 56
Cube: EmployeeSalaries, Cells with data for 'Jan 2023': 30
Cube: InventoryManagement, Cells with data for 'Jan 2023': 0
Cube: CapitalExpensePlanning, Cells with data for 'Jan 2023': 68