At my current customer, somebody accidentally deleted a large number of entries. In itself, this is not a problem, as MDS offers a great way to reverse transactions (provided you configured MDS to use version management).
To do so, go to the MDS homescreen, select the "Version Management" option, and then navigate to the "Transactions" part of it. The direct link to this would be:
http:<yourMDSServer>/MDS/Audit/AuditTransactions.aspx (your path might vary, in case you did not configure it to use the /MDS/ path).
Select the right Model and version, apply any filters you need to isolate the transaction that needs to be reversed, and then highlight the transaction you want to reverse. By clicking the "Revert Transaction" link, you will undo whatever damage has been done.
Great. Except in this particular case, there were over 1,200 transactions removed, and the (terrible) web interface only allows the selection of a single row. I didn't quite feel like spending a week reversing transactions manually, and I'm quite sure my customer would not want to pay an expensive consultant for that either.
However, as it turns out, it is fairly straightforward to handle this stuff with a script. This article is intended to show you how to do this.
We start off by looking up the entity in which the rows were removed. Master Data Services keeps track of these in a table called mdm.SYSTEM_SCHEMA_ENTITY, and the query you can use to find the Entity_ID for your entity is:
Note down the resulting ID. You will also need to find the User_ID of the user that deleted the rows. This can be done suing the following query:
Again, note down the result. Now we are ready to pull a list of all transactions. In our case, we knew it was isolated to a particular user, and a particular entity, after a specific date/time, and the user had not made any other changes since then.
Under the hood, when rows are deleted in MDS, and you have enabled version management, the rows are not actually deleted, but rather changes the member status as "de-activated". We use that to our advantage by looking for the specific state. The query I used to pull a list of all affected rows was:
Obviously, ENTITY_ID and USER_ID are the two values you found using the above queries.
The outcome of that query was used to have the user verify this looked like the right amount of data, and the MemberCodes matched with what she was expecting.
A single ransaction from there can then be reversed using the built-in MDS procedure mdm.UpdTransactionReverse using the parameters User_ID and Transaction_ID:
Once approved, I built a simple script that looped over ever every row in the recordset, and reversed the transactions that way. Building and testing the script took about 10 minutes, running it for 1,300 transactions about 30 seconds.
In case anyone is interested in the actual script, I can post it, but I hope the above helps already.
Happy reversing!
To do so, go to the MDS homescreen, select the "Version Management" option, and then navigate to the "Transactions" part of it. The direct link to this would be:
http:<yourMDSServer>/MDS/Audit/AuditTransactions.aspx (your path might vary, in case you did not configure it to use the /MDS/ path).
Select the right Model and version, apply any filters you need to isolate the transaction that needs to be reversed, and then highlight the transaction you want to reverse. By clicking the "Revert Transaction" link, you will undo whatever damage has been done.
Great. Except in this particular case, there were over 1,200 transactions removed, and the (terrible) web interface only allows the selection of a single row. I didn't quite feel like spending a week reversing transactions manually, and I'm quite sure my customer would not want to pay an expensive consultant for that either.
However, as it turns out, it is fairly straightforward to handle this stuff with a script. This article is intended to show you how to do this.
We start off by looking up the entity in which the rows were removed. Master Data Services keeps track of these in a table called mdm.SYSTEM_SCHEMA_ENTITY, and the query you can use to find the Entity_ID for your entity is:
SQL:
SELECT Entity_ID FROM mdm.viw_SYSTEM_SCHEMA_ENTITY WHERE NAME LIKE ‘%MyEntityName%’;Note down the resulting ID. You will also need to find the User_ID of the user that deleted the rows. This can be done suing the following query:
SQL:
SELECT User_ID FROM mdm.TblUser WHERE UserName = ‘Domain\User’Again, note down the result. Now we are ready to pull a list of all transactions. In our case, we knew it was isolated to a particular user, and a particular entity, after a specific date/time, and the user had not made any other changes since then.
Under the hood, when rows are deleted in MDS, and you have enabled version management, the rows are not actually deleted, but rather changes the member status as "de-activated". We use that to our advantage by looking for the specific state. The query I used to pull a list of all affected rows was:
SQL:
SELECT ID, MemberCode FROM mdm.TblTransaction
WHERE newCode = ‘De-Activated’ AND ENTITY_ID = <ENTITY_ID>
AND LastChgUserID = <USER_ID> AND LastChgDTM >= <myDate>Obviously, ENTITY_ID and USER_ID are the two values you found using the above queries.
The outcome of that query was used to have the user verify this looked like the right amount of data, and the MemberCodes matched with what she was expecting.
A single ransaction from there can then be reversed using the built-in MDS procedure mdm.UpdTransactionReverse using the parameters User_ID and Transaction_ID:
SQL:
EXEC mdm.UpdTransactionReverse
@User_ID = <user_ID>
, @Transaction_ID = <Transaction_ID>Once approved, I built a simple script that looped over ever every row in the recordset, and reversed the transactions that way. Building and testing the script took about 10 minutes, running it for 1,300 transactions about 30 seconds.
In case anyone is interested in the actual script, I can post it, but I hope the above helps already.
Happy reversing!