Microstrategy Blog

Another in the “Basic” series (because that’s about my knowledge level) .

One of the things that you might come across as an Administrator or a Project Manager is a “Time Out Error”. Now most Architects or Project management people will set this in such a way that it allows for your reports to develop, but also times out if your users somehow manage to work themselves into some sort of weird outer cross join magic.

So, say the standard 10 minutes on install isn’t enough because you’re dealing with a million rows joined to 13 tables. (It happens. Trust Me.)

Right click on your project name and select Project Configuration. Or, highlight the project you want to adjust and under the Administration Menu, select Project Configuration.

You can click through to the image below for a larger view.

On the left hand side the third option down is called Governing. This is in MicroStrategy 9 by the way, so it might be slightly different order in 8. The first option is called Results Set. As you can see in the highlighted section, this is where your interactive and scheduled reports timing is governed. Adjust them based on seconds. Keep in mind, if you make it too high, you’re going to have a cranky analyst calling and asking why his report won’t stop running, as opposed to why his report just timed out. Either way, remember it’s his fault.

Administrator, Basic Series

The “avg” function is MicroStrategy has caused me problems in the past. It’s not that “avg” does anything wrong…it just doesn’t necessarily provide the correct subtotals to the end user, depending on the end user requirements.

Here’s the report output that examine all of the different ways average can function.

Now we’ll examine these metrics left to right.

This first one is your true “Avg” metric. Here’s the SQL that is sent through when you query the Warehouse.

SELECT
- ID,
- AVG(VALUE)
FROM FACT_VALUE
GROUP BY ID


Simple enough…but now say you want the “total” average as related to these groups. In the metric definition if you select “avg” as the aggregation type, you’ll notice that the average in the subtotal, is simply the average of the averages (following me?). Because you’re returning the data already at the aggregated level, the system doesn’t know what’s driving t in the background.

The second metric is actually the “weighted average” for our purposes. This metric is actually defined as the SUM(VALUE)/COUNT(VALUE), with the Smart Metric Enabled on your metric creation. The subtotal of this metric is actually the true average of all of the values, not the average of the average.

The columns that follow prove the logic out. You can see that the sum/count is the “more” correct way to calculate average. Keep this in mind when designing your metrics.

Metrics, Tips and Tricks avg, metric

Easing back into the posting, since it’s been a while.

Here’s a simple one to customize you’re look and feel of MicroStrategy web. After logging into the web site, at the top of your quick toolbar, you’ll see an option for preferences. Under general, you should see the menu below ->

Here you can actually change the look or feel. For those CSS inclined, you can make your own, or add your corporate branding. The style sheets are located in

C:\Program Files (x86)\MicroStrategy\Web ASPx\style\mstr

And you can keep drilling in for the images, and their specs.

Any people out there know the steps to add their own?

Tips and Tricks, Web

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