This chapter is taken from book "Introducing
Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for
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
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
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
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
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
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 change attributes, manage hierarchies, apply business rules to validate master data, review and correct data quality issues, annotate master data, monitor changes, and reverse transactions.
- Version Management Use this area to create a new version of your master data model and underlying data, uncover all validation 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 to create and process batches for importing data from staging tables into the MDS database, view errors arising from the import process, and create subscription views for consumption of master data by operational and analytic applications. System Administration Use this area to create a new model and its entities and attributes, define business rules, configure notifications for failed data validation, and deploy a model to another system.
- User And Group Permissions Use this area to configure security for users and groups to access functional areas in Master Data Manager, to perform specific functions, and to restrict or deny access to specific model objects.
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
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 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 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
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
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 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 Attributes and validation issues
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 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 The Required Fields business rule
When creating a business rule, you can use any of the following types of
- Default Value Sets the default value of an attribute to blank, a specific value that you supply in the business rule, a generated value that increments from a specified starting value, or a value derived by concatenating multiple attribute values
- Change Value Updates the attribute value to blank, another attribute value, or a value derived by concatenating multiple attribute values
- Validation Creates a validation warning and, if you choose, sends a notification e-mail to a specified user or group
- External Action Starts a workflow at a specified 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 Business rule validation
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
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 A transaction annotation
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 leaf members, consolidated members, or collections. You provide only the member name and code in this table.
- tblSTGMemberAttribute Use this table to stage the attribute values for each member using one row per attribute, and include the member code to map the attribute to the applicable member.
- tblSTGRelationship Use this table to stage parent-child or sibling relationships between members in a hierarchy or a collection.
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
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
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
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 Querying the Product subscription view
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.
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
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 Model versions
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
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.
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
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
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 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 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 Mixed permissions for a derived hierarchy
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
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
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.
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
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
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
- Microsoft.MasterDataServices.Services Contains a class to provide instances of the MdsServiceHost class and a class to provide an API for operations related to business rules
- Microsoft.MasterDataServices.Services.DataContracts Contains classes to represent models and model objects
- Microsoft.MasterDataServices.Services.MessageContracts Contains classes to represent requests and responses resulting from MDS operations
- Microsoft.MasterDataServices.Services.ServiceContracts Contains an interface that defines the service contract for MDS operations based on WCF related to business 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
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
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
- Mdq.NGrams Outputs a stream of tokens (known as a set of n-grams) in the length specified by n for use in string comparisons to find approximate matches between strings
- Mdq.RegexExtract Finds matches by using a regular expression
- Mdq.RegexIsMatch Indicates whether the regular expression finds a match by using a regular expression
- Mdq.RegexIsValid Indicates whether the regular expression is valid
- Mdq.RegexMask Converts a set of regular expression option flags into a binary value
- Mdq.RegexMatches Finds all matches of a regular expression in an input string
- Mdq.RegexReplace Replaces matches of a regular expression in an input string with a different string
- Mdq.RegexSplit Splits an input string into an array of strings based on the positions of a regular expression within the input string
- Mdq.Similarity Returns a similarity score between two strings using a specified matching algorithm
- Mdq.SimilarityDate Returns a similarity score between two date values
- Mdq.Split Splits an input string into an array 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