Compound Key Attributes

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.