Microstrategy Blog

Having recently upgraded to MicroStrategy 9.0, I’ve been forced to reanalyze a lot of reports that “just worked”…prompted of course by angry end users whose report generation moved from 20 seconds to “Error – Time Out” at ten minutes.

Why didn’t I catch this in development? Well, when I did all of my testing, as a full access desktop user, I don’t have any SQL Security…and that changes everything. As I’ve said before, I’m a heavy user of consolidations (see here), add when you add these to multiple metrics (all full outer joins), multiple filters, etc…sometimes, things can go wrong, especially when you have SQL Global Optimization with MicroStrategy 9.0. Consolidations are fickle things…and the more complicated that you make them, the more that can go wrong.

First step is to check your SQL, and thanks to MicroStrategy Technical support (big thanks!), I was able to learn something new…if you see the metric name GODWFLAGc_1, then you pretty much know that your SQL optimization engine is kicking in; you can change it.

In your VLDB Settings, check Query Optimizations…in my specific instance, I had to change the Global settings (see pic) to force the SQL to work properly, line by line. The more it tried to fix the SQL, the more it broke it. Hey, it’s not perfect…but it gives you as the ability to tweak it yourself.

NULL.

Some people shudder at the idea. I say “BRING IT!!”.

A lot of my metrics are built as full outer joins, because I deal with a lot of nulls in my business…sometimes the data just doesn’t exist. The downside of nulls, is if you have a report that you want your subtotals to be “Average”, the analytical engine by default treats nulls as Zero. Enter VLDB Settings. The intimidating, official looking side of MicroStrategy report development…

Here’s what I had.

See the problem…those totals aren’t really “averages”, at least not in the way that I like.

Under Data >> VLDB Properties, change the settings so they match the below image.

What you’re doing is telling it to basically ignore those nulls when it comes to Post SQL analysis, and forces it to change the behavior of your dynamically generated subtotals. The result?