By: Koen Verbeeck | Comments (4) | Related: > Master Data Services
Problem
I created an entity in Master Data Services 2016. I'd like to track all of the changes that are made against the members of this entity. How can I achieve this?
Solution
Master Data Services has a new feature in the SQL Server 2016 release: Member Revision History. With this feature, it's possible to track all of the changes that are made against the members of an entity. The Member Revision History feature is a type of transaction log. In previous versions, the Attribute Transaction Log was used, but it is now deprecated. This means that the attribute log will be removed in a future version of SQL Server. You can read more about the attribute transaction log in the tip Using the Transaction History in SQL Server Master Data Services. In the first part of this tip, we'll discuss how you can set-up the member transaction log type and how we can view the history of a member.
SQL Server 2016 Preview
At the time of writing, SQL Server 2016 is still in preview (currently Release Candidate 3 (RC3) has been released). This means functionality or features of Master Data Services might change, disappear or be added in the final release.
Test Set-up of Master Data Services
To test this new feature, we'll need an entity for which we want to track changes. When you create a new entity, you can choose the transaction log type.
You can choose between the following types:
- Attribute - The transaction log type used in previous versions of Master Data Services. It is now deprecated.
- Member - The subject of this tip. This type is the default.
- None - To changes are tracked.
A couple of attributes were added to the entity:
With the Excel add-in, we can quickly add a couple of members to the entity.
Member Revision History of Master Data Services
Let's make a change to one of the members.
In the ribbon, there's a button called View History. Depending on the selected member, it will show you the history of that member and its annotations.
However, at this point it will not show any changes, as the change is not yet published to the server. Let's do that first.
When we now click on View History, we can see the previous value of the member.
When you make more changes, all the previous versions of the member are visible, sorted by chronological order with the most recent at the top.
Let's find out what happens when we delete a member. Here we deleted the customer with the location in Paris.
Since the soft-deleted member is no longer visible, you can't select it and thus you cannot view it's history. We now need to resort to the MDS Explorer in the browser. There we have similar functionality to the View History button of the add-in: when you select a member, you can click on the View History link to view its history.
However, not much is shown. The values of the different attributes are not shown, so it's not a really useful feature.
The view the history of the soft-deleted member, we need to go somewhere else. In the Explorer, there's an option to view all of the history for all of the members of the entity.
Here we can see our soft-deleted member. It's difficult to see that this is actually a deleted member, since the status is still Active. In part 2 of the tip, we'll find out how we can easily retrieve the deleted members of an entity.
As explained in the tip Using the Transaction History in SQL Server Master Data Services, the individual transaction can also be viewed in the Transactions section inside Version Management.
However, this section is now reserved for the Attribute Transaction log only.
Conclusion
Using the Member Revision History feature allows you to easily check the history of a specific member of an entity. It's the default type of transaction log in Master Data Services 2016 and it is well supported in the Excel Add-in and in the browser. In the next part of the tip, we'll find out how we can revert transactions and how we can build a slowly changing type 2 view on top of the history.
Next Steps
- Try it out yourself! Make some changes to members of an entity and see how it affects the history. Check out part 2 of this tip series.
- You can find more Master Data Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
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
