Hi Ravi,
1/ You can call command line from SQL Server jobs, so you can use TM1RunTI to execute a process from the job.
2/ You can schedule a Chore in TM1 to run at regular intervals (e.g. every hour) and at the start of the chore have a process that checks for a flag in a SQL DB table. If the flag is populated with a 1 then the process updates the flag in the table to 0 and continues to run the other steps (e.g. import data), if it is already showing a 0 then the process Quits the Chore.
With this method you would add a step into the SQL job that updates the flag to a 1, as and when you want TM1 to start processing the data.
3/ You could look to see whether you have access/licensing to Cognos Command Centre. A lot of customers have licences for Cognos Command Centre but never use it. It's not a tool I have had much use for myself but I believe it is specifically designed for controlling tasks across various applications via a single interface. So if you do already have the right to use it, then it could be a good option to align all of your processes.
Thanks,
Declan
------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------
Original Message:
Sent: Fri July 01, 2022 11:59 AM
From: Ravi Kotha
Subject: Trigger SQL Server jobs from Planning Analytics TI Process
Thanks Declan for detailed info. I see that now we can simply put execute command on DB source query area, which is also executing the stored proc. Do you have any ideas on how we can execute TM1 Chore or process from an SQL server agent job.
-Ravi
------------------------------
Ravi Kotha
Original Message:
Sent: Tue June 14, 2022 08:01 AM
From: Declan Rodger
Subject: Trigger SQL Server jobs from Planning Analytics TI Process
Hi Ravi,
There is the TI function ODBCOutput which allows you to update data in a DB, such as inserting a new row or updating a table (or executing a stored procedure.)
This gives you a few different options on the SQL Server side.
You can create a stored procedure that executes the Agent Job immediately - then from ODBCOutput you just execute the Stored Procedure.
Or you could set up a "trigger" on SQL Server that executes the Job when a certain Table is updated.
Depending on what the "Job" is actually doing, we often use the table update and trigger method. This allows us to Update a "Start Time" for the Job in a table, the "Trigger" then executes the job, and the "Job" in turn updates a status or end time in the same table. This can be quite useful when the Job is running a large integration for example that moves data between DBs and can take a long time.
Whatever method you go with, most of the work you need to do here (although it's not much) is on the DB side itself. TM1 just needs a TI process containing:
ODBCOpen (opens your connection between TM1 and the DB)
ODBCOutput (insert, update or execute etc.)
ODBCClose (closes the DB connection with TM1)
Thanks,
Declan
------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
Original Message:
Sent: Mon June 13, 2022 01:55 PM
From: Ravi Kotha
Subject: Trigger SQL Server jobs from Planning Analytics TI Process
Hi Friends,
I'm looking to trigger an sql server agent job from Planning analytics (TI Process or any other means). A little background, We have a data warehouse on sql server which is refreshed by couple of sql server jobs. One for daily incremental load and other one is an adhoc job which only few of our corporate team have access to trigger the job cognos for data refreshes as they make changes on source during the business hours. We load our TM1 cube from the data ware house and our users are requesting similar functionality from TM1 or PAW to trigger the adhoc data refresh job on sql server and also see last run status etc as they do not know when was data warehouse refreshed last time. Appreciate if anyone can provide an insight on how we can set it up. Thanks in advance.
-Ravi
------------------------------
Ravi
------------------------------
#PlanningAnalyticswithWatson