Microstrategy Blog

“Oh blast, I myth-placed my keys again!”
A. T. Tribute – Famous quotes

Sorry about that… Just go on and read nothing but Robert Asprin for a couple of weeks and see what happens

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.

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.