Edit

Transactions (Master Data Services)

  • Article

Applies to: SQL Server - Windows only Azure SQL Managed Instance

In Master Data Services, a transaction is recorded each time action is taken on a member. Transactions can be viewed by all users and reversed by administrators. Transactions show the date, time, and user who took the action, along with other details. Users can add an annotation to a transaction, to indicate why a transaction took place.

When Transaction Are Recorded

Transactions are recorded when members:

  • Are created, deleted, or reactivated.

  • Have attribute values changed.

  • Are moved in a hierarchy.

Transactions are not recorded when business rules change attribute values.

View and Manage Transactions

In the Explorer functional area, you can view and annotate (add comments to) the transactions that you made yourself.

In the Version Management functional area, administrators can view all transactions for all users for the models they have access to, and reverse any of these transactions.

Note

Administrators can view all transactions for all users as long as they don't have the read-only permission level applied in the Version Management functional area . For example, if the read-only permission and update permission level is set for the administrator, the administrator will not be able to see other user transactions because the read-only permission will take precedence over the update permission.

You can configure how long transaction log data is retained by setting the Log retention in Days property in system settings for the Master Data Services database, and by setting Log Retention Days when you create or edit a model. For more information, see System Settings (Master Data Services) and Create a Model (Master Data Services).

The SQL Server Agent job, MDS_MDM_Sample_Log_Maintenace, triggers cleanup of the transaction logs and runs every night. You can use SQL Server Agent to modify the schedule for this job.

You can also call the following stored procedures to clean the transaction logs.

Stored Procedure Description
mdm.udpTransactionsCleanup Cleans transaction history
mdm.udpValidationsCleanup Cleans validation History
mdm.udpEntityStagingBatchTableCleanup Cleans staging table

Sample

DECLARE @CleanupOlderThanDate date = '2014-11-11',  
@ModelID INT = 7  
--Clean up Transaction Logs  
EXEC mdm.udpTransactionsCleanup @ModelID, @CleanupOlderThanDate;  
  
--Clean up Validation History  
EXEC mdm.udpValidationsCleanup @ModelID, @CleanupOlderThanDate;  
  
--Clean up EBS tables  
EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

System Settings

There is a setting in Master Data Services Configuration Manager that affects whether or not transactions are recorded when records are staged. You can adjust this setting in Master Data Services Configuration Manager or directly in the System Settings table in the Master Data Services database. For more information, see System Settings (Master Data Services).

When importing data in this version of SQL Server, you can specify whether or not to log transactions when initiating the stored procedure. For more information, see Staging Stored Procedure (Master Data Services).

Concurrency

If a particular entity value is shown simultaneously in more than one Explorer session, concurrent edits to the same value are possible. Concurrent edits will not be detected automatically by MDS. This can occur when multiple users use the MDS Explorer in the Web browser from multiple sessions, for example from multiple computers, multiple browser tabs or windows, or multiple user accounts.

More than one user can update the same entity values without error despite transactions being enabled. Typically the last edit to the value in a sequence of time will take precedence. The duplicate edit conflict can be manually observed in the transaction history and can be reversed manually by the administrator. The transaction history will show the individual transactions for the Prior value and New value for the attribute in question from each session, but will not automatically resolve the conflict when multiple New Values exist for the same old value.

Task Description Topic
Undo an action by reversing a transaction (administrators only). Reverse a Transaction (Master Data Services)

External Resources

Blog post, Transactions, Validation Issue and Staging table cleanup, on msdn.com.

Related Content