Use TM1RunTI in the TM1 process

Post Reply
AskAndAnswer
Posts: 39
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Use TM1RunTI in the TM1 process

Post by AskAndAnswer » Wed Oct 16, 2019 6:46 pm

I'd like to create a multi-threaded TM1 process that will run a TI for each month in the time dimension. The idea is to create a loop that will pick a month and then use TM1RunTI to call another process that will use a month as a parameter that will be passed to a SQL query.
My issue is that I have a limited number of threads, and when the server runs out of free threads, I need my process to wait when one of the previous processes finishes and a thread becomes available. Will it be done automatically? If so, will it be using all available threads, or there is a way I can limit the number of threads that should be used by this particular process?

ascheevel
Posts: 85
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: 11.2
Excel Version: 2016

Re: Use TM1RunTI in the TM1 process

Post by ascheevel » Wed Oct 16, 2019 6:59 pm

Check out RushTi from Cubewise. That tool allows you to limit the number of simultaneous threads.

https://code.cubewise.com/rushti

AskAndAnswer
Posts: 39
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Re: Use TM1RunTI in the TM1 process

Post by AskAndAnswer » Fri Oct 18, 2019 4:03 pm

I am not allowed to install additional tools on IBM cloud.

Wim Gielis
MVP
Posts: 2258
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Use TM1RunTI in the TM1 process

Post by Wim Gielis » Fri Oct 18, 2019 4:12 pm

What are your experiences with RunProcess ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

lotsaram
MVP
Posts: 3317
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Use TM1RunTI in the TM1 process

Post by lotsaram » Fri Oct 18, 2019 6:53 pm

AskAndAnswer wrote:
Fri Oct 18, 2019 4:03 pm
I am not allowed to install additional tools on IBM cloud.
You don’t need to install anything in the IBM cloud. All you need is the port for the Rest API to be accessible.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

AskAndAnswer
Posts: 39
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Re: Use TM1RunTI in the TM1 process

Post by AskAndAnswer » Fri Nov 01, 2019 3:22 pm

My response that I cannot install anything on IBM cloud addressed the suggestion to use RushTi.
It is my first time trying to create a multi-threaded process. I have an idea how to do it, and my question was about thread control. If I have 10 threads and 20 processes, how do I tell the 11th process to wait for an available thread?

ascheevel
Posts: 85
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: 11.2
Excel Version: 2016

Re: Use TM1RunTI in the TM1 process

Post by ascheevel » Fri Nov 01, 2019 4:42 pm

AskAndAnswer wrote:
Fri Nov 01, 2019 3:22 pm
My response that I cannot install anything on IBM cloud addressed the suggestion to use RushTi.
Is your issue that you must call your job from within TM1 via TI, action button, or scheduled chore? If scheduled chore, you could use scheduled tasks on your local machine or some other on-premise box that has python installed. If TI or action button, it's beyond my knowledge, but I imagine it's possible to have an internally hosted web server execute a python script when called via REST from PowerShell script executed by TI on ibm cloud server. I wonder if a webapp hosted on Python Anywhere would give you that functionality with their API.

Check out the thread linked below that was recently active. Some users contributed good examples of how they monitor for process completion. You could implement something based of their experiences and use RunProcess or call RunTi script from a TI process using a WHILE loop and SLEEP function to limit the number of parallel processes being executed at one time.

You might be able to achieve with a PowerShell script what RushTi does. You can run PowerShell scripts on ibm cloud server no problem.

I recently moved from TM1 on-premise with python installed on the same server to the cloud where I can no longer have TM1 call RushTi scripts on itself. I understand the limitation you mentioned acutely, but there are workarounds.


viewtopic.php?p=74293#p74293

ascheevel
Posts: 85
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: 11.2
Excel Version: 2016

Re: Use TM1RunTI in the TM1 process

Post by ascheevel » Mon Nov 04, 2019 6:01 pm

I mentioned in my previous reply that there are workarounds and I thought I'd share one I worked on this morning as a proof of concept: using PyInstaller to create executable from a python script to be executed by TM1 on ibm's cloud server where python is not installed. For this POC, I had a simple python script to execute an HTTP POST request that posted a message to a Teams site. Using PyInstaller, I created an executable, transferred it to the ibm cloud server share drive and then called the executable from a TI process using ExecuteCommand. I validated successful execution by seeing the message posted on the Teams site. Presumably, you could attempt the same with a RushTI script written on your local machine where you would have python and the TM1py package installed. You'd then convert it to an executable using PyInstaller and transfer that to ibm cloud server (or your local server if TM1 local installation) where you could call it from a TI process. You can pass arguments to an executable, the number of active threads for example.

One big caution: PyInstaller notes in their documentation that the generated executable is specific to the OS it was created on. You shouldn't expect an executable created on a 32 bit machine to work on a 64 bit one for example, or where the OS's are different between machines. For this reason, I had low expectations about the executable created on my laptop working when run on the server, but it did work. I might not have had the same success with a more complex script. If you were running into compatibility issues, you could use virtualbox on your local machine with the desired OS installed and run PyInstaller within that to create a compatible executable.


To be sure, I don't think this is a great solution and currently doubt that I would prefer it to other viable options.


PyInstaller docs:
https://pyinstaller.readthedocs.io/en/s ... -mode.html


edit:
I thought i'd report back after writing a slightly more complex script. I wrote a python script to check an online resource for an updated excel file and then execute an MDX query against TM1 to see if a cube in TM1 has been updated for the time period denoted in the excel file's filename. If an updated file is available and TM1 is not updated, it will post a message to our Teams site. The script utilizes the following packages: datetime, calendar, requests, BeautifulSoup, TM1py, and pandas. The py script by itself is 5kb, the executable created by PyInstaller with the argument --onefile is 330mb. I was able to call the executable successfully from a TI after transferring it to ibm cloud server, but my opinion of this option is unchanged.

Post Reply