Chapter 7: Master Data Services

Microsoft SQL Server 2008 R2 Master Data Services (MDS) is another new technology in the SQL Server family and is based on software from Microsoft’s acquisition of Stratature in 2007.

This chapter is taken from book "Introducing Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for Microsoft Press.

Microsoft SQL Server 2008 R2 Master Data Services (MDS) is another new technology in the SQL Server family and is based on software from Microsoft's acquisition of Stratature in 2007. Just as SQL Server Reporting Services (SSRS) is an extensible reporting platform that ships with ready-to-use applications for end users and administrators, MDS is both an extensible master data management platform and an application for developing, managing, and deploying master data models. MDS is included with the Datacenter, Enterprise, and Developer editions of SQL Server 2008 R2.

Master Data Management

In the simplest sense, master data refers to nontransactional reference data. Put another way, master data represents the business entities-people, places, or things-that participate in a transaction. In a data mart or data warehouse, master data becomes dimensions. Master data management is the set of policies and procedures that you use to create and maintain master data in an effort to overcome the many challenges associated with managing master data. Because it's unlikely that a single set of policies and procedures would apply to all master data in your organization, MDS provides the flexibility you need to accommodate a wide range of business requirements related to master data management.

Master Data Challenges

As an organization grows, the number of line-of-business applications tends to increase. Furthermore, data from these systems flows into reporting and analytical solutions. Often, the net result of this proliferation of data is duplication of data related to key business entities, even though each system might maintain only a subset of all possible data for any particular entity type. For example, customer data might appear in a sales application, a customer relationship management application, an accounting application, and a corporate data warehouse. However, there might be fields maintained in one application that are never used in the other applications, not to mention information about customers that might be kept in spreadsheets independent of any application. None of the systems individually provide a complete view of customers, and the multiple systems quite possibly contain conflicting information about specific customers.

This scenario presents additional problems for operational master data in an organization because there is no coordination across multiple systems. Business users cannot be sure which of the many available systems has the correct information. Moreover, even when a user identifies a data quality problem, the process for properly updating the data is not always straightforward or timely, nor does fixing the data in one application necessarily ripple through the other applications to keep all applications synchronized.

Compounding the problems further is data that has no official home in the organization's data management infrastructure. Older data might be archived and no longer available in operational systems. Other data might reside only in e-mail or in a Microsoft Access database on a computer sitting under someone's desk.

Some organizations try their best not to add another system dedicated to master data management to minimize the number of systems they must maintain. However, ultimately they find that neither existing applications nor ETL processes can be sufficiently extended to accommodate their requirements. Proper master data management requires a wide range of functionality that is difficult, if not impossible, to replicate through minor adaptations to an organization's technical infrastructure.

Last, the challenges associated with analytic master data stem from the need to manage dimensions more effectively. For example, analysts might require certain attributes in a business intelligence (BI) solution, but these attributes might have no source in the line-ofbusiness applications on which the BI solution is built. In such a case, the ETL developer can easily create a set of static attributes to load into the BI solution, but what happens when the analyst wants to add more attributes? Moreover, how gracefully can that solution handle changes to hierarchical structures?

Key Features of Master Data Services

The goal of MDS is to address the challenges of both operational and analytical master data management by providing a master data hub to centrally organize, maintain, and manage your master data. This master data hub supports these capabilities with a scalable and extensible infrastructure built on SQL Server and the Windows Communication Foundation (WCF) APIs. By centralizing the master data in an external system, you can more easily align all business applications to this single authoritative source. You can adapt your business processes to use the master data hub as a System of Entry that can then update downstream systems. Another option is to use it as a System of Record to integrate data from multiple source systems into a consolidated view, which you can then manage more efficiently from a central location. Either way, this centralization of master data helps you improve and maintain data quality. Because the master data hub is not specific to any domain, you can organize your master data as you see fit, rather than force your data to conform to a predefined format. You can easily add new subject areas as necessary or make changes to your existing master data to meet unique requirements as they arise. The master data hub is completely metadata driven, so you have the flexibility you need to organize your master data.

In addition to offering flexibility, MDS allows you to manage master data proactively. Instead of discovering data problems in failed ETL processes or inaccurate reports, you can engage business users as data stewards. As data stewards, they have access to Master Data Manager, a Web application that gives them ownership of the processes that identify and react to data quality issues. For example, a data steward can specify conditions that trigger actions, such as creating a default value for missing data, sending an e-mail notification, or launching a workflow. Data stewards can use Master Data Manager not only to manage data quality issues, but also to edit master data by adding new members or changing values. They can also enhance master data with additional attributes or hierarchical structures quickly and easily without IT support. Using Master Data Manager, data stewards can also monitor changes to master data through a transaction logging system that tracks who made a change, when the change was made, which record was changed, and what the value was both before and after the change. If necessary, the data steward can even reverse a change.

MDS uses Windows integrated security for authentication and a fine-grained, role-based system for authorization that allows administrators to give the right people the direct access they need to manage and update master data. As an administrator, you can grant broad access to all objects in a model, or you can restrict users to specific rows and columns in a data set. To capture the state of master data at specific points in time, MDS allows administrators to create versions of the master data. As long as a version has an Open status, anyone with access to the model can make changes to it. Then you can lock the version for validation and correction, and commit the version when the model is ready use. If requirements change later, you copy a committed version and start the process anew.

Because MDS is a platform, not simply an application, you can use the API to integrate your existing applications with MDS and automate the import or export processes. Anything that you can do by using Master Data Manager can be built into your own custom application because the MDS API supports all operations. This capability also enables Microsoft partners to quickly build master data support into their applications with domain-specific user interfaces and transparent application integration.

Master Data Services Components

Although MDS is included on the SQL Server installation media, you perform the MDS installation separately from the SQL Server installation by using a wizard interface. The wizard installs Master Data Services Configuration Manager, installs the files necessary to run the Master Data Services Web service, and registers assemblies. After installation, you use the Master Data Services Configuration Manager to create and configure a Master Data Services database in a SQL Server instance that you specify, create the Master Data Services Web application, and enable the Web service.

Master Data Services Configuration Manager

Before you can start using MDS to manage your master data, you use Master Data Services Configuration Manager. This configuration tool includes pages to create the MDS database, configure the system settings for all Web services and applications that you associate with that database, and configure the Master Data Services Web application.

On the Databases page of Master Data Services Configuration Manager, you specify the SQL Server instance to use for the new MDS database and launch the process to create the database. After creating the database, you can modify the system settings that govern all MDS Web applications that you establish on the same server. You configure system settings to set thresholds, such as time-out values or the number of items to display in a list. You can also use system settings to manage application behavior, such as whether users can copy committed model versions or any model version and whether the staging process logs transactions. For e-mail notifications, you can configure system settings to include a URL to Master Data Manager in e-mails, to manage the frequency of notifications, and whether to send e-mails in HTML or text format, among other settings. Most settings are configurable by using Master Data Services Configuration Manager. You can change values for other settings directly in the System Settings table in the MDS database.

On the Web Configuration page of Master Data Services Configuration Manager, you associate the Master Data Services Web application, Master Data Manager, with an existing Web site or create a new Web site and application pool for it. You can also opt to enable the Web service for Master Data Manager to support programmatic access to the application.

The Master Data Services Database

The MDS database is the central repository for all information necessary to support the Master Data Manager application and the MDS Web service. This database stores application settings, metadata tables, and all versions of the master data. In addition, it contains tables that MDS uses to stage data from source systems and subscription views for downstream systems that consume master data.

Master Data Manager

Master Data Manager is a Web application that serves as a stewardship portal for business users and a management interface for administrators. Master Data Manager includes the following five functional areas:

  • Explorer Use this area to changeattributes, manage hierarchies, apply business rules to validate masterdata, review and correct data quality issues, annotate master data, monitorchanges, and reverse transactions.
  • Version Management Use this area to createa new version of your master data model and underlying data, uncover allvalidation issues in a model version, prevent users from making changes,assign a flag to indicate the current version for subscribing systems,review changes, and reverse transactions.
  • Integration Management Use this area tocreate and process batches for importing data from staging tables into theMDS database, view errors arising from the import process, and createsubscription views for consumption of master data by operational andanalytic applications. System Administration Use this area to create a newmodel and its entities and attributes, define business rules, configurenotifications for failed data validation, and deploy a model to anothersystem.
  • User And Group Permissions Use this areato configure security for users and groups to access functional areas inMaster Data Manager, to perform specific functions, and to restrict or denyaccess to specific model objects.

Data Stewardship

Master Data Manager is the data stewardship portal in which authorized business users can perform all activities related to master data management. At minimum, a user can use this Web application to review the data in a master data model. Users with higher permissions can make changes to the master data and its structure, define business rules, review changes to master data, and reverse changes.

Model Objects

Most activities in MDS revolve around models and the objects they contain. A model is a container for all objects that define the structure of the master data. A model contains at least one entity, which is analogous to a table in a relational database. An entity contains members, which are like the rows in a table, as shown in Figure 7-1. Members (also known as leaf members) are the master data that you are managing in MDS. Each leaf member of the entity has multiple attributes, which correspond to table columns in the analogy.

Figure-7.1.gif

FIGURE 7-1 The Product entity

By default, an entity has Name and Code attributes, as shown in Figure 7-1. These two attributes are required by MDS. The Code attribute values must be unique, in the same way that a primary key column in a table requires unique values. You can add any number of additional free-form attributes to accept any type of data that the user enters; the Name attribute of the Product entity shown in Figure 7-1 is one such attribute.

An entity can also have any number of domain-based attributes whose values are members of another related entity. In the example in Figure 7-1, the ProductSubCategory attribute is a domain-based attribute. That is, the ProductSubCategory codes are attribute values in the Product entity, and they are also members of the ProductSubCategory entity. A third type of attribute is the file attribute, which you can use to store a file or image.

You have the option to organize attributes into attribute groups. Each attribute group contains the name and code attributes of the entity. You can then assign the remaining attributes to one or more attribute groups or not at all. Attribute groups are securable objects.

You can organize members into hierarchies. Figure 7-2 shows partial data from two types of hierarchies. On the left is an explicit hierarchy, which contains all members of a single entity. On the right is a derived hierarchy, which contains members from multiple, related entities.

Figure-7.2.gif

FIGURE 7-2 Product hierarchies

In the explicit hierarchy, you create consolidated members to group the leaf members. For example, in the Geography hierarchy shown in Figure 7-2, North America, United States, and Bikes are all consolidated members that create multiple levels for summarization of the leaf members.

In a derived hierarchy, the domain-based attribute values of an entity define the levels. For example, in the Category hierarchy in the example, Wholesale is in the ProductGroup entity, which in turn is a domain-based attribute of the ProductCategory entity of which Components is a member. Likewise, the ProductCategory entity is a domain-based attribute of the ProductSubCategory entity, which contains Forks as a member. The base entity, Product, includes ProductSubCategory as a domain-based attribute.

Regardless of hierarchy type, each hierarchy contains all members of the associated entities. When you add, change, or delete a member, all hierarchies to which the member belongs will also update to maintain consistency across hierarchies.

A collection is an alternative way to group members by selecting nodes from existing explicit hierarchies, as shown in Figure 7-3. Although this example shows only leaf members, a collection can also contain branches of consolidated members and leaf members. You can combine nodes from multiple explicit hierarchies into a single collection, but all members must belong to the same entity.

Figure-7.3.gif

FIGURE 7-3 A collection

Master Data Maintenance

Master Data Manager is more than a place to define model objects. It also allows you to create, edit, and update leaf members and consolidated members. When you add a leaf member, you initially provide values for only the Name and Code attributes, as shown in Figure 7-4. You can also use a search button to locate and select the parent consolidated member in each hierarchy.

Figure-7.4.gif

FIGURE 7-4 Adding a new leaf member

After you save your entry, you can edit the remaining attribute values immediately or at a later time. Although a member can have hundreds of attributes and belong to multiple hierarchies, you can add the new member without having all of this information at your fingertips; you can update the attributes at your leisure. MDS always keeps track of the missing information, displaying it as validation issue information at the bottom of the page on which you edit the attribute values, as shown in Figure 7-5.

Figure-7.5.gif

FIGURE 7-5 Attributes and validation issues

Business Rules

One of the goals of a master data management system is to set up data correctly once and to propagate only valid changes to downstream systems. To achieve this goal, the system must be able to recognize valid data and to alert you when it detects invalid data. In MDS, you create business rules to describe the conditions that cause the data to be considered invalid. For example, you can create a business rule that specifies the required attributes (also known as fields) for an entity. A business entity is likely to have multiple business rules, which you can sequence in order of priority, as shown in Figure 7-6.

Figure-7.6.gif

FIGURE 7-6 The Product entity's business rules

Figure 7-7 shows an example of a simple condition that identifies the required fields for the Product entity. If you omit any of these fields when you edit a Product member, MDS notes a validation issue for that member and prevents you from using the master data model until you supply the missing values.

Figure-7.7.gif

FIGURE 7-7 The Required Fields business rule

When creating a business rule, you can use any of the following types of actions:

  • Default Value Sets the default value of anattribute to blank, a specific value that you supply in the business rule, agenerated value that increments from a specified starting value, or a valuederived by concatenating multiple attribute values
  • Change Value Updates the attribute valueto blank, another attribute value, or a value derived by concatenatingmultiple attribute values
  • Validation Creates a validation warningand, if you choose, sends a notification e-mail to a specified user or group
  • External Action Starts a workflow at aspecified Microsoft SharePoint site or initiates a custom action

Because users can add or edit data only while the master data model version is open, invalid data can exist only while the model is still in development and unavailable to other systems. You can easily identify the members that pass or fail the business rule validation when you view a list of members in Explorer, as shown in Figure 7-8. In this example, the first two records are in violation of one or more of the business rules. Remember that you can see the specific violation issues for a member when you open it for editing.

Figure-7.8.gif

FIGURE 7-8 Business rule validation

Transaction Logging

MDS uses a transaction log, as shown in Figure 7-9, to capture every change made to master data, including the master data value before and after the change, the user who made the change (not shown), the date and time of the change, and other identifying information about the master data. You can access this log to view all transactions for a model by version in the Version Management area of Master Data Manager. If you find that a change was made erroneously, you can select the transaction in the log and click the Undo button above the log to restore the prior value. The transaction log also includes the reversals you make when using this technique.

Figure-7.9.gif

FIGURE 7-9 The transaction log

MDS allows you to annotate any transaction so that you can preserve the reasons for a change to the master data. When you select a transaction in the transactions log, a new section appears at the bottom of the page for transaction annotations. Here you can view the complete set of annotations for the selected transaction, if any, and you can enter text for a new annotation, as shown in Figure 7-10.

Figure-7.10.gif

FIGURE 7-10 A transaction annotation

Integration

Master Data Manager also provides support for data integration between MDS and other applications. Master Data Manager includes an Integration Management area for importing and exporting data. However, the import and export processes here are nothing like those of the SQL Server Import And Export wizard. Instead, you use the Import page in Master Data Manager to manage batch processing of staging tables that you use to load the MDS database, and you use the Export page to configure subscription views that allow users and applications to read data from the MDS database.

Importing Master Data

Rather than manually entering the data by using Master Data Manager, you can import your master data from existing data sources by staging the data in the MDS database. You can stage the data by using either the SQL Server Import And Export wizard or SQL Server Integration Services. After staging the data, you use Master Data Manager to process the staged data as a batch. MDS moves valid data from the staging tables into the master data tables in the MDS database and flags any invalid records for you to correct at the source and restage.

You can use any method to load data into the staging tables. The most important part of this task is to ensure that the data is correct in the source and that you set the proper values for the columns that provide information to MDS about the master data. For example, each record must identify the model into which you will load the master data. When staging data, you use the following tables in the MDS database as appropriate to your situation:

  • tblSTGMember Use this table to stage leafmembers, consolidated members, or collections. You provide only the membername and code in this table.
  • tblSTGMemberAttribute Use this table tostage the attribute values for each member using one row per attribute, andinclude the member code to map the attribute to the applicable member.
  • tblSTGRelationship Use this table to stageparent-child or sibling relationships between members in a hierarchy or acollection.

NOTE For detailed information about the table columns and valid values for required columns, refer to the "Master Data Services Database Reference" topic in SQL Server 2008 R2 Books Online at http://msdn.microsoft.com/en-us/library/ee633808(SQL.105).aspx.

The next step is to use Master Data Manager to create a batch. To do this, you identify the model and the version that stores the master data for the batch. The version must have a status of either Open or Locked to import data from a staging table. On your command to process the batch, MDS attempts to locate records in the staging tables that match the specified model and load them into the tables corresponding to the model and version that you selected. When the batch processing is complete, you can review the status of the batch in the staging batch log, which is available in Master Data Manager, as shown in Figure 7-11.

Figure-7.11.gif

FIGURE 7-11 The staging batch log

If the log indicates any errors for the staging batch, you can select the batch in the log and then view the Staging Batch Errors page to see a description of the error for each record that did not successfully load into the MDS database. You can also check the Status_ID column of the staging table to distinguish between successful and failed records, which have a column value of 1 and 2, respectively. At this point, you should return to the source system and update the pertinent records to correct the errors. The next steps would be to truncate the staging table to remove all records and finally to load the updated records. At this point, you can create a new staging batch and repeat the process until all records successfully load.

Exporting Master Data

Of course, MDS is not a destination system for your master data. It can be both a system of entry and a system of record for applications important to the daily operations of your organization, such as an enterprise resource planning (ERP) system, a customer relationship management (CRM) system, or a data warehouse. After you commit a model version, your master data is available to other applications through subscription views in the MDS database. Any system that can consume data from SQL Server can use these views to access up-to-date master data.

To create a subscription view in Master Data Manager, you start by assigning a name to the view and selecting a model. You then associate the view with a specific version or a version flag.

TIP You can simplify the administration of a subscription view by associating it with a version flag rather than a specific version. As the version of a record changes over time, you can simply reset the flag for the versions. If you don't use version flags, a change in version requires you to update every subscription view that you associate with the version, which could be a considerable number.

Next, you select either an entity or a derived hierarchy as the basis for the view and the format of the view. For example, if you select an entity, you can format the view to use leaf members, consolidated members, or collection members and the associated attribute values. When you save the view, it is immediately available in the MDS database to anyone (or any application) with Read access to the database. For example, after creating the Product

subscription view in Master Data Manager as an entity-based leaf member view, you can query the Product view and see the results in SQL Server Management Studio, as shown in Figure 7-12.

Figure-7.12.gif

FIGURE 7-12 Querying the Product subscription view

Administration

Of course, Master Data Manager supports administrative functions, too. Administrators use it to manage the versioning process of each master data model and to configure security for individual users and groups of users. When you need to make a copy of a master data model on another server, as you would when you want to recreate your development environment on a production server, you can use the model deployment feature in Master Data Manager.

Versions

MDS uses a versioning management process to support multiple copies of master data. With versioning, you can maintain an official working copy of master data that no one can change, alongside historical copies of master data for reference and a work-in-progress copy for use in preparing the master data for changing business requirements.

MDS creates the initial version when you create a model. Anyone with the appropriate permissions can populate the model with master data and make changes to the model objects in this initial version until you lock the version. After that, only users with Update permissions on the entire model can continue to modify the data in the locked version to add missing information, fix any business rule violation, or revert changes made to the model. If necessary, you can temporarily unlock the version to allow other users to correct the data.

When all data validates successfully, you can commit the version. Committing a version prevents any further changes to the model and allows you to make the version available to downstream systems through subscriptions. You can use a flag, as shown in Figure 7-13, to identify the current version to use so that subscribing systems do not need to track the current version number themselves. If you require any subsequent changes to the model, you create a new version by copying a previously committed version and allowing users to make their changes to the new version.

Figure-7.13.gif

FIGURE 7-13 Model versions

Security

MDS uses a role-based authorization system that allows you to configure security both by functional area and by object. For example, you can restrict a user to the Explorer area of Master Data Manager, as shown in Figure 7-14, while granting another user access to only the Version Management and Integration Management areas. Then, within the functional area, you must grant a user access to one or more models to control which data the user can see and which data the user can edit. You must assign the user permission to access at least one functional area and one model for that user to be able to open Master Data Manager.

Figure-7.14.gif

FIGURE 7-14 Functional area permissions

You can grant a user either Read-only or Update permissions for a model. That permission level applies to all objects in the model unless you specifically override the permissions for a particular object; the new permission cascades downward to lower level objects. Similarly, you can grant permissions on specific members of a hierarchy and allow the permissions to cascade to members at lower levels of the hierarchy.

To understand how security works in MDS, let's configure security for a sample user and see how the security settings affect the user experience. As you saw earlier in Figure 7-14, the user can access only the Explorer area in Master Data Manager. Accordingly, that is the only functional area that is visible when the user accesses Master Data Manager, as shown in Figure 7-15. An administrator with full access privileges would instead see the full list of functional areas on the home page.

Figure-7.15.gif

FIGURE 7-15

The Master Data Manager home page for a user with only Explorer permissions Data security begins at the model level. When you deny access to a model, the user does not even see it in Master Data Manager. With Read-only access, a user can view the model structure and its data but cannot make changes. Update permissions allow a user to see the data as well as make changes to it. To continue the security example, Figure 7-16 shows that this user has Read-only permissions for the Product model (as indicated by the lock icon) and Deny permissions on all other models (as indicated by the stop symbol) in the Model Permissions tree view on the left. In the Model Permissions Summary table on the right, you can see the assigned permissions at each level of the model hierarchy. Notice that the user has Update permission on leaf members of the ProductCategory entity.

Figure-7.16.gif

FIGURE 7-16 A user's model permissions

With Read-only access to the model, except for the ProductCategory entity, the user can view data for all other entities or hierarchies, such as Color, as shown in Figure 7-17, but cannot edit the data in any way. Notice the lock icons in the Name and Code columns in the

Color table on the right side of the page. These icons indicate that the values in the table are not editable. The first two buttons above the table allow a user with Update permissions to add or delete a member, but those buttons are unavailable here because the user has Readonly permission. The user can also navigate through the hierarchy in the tree view on the left side of the page, but the labels are gray to indicate the Read-only status for every member of the hierarchy.

Figure-7.17.gif

FIGURE 7-17 Read-only permission on a hierarchy

At this point in the example, the user has Update permission on the ProductCategory entity, which allows the user to edit any member of that entity. However, you can apply a more granular level of security by changing permissions of individual members of the entity within a hierarchy. As shown in Figure 7-18, you can override the Update permission at the entity level by specifying Read-only permission on selected members. The tree view on the left side of the page shows a lock icon for the members to which Read-only permissions apply and a pencil icon for the members for which the user has Update permissions.

Figure-7.18.gif

FIGURE 7-18 Member permissions within a hierarchy

More specifically, the security configuration allows this user to edit only the Bikes and Accessories categories in the Retail group, but the user cannot edit categories in the Wholesale group. Let's look first at the effect of these permissions on the user's experience on the ProductCategory page (shown in Figure 7-19). The lock icon in the first column indicates that the Components and Clothing categories are locked for editing. However, the user has Update permission for both Bikes and Accessories, and can access the member menu for either of these categories. The member menu, as shown in the figure, allows the user to edit or delete the member, view its transactions, and add an annotation. Furthermore, the user can add new members to the entity.

Figure-7.19.gif

FIGURE 7-19 Mixed permissions for an entity

Last, Figure 7-20 shows the page for the Category derived hierarchy. Recall from Figure 7-19 that the user has Update permission for the Retail group. The user can therefore modify the Retail member, but not the Wholesale member, as indicated by the lock icon to the left of the Wholesale member in the ProductGroup table. You can also see the color-coding of the labels in the tree view of the Category hierarchy, which indicates whether the member is editable by the user. The user can edit members that are shown in black, but not the members shown in gray. When the user selects a member in the tree view, the table on the right displays the children of the selected member if the user has the necessary permission.

Figure-7.20.gif

FIGURE 7-20 Mixed permissions for a derived hierarchy

Model Deployment

When you have finalized the master data model structure, you can use the model deployment capabilities in Master Data Manager to serialize the model and its objects as a package that you can later deploy on another server. In this way, you can move a master data model from development to testing and to production without writing any code or moving data at the table level. The deployment process does not copy security settings. Therefore, after moving the master data model to the new server, you must grant the users access to functional areas and configure permissions.

To begin the model deployment, you use the Create Package wizard in the System Administration area of Master Data Manager. You specify the model and version that you want to deploy and whether you want to include the master data in the deployment. When you click Finish to close the wizard, Master Data Manager initiates a download of the package to your computer, and the File Download message box displays. You can then save the package for deployment at a later time.

When you are ready to deploy the package, you use the Deploy Package wizard in Master Data Manager on the target server and provide the wizard with the path to the saved package. The wizard checks to see whether the model and version already exist on the server. If so, you have the option to update the existing model by adding new items and updating existing items. Alternatively, you can create an entirely new model, but if you do so, the relationship with the source model is then permanently broken, and any subsequent updates to the source model cannot be brought forward to the copy of the model on the target server.

Programmability

Rather than use Master Data Manager exclusively to perform master data management operations, you might prefer to automate some operations to incorporate them into a custom application. Fortunately, MDS is not just an application ready to use after installation, but also a development platform that you can use to integrate master data management directly into your existing business processes.

TIP For a code sample that shows how to create a model and add entities to the model, see the following blog entry by Brent McBride, a Senior Software Engineer on the MDS team: "Creating Entities using the MDS WCF API," at http://sqlblog.com/blogs/mds_team /archive/2010/01/29/creating-entities-using-the-mds-wcf-api.aspx.

The Class Library

The MDS API allows you to fully customize any or all activities necessary to create, populate, maintain, manage, and secure master data models and associated data. To build your own data stewardship or management solution, you use the following namespaces:

  • Microsoft.MasterDataServices.ServicesContains a class to provide instances of the MdsServiceHost class and aclass to provide an API for operations related to business rules
  • Microsoft.MasterDataServices.Services.DataContracts Contains classes torepresent models and model objects
  • Microsoft.MasterDataServices.Services.MessageContracts Contains classes torepresent requests and responses resulting from MDS operations
  • Microsoft.MasterDataServices.Services.ServiceContracts Contains an interfacethat defines the service contract for MDS operations based on WCF related tobusiness rules, master data, metadata, and security

NOTE For more information about the MDS class libraries, refer to the "Master Data Services Class Library" topic in SQL Server 2008 R2 Books Online at http://msdn.microsoft.com /en-us/library/ee638492(SQL.105).aspx.

Master Data Services Web Service

MDS includes a Web services API as an option for creating custom applications that integrate MDS with an organization's existing applications and processes. This API provides access to the master data model definitions, as well as to the master data itself. For example, by using this API, you can completely replace the Master Data Manager Web application.

TIP For a code sample that shows how to use the Web service in a client application, see the following blog entry by Val Lovicz, Principal Program Manager on the MDS team: "Getting Started with the Web Services API in SQL Server 2008 R2 Master Data Services," at http://sqlblog.com/blogs/mds_team/archive/2010/01/12/getting-started-with-the-webservices- api-in-sql-server-2008-r2-master-data-services.aspx.

Matching Functions

MDS also provides you with several new Transact-SQL functions that you can use to match and cleanse data from multiple systems prior to loading it into the staging tables:

  • Mdq.NGrams Outputs a stream of tokens(known as a set of n-grams) in the length specified by n for use in stringcomparisons to find approximate matches between strings
  • Mdq.RegexExtract Finds matches by using aregular expression
  • Mdq.RegexIsMatch Indicates whether theregular expression finds a match by using a regular expression
  • Mdq.RegexIsValid Indicates whether theregular expression is valid
  • Mdq.RegexMask Converts a set of regularexpression option flags into a binary value
  • Mdq.RegexMatches Finds all matches of aregular expression in an input string
  • Mdq.RegexReplace Replaces matches of aregular expression in an input string with a different string
  • Mdq.RegexSplit Splits an input string intoan array of strings based on the positions of a regular expression withinthe input string
  • Mdq.Similarity Returns a similarity scorebetween two strings using a specified matching algorithm
  • Mdq.SimilarityDate Returns a similarityscore between two date values
  • Mdq.Split Splits an input string into anarray of strings using specified characters as a delimiter

NOTE For more information about the MDS functions, refer to the "Master Data Services Functions (Transact-SQL)" topic in SQL Server 2008 R2 Books Online at http://msdn.microsoft.com/en-us/library/ee633712(SQL.105).aspx.