All posts by Sorin Suciu

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.

Search by ID is dead. Long live Search by ID!

MicroStrategy, SDK

It appears that someone at MicroStrategy was receptive enough to come up with the idea of including a search by ID function in version 9. It’s called… “Search by ID” (act surprised now) and it can be found under the Tools menu of the “Search for Objects” window.

This, and the new “Translate” function in MicroStrategy 9 render my “Search by ID v2” application almost obsolete. I refrain from calling it completely obsolete for the sole reason that it still offers the direct and recurring dependents export to Excel function. To the date I still haven’t found a recurring dependents search function in MicroStrategy Desktop.

By the way, since I was confronted with a case when this tool was not able to find an object, I feel compelled to add a small tech note. If the user you log in with doesn’t have the right to “see” a particular object, then of course this object won’t be found by Search by ID v2. This may happen if the object you are looking for is placed in a colleague’s personal folder, or if that object has some kind of “Deny all” permission set for your user/usergroup.

Filtering Compound Attributes

Architect, MicroStrategy

Scenario:
Attribute1 is a compound key attribute with a ginormous number of values. In fact, there are so many values that when you attempt to see all its elements, you get a spool space error.

You also have a list of a few hundred elements (ID1 and ID2) belonging to this attribute and you somehow must build a report that would only show results for these elements.

Deal breaker:
As mentioned before, manually selecting the elements is out of the question. Not only does the spool space error occurs, but just imagine handpicking hundreds of values.

Having two filters, one for each ID, in which the appropriate values are entered using the “In List” option is also a bad idea. You will get much more than you expect, regardless if there is an “AND” or an “OR” operator between the two filters.

Solution:
As far as I tested, qualifying on multiple IDs of the same attribute is not possible in MicroStrategy, not even in version 9. Theoretically the element qualification is what we are looking for, but as stated before there are certain limitations.

The only solution that I can think of is to create a FreeForm SQL Report. Writing the WHERE block should be no problem, as you can generate it in MS Excel based on the two columns… something like =CONCATENATE(“OR (a11.Attribute_ID1 = “,A1,” AND a11.Attribute_ID2= “,B1,”)”).

If you know of better/faster ways, please share.

Update:
A better solution was presented to me by AK.
Simply create an additional attribute DESC form as a concatenation between the two IDs, separated by a pipeline character. From here on it gets much simpler.

Metric Aliases

Metrics, MicroStrategy,

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.

Search by ID v2

MicroStrategy, SDK

So, what’s new?
You can now export direct and recurring dependents of an object. That’s all.

Now for some background…

Direct dependents are the dependents found in MicroStrategy by right clicking on an object and selecting “Search for Dependents…”. The main difference is that Search By ID v2 exports them to an Excel file, whereas Desktop only allows the export to a non user friendly txt file.

Recurring dependents are the objects that use the source object directly or indirectly. If a metric is being used by a prompt and that prompt is being used by a report, then the prompt is a direct dependent and the report is an indirect dependent of the metric. A similar kind of search is offered by the Object Manager, if only as an indirect consequence of its use. Search by ID v2 should be easier to use for this particular feature.

Please be aware that finding and exporting recurring dependents can be a lengthy process.

Sluggish Prompts

MicroStrategy, Prompt

Last month Jeremy wrote an excellent article on how filters can return unexpected results. My colleague Oznur found a somewhat similar issue and he was kind enough to allow me to share it with you.

So, there was this tiny little report that used an element prompt, and that prompt contained a list of default answers. Nothing fancy so far. However, it has come to my colleague’s attention that this report had turned rogue… How else could we explain that it started returning result sets for attributes that weren’t even chosen by the user when answering the prompt?
Well, there is a simple explanation, though it did required a little bit of outside of the box thinking. MicroStrategy stores the prompt default answers (ID and DESC) exactly as they were the day the prompt was created or modified. But what if something untoward happens to the lookup table, something like modifying the lookup value for a certain ID?

What happens is that while you distinctly remember choosing “Books” you end up seeing the sales for “Beverages”, and that is because the SQL Engine will write the WHERE clause with its mind on the ID not on DESC field. Since you selected “Books” and this lookup value corresponded to “1001”, then the SQL will filter on “1001”. Then, in the last step it will perform a join with the lookup table and get the description of “1001”, which now is “Beverages”.