Using the Transaction History in SQL Server Master Data Services

By:   |   Comments (5)   |   Related: > Master Data Services


We are using SQL Server 2012 Master Data Services to manage our master data. There are frequent changes to some fields of an important entity and there are a lot of people who are allowed to make those changes. I would like to know if it is possible to find out who changed something at a specific point in time.


SQL Server 2012 Master Data Services (MDS) is the master data management solution offered by Microsoft and it ships with SQL Server Enterprise or Business Intelligence edition. One of its features is that it automatically tracks all changes to the attributes of an entity. Keep in mind that this is not the same as the change tracking feature which you can enable on an entity. Change tracking is a feature you can use to create workflows to act when a field has changed. The transaction history just logs all the changes. You can compare the transaction history a bit with the transaction log of a database and the change tracking with the database feature of the same name.

change tracking

Using the Excel Add-in

Master Data Services comes with an Excel add-in which allows business users to easily create and modify master data. Explaining the entire add-in is out of scope for this tip. For more information, please refer to the following tips:

Let’s suppose we have an entity called Athlete that stores athletes who participated at the Winter Olympics. There were some data quality issues with the gender of some athletes, because often it was missing. A business rule was created to make the gender a mandatory field. In the Excel add-in, we can easily filter on all the members that failed validation:

missing genders

Now we can change the gender of an athlete. Let’s set the gender of Andreas Kunz to male.

change gender

The cell is highlighted in orange, which means the change is still unpublished. By hitting the publish button in the MDS ribbon, we can push the change to the MDS database.

publish data

When publishing changes, you have the option to specify an annotation. You can either specify a general annotation for all changes, or you can specify specific annotations for all changes individually. In our case we only changed one row, so there is no difference.


After the change has been published, we can easily retrieve the transaction history for that member by right-clicking it and selecting View Transactions at the bottom of the context menu.

validation succeeded

This gives us a detailed view on when the change was made and by who.

transaction history for a specific member

As a side note, it is also possible to add additional annotations.

Using the browser in Master Data Manager

In Master Data Manager, you can use the Explorer to achieve the same functionality as in the add-in. When you highlight a member of an entity, you can click on View Annotations to retrieve more information.


The pop-up gives the same information and you can add annotations as well.

transaction history in explorer

With the Excel add-in and the Explorer, you can however only view the transaction history of one specific member at the time. If you want a more high-level overview, you can do this in Version Management (listed under Administrative Tasks in the Manager).

version management

Inside Version Management, there is a tab for the transaction history. There you can review all of the transactions that occurred for a model.

transactions galore


You can create filters to search for certain transactions.



Clicking on a column header in the Transactions table will sort the column either ascending or descending. If you select a transaction, you can review its annotations and add a new one, just like in the add-in or in Explorer.

view annotations

Reverting a transaction in Master Data Services

The transaction history has one specific useful feature: the ability to revert a transaction. If you select a transaction, you can simply click the Revert Transaction button to reverse it.

revert transaction

A new transaction will be added that will undo the action of the “reverted” transaction.

reverted transaction

In our case, the gender value of 1 (which corresponds with male) will be overwritten by an empty value.


MDS keeps track of all changes to the master data by using a transaction history. You can consult the transactions for a specific member of an entity either through the Excel add-in or the Explorer in the browser. You can inspect all the transactions for a model in the Version Management section. It is possible to add extra annotations to transactions or to reverse them.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, April 25, 2022 - 4:37:34 PM - Ivan keith Back To Top (90034)
Hi there. Thanks for sharing the valuable information. I found this article very helpful. I would love to read more from you. Keep up the good work.
I have read a similar article on - Evolution of Master Data Management (MDM) in modern analytics. Please do check it out.

Tuesday, April 12, 2016 - 9:39:21 AM - Koen Verbeeck Back To Top (41202)

I asked the MDS team the same question and this is their response:

"In SQL 2014 and below, the only supported programmatic way to query the transaction log is through the web service API.


In SQL 2016, if an entity uses the new Member transaction log type, then the user may create on the entity a subscription view that exposes transactions."

Although it seems it is possible in SQL 2014 to get the data (hint: supported vs not-supported :).

Tuesday, April 5, 2016 - 12:29:40 PM - Rich Back To Top (41140)

Awesome!  Thank you for the info - that is exactly what I needed! 


Monday, April 4, 2016 - 4:06:06 PM - Dennis Gisseldahl Back To Top (41132)



A comment on that comment. You can make a report based on the view mdm.viw_SYSTEM_TRANSACTIONS. You may filter on Model_Name, Version_Name, Enity and any specific Attribute. If the column TransactionType_ID is 2 it means a change member status (e.g. delete), 1 means member is created and 3 that attribute value is set or changed. A change will have a value in both the Prior Value column and the New Value column. The Date Time column will tell when the change was made and User Name who did it. Date Time is in UTC time so you may want to offset that for your time zone. This comment applies to SQL Server 2014. I don't know if it works the same in other versions.


Friday, April 1, 2016 - 1:56:05 PM - Rich Davis Back To Top (41120)

 Hi Koen,

Your tips on MDS have been very helpful and enlightening.  You were very clear about how transactions are logged and accessed.  I'm wondering if the transaction log could be accessed and used an emailed or generated report?  So, for example, all transactions from today are put into an Excel doc or PDF and posted to a report or emailed out.  If possible, applying a filter first would be great so only changes to certain attributes are exported.  Let me know if you have a solution to this.  Thanks!


get free sql tips
agree to terms