All posts by Sorin Suciu


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964

Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964

About Sorin Suciu

Sorin is a MicroStrategy specialist, experienced in Retail, Telecom and Customer Operations. Blogging is where his passion for writing met his geek nature and decided they should do some crazy things together. Though not a funny guy himself, he is obsessed with arguably funny stuff, such as Terry Pratchett, Monty Python, Douglas Adams and live parrots.

Getting Started


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
MicroStrategy

 

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.

Compound Metrics and Data Type


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
Metrics, MicroStrategy

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:

Search by ID


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
MicroStrategy, SDK
You probably noticed that it is not possible to make a search by using an object ID in MicroStrategy.

In most cases this should be no biggie. Still, there are some particular situations when this can be really frustrating.

Let’s just say that you are working on a multilingual project and you really need to know what the heck is the German for “Commissioned Regional Sales wo. Externals”. Let’s also assume that Babelfish may sometimes have funny ways of translating such a corporate nuisance. I’ll even go further and presume that there is not a single trace of a dictionary-like repository in your organization.

The good thing is that an object has the same ID, regardless of its project source. After all, this comes naturally with the use of Object Manager.

Enter “Search by ID“. Now you can simply log on to the project source of your choice, type the ID of the object whose name in that particular project you want to know, et voila… There you have it!