Share via

Mapping Multiple Systems with Master Data Services

  • Article

Analytics and ETL processes often need a map of the data that is coming from multiple systems. For example, a company may have separate systems for managing customer interactions. If the customers in these systems have different identifiers and descriptions, Master Data Services can be an effective place to store and maintain these mappings.

The following examples show how you to use MDS for mapping. In these examples, you do not have to edit records in the systems you’re already invested in. You can use MDS to ensure the analysis of your existing data is accurate.

These examples are generic and simplistic, and they assume that you've already started using MDS and are familiar with basic terms. This is a wiki post and you’re encouraged to edit it or add your own tips and examples.

One-to-One Mapping

In this example, a customer named John Smith exists in two of your systems. You know that this customer is the same person, and when you calculate how much money John spent in the past 12 months, you want to ensure that you’re reporting all purchases for a single John Smith.

In MDS, create one member to represent John Smith. Then create an attribute to indicate how John Smith is referred to in your other systems.

Although you can stage in all of the customer names and all of the system codes, the mapping will largely be a manual process. You can use security to give multiple users the ability to perform this task in the Master Data Manager web UI, or you can stage in the attributes. Either way, manual work is going to have to occur at some point. However, after you’ve set this up, any time new customers are added to the system, you can use business rules to email someone and let them know that mapping needs to occur. You can also use the “defaults to a generated value” business rule to automatically create codes for new members. For more information about business rules, see http://msdn.microsoft.com/en-us/library/ff486985.aspx.

One-to-Many Mapping

In this example, John Smith exists multiple times in multiple systems. Again, you know that this is the same customer and any analysis done on data related to him should be accurate. You want one John Smith master record in MDS.

In MDS, create three entities:

  • An entity to contain the master record. This gives John Smith an MDS-specific code.
  • An entity to show the relationship between John Smith’s code in System A and MDS code.
  • An entity for each additional system (in this case, System B).

No matter what John Smith’s name and code are in other systems, in MDS he is always “1.” In most cases you would update the name in the subscribing systems to be correct (for example, you would correct Jon Smith to be John Smith).

Again, you can use business rules to ensure that all of these entities are fully populated.

Many-to-Many Mapping

For the many-to-many example, we are going to use a Product example. You can think of this many-to-many relationship like this:

  • Products can contain multiple parts.
  • Parts can be used in multiple products.

You want to be able to query a product to find all of its related parts. You also want to be able to query a part to determine all of the products it belongs to.
In MDS, create three entities:

  • An entity to contain a master list of products.
  • An entity to contain a master list of parts.
  • An entity to map the products to the parts.

In the Product parts table, a name isn’t necessary. This table is used to map the relationships.

This type of mapping can be useful in Finance models for mapping references to accounts.

Note: These relationships cannot be shown easily in MDS hierarchies. Instead, you should create a subscription view and query for the info you need. For more information on subscription views, see http://msdn.microsoft.com/en-us/library/ee633741.aspx.