THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Master Data Services Team

Blog for the Microsoft SQL Server Master Data Services (MDS) team. Blog posts are contributed by various team members.

Enabling Human Workflow – Part 4: Creating the Business Rules

(this post was contributed by Brian Barnett, Senior Software Engineer on the MDS Team)

Up until this point we have been laying the foundation for enabling the human workflow. Along the way (Part 1, Part 2, and Part 3) you’ve been exposed to other features within MDS. Now, we are going to be creating the business rules that drive the human workflow. See Part 1 to review the workflow diagram.

*Note - This post references some features that are forthcoming in the future release of MDS and not available in the CTPs.  It gives you an early look at what is coming to help plan for workflow scenarios such as this.

Step 8: Create the business rules to enable the human workflow

Here is what you will be learning:

  1. How to set business rule priorities.
  2. How to set email notifications to user groups.
  3. How to use the “has changed” condition.
  4. How to use an entity’s domain-based attribute’s attributes in a rule condition or action component.
  5. How to set up compound conditions.

Here are the business rules we will need to add. Priority comes into play and is very important. It determines the order the rules are run. The rules will be processed in order beginning with the lowest value. The sample Product model comes with several business rules (check those out too) and the first sample rule has a priority of 10 so I decided to give the “New Product” rule below a priority of 5 so it runs first.

Notifications are sent when validation rules are broken. That is why we are setting the ProductStatus attribute to not valid below, to create a validation issue.

Priority

Name

Description

Rule

Notification (group)

5

New Product

After a product is entered default its status to New.

IF

None

THEN

ProductStatus defaults to New

 

50

New Bike Product

Notify the Bike Inventory Dept that a new bike has been entered so they can add the cost information.

IF

DBA.ProductSubCategory.ProductCategory is equal to 1 AND ProductStatus is equal to New

THEN

ProductStatus is not valid

MDS Bikes Inventory Dept

60

New Accessory Product

Notify the Accessory Inventory Dept that a new accessory has been entered so they can add the cost information.

IF

DBA.ProductSubCategory.ProductCategory is equal to 4 AND ProductStatus is equal to New

THEN

ProductStatus is not valid

MDS Accessories Inventory Dept

70

Costs changes need review

If any of the three costs attributes that we added to change tracking group 1 are changed then set the product’s status to ReviewCost.

IF

has changed in group 1

THEN

ProductStatus equals ReviewCost

 

80

Bike auto approve

Auto approve cost under the specified amount.

IF

DBA.ProductSubCategory.ProductCategory is equal to 1 AND StandardCost is less than 500.00

THEN

ProductStatus equals Approved

 

90

Accessory auto approve

Auto-approve cost under the specified amount.

IF

DBA.ProductSubCategory.ProductCategory is equal to 4 AND StandardCost is less than 10.00

THEN

ProductStatus equals Approved

 

100

Bike costs review

Notify the Bike Inventory Dept managers that bike cost information has been changed and it needs to be reviewed.

IF

DBA.ProductSubCategory.ProductCategory is equal to 1 AND ProductStatus is equal to ReviewCost

THEN

ProductStatus is not valid

MDS Bike Dept Mgmt

110

Accessory cost review

Notify the Accessory Inventory Dept managers that accessory cost information has been changed and it needs to be reviewed.

IF

DBA.ProductSubCategory.ProductCategory is equal to 4 AND ProductStatus is equal to ReviewCost

THEN

ProductStatus is not valid

MDS Accessories Dept Mgmt

120

Bike costs rejected

Notify the Bike Inventory Dept that bike cost information has been rejected and it needs to be adjusted.

IF

DBA.ProductSubCategory.ProductCategory is equal to 1 AND ProductStatus is equal to Rejected

THEN

ProductStatus is not valid

MDS Bikes Inventory Dept

130

Accessory costs rejected

Notify the Accessory Inventory Dept that accessory cost information has been rejected and it needs to be adjusted.

IF

DBA.ProductSubCategory.ProductCategory is equal to 4 AND ProductStatus is equal to Rejected

THEN

ProductStatus is not valid

MDS Accessories Inventory Dept

140

Discontinued status

Set a product’s status to discontinued.

IF

DiscontinuedItemInd is equal to Y

THEN

ProductStatus equals Discontinued

 

Let’s create two of the rules we need. I’ll leave the rest for you to enter as an exercise.

Let’s add the priority 50 rule from above. This rule will demonstrate how to use an entity’s domain-based attribute’s attributes in a rule condition or action component.

  1. In Master Data Manager, click System Administration.
  2. From the menu bar, point to Manage and click Business Rules.
  3. On the Business Rule Maintenance page, from the Model list, select the Product model.
  4. From the Entity list, select the Product entity.
  5. From the Member Type list, select the Leaf member type.
  6. From the Attribute list, leave the default of All.
  7. Click Add business rule.
  8. For the row that contains your new business rule, double-click a cell in the Priority, Name, Description, and Notification column to update the values as listed above.
  9. Click Edit selected business rule to open the Business Rule Designer.
  10. In the Components pane, expand the Conditions node.
  11. Click the “is equal to” condition and drag it to the IF pane's Conditions label.
  12. In the Entity-Specific Attributes pane, expand the “ProductSubCategory” attribute. Below it you will see all of the ProductSubCategory’s attributes. These are available for use in conditions and actions as well! For our rule we want to use the ProductCategory attribute. So drag ProductCategory to the Edit Condition pane's Select attribute label. You’ll notice that after you drop it the name will appear a little different - DBA.ProductSubCategory.ProductCategory. The “DBA” prefix indicates this is a domain-based attribute’s attribute.image  
  13. In the Edit Condition pane, select 1 in the Attribute value field. The value of 1 is the Code value for the Bike category.
  14. In the Edit Condition pane, click Save. The condition will be displayed.
  15. We need to add one more condition component – checking the product status. In the Components pane, click the “is equal to” condition and drag it to the IF pane’s And node.
  16. In the Entity-Specific Attributes pane, scroll down and click the “ProductStatus” attribute and drag it to the Edit Condition pane's Select attribute label.
  17. In the Edit Condition pane, select New in the Attribute value field.
  18. In the Edit Condition pane, click Save. The condition will be displayed.
  19. Now let’s add the action. In the Components pane, expand the Actions and then the Validation nodes.
  20. Click the “is not valid” action and drag it to the THEN pane's Actions label.
  21. In the Entity-Specific Attributes pane, scroll down and click the “ProductStatus” attribute and drag it to the Edit Action pane's Select attribute label.
  22. In the Edit Action pane, click Save. The action will be displayed.
  23. Click the green Back button (not the IE back button) to return to the Business Rule Maintenance page.

Let’s add the priority 70 rule from above. This rule will demonstrate how to use the “has changed” condition.

  1. Click Add business rule.
  2. Click Edit selected business rule to open the Business Rule Designer.
  3. In the Components pane, expand the Conditions node.
  4. Click the “has changed” condition and drag it to the IF pane's Conditions label.
  5. In the Entity-Specific Attributes pane, click the “StandardCost” attribute and drag it to the Edit Condition pane's Select attribute label. In this case you can actually drag any attribute over because the “has changed” condition doesn’t require an attribute; however, the Edit Condition pane does.
  6. In the Edit Condition pane, select 1 in the Change tracking group field.
  7. In the Edit Condition pane, click Save. The condition will be displayed (without the StandardCost attribute).
  8. Now let’s add the action. In the Components pane, expand the Actions and then the Change value nodes.
  9. Click the “equals” action and drag it to the THEN pane's Actions label.
  10. In the Entity-Specific Attributes pane, scroll down and click the “ProductStatus” attribute and drag it to the Edit Action pane's Select attribute label.
  11. In the Edit Action pane, select RvwCost in the Attribute value field.
  12. In the Edit Action pane, click Save. The condition will be displayed.
  13. Click the green Back button (not the IE back button) to return to the Business Rule Maintenance page.

Once all the business rules have been entered, on the Business Rule Maintenance page click the Publish button. On the confirmation dialog box, click OK. The rules’ statuses will change to Active.

Try it out

Since MDS uses Windows integrated security, to test with different users you’ll need to change the settings in IE to prompt you for the user name and password. Then log in as the various users to test out the different parts of the workflow.

In IE, select Tools | Internet Options.

  1. Select the Security tab.
  2. Select Local intranet
  3. Click Custom level…
  4. Scroll down to the Logon section and choose Prompt for user name and password.
  5. Press the OK button until the Internet Options dialog is closed.

imageimage

Before we begin testing the workflow you may want to log in as each of your users. This will do a couple of things automatically:

  1. Add the user to the MDS user list
  2. Add the user to the MDS group’s membership list

If you are using local users you will need to update their email address in MDS. Follow these steps for each user.

  1. In Master Data Manager, click User and Group Permissions.
  2. On the Users page, click the context menu button (down arrow) next to the user and select Edit | General.
  3. Click the Edit button.
  4. Update the email address then click the Save button.

Entering a new Bike product

Now that the rules are published you are ready to try them out.

  1. Log into Master Data Manager as a user in the MDS Product Administrator group.
  2. In Master Data Manager, select the Product model and VERSION_1 version.
  3. Select Explorer.
  4. On the Explorer page click the Edit button.
  5. Click the Add member button to add a new member.
  6. Enter a Name and Code then click Save.
  7. Enter a ProductSubCategory. Select either of the following because each of these are under the Bikes (Code = 1) ProductCategory, which we used in our rules:
    1. Mountain Bike
    2. Road Bike
    3. Touring Bike
  8. An email notification will be sent to the users of the MDS Bikes Inventory Dept group.

Entering Bike cost

  1. Click on the link in the MDS Notification email
  2. Log into Master Data Manager as a user in the MDS Bike Inventory Dept group.
  3. On the member edit page, on the Inventory tab, enter values for the StandardCost, DealerCost, and MSRP attributes. You can test the workflow by entering StandardCost values less than 500 and values greater than 500. This user should only have update permission to the attributes on the Inventory tab. All attributes on the other tabs should be read only.
  4. If you enter a StandardCost value < 500 the product’s status should be set to Approve.
  5. If you enter a StandardCost value => 500 the product’s status should be set to Review Cost and users of the MDS Bike Dept Mgmt group will receive an email to review the costs.

Reviewing Bike cost

  1. Click on the link in the MDS Notification email
  2. Log into Master Data Manager as a user in the MDS Bike Dept Mgmt group.
  3. On the member edit page, on the Inventory tab, review values for the StandardCost.
  4. On the System tab, change the ProductStatus attribute to either Rejected or Approved.
  5. Click Save.

This is an example of the notification email you should receive.

image

As you can see, enabling human workflow in MDS is a powerful and creative way of using business rules.

Published Thursday, February 18, 2010 8:49 AM by mattande
Filed under: ,

Comments

 

Abhishek Madan said:

Hello Brian,

How exactly do we add groups/users to a notification group? I would really appreciate your help on this.

Thank you!

Abhishek Madan

March 17, 2010 6:31 AM
 

HawkeyeDBA said:

How do you set the condition if IS NUMERIC = FALSE?

April 7, 2010 5:44 PM
 

mattande said:

Hi HawkeyeDBA,

We currently do not support that semantic but you can achieve the same result by using the 'contains pattern' semantic which allows you to use the RegEx pattern to detect numerics.

Thanks,

Brian

April 12, 2010 10:34 AM
 

mattande said:

Hi Abhishek,

Business Rules only allows you to select a single notification user or a single notification group.  Users and User Groups are added to MDS in the Security area.  Because MDS uses Windows Integrated security, adding users to user groups must be done outside MDS via Active Directory or adding users to local user groups via the Server Manager.

Thanks,

Brian

April 12, 2010 10:38 AM
 

David Tappan said:

Hello,

When I edit an attribute in an entity, there is no setting the enable change tracking.  What have I missed?  Change tracking is enabled on the database.  It is not enabled on any of the tables I checked, but I'm not sure which to enable it on, if any.  BTW, I upgraded my MDS from CTP to RTM.

May 6, 2010 4:06 PM
 

mattande said:

(this post was contributed by John Burrows, Lead Program Manager on the MDS Team)

Hi David -

I see that you were able to get help on the MSDN forums for this issue.  I did want to close the loop for others that may encounter this.  The first issue is that SQL Server doesn't support upgrades from CTP releases to RTM.

http://sqlblog.com/blogs/buck_woody/archive/2010/04/26/upgrades-from-beta-or-ctp-sql-server-software-is-not-supported.aspx

Secondly, MDS has a separate installer so one needs to use it to uninstall the CTP and install the RTM version so that change tracking is available.

Finally, one can use the model deployment wizard to move models that you may have created in the CTP version to move them to your fresh install of RTM.

Cheers,

John

May 11, 2010 7:24 PM
 

Roger said:

I dont know why, but my business rules isnt working when I update an attribute using the tblStgMemberAttribute.

It only works when I manually change this attibute on the MDS UI.

I did a subscription view and saw the "ChangeTrackingMask". It doesnt change to 1 when I update using the SSMS, only using the IE!

help me anyone... And sorry for my poor english, rs.

January 18, 2011 1:14 PM
 

Suzanne Selhorn - MSFT said:

Roger,

Is your business rule a "change tracking" rule? In SQL Server 2008 R2, staged data is not considered "changed" and so it is not captured by change tracking business rules.

-Suzanne

January 18, 2011 1:34 PM
 

Roger said:

Suzanne, thanks for the fast reply.

I did a script to put in a future job. That script take the data from a DW database and insert on the stagging area, then I process the data to finally validate it. All that in the script.

For the test, I changed one value to see if the business rules works and the validation failed. That rule just notify me when the change that ocurres on the Name atributte of the entity.

And it only works when I change this value manually on the MDS website.

Am I doing something wrong?

obs: When I NOT validate, an interrogation icon '?' appears on the changed member, but the business rules don't get it.

Is that another way to insert the data on the MDS validanting the changes?

January 19, 2011 1:45 PM
 

Suzanne Selhorn - MSFT said:

Please post your question on the MSDN forums:

http://social.msdn.microsoft.com/Forums/en-US/sqlmds/threads

And if you can, post a screen shot of your business rule.

Thanks

January 19, 2011 1:56 PM
 

Roger said:

January 19, 2011 2:23 PM
 

Raji said:

Other than sending mail notification, is there any possibility to achieve the Human work flow

June 11, 2014 9:27 AM
New Comments to this post are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement