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.

  • Recorded MDS Presentations Available at Tech-Ed Online

    We had great interest for our MDS presentations at the 2010 Tech-Ed / BI North America Conferences.  Thanks to all who attended.  If you didn’t get a chance to attend the MDS sessions, you can view the recorded presentations at Tech-Ed Online.

     

    Introducing Microsoft SQL Server 2008 R2 Master Data Services [BIE203]

    This session introduces Master Data Services (MDS) and describes typical solutions. Product demonstrations illustrate how Master Data Services can help synchronize operational systems and build better dimensions in analytic systems. The session includes an overview of MDS features including product demonstrations and shows how to get started implementing MDS.

     

    Microsoft SQL Server Analysis Services Dimension Management with Master Data Services (MDS) [BIE02-INT]

    This session discusses the problems of dimension and hierarchy management for data warehouses and Analysis Services and describes a solution to the data management problem using Microsoft SQL Server 2008 R2 Master Data Services (MDS). MDS provides ownership of the dimension data to the right data stewards in the business while giving IT process control, security, and auditability.

  • Master Data Services first cumulative update is now available!

    We have assembled a new release that addresses a number (30+) of issues that we could not address before the SQL Server 2008 R2 RTM release.  Everyone who is seriously considering Master Data Services or even kicking the tires is encouraged to take this update.  Anyone actively using the product is encouraged to take this release as well.

    Here is the location of the KB Article:

    http://support.microsoft.com/kb/2143880

    Note: This is a full installation msi.  You can install this MSI on a new server or an existing server that runs the RTM version of Master Data Services. If you apply this to an existing server, you will need to update your MDS database by using Master Data Services Configuration Manager after you install the MSI. Open Configuration Manager, and click Upgrade Database to do this.

    Master Data Services is a feature of the versions and editions of SQL Server listed in Setup Requirements (Master Data Services). Any computer where you install Master Data Services must be licensed accordingly. For more information, refer to the End User License Agreement (EULA).

  • New Samples on MSDN Code Gallery

    (This post was contributed by John Burrows, Lead Program Manager for the MDS Team, May 2010)

     

    ***THIS POST APPLIES TO SQL SERVER 2008 R2. FOR SQL SERVER 2012 SAMPLES, SEE http://sqlserversamples.codeplex.com/wikipage?title=SQL%20Server%202012%20Master%20Data%20Services***

     

    A couple of new samples have been posted to the MSDN Code Gallery; two sample models that illustrate recursive and explicit cap hierarchies and a Visual Studio solution that contains an example of calling the Model Deployment API via code.

    Sample Models

    Employees

    The Employee sample model contains the employees of a fictitious Winery “Coho Winery” that has a legal structure in the form of three subsidiaries and an operating structure in the form of four operating groups.

    You can download the model from MSDN Code Gallery here:

    http://code.msdn.microsoft.com/SSMDSEmployeeSample

    Once you unzip the .pkg file you can deploy it to your MDS system using the Model Deployment Wizard. For more information about using the Model Deployment Wizard, see “Deploying Models (Master Data Services)” (http://msdn.microsoft.com/en-us/library/ff486956(SQL.105).aspx) in SQL Server Books Online.

    Recursive derived hierarchies are illustrated in the model in three hierarchies:

    1. “Reports to” hierarchy. This hierarchy is a simple recursive hierarchy built on the Employee entity and Manager domain-based attribute.

    2. “Operating groups” hierarchy. This hierarchy is also a recursive hierarchy that adds the Group domain-based attribute on top of the recursive Employee-Manager relationship.

    3. “Subsidiaries” hierarchy. Again, here is another recursive hierarchy that adds the Subsidiary domain-based attribute on top of the Group domain-based attribute on top of the recursive Employee-Manager relationship.

    For more information about recursive hierarchies, see “Recursive Hierarchies (Master Data Services)” (http://msdn.microsoft.com/en-us/library/ff487006(SQL.105).aspx) in SQL Server Books Online.

    Accounts

    The Accounts sample model contains the employees of a fictitious multi-national company, Contoso Pharmaceuticals, which has general ledger systems located in various locations across the globe.

    You can download the model from MSDN Code Gallery here:

    http://code.msdn.microsoft.com/SSMDSAccountSample

    Once you unzip the .pkg file you can deploy it to your MDS system using the Model Deployment Wizard.

    The local general ledger systems in the company have different charts of accounts. The “Corporate” hierarchy in the sample illustrates how a derived hierarchy with an explicit cap can be used to establish a standard corporate chart of accounts that combines the local differing chart of accounts. The hierarchy uses the derived relationship between the account and subsidiary account entities and the explicit hierarchy “base” to standardize the rollup of the corporate chart of accounts.

    For more information about derived hierarchies with explicit caps, see “Derived Hierarchies with Explicit Caps (Master Data Services)” (http://msdn.microsoft.com/en-us/library/ff486988(SQL.105).aspx) in SQL Server Books Online

    Model Deployment API code sample

    Deploying the sample

    This sample illustrates calling the Model Deployment API via code with a simple console application demonstrating a couple of common methods. You can download the sample here:

    http://code.msdn.microsoft.com/ModelDApiSample

    Once you download the sample and unzip it, you will need to perform a couple of steps to successfully build the Visual Studio project. These steps are also documented in the readme in the .zip file.

    The sample has the following external dependencies:

    • Microsoft.MasterDataServices.Core.dll
    • Microsoft.MasterDataServices.Deployment.dll
    • Microsoft.MasterDataServices.Services.dll

    In order to build the solution, you will need to adjust the project references to point to these binaries in your Master Data Services deployment.

    You will also need to update the ConnectionString in ModelDUtil.config to point to your deployed database. Please be sure not to change the name of the connection -- that should be left as "defaultMdsConnection". Also, ModelDUtil.config must be located in the same folder as ModelDUtil.exe.

    Running the sample

    The console application can be run with the following commands:

    ModelDUtil [mode] [params]

    where [mode] is one of the following:

    ListModels -- returns a list of all the user models in the target system

    ModelDUtil ListModels

    ListVersions -- returns a list of the versions for a given model

    ModelDUtil ListVersions [model name]

    CreatePackage -- create a package file for a given model

    ModelDUtil CreatePackage [output package file name] [model name] [version name]

    DeployClone -- deploys a clone of a model from a given package

    ModelDUtil DeployClone [input package file name]

    DeployNew -- deploys a model from a given package with the new given name

    ModelDUtil DeployNew [input package file name] [new model name]

    DeployUpdate -- deploys an update to a given version of a model from a given package

    ModelDUtil DeployUpdate [input package file name] [version name to update]

    DeleteModel -- deletes a given model

    ModelDUtil DeleteModel [model name]

    Help -- displays the help

    ModelDUtil Help

    Note: names that contain spaces should be wrapped with double quotation marks. For Example: ModelDUtil DeployUpdate mypackage.pkg "Version 1"

    For more information about the API, see the Model Deployment API reference (http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.deployment(SQL.105).aspx) in SQL Server Books Online.

  • Updated Master Data Services Documentation and Resources

    (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team)

    With the release of SQL Server 2008 R2, it’s a great time to check out the updated documentation and resources for the release, and for SQL Server 2008 R2 Master Data Services ("MDS") in particular. As you saw in the last post (New White Papers Available), there are some great white papers available on MSDN to get you going with MDS. Below you’ll find more information about other updated and newly published content.


    SQL Server 2008 R2 Books Online
    The MDS product documentation was first published in SQL Server 2008 R2 Books Online with the November CTP (at the same time as the first public release of MDS in SQL Server 2008 R2). The MDS User Education (UE) team has been working on improving and adding a lot of additional content that was not available for the first release of the content. You can find the MDS portion of Books Online on both MSDN and TechNet at the following URLs:

    http://msdn.microsoft.com/en-us/library/ee633763(SQL.105).aspx
    http://technet.microsoft.com/en-us/library/ee633763(SQL.105).aspx

    With this documentation update, you’ll see the following improvements:

    • Overviews and workflows for getting started with MDS, installing and configuring MDS, and configuring security (in the Getting Started, Deployment, and Security and Protection nodes)
    • Better installation, configuration, and uninstall documentation (in the Deployment node)
    • Conceptual content with artwork tied to related procedural content (in the Operations node)
    • More comprehensive security documentation (in the Security and Protection node)
    • Updates to the technical reference to have more information about the staging tables and error messages (plus examples for importing data into those tables), Web configuration, and PowerShell cmdlets (in the Technical Reference node)
    • Troubleshooting documentation for installation and configuration, staging, model deployment, and notifications (links from the Troubleshooting node out to topics elsewhere in the doc set)

    If you were familiar with the content before, you’ll notice that some things have moved around a bit in the Table of Contents (TOC), such as some topics being moved out of Getting Started into Operations and out of Planning and Architecture to Deployment. We hope that by moving related content closer together, you’ll find things easier to navigate and have a more comprehensive view.


    Introducing Microsoft SQL Server 2008 R2
    Microsoft Press has released a free e-book for download, Introducing Microsoft SQL Server 2008 R2, which has a chapter about MDS, as well as chapters about the other exciting features and enhancements for the release. The URL to download the book is http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

    Enjoy the new content, and keep giving us feedback. We really appreciate it!

  • New White Papers Available

    (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team) 

    New SQL Server 2008 R2 Master Data Services ("MDS") white papers are now available on MSDN.

    For an application-agnostic overview of master data management, see Organizational Approaches to Master Data Management.

    For the steps needed to configure Master Data Services to work with a SharePoint workflow, see SharePoint Workflow Integration with Master Data Services.

  • Subscription Views - Part 3: Padding Level-Based Views

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

    Background

    This post is the third in a series on Subscription Views in SQL Server 2008 R2 Master Data Services (“MDS”). In the first post, I described how to create standard subscription views. In this post I will describe how to fill in the null columns in a level-based view of a ragged hierarchy.

    This post gives SQL examples that assume you have already created the “_ProductHierarchyLevels” view as described in the first post.

    For those new to SQL scripting and editing in SQL Server Management Studio, refer to Books Online for more information.

    The Problem

    Some subscribing systems require a fixed-level representation of a hierarchy even though the source hierarchy is fundamentally ragged or unbalanced and modeled that way in MDS. Consider the ragged hierarchy displayed below. In this hierarchy, the “PL” branch of the hierarchy is 2 levels deep (not counting “ROOT”) while the “AC” branch is 3 levels deep.

    image

    A level-based representation of this hierarchy would look like the following.

    image

    This level-based column format is often best for subscribing systems like a data warehouse dimension table. However, the nulls may present a problem. For example, in the data warehouse you cannot subtotal product sales by “Level 3” in the product hierarchy and achieve any meaningful information. One solution is to fill in these blank spots in the hierarchy with the leaf-level member. The modified format would then look like the following.

    image

    Solution: Creating a Padded View

    The following query uses the view “_ProductHierarchyLevels” as created in the first post on subscription views.

    SELECT [L0_Code]
          ,[L0_Name]
          ,[L1_Code]
          ,[L1_Name]
          ,[L2_Code]
          ,[L2_Name]
          ,[L3_Code]
          ,[L3_Name]
          ,[Leaf_Code]
          ,[Leaf_Name]
    FROM [mdm].[_ProductHierarchyLevels]
    WHERE Hierarchy = 'Product Management'

    The query results below show that some of the members do not have an L2 or L3 hierarchy parent.

    image

    We can replace the occurrences of NULL with the leaf-level member code and name as needed using the ISNULL transact-SQL function. To do this we could create a query as follows. ISNULL will replace the first value with an alternate value whenever the first value (argument) is NULL.

    SELECT [L0_Code]
          ,[L0_Name]
          ,[L1_Code]
          ,[L1_Name]
          ,ISNULL([L2_Code],[Leaf_Code]) AS L2_Code
          ,ISNULL([L2_Name],[Leaf_Name]) AS L2_Name
          ,ISNULL([L3_Code],[Leaf_Code]) AS L3_Code
          ,ISNULL([L3_Name],[Leaf_Name]) AS L3_Name
          ,[Leaf_Code]
          ,[Leaf_Name]
    FROM [mdm].[_ProductHierarchyLevels]
    WHERE Hierarchy = 'Product Management'

     

    Running the new query will produce what we refer to as a “padded-level view” with results as shown below.

    image

    This is just one way to pad the view. Sometimes you may want or need to pad from the top-down. The key point is you can create a simple view on top of the standard subscription view formats to achieve the final format you require.

    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.

  • Subscription Views - Part 2: Combining and Customizing Views

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

    Background

    This post is the second in a series on Subscription Views in SQL Server 2008 R2 Master Data Services (“MDS”). In the first post, I described how to create standard subscription views. In this post I will describe how to create your own custom views that build upon the standard views.

    This post gives SQL examples that assume you have already created the “_ProductHierarchyLevels” view and the “_ProductLeafAttributes” view as described in the first post.

    For those new to SQL scripting and editing in SQL Server Management Studio, refer to Books Online for more information.

    Example 1: Filtering Rows and Columns

    The standard subscription views may have more rows and columns than your export process needs. To improve query performance and produce only the subset of data that you need, consider building your own custom view on top of a standard, generated subscription view. For example, suppose you only wanted the level columns from the “Product Management” hierarchy. The following example SELECT statement specifically queries the level columns and filters the rows to the “Product Management” hierarchy only.

    SELECT [Leaf_Code]
          ,[Leaf_Name]
          ,[L3_Code]
          ,[L3_Name]
          ,[L2_Code]
          ,[L2_Name]
          ,[L1_Code]
          ,[L1_Name]
          ,[L0_Code]
          ,[L0_Name]
    FROM [mdm].[_ProductHierarchyLevels]
    WHERE Hierarchy = 'Product Management'

    To further this example, you could save this SELECT statement as your own custom view in the database by adding a CREATE VIEW statement at the beginning:

    CREATE VIEW _ProductsPMHierarchy AS
    SELECT [Leaf_Code]
          ,[Leaf_Name]
          ,[L3_Code]
          ,[L3_Name]
          ,[L2_Code]
          ,[L2_Name]
          ,[L1_Code]
          ,[L1_Name]
          ,[L0_Code]
          ,[L0_Name]
    FROM [mdm].[_ProductHierarchyLevels]
    WHERE Hierarchy = 'Product Management'

    Note: If you need to make changes to the view definition and recreate it, issue a DROP VIEW command as follows.

    DROP VIEW _ProductsPMHierarchy

    Example 2: Joining Multiple Views

    Another common requirement is combining attribute and hierarchy data together. Suppose you need a view combining both the Product Management hierarchy levels and the Product attributes. You can achieve this by creating a simple custom view with a JOIN on two standard subscription views. The following example SQL will create a new view “_ProductsAndPMHierarchy”.

    CREATE VIEW _ProductsAndPMHierarchy AS
    SELECT pa.[Member_ID]
          ,pa.[VersionName]
          ,pa.[VersionNumber]
          ,pa.[VersionFlag]
          ,pa.[Name]
          ,pa.[Code]
          ,hl.[L3_Code]
          ,hl.[L3_Name]
          ,hl.[L2_Code]
          ,hl.[L2_Name]
          ,hl.[L1_Code]
          ,hl.[L1_Name]
          ,hl.[L0_Code]
          ,hl.[L0_Name]
          ,pa.[ChangeTrackingMask]
          ,pa.[ProductSubCategory_Code]
          ,pa.[ProductSubCategory_Name]
          ,pa.[ProductSubCategory_ID]
          ,pa.[Color_Code]
          ,pa.[Color_Name]
          ,pa.[Color_ID]
          ,pa.[Class_Code]
          ,pa.[Class_Name]
          ,pa.[Class_ID]
          ,pa.[Style_Code]
          ,pa.[Style_Name]
          ,pa.[Style_ID]
          ,pa.[Country_Code]
          ,pa.[Country_Name]
          ,pa.[Country_ID]
          ,pa.[StandardCost]
          ,pa.[SafetyStockLevel]
          ,pa.[ReorderPoint]
          ,pa.[MSRP]
          ,pa.[Weight]
          ,pa.[DaysToManufacture]
          ,pa.[DealerCost]
          ,pa.[DocumentationURL]
          ,pa.[SellStartDate]
          ,pa.[SellEndDate]
          ,pa.[SizeUoM_Code]
          ,pa.[SizeUoM_Name]
          ,pa.[SizeUoM_ID]
          ,pa.[WeightUoM_Code]
          ,pa.[WeightUoM_Name]
          ,pa.[WeightUoM_ID]
          ,pa.[InHouseManufacture_Code]
          ,pa.[InHouseManufacture_Name]
          ,pa.[InHouseManufacture_ID]
          ,pa.[FinishedGoodIndicator_Code]
          ,pa.[FinishedGoodIndicator_Name]
          ,pa.[FinishedGoodIndicator_ID]
          ,pa.[DiscontinuedItemInd_Code]
          ,pa.[DiscontinuedItemInd_Name]
          ,pa.[DiscontinuedItemInd_ID]
          ,pa.[DiscontiuedDate]
          ,pa.[ProductLine_Code]
          ,pa.[ProductLine_Name]
          ,pa.[ProductLine_ID]
          ,pa.[DealerCostCurrencyCode_Code]
          ,pa.[DealerCostCurrencyCode_Name]
          ,pa.[DealerCostCurrencyCode_ID]
          ,pa.[MSRPCurrencyCode_Code]
          ,pa.[MSRPCurrencyCode_Name]
          ,pa.[MSRPCurrencyCode_ID]
          ,pa.[Size_Code]
          ,pa.[Size_Name]
          ,pa.[Size_ID]
          ,pa.[EnterDateTime]
          ,pa.[EnterUserName]
          ,pa.[EnterVersionNumber]
          ,pa.[LastChgDateTime]
          ,pa.[LastChgUserName]
          ,pa.[LastChgVersionNumber]
          ,pa.[ValidationStatus]
    FROM [mdm].[_ProductLeafAttributes] pa
    INNER JOIN [mdm].[_ProductHierarchyLevels] hl ON pa.Member_ID=hl.Leaf_ID
    WHERE hl.Hierarchy = 'Product Management'

    In this example, I have aliased the views as “pa” for “product attributes” and “hl” for “hierarchy levels”. Note that the two views have been joined on the common member key value in pa.Member_ID and hl.Leaf_ID. As in the first view, I have limited the hierarchy content to the Product Management hierarchy. When generating this SELECT statement, there is no need to type all these column names by hand; see Scripting a Table for more information on generating an initial SELECT statement.

    Once the view is created, we can select from the new view and see the combined set of columns. As shown below, the view now contains both hierarchy level columns and attribute columns for each product row.

    image

    With a little creativity and SQL scripting, the standard subscription views can be customized to produce nearly any output view format you may need.

    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.

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

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