Get Inserted, Updated and Deleted members from MDS

Microsoft SQL Server

To get all inserted or updated members from a SQL Server MDS database, subscription views are used. Deleted members can’t be found in the views, so another technique must be used.

The stored procedure presented here will accept a datetime value (last run time) as input, and the returned result are all inserted, updated or deleted members since last run time. The master data model I’m using for this demonstration is the Customer sample model, included in the MDS installation.

When the sample Customer model is installed, the first thing to do is to create a subscription view on the Customer entity. From the Master Data start page, select Integration, Create Views and click the Add button. Name the view sv_Customer, choose the Customer model, Version_1 and select Leaf members:

MDS New Subscription View

MDS New Subscription View

Click on disk button to save the view. What has happened now is that a view has been created in the MDS database:

mdm.sv_Customer

mdm.sv_Customer

All subscription views have some columns in common that are always created. Among those are EnterDateTime and LastChgDateTime. The first column shows when the member was first created. The second column shows when the member was edited the last time. Those columns are what we need to get the inserted and updated columns. Note that the datetime value set by MDS is UTC time, not local time.

To get the deleted members, we’ll need to look in the MDS internal transaction table, mdm.tblTransaction. When a member is deleted, it is also removed from the subscription view. But there will be a transaction showing which members have been deleted. Deleted members will have the column TransactionType_ID set to 2. And the column LastChgDTM shows when the member was deleted. Note that the transaction table is universal for all models and entities in the MDS installation, so the query needs to be filtered to show only the model / entity that you are interested in.

All combined, the stored procedure to get all inserted, updated or deleted customers look like this:

CREATE PROCEDURE dbo.GetChangedCustomers
	(
		@LastRunTime DATETIME
	)

AS

SET NOCOUNT ON

DECLARE @LastRunTimeUTC DATETIME

SET @LastRunTimeUTC = DATEADD(HH, DATEDIFF(HH, GETDATE(), GETUTCDATE()), @LastRunTime)

SELECT
	CASE
		WHEN EnterDateTime > @LastRunTimeUTC THEN 'Insert'
		WHEN LastChgDateTime > @LastRunTimeUTC THEN 'Update'
	END AS RecordType
	,CASE
		WHEN EnterDateTime > @LastRunTimeUTC THEN DATEADD(HH, DATEDIFF(HH, GETUTCDATE(), GETDATE()), EnterDateTime) 
		WHEN LastChgDateTime > @LastRunTimeUTC THEN DATEADD(HH, DATEDIFF(HH, GETUTCDATE(), GETDATE()), LastChgDateTime)
	END AS Time
	,Name
	,AddressLine1
	,StateProvince_Name
	,Country_Code
	,Telephone
	,Email
	,Website
	,CustomerType_Name
FROM
	mdm.sv_Customer
WHERE
	EnterDateTime > @LastRunTimeUTC
OR
	LastChgDateTime > @LastRunTimeUTC

UNION ALL

SELECT
	'Delete' AS RecordType
	,DATEADD(HH, DATEDIFF(HH, GETUTCDATE(), GETDATE()), LastChgDTM) AS Time
	--THE ATTRIBUTES ARE SET TO NULL FOR DELETES
	,NULL AS Name
	,NULL AS AddressLine1
	,NULL AS StateProvince_Name
	,NULL AS Country_Code
	,NULL AS Telephone
	,NULL AS Email
	,NULL AS Website
	,NULL AS CustomerType_Name
FROM
	mdm.tblTransaction T1
WHERE
	LastChgDTM > @LastRunTimeUTC
AND
	T1.TransactionType_ID = 2
AND
	--Active
	T1.NewValue = 2
AND
	--De-Activated
	T1.OldValue = 1
AND
	--THE DELETE MUST BE THE LAST CHANGE ON THE MEMBER
	LastChgDTM =
		(
			SELECT
				MAX(T2.LastChgDTM)
			FROM
				mdm.tblTransaction T2
			WHERE
				T2.Member_ID = T1.Member_ID
		)
GO

3 comments » Write a comment

  1. Why do you compare both enter and change datetime fields? It would seem to be a redundant (although harmless) filter.

    My observation is that MDS always stamps the change datetime – its just same as enter datetime when originally entered before changes.

    Am I missing a scenario where this is needed?

    Thanks!

    • Hi Gary, and thanks for the feedback!

      If I remember correctly, I checked both fields simply because I wasn’t 100% sure about their behavior. So I’m sure you’re right, to use only change time would suffice.

      Regards,
      Tomas

  2. Note that the transaction table is universal for all models and entities in the MDS installation, so the query needs to be filtered to show only the model / entity that you are interested in.

    Can you help me with applying filter to one entity only. It will be really appreciated.

    Thanks and Regards,
    Varnit

Leave a Reply

Required fields are marked *.