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.
![Add Entity in Master Data Services](/tipimages2/4324_AddEntity.jpg)
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:
![Add attributes in Master Data Services](/tipimages2/4324_AddAttributes.jpg)
With the Excel add-in, we can quickly add a couple of members to the entity.
![Add members to the entity in Excel](/tipimages2/4324_AddMembers.jpg)
Member Revision History of Master Data Services
Let's make a change to one of the members.
![Change member in Excel](/tipimages2/4324_ChangeMember.jpg)
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.
![View History button in Excel for Master Data Services](/tipimages2/4324_HistoryButton.jpg)
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.
![Publish and Annotate change in Master Data Services](/tipimages2/4324_PublishChange.jpg)
When we now click on View History, we can see the previous value of the member.
![Member View History in Master Data Services](/tipimages2/4324_MemberHistory.jpg)
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.
![Member View History 2 in Master Data Services](/tipimages2/4324_MemberHistory2.jpg)
Let's find out what happens when we delete a member. Here we deleted the customer with the location in Paris.
![Deleted Member in Master Data Services](/tipimages2/4324_DeletedMember.jpg)
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.
![View history in Explorer link in Master Data Services](/tipimages2/4324_ExplorerLink.jpg)
However, not much is shown. The values of the different attributes are not shown, so it's not a really useful feature.
![View history in Explorer for Master Data Services](/tipimages2/4324_ViewHistory.jpg)
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.
![View history for entity in Explorer button in Master Data Services](/tipimages2/4324_ExplorerButton.jpg)
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.
![View history for entity in Explorer in Master Data Services](/tipimages2/4324_SoftDeletedMember.jpg)
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.
![Transactions tab in Master Data Services](/tipimages2/4324_Transactions.jpg)
However, this section is now reserved for the Attribute Transaction log only.
![View history for entity in Explorer in Master Data Services](/tipimages2/4324_memberrevision.jpg)
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
![MSSQLTips author Koen Verbeeck](/images/Koen-Verbeeck-2018-2.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips