Delete full or partial data from MDS entity

I have started Microsoft MDS (Master Data Services) as a new category to share some really quick and helpful solution of problems faced while working. Let me know if you have Questions in comments section.
To delete partial (good number of rows) or full Entity data with MDS database, Manual UI usage is slow and really bad if numbers of rows are good enough.

Use following query and read the comments /* . . . */ to see where you can update, based on your situation to get things done.


DECLARE @DateTimestamp nvarchar(100) = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),GETDATE(),120),' ',''),'-',''),':',''); /*Can be used to have distinct Timestamp in BatchTag of stg entity data push*/

DECLARE @MDSVersion nvarchar(20) = 'VERSION_1';/*Specify latest version of MDS model*/
DECLARE @DeleteBatchTag nvarchar(100);


SET @DeleteBatchTag = 'Delete' +'Entity1' + @DateTimestamp ;
INSERT INTO [stg].[Entity_1_Leaf]
(ImportType, ImportStatus_ID, BatchTag, Code)
SELECT 6 AS ImportType,

/*Import Type 6 will help you to delete the rows even if Entity is referred in other child entities as Domain Based. Don't use 4 import type as its going to put you in viscous cycle of deactivated - activated domain based data.*/


0 AS ImportStatus_ID,
@Action_BatchTag
, Code
FROM mdm.Entity_1
/* Use WHERE clause on current data in entity to specify what you want to delete */
WHERE Name Like '%Test%';

EXECUTE [stg].[udp_Entity_1_Leaf] @VersionName = @Version , @LogFlag = 0, @BatchTag = @DeleteBatchTag , @Batch_ID = NULL;
Advertisements

Posted on April 26, 2018, in Current, MDS, SQL and tagged , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: