Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: More > 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 Multiple entities in SQL Server Master Data Services

Create three members under "Demo_Model" entity:

Create the Demo_Model entity with 3 members in SQL Server Master Data Services

Create three members under "Entity_2" entity:

Create Entity 2 members in SQL Server Master Data Services

Delete all members from both entities and review the transactions:

Deactivated members reviewed in SQL Server Master Data Services

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

Create Member Error in SQL Server Master Data Services

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:

Members With Parent Example in SQL Server Master Data Services

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':

Members With Parent Recreated Error Message in SQL Server Master Data Services

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:

Member before deletion in SQL Server Master Data Services

Here is how the user renames it before deleting:

Member renamed before deletion in SQL Server Master Data Services

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:

Transactions review in SQL Server Master Data Services
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
  • .


Last Update:






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

View all my tips
Related Resources





More SQL Server Solutions











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