By: Koen Verbeeck | Comments (2) | Related: > Master Data Services
Problem
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.
Solution
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.
The entity itself has only one extra attribute:
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
Finally, a couple of members have been added to the entity:
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.
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.
We can observe the same using the subscription views:
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:
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:
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
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.
The history view in the Explorer now looks like this:
When the member with code C and name TestC is reverted, the history screen shows the following result:
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:
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.
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.
The subscription views show the same result. Any trace of TestB and TestD is gone.
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).
Conclusion
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
- Try it out yourself! You can follow the tip along to find out what's happening behind the scenes!
- Read the tip Purging Entity Members in SQL Server Master Data Services if you want to purge an entity using the staging tables.
- 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
