Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using the Transaction History in SQL Server Master Data Services


By:   |   Read Comments (4)   |   Related Tips: More > Master Data Services

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

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.

Solution

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.

annotations

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.

explorer

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.

filtering

 

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.

Conclusion

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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 05, 2016 - 12:29:40 PM - Rich Back To Top

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

 


Monday, April 04, 2016 - 4:06:06 PM - Dennis Gisseldahl Back To Top

 Hi,

 

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 01, 2016 - 1:56:05 PM - Rich Davis Back To Top

 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!

 


Learn more about SQL Server tools