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!
Add elements to a dimension from PAfE
-
- 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
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.
https://ibm.github.io/paxapi/#introduction
Look at the TurboIntegrator Functions and REST API.
-
- 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
When I read:
“replace all elements in a dimension with said list“
I would say: be careful, you could end up losing data.
“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
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
-
- 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
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:
It should work with an excel file that has the elements in Column A like this:
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)
Primus |
Argo |
Yaris |
Corolla |
Supra |
-
- 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
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.
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
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
-
- 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
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
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
-
- 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
Thanks, Wim!
Totally agree. We would want to exclude empty cells! Thanks for the fix.
Totally agree. We would want to exclude empty cells! Thanks for the fix.