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 2


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

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.


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 - 1:35:07 PM - Ben Back To Top

 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

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

 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

 

 


Learn more about SQL Server tools