This is a little “don’t” that I stumbled upon recently.
Don’t create metric aliases that contain operators such as “-“, “+”, and so long. Although MicroStrategy allows you to do so, the database application (Oracle, Teradata,…) will nag you about expecting “something between – and ,” and of course, the report will stop executing.
Many a newbie MicroStrategy learning fellow gets a little bit confused when confronted with concepts such as attributes, facts, metrics and so on.
The next sketch is a little something that I use to help them map the new notions to their existing SQL knowledge:
SELECT
a11.country_id,
a11.country_desc,
SUM(a12.sales_amt) sales
FROM
TABLE_COUNTRY a11,
TABLE_SALES a12
WHERE
a11.country_id = a12.country_id
AND
a11.country_id = “15”
GROUP BY
a11.country_id
a11.country_desc
Marked blue you can see the “Country” attribute and its two fields – country_id and country_desc.
Green stands for the “Sales” metric, which is no more than a SUM of the “sales_amt” fact, depicted here in bold italic green.
In psychedelic pink you have the metric alias, as specified in the “Column Name used in table SQL creation” field from the “Metric Column Alias Options” menu.
The tables used by the report are shown here in red.
Lastly, I reserved the wonderful orange for the filter. Note that this filter may exist in the report as a standalone filter or as a result from a prompted filter. Either way, the SQL should look the same.
All right, so this doesn’t happen too often, but when it does happen… it hurts.
Let’s say you are using a compound metric, something like (([Metric 1] / [Metric 2]) * 100) and the VLDB settings for the report are such that the SQL Engine uses intermediary tables.
What this means is that somewhere in the SQL there will be a “CREATE TABLE…” syntax and that your compound metric’s data type will be declared in it. Now, the million dollar question is – what will be the data type of your compound metric? Surely the SQL Engine will use the settings entered in the Values Formatting Properties window. Well, not really… Those settings are only used by the Analytical Engine for formatting the reports.
In fact, the data type of your compound metric will be the data type of the first metric in the formula – in this case, [Metric 1]. This is also the case when you are using a formula like ApplyAgg(“sum(case when #1 = 1 THEN 1.0 ELSE #0 END)”, [Fact 1], [Fact 2]). The data type will be that of [Fact 1], because it is the first one that is being referred. This is actually the best way of writing such a formula, as it ensures that the data type will be that of the result fact instead that of the condition fact.
Why is this so important? Think of what happens if [Metric 2] has a greater number of decimals than [Metric 1]. Most likely you will get one of those pesky red colored errors and the report will stop running.
Fortunately there is a simple way of avoiding this. Just set the data type of your compound metric to Float, with a hefty bit length of 48.
In the Metric Editor go to Tools -> Advanced Settings -> Metric Column Options:
