Tm1py: Best practices update dimension

[scrumthing]

Describe what did you try to do with TM1py
I have a pandas dataframe with multiple columns. In one column I have the future element name and the rest has additional attributes for my dimension.

I see the following issues:

• My dataframe does not include all elements in the dimension
• My dataframe may include elements which are already existing but their attributes need an update
• My elements need to be under different parents (another column in the dataframe)

My current solution is to iterate over the rows and check every element individually if it exists and afterwards add them to the hierarchy and add the edge. Afterwards I create a cellset for the attributes and write that to the attribues cube. I find that solution rather not elegant.
What would be the best way to update my dimension with these informations?

Version

• TM1py 1.5
• TM1 Server Version: 2.0.9.2 aka 11.8.00100.13

[rkvinoth]

My current solution is to iterate over the rows and check every element individually if it exists

You can avoid this by getting all the elements from the dimension and subtract the same with the Dataframe's element column.

from TM1py.Services import TM1Service
import pandas as pd

df = pd.read_csv()
elements = set(df['element_name'].unique())

tm1 = TM1Service()
all_elements = set(tm1.elements.get_element_names('',''))

# These elements are new to the dimension
new_elements = all_elements - elements

# Loop through and add them to the hierarchy

Afterwards I create a cellset for the attributes and write that to the attribues cube.

We have a write_dataframe method for writing a Dataframe. Slice and Dice your Dataframe and send it directly to the cube.

[scrumthing]

Thanks @rkvinoth. That did do the trick.
I had to tweak it a bit by converting all items in both lists to lowercase.

elementsInDataFrame = [each_string.lower() for each_string in elementsInDataFrame]
elementsInDimension = [each_string.lower() for each_string in elementsInDimension]
elementsNew = set(elementsInDataFrame) - set(elementsInDimension)

[MariusWirtz]

Hi @scrumthing,

if you use the CaseAndSpaceInsensitiveSet instead of the default set you don't have to convert the strings yourself.

from TM1py.Utils import CaseAndSpaceInsensitiveSet

elements = CaseAndSpaceInsensitiveSet("UK", "US ", "CH")
existing_elements = CaseAndSpaceInsensitiveSet("uk", "us")

elements_to_add = elements - existing_elements

for element in elements_to_add:
    print(element)

>>> CH

[scrumthing]

Here is my current solution after considering the feedback of @rkvinoth and @MariusWirtz. For me the issue is closed. :-)
Couldn't get the CaseAndSpaceInsensitiveSet to work. :-(

dimName = 'XXX' 
cubeName = '}ElementAttributes' + dimName 
mdxViewZeroOut= 'SELECT [...]'
# dataframe for attributes
df= pd.DataFrame(columns=['element','attribute','value'  ])

# some stuff to fill the dataframe
#...

# get list of new elements for instance dimension
elementsInDataFrame = set(df['element'].unique())
elementsInDataFrame = [each_string.lower().replace(' ', '') for each_string in elementsInDataFrame]
elementsInDimension = [each_string.lower().replace(' ', '') for each_string in elementsInDimension]
elementsNew = set(elementsInDataFrame) - set(elementsInDimension)

# write data to tm1
with TM1Service(**creds) as tm1_basis:

    #dimension operations
    if len(elementsNew) != 0:
        dimension = tm1_basis.dimensions.get(dimName)
        h = dimension.hierarchies[0]
        for element in elementsNew:
            print(element)
            h.add_element(element_name=element, element_type='Numeric')        
            h.add_edge(elementParent, element, 1)
        tm1_basis.dimensions.update(dimension)

    tm1_basis.cubes.cells.clear_with_mdx(cubeName,mdxViewZeroOut)
    tm1_basis.cells.write_dataframe(cubeName, df)

[rkvinoth]

Here is my current solution after considering the feedback of @rkvinoth and @MariusWirtz. For me the issue is closed. :-)
Could get the CaseAndSpaceInsensitiveSet to work. :-(

dimName = 'XXX' 
cubeName = '}ElementAttributes' + dimName 
mdxViewZeroOut= 'SELECT [...]'
# dataframe for attributes
df= pd.DataFrame(columns=['element','attribute','value'  ])

# some stuff to fill the dataframe
#...

# get list of new elements for instance dimension
elementsInDataFrame = set(df['element'].unique())
elementsInDataFrame = [each_string.lower().replace(' ', '') for each_string in elementsInDataFrame]
elementsInDimension = [each_string.lower().replace(' ', '') for each_string in elementsInDimension]
elementsNew = set(elementsInDataFrame) - set(elementsInDimension)

# write data to tm1
with TM1Service(**creds) as tm1_basis:

    #dimension operations
    if len(elementsNew) != 0:
        dimension = tm1_basis.dimensions.get(dimName)
        h = dimension.hierarchies[0]
        for element in elementsNew:
            print(element)
            h.add_element(element_name=element, element_type='Numeric')        
            h.add_edge(elementParent, element, 1)
        tm1_basis.dimensions.update(dimension)

    tm1_basis.cubes.cells.clear_with_mdx(cubeName,mdxViewZeroOut)
    tm1_basis.cells.write_dataframe(cubeName, df)

I don't see the CaseAndSpaceInsensitiveSet anywhere!

[scrumthing]

Thanks @rkvinoth. That was a typo... COULDN'T get it to work. :-D
Already told @MariusWirtz about it