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.

I deal a lot with Consolidations, as any reader of this site will know, however, it seems the the MicroStrategy universe is a bit adverse to them…

Looking for explanations as to why? Is there a better way to be doing things? Are Consoldations just generally a no-no that lead to bigger problems?

Comment people…I’m actually soliciting opinions on this one.

I’ve been playing around with MicroStrategy 9 on my test box at home, and easily my favorite function, and the one that I see the most potential for, is Derived Elements.

A couple of quick tips. You can’t just make Derived Elements on any report…you have to create the report that you want the make the Derived Elements off of a predefined “Intelligent Cube”. Now I’m going to assume that if you’re looking into this as an option, I don’t have to walk you through babysteps, but of course, I’m willing to address any questions you might have in the comments, and if not me, MicroStrategy themselves is pretty good about picking up on what I miss.

So, step one. Create an Intelligent Cube. Make sure to include every attribute and metric in the cube that you might want to have access to. Once the cube is created (it’s essentially the same as creating a report, just without organizing the elements on a grid) right click on the new cube that you’ve created and set up when you want this cube to be created by selecting -> Schedule Delivery To -> Refresh Cube.

In the following screen, you can set the cube up to refresh based on any schedule that you’ve created via your Schedule Manager.

Now, create a new report, but instead of selecting “blank report” under the General Tab, select the option below.

One of the things that I like to do is track video game review scores online, because there’s plenty of attributes and hierarchies that just exist (genre, publisher, developer, etc), so it create for a pretty dynamic set. So here’s a report that I created using the new Intelligent Cube that I created.

But say I want to group what i consider “Next Gen” into it’s own category, and then have a category for everything else. Next Gen, as I’m defining it, is going to be PS3, and XBox 360, and group all others into “Other”. There’s also an “auto” group that basically means that anything you don’t select, is going to be it’s own item.

So, once these derived elements are created…what do you get?

Note how the attribute you made the derived elements from pretty much as the “consolidation” symbol over it now.

So here’s some other things you can do with this new found toy.

• Save the derived element for later use.
• Make calculations off of the elements you’ve created (A+B=C)
• Create Specific Filters for line items.
• Web Functionality almost exactly mimics Desktop functionality (where my screen caps are from).

Really, what this allows for is the ability for users to group data that is relevant, into ad-hoc groups depending on what their immediate needs may be. This allows for possible groupings of variance analysis categories against budget (imagine grouping one product under “low inventory”, and the other under “poor customer repsonse”, so that you can make dynamic analysis more relevant to “now” reasons).

Kudos to MicroStrategy for being able to internalize one of the main reasons that organizations have to export their data into external programs such as Excel…which to date, offered the easiest way for individual users to manipulate data to give it their own flavor and meaning.

…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.

The quick answer? There’s no easy answer, in my experience.

Here’s the two ways I’ve used to work out sort issues.

1. Consolidations – This is a great option if you’re the guy who builds all the reports. You don’t want you’re user base to have to have to drag a consolidation into a report every time they want to sort it…on the other hand…if it’s not an attribute you’re using all the time, then this might be for you.



2. Data Warehouse – This is the more “Elegant” approach. The idea is, in your warehouse, you add a column in addition to the ID and DESC, in this case ACCOUNT_GROUP_SORT, and create a new Form. Tag it as the default sort column with “ascending”. I usually also have the field as viewable, just because many of your users will say something like “see line 12″, and it’s a better visual search key. The downside with this, is you have to actively maintain this column, either manually, or with the ETL process.



Either way, there is no easy permanent fix. Both require maintenance and care, but ultimately make your report more readable.