Page 1 of 1
Add elements to a dimension from PAfE
Posted: Tue Jul 12, 2022 2:36 pm
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!
Re: Add elements to a dimension from PAfE
Posted: Tue Jul 12, 2022 2:58 pm
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.
Re: Add elements to a dimension from PAfE
Posted: Thu Jul 14, 2022 6:11 am
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.
Re: Add elements to a dimension from PAfE
Posted: Thu Jul 21, 2022 11:16 am
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 |
Re: Add elements to a dimension from PAfE
Posted: Thu Jul 21, 2022 11:28 am
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.
Re: Add elements to a dimension from PAfE
Posted: Thu Jul 21, 2022 1:40 pm
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")
Re: Add elements to a dimension from PAfE
Posted: Fri Jul 22, 2022 8:14 am
by MariusWirtz
Thanks, Wim!
Totally agree. We would want to exclude empty cells! Thanks for the fix.