By: Koen Verbeeck | 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.
Click on Add to create a new view.
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.
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:
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.
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
Let's revert the soft-delete of the customer with location Paris.
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.
CustomerA was reverted as well, to the location of Brussels (which was the original location). The current status now looks like this:
If we check the views again, we get the following results:
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.
About the author
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
