Tm1py: Reading from TM1

[hermie64]

Newbie alert.

I went through the docs and checked “data_structures_in_python.ipynb” from samples, but I cannot make this work. Any hint or example would be very much appreciated.

I want to read some numerical attributes from an ElementAttributes cube and assign those to variables.

Via cellset = tm1.cubes.cells.execute_mdx(mdx) I get this dictionary:

{('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[year]'): {'Value': 2020}, ('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[month]'): {'Value': 10}, ('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[day]'): {'Value': 29}, ('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[hour]'): {'Value': 13}, ('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[minute]'): {'Value': 1}, ('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[second]'): {'Value': 25}}

How do I extract the year (2020), month (10), day (29), hour (13), minute (1) and seconds (25) from this cellset so that I can assign these values to variables?

[rkvinoth]

@hermie64 this isn't something related to TM1py but stackoverflow. I can definitely help you now but that wouldn't help you in the long run.
You definitely have to learn python basics and understand how the basic datatypes work.

To answer your question..

cellset ={('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[year]'): {'Value': 2020}, 
('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[month]'): {'Value': 10}, 
('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[day]'): {'Value': 29}, 
('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[hour]'): {'Value': 13},
 ('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[minute]'): {'Value': 1}
, ('[dimension_name].[dimension_name].[#TE_Test2]', '[}ElementAttributes_dimension_name].[}ElementAttributes_dimension_name].[second]'): {'Value': 25}}
for (key1, key2), value in cellset.items():
    if 'year' in key2:
        year = value['Value']
    elif 'month' in key2:
        month = value['Value']
    elif 'day' in key2:
        day = value['Value']
    elif 'hour' in key2:
        hour = value['Value']
    elif 'minute' in key2:
        minute = value['Value']
    elif 'second' in key2:
        second = value['Value']

keep in mind that, this is not the efficient way.

The easier way to achieve what you are looking for is:

from TM1py.Services import TM1Service


tm1 = TM1Service()
mdx = """select {{[dimension_name].[#TE_Test2]}} on rows,
                 {{[}ElementAttributes_dimension_name].[year], 
                 [}ElementAttributes_dimension_name].[month],
                 [}ElementAttributes_dimension_name].[day],
                 [}ElementAttributes_dimension_name].[hour],
                 [}ElementAttributes_dimension_name].[minute],
                 [}ElementAttributes_dimension_name].[second]}} on columns
                from [}ElementAttributes_dimension_name]"""
year, month, day, hour, minute, second = tm1.cubes.cells.execute_mdx_values(mdx)

[MariusWirtz]

Hi @hermie64,

TM1py provides different functions to retrieve data from TM1 through MDX or cube views.
You can read more about them here:
https://code.cubewise.com/tm1py-help-content/getting-data-from-tm1-with-python

[hermie64]

Hi Marius,

I already checked that page, but I couldn't figure it out.
rkvinoth is right, I should take a deep dive into Python and data types.
For now the 2nd example from rkvinoth worked and thanks for that!

Herman