Trigger MicroStrategy Events from ETL

Use Case:

You have multiple MicroStrategy iCubes that you want to be able to refresh from your ETL process, which would allow you to refresh them only when certain criteria have been met (like after data load has been successfully completed)

Using the Command Manager tool you could refresh each cube separately, but it might be easier to have all cubes refresh when a specific Event is triggered.

Steps Overview:

1. Create a new Event in MicroStrategy 
2. Create a new Schedule
3. Subscribe cube refresh to Schedule
4. Create Trigger Event script using Command Manager (CM)
5. Create batch file containing CM commands

Steps Detail:

1. Create a new Event in MicroStrategy

Administration >> Configuration Manager >> Events

Right Click “Events” >> New >> Event

Change the name of the event to something meaningful. Keep in mind that you can link several jobs to a single event.

2. Create a new Schedule

Administration >> Configuration Manager >> Schedules

Right Click “Shedules” >> New >> Schedule

Go through the wizard and make sure to set the Shedule to “Event-triggered” and choose the appropriate Event. (This screen also allows you to create a new Event on the fly)

3. Subscribe cubes to Schedule

For each cube that you would like to link to the Event, right click the cube object >> Shedule Delivery To >> Refresh Cube

Give the Subscription a name (Although you never have to reference the name of the subscription, it does help to maintain a naming convention that makes sense)

Choose the Schedule created in step 2 from the drop down

4. Create Trigger Event script using Command Manager (CM)

Open MicroStrategy Command Manger 

Connect to the appropriate iServer

A new script window edit should be displayed. If not, click on File >> New Script

Right click inside the script editor window >> Insert / Search Objects >> Outlines

Find the “Event_Outlines” folder and expand. 

Select ”Trigger_Event_Outline” 

Every outline shows samples in the bottom window. Be sure to look at what the Trigger Event command should look like.

Click “Insert”

Delete the <event_name> part from the script and type in the name of the Event you created in step 1. Your Script should resemble this: 

TRIGGER EVENT “Event Name”;

You can now save the script somewhere as a .scp file

5. Create batch file containing CM commands

Open a windows Command Prompt window

Execute the following command:

cmdmgrlt.exe –help

This will display the way the batch file needs to be constructed

Our batch file will look as follows

Cmdmgrlt.exe –n “Project Source Name” –u “User Name” –p “Password” –f “file created in step 4.scp”

For those who don’t know batch files:

• Open Notepad
• Type the command above into notepad
• Save the file with a .bat extension instead of .txt 

All you need to do now is run the .bat file from your ETL Process

PS.

The cmdmgr executable will only run on the machine where Command Manager has been installed.

If your ETL process runs on a different machine you can install Command Manager on the ETL machine or use PSEXEC  (http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx) to execute the batch file remotely.