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

 

Member Revision History in Master Data Services 2016 - Part 1


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

Attend these FREE MSSQLTips webcasts >> click to register


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

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

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.


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     



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

 

Hi Ben,

I'll reply in the other post.

Koen


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

 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.

Thanks,

Ben

 


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

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.

 

https://www.mssqltips.com/sqlservertip/4325/member-revision-history-in-master-data-services-2016--part-2/


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

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?

Thanks,

Ben

 


Learn more about SQL Server tools