How to Configure the Master Data Services(MDS) to a workflow

Step 1: Install Master Data Services

First, install Master Data Services. For more information, see the SQL Server 2008 Books Online topic “How to: Install Master Data Services” (http://msdn.microsoft.com/en-us/library/ee633762(SQL.105).aspx).

This white paper uses C:\Program Files as the folder where Master Data Services is installed.

Step 2: Install Visual Studio

Install Visual Studio 2010 or later. You will use Visual Studio to create your workflow handler.

You can install Visual Studio from:

http://www.microsoft.com/visualstudio/en-us/products/2010/default.aspx

Step 3: Optionally Install SharePoint

If you use Master Data Services to start a SharePoint Workflow, Master Data Services requires a version of SharePoint to be on the server where Master Data Services is installed. This version of SharePoint can host the workflow, but it does not need to. If you plan to use a custom workflow instead of a SharePoint workflow, you do not need to install SharePoint.

The minimum version of SharePoint required is SharePoint Foundation 2010. You can install SharePoint Foundation from:

http://technet.microsoft.com/en-us/sharepoint/ee263910.aspx

In certain situations, the SharePoint installation will stop the default web site on the server and set the SharePoint site to handle all requests coming to port 80. If you have set up your Master Data Services site in the default web site, or to use port 80, these will conflict. To fix this problem, use Internet Information Services (IIS) manager to configure either Master Data Services or SharePoint to use a port other than port 80.

If Master Data Services is installed in the default web site, you will also need to restart the default web site.

Note: The required collation for SharePoint Foundation database installations is Latin1_General_C1_AS_KS_WS. Other collations are not supported. For more information about setting up the SharePoint Server, see the SharePoint topic “Setting Up the Development Environment for SharePoint Server” (http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx)

Step 4: Create a Local User for the MDS(Master Data Services) Integration Service

For the greatest security, create a local user on the Master Data Services server and grant this user only the permissions needed to perform the workflow operations that are detailed in subsequent procedures in this document. Alternately, if you will use SharePoint workflows, you can use the W3WP identity used by the SharePoint Application Pool in Internet Information Services (IIS), normally Network Service. The remainder of this document shows you how to configure your system to use the mds_workflow_service Windows user you create in this section. The steps are similar if you are using the W3WP SharePoint identity.

Create a local MDS Workflow service user account.

Open the Local Users and Groups manager. To open this component, click Run… on the Start Menu, type lusrmgr.msc, and press ENTER.Select the Users folder.Select New User… from the Action menu.Enter mds_workflow_service for User name and create a secure password. Click the Create button to create the user.

Grant stored procedure permissions to the MDS Workflow service user

For the greatest security, it is recommended that you configure your Master Data Services database to use Windows authentication. Alternately, you can use mixed authentication and create a SQL Server login and user.

Open SQL Server Management Studio and connect to the SQL Server Database Engine instance that hosts the Master Data Services database.

Create a new login for the mds_workflow_service user you created in the previous section. To create a new login, open the Security node in the Object Explorer, right click the Logins node, and select New Login…. In the Login dialog, enter <your server name>\mds_workflow_service for the Login name, make sure Windows authentication is selected, change the default database to the Master Data Services database, and click OK.

Create a user for the Master Data Services database and map it to the mds_workflow_service login. To create and map a user, right click the mds_workflow_service login in the Object Explorer, and select Properties. On the properties dialog, navigate to the User Mapping page, check the Map checkbox for the Master Data Services database, and click OK.

Grant the mds_workflow_service user permission to the stored procedure required for the MDS Workflow Integration service. To do this, open the Master Data Service database node in the Object Explorer, open the Security and Users nodes, right click the mds_workflow_service user, and select Properties. On the properties dialog, navigate to the Securables page, click the Search button and search for all objects of the stored procedures object type. Find [mdm].[udpExternalActionsGet] in the list and grant EXECUTE permission to it.

Step 5: Create a Workflow Handler in Visual Studio

There are two kinds of workflow handlers you can create:

a SharePoint workflow or a custom workflow.

A SharePoint workflow integrates with SharePoint by creating a workflow on a SharePoint site you specify. A custom workflow handler is a .NET class library you create that can perform any actions you specify.

Create a SharePoint workflow

Create the SharePoint workflow for your organization.

In Visual Studio 2010, create a new ‘Sequential Workflow’ project. This project type is found in the SharePoint 2010 templates.

An OnWorkflowActivated is included by default in the designer for your solution. Right-click the control and choose Generate Handlers.

Visual Studio generates a class that inherits from the SequentialWorkflowActivity class. The class contains a workflowId property that contains the ID of the workflow, a workflowProperties class that contains data associated with the activity, and a method named onWorkflowActivated1_Invoked that is called when the workflow is activated. If you want to perform any custom handling for the workflow, include it in this method. The data that is passed from the workflow service is stored as a string in workflowProperties.InitiationData.

Compile and deploy your workflow. To do this, right-click the project in Solution Explorer and click Deploy.

Create a custom workflow

A custom workflow is a .NET class library assembly that implements the Microsoft.MasterDataServices.Core.Workflow.IWorkflowTypeExtender interface. The MDS Workflow Integration service calls the StartWorkflow method to run your code. Follow these steps to create an assembly and configure the MDS Workflow Integration service to call its interface:

In Visual Studio, create a new Class Library project that uses the language of your choice. To create a C# Class Library, select the Visual C#\Windows project types and select the Class Library template. Enter a name for your project, such as MDSWorkflowTest, and click OK.

Add a reference to Microsoft.MasterDataServices.Core.dll. In the Solution Explorer pane, right click References and select Add Reference…. In the Add Reference dialog, go to the Browse tab and navigate to C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin. Select Microsoft.MasterDataServices.Core.dll and click OK.

Open the Class1.cs file by double-clicking it in the Solution Explorer pane.

Add ‘using Microsoft.MasterDataServices.Core.Workflow;’ below the other using statements.

Rename your class to WorkflowTester, and inherit from IWorkflowTypeExtender. The class declaration should look something like the following:

public class WorkflowTester : IWorkflowTypeExtenderImplement the interface. Right-click IWorkflowTypeExtender in the code file, select Implement Interface\Implement Interface in the menu. This creates stub code for all members in the interface.

Add whatever code you desire to the StartWorkflow method. This method is called by the MDS Workflow Integration service to start your workflow. Complete sample code and a description of the parameters of the StartWorkflow method are included in the last section of this document.

Build the solution.

Copy your MDSWorkflowTest.dll assembly to the location of the MDS Workflow Integration service executable, in C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin.

Depending on the configuration of your sever, you may need to grant the mds_workflow_service user READ and EXECUTE permissions on MDSWorkflowTest.dll. In Windows Explorer, right-click MDSWorkflowTest.dll and select Properties. In the Properties dialog, go to the Security tab, click Edit…, click Add…, and add the <server name>\mds_workflow_service user. The mds_workflow_service user is granted READ and EXECUTE permissions by default. Click OK to close all the dialogs that have opened.

Grant the mds_workflow_service user any additional permissions it needs to perform the workflow operations, such as READ and WRITE permissions on another database in your system.

Step 6: Update the MDS(Master Data Services) Workflow Integration service Web Configuration File

Edit the Master Data Services Web configuration file to include the name of your Master Data Services server and database and your custom workflow assembly. If you are using a SharePoint workflow, you do not have to include assembly information.

Edit the Master Data Services web configuration file

If you are using Windows authentication you do not have to specify a user or password in the configuration file. Alternately, if you use SQL Server authentication, specify the User ID and Password of a SQL Server login that has the necessary permissions on the Master Data Services database.

On the server where Master Data Services is installed, open an elevated command prompt.
Open Microsoft.MasterDataServices.Workflow.exe.config in Notepad from C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin.

Find this setting:

<setting name="ConnectionString" serializeAs="String">

Update the value to reflect the server and database. If your SQL Server installation uses case-sensitive collation, then the name of the database must be entered in the same case as in the database.

For example,

<value>Server=myServer;Database=myDatabase;Integrated Security=True</value>
Below the ConnectionString setting add another <setting> tag for your custom workflow. For example:

<setting name="WorkflowTypeExtenders" serializeAs="String">
<value>TEST=MDSWorkflowTestLib.WorkflowTester, MDSWorkflowTestLib</value>
</setting>

The following guidelines will help you construct your <setting> tag:

· The inner text of the <value> tag is in the form of <Workflow ID>=<assembly-qualified workflow type name>.

· <Workflow ID> is a string you use to identify this workflow assembly when you create a business rule in Master Data Manager.

· <assembly-qualified workflow type name> is the namespace-qualified name of your workflow class, followed by a comma, followed by the display name of the assembly.

· If your assembly is strongly named, you also have to include version information and its PublicKeyToken.

· You can include multiple <setting> tags if you have created multiple workflow handlers for different kinds of workflows.

· If you are using a SharePoint workflow, you do not have to include this <setting> tag.

Save and close the file.

Depending on the confugration of your server, you may see an “Access is denied” error when you try to save the file. If this occurs, temporarily disable User Account Control (UAC) on the server. To do this, open Control Panel, click System and Security. Under Action Center, click Change User Account Control Settings. In the User Account Control Settings dialog, slide the bar to the bottom so that you are never notified. Click OK. Click Yes in the confirmation dialog. Restart your computer and repeat the above procedure to edit your configuration file. After saving the file, reset your UAC settings to the default level.

Step 7: Install and Start the Workflow Integration Service

Install and start the SQL Server MDS Workflow Integration service.

Grant read and execute permission to the MDS workflow user

Depending on the configuration of your server, you may need to grant the mds_workflow_service user READ and EXECUTE permissions on the MDS Workflow Integration service executable.

On the server where Master Data Services is installed, open Windows Explorer and go to C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin.

Right-click Microsoft.MasterDataServices.Workflow.exe and select Properties.

In the Properties dialog, go to the Security tab, click Edit…, click Add…, and add the <server name>\mds_workflow_service user. The mds_workflow_service user is granted READ and EXECUTE permissions by default.

Click OK to close all the dialogs that have opened.

Find the install utility

On the server where Master Data Services is installed, open an elevated command prompt.

Go to %windir%\Microsoft.NET.

If you have more than one Framework folder, determine which is the most recent and go to that folder.

Go to the Framework folder’s subfolder, for example Framework\v4.0.30319.

Confirm that InstallUtil.exe is in the folder.

Copy the path for InstallUtil.exe, for example:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe.

Install the service

At the command prompt, go to C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin.

Type: C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil Microsoft.MasterDataServices.Workflow.exe

Press ENTER. A dialog is displayed, prompting for a user name and password.

Type <server name>\mds_workflow_service for user name and the password you specified when you created the user. The user must have Read and Execute permissions to any assemblies that handle the workflow processing, as well as appropriate access to any resources required by the workflow handler assembly, such as Read-Write access to a database that the workflow handler will modify.

Click OK.

Start the Service

From the Start menu, click Run.

Type: Services.msc

Click OK. The Services snap-in opens.

Find the service called SQL Server MDS Workflow Integration and select it.

Click the Start link.

After the service starts, close the Services snap-in.

Step 8: Create and Apply Business Rules in Master Data Manager

Create a business rule in Master Data Manager. When data is validated against the business rule, the Service Broker queue in the Master Data Services database is populated. The service periodically checks the queue, sends the data to the specified workflow handler, and then clears the queue.

Create and Publish a Business Rule

You must first create and publish the business rule that will start the workflow when applied.

You should ensure that your business rule contains actions that change attribute values, so that the rule evaluates to false after it has been applied once. For example, your business rule might evaluate to true when a Price attribute value is greater than 500 and the Approved attribute value is blank. The rule can then include two actions: one to set the Approved attribute value to Pending and one to start the workflow.

Alternatively, you may want to create a rule that uses the “has changed” condition and add your attributes to change tracking groups.

Note: This functionality is not available in SQL Server 2008 R2 November Community Technology Preview and earlier

To create a business rule in Master Data Manager, you must have permission to the System Administration functional area and be a model administrator for the model you want to create the business rule for. For more information, see Administrators (Master Data Services).

In Master Data Manager, click System Administration.

From the menu bar, point to Manage and click Business Rules.

On the Business Rule Maintenance page, select a model, entity, member type, and attribute.

Click Add business rule. A row is added to the table and is highlighted.

Click Edit selected business rule.

In the Components pane, expand the Conditions node.

Drag conditions to the IF pane’s Conditions node.

In the Entity-Specific Attributes pane, click an attribute and drag it to the Edit Action pane’s Select attribute label.

In the Edit Condition pane, complete any fields and click Save item.

In the Components pane, expand the Actions node.

Under External action, drag Start Workflow to the THEN pane’s Action label.

In the Entity-Specific Attributes pane, click any attribute and drag it to the Edit Action pane’s Select attribute label. This attribute has no bearing on the workflow process.

In the Edit Action pane, in the Workflow type box, type the tag that identifies your workflow handler. To start a SharePoint workflow, type SPWF. Otherwise, type the tag you specified in the web configuration file for your assembly, for example, TEST.

Optionally, select the Include member data check box. Choose this to include attribute names and values in the XML that is passed to the workflow handler.

In the Workflow site box, type the name of a website. For a SharePoint workflow, this must be your SharePoint site. For example, http://site_name. For a custom workflow this may not apply, but can be used for added context.

In the Workflow name box, type the name of your workflow from Visual Studio. In Visual Studio Solution Explorer, this value is in the Display Name field. For a custom workflow this may not apply, but can be used for added context.

At the bottom of the page, click Save.

At the top of the page, click Back. The Business Rule Maintenance page opens.
Click Publish business rules.

On the confirmation dialog box, click OK. The rule's status changes to Active.

Apply Business Rules

Now you must apply the business rule to your data. This action passes the data to the Service Broker queue.

From the Master Data Manager home page, click Explorer.

Edit the entity with members you want to validate.
Click Apply business rules.
The SQL Server Service Broker queue is populated. When the service checks the queue, it sends the data to the workflow handler and clears the queue.

Troubleshooting

When configuring Master Data Services to work with a workflow, you might encounter any of the following issues.

The Workflow Handler Doesn’t Receive Data

If the workflow handler doesn’t receive data, you can try debugging the workflow service or viewing the Service Broker queue to determine if the correct data is still in the queue.

Debug the Workflow Servic.

Stop the service if it is running.

Open a command prompt.

Go to the location of your service and run the service in console mode by typing: Microsoft.MasterDataServices.Workflow.exe -console

Press ENTER.

In Master Data Manager, update your attribute and apply business rules again. Detailed logs are displayed in the console window.

View the Service Broker Queue

The Service Broker queue that contains the master data passed as part of the workflow is: mdm.microsoft/mdm/queue/externalaction. This queue can be found in SQL Server Management Studio by opening the Master Data Services database in Object Explorer, then opening Service Broker and Queues. Right-click the queue and select Select Top 1000 Rows to view the top 1000 rows in the queue.

If the service cleared the queue properly, this queue should be empty.

If there is data in the queue that you want to remove, you can run the following SQL script to clear the queue. You must change myDatabase to the name of your Master Data Services database.

declare @conversation uniqueidentifier

while exists (select 1 from [myDatabase].[mdm].[microsoft/mdm/queue/externalaction])

begin

set @conversation = (select top 1 conversation_handle from [myDatabase].[mdm].[microsoft/mdm/queue/externalaction] )

end conversation @conversation with cleanup

end

Custom Workflow Example Code and XML Description

When you create a custom workflow class library, you create a class that implements the Microsoft.MasterDataServices.Core.Workflow.IWorkflowTypeExtender interface. This interface includes one method, StartWorkflow, that is called by the MDS Workflow Integration service when a workflow starts. The StartWorkflow method contains two parameters: workflowType contains the Workflow type you entered in Master Data Manager, and dataElement contains metadata and item data for the item that triggered the workflow business rule.

Code Example

The following code example shows how you how to implement the StartWorkflow method to extract the Name, Code, and LastChgUserName attributes from the XML data for the element that triggered the workflow business rule, and how to call a stored procedure to insert them into another database.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using System.Data.SqlClient;

using System.Xml;

using Microsoft.MasterDataServices.Core.Workflow;

namespace MDSWorkflowTestLib

{

public class WorkflowTester : IWorkflowTypeExtender

{

#region IWorkflowTypeExtender Members

public void StartWorkflow(string workflowType, System.Xml.XmlElement dataElement)

{

// Extract the attributes we want out of the element data.

XmlNode NameNode = dataElement.SelectSingleNode("//ExternalAction/MemberData/Name");

XmlNode CodeNode = dataElement.SelectSingleNode("//ExternalAction/MemberData/Code");

XmlNode EnteringUserNode = dataElement.SelectSingleNode("//ExternalAction/MemberData/LastChgUserName");

// Open a connection on the workflow database.

SqlConnection workflowConn = new SqlConnection(@"Data Source=<Server instance>; Initial Catalog=WorkflowTest; Integrated Security=True");

// Create a command to call the stored procedure that adds a new user to the workflow database.

SqlCommand addCustomerCommand = new SqlCommand("AddNewCustomer", workflowConn);

addCustomerCommand.CommandType = System.Data.CommandType.StoredProcedure;

addCustomerCommand.Parameters.Add(new SqlParameter("@Name", NameNode.InnerText));

addCustomerCommand.Parameters.Add(new SqlParameter("@Code", CodeNode.InnerText));

addCustomerCommand.Parameters.Add(new SqlParameter("@EnteringUser", EnteringUserNode.InnerText));

// Execute the command.

workflowConn.Open();

addCustomerCommand.ExecuteNonQuery();

workflowConn.Close();

}

#endregion

}

Item Data XML

The StartWorkflow method receives a block of XML that contains metadata and data about the item that triggered the workflow business rule. The following example shows what the XML looks like:

<Server_URL>This is my test!</Server_URL>

<Action_ID>Test Workflow</Action_ID>

<Model_ID>5</Model_ID>

<Model_Name>Customer</Model_Name>

<Entity_ID>34</Entity_ID>

<Entity_Name>Customer</Entity_Name>

<Version_ID>8</Version_ID>

<MemberType_ID>1</MemberType_ID>

<Member_ID>12</Member_ID>

<Version_ID>8</Version_ID>

<ValidationStatus_ID>3</ValidationStatus_ID>

<EnterUserName>MyUserName</EnterUserName>

<EnterVersionName>VERSION_1</EnterVersionName>

<LastChgUserName>MyUserName</LastChgUserName>

<LastChgVersionName>VERSION_1</LastChgVersionName>

<Name>Test Customer</Name>

</MemberData>

</ExternalAction>

The following table describes some of the tags contained in this XML:

Tag	Description
<Type>	The Workflow type you entered in Master Data Manager to identify which custom workflow assembly to load.
<SendData>	A Boolean value controlled by the Include member data in the message checkbox in Master Data Manager. A value of 1 means that the <MemberData> section is sent; otherwise the <MemberData> section is not sent.
<Server_URL>	The text you entered in the Workflow site text box in Master Data Manager.
<Action_ID>	The text you entered in the Workflow name text box in Master Data Manager.
<MemberData>	Contains the data of the member that triggered the workflow action. This is include only if the value of <SendData> is 1.
<Enterxxx>	This set of tags contains metadata about the creation of the member, such as when it was created and who created it.
<LastChgxxx>	This set of tags contains metadat about the last change made to the member, such as when the change was made and who made it.
<Name>	The first attribute of the member that was changed. This member contains only Name and Code attributes.
<Code>	The next attribute of the member that was changed. If this member contained more attributes, they would follow this one.

Enterprise AI & Cloud Architecture