How to trigger a MSTR event after a database table is loaded? 1

[sunnyphoenixier]

I am sure many of you guys have done this before. I am trying to expolore some options here. The request is that once a tablebase table is loaded, for example, one indicator column is set to 1, a MicroStrategy report needs to run.

One way of doing it is to schedule a Command Manager Job after the ETL job immediately to trigger a MSTR event. Are there any other options you guys have used.

Any suggestions will be greatly appreciated.

Thanks,

Phoenixier

 

[petperson]

The method we use works well. Here's how I have it designed:
1)A job runs a SQLPlus query looking for the 'flag' that gets set when a table has completed loading. For example...select count(*) loading_flag = 1 from DW_LOAD. The batch file looks for the result set = to zero meaning all tables have been loaded. When it finds a zero, it spools the results to a text file.
2)Using NT scheduler, there is a batch job that runs every 5 minutes looking for this 'results' text file. When it finds the file, the batch job continues on and runs a 'fire event' on the IServer. With this event based schedule, I have daily reports scheduled to it, so within 5 minutes of the DW load, we've got reports running.
3) taking this to the next step, I have several of these batch jobs scheduled at incremental times in order to control the load on the IServer. some of the batch jobs have the 'SLEEP' utility built in so the jobs will sleep before the next one kicks in.
4)To involve Narrowcast, some of these event based schedules fire off Datamart 'trigger tables' that Narrowcast services will look for in a PRE SQL statement. If the NC service finds this 'table'; it drops it and service continues. We have these NC services set on a re-ocurring schedule to run every few minutes as well. The next time the service runs, of course it will fail as the table has been dropped.

PRO's: The system works well under 'normal' daily loads. I have many different event based triggers running to kick off reports after the load of specific business areas, ex. as soon as SALES is done, then sales reports start running..etc.
CON's: If the DW load extends beyond the times of the NT schedules running, some manual intervention is required. NC Adminstrator can tend to get bogged down with all the re-occuring schedules popping in and out during the window the schedules are set. (Have logged an enhancement request for a 'run til successful schedule' to alleviate this). The logs also get filled with many failed services due to the pre SQL statement looking for a table.

All in all, this works well for us and we are fully automated with jobs running within a few minutes of the DW being loaded.

Hope this helps...

p.p.

 

[sunnyphoenixier]

petperson,

That's a wonderful reply. Thanks!

Your NC approach sounds especially creative. It may be an apporach that we can adopt in our case. However, I am curious about what impacts on the NC server it may impose that having a schedule runs every 5 minutes. I guess we can test it out.

Again, very helpful tips. Thanks!


Phoenixier