Purging Entity Members in SQL Server Master Data Services

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

Problem

I have an entity in SQL Server Master Data Services where the code value is not generated automatically. I deleted a member and now I want to add a new member with the same code, but instead I get an error. How is this possible and how can I resolve this?

Solution

As can be seen in the tip Using the Transaction History in Master Data Services (MDS), MDS keeps track of all changes in the transaction history. So when you delete a member, it's actually only soft deleted so the transaction can always be reversed. Let's test it out on an entity where the code has to be specified. I have created a simple member with the value "Test" as the code.

When I delete this member, you can see that it has been deactivated in the transaction log.

If you would revert the transaction, the member would be reactivated again. But what if we try to add a member with the same code value? This will lead to an error message:

You have two options:

Choose a different code
Ask an administrator to remove the deleted member from the MDS database

Since you are reading this tip, it's possible you are the administrator and you have been asked to delete a member from the MDS database, so in this tip we'll explore the second option.

The only way to permanently delete a member in MDS is to use the staging tables. Each time you create an entity in MDS, a corresponding staging table is created in the MDS database which you can use to handle your master data in a more automated fashion. You can import data, but also change data, delete it or purge it permanently.

Inserting Data into the Staging Table

A staging table – referred to as leaf member staging table – consists of the following columns:

ID: an automatically assigned identifier.
ImportType: This import column determines what kind of data manipulation you want to do with the staging data. It can have the following codes:
- 0 – Create new members. If a member with the same code already exists, try to update. NULL values are ignored. This is some sort of "smart insert".
- 1 – Create new members. Updates will fail.
- 2 – Create new members. If a member with the same code already exists, try to update. NULL values are not ignored and will overwrite existing data.
- 3 – Deactivate member based on code value. If the member is used as a domain-based attribute, the operation will fail. This is kind of the same as deleting the member in Explorer or the Excel MDS add-in.
- 4 – Permanently delete member. If the member is used as a domain based attribute, the operation will fail. This is the one we need to use to purge the member from the entity in our example.
- 5 – Deactivate member based on code value. This will even succeed if the member is used as a domain based attribute. It will replace its value with NULL in other entities.
- 6 – Permanently delete member. This will even succeed if the member is used as a domain based attribute. It will replace its value with NULL in other entities.
ImportStatus_ID: The status of the import process. It can have the following codes:
- 0 – The member is ready for staging
- 1 – The staging has succeeded (automatically assigned)
- 2 – The staging has failed (automatically assigned)
Batch_ID: Automatically assigned identifier.
BatchTag: A unique name for the batch.
ErrorCode: Displays the error code if the staging failed.
Code: The code of the member.
Name: The name of the member.
NewCode: Use only if you want to change the code of a member.

For more information about the leaf member staging table, check out the corresponding MSDN page.

Inserting Data into the MDS Staging Table

The staging table itself can be found in the MDS database in the stg schema:

The following INSERT statement should do the trick:

INSERT INTO [stg].[Test_Leaf]
	([ImportType]
	,[ImportStatus_ID]
	,[BatchTag]
	,[Code]
	,[Name])
VALUES
	(4 -- purge member
	,0 -- ready for staging
	,'Purge Member Test' -- batch tag
	,'Test' -- code
	,'Test'); -- name
GO

Remember, this only creates a batch. You still have to start it.

Processing the Batch

Starting a batch can be done with a stored procedure or by starting it manually in Integration Management.

In the Import Data section of Integration Management, you can find the newly created batch. You can start it with a simple click on 'Start Batches'.

After some time, the batch should be finished. Refresh the browser to make sure. The status should be changed to Completed and there should be no errors.

If you take a look at the staging table, you can see that the staged record is now updated.

If you want to empty the staging table, you can either delete all records, or click Clear Batches in Integration Management.

It is now possible to insert a new member with the same code. If you would take a look at the transaction history, you can still see the transactions on the old member. However, reversing a transaction would not have any effect.

Avoiding the problem

This problem can actually quite easily be avoided. By creating an entity, you have the option to let the code values be generated automatically, much like an IDENTITY property in SQL Server.

The column that you used as code before just has to be stored in a different attribute. Since codes are generated, you can delete and re-insert a member as many times as you want, since it will get a new code value each time.

Conclusion

If you permanently want to delete members from an entity in MDS, you need to use the staging tables. Deleting through the user interfaces will only result in a soft delete, since the transaction needs to be reversible. An alternative is to use generated codes instead, which will allow you to insert and delete members without issues.

Next Steps

For more information about the transaction history in MDS, check out the tip Using the Transaction History in Master Data Services.
Review the documentation about the leaf member staging tables, as there are many options on how to stage data in MDS.
Check out the overview of all MDS tips.

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