Purge an Entity in Master Data Services 2016

By:   |   Comments (2)   |   Related: > Master Data Services


In previous releases of Master Data Services (MDS), when you deleted a member from an entity it was only soft-deleted. It still existed in the background. To get really rid of the member, you had to purge it using the staging tables. In SQL Server 2016, you can now purge an entity using the user interface. This tip will explain how.


The tip Purging Entity Members in SQL Server Master Data Services explains how you can purge deleted members of an entity using the staging tables. This method is still valid in SQL Server 2016 and can be used just as well.

Test Master Data Services Entity

In order to test this new feature, I created an entity called TestSoftDelete. The transaction log type is the default: member revision history (you can read more about this in the tip Member Revision History in Master Data Services 2016 - Part 1). Codes are not automatically created, so they need to be provided with the creation of new members.

Manage Entities in Master Data Services

The entity itself has only one extra attribute:

Test entity attributes in Master Data Services

On top of the entity, three subscription views have been constructed:

  • one view with the current members only
  • one view with only the history of the members
  • one view with the full SCD  Type 2 view of the entity
Test entity views in Master Data Services

Finally, a couple of members have been added to the entity:

Add test entity members in Master Data Services

Purging an entity in Master Data Services

To test the purging functionality, we first have to delete some members of course. In the example we are deleting B, C and D.

Delete members in Master Data Services

In the MDS explorer, you can view the deleted members in the history view of the entity. The status is Active, because that was the status of the member prior to deletion.

History view in Master Data Services

We can observe the same using the subscription views:

Subscription view in Master Data Services

In the view with the current members, we can see that B, C and D are still present, but their status is Deleted. Now let us try something different: we are going to add another member, but with a code that already exists:

Reuse code B in Master Data Services

Oddly enough, this works. In previous versions of MDS, you would have received an error stating the code was already used. In SQL Server 2016, you can reuse codes. The ID that makes a member unique is the GUID in the system MUID column, as you can see in the subscription views:

Review subscription in Master Data Services

This means you cannot take for granted that the code attribute of an entity is unique, and you have to take that into consideration when building your processes. When we would try to revert to the original member with code B, an error is shown. That's because it's not possible to revert the member with code B, as there is another member active with the same code

Revert error in Master Data Services

The only way to revert the member, is to delete the other member with code B first. Let's try this out with the member having code C. In the next example, a member with code C is added to the entity and subsequently deleted as well. In the subscription views you can now see there are multiple members with code C (TestC and TestCAgain). Another example that you cannot rely on the code for uniqueness anymore.

Subscription view in Master Data Services

The history view in the Explorer now looks like this:

History view in Master Data Services

When the member with code C and name TestC is reverted, the history screen shows the following result:

History view in Master Data Services

The Deactivated status means that the member was previously deleted. So it's possible to revert deleted members, as long as there are no current active members with the same code. The subscription views return the following data:

Subscription view with deactiviated status in Master Data Services

Finally, let's get to the point of this tip: purging the entity. You can find this action in the Explorer by clicking on the arrow next to Delete Members. Remember that purging an entity will remove all soft-deleted members. If you want to get rid of just a couple of specific members but keep others, you need to use the staging tables.

Purge entity in Master Data Services

After purging the entity, the history view will only show the history for TestC. The Deactivated line is still present, because the member was deleted in the past, but is currently active again.

History view with deactivated members in Master Data Services

The subscription views show the same result. Any trace of TestB and TestD is gone.

Final subscription view in Master Data Services

Note: when experimenting with this feature I have noticed that sometimes the feature acted buggy. At one time, the entity wouldn't purge the soft-deleted members. Even the staging tables didn't work. Finally I had to remove the members by manually deleting them from the tables in the MDS database (not recommended in production scenarios).


With the new purge functionality in Master Data Services 2016 you can now easily remove soft-deleted members from an entity. It is an all or nothing approach though. If you only want to delete specific members, the staging tables are a better option.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck 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

Comments For This Article

Wednesday, November 15, 2017 - 3:00:39 AM - Koen Verbeeck Back To Top (69754)

Sorry no, I have never used the webservice before. There's a book about Master Data Services 2012 which also covers some aspects of the webservice, but I have no experience with it.



Tuesday, November 14, 2017 - 11:33:56 AM - Karina Back To Top (69718)


 Thanks for the post. I am trying to consume the web service using SOAPUI. I am wondering if you have done this? I would appreciate any help.

get free sql tips
agree to terms