Category Archives: MicroStrategy

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!

Report Service Documents


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

Every time we rename an object, MicroStrategy updates that object’s name in all of its dependants. That’s true, right?… Or is it?
Well, it’s almost true. This fails when the object whose name we change is referenced as a data field in a Document.

Tip. A data field is a text field that is created either by dragging it from the Dataset Objects area into the Layout area, or by creating it manually and then typing the object’s name. The name should appear between braces ({}) and should be also surrounded by brackets ([]) if it contains spaces or special characters.

Now, because the data field does not have an object ID, MicroStrategy is completely oblivious if the object referenced in it is renamed, thus the document will not run. Strangely enough, it performs fine if the object was moved to a different folder.

The way to avoid such mishaps is to do a dependence check when renaming a Metric or Attribute and see if they are being used as data fields inside a Document. All it takes then is typing the new name into the text box.

Of course, if the object is used in the Document as part of a grid, then the Metadata does its job very well.

Update: This issue is fixed in version 9.

No projects were returned by this project source


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

If no projects are returned by MicroStrategy these could be reasons.

  1. Run License Manager and check that you have MicroStrategy Desktop and MicroStrategy Architect among Installed Products.
  2. You enabled anonymous authentication. The Guest user is a member of the Public group. By default, this user group does not have access to any projects in the project source. Revert back to Standard authentication, Administrator -> User Manager -> Public/Gest -> project Access. Set the access and privileges desired for the guest login.
  3. You are trying to connect to a Microsoft Access database with database authentication. Microsoft Access does not support database authentication.
  4. Similar to #2, LDAP user ID and password blank would lead to no project(s) when using LDAP authentication.

Sometimes a faulty installation is needed to be repaired.

But mostly, I have found the issue to be user not given right privileges, not connecting to right Project source or metadata corruption.

Performance tuning in MicroStrategy


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

Some of collection (mine and from friends)

1. Use Aggregate tables
2. Use partition mapping
3. VLDB settings
4. Collect statistics on the tables
5. Use Index on all Key columns ( for conditions from WHERE clause, JOIN conditions, ORDER BY clause , GROUP BY clause )
6. Convince the requester of report to drop least used and most SQL intensive calculations/columns. In rare cases it works.
7. Select from in-memory tables (from Orion onwards)
8. Avoid Custom Groups
9. Use Free form reports (if any other tuning doesn’t bring much improvement)
10. Use Database function instead of MicroStrategy provided function
11. Use Cartesian Products Wisely
12. Connectivity between I-Server, Web-server, Database server and meta-data server is fast.
13. chk explain plan of the query generated (teradata specific). it should indicate high confidence level… also full table scan should not be there.
14. Use Case Statement option available in VLDB Properties, If your report contains any Custom Groups.
15. If the SQL of your Report has any Sub queries, You can use the “Use Temporary Table” option available in Query Optimization in VLDB Properties. For reports with several relationship filters, temporary table syntax may execute significantly faster on the database.
16. make sure you not using lookups at fact table.
17. as others pointed out make sure indexes and collect stats are there
18. get the SQL and execute directly in DB. if it’s take less time then you’ll have something to do with MSTR or fine tune DB SQL with the help DBA.
19. in MSTR, go to project config, increase the project memory settings. this is the memory MSTR uses for worker set. it should be large enough to process it quickly.
20. also go to intelligence server configuration and make sure the worker set XML is at optimum level. ( you can set 5 Lk for 8.0.3)
21. web server java heap size should be more if you are working with bigger data set and RS documents. by default it is 256 MB and you can set it as 512 MB (max)
22. MicroStrategy recommends the use of warehouse partitioning when implementing cross-dimensional partitioning.