Add elements to a dimension from PAfE

Post Reply
nyczducky
Posts: 1
Joined: Sun Sep 19, 2021 6:08 am
OLAP Product: Planning Analytics
Version: Cloud
Excel Version: 365

Add elements to a dimension from PAfE

Post by nyczducky »

Just wondering if there is a way to add elements to an existing dimension via Excel?

We are running macros as a part of our process to scrub and parse Excel files for all elements. At the end of the day, we are left with a list in Excel that should represent all elements in a dimension.

We'd like to use something in Excel to add elements or replace all elements in a dimension with said list. Ideally with a macro running.

Any ideas would be appreciated! Thanks!
Emixam
Posts: 139
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: The Internet

Re: Add elements to a dimension from PAfE

Post by Emixam »

I would probably use the IBM Planning Analytics for Excel API.

https://ibm.github.io/paxapi/#introduction

Look at the TurboIntegrator Functions and REST API.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Add elements to a dimension from PAfE

Post by Wim Gielis »

When I read:

“replace all elements in a dimension with said list“

I would say: be careful, you could end up losing data.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MariusWirtz
Posts: 29
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

Re: Add elements to a dimension from PAfE

Post by MariusWirtz »

Hi,

instead of using a macro and the PAX API, you could also use a py script to read the column values from excel and add the elements to the dimension with TM1py.

Here is a sample in case you are interested:

Code: Select all

import openpyxl
from TM1py import TM1Service

excel_products = set()

wb = openpyxl.open("products.xlsx")
sheet = wb["Sheet1"]
for cell in sheet["A"]:
    excel_products.add(cell.value)

with TM1Service(address="", port=12354, ssl=True, integrated_login=True) as tm1:
    product_hierarchy = tm1.hierarchies.get(dimension_name="Product", hierarchy_name="Product")

    for product in excel_products:
        if product not in product_hierarchy:
            product_hierarchy.add_element(element_name=product, element_type="Numeric")

    tm1.hierarchies.update(hierarchy=product_hierarchy)
It should work with an excel file that has the elements in Column A like this:

Primus
Argo
Yaris
Corolla
Supra
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Add elements to a dimension from PAfE

Post by Wim Gielis »

Thanks Marius.

In case of empty cells in the column:
Would you exclude them from the first main loop through the cells ?
Or would they be skipped by Python ?
Or would they be added by Python but this line skips them: ‘if product not in product_hierarchy:‘

I would still do an if len check on excel_products in case nothing is to be created. If only just to provide a message that nothing will be done.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Add elements to a dimension from PAfE

Post by Wim Gielis »

With a few additions/changes - up to Marius to comment if useful or not:

Code: Select all

import openpyxl
from TM1py import TM1Service

def isBlank (myString):
    return not (myString and myString.strip())

def isNotBlank (myString):
    return bool(myString and myString.strip())

excel_products = set()

for cell in openpyxl.open("D:\products.xlsx")["Sheet1"]["A"]:
    if isNotBlank( cell.value ):
        excel_products.add( cell.value )

if len(excel_products):
    # print(excel_products)
    with TM1Service(address="", port=12354, ssl=True, integrated_login=True) as tm1:
        product_hierarchy = tm1.hierarchies.get(dimension_name="Product", hierarchy_name="Product")

        for product in excel_products:
            if product not in product_hierarchy:
                product_hierarchy.add_element(element_name=product, element_type="Numeric")

        tm1.hierarchies.update(hierarchy=product_hierarchy)
else:
    print("Set of products is empty")
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MariusWirtz
Posts: 29
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

Re: Add elements to a dimension from PAfE

Post by MariusWirtz »

Thanks, Wim!

Totally agree. We would want to exclude empty cells! Thanks for the fix.
Post Reply