Member Revision History in Master Data Services 2016 - Part 1

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


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?


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

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

With the Excel add-in, we can quickly add a couple of members to the entity.

Add members to the entity in Excel

Member Revision History of Master Data Services

Let's make a change to one of the members.

Change member in Excel

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

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

When we now click on View History, we can see the previous value of the member.

Member View History in Master Data Services

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

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

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

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

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

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

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

However, this section is now reserved for the Attribute Transaction log only.

View history for entity in Explorer in Master Data Services


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.

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

Thursday, March 23, 2017 - 10:47:09 AM - Koen Verbeeck Back To Top (51568)


Hi Ben,

I'll reply in the other post.


Wednesday, March 22, 2017 - 1:32:01 PM - Ben Back To Top (51541)

 Thanks again for the tip and quick reply.  I was able to successfully create the audit table after reading your follow-up tip, but still can't figure out how to display the history in MDS - as I mentioned on the other post.




Wednesday, March 22, 2017 - 8:19:35 AM - Koen Verbeeck Back To Top (51531)

Hi Ben,

you can find that information in part 2 of this article.
There you create a view on top of the history that will give you all detailed information.

Tuesday, March 21, 2017 - 3:52:25 PM - Ben Back To Top (51513)

Thanks for the tip! I still don't see the audit history that includes the timestamps and users who made the changes to the attribute members, i.e. Created By, Created On, Updated By, or Updated On, when I select 'View History'.

Is there a property that needs to be set in order to populate those values?




get free sql tips
agree to terms