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 1: Changing your model

 

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

In my last post, I showed how to create a simple validation business rule. In this post, we are going to look at more advanced capabilities by using business rules to enable human workflow via email notifications. As with the last post, we will be using the sample Product model supplied with SQL Server 2008 R2 Master Data Services (MDS).

Because there is a lot I’ll be covering with this scenario, I’ll be breaking it into several parts. In this post I’ll lay out the scenario and walk through some of the initial model changes required.

Human Workflow Scenario

We will be creating a product notification workflow. To keep the scenario somewhat simple, we will look at a subset of the Product entity’s attributes but you can apply the same concepts to other attributes.

Here’s the scenario. Our business manager wants you to create a product workflow (see below).

ProductStatus state transition

We have two product lines we are concerned about – bikes and accessories. When a new bike is added, an email notification should be sent to the MDS Bike Inventory Dept so they can enter cost information. When a new accessory is added, an email notification should be sent to the MDS Accessories Inventory Dept to enter cost information. “Cost” in the diagram above and here refers to three cost attributes on the Product entity: StandardCost, DealerCosts, and MSRP. If any of the cost attributes are changed, then we need to change the product status to “Costs under review” and send an email to the management teams to review the changes, allowing them to approve or reject the changes. If bike costs need to be reviewed, then an email should be sent to the MDS Bikes Dept Management. If accessory costs need to be reviewed, an email should be sent to the MDS Accessories Dept Management. However, if the StandardCost for a bike is less than $500 or the StandardCost for an accessory is less than $10, then the change should be approved automatically. If any product is discontinued, then the status should be set to Discontinued.

Steps required

To accomplish these requirements, we need to do the following:

  1. Add a new ProductStatus entity.
  2. Add a new ProductStatus domain-based attribute to the Product entity.
  3. Add the ProductStatus domain-based attribute to the System attribute group.
  4. Add cost attributes to a change tracking group.
  5. Add list of members to the ProductStatus entity.
  6. Set up the proper security permissions for the groups.
  7. Configure MDS to send out email notifications.
  8. Create the business rules to enable the human workflow.

A lot of what we will do is one-time setup and can be used to facilitate other scenarios in other parts of MDS. I’ve described these steps in detail to help you understand some of the other MDS features. After the foundation is laid, building on it will be quicker.

Ok, let’s get started.

Step 1: Add a new ProductStatus entity

Follow these steps to add a new entity to contain the product status list of values.

  1. In Master Data Manager, click System Administration.
  2. On the Model Explorer page, from the menu bar, point to Manage and click Entities.
  3. Select the Product model from the Model dropdown list.
  4. Click the Add entity button.
  5. Enter ProductStatus in the Entity name field.
  6. Select No to Enable explicit hierarchies and collections.
  7. Click Save.

Step 2: Add a new ProductStatus domain-based attribute to the Product entity

Now we’ll add a ProductStatus domain-based attribute (DBA) to the Product entity to store the product’s status. You can do this one of two ways. The first is through Entity attribute maintenance and the second is from the System Admin Explorer page. I’m going to show you the second way.

If you just completed Step 1 above then you should be on the Entity Maintenance page. Follow these steps.

  1. On the Entity Maintenance page, on the menu bar, click Explorer.
  2. Click the + next to the Product model to expand it.
  3. Click the + next to the Product entity to expand it.
  4. Click the Product entity to display all the available Product model entities on the right-hand side of the screen.
  5. Next click the ProductStatus entity in the list on the right and drag it to the Product entity on the left (see screen shot below).
  6. You’ll see a “Processing. Please wait” message. After a few seconds you will see the ProductStatus domain-based attribute in the bottom of the list on the left.

image

Step 3: Add the ProductStatus domain-based attribute to the System attribute group

Attribute groups are a way to group related attributes together. They are displayed as separate tabs in member grids in Explorer. They are also a useful way to apply permissions to a group of attributes all at once.

Because attribute groups exist for the Product entity, you must also add the ProductStatus domain-based attribute to an attribute group for it to appear in Explorer.  Let’s add it to the System attribute group.

If you’ve just completed Step 2 above then you should be on the Model Explorer page. Follow these steps.

  1. On the Model Explorer page, from the menu bar, point to Manage and click Attribute Groups.
  2. Select the Product model from the Model dropdown list.
  3. Select the Product entity from the Entity dropdown list.
  4. Under Leaf Groups, click the + to expand the System attribute group.
  5. Click the + next to Attributes to expand the list of System attributes.
  6. Click the Attributes node.
  7. Click the Edit button just above Leaf Groups.
  8. On the bottom of the screen you will see an Available and an Assigned list of attributes. The Assigned list contains the attributes assigned to the attribute group. Scroll to the bottom of the Available list to find the ProductStatus attribute.
  9. Select the ProductStatus attribute and click the Add button (right arrow).
  10. Click the Save button that is just above the Available list.
  11. The ProductStatus attribute should now appear under the System Attributes node (see below)

clip_image007[6]

Step 4: Add the cost attributes to a change tracking group

We need to track the changes for the following cost attributes: StandardCost, DealerCost, and MSRP. We will need to modify two settings for each attribute to enable MDS to detect changes in the attribute values.

The settings are Enable change tracking and Change tracking group. Change tracking group allows you to create several change tracking groups with different attributes. For example, group 1 could contain StandardCost, DealerCostand MSRP; while group 2 could contain Color, Class, and Style.

Follow these steps to add an attribute to a change tracking group:

  1. In Master Data Manager, click System Administration.
  2. On the Model Explorer page, from the menu bar, point to Manage and click Entities.
  3. Select the Product model from the Model dropdown list.
  4. In the list of entities select the row for the Product entity.
  5. Click Edit selected entity.
  6. On the Edit Entity page, select the StandardCost attribute from the Leaf attributes list and click the Edit attribute button.
  7. Select the Enable change tracking check box and set the Change tracking group to 1.

clip_image009[6]

  1. Click Save attribute.
  2. Repeat this procedure for the DealerCost and the MSRP attributes. Use the same change tracking group number for each attribute in the group.

Step 5: Add list of members to the ProductStatus entity

Add the following members to the ProductStatus entity.

Name

Code

New

New

Review Cost

ReviewCost

Rejected

Rejected

Approved

Approved

Discontinued

Discontinued

  1. In Master Data Manager, select the Product model and a version then click Explorer.
  2. On the Model View page, from the menu bar, point to Entities and click ProductStatus.
  3. Click the Add button.
  4. Enter the Name and Code, as listed above, for each member. For the first four members click the Save and add another button after entering the Name and Code. On the fifth one click the Save and go back button to return to the member grid.

We now have the necessary model changes in place.  Next time we will add the security groups we need and set their permissions.

*Update - 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.

Published Monday, February 15, 2010 11:42 AM by mattande
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Greeny said:

Thanks for the nice article!

One question, how do I enable the change tracking for my custom entities. I don't see the "Enable change tracking"-checkbox at all when I go editing the attribute settings.

What's wrong there ? I don't find any sections on change tracking in the MSDN either.

February 16, 2010 5:04 AM
 

Master Data Services Team said:

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

February 17, 2010 10:44 AM
 

Master Data Services Team said:

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

February 17, 2010 10:57 AM
 

Master Data Services Team said:

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

February 18, 2010 10:10 AM
 

mattande said:

Hello Greeny,

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.

Thanks,

Brian

February 18, 2010 2:54 PM
 

Abhishek Madan said:

Hello Brian,

May we expect the "Enable Change Tracking" and other Business Rule Conditions to be avialble in the RTM version of the product... Or is it still pending?

Secondly, I wanted to build a Demo for a worflow for apporval...Is there anything readily available that I could even look at.

Thank you!

Abhishek Madan

March 17, 2010 6:29 AM
 

mattande said:

Hello Abhishek,

Yes, the "Enable Change Tracking" feature will be part of the RTM version.

Thanks,

Brian

March 18, 2010 10:10 AM
 

cyssima said:

Hello, does MDS support some programable workflow (business rules) to write them in C# and then use to validate mds data modifications?

March 18, 2010 2:52 PM
 

mattande said:

Hi Cyssima,

Check out this new article on MSDN.

SharePoint Workflow Integration with Master Data Services

http://msdn.microsoft.com/en-us/library/ff459274.aspx

Thanks,

Brian

March 19, 2010 9:21 AM
 

David Chrastecky said:

Hi Brian,

do you have any examples of integrating change tracking with BizTalk (e.g. publish change tracking group attribute information to BizTalk)?

Thanks

David Chrastecky

March 13, 2011 8:48 PM
 

Company Logo Design said:

Useful information shared..I am very pleased to study this article..Many thanks for giving us nice information.

January 4, 2012 1:48 AM
 

Template Design said:

Thanks for sharing this valuable piece of information with us. I am glad to find this stuff here. Keep up the good work.

January 16, 2012 1:16 AM
 

affordable web hosting said:

Hi, thank you for sharing this info. Was just browsing through the net in my office and happened upon your blog. It is really very well written and quit comprehensive in explaining with a very simple language.

February 20, 2012 1:17 AM
 

Web hosting in Chile said:

This is a good piece of writing made for this particular topic. I would link it back to your website though. I was wondering if I could use this write-up on my website.

April 30, 2012 6:22 AM
 

Jenny Simpsons said:

October 26, 2012 4:37 PM
 

Gena23 said:

They're quantities of problems you ought to understand concerning submission. Common guys aren't ready recognize all concerning such stuff.web log posting service "cheap-link-building.com"will facilitate with such a drag sure. You'll not even do a little efforts to envision your web site optimized!

http://www.youtube.com/watch?v=2spNzWmeJIw

November 8, 2012 10:56 AM
 

Fosamax Class Action Lawsuit said:

This post references some options that ar forthcoming within the future unharness of MDS and not out there within the CTPs. It provides you AN early scrutinize what's returning to assist arrange for work flow eventualities like this.

Thanks!

http://www.youtube.com/watch?v=9ouv4-Z92KI

January 8, 2013 2:18 PM
 

Audience Response System Rental said:

Still, the posts are too short for novices. Could you please extend them a little from next time? Thanks for the post.

February 8, 2013 4:23 AM
 

Interactive Response System said:

Still, the posts are too short for novices. Could you please extend them a little from next time? Thanks for the post.

March 4, 2013 9:53 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement