[MSTG] Using MicroStrategy Scheduler

 

Using MicroStrategy Scheduler

Scheduling Reports based on time and event

Reports can be schedule based on time or event using MicroStrategy scheduler.

Event based Scheduling: Lets assume we have a status table (name: load_status) in our warehouse which indicates an event of a fact table data load. We wanted to execute a few reports based on the event (when it turns '1').

Command manager script

 
Create a command manager script (name:fact_load.scp) with a "trigger event" command. This will be a .scp file with the following command (assuming "FACT LOAD" is the event name)
TRIGGER EVENT "FACT LOAD";

Shell Script

 
Create a shell script (name:face_load.sh) which will have the SQL to check the status table (load_status) for a particular event (in our case - '1'). If the status is 1, the shell script will run the command manager script (fact_load.scp). Upon successful completion, a post SQL can be included to change the event to '0' in the load_status table. Below is a sample shell script-

export PATH=:.:$PATH

CONNECT=CONNECT PARAMETERS
# get  part_date
sql_return=`$CONNECT << EOF
   set pagesize 0
   set pause off
   set feedback off
   set heading off
    select job_status
    from load_status;
EOF`
echo sql_ret is :$sql_return:
echo  job_status is :$job_status:

 if [ "$job_status" == 1 ]; then
  echo " going to call trigger"
 cmdmgr -n "ISERVERNAME" -u administrator -p PASSWORD -f "fact_load.scp" -o "fact_load.log"
  echo "finish call to mstr"

   sql_return=`$CONNECT << EOF
   set pagesize 0
   set pause off
   set feedback off
   set heading off
 update  load_status
    set job_status=1;
    commit;
EOF`

  else
   echo sleep for some time
  fi

exit 0

Batch File
Create a batch file "fact_load.bat" which can trigger the shell script, fact_load.sh

Windows Scheduled

Create a windows scheduler which will run the batch files on a regular interval.

MicroStrategy Scheduler

Create a MicroStartegy Scheduler to executes reports based on the event "FACT LOAD"

Administration > Schedule Manager 

Right click > New Schedule

Once the schedule wizard opens - Click "Next"

Enter Name and Click "Next"

Select "Event Triggered" and click "Next"

Enter "Start Date" and Select "No End Date". Click "Next"

Click on "Events"

Click on the top button of the wizard which will allow us to create a new event
Enter the new event name as "FACT LOAD"

Click "Next" and then "Finish"

Right click on the new schedule we create and select "Schedule Multiple Reports"
This wizard should allow you to select the project, report and the user/user group to which you wanted to associate.

Creating a time triggered schedule is similar. Just that "Time Triggered" should be selected instead of "Event Triggered" in the wizard.