Category Archives: Architect

Manual Mapping

Architect

In a previous post, I’ve warned about the not so automatically behavior of the automatic mapping. However, I can’t blame the manual mapping for the same degree of inconsistency. I mean, boy, that’s as manual as it gets!

Take a manually mapped attribute and have a look at its source tables. Now drop the attribute from one of these tables (on database and Catalog level) and try to run a report which used that table.

On the one hand, MicroStrategy knows from the attribute definition that it can include that table into the SQL. On the other hand, it should know from the table definition, as updated in the Warehouse Catalog, that the column is no longer there, but such a small detail doesn’t have enough force to deter the SQL Engine. So it simply writes the SQL as if nothing had happened, and the only error you get is from the database (i.e. a11.attribute_id does not exist).

So, if you aren’t doing this already, don’t forget to inform the manually mapped attribute that one of its source tables decided to discontinue their relationship, next time you indulge in such data model changes.

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.

Compound Key Attributes

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.

Warehouse Partition Mapping Table in MicroStrategy

Architect, MicroStrategy,

To add and set up a Partition Mapping Table in MicroStrategy:

Step 1: Create the Partition Mapping Table (PMT). Views are not recommended since they defeat the purpose of partitioning and the performance will be hampered instead of improved. Usually the PBTs hold the same structure as the original base fact table.

This table must be created with the following structure:

The ATTRIBUTE_ID column name must match the column name on the partitioned base tables. This column contains the values of the attributes at which the tables are partitioned. Attribute ID(s) used to define the partitioning (partition keys). In addition, the PMT must contain a column named ‘PBTNAME’ containing the names of each of the partitioned base tables.
For Example, if the partition level is at Year, this column will be named ‘Year_id’ and contain values such as: 1998, 1999 and 2000.
The PBTNAME column name cannot be changed. This column contains the names of the partitioned base tables. PBTNAME = Partitioned Base Table Name.

Step 2: Add this table to MicroStrategy using Warehouse Catalog. It will be added as a partition mapping table, the icon will change and the number of partitions will be shown in parenthesis. Also, all the corresponding partitions are removed from the list of available tables. The partitions function as a unit; they cannot be deselected individually. Update Schema.

Note: If a prefix is needed to access the PMTs, it has to be included into the mapping table when populating it in WH Catalog.

Step 3: Go to the Partition Mappings folder under Schema Objects. The PMT appears in the right window. Right-click on the table and select edit. Click on the ‘Add’ button and select the attribute that marks the level of partitioning. Update Schema.

NOTE: (1) The PBTNAME in the Partition Mapping Table (PMT) should be unique. Otherwise, double counting may occur.(2) A PMT is needed for each fact table to be partitioned. (3) A normalized partition base table (PBT) saves database space but it is not recommended if performance is a key issue. The MicroStrategy Engine always applies filters on the partitioned base table queries even if it the filter is a partitioning key.

Keys and Levels in MicroStrategy

Administration, Architect, MicroStrategy, ,

Table keys are the lowest level attributes in their respective hierarchies that are present in the logical table.

Fact entry levels are the lowest attribute levels at which a fact exists. The fact entry levels correspond to the lowest-level table key attributes for all the tables that define the fact.

A fact is only extended if a join does not exist between a fact table and the attribute to which the fact will extend to, i.e. the fact does not exist at the level of that attribute.

MicroStrategy SQL Generation Engine is sensitive to differences between its internal state and the states of metadata objects, there is a risk of SQL generation failures if schema modifications take place during normal user activity. In general, it is not recommended to perform schema updates during periods of normal or high usage. Schema changes should normally be reserved for periods of little to no user activity.