Schedule sequential ElastiCube builds using windows task scheduler

[intapiuser]

In many cases we would like to schedule our ElastiCubes builds to run one by one, using just one command, or to run in a specific hour so our users won't create or watch dashboards while the cube is building and also will have recently updated data. 

Although you can schedule a cube to build in a specific hour through the scheduled build settings in the ElastiCube Manager, this post can help you to define more complex and specific terms to run the build. For example building all your ElastiCubes in one process and determining a specific hour for the build.

Step 1: Copy the script and save it as .bat file

This script contains a few steps to activate a cube build.

The following lines are the commands to build the cubes.  Add a new line for every ElastiCube you want to build. It is crucial to add the "/wait" part so every command start in its turn only after the previous is finished.

@echo off

start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\<My_Cube_A>.ecube" serverAddress="LocalHost" mode="restart"
start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\<My_Cube_B>.ecube" serverAddress="LocalHost" mode="restart"

@end

For more information about available command parameters, please see our PSM documentation at https://sisense.dev/guides/legacy/psm/#sisense-shell-psm-exe. Using the PSM commands, you can build from either a .ecube file or an existing ElastiCube.

Example to build from a .ecube file:

start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build filename="C:\Users\Administrator\Documents\My ElastiCubes\Northwind.ecube" serverAddress="LocalHost" mode="restart"

Example to build from an existing ElastiCube in the ElastiCube Server Console:

start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build name="Northwind" serverAddress="LocalHost" mode="restart"

Here are the available build types:

1. mode="restart": Rebuilds the ElastiCube entirely.
2. mode="full": Rebuilds the ElastiCube and accumulates data for tables marked as accumulative. This mode should only be used for accumulative builds.
3. mode="changes": Rebuilds from scratch tables that have changed in the ElastiCube schema.
4. mode="metadataonly": Updates the ElastiCube server with the ElastiCube schema, without building.

Step 2 : Creating a new task

Open the windows task scheduler and create a new task. Name the task and set security filters as followed.

*note that the user account running the task MUST BE AN ADMINISTRATOR USER

Step 3: Setting the Triggers

In the triggers tab create a new trigger and define the time to activate the command, this can be an interval for every few hours or run on a specific hour. In case you want to run the task a few times a day in predetermined hours, for every build occurrence create a new trigger. (i.e one occurrence in the morning and one at night).

Step 4: Specifying the build action and finishing

In the actions tab create a new one and choose "Start a Program" action, then choose the .bat file you created.

Edit the Condition and Settings tabs as required (optional) and hit OK.

Test the task to see if it's working by right clicking on the task and choosing "Run".

In cases you want one cube to run every hour and another cube only once a day at a specific time, and don't want the builds to run in the same time, you can create a task with the following script to run every hour, with a loop that contains a time window to run a second cube. the following script will run CUBE_A every time the task starts, but CUBE_B will run only if the time is between 4pm and 5pm:

@echo off 

start /wait /d "C:\Program Files\Sisense\Prism" 
psm ecs stopstart /wait /d "C:\Program Files\Sisense\Prism" 
psm ecs startstart /wait /d "C:\Program Files\Sisense\Prism" 
psm ecube build name="CUBE_A" serverAddress="LocalHost" mode="restart" 

set "now=%time: =0%"  
      
if "%now%" geq "16:00:00,00" if "%now%" lss "17:00:00,00" ( 
start /wait /d "C:\Program Files\Sisense\Prism" psm ecube build name="CUBE_B" serverAddress="LocalHost" mode="restart"
)

@end 

In the “if” line you should change the time window to run the other cubes.

Add a line for each cube, add a new loop for every time window (notice the parenthesis)

**NOTE: if a build fails for some reason, the following ones in the script will not run.