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:
Click on disk button to save the view. What has happened now is that a view has been created in the MDS database:
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
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
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