Microstrategy Blog

While the merge with Object Tracking Tool is still in development, I am proud to introduce the new and improved version of Metadata Browser, which you can download here: MBsetupV2.0.exe

Improved Documentation Tool
New properties are now retrieved for common object types:

• Metrics: dimensionality, condition, transformation, smart (yes/no)
• Attributes: form, lookup table, child & parent relations
• Filters: expression
• Prompts: item list

Change Journal Tools
This new function only works with MicroStrategy version 9.0 or higher and it either shows the Change Journal entries for a single object or for all the objects in a particular folder.

God Mode
If you have admin rights you can connect using “God Mode” and thus gain access to hidden system files. An interesting use (found during the test phase) is to navigate to System Objects\Columns and make dependency searches directly on Columns.

This is a read only application. Using it won’t cause any changes to your Metadata.

Downloadable, Enhancement, MicroStrategy 9.0 Metadata Browser, Microstrategy

A while ago I had the opportunity to prepare a short training on the FreeForm SQL reports for some of my colleagues. I thought I’d better share this with you as well, so… here is a not-at-all-exhaustive list of things worth knowing about this type of report.

What is a FF SQL?

FreeForm SQL (aka FreeStyle SQL) is a type of report which allows the designer to define its own SQL code. Notice this is a rather different approach than what we are normally used to in MicroStrategy, where we would usually let the SQL Engine do the job.

When should it be used?

1. Whenever you tried everything (VLDB properties, Metric Levels, Attribute Relationships, Table Logical Size, Data Model changes…) and the SQL Engine still doesn’t perform that join the way you want it to.

2. Whenever you want to use a data source which does not exist in the Warehouse Catalog. That’s right, with FF SQL you can use almost any data source, including those tables you see in the left hand side of the Catalog.

How do I recognize it?

You don’t, at least not from the outside. You can either edit a report and see if you can spot the unnoticeable “Freeform SQL Definition” button, or you can right click on a report and make a component search. If it has no components, you found it.

So, unless you set up some naming convention for FF SQL reports you will very soon lose track of them.

How is it created?

Click on “New -> Report” and select “Freeform SQL” from the list (you need a special role to see this option).

Type your code in the upper right hand side frame.

Now you have to map the items from the “Select” section of your script to virtual Attributes and Metrics. To do this, right click on the lower frame and select either “Add new Attribute form” or “Add new Metric”.

For a script like “SELECT country_id, country_name, SUM(sales)…” the mapping should be defined as:

These are virtual objects. You may actually have a “Country” Attribute in your project, but please don’t make the mistake of thinking these two are the same thing.

Click OK and you are back on familiar grounds.

Prompts in FF SQL

You can either create or insert them into a WHERE clause, by using the two dedicated buttons. The Prompts will show in bold pink.

Now, here is the interesting part. Let’s say you want to make some changes to the script, and you copy it into an external editor. You will notice that the prompts were converted into object IDs. Now, paste the text back into the FF SQL editor. They are bold pink again. Too bad the report won’t work anymore…

The prompts will have to be inserted again, the “button” way, if you want the report to run properly.

Words of caution.

Being what I call a “manually defined object”, the FF SQL is not subjected to automatic Schema updates. That is to say, you won’t be warned if you delete a table which is being used by such a report. So please bear in mind that maintenance for FF SQL can be harsh.

Reports, Things I Learned FreeForm SQL, Microstrategy

Note: Part 3 includes the work done so far in Part 1 and Part 2.

You probably found out by now that the Type and SubType properties of an IDSSObjectInfo (or similar class) return a numeric value. Call me picky, but if I were to build an application I would rather use layman’s terms such as Attribute or Drill Map instead of SubType 3072 or SubType 14336.

This third part of our tutorial offers a great way to convert these not-so-intuitive numerical values into their English meaning, by using a custom made function called DSS2Name.

You can download the VB6 files here:
SDK Kit part 3.zip

Downloadable, Things I Learned, Tips and Tricks IDSSObjectInfo, Microstrategy, SDK Kit, SubType

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

Now that the 9.0 is all installed, it’s time to dig into some of those finishing touches that I’ve always wanted to do…which is a dynamic and expansive drill map.

I’m starting by doing specific drill maps for all of my major attributes, but what I’d like to focus on today isn’t the Up, Down, Across (though I’d really love someone to comment on the real difference between the 3…maybe I’m just slow), but the “Drill to template” option. The reason this drill option is so powerful is because it allows you to drill outside of the metrics that you are already working with. In a standard drill may you might be drilling into sales. The tutorial has the best example of this – Drill from Category, into Sub-Category, into Product. However, sometimes that doesn’t answer the questions that you want to have answered. What if you’re looking at a revenue report, and you want to jump across to Accounts Receivable?

Enter Template Drilling. As a background for this, you might also want to view document ID TX5700-8X-2789 in the MicroStrategy Knowledge base.

The link above is click-able to a full sized image.

The template that I have created includes on it 3 items: Invoice Number (with Description), Due Date, and the Metric “Invoice Amount”. When you drill to this template, the end goal is to display the invoices that are outstanding on whatever project you drilled into. This requires an understanding of some of the drill settings.

Drilling can make the navigation process between reports truly dynamic and give insight into metrics that under normal circumstances might be unrelated.

Drill Map, MicroStrategy 9.0 Apply Filter, Drill Map, Microstrategy