Migrate Application folder structure to PAW or Excel?

[michaelc99]

Good Afternoon,

We have utilized the "Application" folder structure within Architect/Perspectives in order to place Excel and TI Processes in execution order for Month-End, Year End and Forecasting processes. Here is a basic example:

Applications
>Month End Processing
> Step A
> Excel Based data load into Local Cubes
> Step B
> TI Based Equity Processes
> Step C
>TI Based Data Load from Local Cubes to GL MAIN
... etc

Since the Rich Tier Desktop is going away, it appears that, in the future, I will need to execute the Excel-based files from my local machine while executing the TI processes from PAW > Data and Models > Workbench > Processes in the same order as the original folder structure. In an attempt to re-create my "application folder" struture within PAW, it appears that I cannot select my TI processess. I suspect the feature is not available or I have a configuration issue.

I wondering if there is either a better solution towards this issue, or if there is a way to migrate my "application folder" structure into PAW, or if you recommend building multiple tabs in an Excel spreadsheet with Action Buttons mapped to each process.

Thank you in advance,
Michael

[Wim Gielis]

Is this thread similar ?
https://community.ibm.com/community/use ... -architect

[michaelc99]

Is this thread similar ?
https://community.ibm.com/community/use ... -architect

Thank you Win!

It is indeed the same challenge. We run a number of wrapper-rule processes, where possible, but typically our month-end process is as follows (edit: bold = excel files):

1) Run a Macro-enabled Excel file that loads XLS files to 17 local cubes
2) Run an TI-based equity wrapper rule
4) Run a TI-based wrapper to load 17x local cubes into one GL MAIN cube.
5) Run a TI-based process for Fixed Freight
6) Run Std Sales Eliminations TI-based Rule
7) Run an Excel file that loads Eliminations - Profit in Inventory
8) Run an Excel file that loads Eliminations - Other
9) Run a TI-based Allocations wrapper rule

I am looking into Luc's suggestion of building a Proess-Execution cube that code breaks on error. It would be great if I could get it to pause on the two Excel-based processes but I may need to write separate TI processes to handle that scenario. I am working (behind other priorities) to convert the remaining Excel files into TI processes, but those may take longer to convert given their nature.

Thank you,
Michael

[ascheevel]

It would be great if I could get it to pause on the two Excel-based processes...

I'm guessing the tm1 authentication piece is preventing you from calling the excel-based processes via a vbs script and running unattended? If not, you could call the vbs from a TI and simply make that part of the execution stack.

I don't know what orchestration options/tools you have available to you (Autosys, python, vbs, powershell, AWS, etc.), but if your update job list includes actions that happen outside of TM1, it may be worth considering a solution that doesn't have TM1 in the driver's seat or as the center of the universe. By using an orchestrator that is not TM1, you would gain the ability to execute sequential actions that are happening both in and out of TM1 and get much better error/incident handling & notification when those outside-of-tm1 jobs fail.

Ignoring what I said above and just looking at your 9 steps, I think you could probably handle all of those in a python file called via TI that leverages Pandas to read the excel files and TM1py to load data directly to cubes and call TI processes in TM1. If you're going for biggest automation bang for your money and you have a bit of python experience, this might be your best and quickest option.

[Paul Segal]

What ascheevel said, particularly on Python and TM1py - Python can act as an excellent controller and ETL to TM1, and depending on use case you can package it up as an executable using pyinstaller so the machine running it doesn't even have to have Python installed.

[michaelc99]

It would be great if I could get it to pause on the two Excel-based processes...

I'm guessing the tm1 authentication piece is preventing you from calling the excel-based processes via a vbs script and running unattended? If not, you could call the vbs from a TI and simply make that part of the execution stack.

I don't know what orchestration options/tools you have available to you (Autosys, python, vbs, powershell, AWS, etc.), but if your update job list includes actions that happen outside of TM1, it may be worth considering a solution that doesn't have TM1 in the driver's seat or as the center of the universe. By using an orchestrator that is not TM1, you would gain the ability to execute sequential actions that are happening both in and out of TM1 and get much better error/incident handling & notification when those outside-of-tm1 jobs fail.

Ignoring what I said above and just looking at your 9 steps, I think you could probably handle all of those in a python file called via TI that leverages Pandas to read the excel files and TM1py to load data directly to cubes and call TI processes in TM1. If you're going for biggest automation bang for your money and you have a bit of python experience, this might be your best and quickest option.

Good Morning ascheevel,

For me, it is lack of knowledge that the functionality existed versus an authentication issue since my path-forward has been to convert Excel files into TI processes versus calling Excel via a TI process.

In the interim, I will look into converting Excel files from XLS to XLSM and build out the VBS scripts to automate the manual steps. I have downloaded TM1py and have access to Python and I think that's the best long-term solution, but my python skillsets are quite limited at the moment. If there are examples out there, or I can develop something quickly/easily with generative AI, then I probably can build something quickly. As far as other toolsets that I have at my disposal are limited to PowerAutomate, AzureData Studio and Python.

Thank you,
Michael

[michaelc99]

It would be great if I could get it to pause on the two Excel-based processes...

I'm guessing the tm1 authentication piece is preventing you from calling the excel-based processes via a vbs script and running unattended? If not, you could call the vbs from a TI and simply make that part of the execution stack.

I built out the VBA code within one Excel file and a separate VBS script to open up the file, execute the macro, and close the process. However, it got hung up on Application.Run "TM1RECALC", which is probably because Perspectives is not loaded when Excel opened up. I replaced that with ActiveWorkbook.Calculate but got a similar message. I'll have to see if there is another call to execute "F9" twice.

Separately, the vbs script can be called using an explicit path. How does that work when the Rich Tier clent is removed? Is that placing the Excel files int he /data/ folder and calling the from there using a relative path? Or, are you creating separate TI Processes for Excel-based processes and calling the file as a datasource?

I'll explore Python next, but I want to keep both options on the table.

Thank you,
Michael

[michaelc99]

For those following along - since the VBS script was initially placed on the Rich Tier desktop, which is going away, and the Data Tier does not and will not have Excel installed, then I had to stop pursuing this approach.

I am now pivoting my efforts into developing a python script to execute TO and Excel-Macro steps in execution order. I will also look at developing an Admin cube but this might be an early FY2025 effort.