CustomerLocation

Hello Kimball Forum.

The data that is received for a specific project that I am working on, ties customer and location together such that two customer may have the same natural key. This is because the source systems can allocate the same natural key to 2 different customers. Currently the developers tie in Customer with location in order to discriminate customers with the same natural key. I am developing a dimensional model that separates the location/territory hierarchy and customer (I am trying to discriminate between the two entities - Territory and Customer), and am wondering what to do with the nonsensical customer key. Should I be creating this Hybrid entity (DimCustomerLocation) and follow the path of the designers, or should I separate them into distinct entities? I have considered throwing the customer natural key into a junk dimension as a trade-off, but am still not certain how this can be done. Could you let me know your thoughts on this please.

Thanks, Nick.

If 2 customers at 1 location can have the same natural key, how do you know they are 2 customers?

The data is loaded as an integrated load from two different systems. We recieve the integrated load which does not distinguish which item came from which system and are told by the supplier that to distinguish the data between two customers existing on the same natural key, that we (apparently) need to use location. I find this very odd, but it is what it is. Any help on this subject is greatly appreciated. It seems odd why attributes pertaining specifically to the actual transaction should be in the customer dimension, the customer dimension would then almost become a degenerate dimension which I see as ridiculous.

Natural key in source system is also primary key of a table representing a data entity. However primary key is only unique within the table that holds the entity. So when integrating customers into a centralised system from different source systems, it's not uncommon to combine location id with customer id, if it's really unique across systems, to form a globally unique PK for customers.

Natural key in a dimension table can be a multi-field key. If location id is part of customer natural key, include it into customer dimension. Should you denormalise other location attributes into customer dimension? It really depends on the size of customer and whether the location attributes are shared by other dimensions. For a small and simple system, denormalisation is preferred. Otherwise separate dimensions should also work.