Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Merge a Conflict in SQL Server 2016 Master Data Services


By:   |   Read Comments   |   Related Tips: More > Master Data Services

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

At my company we use SQL Server Master Data Services (MDS) to manage the master data. We have a couple of entities inside a model and we want to know how to prevent multiple users from updating the same entity member at the same time?

Solution

In SQL Server 2016, Master Data Services has the ability to merge conflicts. A conflict arises when more than one person has modified a member of an entity at the same time. MDS doesn't prevent you from modifying it, but when you try to publish your changes, you get a chance to review your change and those of your coworkers and choose which change should be kept.

SQL Server 2016 Preview

At the time of writing, SQL Server 2016 is still in preview (currently Release Candidate 3 (RC3) has been released). This means functionality or features of Master Data Services might change, disappear or be added in the final release. To be honest, we're very close to the release date, so it seems unlikely.

Merging a Conflict in SQL Server Master Data Services

I created a very simple entity - with just a few attributes - to test this new functionality. A couple of sample members are already added to this entity.

Sample entity in SQL Server Master Data Services

Let's open this entity in the MDS Excel Add-in (which you can download here).

Excel Add-in for SQL Server Master Data Services

First we're going to try the scenario where multiple people update the entity, but not the same member. Let's update one of the attributes of the second member with the browser using the Explorer section. Once you hit OK in Explorer, the change is immediately committed on the server.

Update Country to USA in SQL Server Master Data Services

Using Excel, we're updating a different member. As you can see, the second member still has the original value for its country.

Update birth date in Excel Add-in for SQL Server Master Data Services

Let's publish this change to the server.

Publish change 1 in SQL Server Master Data Services

However, there's no conflict and the update is published just fine to the server. This is because the add-in only publishes the change and ignores all the other members which are unchanged. Now let's update the same member at the same time. In the browser, we update the city of the first member.

Change member 1 City in SQL Server Master Data Services

In Excel, we also update the city of the first member, but to a different value.

Change member 1 in the Excel Add-in for SQL Server Master Data Services

Next we publish the change to the server.

Publish the change in SQL Server Master Data Services

Now there's a conflict, because the change we want to publish conflicts with the change on the server. MDS will not just update the member to the new value. The reason is because MDS keeps track off all transaction changes. This means that for each change, it knows the old value and the new value. With this change however, the old value is not the same as the current value on the server, so there's a conflict. The change is indicated in red and an error message is displayed.

Conflicts for two City changes in SQL Server Master Data Services

In the add-in, there's a new action in the ribbon to resolve the conflict.

Merge conflict button in Excel Add-in for SQL Server Master Data Services

When you click this button, a pop-up will show you the conflict and it gives you three possible solutions:

  • Keep your value
  • Keep the value on the server
  • Discard all changes and keep the original value
Merge conflict in SQL Server Master Data Services

Let's decide to keep the value from the add-in and click Accept. The change now gets the normal "orange change" color and the InputStatus changes from Error to Unchanged.

Conflict merged in Excel Add-in for SQL Server Master Data Services

However, we're not yet at the finish line! We still have to publish the change to make it take effect.

Publish changes again in SQL Server Master Data Services

When we refresh the browser window, we can take a look at the new value.

Successful data publish in SQL Server Master Data Services

The same merge functionality is present in the browser as well. If you try to update a member which has already been changed by someone else, you immediately get a pop-up window asking you to merge the conflict.

Merged data in the browser for SQL Server Master Data Services

After merging the conflict, don't forget to click on OK to commit the change to the server!

Conclusion

The ability to merge a conflict in Master Data Services is useful new functionality. It makes working with entities in a team much easier and it prevents users from accidentally updating the same attribute of a member multiple times. This functionality is supported in the Explorer at the MDS web site as well as in the Excel add-in.

Next Steps
  • Try it out yourself! You don't need multiple users to test this functionality. You can update a member in the browser and in the Excel add-in to create a conflict.
  • You can find more Master Data Services tips in this overview.
  • For more SQL Server 2016 tips, you can use this overview.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools