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.

Remove from Grid/Report


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

You know what they say – you are seldom assigned to a new role, instead new roles are constantly being assigned to you. This is how it came into past, that my job description (being the hospitable chap that it is) was kind enough to make room for yet another topic – second level support.

All in all, this suddenly made my job even more interesting. It is an established fact that no matter the data model’s flawlessness, no matter the excellence of design and no matter the software, be it rain or snow, given enough time and creativity, someone is bound to find an ingenious way of bringing that software to its knees. Thus it is with great confidence that I am promising plenty of new articles on how things can go wrong, and hopefully on how to fix them.

One common problem that I encountered so far is an issue that it’s usually pretty well covered during the first training modules – the difference between “Remove from Report” and “Remove from Grid”. Basically what happens is that users tend to play around with various attributes while creating a report and then neglect to properly remove them from the “Report Objects”.

Having an alien attribute in the “Report Objects” can cause cross joins, and the bad thing about cross joins is that, unless someone has a look at the SQL or the report runs into a spool space error then it is kind of hard to spot. I have seen user created reports that ran with cross joins for years without anyone ever raising an eyebrow…

It would be unfair not to mention that sometimes we need to bring an attribute to the “Report Objects” although we don’t want to see it in the grid, precisely in order to avoid cross joins. How’s that for confusing? Well, let’s just say that in this particular case, the attribute I’m talking about is not an alien attribute, but rather a “connecting” attribute. I can’t think of a suitable example, but feel free to post a comment if you have one.

Compound Key Attributes


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

A compound key attribute is an attribute that has two or more IDs. For instance, the attribute City may be defined in the data model as a combination of COUNTRY_ID and CITY_ID. Quite often these attributes have automatic mapping for one ID and manual mapping for the other one. This design may look strange, but in most cases there are sound rationales behind it.

Now, here is the thing. Let’s say you need to create a new table that should contain the compound key attribute and a new fact. Obviously, you create the table, load it into the Warehouse Catalog, create the new fact and update the schema.
You then create a new metric based on the new fact and attempt to use it in a report, together with the compound key attribute. What happens next is you get an error stating that the new fact does not exist at the attribute level. Staggered, you have a look at the logical view of your new table and see that your attribute and fact are both present, and therefore there should be no call for the error.

Of course, there is a very good reason for the error, and that is because the compound key attribute knows about the new table only through its automatically mapped ID, which is not really enough. So just add the new table to the source table list of the manually mapped ID and it should work.

Common sense suggests that no one would run into such a problem. Nevertheless this belief is based on the idea that all the developers are familiar with each and every compound key attribute in the project, which may not always be true.

A good practice in this case is to maintain a list of attributes that have at least one connection mapping set to manual, and pay special attention to these attributes. Even an attribute with a single ID is liable to cause the same problem if that ID is manually mapped, except that in its case the logical view will not show the attribute at all, thus not fooling you.

SDK – DssType…


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

If you find yourself writing a program based on MicroStrategy’s SDK, sooner or later you will realize that although huge, the SDK documentation is not nearly sufficient.

Let’s say you are trying to write an IF/CASE statement on object.Type. Most of the time the type is self explanatory, like DssTypeAttribute or DssTypeFilter.

If object.Type = DssTypeMetric Then…

However, there are some objects for which no DssTypeXyz is defined, like Hierarchy or Transformation.
The good news is that it is quite simple to learn how MSTR stores these objects. Just go to C:\Program Files\MicroStrategy\Tutorial Reporting\TUTORIAL_METADATA_7200.mdb and open the table DSSMDOBJINFO.

Now go to the Tutorial environment of MSTR and locate an object whose type you want to learn, i.e. a Transformation. For this example I chose the Transformation “Quarter to Date”, whose Object ID is DB5E65D641D790631D8D7CA48FDB90FE. Simply search for this ID in the OBJECT_ID column of DSSMDOBJINFO and you will find that the OBJECT_TYPE is 43.

If object.Type = 43 Then…

Sometimes it pays to have a look at the SUBTYPE as well. Just to make sure your IF/CASE statement is correct, use the “Filter by Selection” button on the OBJECT_TYPE value that you identified and check if there is more than one subtype available for it.

If object.Type = 43 and object.SubType = 11009 Then…

Please note that there is no actual need for specifying the subtype for the Transformation, as there is only one available. The code above is just for emphasis purposes.

By the way, whatever you do, don’t modify anything in the TUTORIAL_METADATA_7200.mdb file, not if you still want to use the Tutorial environment…

The depressing story of the renamed fact


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
MicroStrategy
The fact SALES_AMOUNT has been enjoying a quiet life for the last five years until, one rainy day, the new QA guy called Eureka! and proudly informed everyone that according to the naming conventions the fact name should be SLS_AMT. Of course, he took the liberty of checking the source tables and discovered that they too should be updated to match the aforesaid conventions. Way to go, QA guy!

What do you do? You could swear the QA guy or, if you are more literary inclined you could write him a bitter limerick and post it on the Intranet. Seriously now, how do you go about accommodating this change? I’ll skip the whole ETL part and go directly to MicroStrategy, right after the source tables were updated.

What you can’t do is go to the Warehouse Catalog and update the source tables’ definition. The catalog would just throw a number of dependencies that need to be solved before performing the update. You can’t delete the fact either, as it is being used by several metrics which in turn are being used in reports, and so forth.

The thing to do is to make the fact independent of any table. There are some ways this can be achieved, and one of them is to clear the fact definition and map it manually to “1”. You can actually write anything in the definition field, as long as it is not “SALES_AMOUNT”.


Thus the fact is no longer related to any of its source tables. Now it’s the time to update the tables’ definition in the Warehouse Catalog. After that, just go back to the fact editor and map it automatically to “SLS_AMT” and perform a schema update.

A similar process can be applied to attributes. Rather than mapping an attribute element to “1”, you can try to temporarily map it to any other column in any other table. Most likely the relationships with other attributes will be lost because of this, but they can easily be restored when all work is finished.

Automatic mapping


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

Things aren’t always what they seem…

Automatic mapping is a great thing. It enables us to stop worrying about updating an attribute or a fact whenever we bring modifications to the Warehouse Catalog.

Well, my advice is to don’t rely too much on it. The following pictures show two instances of the “Customer” attribute and both of them appear to have CUSTOMER_ID automatically mapped.

However, one of them lacks the ORDER_FACT table, and that is because I mischievously removed it by clicking Modify and then unchecking it from the source tables list.
This ID’s mapping is still automatic, in that that it will use any new table that contains CUSTOMER_ID and it will remove from its definition any table that cease to contain this column. But as far as the table ORDER_FACT is concerned, it will not be used by the attribute until it is checked again in the source table list.

So, when debugging reports which don’t run because a fact appears not to be available at a certain attribute level, do yourself a favor and check if that particular attribute suffers from what I have described above.