There is often a need in IBM Planning Analytics/TM1 system to set chores that run-in ways that the basic functionality of chores is not especially well designed to handle.
Planning Analytics/TM1 Basic Functionality
The classic PA/TM1 scheduling, which suffices for most desires is seen here in the Planning Analytics Workspace (PAW) view:
One can pick a start Day, a time and, it defaults to a frequency of “daily”, it’ll re-execute every day once enabled.
The frequency can be further chosen via the frequency drop down:
This is pretty good. One can pick a Friday and 7 day interval and a chore will run weekly, for example. Or 6 am and a 4 hour interval and it will run at 6:00, 10:00, 14:00, 18:00, etc…
You can also set up multiple chores to execute the same underlying processes at different times to achieve “run every 2 hours only during business hours”.
- If you are considering scheduling chores to run on a minute or second basis above, you should speak with an experienced PA/TM1 person to review the necessity. There are valid reasons to do so, but those reasons are rare. Often the perceived need can be avoided with a better/different design.
- Executing multiple chores at the same time (watch for different rolling executions that “sync up” occasionally!) is not recommended. This is especially true if the sub-processes edit data or metadata.
Tips and Tricks: In addition, the Architect interface currently allows the choice of running /scheduling in UTC or Local time, AND the ability to schedule run times to nearest minute and second e.g. below at 7:38:54. The reason you might care about such precision scheduling is to avoid contention or confusion that might occur in some external or internal contexts if the chore and outside events are all scheduled to run at exactly the same time. UTC scheduling is helpful when system operations need to be coordinated across many time zones. This functionality will probably be explicitly migrated to the PAW (workspace) interface in the future.
Advanced Scheduling and Chore Parameter Control
There are reasons to run chores on a more complex basis, examples:
- Accounting close happens on the 15th of the month, so execute the whole consolidation “process train” every morning before business start at 4 AM and at 1 PM so that it is fresh in the local morning, and after lunch. But stop the automatic execution & reloads, after that.
- Some chores must happen on the last day, or 5th, day of the month.
- Starting the 1st business day of the month, the upload of a fresh CSV file(s) must be checked for every 4 hours until the 8th, at which time new European sourced files stop loading automatically at noon Eastern Time (North America), America’s source files at 5pm and Asian files at 6am.
- During some periods give absolute priority to some chores. Otherwise allow normal locking/unlocking, waiting, managed by the system to occur.
- Do not run a specific chore overnight between 6pm and 5am otherwise run every 2 hours.
- Avoiding the need for formal scheduling of chores to run one after another at specific times, especially when the execution time variability of each chore can result in contention in occasional exceptional (end of month, end of year) conditions.
- g. Chore 1 runs at Midnight, and a 2nd chore which depends on first, a ½ hour later which is normally fine, but if Chore 1 fails to finish, then Chore2 will fail or partially execute and possibly leave the data or metadata in the system in a state where users are negatively affected.
In addition, and closely related, as chores run it is obviously helpful to pass their constituent processes different parameters that may change, or desired to be changed, by users. Native Chores can only pass “hardcoded in the chore” parameters.
The bad news is that PA/TM1 does not have this as native functionality, but the good news is that programming this is possible using a combination of existing “internal to” TM1/PA techniques.
How to Organize Chores & Processes
While there is only one way in TM1/PA to schedule the running of turbo integrator processes internally, namely chores (There are ways to “reach into” TM1 from outside scheduling applications & using the API, but that is outside the scope of this article.), but there are two ways to run a series of processes in a specific order.
- You can add them to the chore in a specific order using pre-arranged parameters (at the chore creation time) if the processes require them.
- You can use one process to execute sub-processes and pass variables.
- One can of course “mix and match” the above.
There is a major difference between a chore calling processes and processes calling processes (within chores and on their own)
A Chore can “commit all changes at the end of the chore” or “commit at end of each process” as an option. In the former case, changes to data or metadata made by an earlier process may not be available to later processes, as the changes are held until the very end. It follows that multiple commits means each process executes and commits one after another so that all subsequent processes have prior changes available. Generally, multiple commits are advised.
This is different for a process that executes other processes:
Each process commits its changes upon exit of the epilog section, but if a calling process executes multiple child processes, then the sibling processes do not see each other’s changes, and no commit occurs, until the “mother process” terminates.
Consequently, if subsequent process logic depends on changes that might be happen in a previous section of the chore, results can be unexpected. It is important to carefully consider what information is used in logic and ensure the “commit” events occur.
The CUBESAVEDATA command, which forces a “commit to disk” event for specific cube, can potentially be used to avoid this if the locking implications and time it takes are understood.
Advanced Scheduling Implementation
Advanced scheduling requires process code that evaluates logic. So, one sets up a chore and one or more of its constituent processes evaluate logic. A common way to help with this to set up a chore “control cube” that contains the necessary control data and exposes it.
The heart of the control logic to achieve logic that is flexible, easily controllable, and maintainable is to externalize the control parameters from processes. While a chore can be used to save /enter “hard coded” parameters to use at run-time, this requires editing of the chore to change, which is somewhat error prone and difficult to see, and the same is true with hard coded process logic.
A much better practice is to set up a control cube with the following dimensions:
- An index dimension (elements: ‘Index1’, ‘Index2’, ‘Index3’, etc….)
- A Measure dimension (has various elements as needed, including comment field(s)
- A Chore dimension. This can be }Chores dimension, but it is better practice to have a system specific “<MyChoreNames>” dimension, as not all chores will need complex controls.
A control cube example is below:
In this example, a chore referred to by the name “NightlyChore” in this cube, has a comment to help the admin know which real chore might be referred to and other information. Since many chores run for months with minimal attention, having such memory aids can be a help to administrators or power users charged with upkeep.
Another sort of comment might be the exact process name in the chore that the index is referenced. Again, this is mostly a convenience to users so they can rapidly identify what chore index drives which process.
This example also has generic parameter names, so a set a processes can be controlled from such a cube. This can imply designing processes with parameters option like “CELLGET parameter = yes” as an option, so the process knows to look to the control cube and ignore hardcoded parameters.
Typical Commands, Hint and Tips, for Advanced Scheduling
- Set On and Off times as parameters in the control cube, use the NOW() function to get local (serial) system time and TIMVL (or TIMST) to convert and extract the hour.
- Use PROCESSBREAK or CHOREQUIT, ITEMSKIP to escape unwanted code execution.
- Create multiple chores. It is often easier to have to have two chores that execute the same processes once a day at different times than run one chore that runs every hour checking for “is it time yet?”. Also, this creates fewer logging entries.
- Set up a Control cube that indicates the day of the month the automatic “data pull and refresh” stops. TIMVL (NOW(), ‘D’) to check the day. “Business day” is often more logical, but given the complexity associated to knowing that, it can be easier/faster to have a user set the correct calendar day each month.
- Last day of the month, in the control cube, use cube/process rules to count down one day from beginning day “1” of next current month in serial time. Similar process updated/driven logic can update the control cube to determine things like the day of the 3rd Friday in a month. The process commands: FormatDate, NewDateFormatter and ParseDate are helpful. Offloading this to a time attribute cube is recommended.
- Processes that load files, should also test for ‘file existence’ (FILEEXISTS command) and move now-loaded files to an archive directory (“Batch files” work well.) As a best practice, all file paths and file names should be parameters in the control cube.
- Priority can be handled by a variety of techniques.
- The proverbial “800lb gorilla” of joke infamy would be the commands ENABLEBULKLOAD () and DISABLEBULKLOAD() they have the effect of suspending all other PA/TM1 activity on the service: users are suspended, logins are stopped, scheduled chores will not run if they occur during the interval, etc. For most purposes this is overkill, but in busy systems where mission critical operations must occur it is an option that could be triggered.
- In a “softer” priority where known conflicts might occur, “run-semaphores” can be useful to force processes to wait on others. Click here for more information.
- Synchronization via the SYNCHRONIZED function (driven by a control cube for the sync objects) to force serial execution is also a possibility.
Almost any client’s desire for advanced scheduling can be accommodated in PA/TM1 with correct implementation and design. However, complex schema’s should be carefully thought through and tested.