Do you have resource heavy SQL queries running from MicroStrategy and can't easily determine which user is consuming all of those resources? You're not alone. This is how I solved it.
In one of my past projects we had issues with available system resources. One of the bigger annoyances was that we could see on the database side that is was typically a MicroStrategy report based on the SQL and database user . Although we knew the source of the issue it typically took some time to track down which user was running the report, if it was a self service report, or what is was they were trying to do in general.
In this MicroStrategy environment we had prescribed reports, documents, and dossiers but it was also a self service environment. The “self service” was setup so that a user could log into MicroStrategy web and would have access to create a report with a specific set of Metrics, Attributes, Filters, etc. This environment would have a peak average 200,000+ SQL queries per hour (about 55.5 per second). This was also a clustered Intelligence Server environment (5+ I-Servers). This made it hard to review the jobs per I-server which would take more time than was acceptable. .
To easily track which user was running the long running SQL and which report they were executing we took a few approaches. First, we has to identify the SQL running and resources being consumed. Since this was an MS SQL server environment we used SP_WhoIsActive to see the SQL queries running and the resources. If you or your designated DBA hasn't installed this Stored Procedure, it can be found at http://whoisactive.com/. It has some cool parameters which allow you to get the execution plan along with some other information. Documentation on this specific Stored Procedure is at the download website.
Now that we can easily see the SQL being run at any given time, we needed a method to tag the SQL with the MicroStrategy username and the report being run. Luckily, MicroStrategy provides a few key wildcards which can be inserted into the VLDB settings to influence the SQL.
The MicroStrategy community links frequently change so I’ve added an image at the bottom of this post with some of the key wildcards. The two wildcards that we are interested in are;
!u : Username
!o : Reportname
With these wildcards we now have the ability to add in the username and report name directly into the SQL. These can be placed in many of different places in the VLDB settings as long as they’re commented out (or they'll produce an incorrect SQL syntax error). I found it most useful to add them in the “hint” which comes right after the select in the SQL. This way the username and report are right at the beginning of the select statement so you don’t have to dig far into the code. It’s also a good idea to set this at the project level so that every select query includes the username and report.
Like this;
/* Username: !u - Reportname: !o*/
Let's take a look at a configured report.
Now let see how this looks when checking sp_WhoIsActive
Now if there is ever a query which is eating system resources, you’ll easily know which user and report are the source. Of course this is a reactionary approach but is one of the many tools I've used to quickly identify resource heavy reports and the users running them. In a later post I'll discuss how to monitor for long running queries.
As noted above, the MircoStrategy wildcards below.
Thanks for checking out this blog post. If you found this post helpful please consider donating. Any contribution is appreciated! Just click the PayPal icon at the bottom of this page.