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

 

Approval Workflow in SQL Server Master Data Services 2016 using Change Sets - Part 1


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

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


Problem

I have an entity in SQL Server Master Data Services (MDS) and I want to put an approval process into place. Meaning, if someone makes a change to a member of this entity, this change should be approved by an administrator before the update goes live. How can I achieve this?

Solution

Master Data Services has a new feature in the SQL Server 2016 release: Change Sets. With this feature, users can attach member changes into a change set. An entity administrator can then approve or reject this change set. Using change sets streamlines the approval process and gives the administrator the opportunity to approve or reject multiple changes at once. In this tip, we'll focus on creating the necessary users to test this feature, creating change sets and adding changes to a change set. A second part will focus later on the approval process itself.

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 SQL Server 2016 Master Data Services Change Sets

To test this new feature, we'll need an entity for which we want an approval workflow. When you create a new entity, you can simply enable this by selecting the checkbox Approval Required.

Add Entity in SQL Server Master Data Services

Be aware that if you are the only administrator, you cannot make any actual changes to the members of an entity, since you cannot approve your own changes. MDS warns you for this and suggests to add a second user as an administrator. After the entity has been created, you can easily check which entities require approval in the overview.

Approval Required Column in SQL Server Master Data Services

Let's add another user to MDS. This user will make the changes which we will approve or reject using the administrator account. In the User and Group Permissions section, add a new user. Then click the pencil to edit its permissions.

Add new user in SQL Server Master Data Services

In the Functions tab, add Explorer. Don't forget to click on the pencil first to go into edit mode.

Modify functions in SQL Server Master Data Services

In the Models tab give the user the necessary permissions to make changes to the entity.

Modify models in SQL Server Master Data Services

Log in using this user so we can make changes to the test entity and try out the change sets feature.

Creating a Change Set in Master Data Services

In the Explorer, there's a specific section for managing change sets.

Change sets menu in SQL Server Master Data Services

Click on Add to create a new change set. You can specify a name, a description and the entity to which it belongs.

Add new change set in SQL Server Master Data Services

After the creation state, the change set has the status open, meaning it's ready to accept member changes.

Open status in SQL Server Master Data Services

Another method to create a change set is to add an entity member or to make a change to an existing member. When you do so, a pop-up will warn you that the entity needs approval and that the change needs to be added to a change set. You can either create a new one by specifying a name, or add the change to an existing change set.

Add change to change set in SQL Server Master Data Services

When the change is added to a change set, the changes are indicated in yellow in the explorer. Furthermore, the change itself is displayed in the change set overview at the bottom right.

Changes are indicated in yellow in SQL Server Master Data Services

Let's add a couple more changes to the change set.

More changes in SQL Server Master Data Services

At this point, the user can either discard the change set - and thus discarding all changes - or the user can submit the change set for approval by an administrator. Once the change set is submitted, the status is set to Pending.

Submit change set in SQL Server Master Data Services

If you double click on the change set, you can view the individual changes again. There, you can also recall a change set. This changes to status from pending back to open. As long as a change set is not submitted, it's not visible to other users, even administrators.

Recall change set in SQL Server Master Data Services

Conclusion

In this tip we covered change sets from the user perspective: we saw how easy it is to create a change set for an entity and add one or more member changes to it. We saw how we can discard this change set, or how we can submit or recall it. In the next part, we'll take a look at change sets from the administrator point of view.

Next Steps
  • Try it out yourself! Create a change set and familiarize yourself with all of the different statuses and actions. Stay tuned for part 2 of this tip.
  • 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, August 24, 2017 - 1:55:35 AM - Shally Back To Top

 Hi Koen,

 Yes, Validation and Change set is different processes in MDS.I have validated all the business rule, but still its not showing Validation succeded status.

Even tried to modify an existing record with minor chnage which was earlier in Validation Succeded state.After making any modification its changing status- "Awaiting revalidation".


Wednesday, August 23, 2017 - 8:08:21 AM - Koen Verbeeck Back To Top

Now I see. If I'm not mistaken, validation and change sets are two separate processes in MDS. Change sets protect an entity from unauthorized changes, which means an admin has to approve all changes.

Validation means the row has been validated against the business rules created against that entity. If you don't have any business rules defined, validation is basically meaningless. Although, I would think that if an admin has approved a change set, the record would have status "validated" if there are no business rules, but apparently not.


Wednesday, August 23, 2017 - 2:42:24 AM - Shally Back To Top

 Hi Koen,

 Yes, My change set is in Committed state. But still my record is showing Awaiting validation.Please find below highlighted details.

 

 

 

 


Tuesday, August 22, 2017 - 8:23:02 AM - Koen Verbeeck Back To Top

 

Hi Sally,

did you approve the change set with another user?

I've checked part 2 of this tip (https://www.mssqltips.com/sqlservertip/4291/approval-workflow-in-master-data-services-2016-using-change-sets--part-2/) and there it says the status should be "committed".

Regards,

Koen


Tuesday, August 22, 2017 - 6:35:56 AM - Shally Back To Top

 Hi Koen,

Thanks for the detailed post on work flow process. But i have one oubt.

 

Even when my Change set is in Approved state, But Still my record is in " Awaiting Revalidation " Status. Even after 1, 2 hours its status is not refershing or changing into " Validation Succeeded" .I am submitting all the valid records.

 

 


Tuesday, March 28, 2017 - 5:16:37 AM - Koen Verbeeck Back To Top

Hi Nagendra,

I took a look, but it appears this information is not saved. I  can find the timestamp of when the change set was approved, but not the person who did it.
I even checked the trace logs and there's nothing in there.


Tuesday, March 14, 2017 - 3:14:59 AM - Nagendra Back To Top

Hi,

This is very helpfull. Have one question on approvals.

Is there a way to see who approved the chagned set. I am not getting this information anywhere.

 

Thasnks,

Nagendra.S 

 


Monday, February 13, 2017 - 2:57:56 AM - Chitrasen Back To Top

 

 Thanks good one. Just I have started working on 2016, it will help me.

 

Thanks

Chitrasen

 


Learn more about SQL Server tools