MicroStrategy: Event Triggered Intelligent Cube Refresh

I recently had a need to trigger Intelligent  Cube refresh based on a certain event. That certain event was ETL completion. My ETL writes begin and end times to a log. I could easily have a service run that would periodically monitor the log and refresh the Intelligent Cube.

That seems to be too much of an overhead when I can simply call the Command Manager to execute the cube refresh from the ETL.

Image Source: https://www.rubiks.com/solve-it/3x3

You will need to create a MicroStrategy Event, MicroStrategy Schedule, schedule Intelligent Cube Refresh, create Script file, create Batch file and that pretty much sums it all!

Create an Event

Select New, Event and name it – Events are an empty object - as in they do not do anything, it is just an empty shell. Call this event a proper name. In my case, it is aclled Batch Cube Refresh - Short Form.

Create a Schedule that uses the new Event

Select New > Schedule

Name the schedule, in my case 'Batch Cube Refresh'

Click Next and select Event Triggered

Click Next and assign appropriate Start and End date/times

Click Next and Select the event to Trigger (one that you created in previous steps)

Click Next and then Finish

Schedule The Intelligent Cube To Be Refreshed

Right click on the Intelligent Cube and select Schedule Delivery To > Refresh Cube

Under Schedule, select the schedule name that we created above

Batch Statement

Next, we need a script for the Batch file (.bat) to run from SQL Server. This batch file will execute a Script (.scp) via Command Manager. Note: You might need to install Command Manager on the server/machine where the batch file will be executed.

Script File (.scp):

CONNECT SERVER "BizIntServerNameHere" USER "Administrator" PASSWORD "P4ssw0rdHere";

TRIGGER EVENT "Your Event Name From Above Steps Here"; 

DISCONNECT SERVER;

Batch File (.bat):

cmdmgr -connlessmstr -f "\\Fully Qualified Path\ScriptFileName.scp" -o "\\Fully Qualified Path\Output FileName Of Your Choice.log"

Executing Batch File

Execute the Batch File that we created above as the last step of your ETL process.

SUCCESS!