By: Koen Verbeeck | Comments | Related: > Master Data Services
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.
Let's open this entity in the MDS Excel Add-in (which you can download here).
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.
Using Excel, we're updating a different member. As you can see, the second member still has the original value for its country.
Let's publish this change to the server.
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.
In Excel, we also update the city of the first member, but to a different value.
Next we publish the change to the server.
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.
In the add-in, there's a new action in the ribbon to resolve the conflict.
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
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.
However, we're not yet at the finish line! We still have to publish the change to make it take effect.
When we refresh the browser window, we can take a look at the new value.
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.
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.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
