One of the interesting features of Master Data Services 2016 is Change Set and Require Approval. In prior versions of MDS when you apply a change and publish that change automatically take place in the entity and records. However in the new MDS in SQL Server 2016 you can create a Change Set of your pending changes, and then you can commit the change if you are happy with it. Change sets can be pending for approval by administrator, which means the entity administrator can approve or reject the change set. In this post we will have a look at this feature.

Change Set

This feature introduced in SQL Server 2016 CTP 3.0. You can save your pending changes in a set called Change Set. This Set then can be modified or deleted or applied. You can also add the functionality of Requirement for Approval for the change set, which then requires an entity administrator to approve or reject the change set.  This is a great feature for MDS and would help on the work flow process of making changes in master data. Let’s now have a closer look at how this feature works in action.

Enable Require Approval

You can go to System Administration, and then Manage Entities, Select the Entity and click on Edit, and in Edit setting check the box for Require Approval.

You would see then the entity with Required Approval column as Yes

How Change Set Works in Web UI?

After enabling Require Approval, in MDS Web UI Explorer section you will see Change Set in Edit pane right hand side as highlighted below

Now if you make any changes to the records in entity that you’ve enabled Require Approval for it before, you will get a message that says The change on this entity require approval, please choose a Change Set.

You can choose an existing change set or create a new one (as I’ve done above with a new name). After doing that you will see the change set created with list of available actions for it

Actions on a change set can be;

• Unhappy; will rollback the change in entity, but keeps the change set
• Delete: will delete the change set
• Submit; will submit the change set for approval

You will be asked to confirm if you want to submit the change set

After submitting a change set, the status of that changes to Pending. and can be seen in the change set section of Explorer. So when an authorized person check the change set he or she can see that there is a pending change set created by someone else at specific date and time with the name associated to the change set.

Authorized Person (Who should be entity administrator) then can view the change and approve or reject it. there will be a prompt for confirmation for approval as below;

After approval the status of change set will change to Committed, and the change will take place in the entity (as you see in below value of record changed)

User also can Recall changes before the admin approve or reject it as one of the actions.

Change Sets in Excel Add-in

As I’ve mentioned earlier change sets introduced in SQL Server 2016 CTP 3.0. However Excel Add-in of CTP 3.0 doesn’t support change sets and require approval. It will return an error if you want to change value of an entity with require approval enabled!

The Excel Add-in support for Change Set and Require Approval added in CTP 3.1. So after installing that you would be able to see change sets and perform same actions as you do in web UI simply from Excel

My Thoughts on This Feature

Adding work flows in the process of changing the data is a great feature, specially because the data is master data. When we talk about MDS we always work with the data that is the ONLY single version of truth, and it is important that data change only with the right process. So Change Set and Require Approval process is really useful feature.

However there is no ability to define a work flow yet. The current work flow is just a simple one that user submit and admin approve or reject. So I think more extensions to this feature will be available soon. On the other hand at the moment there is no documentation on how the change set history will be maintained. After a while the history might get big and slow down the performance of MDS. There should be some consideration about this for sure in the future.

In general this is a good step forward to add such feature in MDS, I hope we hear more about extensions to this features in near future 🙂

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.