Many-to-Many Mapping in Master Data Services

In SQL Server 2012 RC0, Master Data Services (MDS) introduced functionality to help you navigate between related entities.

Many-to-many relationships are described briefly at the end of this post. (http://social.technet.microsoft.com/wiki/contents/articles/mapping-multiple-systems-with-master-data-services.aspx)

In this article, we’ll take the example set up in that article and show you the new MDS functionality that relates to it.

To do a simple many-to-many mapping, you need three entities. In this example, there are products that can contain many parts, and parts that can be used in many products. The “Product parts” entity is used to map these relationships.

 

In this example, we’ll call the Products and Parts entities the “source entities.”

When viewing each of the source entities, you can now show the related mapping entity.

When you click the link in the Related Entities pane, a filtered list opens. In this case, the mapping entity (Product parts) opens and displays all parts in the Bicycle product.

As shown in this image, for each domain-based attribute in the mapping entity, you can open the related source entities to view details.

In this example, each bicycle contains four parts. If you want to drill down and display details for any of the related parts, you click the button to the right of the field to view details. In this example, when you click the button by Part Code, the Parts entity is displayed and the list is filtered to show Part 3 (spokes) only.

 

Tip: In these examples, only the code is displayed in the drop-down lists. To see the name in addition to the code in the lists, above the grid, click Settings. Then choose a different display format.