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.

Administrator, Consolidations, VLDB 9.0, Consolidation, Global Optimization, Microstrategy, VLDB

…also known as Things I Learned The Hard Way – Part.2

A Consolidation is a great way to visually group data…but there are MANY problems that consolidations can create.

Specifically, an over-zealous pivoter who doesn’t understand the finer points of MicroStrategy can make a real mess of things. Consolidations are great for making income statement, balance sheets, or any drillable report that has specific subtotal or formatting requirements. Below is a standard consolation.

We have out various elements, and some subtotals that we want to see. On the image below, if I add the account category that drives those subtotals, I add them to the Grid. So now you see how the Consolidation actually treats each element.

Now, if we pull off the consolidation, look at what happens to the “Account Category” (ignoring the sort order). The new dynamic totals created are no longer accurate, they’re the sum of each item, plus every subtotal that the attribute appears in.

Here’s what the report should look like. Look at the Object Manager on the left to see that the consolidation has now been removed from the report objects….and…Tada! The correct results.

So, make sure that your Web Pro users know that they have to be very aware of the elements that are in their reports before they start ‘OLAP’ing themselves into inaccurate data.

Consolidations, Things I Learned Consolidation, Microstrategy

What do you do when you want a negative to appear as a positive mathematically? Two options if you’re using a program like Excel. Throw a negative sign in front of it, or, multiply by a negative one (-1). Regrettably, that doesn’t work with Consolidations. Try to throw a negative in front of a number and you get this:

Consolidations don’t like negative numbers. The solution?

* (0.0 – 1.0)

Multiply any numbers that you want reversed by the above formula. In effect you’re “clarifying” what a negative number is, without confusing the system by throwing a ” * ” right next to a ” – “.

Basic Series, Consolidations, Tips and Tricks Consolidation, Microstrategy, Negative