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.

  • Publishing Master Data with Subscription Views - Part 1: The Basics

    (this post was contributed by Val Lovicz, Principal Program Manager on the MDS Team)

    Background

    This post is the first in a series on Subscription Views in SQL Server 2008 R2 Master Data Services (“MDS”). In this first post I will describe this feature and then provide step by step instructions for creating example views using the sample Product model delivered with MDS.

    Subscription views provide a simple way to extract and publish master data from MDS using Integration Services, SQL scripts or any other tools and techniques that access a standard SQL Server view. MDS administrators may generate subscription views as needed from the Integration Management - Export Page of the Master Data Manager Web User Interface (“UI”) or the ExportViewCreate operation of the Web services API.

    Subscription views are helpful because they denormalize the master data and apply recognizable names to columns. This makes them much easier to work with than the underlying tables which are normalized for performance and data integrity and have key-based names.

    Note: Direct queries against the MDS tables should be avoided because future product releases may not have the same schema objects.

    Once generated, subscription views appear as ordinary views within the “mdm” schema of the MDS SQL Server database. To access the views, you need to have the db_datareader role on the MDS database or be assigned a role with Select permission on the views.

    Note: Permission to access database views should only be granted to trusted individuals who may read all MDS data. Views do not enforce MDS application security permissions.

    Subscription views are best suited to bulk, batch-oriented exports of master data and may not be best for all integration scenarios. For event-based integration, use the change tracking notification feature. For programmatic access that enforces user security and application logic, use the Web services API.

    Managing the Subscription Views

    To browse and manage existing subscription views or generate new views, use the Export page in the UI.

    1. Go to the Master Data Manager URL in your web browser.

    2. Click Integration Management on the home page.

    3. Click Export on the menu bar.

    After following the above steps, you will see the list of Subscription Views, which is initially empty in a new MDS instance. Here you may add new subscription views, edit configurations of existing views, delete existing views or regenerate views as needed when the underlying model changes.

    All objects on the Export page are described in Books Online, so I will not describe the individual page elements here.

    Creating an Attribute View

    Let’s create a view of all products and their attributes using the sample Product model. To create this view, perform the following steps.

    1. Click the Add Subscription View button. The Create Subscription View Definition panel will appear.

    2. Enter a subscription view name like “_ProductLeafAttributes”. I added the underscore so that it will sort to the top of the list.

    3. Select the “Product” model.

    4. Select the “VERSION_1” version.

    5. Select the “Product” Entity.

    6. Select the “Leaf attributes” format.

    7. Click Save. The definition panel will close and the new view will now appear in the list.

    If you now connect to the MDS database using SQL Server Management Studio, you will see the newly created view in the database. Of course, you will need to have sufficient permissions directly in the database to connect and see the views.

    image

    Note that you may need to refresh the list of views if you already had the list open in Management Studio when you generated the new subscription view in MDS.

    Usefulness of an Attribute View

    The “Leaf Attributes” view format lists all the leaf entity members and includes columns for all attributes. In our product example, domain-based attributes are denormalized into the Code and Name of the referenced members in other entities rather than providing meaningless key values. Other information, such as the Version Name and Version Flag is also provided. Each column is named based on the user-defined model object names for easy recognition.

    image

    Creating an Explicit Hierarchy View

    Let’s create a level-based view of explicit hierarchies applied to products using the sample Product model. To create this view, perform the following steps.

    1. Click the Add Subscription View button. The Create Subscription View Definition panel will appear.

    2. Enter a subscription view name like “_ProductHierarchyLevels”.

    3. Select the “Product” model.

    4. Select the “VERSION_1” version.

    5. Select the “Product” Entity.

    6. Select the “Explicit levels” format.

    7. Change Level to 4 so that we expose up to four level columns.

    8. Click Save. The definition panel will close and the new view will now appear in the list.

    Usefulness of a Hierarchy View

    Hierarchies are often represented in relational tables and views as level-based columns (i.e. each column represents a level) or as parent-child relationships (e.g. each row represents a relationship of one child to one parent). Subscribing systems will impose preferred data formats. For example, a relational data warehouse would typically require a level-based view.

    image

    Selecting Versions

    Subscription views filter versions based on a version or a version flag. Use version flags as the recommended approach when you expect the published version to change periodically. Version flags allow you to point at the currently desired version for export by moving the version flag from version to version; this avoids the need to regenerate the view with each new version.

    Model Changes; Regenerating a View

    Over time, you may need to expand or otherwise revise your master data model. If you already have subscription views, those views may become out of sync with the underlying model. For example, there may be a new attribute that is not included in the view. Because subscription view changes can destabilize your processes that consume those views, MDS lets administrators electively regenerate views (and sometimes requires deletion of views prior to a model change). As a guide, the Changed column in the list of views will show True for any views where the model has changed since view generation. To regenerate a view against the latest model: select the view, click Edit then click Save.

    Legal Notice

    © 2010 Microsoft Corporation. All rights reserved. This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only. Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

  • Creating a Bing Map URL with a Business Rule

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

    While using Bing Maps last night to look for directions I thought of a way to use MDS business rules to create a Bing Map URL link attribute. This may sound like merely a cool thing to try but not very useful. But, I’m going to show a couple of examples of how it could be useful to pique your interest for other possibilities.

    Here is the URL format we will need to follow to open Bing Maps to a specific address:

    http://bing.com/maps/default.aspx?where1=1%20Microsoft%20Way%20Redmond%20WA%2098052

    For more info see the Bing Maps help topic Build your own URL.

    I’m going to use the Customer sample model. We’ll need to add a link attribute to the customer entity then create a concatenation business rule to set the value of the link attribute. In this post I’m not giving the step-by-step, detailed instructions to create each item. Please see my Creating a Simple Business Rule and Enabling Human Workflow – Part 1 posts for specific instructions.

    Add the link attribute

    Add a new Free-form, link attribute to the Customer entity called BingMapURL.  Make sure the data type is Link.

    clip_image002

    Create the business rule

    Now let’s create a rule to populate its value.

    1. Create a new rule and then navigate to the Business Rules Designer page.
    2. In the Components pane, expand the Actions and Change value nodes.
    3. Click the “equals a concatenated value” action and drag it to the THEN pane's Action label.
    4. In the Entity-Specific Attributes pane, click the BingMapURL attribute and drag it to the Edit Action pane's Select attribute label.
    5. In the Entity-Specific Attributes pane, click and drag the following attributes to the “Value” node of the “equals a concatenated value” action.
      1. AddressLine1
      2. City
      3. StateProvince
      4. PostalCode
    6. At this point your Edit Action should look like this

    clip_image004

    Now let’s add the Bing Map specific URL pieces.

    1. Right click on AddressLine1 attribute and select “Insert text” from the context menu.
    2. Double click the new line and enter this text, without the quotes, “http://bing.com/maps/default.aspx?where1=”.
    3. Right click on the City attribute and select “Insert text” from the context menu.
    4. Double click the new line and enter this text, without the quotes, “%20”.
    5. Repeat steps 3 and 4 for the StateProvince and PostalCode attributes.
    6. Now your Edit Action should look like this.

    clip_image006

    1. In the Edit Action pane, click Save. The action will be displayed.

    image

    1. Click Back to return to the Business Rule Maintenance page.
    2. Optionally, on the Business Rules Maintenance page, for the row that contains your business rule, double-click a cell in the Name and Description columns to update the value.
    3. Click Publish Business Rules
    4. On the confirmation dialog box, click OK. The rule’s status will change to Active.

    Try it out

    Add a new customer then return to the Member grid. Scroll over to the right and you will see the populated BingMapURL.

    clip_image010

    Double click the link icon and then select View from the context menu.

    clip_image012

    A new browser window opens to the Bing Maps URL.

    clip_image014

    To prevent users from editing the BingMapURL attribute change the security permissions to Readonly. Other possibilities are to create another link attribute called BingMapDirectionsURL which would be the URL for directions from your company to the customer.

    Here’s another example, which I’ll not walk through but simply give you as an idea. What if you have an internal (or external) product catalog website? If you are managing Product data in MDS then you can add a ProductCatalogURL link attribute to the Product entity then follow the same steps we did with the BingMapURL to create it.  The link would be the URL to jump to that product in the catalog.

    If you want to take this a little further you could create an ASP.Net web application that utilizes the MDS API. You could create a product list page listing all the products. Each product listing would have a link to the product catalog using the ProductCatalogURL.

    There are a lot of possibilities. All it takes is a little creativity.

  • 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.

  • Enabling Human Workflow – Part 3: Configuring Email Notification

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

    In Part 1 you made the necessary model changes. In Part 2 you made the security changes to give the new groups the required security access and permissions . In this post we will continue with Step 7, configuring MDS to send out email notifications.

    *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 7: Configure MDS to send email notifications

    Select the Start | Programs | Microsoft SQL Server 2008 R2 | Master Data Services | Configuration Manager menu item.

    image

    Select Databases in the left panel. Then select Create Profile…

    image

    On the Create Database Mail Profile and Account screen fill in the information based on your environment. The Profile name and the Account name shown below are simply suggestions I've supplied but you are free to enter different names if you choose.

    image

    There is a System Setting called "Master Data Manager URL for Notifications".  The URL for your MDS application should be specified here.   Once the screen is completed click the OK button. Then click the Apply button. Database Mail should be set up and ready to use.

    If desired, you can test it out in SQL Server Management Studio. Expand Management then right-click Database Mail. Select Send Test E-Mail…

    image

    In Part 4 we will wrap things up by creating the business rules that drive the human workflow.

  • Enabling Human Workflow – Part 2: Granting permission to your data

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

    In Part 1 you made the necessary model changes. In this post we will continue with Step 6, making the security changes necessary to meet the requirements of our workflow scenario.

    Step 6: Set up the proper security permissions for the groups

    Based on our scenario, we want to send out email notifications to several different types of users based on the product line they work with and their responsibility with that product line. The best way to implement this is by creating groups, setting the group permissions, and then assigning users to these groups.

    The security model within MDS allows you to create very general to very granular access permissions on groups and users. MDS uses Windows integrated security - local and/or domain principals can be used. Therefore, the creation of users, groups, and user-group assignments is done outside of MDS. This needs to be done in either Active Directory or Server Manager (Configuration | Local Users and Groups). Within MDS, you simply select the local or domain users and groups that you desire to give access.

    In our scenario we will only be adding groups, since that is how we are going to be managing permissions. We will be setting up the following groups and permissions. You will need at least one user assigned to each group. Again, this user-group assignment must be done outside of MDS.

    The Functions, Models, and Hierarchy Members bullet points below correlate to tabs on the Group security page.

    • Functions – What functional areas of MDS the user is allowed to access.
    • Models - What model metadata the user is allowed to see and maintain.
    • Hierarchy Members - What hierarchy members the user is allowed to see and maintain.

    Group Security - Functions

    Here are the five groups we will be adding.

    MDS Product Administrator

    • Description – Members of this group have full access to the all products and have access to all functions.
    • Functions
      • Explorer, Version Management, Integration Management, System Administration, User and Group Permissions
    • Models
      • Model Product – Update
    • Hierarchy Members
      • No explicit permissions given, thus, has full access based on the update permission on the Product model.

    MDS Accessories Inventory Dept

    • Description – Members of this group maintain the Inventory information of the Bike Accessories product line.
    • Functions
      • Explorer
    • Models
      • Model Product - Read only
      • Attribute group Product:Product:Leaf:Inventory - Update
    • Hierarchy Members
      • Derived: Product: Category 4{Accessories} - Update

    MDS Accessories Dept Mgmt

    • Description – Members of this group manage the Bike Accessories product line.
    • Functions
      • Explorer, Version Management
    • Models
      • Model Product - Update
    • Hierarchy Members
      • Derived: Product: Category 4{Accessories} - Update

    MDS Bikes Inventory Dept

    • Description – Members of this group maintain the Inventory information of the Bike product line.
    • Functions
      • Explorer
    • Models
      • Model Product - Read only
      • Attribute group Product:Product:Leaf:Inventory - Update
    • Hierarchy Members
      • Derived: Product: Category 1{Bikes} - Update

    MDS Bikes Dept Mgmt

    • Description – Members of this group manage the Bike product line.
    • Functions
      • Explorer, Version Management
    • Models
      • Model Product - Update
    • Hierarchy Members
      • Derived: Product: Category 1{Bikes} - Update

    I’ll walk through creating one group here and will leave the rest as an exercise for you. Before you begin, ensure the users and groups exist in Active Directory and/or your local server.

    In Master Data Manager, click User and Group Permissions.

    1. On the Users page, from the menu bar click Manage Groups.
    2. Click the Add button.
    3. In the Groups field enter the domain\name of the groups, separated by a semi-colon.
    4. Optionally click the Check names button to verify the names exist.
    5. Click the OK button.

    Now that the groups have been added to MDS, let’s walk through setting permissions for the MDS Bike Inventory Dept group .

    1. On the Groups page, click the context menu button (down arrow) next to the MDS Bike Inventory Dept group and select Edit | Functions.
    2. Click the Edit button.
    3. Move Explorer from the Available functions list to the Assigned functions list.
    4. Click the Save and continue button.
    5. On the Model Permissions page, click the Edit button.
    6. Right-click on the Product model node and select Read-only from the context menu.
    7. Now we need to expand a few levels down to set permissions on the Inventory attribute group.
    8. Expand the Product model node as follows: Product –> Entities –> Product –> Leaf –> Attribute groups.
    9. Click on the Inventory attribute group node and select Update from the context menu.
    10. Click the Save and continue button.
    11. On the Hierarchy Member Permissions page, in the Hierarchy list, select Derived: Category.
    12. Click the Edit button.
    13. Expand the hierarchy as follows: Root à 2{Retail}.
    14. Click on the 1{Bikes} node and select Update from the context menu.
    15. Click the Save button.

    Below are what the Models and Hierarchy Members tabs should look like for the MDS Bikes Inventory Dept group.

    Group Security - Models

    Group Security - Hierarchy Members

    Follow similar steps as above to set permissions for the other groups.

    In Part 3 we will configure MDS to send out email notifications

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

  • 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.

  • Staging Examples and Troubleshooting

    (this post was contributed by Suzanne Selhorn, Technical Writer on the MDS Team)

    This article is applicable to Microsoft SQL Server 2008 R2 Master Data Services feature.

    Update Notice for SQL Server 2012 users: This blog article is applicable to SQL Server 2008 R2 Master Data Services.  MDS Staging was vastly improved in SQL Server 2012 MDS release and is called Entity Based Staging. Please watch this video intro  and refer to product documentation on the new 2012 MDS staging concepts.

    In this post I will provide some staging examples and give you some tips for troubleshooting. First I'll show examples of the data format required by the staging tables. Then I'll show how to use SQL scripts to import data, invoke the staging process, and then invoke the validation process. Finally, I'll give you some tips that can help with issues you might run into while staging data.

    For an introduction to the staging process, see my post: Importing Data by Using the Staging Process.

    Data Format Examples

    Creating members or collections
    You can use the Members staging table (tblStgMember) to create leaf members, consolidated members, or collections. For more information about the fields in this table, see Members Staging Table.

    The following example shows how you could create a leaf member, a consolidated member, and a collection, respectively.

    INSERT INTO mdm.tblStgMember (ModelName, HierarchyName, EntityName, MemberType_ID, MemberName, MemberCode) VALUES

    (N'Product', NULL, N'Product', 1, N'Mountain-100', N'BK-M101'),  
    (N'Product', N'Product Management', N'Product', 2, N'Men''s and Women''s Products', N'MW'),
    (N'Product', NULL, N'Product', 3, N'John''s Responsibility', N'JR')

    Updating attribute values
    You can use the Attributes staging table (tblStgMemberAttribute) to update attribute values. For more information about the fields in this table, see Attributes Staging Table.

    The following example shows how you could update an attribute for a leaf member, a consolidated member, and a collection.

    INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES

    (N'Product', N'Product', 1, N'BK-M101', N'DaysToManufacture',N'4'),
    (N'Product', N'Product', 2, N'MW', N'Owner', N'Gisli Olafsson'),
    (N'Product', N'Product', 3, N'JR', N'Description', N'John Yokim''s Products')

    You can also use this table to deactivate a member. Deactivating a member changes the MemberCode to a GUID and changes the AttributeValue to De-Activated so the member is no longer displayed in the UI.

    INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES

    (N'Product', N'Product', 1, N'BK-M101', N'MDMMemberStatus', N'De-Activated')

    To reactivate a member, use the following example.

    INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES

    (N'Product', N'Product', 1, N'B12DDC7B-DD25-4623-AAC6-BBE51A017D2F', N'MDMMemberStatus', N'Active')

    To determine the GUID that is the MemberCode, open the view: mds.viw_SYSTEM_SCHEMA_ENTITY. Find the name of the entity that contains the deleted member or collection. Note the value in the EntityTable column. Then open the table that was listed in the EntityTable field. Find the name of the member or collection and note the value in the Code column. That value is the GUID.

    Moving members in explicit hierarchies or adding members to collections
    You can use the parent child relationship staging table (tblStgRelationship) to move members in explicit hierarchies and to add members to collections. For more information about the fields in this table, see Parent Child Relationships Staging Table.

    The following example shows how you could stage these relationships.
    • The first line in this example contains the column names.
    • The second line designates the MW consolidated member as a parent of the BK-M101 leaf member in the Product Management explicit hierarchy.
    • The third line designates the BK-M101 leaf member as a sibling of (at the same level as) the BK-M202 leaf member in the Product Management explicit hierarchy.
    • The fourth line adds the JR collection to the C2 collection.
    • The fifth line adds the BK-M101 leaf member to the C2 collection.
    • The sixth line adds the MW consolidated member to the C2 collection.

    INSERT INTO mdm.tblStgRelationship (ModelName, EntityName, HierarchyName, MemberType_ID, MemberCode, TargetCode, TargetType_ID) VALUES

    (N'Product', N'Product', N'Product Management', 4, N'BK-M101', N'MW', 1),
    (N'Product', N'Product', N'Product Management', 4, N'BK-M202', N'BK-M101', 2),
    (N'Product', N'Product', NULL, 5, N'JR', N'C2', 1),
    (N'Product', N'Product', NULL, 5, N'BK-M101', N'C2', 1),
    (N'Product', N'Product', NULL, 5, N'MW', N'C2' , 1)

    SQL Scripting Examples 

    Invoking the Staging Process
    After the tables are populated, you can execute a stored procedure that batches your records and sets them as queued to run. The staging table records that will be batched have:

    • The model in the ModelName field.
    • The user’s name or no user name in the UserName field.

    The user must also be a model administrator.

    DECLARE @ModelName nVarchar(50) = 'Customer'
    DECLARE @UserName nvarchar(50)= 'DOMAIN\user_name'
    DECLARE @User_ID int
    DECLARE @Version_ID int

    SET @User_ID =    (SELECT ID 
                       FROM  mdm.tblUser u
                       WHERE u.UserName = @UserName )

    SET @Version_ID = (SELECT MAX(ID) 
                       FROM mdm.viw_SYSTEM_SCHEMA_VERSION 
                       WHERE Model_Name = @ModelName)

    EXECUTE mdm.udpStagingSweep @User_ID, @Version_ID, 1

    The 0 in this procedure indicates that the batch will be processed at the interval specified in Master Data Services Configuration Manager. If you specify 1, the process runs immediately.

    Note: When the process runs, all batches that are queued to run are imported. This can include your batch and other user’s batches.

    Invoking the Validation Process
    If you want to invoke validation, you can do so after the staging process is complete. You will know the process is complete when all batches for the version have a Status_ID of 2 in mdm.tblStgBatch.

    If you invoke validation before the staging process is complete, members that have not finished staging will not be validated.

    You can then invoke business rules to validating data by executing mdm.udpValidateModel:
     
    DECLARE @ModelName nVarchar(50) = 'Customer'
    DECLARE @Model_id int
    DECLARE @UserName nvarchar(50)= 'DOMAIN\user_name'
    DECLARE @User_ID int
    DECLARE @Version_ID int

    SET @User_ID =  (SELECT ID 
                     FROM  mdm.tblUser u
                     WHERE u.UserName = @UserName)

    SET @Model_ID = (SELECT Model_ID
                     FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                     WHERE Model_Name = @ModelName)

    SET @Version_ID = (SELECT MAX(ID)
                       FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                       WHERE Model_ID = @Model_ID)

    EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1

    Troubleshooting
    When you import data by using the staging process, you might encounter any of the following issues.

    The model is not available in the Model list
    In Master Data Manager, in the Integration Management functional area, on the Import page, in the Unbatched Staging Records pane, the model may not be available in the Model list.

    You must be a model administrator to import data for a model. A model administrator is a user with Update permission assigned to the top object in the model, and no other permissions assigned.

    The version is not available in the Version list
    On the Import page, in the Unbatched Staging Records pane, the version may not be available in the Version list.

    Model versions with a status of Committed are not available. Ensure that you are importing into an Open or Locked version.

    The number of records is not correct
    On the Import page, in the Unbatched Staging Records pane, the number of records available for import may not match the number of records you expected to retrieve from the staging tables in the Master Data Services database.

    • In the staging tables, ensure that the UserName field for each record contains your user name in the format Domain\user_name, computer\user_name, or that the field is NULL.
    • In the staging tables, ensure that the value in the Batch_ID field for each record is NULL and that the Status_ID field is 0.
    • In the staging tables, ensure that the value in the ModelName field matches the name of the model exactly. The name should be the same case as the model name in Master Data Manager.

    The batch doesn't process
    The batch may be queued to run but it does not process. In Master Data Manager, on the Import page, in the Unbatched Staging Records pane, the status remains Queued to Run.

    Ensure that Service Broker is enabled. For more information, see How to: Activate Service Broker Message Delivery in Databases (Transact-SQL).

    The batch is taking a long time to finish

    If the batch takes longer than usual to complete, the statistics on the Master Data Services database may need to be updated. For more information, see:

    Legal Notice 
    © 2010 Microsoft Corporation.  All rights reserved.  This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only.  Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

  • Importing Data by Using the Staging Process

    (this post was contributed by Suzanne Selhorn, Technical Writer on the MDS Team)

    This article is applicable to Microsoft SQL Server 2008 R2 Master Data Services feature.

    To populate the Master Data Services database with data from your organization's disparate data sources, you can import data into staging tables and then process the staged data as a batch. This post provides some basic information about the staging process. It introduces you to the database tables used for staging and the workflow required to import data.

    For staging examples and tips for troubleshooting, see my post: Staging Examples and Troubleshooting.

    Update Notice for SQL Server 2012 users: This blog article is applicable to SQL Server 2008 R2 Master Data Services. MDS Staging was vastly improved in SQL Server 2012 MDS release and is called Entity Based Staging. Please watch this video intro and refer to product documentation on the new 2012 MDS staging concepts.


    Staging Tables

    The Master Data Services database includes three staging tables that you can populate with data and one batch table that tracks each batch of staged data.

    mdm.tblStagingMember -
    Use to create new leaf members, consolidated members, and collections.
    mdm.tblStgMemberAttribute - Use to update the attributes of existing members and collections.
    mdm.tblStgRelationship - Use to add members to a collection or move members in an explicit hierarchy.
    mdm.tblStgBatch - Displays the status of each batch of staged data.

    Note: You must have INSERT permission to the staging tables in order to populate them with data.

    Staging Process Workflow

    During each step of the staging process, fields in the staging and batch tables are updated. The following workflow explains how and when the most important fields in each table are updated.
     

    Step 1: Populate the staging tables
    When you initially populate the three staging tables (tblStgMember, tblStgMemberAttribute, and tblStgRelationship), the Batch_ID for each record should be NULL. The Status_ID field for each member will default to 0, which indicates that record is ready to be processed.

    The batch table (tblStgBatch) does not yet have a record for the batch.

    staging step 1

    For examples of how to populate the staging tables, see my post: Staging Examples and Troubleshooting.

    After you have imported data into the staging tables, you can open the Master Data Manager user interface (UI) to confirm the number of records available to process. In the Integration Management functional area in the Unbatched Staging Records pane, choose a model. The number of records available to stage is displayed. This is the total number of staging records that contain:

    • The model in the ModelName field.
    • The logged-in user's name or no user name in the UserName field.

    Step 2: Invoke the staging process
    To invoke the staging process in the UI, select the version to import data into and click Process unbatched data. Or, for an example of how to use SQL scripting to invoke the staging process, see my post: Staging Examples and Troubleshooting.

    When the staging process starts, a row is added to the batch table. An ID is assigned in the ID field and the Status_ID field is updated to 1 to indicate that the batch is queued for processing.

    Each row in the staging tables is updated with the batch ID from the batch table. The Status_ID remains 0 to indicate that processing has not started.

    staging step 2

    Note: Staging batches that are queued to run are processed in sequence, and processing begins at an interval determined by a setting in Master Data Services Configuration Manager. The staging tables are processed one after another. tblStgMember is processed first, followed by tblStgMemberAttribute, and then tblStgRelationship. You cannot initiate the processing of one table at a time.
     

    Step 3: Batch completes
    As each row in the staging table is imported into the appropriate Master Data Services database tables, the Status_ID field for the row is updated with 1 or 2 to indicate success or failure, respectively. Each row’s ErrorCode field is also updated. These error codes are displayed along with descriptions on the Staging Batch Errors page in Master Data Manager.

    When all members in a batch have been processed, the batch table Status_ID field is updated to 2 and the LastRunEndDTM field displays the date and time that the process completed. 
    staging step 3
    Records that were successfully loaded are now available in the Explorer functional area of Master Data Manager.

    Note: ERR210000 is not an error and appears on successfully loaded records.

    The following illustration shows a summary of the workflow. 
    staging workflow

    Note: Members remain in the staging tables until you clear them. To clear the records, in Master Data Manager, click Integration Management, select the batch to delete and click Clear selected batch. The batch will be cleared when the staging process runs. 

    Step 4 (Optional): Invoke the Validation Process

    In Master Data Manager, you can validate the version in the Version Management functional area by clicking Validate Version on the menu bar. Choose your model and version and click Validate version.

    For an example of how to use SQL scripting to invoke the validation process, see my post: Staging Examples and Troubleshooting.

    Legal Notice 
    © 2010 Microsoft Corporation.  All rights reserved.  This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only.  Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

  • Configuring a MDS load-balanced web farm using IIS v7 ARR

    (this post was contributed by Nick Nieslanik, Senior Software Engineer on the MDS team).

    Many production environments will want to configure Master Data Services (MDS) in a load-balanced Web Farm for scalability and performance reasons. This blog entry will walk through configuring MDS to run with Internet Information Services v7.x Application Request Routing (ARR), a software load-balancer.

    Preparation

    To configure an MDS web farm, you'll need at least three IIS Web Servers available. Assign one of these servers the task of being the IIS ARR server. The remaining servers will serve as MDS web application servers. Please note that you’ll want a centrally located MDS database that all MDS application servers are configured to use. Once the servers are allocated, please follow the steps indicated below.

    Farmhardware

    Configuration Steps

    Step 1

    Install ARR v2 on the allocated ARR server by following steps 1 through 3 located here. Reboot the server after finishing the installation steps in the link provided.

    Step 2

    Install and configure Master Data Services on the allocated application servers. Detailed setup and configuration settings are located here.

    Step 3

    Create an MDS server farm on the ARR server. Detailed steps for configuring an ARR server can be found here. Please ignore the Health Test section in step 2 of the ARR configuration page as we’ll cover that below.

    Step 4

    Once the server farm is created, the Server Affinity setting must be changed to allow Master Data Services to work correctly. To do this, perform the actions below.

    • Select the server farm created earlier in Step 3 and double click the Server Affinity feature.

    IIsFarmConfig

    • Once in the Server Affinity panel, check the "Client affinity" checkbox if it is not already checked and choose a unique cookie name for the server farm.

    IIsFarmServerAffinity

    • Click Apply in the upper right hand corner.

    Step 5

    Perform a server farm health test from the ARR Server. To do this, click the MDS server farm created in Step 3 and select Health Tests from the Features view and follow steps 1 & 2 here. This link will also elaborate on the Client Affinity setting configured in Step 4. When creating the healthCheck.txt test file, please place it into the default web site root rather than the MDS web application folder.

    Limitations

    ASP.NET SQL Session State is not currently supported. Thus Client Affinity must be set as described in Step 4 for MDS to work correctly in a Farm scenario.

    Downloads

    ARR v2 x86: http://download.microsoft.com/download/4/D/F/4DFDA851-515F-474E-BA7A-5802B3C95101/ARRv2_setup_x86.EXE

    ARR v2 x64: http://download.microsoft.com/download/3/4/1/3415F3F9-5698-44FE-A072-D4AF09728390/ARRv2_setup_x64.EXE

    Links

    ARRv2 RTW Announcement: http://forums.iis.net/t/1162464.aspx

    ARRv2 Installation: http://learn.iis.net/page.aspx/482/install-application-request-routing/

    ARRv2 Configuration: http://learn.iis.net/page.aspx/485/define-and-configure-an-application-request-routing-server-farm/

    ARRv2 Load Balancing: http://learn.iis.net/page.aspx/486/http-load-balancing-using-application-request-routing/

    MDS Installation and Configuration: http://sqlblog.com/blogs/mds_team/archive/2009/12/10/installing-and-configuring-master-data-services-2008-r2-november-ctp.aspx

  • Creating a Simple Business Rule

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

    This post is a first in a series that will explore what you can do with SQL Server 2008 R2 Master Data Services (MDS) business rules. It will introduce business rule creation and publishing. Here are some of the topics we will be covering in the future:

    • Using business rules to enable human workflow via email notifications
    • Creating and initiating a SharePoint workflow from MDS business rules
    • Using different types of conditions and actions
    • Using complex conditions
    • Using business rules to propagate attribute changes down a hierarchy
    • Getting creative with business rules.
    • What is happening under the covers when you publish and run business rules

    If there are other business rule topics you are interested in please drop us a line at mdsblog@microsoft.com.

    What are Business Rules?

    The ability to validate master data against a variety of rules and conditions is a key component to any best-practice master data management strategy. In MDS, business rules are used to enforce data integrity and help ensure completeness and accuracy of the master data.

    Business rules are If/Then statements that can be created in the Master Data Manager user interface (UI) or the WCF API. If an attribute value meets a specified condition, then an action is taken. Possible actions include setting a default value, changing a value, performing attribute validation, or initiating a SharePoint workflow.

    To use business rules, you must first create and publish your rules, then validate your data against the published rules. An administrator can validate an entire version of a model at one time. Other users can validate subsets of data against business rules.

    If an attribute value doesn’t pass business rule validation, the value can still be saved. Validation issues are displayed in the UI, and a version cannot be committed until all attributes pass business rule validation.

    Business Rule Designer

    In Master Data Manager, the Business Rule Designer is where you create and edit business rules. Drag and drop plays a key role in creation and editing of business rules, as shown below.

    Business Rule Designer

    The screen has five main sections.

    Section Description

    Components

    Contains the logical operator, condition, and action components.

    Entity-Specific Attributes

    Contains all the attributes for the specific entity/member type as well as any explicit hierarchy (consolidated) attributes.

    IF

    Contains the conditions for which the action clause of the rule should be performed. You drag logical operators and conditions to this section. You can use the logical operators “AND” and “OR” to create combinations of conditions. All rules must have at least one logical operator. If no logical operator exists when you drag a condition component, an “AND” logical operator will automatically be added.

    A condition is a Boolean (true or false) expression that is applied to an entity attribute. If the condition is true, the actions for the rule are performed.

    A business rule does not require a condition. If no condition is specified, the actions for the rule are applied to all members.

    THEN

    Contains the actions that should be performed when the IF conditions are satisfied. You drag actions to this section.

    An action is the consequence of a condition evaluation. If a specific condition is true, the action is initiated. You can also create a rule that initiates an action without a condition. In this case, the rule is applied to all members.

    Edit

    When you select a condition component in the IF section or an action component in the THEN section, the Edit section will display the individual condition or action component for editing. You drag attributes to this section as well as select and enter values.

    Simple Rule Example

    The following steps will show you how to create a simple validation business rule using the sample Product model. Let’s say your business manager has indicated that any product manufactured in-house must have a fabrication time of between 1 and 10 days. So, in MDS we will create this rule:

    IF

        InHouseManufacture is equal to Y

    THEN

        DaysToManufacture must be between 1 and 10

    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. Click Edit selected business rule to open the Business Rule Designer.
    9. In the Components pane, expand the Conditions node.
    10. Click the “is equal to” condition and drag it to the IF pane's Conditions label. Notice that an “AND” logical operator is automatically added for you. If you need to start out with an “OR” then make sure you drag it over first.
    11. In the Entity-Specific Attributes pane, click the “InHouseManufacture” attribute and drag it to the Edit Condition pane's Select attribute label.
    12. In the Edit Condition pane, enter Y in the Attribute value field.
    13. In the Edit Condition pane, click Save. The condition will be displayed.
    14. In the Components pane, expand the Actions node.
    15. Click the “must be between” condition and drag it to the THEN pane's Action label.
    16. In the Entity-Specific Attributes pane, click the “DaysToManufacture” attribute and drag it to the Edit Action pane's Select attribute label.
    17. In the Edit Action pane, enter 1 in the “must be between” Attribute value field and 10 in the “and” Attribute value (as shown above).
    18. In the Edit Action pane, click Save. The action will be displayed.
    19. Click Back to return to the Business Rule Maintenance page.
    20. Optionally, on the Business Rules Maintenance page, for the row that contains your business rule, double-click a cell in the Name and Description columns to update the value.
    21. Click Publish Business Rules
    22. On the confirmation dialog box, click OK. The rule’s status will change to Active.

    Try it out

    Now that the rule is published you are ready to try it out.

    1. Go back to the home page.
    2. Select the Product model and a version.
    3. Select Explorer.
    4. On the Explorer page click the Edit button.
    5. Select Edit Member from the popup menu to edit a member that has InhouseManufacture = Y.
    6. Ensure the Supply Chain tab is active.
    7. In the Attributes section click the Edit button.
    8. Change the “DaysToManufacture” to 11.
    9. Click Save.

    Because the updated value is outside the allowable DaysToManufacture range, a validation issue is created and displayed at the bottom of the screen showing the value violates the business rule we entered and published.

    image

    This was a simple example to get you started but you can see where we could add several more rules with more complex conditions to ensure DaysToManufacture is valid for a wide variety of product lines.

    In the next Business Rules post, we will look at more advanced business rule capabilities by using business rules to enable human workflow via email notifications.

  • Creating Entities using the MDS WCF API

    (this post was contributed by Brent McBride, Senior Software Engineer on the MDS team).

    The below example shows how to create a new model and add new entities in the same call to the MetadataCreate API operation:

     

                Example 1: Add a model and its entities in the same operation.

     

                // Add a new model.

                string modelName = "My New Model Name";

                Metadata metaData = new Metadata();

                metaData.Models = new List<Model> {

                                    new Model() {

                                        Identifier = new Identifier() { Name = modelName } } };

     

                // Add new entities to the new model.

                foreach (string entityName in entityNames)

                {

                    Entity entity = new Entity();

                    entity.Identifier = new ModelContextIdentifier();

                    entity.Identifier.Name = entityName;

                     

                    metaData.Models[0].Entities.Add(entity);

                }

     

                OperationResult operationResult = new OperationResult();

                client.MetadataCreate(international, metaData, true, out operationResult);

    The Metadata DataContract contains several properties that are lists of metadata objects, such as Models, Entities, Versions, Attributes, etc. However, in any single call to a metadata CRUD operation (including MetadataCreate), only one of these properties may be non-empty. Otherwise, the operation returns error 200000 “The operation permits only one metadata property list.” Thus, Example 2 is invalid:

               

                Example 2: Incorrectly add model and entities.

     

                // Add a new model.

                string modelName = "My New Model Name";

                Metadata metaData = new Metadata();

                metaData.Models = new List<Model> {

                                    new Model() {

                                        Identifier = new Identifier() { Name = modelName } } };

     

                // Add new entities to the new model.

                metaData.Entities = new List<Entity>();

                foreach (string entityName in entityNames)

                {

                    Entity entity = new Entity();

                    entity.Identifier = new ModelContextIdentifier();

                    entity.Identifier.Name = entityName;

     

                    metaData.Entities.Add(entity); // Error! Both Models and Entities cannot be populated.

                }

     

                OperationResult operationResult = new OperationResult();

                client.MetadataCreate(international, metaData, true, out operationResult); 

                // Result: Error 200000

    The new entities must be added directly underneath the new model to which they belong, as per Example 1. However, the Metadata.Entities property comes in handy when adding entities to an already existing model, as Example 3 shows:

               

                Example 3: Add entities to a preexisting model.

     

                // Reference an existing model.

                string modelName = "My Existing Model Name";

                Metadata metaData = new Metadata();

     

                // Add new entities to the existing model.

                metaData.Entities = new List<Entity>();

                foreach (string entityName in entityNames)

                {

                    Entity entity = new Entity();

                    entity.Identifier = new ModelContextIdentifier();

                    entity.Identifier.Name = entityName;

                    entity.Identifier.ModelId = new Identifier { Name = modelName };

     

                    metaData.Entities.Add(entity); // No error this time because the Models property is empty.

                }

     

                OperationResult operationResult = new OperationResult();

                client.MetadataCreate(international, metaData, true, out operationResult); 

    As shown in the highlighted line of Example 3, each entity must reference the model to which it pertains. Without this context, the operation returns error 120003 “The user does not have permission or the object ID not valid”. This error message is intentionally ambiguous for the sake of security. Using the same error code for both insufficient permissions and invalid IDs makes it harder for an attacker to glean information about the attack surface. Although Example 3 only deals with creating entities, the same principle could also apply to other API operations and metadata objects such as attributes. Error 120003 could occur if there is not enough contextual information provided for the operation to uniquely identify a referenced metadata object.

     

    © 2010 Microsoft Corporation.  All rights reserved.  This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only.  Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

  • Getting Started with the Web Services API in SQL Server 2008 R2 Master Data Services

    (this post was contributed by Val Lovicz, Principal Program Manager on the MDS Team)

    With the November CTP release available, many have been asking for basic information and examples for programming the Web services API. While MDS conceptual content and API reference are available and updated periodically in SQL Server Books Online, we hope this brief post will help get you started and give you a sense of how to use the Web services API.

    This post provides instructions and examples using Visual Studio 2008, .NET and C# and is intended for an audience with a basic understanding of these technologies.

    The following are general references on MSDN for working with Web services if you would like more background information.

    Enable Web Services in Configuration Manager

    Before we get started, make sure your installation of MDS has Web services enabled. The prior post Installing and Configuring Master Data Services provided detailed configuration instructions and included a step to check the Enable Web services for the Web application setting as shown below.

    clip_image002

    Expose the WSDL

    Exposing the WSDL is only necessary at the time you want to generate proxy classes using a client development tool such as Visual Studio. After a proxy has been generated, the WSDL does not need to be exposed going forward for client programs to call the API.

    Caution: Updating web.config will cause the MDS application domain in IIS to recycle.  Existing user sessions will lose cached information, and users may experience session errors or slow page loads.  Perform changes at off-peak times if possible and use non-production environments for development.

    To enable an http/https Get on the WSDL:

    1. Open the MDS web.config file in a text editor (<Program Files>\Microsoft SQL Server\Master Data Services\WebApplication\web.config).
    2. Look for the tag serviceMetadata and set httpGetEnabled to true (or httpsGetEnabled if using SSL).

    To also enable service exception details for additional debugging (not necessary for standard, trapped errors):

    1. Look for the tag serviceDebug and set includeExceptionDetailInFaults to true.

    <system.serviceModel>
         <behaviors>
              <serviceBehaviors>
                   <behavior name="mdsWsHttpBehavior">
                        <!-- Enable to allow clients to retrieve metadata (WSDL) about the service endpoints. -->
                        <!-- If not using SSL (httpGetEnabled="true" httpsGetEnabled="false") to expose service metadata.—> 
                        <!-- If SSL is being used (httpGetEnabled="false" httpsGetEnabled="true") to expose service metadata.-->
                        <serviceMetadata httpGetEnabled="true" httpsGetEnabled="false" />

                        <!-- Enable to allow clients to see service exception details -->
                        <serviceDebug includeExceptionDetailInFaults="true" />

                        <serviceThrottling maxConcurrentSessions="400"/>

                       <dataContractSerializer maxItemsInObjectGraph="999999999"/>
                   </behavior>
              </serviceBehaviors>
         </behaviors>

    </system.serviceModel>

    Note: these settings will apply to all Web application instances on this server. All instances are virtual directories that point to the same Web files and web.config.

    Create an Example Application in Visual Studio

    Create a simple Console Application using the following steps.

    1. In Visual Studio, select FileNewProject…
    2. In the New Project Dialog, select Visual C# - Windows under Project types
    3. Select Console Application under Templates
    4. Enter “HelloMDS” as the Name
    5. Click OK to create the project

    clip_image004

    You will now have an empty template program as shown.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    namespace HelloMDS
    {
         class Program
         {
              static void Main(string[] args)
              { 
              }
         }
    }

    Add a Service Reference

    Next, add a service reference to the project for consuming the MDS Web service.

    1. In the Solution Explorer window, right-click References
    2. Click Add Service Reference

    clip_image006

    You will see the Add Service Reference dialog. Now, configure the service reference.

    1. In Address, enter the URL to the MDS service which will be “http://<ServerName>/<MdsSiteName>/service/service.svc”.  If you do this on the computer where MDS is hosted, you can use “localhost” as the server name.
    2. Click Go. Visual Studio will attempt to contact the service and retrieve the WSDL.
    3. If successful, you will see Service and IService in the Services box. Click on IService to preview the list of operations as shown.
    4. Assign a namespace to the service in the Namespace box. In this example, I am using MDService.
    5. Click the Advanced button to configure advanced settings.

    clip_image008

    Advanced Settings

    Here you will change how the proxy classes are generated. This step is only necessary so that your code can be identical to my examples.

    1. Check Always generate message contracts
    2. Set the Collection type drop-down to System.Collections.ObjectModel.Collection
    3. Click OK to return to the Add Service Reference dialog
    4. Click OK. In the status bar you will see “Generating new service reference to…”. The proxy classes are being generated.
    5. When VS is ready, you will see the MDService service reference added to your project (shown in Solution Explorer).

    clip_image010

    Create a Simple Client Program

    Next, you can use the following example code to expand the template program. The program, as shown below, only contains the minimal steps to initialize a client proxy which you may then use to call MDS Web service operations. You will need to add additional statements to make this program perform useful work. You will need System Administrator access to MDS to successfully run the example application.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    // additional references...
    using HelloMDS.MDService; /* for the created service reference */
    using System.Collections.ObjectModel; /* supports collection objects used in the proxy */

    namespace HelloMDS
    {
         class Program
         {
              private static ServiceClient mdsProxy; /* service proxy object */

              static void Main(string[] args)
              {
                   // Create the service proxy
                   Console.WriteLine("Connecting...");
                   try
                   {
                        mdsProxy = CreateMdsProxy("http://localhost/MDS/service/Service.svc");
                        Console.WriteLine("Connected.");
                   }
                   catch (Exception ex)
                   {
                        Console.WriteLine("Error connecting: " + ex.Message);
                   }

                   // At this point we could start calling methods on mdsProxy to execute Web service operations.

              }

              // creates the service client proxy
              private static ServiceClient CreateMdsProxy(string mdsURL)
              {
                   // create an endpoint address using the URL
                   System.ServiceModel.EndpointAddress endptAddress = new System.ServiceModel.EndpointAddress(mdsURL);

                   // create and configure the WS Http binding
                   System.ServiceModel.WSHttpBinding wsBinding = new System.ServiceModel.WSHttpBinding();

                   // create and return the client proxy
                   return new ServiceClient (wsBinding, endptAddress);
              }
         }
    }

    Calling Web Service Operations via the Proxy Methods

    Now that a service client object mdsProxy is established in the code, you can begin calling its methods.

    For example:

    1. In the example code above, place the cursor after the line “//At this point we could start…”
    2. Type “mdsProxy.” and IntelliSense will display the available methods as shown below. Most of these methods correspond directly to MDS Web service operations.

    clip_image012

    The Basics of Service Operations / Proxy Methods

    MDS service operations follow the naming convention of NounVerb. For example, the operation to create new metadata objects such as models, entities and hierarchies is MetadataCreate. Each proxy method that represents a service operation will have a single request object parameter (e.g. MetadataCreateRequest) and will return a response object (e.g. MetadataCreateResponse). Therefore, a typical call of a proxy method, such as MetadataCreate, would include the following lines.

    // Create the request and response objects
    MetadataCreateRequest request = new MetadataCreateRequest();
    MetadataCreateResponse response = new MetadataCreateResponse();

    // Here, we need to populate the request in order to do something useful.

    // Make the service request
    response = mdsProxy.MetadataCreate(request);

    This example calls MetadataCreate without populating the request. You would need to populate the request with a new object for creation, such as a new model, to perform a successful and useful call.

    Completing the Example

    Next, you may use the following code example to complete the program. The program below has an added method CreateModel. This method accepts a new model name string parameter and populates the request with the single model name provided.

    To complete the example, add the call to CreateModel(“Hello World”); in Main as shown. This will create a new model in the MDS repository, named “Hello World”.

    You’ll also notice the addition of the HandleErrors method. Each response includes an OperationResult object which contains a collection of Error objects if any errors are encountered when processing the request. This method example prints the list of operation error messages if any are returned.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    // additional references...
    using HelloMDS.MDService; /* for the created service reference */
    using System.Collections.ObjectModel; /* supports collection objects used in the proxy */

    namespace HelloMDS
    {
         class Program
         {
              private static ServiceClient mdsProxy; /* service proxy object */

              static void Main(string[] args)
              {
                   // Create the service proxy
                   Console.WriteLine("Connecting...");
                   try
                   {
                        mdsProxy = CreateMdsProxy("http://localhost/MDS/service/Service.svc");
                        Console.WriteLine("Connected.");
                   }
                   catch (Exception ex)
                   {
                        Console.WriteLine("Error connecting: " + ex.Message);
                   }

                   CreateModel("Hello World");

              }

              public static void CreateModel(string newModelName)
              {
                   // Create the request and response objects
                   MetadataCreateRequest request = new MetadataCreateRequest();
                   MetadataCreateResponse response = new MetadataCreateResponse();

                   // Build the request with the new model
                   request.Metadata = new Metadata();
                   request.Metadata.Models = new Collection<Model>() { new Model() };
                   request.Metadata.Models[0].Identifier = new Identifier();
                   request.Metadata.Models[0].Identifier.Name = newModelName;

                   // Make the service request to Create.
                   response = mdsProxy.MetadataCreate(request);

                   HandleErrors(response.OperationResult);
              }

               // creates the service client proxy
              private static ServiceClient CreateMdsProxy(string mdsURL)
              {
                   // create an endpoint address using the URL
                   System.ServiceModel.EndpointAddress endptAddress = new System.ServiceModel.EndpointAddress(mdsURL);

                   // create and configure the WS Http binding
                   System.ServiceModel.WSHttpBinding wsBinding = new System.ServiceModel.WSHttpBinding();

                   // create and return the client proxy
                   return new ServiceClient (wsBinding, endptAddress);
              }

              // Handles the operations results
              private static void HandleErrors(OperationResult result)
              {
                   string errorMessage = string.Empty;
                   if (result.Errors.Count() != 0)
                   {
                        for (int i = 0; i <= result.Errors.Count() - 1; i++)
                        {
                             errorMessage += " OperationResult:Error: " + result.Errors[i].Code + ":"
                                  + result.Errors[i].Description + ":" + result.Errors[i].Context.Type.ToString();
                        }
                        Console.WriteLine("Error: " + errorMessage);
                   }
              }
         }
    }

    Try it Out

    If you have copied the example code so far, build (F6) and run (F5) your solution to test it. You will briefly see the console application appear and then complete. Verify the results by logging into the Master Data Manager Web application (MDM). You will see “Hello World” added to the list of models on the home page.

    Note: MDM caches metadata, so if you already had MDM open in your browser when the HelloMDS application was run, you will not see the newly created model. You would need to click Refresh at the bottom of the Home page or close your browser and reconnect.

    clip_image014

    More on Bindings

    As you expand the solution to get or receive larger, more complex messages, you may find the need to increase certain default binding settings. In the example below, the CreateMdsProxy method has been expanded to include additional settings on wsBinding. These settings are not recommended settings for all situations; they merely show how to increase these settings should you run into timeouts or message overflows. These same settings may also be configured in the app.config file within your project. For more information, refer to the MSDN article: Using Bindings to Configure Windows Communication Foundation Services and Clients.

              // creates the service client proxy
              private static ServiceClient CreateMdsProxy(string mdsURL)
              {
                   // create an endpoint address using the URL
                   System.ServiceModel.EndpointAddress endptAddress = new System.ServiceModel.EndpointAddress(mdsURL);

                   // create and configure the WS Http binding
                   System.ServiceModel.WSHttpBinding wsBinding = new System.ServiceModel.WSHttpBinding();
                   // binding settings such as the following may be set here or in app.config
                   wsBinding.CloseTimeout = TimeSpan.FromMinutes(30);
                   wsBinding.OpenTimeout = TimeSpan.FromMinutes(30);
                   wsBinding.SendTimeout = TimeSpan.FromMinutes(30);
                   wsBinding.ReceiveTimeout = TimeSpan.FromMinutes(30);
                   wsBinding.MaxReceivedMessageSize = 999999999;
                   wsBinding.MaxBufferPoolSize = 999999999;
                   XmlDictionaryReaderQuotas ReaderQuotas = new XmlDictionaryReaderQuotas();
                   ReaderQuotas.MaxArrayLength = 999999999;
                   ReaderQuotas.MaxBytesPerRead = 999999999;
                   ReaderQuotas.MaxDepth = 999999999;
                   ReaderQuotas.MaxNameTableCharCount = 999999999;
                   ReaderQuotas.MaxStringContentLength = 999999999;
                   wsBinding.ReaderQuotas = ReaderQuotas;
                   wsBinding.Security.Mode = System.ServiceModel.SecurityMode.Message;
                   wsBinding.Security.Message.ClientCredentialType = System.ServiceModel.MessageCredentialType.Windows;

                   // create and return the client proxy
                   return new ServiceClient(wsBinding, endptAddress);
              }

    Setting the properties of XMLDictionaryReaderQuotas requires the following additional reference line in the program.

    using System.Xml;

    Further Recommendations

    The preceding example was very basic. To create a more robust, object-oriented solution, I recommend creating an “MDSServiceWrapper” class that encapsulates mdsProxy and its service methods and exposes granular, purpose-built methods such as CreateModel, CreateEntity, etc. This approach would yield a reusable API wrapper that cleanly separates the body of your application from calls to the MDS API.

    API Quick Reference

    The following information is a brief reference, listing the operation stereotypes and links to further MSDN documentation. The Master Data Services – Technical Reference in SQL Server 2008 R2 Books Online currently provides the lists of operations and classes.

    Operation Stereotypes

    The following operation stereotypes are defined for consistency. Understanding the functional patterns implemented by each stereotype helps you understand how to use the operations properly, how to form valid requests and what to expect in responses.

    Note the term “ID” is used generically. This may be a GUID, member code or other unique ID as appropriate to the type of data.

    Get operations…

    • search for items by ID’s and/or names and return matched items in their response
    • may make use of “<objectType>SearchCriteria” parameters that act as a filter on the retrieved items
    • may make use of “<objectType>GetCriteria”, not SearchCriteria, when the criteria are explicit, exact matches
    • may also use “<objectType>ResultCriteria” parameters that define the information to be retrieved on the matched items

    If you were to think of the Criteria parameters in the form of a SQL-like expression, they would be represented as:

    SELECT ResultCriteria WHERE records match SearchCriteria

    Create operations…

    • create new items that do not exist
    • error when the item to be created already exists (matching context and name exists)
    • require names for new items in the request and assign new internal ID’s to the created items
    • ignore ID’s that are provided in the request data
    • may include a “ReturnCreatedItems” option to respond with the ID’s of newly created items

    Clone operations…

    • create items within an MDS instance as exact copies of items from another MDS instance, including the ID’s
    • require an ID along with a name for creation of the new item
    • assign both the supplied ID and name to the newly created item
    • update existing items that are matched by ID

    Update operations…

    • modify existing items and never create new items
    • error when the item to be modified is not found by its ID

    Delete operations…

    • remove or deactivate existing items by ID
    • error when the item to be deleted is not found by its ID
    • may be overloaded with an Undelete option for items such as master data that can be deactivated (soft-deleted)

    Merge operations…

    • automatically create new items or update existing items as appropriate
    • are only implemented for master data members

    Typical functions / operations for editing would include three distinct operations for creating, updating and deleting. This approach is sufficient but sometimes requires extra work by the user. When there is a mix of pre-existing and new items, the user may need to issue additional commands to determine what pre-exists to conditionally execute create or update requests to avoid errors. MDS provides “Merge” operations for master data so the user may avoid these extra steps that increase coding time and complexity.

    List of Operations

    Refer to the list of Service Methods on MSDN.

    Web Service Class Reference

    Visit the MDS Web Service Class Reference on MSDN to look up class descriptions for the message and data contracts of the services.

    Legal Notice

    © 2010 Microsoft Corporation.  All rights reserved.  This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only.  Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

  • Trace Logging in SQL Server 2008 R2 Master Data Services

    (this post was contributed by Val Lovicz, Principal Program Manager on the MDS Team) 

    Once you’ve installed Master Data Services (MDS), you may find the trace logging feature useful for error diagnostics and support requests as well as monitoring of application usage and performance.  This post covers the steps to turn on and configure trace logging.  Specifically, this post covers tracing to a text log file.  Other logging mechanisms are supported and will be covered in future posts and SQL Server Books Online.

    Background

    Master Data Services consists of an ASP.NET web application (Master Data Manager) and a WCF service, both hosted in IIS, plus a SQL Server 2008 R2 database.  Both Master Data Manager requests and external calls to the web services API end-point are handled by a common service layer.  The service layer can log each each operation request/response as well as important events and errors.

    The web.config file

    As an administrator with access to files on the IIS web server, you can enable logging by modifying the MDS web.config file.  This file is located in the web application folder: <program files dir>\Microsoft SQL Server\Master Data Services\WebApplication.

    Caution: Updating web.config will cause the MDS application domain in IIS to recycle.  Existing user sessions will lose cached information and users may experience session errors or slow page loads.  Perform changes at off-peak times if possible.

    The file snippet below shows the diagnostics section from the originally installed file. Note that switchValue is set to “Off”. Additionally, the example lines that follow are commented out. These lines are examples for adding trace listeners of various types.

    <system.diagnostics>
    <sources>
    <!-- Adjust the switch value to control the types of messages that should be logged. -->
    <source name="MDS" switchType="System.Diagnostics.SourceSwitch" switchValue="Off">
    <listeners>
    <!-- Enable and configure listeners as desired to obtain trace messages. -->
    <!-- <add name="LogFileListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="MdsTrace.log" traceOutputOptions="DateTime" /> -->
    <!-- <add name="EtwListener" type="System.Diagnostics.Eventing.EventProviderTraceListener, System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"
    initializeData="{F2A341B8-CA5F-49ad-B00C-A82D3FCF948B}"/> -->
    <!-- <remove name="Default"/> -->
    </listeners>
    </source>
    </sources>
    <trace autoflush="true" />
    </system.diagnostics>

    Turning on log file tracing

    To enable logging, change switchValue to “All” or another valid value as described below in Table 2. To enable the output to a log file, uncomment the LogFileListener line as shown in the file snippet below.

    <system.diagnostics>
    <sources>
    <!-- Adjust the switch value to control the types of messages that should be logged. -->
    <source name="MDS" switchType="System.Diagnostics.SourceSwitch" switchValue="All">
    <listeners>
    <!-- Enable and configure listeners as desired to obtain trace messages. -->
    <add name="LogFileListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="MdsTrace.log" traceOutputOptions="DateTime" />
    <!-- <add name="EtwListener" type="System.Diagnostics.Eventing.EventProviderTraceListener, System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"
    initializeData="{F2A341B8-CA5F-49ad-B00C-A82D3FCF948B}"/> -->
    <!-- <remove name="Default"/> -->
    </listeners>
    </source>
    </sources>
    <trace autoflush="true" />
    </system.diagnostics>

    File name and path

    The initializeData value is the name of the log file. This may be modified to another name or to include a desired path. If the path is not specified then the file will default to the web application directory path (where web.config resides).

    Important Note: The service account for the MDS application pool must have write access to the log file location.

    Logging level of detail

    Table 1 below describes the categories of trace events that may be logged, sorted with the most important / critical events at the top.  Table 2 below lists the valid settings for the logging switchValue; this setting may be adjusted to produce the right amount of logging details to suit the situation.

    Table 1 - MDS logging event types

    Event Type

    Description

    Critical

    a fatal error or application crash

    Error

    a recoverable error

    Warning

    a noncritical problem

    Information

    an informational message

    Verbose

    a debugging trace message

    Start

    starting of a logical operation

    Stop

    stopping of a logical operation

    Table 2 - SwitchValue settings for logging

    Setting

    What is logged:

    Off

    nothing

    Error

    errors only

    Warning

    errors and warnings

    Information

    errors, warnings, informational messages

    Verbose

    “Information” plus additional debugging trace information including API requests and responses in XML format

    ActivityTracing

    start and stop events only

    All

    “Verbose” plus “ActivityTracing”

    Log setting recommendations

    • For normal operation, use the “Off” setting to avoid logging altogether or use the “Error” or “Warning” settings which will keep the log small while alerting administrators to problems.
    • Use the “All” setting for support / troubleshooting situations. 
    • Use “ActivityTracing” for performance measurement or usage monitoring.
    • Use “Information” only if prepared to periodically check and clean logs. The logs could get lengthy with this setting. This setting is helpful for tracking usage and usage patterns.
    • Do not use “Verbose” or “All” under normal operating conditions as the volume of data logged will negatively affect performance.

    Creating a log for product support – step by step

    In situations where an unexpected error occurs and further diagnostics are required, it is helpful to create a log file that traces the events leading up to the problem along with the service requests and responses.

    To produce a concise and helpful log file, perform the following steps.

    Caution: Updating web.config will cause the MDS application domain in IIS to recycle.

    1. If possible, stop the MDS application pool in IIS Manager (warning: do this only if the application can be taken off line)

    2. If possible, move or delete the current log file (if one already exists).

    3. Open the web.config file with a text editor (<program files>\Microsoft SQL Server\Master Data Services\WebApplication\web.config).

    4. Find the system.diagnostics section.

    5. Change the switchValue to All or ActivityTracing and uncomment the log file line as shown in Sample 3.

    a. Use the “All” switchValue for error diagnostics.

    b. Use the “ActivityTracing” switchValue for performance diagnostics.

    c. The MdsTrace.log file name can be prefixed with a path if desired.

    6. If the application pool was previously stopped:

    a. Then: start the MDS application pool in IIS

    b. Else: wait for the log file to appear after some time and web application activity. The web application will periodically reload configuration settings from the file (should be within minutes).

    7. Reproduce the problem - perform the actions / requests that led to the error.

    8. If able to stop application pool:

    a. Then:

    i. stop the application pool

    ii. retrieve the log file (may need to wait for processes to finish; there could be a delay after stopping the app pool)

    b. Else:

    i. Open the log file with an editor that doesn’t lock the file and copy the relevant tracing messages.

    9. Open the web.config file with a text editor and change the switchValue back to Off or the prior value.

    10. Start the application pool if stopped.

    Error Handling

    All service operations return an array or collection of errors within the OperationResult object of a response message. When an error occurs, the error array is also serialized to XML and written to the web application log file for certain switchValue settings as described above.

    Example of an API response error that has been written to the log file:

    MDS Error: 0 : <ArrayOfError xmlns="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Error>
    <Code>110003</Code>
    <Context>
    <FullyQualifiedName>Model1</FullyQualifiedName>
    <Identifier>
    <Id>00000000-0000-0000-0000-000000000000</Id>
    <Name>Model1</Name>
    <InternalId>0</InternalId>
    </Identifier>
    <Type>Model</Type>
    </Context>
    <Description>The name already exists. Type a different name.</Description>
    </Error>
    </ArrayOfError>
    DateTime=2009-12-10T20:48:05.6949548Z

    Error object contents

    Table 3 - As shown in the example above, each error includes the following data properties.

    Property

    Description

    Code

    The unique error number identifying the type of error

    Description

    Localized error message text

    Context.FullyQualifiedName

    The fully qualified name of the object involved in the error

    Some names are only unique within their context. An entity would be qualified with a Model name prefix such as ModelName : EntityName.

    Context.Type

    The type of object involved in the error

    Context.Identifier

    The identifier of the object involved in the error

    Context.Identifier.Id

    The unique GUID of the object, if specified or available

    Context.Identifier.Name

    The name of the object, if specified or available

    Context.Identifier.InternalId

    Deprecated – do not use

  • Welcome to the SQL Server Master Data Services Team Blog!

    We are hard at work putting the finishing touches on our first release of SQL Server Master Data Services (MDS). We will ship as part of SQL Server 2008 R2, and, already we are getting valuable feedback about our November CTP. Thanks to those of you who have downloaded MDS and provided feedback via our forums.
     

    Since MDS is new, we know there will be lots of interest in learning more about the technology. In addition to SQL Server Books Online and our forums, we will be posting more focused and in-depth articles here on our team blog. Your feedback is always welcome and we’d appreciate hearing from you about topics of interest for future posts.
     

    You can reach MDS team bloggers using our blog email address: mdsblog@microsoft.com.
     

    Thanks!
    The MDS Team

  • Installing and Configuring Master Data Services 2008 R2 November CTP

    (this post was contributed by Matt Anderson, Senior Software Engineer on the MDS team)
     

    Master Data Services (MDS) is now available as part of the November CTP of Microsoft® SQL Server® 2008 R2! An overview of MDS can be found on the product home page and documentation is available in SQL Server Books Online.
     

    This post covers the basic steps for installing and configuring MDS as well as some behind-the-scenes information about what is happening and why. Please let us know your thoughts about the install and configuration experience and any questions you have.
     

    Background

    Master Data Services consists of an ASP.NET web application (Master Data Manager), a WCF service hosted in IIS, and a SQL Server 2008 R2 database. MDS web and database components can be installed on a single server or on multiple servers. MasterDataServices.msi installs the pages, database scripts, samples, and assemblies for MDS, and a separate utility called Configuration Manager is used to create MDS web applications and databases.
     

    Installation
     

    1. Install one of the supported Windows operating systems for MDS.
       
      NOTE: MDS is supported on x64 operating systems only.
       
    2. Install the Web Server (IIS) role on the machine that will host the MDS web site. Ensure that both the ASP.NET and Windows Authentication role services are installed as well. For a list of the specific role services and features, see Web Application Considerations (Master Data Services) in SQL Server Books Online. 
       
      image
      The MDS Web application and WCF service require Windows Authentication. Active Directory users and groups can be assigned permissions in MDS to control access to features and data. Local users and groups can also be used in a single machine (i.e., development or test) environment.
       
    3. Locate and run the MasterDataServices.msi. The location for the msi varies based on how you downloaded MDS:
       
      • DVD Image: <drive>:\MasterDataServices\x64\1033_ENU\MasterDataServices.msi
         
      • Self-extracting executable: <extract location>\1033_enu_lp\x64\setup\masterdataservices.msi
         
      • MasterDataServices.msi download: Wherever you saved the msi during download 
         
         image
    4. Follow the steps in the wizard to install the MDS pages, database scripts, samples, and assemblies. No web applications or databases are created during the installation. Those tasks are done using Configuration Manager, which is started automatically at the end of the wizard.

      After the install, you should find that the MDS pages, scripts, and assemblies are in the target location selected during the install wizard. 
       

      image


      The MDS assemblies and resource should also be present in the Global Assembly Cache:

      image 

    Configuration
     

    1. The MDS Configuration Manager is started automatically at the end of the installation. If you want to start it manually, click the Start menu link: Start –> All Programs –> Microsoft SQL Server 2008 R2 November CTP –> Master Data Services –> Configuration Manager

      image 
    2. Review the status information shown in the “Server Configuration” panel to ensure that the required components are found on the machine.
       
    3. Click “Databases” in the left pane and then click the “Create Database” button to start the Create Database wizard. Follow the steps in the wizard and supply the required information to create a new MDS database. For more information about the requirements for a computer that hosts the Master Data Services database and the options in the wizard, see Database Considerations (Master Data Services) and Create Database Wizard (Master Data Services) in SQL Server Books Online. 

      NOTE: The MDS web application uses Windows Authentication to connect to the MDS database. The “Service Account” step in the wizard allows you to specify the Windows user that should be used for this connection. The service account will be granted the correct permissions in the newly created MDS database and should also be configured as the identity for the MDS Application Pool in IIS when the MDS web application is created (see step 5.2 below). The service account should be a low privilege domain account that can connect to the database server hosting the MDS database.

      NOTE: The “Administrator Account” is the user that should have full access to all MDS functions and data. During database creation the specified account will be granted these permissions.

      Database creation can take several minutes as the required objects are created and system data is inserted. 
       
    4. After the database is created, various system settings can be adjusted. For more information about system settings, see Database Page (Master Data Services) in SQL Server Books Online.

      image
    5. Click “Web Configuration” in the left pane to configure a new IIS web application for MDS. The MDS web application can be created as its own web site (i.e., root application) or within another web site. For more information about the options on the Web Configuration page, see Web Configuration Page (Master Data Services) in SQL Server Books Online.
       
      The simplest configuration is to create the MDS web application within the existing Default Web Site, which is described below:
       
      1. Select “Default Web Site” in the Web Site drop down list.
         
      2. Click the Create Application… button to create a new MDS web application. Accept the defaults for web application Alias and Application Pool Name unless you have reason to change them. Type the User name and Password for the identity of the MDS application pool. This should be the same account as provided in the Service Account step of the Create Database wizard (see step 3 above). Click the OK button to create the new web application.

         image
      3. Within the Database section of the Web Configuration page, click the Select… button. Then, in the Connect to Database dialog, connect to the database server hosting the MDS database and select the MDS database. Click the OK button to select the database for the MDS web application.

        image
      4. Within the Web Services section of the Web Configuration page, check the “Enable Web services for this Web application.” checkbox if you would like to expose the MDS WCF application programming interface for this web application.

        NOTE: If you plan to create custom solutions using this API, you should enable the web services. Otherwise, leave them disabled to reduce the surface area of the MDS instance. The Master Data Manager web UI does not require the services to be enabled.
         
      5. Click the Apply button to save the Web Application settings. In the Configuration Complete dialog, optionally check the Launch Web application in browser window to open Master Data Manager for the web application and then click OK.

        The Web Configuration should look similar to that shown below:

         image 
         

    If you chose to launch the web application, you will be taken to the MDS getting started page. Click the Open the Master Data Manager home page link to open the Master Data Manager.


                                             Master Data Manager Getting Started Page

    image

      

                                               Master Data Manager Home Page

    image

    Although not shown above, multiple MDS web applications and databases can be created using Configuration Manager. This can be useful when you want to configure development, test, and staging environments. You can also use Configuration Manager to change the database that should be used for an MDS web application.

    NOTE: Configuration Manager can create databases on remote database servers, but it can only create web applications on the server where it is running. As a result, you will need to run Configuration Manager separately on each server that is hosting one or more MDS web applications.


    Next Steps 
    MDS installation and configuration are complete. Some suggested next steps are listed below.  
     

    1. If this is a production MDS instance, setup a database maintenance plan and backup schedule. The maintenance plan should include updating statistics on the database.
       
    2. If this is a production MDS instance and/or it contains production data, configure the MDS web application to use TLS/SSL to help protect the master data as it is transferred across your network.
       
    3. Create and configure the mail profile for the MDS instance if you want to enable email notifications.
       
    4. Deploy sample package(s) into the MDS instance.
More Posts « Previous page
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement