Triggering Events with Command Manager
MicroStrategy Command Manager is an external application that has basic scripting components that can be used for bulk operations. While it’s designed to automate common tasks and save time in bulk changes, it does come with the ability to integrate external applications with MicroStrategy Events. This allows you to integrate your process flow from sources such as ETL or source system events to trigger MicroStrategy tasks like Subscriptions, Cache Clears or Intelligent Cube refreshes.
Today, we’ll take a look at setting up Events in MicroStrategy and triggering them externally with Command Manager. But the fun doesn’t stop at ETL, as another use case for triggering external Events is to setup schedules otherwise not supported by the MicroStrategy Scheduler!
The MicroStrategy Scheduler handles all automated tasks inside the Intelligence Server. The traditional schedules are Time Based, which execute on a date/time schedule and repeat as defined. While this is an obvious method for scheduling, it does leave you exposed to a number of issues, specifically, that you could be firing events when the system isn’t quite ready. For example, if you have Subscriptions scheduled to run at 6am, but ETL hasn’t completed because of an error or long running jobs, you could be sending out incorrect data.
Fortunately, MicroStrategy Scheduler supports a second type of trigger, which is an Event Based schedule. For these, you simply create an Event and tie the schedule to it. From inside MicroStrategy Desktop, this is where the journey ends. You can’t do anything with an Event from inside Desktop except right click and trigger it immediately. However, from a tool such as Command Manager, you can trigger that Event and any Schedules that are tied to it. So now, you can create an Event called “Monday”, create a Schedule called “Monday Subscriptions” and then allow end users to subscribe to that schedule. ETL can now trigger the “Monday” event when they’re finished, and the Subscriptions will always go out as early as possible, but guaranteed to have completed data.
While Command Manager is a pretty robust scripting tool in it’s own right, scripts can also be run via a command line interface which is handy for automation tasks. Follow these steps to configure a Command Manager script to trigger Monday Subscriptions once ETL is ready:
Step 1 – Create the Script
Create the following script in Command Manager or even just in notepad:
TRIGGER EVENT “Monday”;
Alternatively, you can bypass the Event and also trigger subscriptions directly per project for extra control:
TRIGGER ALL SUBSCRIPTIONS FOR SCHEDULE “Monday Subscriptions” FOR PROJECT “Your Project”;
That’s it! That’s the full syntax and content of the script. Now just save it as MyScript.scp (or whatever you want to name it) and you’re done. Note that “Your Project” is case sensitive.
Step 2 – Create a Batch/Shell Script
I prefer to create a Batch/Shell script and then execute that script from the Operating System’s scheduler (Windows Task Scheduler or Cron depending on your OS). This gives you an easier place to make tweaks than opening up those tools.
Open up Notepad again and fill out the command line to call Command Manager like this:
cmdmgr.exe -n “Project Source” -u “User”-p “Password” -f “MyScript.scp” -o “MyScript.log”
Note that “Project Source” actually is a Project Source, and not a Project Name. You have to create a project source locally on the server making the call. This means you have to at least install Command Manager locally on whatever box is running this script, such as your ETL box, and you have to go to Start->Programs->MicroStrategy->Tools->Project Source Manager to create a Project Source for this script to use.
User/Password will be stored in clear text in this script file. You can protect the login information by taking a few more steps outlined in this tech note.
In a Unix environment, all of the steps are the same, except the application is mstrcmdmgr instead of cmdmgr.exe.
Step 4 – Schedule it!
This step is largely up to you, but now you just have to execute the .bat file from whatever application you’re trying to hook up to MicroStrategy Events. This can be a Cmd Task in an ETL tool such as Informatica, a command in a .NET application or even a task in the Windows Scheduler/Cron. Simply run the script, and it’s off on it’s way.
The best use case for this it to add a step at the end of an ETL process to trigger Subscriptions or Caches. But it doesn’t just end there. Another one I commonly use is to support Time Based Schedules that aren’t supported by the MicroStrategy Scheduler. The most common one is a schedule that has to run on the last day of the month. MicroStrategy doesn’t directly support that kind of dynamic date, but an easy work around would be to use a more robust type of scheduling tool like Windows Task Scheduler or Cron and have it simply execute an Event Based Schedule itself.
Many years ago, I worked in a 32bit environment that had a very heavy Document/PDF Narrowcast run in the morning. The system would get stressed and run into issues when all of those Documents tried to fire up at the same time. A solution that made it manageable to was handle the schedule queuing myself, so I wrote a simple program that would stagger the schedules by 15 minutes or so once they started. ETL would trigger my program, and my program would trigger the Events via Command Manager staggered every 15 minutes so they didn’t run on top of each other.
Do you have another idea for a use case? Drop it in the comments!
This is great, thank you. I am new to this and I was wondering if you can use the Command Manager to create a package. Right now we manually create a package via Object Manager > Tools > Create Package and are looking to automate that process.
Thanks in advance for any input.
I don’t think you can, but the packages are only XML so you could probably write that out programmatically. May I suggest a presentation I gave at MicroStrategy World 2010 “Improving Object Migration Management with Ease“
Thanks for the information,
But, how about if we don’t have license for command manager can we still do scheduling by event base?
or maybe mstr can get trigger from ETL tools.
(for this case we use Wisdomforce for ETL)
You can only remotely trigger events with Command Manager or by writing an application using the SDK.
This technote shows you how, but you’ll need an SDK license.
Are there any option like 3rd party free software for trigger mstr to run the report when database load is complete?
Because we don’t have command manager license and SDK license too.
Sorry, not that I’ve ever seen. That’s why they sell those tools 😉
Hi Bryan, does Microstrategy support executing .bat file (similar to what an ETL tool would do as mentioned in this chain) after running a job/schedule? My customer wants to automate the execution of a .bat file that will do some processing on the exported files.
Unfortunately, they don’t. Command Manager will complete itself once the trigger is submitted, so you can’t go that route.
Some ideas would be to maybe put some Post-SQL in one of the reports. I think some DBs can execute external applications.
Another would be to have an external application monitor the subscriptions, and it could trigger a .bat when it’s finished (lots of moving parts there).
This comment has been removed by the author.
This is a very useful post. Can you please let me know how to trigger execution of a Narrowcast service after an ETL job is completed?
Thanks in advance!
I have found the script to call NC service:-
TRIGGER SERVICE service_name IN [FOLDER] location_path [SUBSCRIPTIONSET subscription_set1 [, subscription_set2, … , subscription_setn]];
But, Narrowcast Admin (Execution Engine and Distribution Engine ) is installed on a separate server. It is not installed on the intelligence server. I can not add this server as a project source on ETL server or any other server from which I want to call the NC Service. Please advise.
If you’re triggering a Narrowcast Service through the command line, you actually connect to an ODBC for the Narrowcast Metadata, not a Project Source.
cmdmgr -w ODBC_DSN -u Login [-p Password] [-d Database] [-s system_Prefix] [-f Inputfile] [-o Outputfile]
You can check out this tech note for detailed instructions: https://resource.microstrategy.com/support/Authoring/DisplayTN.aspx?tnkey=12318&formatted=1
Your blogs’s Lovely .Your expertise is clearly refleted in your replies.
Thanks Bryan for your response. Sorry for replying so late. Is there any way I can use the web services to fulfil my requirement of Triggering the execution of NC services when an ETL job is complete? How SDK can be used to allow 3rd party apps to send events to MSTR?
All your posts are very informative. Thanks for sharing them with us.
I have a question regarding scheduling client specific reports using distribution manager.
I have a dashboard that displays grids/graphs based on the client name selected (selected through a Prompt)
I have to schedule this dashboard to generate individual PDFs (and save them into a file repository) for every client monthly.
I can do this as specifying these prompts (clients) as defaults one by one and creating a schedule for each. But there are more than 500 clients and this number keeps on changing (clients contract can end or new contract can start).
Is there any better way of achieving this. (maybe use some API or command manager to automate the subscription creation process for all the clients)
Thanks a lot
Command Manager doesn’t have an API for answering prompts yet unfortunately. What you really want here is called Mid Tier Slicing, and it’s one of the most powerful features of Narrowcast. Distribution Services is in the process of replacing Narrowcast (so both are still around), but it hasn’t reached feature parity yet. One of the biggest things it’s missing is this Slicing capability. Unfortunately, the only way you can do that dynamically would be to build it as a Narrowcast Service.
Thanks a lot for your reply. So should I use Personalized Report Execution with dynamic subscription set in narrowcast server. Can you please let me know the detailed steps on how to achieve this.
It’s been many years since I’ve done it. I don’t recall the exact steps and I don’t have Narrowcast installed any more, but there’s plenty of MicroStrategy resources to walk you through that. The best bet is to check the Narrowcast Administrator Guide, but this tech note looks like it contains most of the steps too:
PRE or PPE..both needs creation of n number of users for n number of reports because only one attribute or prompt answer could be selected for each user. In my case a user may have to receive all reports to him or store them in server. But narrowcast server provides only one attribute to choose for one user.
Can the user create multiple dynamic preferences so that all the reports are sent to the same user?
Is there any option of using service_name instead of ODBC for metadata database?
I need to call command manager from a unix server.
mstrcmdmgr -w ODBC_DSN -u Login [-p Password] [-d Database] [-s system_Prefix] [-f Inputfile] [-o Outputfile]
Please let me know. Thanks a lot in advance!!
I’m not sure what you mean by service_name .. do you mean an IServer? You would connect with -n Project_Source. I believe -w ODBC is only used when using Command Manager to connect to Narrowcast, though I couldn’t find documentation that said you *can’t* use -w ODBC for a metadata connection.
Please disregard my previous question.
I have installed command manager on Unix (HP-UX).
When I am running the mstrcmdmgr command from home_path/bin I am getting following error:-
interpreter “/bin/bash” not found
file link resolves to “/usr/bin/bash”
ksh: mstrcmdmgrw: not found
I changed the #!/bin/bash to /usr/bin/bash in mstrcmdmgr file, still it is not working.
Any idea what’s wrong?
How we Invoke Microstrategy Scheduler from DotNet & Shell script ??? please explain it.
The above article tells you exactly how to do it. Using Command Manager is the easiest way, just setup a Command Manager script and trigger it using the command line utility.
All your posts are very informative. Thanks for sharing them with us. I have a question ,Iam not able to create a Project under Project Source, when I’m trying to create it,showing an error message that this this object can’t modify as this is using Administrator.I have tryed with different names.Please help me in this issue.Thanks
Check out this technote that discusses the different kinds of Locks in MicroStrategy. Also, things like Object Manager and Command Manager can create locks, so check to make sure those tools are closed.
Is there a way where I can write command manager script to list all the users (in a single metadata) who have missing “WHLink” or “Turted Login” paraeters and automate the script to run, say every 24 hours? Thanks – Sumit (MSTR Admin)
Yeah, you’d have to write it as a procedure. You could model it after the Tagging User Emails post. Basically, list all users, iterate through them, check if those fields are blank and then set them if they are. you can’t quite get away with the lazy script in that post, since I just try to create emails for everyone, letting existing emails just fail out. In your case, I think it would modify the links and you may not want to do that if the links don’t match your patterns.
For scheduling it, just throw a trigger in a batch file (as mentioned in this article) and set it up with Windows Schedule or Cron.
Thanks for your prompt reply!
I have sent a separate E Mail to firstname.lastname@example.org with all the details. The users in this case are all “External Users”. Not sure how to modify the script or compile/execute it as I am very new to MSTR. (Has worked only for 3-4 months as MSTR Admin). Can you please go through that E Mail and let me know the apt procedure to update WHLink and TRUSTEDLOGIN parameters?
Will this work:
lResult = execute (“ALTER USER “” + lLogin + “” WHLINK “” + lLogin + “” TRUSTEDLOGIN “” + lLogin + “”;”);
Just wanted to veirfy before running it against PROD metadata.. 🙂
Brillian as usual. I’m struggling to work out the best way to handle failures. What is your best way to handle errors through CM? Once you launch a Cube refresh (trigger the associated event) is there anyway of knowing if the cube successfully refreshed? I don’t see in CM how to get a success flag or a Last Refresh Date.
There’s a setting on the subscription itself to send an email in the event of a failure, so I use that. If it fails, I’ll get a notification and can then take action.
In my case, I need to know at what time does each ICube in my platform finish publishing /refreshing or fails. (and I need to have this information almost real-time). The way I’m doing it right now is querying the statistcs database but I would like to know if it is possible to obtain this information within the Command Manager script that tiriggers the event or runs a PUBLISH INTELLIGENT CUBE sentence.
Thank you very much for your help.
To my knowledge, it is not possible, but it’s been a long time since I’ve looked into this stuff. (Though not many versions have come out since then, so probably still can’t).
Where I can get a list of commands that are supported by the command manager? Where can I find the documentation for command manager?
In Command Manager itself, there’s an icon in the toolbar that says Insert that will give you templates and examples of every command it supports.
Please let me know is it possible to compare (Version id, object id, Expression and form) of schema or public objects of two different environments. Also please let know is it possible to import these object list with use of excel sheet to compare.
You may be able to write a Command Manager Procedure to do this, check out this example which gets a Metric Expression as an example. You could write something similar to list out all of the object details in an environment, export it to excel, and join it with the same output from another environment. CM won’t compare them for you, but you could do that analysis yourself in excel.
Thank you very much….
Hi Bryan, I am working to find out a way where we can change the “DBCNAME” parameter in the ODBC.ini file (located on the physical IServer). A typical entry for a DSN in odbc.ini looks like –
I need to change the from command manager.
Do you know of something related on how can we do that?
I need to change the DBCNAME=”” from command manager*
I don’t think it’s possible.
We have multiple members in our BI system, so we need to have a way to such that for each and every member data load completed an event to be kicked off. For this as of now we created one event for each and every member, so this amounts to 50 events for 50 members and 50 schedules.
Is there any way where we can limit creating these many events and schedules ?
You can trigger individual subscriptions, so assuming you only have 1 sub to run from each event, you could just call them individually from CM. If that’s not the case, I don’t think there’s anything else you can do.
Excellent post, I’m faced with this issue:
1) Command Manager and I-Server are box A
2) SQL Server and ETL is on box B
3) According to your post CM has to be on the same box as the ETL process in order to trigger the event.
4) DBA will not allow the installation of CM on SQL Server box
5) Is it possible for me to do what you suggest in this scenario.
If this sounds silly, please let me know, I’m new to MSTR and am using your blog for education.
Yes, it does sound silly. Why wouldn’t your DBA allow Command Manager to be installed on the SQL Server box? It’s a pretty small program and doesn’t run in memory other than when called from the command line.
The only other way to do it is to have ETL update a table with a status, then have a separate process on a different box monitor that table for a change, which would then trigger Command Manager and update the original table so that it doesn’t trigger a 2nd time. Or just install CM on the SQL Box!
Bryan – Yes, that second option is what we are using currently. using the pre and post sql’s in a service we test the status of the flag column and accordingly control various ncast services. But unfortunately there is no way to stop the scheduler tied to a service after the completion of all the services. There is a crude way of deactivating the scheduler by writing an update statement on the metadata which has the scheduler information (like the scheduler ID). Is there any other way you suggest or is there a command in command manager which monitors the current session (or services that are running).
It’s been a very, very long time since I’ve used Narrowcast, but you can use Command Manager to TRIGGER SERVICE and you don’t need a scheduler at all (unless I’m misunderstanding your question). I had a situation a long time ago where I had to stagger some schedules, and I did this by writing my own scheduler and invoking them with command manager. I did a presentation about it (http://www.slideshare.net/BryanBrandow/world-2008-administering-narrowcast-server) towards the end, it was called Narrowcast Triggers.
Bryan, do you know where I can find some samples and tutorials for Command Manager?
Fantastic post Bryan….This solved many of my doubts in event based scheduling.
Triggering a service or a schedule appears to be fairly straightforward, but how can we generically trigger Narrowcast in general to run all of the relevant schedules from SQL Server, without re-defining that logic twice? For example, we have schedules that run daily, some that only run on Friday, some on Monday, some on the 16th of the month, 2nd of the month, etc.
Rather than defining the logic twice, is there a way to trigger narrowcast to run all relevant schedules based on the run date?
I want to execute my all reports once the Datamart is published successfully. I want to do this because, once my datamart will published or executed successfully I need to cache again my reports.
So can you please help to perform this task?
Is there any scripts in which I can create a file subscription automatically. The data will come from an excel file. Thanks in advance for your help.
I have one domain in which i have 2 servers, both have MicroStrategy installed.
Just like we do import package from one server to another using the command manager, do we have any command to restart intelligence server also?
Thanks & Regards,
In Command Manager, the command is: RESTART SERVER IN “ServerName”;
I came across your wonderful post while browsing for an issue that I am facing with scheduling report status. Could you please help me solve below problem?
I have 4 event based schedules each triggered by an event one after another with a gap of 15 minutes and each has hundreds of reports but I want to make this dynamic. For ex: kick trigger 2 only after all the reports kicked with trigger 1 are completely finished executing. Q: How could I get the status of reports kicked within a schedule? How many executing and how many waiting? (I can manually see this info in Job monitor but need to automate this process)That way I can kick triggers one after another with the completion of each set of reports and it allows me to notify business after each set is finished. FYI, I have stats enabled.
You could build some external system if you really, really needed batch 1 to finish before batch 2, but my guess is you have governing issues with sending a high number of jobs to the server at once. You could set your subscriptions to a specific report cost or priority () and then only set X number of concurrent jobs so that you can just funnel them all through without overloading the server.
If you really do need absolute control, it sounds like you have something already firing events. You could add logic there, but there isn’t a clean place to check. If it’s in Java, you may be able to literally poll the server () or maybe you can query the IServer stats to see if a schedule has completed the number of jobs it originally had scheduled (I don’t remember if the IS_Stats shows you the schedule it was attached to). Either way, you’re going to be building a really convoluted system that is going to be prone to failure, so I’d just consider why you need your schedules to execute this way and if there are different ways to solve the requirement.
Thanks Brian for your quick reply. Sorry for my late reply as I was out for some days.
I am using Prioritization using groups. For Ex: Reports from SysAdmin group always go with High priority and then Group1 reports go with Medium and Group2(everyone else) go with low priority. I tried with sending all reports to ‘Low” priority threads that way first fired event reports will run first but that did not work cleanly. Do you think I can as well use report cost also in Prioritization? And this needs to done for all reports and report by report right? Also, I have to send only certain number of reports to database so that it will not overload DB.
The main reason I go with approach is, we have tons of (>2500) subscriptions (both History and Email) to process early morning and some of them are of high importance for business and they cannot wait until all are finished so I don’t know if there is better solution to this problem. Could you please suggest me any alternate ways to solve this?
You could use Report Cost to set your critical reports to be High and put all other subs at Low. They’ll all run as they can, but your High ones won’t get caught in the queue. Other things you could do is institute a clean up (I promise you 2500 subs aren’t being read) or try to hook up subscriptions to ETL completing instead of time based schedules (you have to build this yourself, but a simple mechanism is a signal table that ETL flips a flag in, and a cron job that looks for that flag and calls a Command Manager script to fire the sub).
Great, Thanks Brian I will follow these steps. Just a quick question from your reply. How can I find a unread subscription(history/email)? using stats tables?
There’s no way to find an unread email. I’ve never tried finding a read history list, but it might just be looking at the execution stats and seeing if there is any indicator in object_type or something. It may just consider it a report. One way I’ve handled emails is to check for reports that aren’t being run interactively, then ask the recipients if they are using the email (probably not). Another is to make sure you have a real mailbox setup as the sender of the subscription, and check it for bounce backs (people who have left the company tend not to unsubscribe).
Right, Thanks Brian. I am using a tech note (TN39909) to identify unread history using stats. Also, our batch is scheduled via our enterprise scheduler which also checks ETL completion to fire events.
I want extract the User information through command manager. I am able to extract excel file manually from Command Manager GUI tool, But I need this excel through automated Script.
cmdmgr -n “MicroStrategy Analytics Modules” -u “Administrator” -p “” -f “Untitled1.scp” -or “Results.xls”
I am getting the excel file, but formatting is not in the way that we get through Command Manager GUI tool. I have attached both the reports ( from script and Command Manager GUI application).
Can this be accomplished using script ??
Could you please suggest, let me know if you need any further information on this.
Thanks in Advance
Can you please explain in detail the ‘Step 4 – Schedule it!’ in your post on how the batch file can be created so it can be called from a .net application. This could be useful for my current situation in my project where I am trying to refresh the cubes from a .net page.
The batch file itself is described in Step 2 or you can just call cmdmgr.exe directly with the appropriate flags (I personally prefer something like batch because you don’t have to make code modifications to change the script). From there, it’s just whatever your normal course of executing a command line in your application. Here’s an example in C#: http://stackoverflow.com/questions/206323/how-to-execute-command-line-in-c-get-std-out-results
Thanks for your tutorials.
There is some way to check job execution without the app job monitoring of microstrategy. With command line for example?
Great post. I’m currently trying to figure out how to do the following:
I need to be able to send a scheduled report to every member of a list of staff (that we can maintain in a table). Each report needs to be specific to each staff member and we want to have the report emailed to this particular staff member. So if I had staff members A, B and C, I would need something that sends 3 different reports, one to staff A, one to B and one to C. The staff name / email (or whatever else unique) will obviously need to be passed in as a parameter into the report to make the report unique to the staff. I’m just stuck on how to automate this so that I can iterate through this staff list and for each staff, pass in the staff email as a parameter into the report as well as for the recipient of the report and then execute the report + email the report to each staff. Would this be best achieved using techniques you’ve described here using the command manager?
I want to alter metric names every week from a database table. Is it possible using command manager ?
i subscribed one dashboard daily 7am , but the user getting duplicate emails i dont know how they are getting duplicate emils.
the users are getting same mail twice.
can you please any sugestions.
Appreciate your great work.
Can you please give some starting point to write a program that would “stagger the schedules by 15 minutes or so once they started. ETL would trigger my program, and my program would trigger the Events via Command Manager staggered every 15 minutes so they didn’t run on top of each other.”
In Distribution service subcrptions.