By: Koen Verbeeck | Comments (5) | Related: > Master Data Services
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.
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:
- Excel add-in for SQL Server 2012 Master Data Services - Part 1
- Let's Explore Excel add-in of Master Data Services of SQL Server 2012 - Part 2
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:
Now we can change the gender of an athlete. Let’s set the gender of Andreas Kunz to male.
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.
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.
This gives us a detailed view on when the change was made and by who.
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.
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).
Inside Version Management, there is a tab for the transaction history. There you can review all of the transactions that occurred for a model.
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.
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.
A new transaction will be added that will undo the action of the “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
- For more information about the Excel MDS add-in, check out the following two tips:
- Check out the overview of all MDS tips.
About the author
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 MSSQLTips.com. 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
