The surrogate key strikes back

If you ever participated in building a data warehouse or worked around data warehouses, you've probably came across the surrogate key (SK) notion. If you’re not familiar, then take a look at this article on the Kimball's group website.

Surrogate keys are very common in data warehouses and they bring a lot of functionality, from design to performance enhancements.

Now, one of the surrogate key's drawbacks is the difficulty to keep it in synch between all environments. As each environment can be linked to a different version of the source (e.g. UAT, live environments etc.), surrogate keys can get off for the same natural key and this can also have an effect in MicroStrategy.

When building attributes you have to define its forms. The ID form should contain a unique key describing the attribute in order to provide correct joining, aggregation and attribute member list when applying filters. Indeed the surrogate key doesn’t have any business meaning, so this form will be hidden from user interaction, therefore the key is not displayed by default in reports, nor in the member list when filtering.

The reusable filters (filter objects) in MicroStrategy have a specific property that makes using attributes based on surrogate keys a no-go. When the filters are defined, even if the display form is the description (DESC form type), the attribute's key in the ID form is also encapsulated in the filter, as this is the core information that provides display integrity to the attribute. When these filters are pushed to the other MicroStrategy environments, the key(s) defined in the filter are part of the filter's definition. This is really going to be a blow; your report will retrieve other values, because the keys used in the filter definition don’t match the same keys on the environment where they were defined.

To overcome this, we defined a policy to isolate the surrogate keys into dedicated technical attributes, which we eventually hid from the users (as they don't have a business meaning). This indeed increases the number of defined attributes, but overcomes the aforementioned problem and also makes things a bit more clear and visible in terms of the  MicroStrategy model. After the technical attribute is defined tyou just need to relate all the other dimension's attributes as the parent of the technical attribute and you're done. Your main attribute should now contain either the natural key, or the concatenated natural key if this is compound natural key.

Because the SK is now separated from any main-stream attribute, all the filters will work correctly.