Entities of data is mostly a shared resource, and utilized in or contributed to by multiple systems. For example, a company may have separate systems for managing customer interactions. A part of the data will properly be an intersection, but each of the systems is also likely to have a subset of the customer information. Establishing a common definition and mapping to the natural keys of source systems is key, Master Data Services can be an effective place to store and maintain these mappings.
The following examples aim to demonstrate how to use MDS for mapping. It is also worth noticing that the examples are generic and simplistic, and assuming the members to be on the same granularity. Furthermore is it a prerequisite that the reader is familiar with MDS and its terms known ground.
One to One
In MDS, create one member to represent the country. There is two attributes to indicate how the country is referred to the two other systems.
The benefits of this approach are that it is easy to map source systems to one member in MDS with no impact on usability. But the downside is that it is not possible to map multiple entries to one MDS entry.
One to Many
The 1:N scenario is created by creating “mapping” entities. One for each source system is needed. The reason for separating out in more than one mapping table is that the natural keys from a source system often is built as a composite key, with varying number of attributes.
This modeling technique features more than one Natural key in the source system for the same entity, but cannot handle the Zombie Keys scenario as well described by Thomas Kejser.
Modeling this style was in earlier releases on the expense of the user experience, while navigating between members in the entities was slow and difficult. In this version has the ease of navigating to referred entities been improved. By clicking on the icon a new is a new window opened with the referring entity, and it’s easy to maintain its values without destroying the context which is currently edited.
Eventough its possible to manually maintain the mapping tables, is the ideal method to maintain these though ETL processes. The reason for this is that these tables quickly becomes complex, and human errors will most likely occour.
Many to Many
The N:M scenario aims to solve relations between Source Systems and an entities in a given time range. Where relations can cease to exist and reemerge later on.
This solution features a number of special purpose entities:
1) An entity holding “truth” as we would like to see it.
2) An entity holding the natural key from the Source System.
3) An entity holding the mapping in time between the entity containing the “truth” as we would like to see it and the entity holding the natural key for a given source system. This Entity also holds special payload. It must tell us in which timespan the relation is valid (ValidFrom, ValidTo). It is also hand with other flags indicating what state the map is in (IsCurrent , IsDeleted) all this is explained in more detail in Thomas kejsers blog post Fixing Zombie Keys
This illustration shows how the mapping table explicit tells the life span of the individual keys.
This construct is repeated pr source system pr unique natual key combination. Thomas kejsers blog post An Overview of Source Key Pathologies aims to explain the nature of each key type along with Transforming Source Keys to Real Keys – Part 1: Introducing Map tables and Transforming Source Keys to Real Keys – Part 2: Using Maps To Fix Key Problems is a most interesting read on this topic.
The Golden Hour 