SQL Server Master Data Services (MDS) custom Delete Stored Procedure

By: Svetlana Golovko   |   Updated: 2016-08-03   |   Comments   |   Related: > Master Data Services

Problem

By default entity members in Master Data Services (MDS) are not deleted permanently. They are "soft" deleted (deactivated) and available for recovery from a transaction history. We need to create multiple members with the same code as recently deleted ones, but we are getting these errors "The member code is already used by a member that was deleted. Pick a different code or ask an administrator to remove the deleted member from the MDS database". How can we mass purge deleted members with minimal steps involved? We do not want to recover them from the transaction history, we want to load them with the new members.

Solution

Depending on what you need to delete and how many manual steps will be involved you can use different methods.

Using Staging Tables to Purge Deleted Members

In this MDS tip there was one of the delete method described (using staging tables). This method is probably the most known and popular. The tip provides step by step fundamentals on how to purge deleted members using different areas of MDS.

Using Microsoft's mdm.udpDeletedMembersPurge Stored Procedure

The MDS database contains a Microsoft provided stored procedure mdm.udpDeletedMembersPurge. You can use it to purge already deleted members for the specified model and version:

USE MDS
GO
EXEC mdm.udpDeletedMembersPurge @ModelName = N'DEMO_Model', @VersionName = N'Version_1';
GO     

This stored procedure will work if we need to purge all deleted records in the model, but we do not have flexibility to purge a single record or set of records. Also, if the model has multiple entities we do not have a choice to purge the records that belong to the specific entity.

Let's create DEMO_Model with 2 entities - "DEMO_Model" and "Entity_2":

Create three members under "Demo_Model" entity:

Create three members under "Entity_2" entity:

Delete all members from both entities and review the transactions:

Now when we try to create a member with the same code as inactivated members we will get this error:

Run the mdm.udpDeletedMembersPurge stored procedure with parameters provided in the example above. It will purge all inactivated members.

Note -  We will not be able to revert any transactions (re-activate deleted members) that were logged before the purging.

Now we can create the members a, b, c under the "DEMO_Model" entity and the members 1, 2, 3 under the entity "Entity_2".

Using Custom Purge Deleted Stored Procedure for MDS

In some cases we may need to purge just a single member or set of members. In our case we have one entity maintained by users and another maintained automatically. We want to make sure that we can purge deleted members from any manually maintained entities, but keep purged records from the automatically maintained entities under the same model.

Here is the custom stored procedure that will allow us to do this. It uses the same idea as the first method (staging tables), but it has more flexibility and there are less manual steps involved:

USE MDS
GO

CREATE TYPE MDSCodeTableType AS TABLE 
( MDSCode NVARCHAR(250));
GO
  
CREATE PROC dbo.mds_Purge_DeleteMember 
 @p_ModelName NVARCHAR(50) ,
 @p_ImportType TINYINT = 4,  -- 4 - purge member, 6 - force purge member
  -- If the member is used as a domain-based attribute value of other members, the related values will be set to NULL. 
  -- ImportType 6 is for leaf members only.
 @p_BatchTag NVARCHAR(50) = 'Admin Delete/Purge' , -- batch tag, free text
 @p_MDSCodeTableType MDSCodeTableType READONLY,-- table with list of an entity members codes
 @p_VersionName NVARCHAR(50) = 'VERSION_1',
 @p_Entity_Name  NVARCHAR(50)
AS

SET NOCOUNT ON;

DECLARE @VersionName NVARCHAR(50) 
DECLARE @LogFlag INT
DECLARE @BatchTag NVARCHAR(50)
DECLARE @ModelId INT
DECLARE @UserId INT
DECLARE @VersionId INT
DECLARE @EntityTable SYSNAME
DECLARE @StagingBase NVARCHAR(60)

SET @UserId =  (SELECT ID FROM mdm.tblUser u
                   WHERE u.UserName = SUSER_SNAME())

SELECT @EntityTable = e.EntityTable, 
    @ModelId = v.Model_ID, 
    @VersionId = v.ID, 
    @StagingBase = e.StagingBase 
FROM mdm.viw_SYSTEM_SCHEMA_ENTITY e  
JOIN mdm.viw_SYSTEM_SCHEMA_VERSION v ON e.model_id = v.Model_id 
WHERE e.Model_Name = @p_ModelName AND 
     v.Name = @p_VersionName AND 
     e.Name = @p_Entity_Name

DECLARE @sql NVARCHAR(1000), @MDSCodeList INT

SELECT @MDSCodeList = COUNT(MDSCode) FROM @p_MDSCodeTableType

SELECT @sql = '
 INSERT INTO stg.' + @StagingBase + '_Leaf 
    (ImportType
    ,ImportStatus_ID
    ,BatchTag
    ,Code)
  
SELECT DISTINCT ' + CAST(@p_ImportType as NVARCHAR(2))+ '
   ,0 -- ready for staging ImportStatus_ID
   , ''' + @p_BatchTag + '''
   , Code 
FROM mdm.' + @EntityTable + ' e
LEFT JOIN @p_MDSCodeTableType tvp ON e.Code = tvp.MDSCode
WHERE (@MDSCodeList = 0  -- all deleted members in the specified entity
   OR (@MDSCodeList > 0 AND e.Code = tvp.MDSCode)) -- only deleted members from input table parameter
  AND Status_ID = 2 
  AND version_ID = ' + CAST(@VersionId as  NVARCHAR(20))+ ';'-- status 2 is "soft" deleted members

EXEC sys.sp_executesql @sql , 
                        N'@p_MDSCodeTableType MDSCodeTableType READONLY, @MDSCodeList INT',
   @p_MDSCodeTableType, 
   @MDSCodeList

SELECT @sql  ='EXEC stg.udp_'+ @StagingBase + '_Leaf 
   @VersionName = ''' + @p_VersionName  + '''
   ,@LogFlag = 1 -- log
   ,@BatchTag = ''' + @p_BatchTag + ''''

EXEC sys.sp_executesql @sql
 
EXEC mdm.udpValidateModel @UserId, @ModelId, @VersionId, 1

GO

Custom Purge Deleted Stored Procedure testing and usage

To purge all members of the specific entity:

USE MDS
GO

SET NOCOUNT ON;

DECLARE @tvp_MDSCodeTableType AS MDSCodeTableType; 

/* Don't need to add data to the table variable */

EXEC dbo.mds_Purge_DeleteMember 
 @p_ModelName = 'DEMO_Model',
 @p_MDSCodeTableType = @tvp_MDSCodeTableType,
 @p_Entity_Name = 'Entity_2',
 @p_ImportType = 4 ;  

To purge a single member of the specific entity:

USE MDS
GO

SET NOCOUNT ON;

DECLARE @tvp_MDSCodeTableType AS MDSCodeTableType;

/* Add data to the table variable. */
INSERT INTO @tvp_MDSCodeTableType (MDSCode)
    SELECT '11'
    
EXEC dbo.mds_Purge_DeleteMember 
 @p_ModelName = 'DEMO_Model',
 @p_MDSCodeTableType = @tvp_MDSCodeTableType,
 @p_Entity_Name = 'Entity_2',
 @p_ImportType = 4 ;  

Let's assume we have different members in our "Entity_2" entity with different parents:

We will delete all members and later have to make sure that we can recreate only members with the parent '111'.

To purge multiple members of the specific entity (for example members that have the same parent):

USE MDS
GO

SET NOCOUNT ON;

DECLARE @tvp_MDSCodeTableType AS MDSCodeTableType;

/* Add data to the table variable. */
INSERT INTO @tvp_MDSCodeTableType (MDSCode)
    SELECT Code FROM dbo.SomeTable WHERE Parent = '111';
  
EXEC dbo.mds_Purge_DeleteMember 
 @p_ModelName = 'DEMO_Model',
 @p_MDSCodeTableType = @tvp_MDSCodeTableType,
 @p_Entity_Name = 'Entity_2',
 @p_ImportType = 4 ;  

Now we will try to re-create the members with the parent '111' and with the parent '222':

We can now re-enter the members with the parent '111' and cannot create the entities with the parent '222'.

Using Rename Code method to Delete a Member in MDS

Every step above requires some extra permissions on MDS database and in most cases the steps have to be performed by MDS Administrator. But what if a user needs to delete a member and then re-create it, but the MDS Administrator is not available.

This method was shared with me by one of my MDS fellow developers.

The user before deleting the member can rename the code and then delete the member.

Here is how it looked before the user decided to delete the member:

Here is how the user renames it before deleting:

Now the user can delete this member and will be able to re-create the member with code '1'. The user did not need the MDS Administrator's help. Here are how all of the transactions look:

Next Steps

• Read all MDS tips here.
• Find out the steps to delete a member here.
• Check how you can reactivate the member here.
• Learn how to Deactivate or Delete Members in bulk by Using the Staging Process here.
• Check other Microsoft resources about MDS
.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips