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

 

Purge an Entity in Master Data Services 2016


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

Attend these FREE MSSQLTips webcasts >> click to register


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.

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).

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


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