Member Revision History in Master Data Services 2016 - Part 2

By:   |   Updated: 2016-06-21   |   Comments (3)   |   Related: > Master Data Services


Problem

With SQL Server 2016, Master Data Services (MDS) comes packing with a lot of new features. One of those features is a brand new transaction log type: member revision history. With this transaction log type, changes are kept at the member level, instead of per attribute. In part 1 of the tip, we introduced the feature, talked how you can enable it and how you can inspect the history of a member. In the second part, we'll build views on top of the history and we will see how we can revert history.

Solution

As explained in the introduction, the tip Member Revision History in Master Data Services 2016 - Part 1 contains an introduction to the member transaction log type. It is advised to read part 1 first, if you haven't already.

SQL Server 2016

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.

Creating Subscription Views on Top of the MDS History

In Integration Management, click on Create Views to go to the management screen of the subscription views.

Subscription Views in SQL Server Master Data Services

Click on Add to create a new view.

Add view in SQL Server Master Data Services

In the view specification, there are two important items to consider:

  • Include soft-deleted members: if this checkbox is enabled, the view will display members that have been deleted from the entity. When a member is deleted, it is still kept behind the scenes. To truly delete a member from MDS, you need to purge it.
  • Format: here you specify the format of the view. You have three options:
    • Leaf members: those are current members only of the view. This type of view corresponds with the views available in previous versions of MDS.
    • Leaf Members History: those are the history rows only. These are either members with the old values of updated members, or deleted rows.
    • Leaf Members SCD Type 2:  the most comprehensive view, as it contains both current and history rows.
Specify view in SQL Server Master Data Services

For testing purposes, I created two views: one with the history and one SCD Type 2. The MSDN documentation for MDS claims that the member revision history works like a type 4 slowly changing dimension (SCD). On the Wikipedia page on slowly changing dimensions, you can see this means that the history is stored in a separate table. The view then combines the current and history table into one single SCD Type 2 output. This is a bit similar on how SQL Server 2016 works with temporal tables. However, the official Kimball definition of Type 4 means working with a mini-dimension. So it seems there's some confusion on the definitions on SCD types. I prefer to just use the Type 2 terminology for the view, as that's the one that's 100% correct.

We can easily query the views using the following T-SQL statements:

SELECT
       [ID]
      ,[Customer]
      ,[Location]
      ,[EnterDateTime]
      ,[EnterUserName]
      ,[LastChgDateTime]
      ,[LastChgUserName]
      ,[State]
FROM [MDS].[mdm].[TestHistory_HistoryRows];



SELECT
       [State]
      ,[ID]
      ,[Customer]
      ,[Location]
      ,[EnterDateTime]
      ,[EnterUserName]
      ,[LastChgDateTime]
      ,[LastChgUserName]
FROM [MDS].[mdm].[TestHistory_SCD2];

We get the following output:

Output from the views in SQL Server Master Data Services

In the first result set, we get the history rows only. Note that it's still not easy to spot the deleted members (in part 1 the customer with location Paris was deleted). In the second result set, we can see both the current members and the history members. The current rows have a LastChgDateTime equal to "9999-12-31 23:59:59:998", while LastChgUserName is NULL. For customerA, we can see that the current value is Leuven, and the previous values are Brussels and Antwerp. Using the EnterDateTime and LastChgDateTime it's easy to retrieve the chronological order in which a member was updated. The SCD Type 2 view also adds a State column, where we can finally easy see which members were deleted. Note that the Deleted state here is the current state for customerB, meaning that the member customerB is now currently deleted.

Reverting History

Currently it's only possible to rollback changes in the browser, not in the Excel Add-in. In contrast with the attribute transaction log, you don't need to be a model administrator. You only need update permissions on the attributes of the member you wish to rollback. In the Explorer, click on the member for which you want to rollback a change. Click on View History. In the pop-up, select the change you want to revert and click on Rollback.

Rollback member in SQL Server Master Data Services

Another option is to go to the entire entity member history. There you can click on the member history row you want to revert and select Revert Member

Revert member in SQL Server Master Data Services

Let's revert the soft-delete of the customer with location Paris.

Revert delete in SQL Server Master Data Services

Reverting a member doesn't actually roll back the change. Instead the member is updated again, or in the case of a delete, the member is inserted again. In the following screenshot, customerB with location was inserted again, as the delete was reverted. A new row has been added to the history, stating the member is now deactivated.

After rollback in SQL Server Master Data Services

CustomerA was reverted as well, to the location of Brussels (which was the original location). The current status now looks like this:

Current members in SQL Server Master Data Services

If we check the views again, we get the following results:

View result in SQL Server Master Data Services

In the first result set, we have the history rows. Now we can see that CustomerB was deleted: since it is no longer deleted, this status has moved from the current rows to the history rows. The status Deleted corresponds with Deactivated from the Explorer. In the second result set, we get again the combination of the current and historical rows.

Conclusion

In part 2 of the member revision history tip, we saw how you can easily create subscription views on top of the transaction log. They come in two flavors: the history records only or a SCD Type 2 style view where current records are combined with historical records. We also investigated the possibilities of rolling back changes to entity members.

Next Steps
  • Try it out yourself! Read part 1 first, make some changes to an entity and observer how those changes are reflected in the subscription views.
  • 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 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


Article Last Updated: 2016-06-21

Comments For This Article




Thursday, March 23, 2017 - 1:35:07 PM - Ben Back To Top (51572)

 Hi Koen,

Thank you for confirming that the missing audit values are a known issue in the Excel add-in.  I just wanted to make sure that there wasn't a configuration step that I was missing when following your steps.  Any idea whether this will be addressed by Microsoft, since Excel will be the primary client tool for our stakeholders.

As far as the audit history in Explorer, I'll see if they appear once I upgrade to SP1, since it appears as though I'm still using RTM (13.0.16).

Really appreaciate your responsiveness and helpful insights!

Thanks,

Ben

 


Thursday, March 23, 2017 - 10:54:44 AM - Koen Verbeeck Back To Top (51569)

Hi Ben,

I just tried it out. I updated a member a couple of time. In the MDS add-in in Excel, there's a button "Show Audit Info". This will add some extra columns to the worksheet: CreatedBy, Created On, Updated By, Updated On. These will of course only show values of the latest update.

There's also a button "View History" if you have selected a member. This will show you the entire history of that member. However, the audit columns are empty in my case. I've catched the SQL that fetches the data from the MDS database and it does return all the audit info, so it's just Excel that doesn't display those values. Nothing we can fix here.

In the UI, when you have selected a member in the Explorer, there's a little link at the right corner (under annotations) with "View History". This will open a pop-up with all the history of that member. In contrast with the Excel add-in, all the audit info is shown over here. I see in the screenshot in this article that these columns are missing at the time I wrote the article. SQL Server 2016 was in preview at the time. Currently my SQL Server 2016 is up to date with the latest CU, which is SQL Server 2016 SP1 CU2. If you don't see those columns, you might want to upgrade to the latest version.


Wednesday, March 22, 2017 - 1:27:16 PM - Ben Back To Top (51540)

 Hi Kevin,

Thanks for the follow-up tip to create an audit table, which worked for me.  However, I still can't find the UI feature which will display the audit history in the UI or Excel add-in.  For example, when selecting 'View History' in the add-in.

Is it necessary to add an additional configuration or is it only possible to view the audit history in SSMS?

Thanks,

Ben

 

 















get free sql tips
agree to terms