Working with Python lists and dictionaries in a TM1 context

Lists and dictionaries are probably the most important data structures that you should be aware off when writing Python scripts for your IBM TM1 and Planning Analytics application.

To learn more about all different data structures available in Python, there is already lots of content online such as https://python.swaroopch.com/data_structures.html.

In this article we will focus on lists and dictionaries and then how to use them in a TM1 context.

Lists: an ordered collection of items

A list is a data structure that holds an ordered collection of items i.e. you can store a sequence of items in a list:

fruits = ['apple', 'banana', 'kiwi', 'cherry']
print(fruits)

A list in Python has lots of useful features such as:

Function Example Definition
append fruits.append('dragonfruit') Adding a new item
+ healthy_stuff = fruits + vegetables Concatenating two lists
len() len(healthy_stuff) Getting the number of items

It is also very easy to loop through the list:

for element in healthy_stuff:
    print(element)

In Python when you want to create a new list from an existing one it can done easily through List Comprehension. So you could execute the upper function on every item and create a new List from the result. The code almost reads like plain English:

Lists in a TM1 context

The Python lists can be used to manipulate elements. For example let’s create a new list called element_names containing the list of all elements from the hierarchy City of the dimension City:

with TM1Service(address=ADDRESS, port=PORT, user=USER, password=PASSWORD, ssl=SSL, decode_b64=DECODE_B64) as tm1:
    element_names = list(tm1.dimensions.hierarchies.elements.get_element_names(
        dimension_name='City', 
        hierarchy_name="City"))

Once our elements list has been created, we can now leverage the lists properties such as:

Function Example Definition
len() len(element_names) Getting the number of elements
in 'NYC' in element_names Checking if an element exists

Dictionaries

A dictionary is like an address-book where you can find the address or contact details of a person by knowing only his/her name i.e. we associate keys (name) with values (details). The key of each object must be unique.

food_rating = {'Sushi':4, 'Ice Cream':3, 'Bacon':3.5}
print(food_rating)

Once your dictionaries has been created you get access to all dictionaries features:

Function Example Definition
[] food_rating["Xiao Long Bao"] = 5 Adding a new element with 5 as value
get(object) food_rating.get("Bacon") Getting the value of an object
sum()/len() sum(food_rating.values()) / len(food_rating) Calculating average
del del food_rating['Ice Cream'] Removing one object

Dictionaries in TM1 context

The Python dictionnaries can be used to manipulate TM1 data . Let’s create a new dictionary containing the data from an MDX statement:

mdx = """
SELECT
{[Date].[2019-12-20]:[Date].[2019-12-31]} * {[City].[NYC], [City].[Chicago]} ON ROWS,
{[Public Holidays Measure].[Public Holiday]} ON COLUMNS
FROM [Public Holidays]
WHERE ([City].[NYC])
"""

To create a dictionary containing the cells intersection and the values we use the TM1py function tm1.cubes.cells.execute_mdx_rows_and_values:

with TM1Service(address=ADDRESS, port=PORT, user=USER, password=PASSWORD, ssl=SSL, decode_b64=DECODE_B64) as tm1:
    holidays = tm1.cubes.cells.execute_mdx_rows_and_values(
        mdx=mdx, 
        element_unique_names=False)
print(holidays)

The TM1py dictionary created, will contained the cell coordinates as keys and the values as values. We can then easily loop print each intersections and values:

for row_elements, cells in holidays.items():
        print(*row_elements, *cells)

To get a specific value from this dictionary, you need to specify all coordinates, in this example there are only two dimensions in this cube:

  • holidays[('2019-12-25', 'Chicago')] returns 1

  • holidays[('2019-12-25', 'CHI CAGO')] returns 1

It can be noted that ‘Chicago’ or ‘CHI CAGO’ will return the same value because the dictionary created by TM1py is case and space insensitive.

More examples are available in the data_structures_in_python.ipynb Jupyter Notebook available in the TM1py samples Data folder:

The easiest way to test it is to download the all TM1py Samples repository.