Master data services руководство

Время на прочтение
8 мин

Количество просмотров 20K

В этой статье я хотел бы поделиться своим первым профессиональным опытом применения Master Data Services (MDS) в MS SQL Server 2012. До недавнего времени я был знаком с этим продуктом, входящим в состав MS SQL Server 2012 (Business Intelligence and Enterprise editions), только в теории и ждал удачного случая, чтобы проверить его на практике, и вот такой случай представился.

Вводные данные

Мой клиент использует BI-решение на базе Microsoft SQL Server 2012 Business Intelligence Edition. Центром этой BI-системы является хранилище данных (Data Warehouse), которое наполняется с помощью SSIS-пакетов из транзакционных систем (OLTP). Хранилище данных, в свою очередь, служит источником для многомерной модели данных (Multidimensional Data Model). На основе многомерной модели данных аналитики заказчика формируют отчетность в Excel, используя сводные таблицы (Pivot Tables). Поскольку источников данных много, и пользователи транзакционных систем управляют справочниками хаотично, заказчик обозначил потребность в решении, которое позволяло бы создать в хранилище данных иерархии для некоторых измерений, которые были бы удобны для целей аналитического анализа. Так, например, информация об организационной структуре компании, хранящаяся в измерении «Подразделения» (таблица dbo.dimDivisions), импортируемая из OLTP-систем, удобна для оперативных отчетов, формируемых в OLTP-системах, но не подходит для целей анализа в BI-системе.

Требования бизнеса

Формальные требования бизнес-заказчика можно кратко описать следующим образом:

• Разработать инструмент, позволяющий управлять данными о подразделениях компании для целей аналитической отчетности.
• Использовать новые данные о подразделения в существующем BI-решений.
• Изменения не должны повлиять на работу OLTP-систем.

Предварительная оценка и выбор решения

На основании имеющихся вводных данных и бизнес-требований заказчику было предложено следующее решение:

• Добавить в хранилище данных дополнительное измерение для хранения данных об орг. структуре для целей аналитической отчетности.
• Обеспечить связь между записями нового измерения и измерения «Сотрудники».
• Изменить многомерную модель данных с учетом изменения структуры хранилища данных.
• Выполнить настройку Master Data Services. Под настройкой понимается создание возможности ввода и редактирования данных о сотрудниках и подразделениях вручную, а так же настройка, необходимая для импорта/экспорта данных в/из MSD.
• Обеспечить автоматический импорт данных о новых сотрудниках из хранилища данных в Master Data Services.
• Обеспечить автоматический экспорт данных о подразделениях и принадлежности сотрудников к подразделениям сотрудниках из Master Data Services в хранилище данных.

Реализация решения

Доработка хранилища данных

Итак, поехали по-порядку. Сначала создадим в хранилище данных новое измерение «Произвольные подразделения» (dbo.dimDerivedDivisions) и свяжем его с измерением «Сотрудники» (dbo.dimEmploees). SQL-скрипт для этой задачи выглядит следующим образом:

--Создадим новое измерение "Произвольные подразделения"
CREATE TABLE dbo.dimDerivedDivisions
(
    id int NOT NULL primary key identity(1, 1),
	parentId int NULL,
	sourceCode int NOT NULL,
	sourceParentCode int NULL,
	name nvarchar(100) NOT NULL DEFAULT ('N/A'),
	lineageDate datetime DEFAULT GETDATE(),
	lineageSource nvarchar(255) NOT NULL DEFAULT ('')
);

--Определим внешний ключ для parentId, ссылающийся на dbo.dimDerivedDivisions(id) для обеспечения иерархии родитель-потомок
ALTER TABLE dbo.dimDerivedDivisions
	ADD CONSTRAINT fk_dbo_dimDerivedDivisions_dbo_dimDerivedDivisions
		FOREIGN KEY (parentId)
			REFERENCES dbo.dimDerivedDivisions(id);

--В новое измерение добавим значение по умолчанию, на которое будут ссылаться не распределенные сотрудники
SET IDENTITY_INSERT dbo.dimDerivedDivisions ON;
INSERT INTO dbo.dimDerivedDivisions
	(id, parentId, sourceCode, sourceParentCode, name, lineageDate, lineageSource)
	SELECT
		0, NULL, 0, NULL, 'N/A', GETDATE(), 'Запись введена вручную'
	WHERE
		NOT EXISTS (SELECT id FROM dbo.dimDerivedDivisions WHERE id = 0);
SET IDENTITY_INSERT dbo.dimDerivedDivisions OFF;

--Добавим новую колонку в измерение "Сотрудники"
ALTER TABLE dbo.dimEmployees ADD derivedDivisionId int NOT NULL DEFAULT(0);

--Определим внешний ключ, ссылающийся на dbo.dimDerivedDivisions(id)
ALTER TABLE dbo.dimEmployees
	ADD CONSTRAINT fk_dbo_dimEmployees_dbo_dimDerivedDivisions
		FOREIGN KEY (derivedDivisionId)
			REFERENCES dbo.dimDerivedDivisions(id);

Доработка многомерной модели данных

Теперь добавим новое измерение в многомерную модель данных. Для этого откроем проект многомерной модели данных в SQL Server Data Tools и добавим новую таблицу dbo.dimDerivedDivisions в Data Source View. Результат выглядит следующим образом:

Чтобы не уходить далеко от темы, кратко опишу процесс доработки многомерной модели. В многомерной модели данных создаем новое измерение «Произвольные подразделения», настраиваем связь нового измерения с существующими таблицами фактов через измерение «Сотрудники», деплоим и процессим куб:

Настройка Master Data Services

Теперь все структуры данных для подготовки аналитической отчетности в разрезе нового измерения «Произвольные измерения» готовы, приступаем к самому главному — к настройке Master Data Services. Для этого в браузере переходим по ссылке, которую дал нам SQL Server Administrator, и попадаем в web-интерфейс MDS, который выглядит следующим образом:

Я не буду описывать здесь установку Master Data Services, так как это рутинная задача подробно описана на msdn.microsoft.com. Сосредоточимся лучше на реальной практике использования MDS.

Итак, первое, что нам необходимо сделать — это создать модель. Моделью в MDS называется логический контейнер, который содержит в себе сущности определенной бизнес-области. В нашем случае уместно создать модель «Сотрудники», содержащую сущности «Сотрудники» и «Подразделения». Для создания модели перейдем в web-интерфейсе Master Data Services в Administrative Tasks по ссылке System Administration. В открывшемся окне введем название модели Employees и нажмем кнопку Save model:

При установленном флажке Create entity with same name as model автоматически вместе с моделью будет создана одноименная сущность Employees. Далее создадим еще одну сущность «Подразделения» (Divisions), для этого выберем модель Employees и перейдем в меню Manage — Entities:

Нажмем на кнопку Add entity:

В открывшемся диалоге заполним параметры новой сущности и нажмем кнопку Save entity. Обратите внимание, что при создании сущности «Подразделения» был установлен флажок Enable explicit hierarchies and collections (это означает, что для сущности появится возможность создания иерархии), и ниже укажем название иерархии Divisions. Явная иерархия (Explicit Hierarchy) — это иерархия, члены которой могут быть организованы любым образом, т.е. на каждом уровне иерархии может быть любое любое количество членов и следующих уровней вложенности:

После того, как сущности созданы, необходимо настроить атрибуты сущностей. Для сущности «Сотрудники» (Employees) добавим атрибут «Подразделение» (Division). Выберем сущность Employees и нажмем кнопку Edit selected entity:

В открывшейся форме редактирования сущности нажмем на кнопку Add leaf attribute для того, чтобы добавить атрибут «Подразделение» конечного элемента сущности «Сотрудники»:

В открывшейся форме добавления атрибута, заполним наименование атрибута и установим переключатель типа атрибута в положение Domain-based. Это означает, что значения данного атрибута будут принадлежать определенной сущности, и ниже укажем какой именно, в нашем случае это сущность «Подразделения» (Divisions). В конце нажимаем кнопку Save attribute:

Ручной ввод данных в Master Data Services

Итак, модель «Сотрудники» (Employees) и сущности «Сотрудники» (Employees) и «Подразделения» (Divisions) готовы, теперь необходимо наполнить их данными. Сущность «Подразделения» (Divisions) пользователи будут заполнять вручную. Для демонстрации этого процесса перейдем на главную страницу web-интерфейса Master Data Services, в подразделе Information Worker Tasks выберем модель Employees и перейдем по ссылке Explorer:

Выберем иерархию «Подразделения» (Divisions):

Выберем тип элементов, которыми мы хотим управлять. Сначала создадим несколько групповых элементов (Consolidated Member):

Нажмем кнопку Add, введем наименование подразделения-группы «Отдел продаж» и нажмем кнопку OK:

Аналогично добавим другие подразделения и, таким образом, создадим структуру подразделений, изображенную на следующем рисунке:

Обратите внимание, жирным шрифтом выделены подразделения-группы, а нежирным — конечные элементы. На одном уровне иерархии могут быть как те, так и другие типы элементов.

Импорт данных в Master Data Services

Теперь необходимо импортировать данные о сотрудниках в Master Data Servises из существующего хранилища данных ( для дальнейшего сопоставления сотрудников и подразделений, и экспорта этих данных обратно в хранилище). Для загрузки данных в MDS в SQL базе данных, обеспечивающей работу Master Data Servises, существуют специальные промежуточные таблицы (Staging Tables), в которые мы можем вставить данные SQL-запросом или создать специальный SSIS-пакет, который будет импортировать новые записи о сотрудниках из хранилища данных во временные таблицы для их дальнейшей обработки в Master Data Servises. Откроем SSMS и найдем временные таблицы в базе данных Master Data Servises. Вот они:

В качестве примера импортируем произвольные 10 записей о сотрудниках из хранилища данных в промежуточную таблицу базы данных Master Data Services. Для этого выполним следующий SQL-запрос:

INSERT INTO [stg].[Employees_Leaf]
	([ImportType], [ImportStatus_ID], [BatchTag], [Code], [Name])
	SELECT TOP 10
		1, 0, N'Employees_Leaf_Batch00001', E.id, E.name
		FROM [DW].[dbo].[dimEmployees] E;

Вернемся в web-интерфейс Master Data Services и на главной странице перейдем по ссылке Intergation Management:

В открывшемся окне видим пакет Employees_Leaf_Batch00001, который был только что создан SQL-запросом. Запустим его, нажав на кнопку Start Batches:

После отработки пакета увидим вот такую информацию о статусе, времени начала и окончания выполнения, ошибках:

Управление данными в Master Data Services

Теперь давайте перейдем в режим управления данными и посмотрим, как загрузились записи о сотрудниках из промежуточной таблицы. Для этого перейдем на главную страницу web-интерфейса Master Data Services, в подразделе Information Worker Tasks выберем модель Employees и перейдем по ссылке Explorer. В открывшемся окне видим, что новые данные о сотрудниках были добавлены в Master Data Services и находятся в состоянии Waiting to be validated:

Обратите внимание, что данные о подразделении (Division) у сотрудников не заполнены. Нам необходимо для каждого сотрудника выбрать подразделение, в котором он работает и нажать на кнопку ОК:

Экспорт данных из Master Data Services

После того как данные о подразделениях и принадлежности сотрудников к подразделениям введены, необходимо импортировать их обратно в хранилище данных. Для этого необходимо создать специальные представления (Subscription Views) в MDS. Перейдем на главную страницу web-интерфейса Master Data Services, в подразделе Administrative Tasks перейдем по ссылке Intergation Management:

В открывшемся окне перейдем в меню Create Views и нажмем кнопку Add subscription view:

Заполним параметры представления для сущности «Подразделения» (Divisions) и нажмем кнопку Save:

Аналогичным образом создадим представление для сущности «Сотрудники» (Employees):

Теперь давайте разберемся, что же это за представления и как можно их использовать. На самом деле все довольно просто, представления в MDS есть ни что иное, как привычные нам представления (Views) в SQL базе данных. Откроем SSMS, и убедимся в этом:

И последнее, что остается сделать для решения поставленной задачи — это разработать SQL-скрипт или пакет SSIS, который экспортирует информацию из представлений MDS в хранилище данных.

Выводы

На реализацию данного решения было потрачено около восьми человеко-часов, что, как мне кажется, вполне адекватное время для такой задачи. В описанном решении я не использовал все возможности Master Data Services, например, остались без внимания Business Rules, которые могут использоваться для очистки данных при импорте в MDS. Однако несомненным плюсом считаю тот факт, что у заказчика в арсенале появился новый, довольно простой, но в тоже время гибкий инструмент — Master Data Services, с помощью которого в будущем могут быть решены задачи, связанные с обработкой и хранением эталонной НСИ.

Chapter 1: Introduction to Master Data Services

Master Data Services (MDS) is Microsoft’s solution for managing master data. In the past, master data management (MDM) has been a highly problematic area for database administrators. To help you understand better how MDS addresses the inherent challenges of MDM, this guide begins by explaining what MDM encompasses and how it has been handled historically. It then introduces you to MDM solutions other than MDS and explains their shortcomings so that you can fully appreciate the features and functionality of MDS, which is introduced in the latter half of this chapter.

What Is Master Data Management?

Master data management (MDM) is the management of the non-transactional data within an organization. The definition of “master” data varies by organization but can be loosely defined as the nouns that describe all business processes. These nouns might be organization-specific data, like your organization’s list of products or list of employees. Or the nouns might be common reference data provided by an external service provider or government agency, like address information or a Dunn & Bradstreet (D-U-N-S®) number. Although customer and product are the two most commonly managed domains, many businesses find value in managing additional domains, such as wells and fields at big oil companies and recipes at food manufacturers.

In most businesses, customers buy products or services. Because customer relationships are essential, each time a customer buys a product or service, the transaction is recorded. After the transaction is recorded and coded properly, the details of the transaction will never change. MDM is about managing the relationships between these static transactions, rather than the transactions themselves. For example, with a retail chain, each store has a certain group of products that it has in its inventory and a database of registered customers. The list of products available to sell and the list of customer addresses are master data. Sally buying four shirts on Friday is a transaction.

The more often this data is required for a transaction, the greater its importance to your organization. Central management of this data helps identify data discrepancies between multiple transactional systems and helps your organization run more efficiently.

Master data management is composed of the following:

• The business policies (who owns the data, where the data lives, and so forth)
• The processes (how the data is updated)
• The technological tools that facilitate these processes

By definition, all companies must be utilizing some form of MDM, although the term generally refers to the implementation of formalized processes and specifically designed tools.

History of Master Data Management

When companies began using computer applications to manage information, they had to load all pertinent data directly into each application before they could perform tasks and generate results with those applications. Over time, companies adopted more applications and had to enter the same dataset into each of them. As a response to this, developers began to employ the technique of creating master files that stored key, reusable data for use within each application. These initial master files were the first MDM systems.

Once the master data was loaded into each application, new records would be added in the application or to the master list, and the result of which was that the two sets of data were no longer in sync. Early data reconciliation efforts were time consuming and costly. Very few tools existed that could identify differences and manage duplicate records.

Enterprise resource planning (ERP) systems were then created to help combine the data in these systems and to solve the constant need for integration between systems. ERP systems were meant to be single software suites that managed standard business processes within an organization. Each module in an ERP system could leverage the common master data tables required for the associated business process. Many organizations today consider the master data tables within their ERP systems to be the “master” data within their organization.

As more organizations adopted ERP systems, they realized that using these single systems in isolation was an unrealistic solution. They then adopted multiple ERP systems, and their master data problems re-emerged. Specialized systems designed to manage the most problematic domains became major implementations in the larger organizations. Now there is a push in the industry to provide solutions that can solve MDM issues across a multitude of domains.

Overview of Other Master Data Management Solutions

All organizations must deal with their MDM issues. Whether managing domains in Excel spreadsheets or managing them with a specialized solution, a key role of the IT department in any size organization today is to provide processes for managing master data and integrating it across all systems. Various solutions other than MDS are available, but each has weaknesses that are addressed by MDS.

As explained in the following sections, ERP solutions can have several drawbacks as MDM solutions, other specialized solutions can prove to be too specialized, and often MDM solutions typically cater to only the biggest organizations. As you will read later, in MDS, Microsoft hopes to provide an adaptable solution that can cater to organizations of all sizes.

Shortcomings of ERP Systems as MDM Solutions

While ERP systems are a significant consumer of master data, using these systems by themselves to manage master data has major drawbacks. Also, the initial concept vision that a single ERP system would handle all the computing needs of an organization was shortsighted.

1. ERP Systems Are Not Specialized or Innovative: While many ERP systems contain modules for most common business processes, a specific industry need or the need for more innovative software leads businesses to implement additional systems. Integration of these new systems with current IT infrastructure and business processes becomes a major implementation cost of any new system.
2. ERP Systems Do Not Play Well with Others: When an organization merges with or acquires another organization, it inherits additional ERP systems. Since these systems are highly customized and not initially designed for easy integration, additional processes must be created to ensure that these systems are synchronized across the organization. The complexities of these software systems and the underlying data models make successful integration, or even effective synchronization, extremely difficult for large organizations. When Tyler moved across the country for work, it was impossible for his electronic bank accounts at a nationwide bank to move with him because each of the bank’s regions were on its own system. Imagine, it was easier to move the contents of a house 3,000 miles than to move a set of data and transactions that would fit on a thumb drive.
3. ERP Systems Do Not Manage Analytical Dimensions Effectively: The need for complex modeling and analysis of transactional data to determine statistics and trends has led companies to create advanced online analytical processing (OLAP) systems and associated data warehouses to go along with them. These systems have added new, complex data modeling needs that simply were not supported in legacy applications. Hierarchies and other consolidations are not natively managed by ERP systems.
4. ERP Systems Are Not Designed to Manage Attributes: Many ERP system vendors realize that users want to store additional attributes in their master tables. To accommodate this, they provide a few custom fields for customers to use as they see fit. These custom fields are overloaded with information without providing any validation of appropriate use.

Specialized Master Data Management Solutions

As you would suspect, the most common domains for management are those domains most troublesome to large organizations. Specialized systems have been created to manage these domains, with many features designed directly for them. These systems were designed to manage a single domain and don’t translate well to other master data problems. The two most common types of specialized solutions for managing master data are customer data integration and product information management.

1. Customer Data Integration: Customer data integration (CDI) solutions are designed to provide a standard view of customers across an organization. Some CDI solutions accomplish this as a single system that centralizes customer data across an enterprise. Other solutions manage the integration of multiple ERP systems and additional systems through a registry approach. These solutions manage customer IDs from multiple systems to ensure synchronization and to provide a consistent view of each customer.

A central feature of CDI solutions is the ability to identify and manage duplicate customer records. Other common features include address correction and standardization and the ability to integrate with service organizations to further enrich business-specific customer knowledge. CDI implementations tend to incorporate most systems within an enterprise and focus on a relatively small subset of attributes.

Companies with customer management problems will certainly benefit from a CDI implementation. Unfortunately, these features do not translate well to managing other domains across the enterprise such as organization or product.

2. Product Information Management: Product information management (PIM) solutions are designed for the product domain. There are fewer PIM solutions than CDI solutions, and PIM solutions tend to focus on specific industries. Most PIM solutions centralize product data management and provide integration to many distribution systems. These solutions tend to be implemented in large retailers and wholesalers that need to manage multiple sales channels for large product catalogs. Management of online catalogs and integration with standardized product channels are some specialized features of PIM solutions.

Again, these systems are highly effective to solve a narrow band of problems. These systems do not translate well to additional domains. Very few providers have solutions in both the CDI and PIM spaces.

Catering to the Titans

Historically, vendors of MDM solutions have catered to the needs of Fortune 500 companies. Until recently, the MDM market was dominated by complex and expensive applications that generate large amounts of consulting dollars for trained implementers. These companies generally have the means and the budget to pay for the consulting time and tools that comprise an MDM solution. These solutions were tied to either a specific domain or a feature set that supported one domain better than others. These solutions are expensive and are built to solve a unique set of issues in large organizations. The size and complexity of these engagements have led many of these projects to end in failure.

The MDM market’s focus on Fortune 500 companies doesn’t mean that small and midsize companies don’t face similar issues with managing their data. Any organization that’s attempting to store critical data in multiple systems or spreadsheets and having trouble determining a true version of its master data needs an MDM solution.

The costs associated with MDM solutions and the high risk of failure in self-deploying such solutions leave a large portion of the small and midsize business market underserved. Out of necessity, many of these businesses are using Excel spreadsheets or internally designed systems to manage master data. These systems typically neglect the need for security, central management, and versioning.

What is Master Data Services?

Microsoft first shipped Master Data Services with SQL Server 2008 R2. That release was largely about Microsoft entering the MDM market and beginning to shape the discussion around MDM. It also provided an opportunity for Microsoft to begin building channels for partners that both implement the solution and create additional applications on top of MDS. In effect, the first release was about getting into the game and the second is about changing the rules. In SQL Server 2012, MDS looks to redefine the MDM market, expanding the types of data stored and easing the barriers to managing it. This section provides a quick overview of the new MDS features in SQL Server 2012 as well as the features carried forward from SQL Server 2008 R2.

What Master Data Services Still Delivers

Master Data Services (MDS) provides several features that facilitate central management of master data while providing greater access to the editors and consumers of this information.

1. Domain Agnostic: MDS is not designed for a specific domain. Any data type and virtually any data schema can be supported by the MDS system. Chapter 3 discusses how to map your organization’s systems and determine how MDS might best suit your needs. Chapter 4 shows you how to create your models, entities, and attributes, which are the core MDS objects.
2. Hierarchy Management: Excel- and IT-developed applications are notoriously bad at representing hierarchical data. Specialized controls and multiple hierarchy types in MDS provide effective and flexible management of business hierarchies. Chapter 5 shows you how to work with hierarchies and collections.
3. Web-Based UI: A web-based user interface (UI) provides access to a large user base without the need for installing software on numerous machines. As intranet access becomes more portable, mobile device access can be integrated into the MDM story.
4. Transaction Logging: Even with a robust security model, it is essential to provide an audit trail of changes. MDS provides a filterable transaction log to ensure a manageable history of changes, including the ability to annotate transactions so users can explain why they made changes to the master data.
5. Data Validation: Many master data lists require additional validation to ensure certain fields are populated in specific cases. Business Rules can provide users with a proactive monitoring process to ensure data validity before these lists are used in process systems. Chapter 6 shows you how to create business rules.
6. Versioning: Many domains require snapshots of different points in time to be maintained. With MDS, each model can be versioned, which allows users to tag specific versions for subscribing systems. Chapter 7 shows you how to create versions of a model.
7. Security: The ability to control access at the entity, attribute, and record levels allows IT to empower business stewards to update data in a single centralized tool without risking unauthorized changes. Chapter 9 shows you how to implement security.

The Value Proposition

The MDS system is built to be rapidly deployed for any domain within an organization. Once deployed, all models support additional customization without complex coding or reconfiguration. The intent is to make MDM software more accessible to small and midsize businesses and to aid departments of large companies in creating solutions for themselves. While Master Data Services provides a rich web services platform for system integration and enables you to use web services to create your own custom user interface, you do not need to use web services to take advantage of what MDS has to offer. An MDS implementation can be completed successfully by business users with no programming knowledge.

Chapter 2: Installation and Configuration

The first step in any software application is to deploy the software, and Master Data Services is no exception. This chapter focuses on project size and scope, which should help you determine your needs, including whether you need any external assistance.

Determining the Initial Scope of Your Project

Before installing Master Data Services, it is important to determine your short- and medium-term goals for the application. MDS was designed as a web application to provide a simple deployment model for a wide range of organizations. This is also a good time to make sure you have the necessary information to deploy and configure the application successfully, and to determine whether your project requires external expertise. There are several questions to consider before installing MDS:

1. Which domains will I manage in MDS? While this guide addresses how to organize this data, it is important to take an initial assessment of what data must be stored to create a functional MDS solution.
2. How many attributes will I need to manage? For each domain that will be managed in MDS, it is valuable to understand the number of attributes that are relevant to the organization. A central benefit of MDS is the ability to modify your model at any time, so a complete list is not essential. A rough estimate of the number of attributes provides one of the best metrics of the scope of the management problem and can provide insight into the owners, editors, and consumers of the records to be managed.
3. How many employees will edit the data? To provide an effective long-term solution and to see the largest return on investment (ROI) for any master data management solution, it is imperative to empower the owners of the data to make changes directly within the system. Whether this is accomplished directly in the Master Data Manager web application or in some external entry system that is integrated with MDS, empowering the business owners reduces IT effort and eliminates the communication breakdowns that occur when routing data changes through IT.
4. How many employees will consume the data? Ideally, completed implementations should give everyone with a business need in the organization access to the cleanest and most accurate data at all times. In most MDM projects, reaching this ideal state is a work in progress and data consumers should be prioritized based on business need and the costs associated with providing access.
5. How many systems do I need to integrate? Depending on the size of the organization, identifying all systems that rely on a specific domain may not be feasible this early in a project. Most small and medium-sized businesses should identify all systems that consume the domain to be mastered and determine the primary owner of each system.

Based on the preliminary data that you discover from answering the preceding questions, you can determine the relative complexity of your MDM project.

The complexity of your project should affect both the scope of the implementation and the amount of ongoing effort required to maintain the MDS project. Small projects should be manageable by novice individuals with sufficient business knowledge and the aid of this guide. These projects should be functional and productive within a week’s worth of effort.

Many small projects revolve around finding a home for “homeless” data within the organization. This data is critical to regular business processes, but not important enough to be managed in any standard process system. Much of this homeless data tends to live in unmanaged Excel spreadsheets. The transition of this homeless data into MDS can provide structure and control over it. If IT personnel were previously responsible for managing changes provided by business users, they can now provide users with access to the appropriate data directly. No formal roles or duties need to be created.

A wide range of projects falls into the intermediate range. These projects can be handled internally but require resources to be fully committed to the implementation effort. Most small and medium-sized business implementations will be intermediate-sized projects. As these projects become more complex, bringing in outside expertise for the implementation should be considered seriously. Making the decision to do so does not diminish the need for internal knowledge and education. Although these consultants will aid in the implementation of the solution initially, internal staff will be needed to maintain the MDM system and processes going forward. Most of these projects can be managed in a single phase, requiring approximately 30 to 200 hours to be successfully implemented. Identification of at least one data steward within the organization is essential for long-term success.

Data steward is a common role found in MDM projects. These individuals tend to be technically savvy while still understanding the nuances of the business domains. A data steward must be a champion of data governance and must help create sustainable data maintenance processes within the organization. Many times, data stewards find themselves acting as referees in how data is maintained, caught between competing business processes and applications. Systems may maintain different rules regarding the quality and timeliness of data that must be managed by the data steward. The ability to find efficient compromises will determine how effective a data steward is for an organization.

Once projects reach a certain level of complexity, they become too large to manage in a single phase. The cost and complexity of these large projects requires engaging external expertise that can provide the guidance and resource necessary to implement enterprise-wide MDM solutions. These large projects may span multiple years and locations and breaking these projects into multiple milestones and ROI checkpoints is advisable. If these projects can be broken down into more manageable intermediate projects, lessons learned from preceding projects can be applied to later implementations.

Chapter 3: Starting an MDS Project

Before you build a model in MDS or any other MDM system, it is essential to have a basic understanding of your data and current processes. In this chapter, we provide deeper insight into what is and is not master data, and we provide some simple labels for your current business systems. Toward the end of the chapter, we discuss some tips and tricks for modeling your data, with the goal of helping you to free your enterprise from many of the data restrictions that have plagued it for so long.

How Do I Know Master Data When I See It?

Over the past year, one of the questions most commonly asked by organizations has been, “Is there a definitive line between master data and other important data in my company?” The truth is, there isn’t one and there shouldn’t be. In most companies, master data management is used only for business-critical domains like product or customer because the implementation of MDM solutions throughout these organizations would be so prohibitively expensive. If an accessible solution with a rapid time to value were available, other important domains could benefit from MDM tools as well.

We’ve already talked about a couple of the key differentiators between master data and transactional data within an enterprise, but they are worth repeating: Data that you wish to store in MDS should not relate to a single event in time because that is transactional data. In MDS, you should store information assigned to a state or that continues to have a state for a specific period. This information is master data.

Business Process Models

Before you build your model in MDS or any other MDM system, it is imperative to understand the flow of data through your enterprise. Where do new accounts, customers, or products originate? What is the process that turns a concept into a product? What is the process to onboard a new customer? How does the data flow from inception to all systems across the organization?

Business process modeling can provide decision makers with the information that they need to prioritize and plan the creation of their MDS implementation. Modeling relevant processes related to the creation and maintenance of domains you are interested in managing is the most logical place to start. Although you could employ skilled business process managers to interview employees and provide detailed designs of their findings, most projects can get by with a do-it-yourself approach.

Your first step should be a quick inventory of any existing documentation of current processes. Much of what you require might already exist. Be sure to review any existing documents with current employees in the modeled roles. It is astounding how quickly reality can diverge from the documentation given a little bit of time. The more manual the processes, the more easily these processes can be changed or abandoned.

When you conduct the interviews required to create a business process model yourself, you should complete the following two steps in sequence. Each step provides insight into the investigated domain. Make sure to record the actors, activity, and length of time required for each business process.

1. Determine how new data members are created in the organization. Make sure to investigate any alternative methods that may lead to the creation of a new member. Lead the interview with questions like “How does this system interact with other systems?” or “Are there any emergency processes?” These can lead to important discoveries. As you begin to integrate systems across organizations, it may be pertinent to ask about any automated systems that may generate new members.
2. For each of the methods discovered in the previous step, drill into any workflow or process to enrich the data member before you consider it complete and ready for use in any systems within the scope of the project. Each system that depends on this information may have different requirements, so it is best to address each system’s needs. This step is also the best time to identify the primary owner for each business process or system. Primary owners will have a vested interest in their processes and will require assurances that most if not all of their current domain needs will be addressed in any business refactoring.
3. All the information you collect will result in a diagram or set of diagrams. These diagrams will help you determine the most palatable flow of master data through the organization. You will need to review each system within the scope of the project to decide what type of role it will play for each domain’s data. Determining the role played by each system is the next step in determining how MDS fits into your organization.

System Roles

Systems can be categorized into three different roles. As different domains have significantly different origination points, it is common for the same system to perform separate roles for different domains within a company.

System of Entry

A system of entry (SOE) is a system where data is first entered into a business. These systems typically include all the users who can create new data. Generally, these are the systems that are used daily by the owners of investigated data. Many systems of entry require that rules be enforced before data can fully be entered. Oftentimes these rules force much of the accumulated information about the member record to be managed outside of the system.

Think of a new product being created. Often, the inception of a new product is scribbled on a napkin or sketched on a whiteboard. Many of the details of the product will be discussed and decided via e-mail format. It is important to capture and manage this process because creating a workflow and roles around product creation can provide many benefits like online collaboration and a tracked history of the creative process. The lack of resources to manage this process often impedes the implementation of a new product.

System of Record

A system of record (SOR) is any system within the organization that is considered the source for other systems. Typically, the enterprise resource planning (ERP) system is the system of record within an organization, but oftentimes many other systems can be considered sources for downstream systems. Many data inconsistencies are created on multiple SORs within an organization. While MDS strives to be an adequate SOE for organizations, it is imperative that many if not all the SORs are moved onto the MDS platform. This is where much of the value of MDM is derived.

Subscribing System

As your MDS implementation matures, the most common type of system should be a subscribing system. Subscribing systems are those systems that consume data from another system with no direct user changes to the managed domain. Some larger companies use an intermediate store to pass data to multiple systems. If direct changes are not made in any of these systems, all these systems can be considered downstream subscribing systems.

Mapping the Data

The next phase in the project plan is to determine data sizes and types within the MDS system. Analysis of all source systems’ main tables and their columns to determine the best data type for storing that data can provide an initial roadmap for MDS. There are two main questions that must be asked during this phase: What types of internal constraints are placed on this column? Are there any downstream constraints in the organization that require further data cleansing to be performed?

You should start the mapping process by identifying those columns within the source system that you will manage in MDS. For each of these columns, determine a rudimentary data type. In Chapter 4, we discuss the different data types available in MDS, but for now just identify data as date, number, or string.

If you identify a date field, be very careful that this field provides information across multiple systems and is a state of the mapped domain. Oftentimes, date fields are red flags that you have mistaken a system-specific field such as “entry date” for a more important field like “start date” or “discontinued date.” You should be extra wary of any column that requires the storage of a specific date or time. These fields generally signal that the information stored is most likely transactional in nature.

Columns that you identify as numeric should be reviewed as well. Is this column storing a valid state on a record? We have seen many projects in which the designers have been tempted to map in fields such as account balances or sales figures. These numbers are subject to daily change and are best handled as part of a business intelligence (BI) solution. Master Data Services does not support simple math functions or consolidations, to dissuade users from storing inappropriate data. This does not mean all numbers should not be stored. MSRP, safety stock levels, standard terms, and credit limits are all valid information for MDS. When identifying numeric columns, you should log the precision, or number of decimal places, each column will require. While many systems may not limit this on the backend, the actual required precision should be easy to determine for the primary system owner.

When mapping a source system to MDS, it may be advisable to create additional tables to store choices for certain fields. Some of these relationships are easy to see. If there is a foreign key relationship within the source system that displays options for one field to another, you should continue to preserve this relationship. For instance, if a customer has a relationship to an address table that stores addresses available to the customer, this relationship can be modeled in MDS.

Some relationships are not quite as evident. For instance, a source system may only store specific attributes as text fields, yet valid values for those text fields may be constrained by the business process. If those values are better managed in a separate table, and a foreign key relationship between those tables would be advisable, you want to highlight those relationships now. Determine if any of these relationships may be reused. For instance, within many data sources, there will be several fields that map to a choice of either Yes or No. In these cases, you may want to create a single entity to store those valid choices.

Determining What to Do with Duplicate Records

Data quality is a major concern for corporations, and the identification and management of duplicate records is a central task in the effort to ensure data quality. Duplicate records exist within organizations for a variety of reasons. One of the biggest reasons employees duplicate records is that they are unaware that records already exist in a separate system, because the systems are not integrated effectively. These records should be merged wherever possible.

Some companies intentionally duplicate records within systems for a specific purpose, typically to work around limitations within current applications to support necessary business processes. In most cases, these duplicates must remain within the business application to continue to provide the workaround they were designed for. Systems that required duplicate member records should never be considered for Systems of Record. When connecting these subscribing systems to the MDS repository, master data attributes should be updated in all downstream attributes.

Determining Which Attributes to Manage

To determine whether an attribute should be managed in MDS, data stewards must decide on the nature of the attribute. Is this a state of the domain? Is this information useful for multiple systems? All numeric and date fields should be evaluated closely. Tracking information should not be managed if it is specific to an application in the organization. Calculated values or balances should not be managed in an MDS system because these values will change over time. Make sure that attributes that store a product’s age are not managed as such but that you manage the product’s date of creation instead.

Summary

It is important to understand the flow of data within an organization before implementing an MDM system. Any system that does not match the natural flow of data through an organization is doomed to fail. Analysis of business processes can provide insight into the flow of related data through the enterprise. All systems within an organization can be classified as either systems of entry, systems of record, or subscribing systems.

Many systems within the organization will have data-cleansing needs. It is important to determine what remediation will be acceptable if duplicate records are found. If history must be maintained for duplicate members, a mapping between the MDS source and the system should be maintained. In the next chapter, we discuss how to create our models in MDS.

Chapter 4: Creating Your Model

In this chapter, we discuss the creation and customization of MDS models. In MDS, models are the central work surface that will be exposed to the master data editors and consumers once your project goes live. The success of your project will depend mainly on how well you design your model.

We begin with a discussion of modeling concepts. Then we show you how to deploy pre-built models.

MDS Modeling Concepts

Master Data Services is made up of a relatively simple group of concepts. These concepts are encapsulated as data containers and services exposed in MDS. While these concepts are simple to understand by themselves, it is the varied and complex data schemas that these concepts support that provide both the power and complexity of MDS. Before we delve into the implementation of the model objects, let’s review general definitions of these MDS concepts. Each of these concepts is described in more detail later in the chapter.

1. Models: Models are the highest level of organization within MDS. Models are nothing but containers of related entities. Only entities within the same model can be related within MDS. Models are the first concept discussed in this chapter.
2. Entities: Entities are the base containers for data in MDS. In their simplest form, entities can be thought of as tables in a database. Users control the attributes (columns) that are managed for each entity. If explicit hierarchies are enabled for an entity, the entity becomes far more complex, managing parent members and their consolidations as well as collections, their attributes, and the members associated with those collections. Most of this chapter is devoted to discussing how to create the structures of entities.
3. Members: Members are the records that populate all the entities created in MDS. Members can be either leaf or consolidated. Leaf members are the primary members of an entity. If an entity is enabled for explicit hierarchies and collections, then consolidated members can be created, and can have their own attributes.
4. Attributes: Attributes describe members. Attributes can be loosely thought of as columns in a table. Entities contain members and their attribute values. Attributes can be free-form or domain-based.
5. Domain-based Attributes: Domain-based attributes are attributes in which the available values are restricted to the members stored in a related entity. This is like selecting from a predefined list, but in MDS all lists are entities themselves.
6. Hierarchies: Hierarchies are consolidations or groupings of members that aid in reporting and analysis. There are two management types for hierarchies in MDS: explicit and derived. Hierarchies enforce rules for member inclusion to ensure consolidations do not lose or double count values in connected applications. Hierarchies will be discussed in detail in Chapter 5.
7. Collections: Collections provide member grouping flexibility that is not supported in hierarchies.

Building a Model

Master Data Services begins as a blank canvas, allowing you to create your data models within the product however you choose. The model structure is created in the System Administration functional area of the Master Data Manager web application, or by using the web services. As you create the structure, you can open the Explorer functional area of the UI to see the results of your work. The Explorer functional area is where users will go daily to manage their master data.

Opening the System Administration area of the Master Data Manager web UI for the first time can be a daunting experience. In the latest release, the System Administration will default to the model creation screen if you have not loaded any models. When you first create an MDS database, only the Metadata model is created. Figure 4-1 below shows this first model if you open in the Model View screen.  This is the only system model within Master Data Services and has been marked for deprecation in a future release. While this model can still be accessed from System Administration or Explorer as its own model, all access to the Metadata features from your own models has been eliminated through the UI. Access to these features will need to be completed through the web services for Metadata.

Notice that the menu bar and page title of the System Administration functional area both contain the word “Explorer.” If you go back to the home page, you’ll see that there is an Explorer functional area, where you will manage master data. This is not the same as the System Administration area, where you use the Explorer page to manage your model structure. Most of the time, when we mention Explorer, we are referring to the functional area that you access from the home page of the Master Data Services Web UI.

If you are a new user of MDS, you have two avenues for creating your first useful model within the product. You can load a sample model deployment package into your database and modify the model from there, or you can build your model from scratch.

Starting with a predefined model can be the easiest way to get started with SQL Server MDS. MDS also includes three sample models in the installation. These samples are very basic Customer, Product, and Account models, located in Program FilesMicrosoft SQL Server110Master Data ServicesSamplesPackages. You can look at them to get a better idea of some of the more common features of the application.

While these models provide some data and attributes and support the documentation provided in Microsoft Books Online for SQL Server 2012, they are relatively limited in their functionality and do not provide adequate coverage for the exercises listed in this book. However, these models include sample data, hierarchies, business rules, and versioning, so you can see what the result of all your upcoming work might look like.

Deploying a Model

A model deployment package is an XML file saved with a .pkg extension. It includes the model structure, the business rules (Chapter 6), and version flags (Chapter 7) and Subscription views (Chapter 10). It does not include file attributes (Chapter 4), and user and group permissions (Chapter 9). Model packages can contain the data from a version of the model when created from the ModelDeploy.exe command-line executable. Only the model structure can be created or deployed from the web UI in the SQL Server 2012 version of Master Data Services. The new utility and the limitations on data in the UI were added in response due to the inability to support all model sizes through the web services.

Model deployment in MDS was designed to provide organizations with two very important capabilities related to managing data models in MDS. The first is the capability to develop, test, and deploy a model within three separate implementations of MDS, while limiting the number of objects that need to be re-created in each environment. This enables IT management to roll out changes together and to ensure that the production environment remains operational throughout the process.

The second capability enables organizations to share their model schemas with others. There are several efforts across a host of industries to standardize object models. Previously, most organizations would develop data models in a vacuum, creating schemas that solved their current problem most efficiently. They integrated only those applications owned by the organization and central to the solution. IT departments at all their competitors were doing the same. This led to a wide variety of data models among competitors in the same industry. Some organizations built data models that were flexible, stable, and scalable, but many did not. When consolidation within an industry occurred, acquiring companies discovered similar business processes were hard to integrate due to the vastly different data models that had been developed in isolation.

The following are  advantages to employing common data models within an industry:

1. Benefit of experience: Civilization wouldn’t get very far if each new generation needed to reinvent the wheel. By leveraging a common data model developed by industry experts, companies can avoid issues that they might otherwise experience personally.
2. Standardization: Standards help companies and software providers by limiting the amount of variation that must be managed. Standards allow companies to better manage acquisitions and new integration projects. Software can focus on enhancing business value as opposed to compatibility.

MDS supports these efforts in two ways. By not enforcing its own model, MDS ensures there is one less data model to be managed. Model deployment empowers industry leaders to quickly build master data–centric versions of these industry models, providing efficient deployment across their industry. Any customization that needs be made to these standardized models is still supported in MDS.

About Models

As discussed previously, models are the highest level of organization within Master Data Services. Models are nothing but containers of related entities. Only entities within the same model can be related within MDS. This supports a simplified versioning model where all entities within a model are versioned simultaneously. Although many models revolve around a single domain such as customer or product, this is not a requirement. You can combine major entities into a single model if this makes business sense.

When determining whether to include an entity in a model, there are several factors to consider:

1. Do the entities in the model have a relationship to one another? If so, is this relationship master data? Many entities have relationships that are transactional in nature. Only those relationships that change over time and are not discrete instances should be managed in the MDS system.
2. Do the entities change on similar schedules? If the two entities have a natural affinity to be versioned at separate times, you may need to manage them in separate models.
3. Does an entity only relate to a subset of another entity? If only a subset of the members participates in the relationship between the two entities, ensuring accurate selection may be compromised. Business rules can be used to ensure only the valid members are selected; business rules will be discussed in greater detail in Chapter 6.

About Entities

In MDS, all data that is managed by the system is stored in entities. Entities can be loosely thought of as tables in SQL. The data within entities are called members.

MDS’s entities can also support explicit hierarchies. Once explicit hierarchies have been enabled for an entity, an entity becomes far more than a single table. The entity can then support parent and collection members, as well as hierarchy and collection relationships. Hierarchies and collections are discussed in detail in Chapter 5.

About Members

Members are the individual records stored in Master Data Services. Members are uniquely identified by the required Code attribute. There are two types of members in MDS: leaf members and consolidated members.

Leaf members are the most granular level of records in an entity and usually represent physical objects within your business. For example, in a Product entity, a leaf member might be Men’s Shirt #602. A leaf member in an Employee entity might be John Smith. A leaf member in a Warehouse entity might be Warehouse-98101.

One notable exception to this rule of thumb applies to any entities managed within the Finance domain. When managing entities associated with Finance, leaf members typically represent those low-level identifiers that transactions can be coded to. If transactions are coded with a store, account, employee, and product, you can be assured that the available values for each of these attributes should be stored as leaf member records in the associated entities.

Consolidated members are used only in explicit hierarchies and will be discussed in more detail in Chapter 5.

About Attributes

All entities within Master Data Services can be enriched by the creation of additional attributes. MDS supports a subset of the attributes supported in SQL Server. MDS supports four specific types of free-form attributes for leaf and consolidated entity members: text, DateTime, number, and link. Text and link are essentially string fields, with link providing one-click support for hypertext links. Number supports as many as seven decimals, and DateTime allows you to specify the mask for how the data will be input.

To relate two entities and ensure that values are constrained to specific values, MDS gives users the ability to create domain-based attributes. These attributes limit available values to the list of active members within the related entity.

The last type of attribute available to entities is the file attribute. Within a master data entity, it may be advisable to manage some files associated with each member. For instance, there may be instruction documents, specifications, blueprints, or photos that need to be associated with a Product entity within an organization. However, the file attribute has several limitations that make it less attractive to use for file management than SharePoint. First, to limit the database sizes of MDS, file attributes will not be versioned. Only the last loaded file will be available within the MDS system. The transaction log will display information related to new files that have been loaded, but these will not be reversible.

About Attribute Groups

Master Data Services can contain every attribute associated with an entity in a business. Because of this, some entities can become unwieldy when viewed as a single table in the web UI.

In a large organization, it is common for a major entity to contain over 400 attributes. Very few, if any, system users are interested in viewing all attributes simultaneously. Most users are interested in viewing only specific, related attributes at any one time. MDS provides the ability to group attributes into multiple tabs in the Master Data Manager web application. MDS calls these groupings attribute groups. Any attribute can be added to any attribute group.

Typically attribute groups are created for each role consuming data within an entity. Different functional areas of the organization will be concerned with different groups of attributes for each entity stored in the application. Access to attribute groups can be managed by applying security for different users or groups at the attribute group level. This will be covered in more detail in Chapter 9.

Chapter 5: Working with Hierarchies and Collections

Businesses are not built only on lists; they require more complex structures for data. Dollars, units, and hours must all be calculated to produce consolidated views of a business. For many years, companies have been using business intelligence (BI) applications to better understand their businesses and discover opportunities for both cost savings and revenue growth. At the heart of these applications are the hierarchies that make consolidations possible.

When managing consolidations within a business, it is imperative that all values are accounted for only once. To ensure this, hierarchies in Master Data Services enforce that all leaf members have only one parent. This limits your ability to manage many-to-many relationships within MDS and display these relationships hierarchically.

In this chapter, we review the two primary types of hierarchies supported in MDS.

Ragged vs. Level-Based Hierarchies

When you’re working with hierarchies, it is important to understand some common terms used across applications. Any hierarchy can be either ragged or level-based. Ragged describes hierarchies that support leaf members at multiple levels. A hierarchy is level based if leaf members always exist at the same level, regardless of the number of levels within the hierarchy.

Some business applications do not support ragged hierarchies, so it is important to know the limitations of downstream systems before you create hierarchies in MDS.

Derived Hierarchies

In MDS, derived hierarchies provide the ability to highlight preexisting data relationships within entities and display them hierarchically. Derived hierarchies are based on the pre-existing data relationships within MDS. In SQL Server 2012, derived hierarchies have been expanded to allow more complex relationships to be managed effectively. As the MDS product advances, derived hierarchies will continue to evolve to handle all data relationships, and explicit hierarchies will lose importance. Derived hierarchies are always level-based. This means that every level within the hierarchy corresponds to a specific domain-based attribute within the entity–attribute chain (see Figure 6-1).

Explicit Hierarchies

Explicit hierarchies are multi-level hierarchies with very few restrictions. Explicit hierarchies are managed as name–value pairs, with consolidated members containing other consolidated or leaf members. Explicit hierarchies are created for one entity at a time. Derived hierarchies, in contrast, require multiple entities.

Consolidated members are almost always theoretical items, whereas leaf members represent physical items. Like leaf members, consolidated members can have attributes assigned to them. In the MDS database, a separate table exists to manage consolidated members and their associated attributes. Consolidated members are available only if an entity is enabled for explicit hierarchies. Each consolidated member can be associated with only a single hierarchy no matter how many explicit hierarchies have been created for the entity.

Derived vs. Explicit: Which Hierarchy Is Best?

Derived hierarchies are determined by the structure of the model, and changes to the structure are rare. To illustrate the value of this rigor, consider the following scenario that organizations commonly encounter:

A fictional company devises a regional hierarchy to manage sales. As with most of these hierarchies, this hierarchy begins as a level-based hierarchy, with each level within the hierarchy corresponding to a distinct type of attribute. To store the hierarchy, IT uses the parent-child format from its analysis system.

Over time, this hierarchy is modified by mid-level managers to help them manage their divisions better. Changes are not centrally managed, and new levels are added monthly. John is the manager of the Western region of the company. John has two managers who split duties managing the Southwest division for him: Bill and Margaret. Since Bill and Margaret are splitting the Southwest, John needs to split divisional data in the company’s reports to measure this divisional structure. To do this, he creates two additional nodes within the hierarchy for the Southwest division, SWB (Southwest Bill) and SWM (Southwest Margaret).

Over time, Bill and Margaret move on to other jobs, either moving up in the company or on to other opportunities. Yet the Southwest division continues to be split into SWM and SWB. This is not an isolated occurrence, as many managers make isolated changes to the hierarchy structure. Some of these changes may not be warranted, and others may have a short shelf life. Soon the initial hierarchy is unrecognizable and difficult to manage. The ability to provide managers with rigid derived hierarchies, malleable explicit hierarchies, and focused collections allows BI professionals to provide the perfect tool for each scenario.

Hierarchy Type	Description
Derived	Uses multiple entities. Based on domain-based attribute relationships. Level based. Hierarchy structure is designed in System Administration. Hierarchy members are updated in Explorer.
Explicit	Uses one entity only. Consolidated members are used to group other consolidated and leaf members. Ragged. Entity must be enabled for explicit hierarchies in System Administration. Hierarchy structure is designed in Explorer. Hierarchy members are maintained and updated in Explorer.

Chapter 6: Using Business Rules

Managing data is not just about storing the data in a customized entity; it is also about ensuring that the data is both accurate and complete. Master Data Services provides business rules to achieve this aim. In this chapter, we review business rules.

Business Rules Overview

In many organizations, no matter what the size, the business owners responsible for managing the master data don’t have the technical knowledge needed to implement the related processes. If the business owners don’t know how to use SQL Server or how to code business rule engines, they can be left at the mercy of their IT departments. At the same time, because the IT department has the technical know-how, the burden often falls on them to learn business domains they don’t necessarily need to know. MDS strives to simplify the creation of business rules to empower business users to manage their own data quality.

In MDS, business rules are declarative expressions that govern the conduct of business processes. These expressions are compiled into stored procedures that perform the task of validating the data. The area of the Master Data Manager web application used for business rules was created to empower business users to write relatively complex business rules without knowledge of Transact-SQL.

Business Rule Structure

Business rules are IF…THEN statements. IF certain conditions evaluate to be true, THEN perform specific actions. Conditions can be combined using either AND or OR logical operators. These operators can be used to create extremely complex business rules. You can use as many as seven levels for complex conditioning.

Although you can create complex rules, there are some real benefits in creating multiple, more granular rules. You should consider breaking any rule that uses the logical OR operator in multiple rules. This makes rules easier for other users to read and understand. Multiple rules also allow you to exclude specific rules and to provide more granular notifications. Rules built with the AND operator must be kept together to function as a unit.

Business rules are always applied to attribute values. For example, if an attribute value is blank, you might want to send an e-mail to notify someone or set the value to Pending. Or you might want to update the value of one attribute based on the value of another attribute. Because business rules are applied to attribute values, you should determine which attributes you’re going to work with before you start creating rules. Each time you create a rule, you must select the model, entity, and type of member that contains the attribute you’re looking for.

Chapter 7: Creating Versions of Data

After you understand the workflow for adding and modifying the data stored in MDS, you must learn how to manage that data over time. For each model within MDS, a historical record of the data can be stored. These data snapshots are called versions.

Each time you create a version of a model, the data for all the entities within the individual model are versioned at the same time. Only the data is stored; any changes to the structure of the model affect all versions and can create unintended consequences.

 Version flags are another important component of versioning in MDS. By assigning flags to versions, integration with other systems can be better managed.

There were no significant changes to versioning in the SQL Server 2012 release. Administrators should continue to use versions to manage snapshots of their models over time.

Versions Overview

To manage data within your organization effectively, you may be required to create versions of the data stored within a model. In MDS, the data in all entities within each model is versioned simultaneously. A benefit of this design is that you can manage relationships between entities without worrying about time and version. A side effect of this design is that entities in different models cannot interact with one another.

There is often a natural cadence to many data domains. This cadence can help define which entities should be managed in the same model. For example, in many organizations, the accounting department manages structural changes monthly. As each month ends, everyone in the accounting department goes through standard routines to ensure that the month’s books can be closed properly. Any entities related to this process—accounts, divisions, departments, or other internal business entities that are central to this process—should be managed together, and versioned in concert with the month-end process.

In another example, an organization may release products quarterly. All entities central to the product development process would follow this quarterly versioning scheme. Other entities may not require a versioning scheme at all.

Versions provide several benefits to the data management process:

1. Complete model history for a specific point in time: MDS model versions can be committed to ensuring that an exact record of a model’s data can be stored for later review. These committed versions can provide a portion of the required audit trail for new, more rigorous compliance requirements.
2. Limited access during sensitive processes: When performing certain processes, like validating the entire model or loading large numbers of records, it may be prudent to restrict access to the model by locking it.
3. Additional version copies for analysis: Additional versions can be created outside of the standard cadence for a variety of purposes. These versions can be used to examine new hierarchy configurations or potential acquisitions without affecting the current regular processes.

Changing the Structure of Your Model

MDS does not version metadata changes. Any changes to the model structure affect all open and committed versions. If an attribute or entity is deleted, for example, all history for that attribute or entity is lost from all versions forever. When you need to maintain historic data, we suggest that you use security permissions to hide attributes or entities instead of deleting them. You can also hide attributes by setting the display width to zero or by not adding them to attribute groups.

When you add an attribute or entity, the model structure is updated in all versions as well. You can add the corresponding data to any version of the model; if you add data to a later version, the structure exists in the earlier versions, but the data does not.

Committing Versions

Sometimes users and downstream systems need to be certain that all data has been validated and reviewed. Because MDS allows incomplete members to be added to the system and encourages users to manage the data creation and correction workflow from within MDS entities, it may not be reliable for external production systems to use open or locked versions. Only committed versions ensure that all members in every entity within the model have passed all business rules successfully. Once a version is committed, no additional changes can be made to the data, and the status of the version cannot be changed.

Chapter 8: SQL Server 2012 MDS Add-In for Excel

More master data is stored in Excel than in any other application on the planet. Some smaller companies use this solution because it is cost effective and easy to use. Others use it to store their “homeless” data in worksheets because it is the tool they are most comfortable with. Even the largest organizations with the greatest, most well-designed MDM systems available struggle with the propagation of master data in Excel.

In SQL Server 2012, the Master Data Services team has attempted to harness the power of Excel to give users all the security, audit, and management features of MDS in a package that Information Workers are most comfortable with. In this chapter we will explore working with MDS data in Excel and the benefits of doing so.

Using the combination of Excel and MDS as a data management tool can provide organizations with significant advantages. Excel is a natural platform for staging data from a variety of sources. Its built-in data functionality can be used to parse and cleanse data before loading the data into MDS. The developer interfaces, whether code, macros, or formulas, can provide additional custom automation in a rapid fashion, without voiding any support in the MDS solution.

Many of Excel’s limitations are also addressed by using it in conjunction with Master Data Services. Data sharing, security, and transaction logging have always posed problems that spreadsheet designers have struggled to solve. One of the biggest concerns with storing data in Excel is the lack of security and central management. Using the MDS Add-In for Excel can alleviate many of these concerns.

Working with MDS Data in Excel

The Master Data Service Add-In for Excel will allow you to load entity data into a worksheet as an Excel formatted table. An Excel table is really a perfect environment to review and manage reasonable amounts of data from Master Data Services. Filtering, sorting, and formula functions can be used to review the data in a friendly format without destroying the ability to write back to the server. Data can also be taken “‘offline”’ and modified without connectivity and then published back to MDS when connectivity has been restored.

Summary

The MDS Add-in for Excel really changes the game in MDM, providing a well-known and efficient interface for all members of the enterprise to load and manage their data. The intuitive interface can create new entities, thereby eliminating or reducing costly modeling phases of MDM projects. In Chapter 11, the new modeling process will be discussed in greater detail.

Chapter 9: Implementing Security

By the time you are ready to implement security, most of the functionality in MDS has been enabled. Models have been built and refined, rules have been written, and the application has been integrated into your organization.

To deploy MDS, everyone needs access to the application. While transaction management can provide some accountability, limiting access based on needs and roles ensures that users are unable to change data without authorization. Limiting the number of models and functions available to users can also help them focus more quickly on the data they need. The ability to provide specific data access within the MDS system is the single most important feature of the application. The focused security access provided by MDS empowers business users and frees the IT organization to manage the overall process, not maintain the individual data points.

In this chapter we provide an overview of the security framework in Master Data Services. We discuss the process of managing user and group permissions and explain the highly customized access that can be granted.

Security Overview

MDS security is broken into three distinct areas:

1. Functional security: Corresponds to each of the five functional areas displayed on the homepage of the Master Data Manager web application. Most users need access only to the Explorer functional area of the web UI. All other functional areas are available only to administrators. Users must have permissions to the Explorer functional area to use the MDS Add-in for Microsoft Excel.
2. Model object security: Provides access control to attributes, based on the model objects within the MDS architecture. For example, you can set permissions on an entity, which determines permissions for all attributes for the entity. Or you can set permissions on a single attribute, which affects that attribute only. Model object security is required; without it, a user cannot perform any tasks in MDS.
3. Hierarchy member security: Provides the most granular level of security and is optional. It is used to grant access to specific members, based on their location in a hierarchy.

Model object permissions (which apply to attributes) and hierarchy member permissions (which apply to members) are combined to determine the exact level of security for every attribute value. Figure 11-1 shows how attribute and member permissions intersect so that security can be determined for an individual attribute value.

• Hierarchy member permissions determine which members a user can read or update.
• Model object permissions determine which attributes a user can read or update.

Security Changes in SQL Server 2012

Security in the prior release of MDS was a double-edged sword. The ability to set security on multiple hierarchies simultaneously provided significant flexibility and complexity. But with so many pieces working together, it was easy for administrators to lose sight of what the effective permissions were for each user. A key effort for the second release of Master Data Services was to simplify the security model. In SQL Server 2012, Attribute Groups are no longer securable; you must set attribute security explicitly on each attribute. Direct hierarchy security has been removed from the security model.

Users and Groups

MDS relies on Active Directory for user and group authentication. While all security permissions are stored in the MDS database, no passwords or group memberships are managed in MDS.

To keep security as simple as possible, you should do the following:

1. Create either Active Directory or local groups and add either Active Directory or local users to those groups.
2. Assign security in MDS to these groups, rather than to individual users.
3. If you decide to assign security to a user, don’t also assign security to groups that the user is a member of. While MDS has rules for determining which permissions take effect, security becomes more complicated when you do this.

Before you begin working with security, you should take some time to determine which groups your users might be part of, and which attributes or members those groups might need access to. The following list should give you a general idea of the groups that you might use.

1. Product Administrators: This group will have permission to all functional areas and to take any action available for the Product model. This includes changing the model structure and modifying all members, among many other things.
2. Finance Administrators: This group will have the same type of permission as the Product Administrators, but for the Finance model.
3. Purchasing: This group will be able to update the Cost attribute for all products.
4. Warehouse: This group will be able to update the attributes on the Logistics tab only.
5. Logistics: This group will be able to update the Safety Stock Level, Reorder Point, and Discontinued attributes. All other attributes will be read-only.

All these groups will be able to access MDS after being assigned functional area and model object permissions. You might also assign hierarchy member permissions to a few select members of the Purchasing group. These users should be able to view products for only the manufacturers they are responsible for.

Even though we recommend that you assign permissions to groups, for the rest of this chapter we’ll refer to permissions that users receive, because groups wouldn’t mean anything if users weren’t in them. They are ultimately the ones who will access the data.

Administrators

In MDS there are two types of administrators:

1. The system administrator you specified in the Administrator Account field when creating the MDS database. This user has full control over all models and data. When new models are created, this user automatically has access. This user also has permission to access all functional areas of the web UI. To change this user, you must run a stored procedure in the database.
2. A model administrator who is manually assigned Update permission to a model, and no other model object or hierarchy member permissions. This user has full control over the model he or she has Update permission to. Model administrators do not necessarily have permission to access all functional areas of the application. For example, a model administrator might be responsible for integration only. However, they can perform all tasks in whichever functional area they have permission to access. All model administrators with access to the User and Group Permissions functional area can assign permissions for other users. Keep this in mind when you’re assigning someone permission to update a model.

Functional Area Permissions

Functional area security determines which of the five functional areas on the Master Data Manager home page a user or group can access. Security at this level is either permitted or denied. If permission to access a specific functional area is denied, the area is not displayed in the web UI and related web service operations are denied.

The Explorer functional area is where users manage data. When you assign access to Explorer, you must assign access to specific model objects, so the user gets access to a specific set of data. When you assign access to any of the other functional areas, the user must have access to the entire model (on the Models tab) in order to use those areas. Without this access, the user can open the functional areas, but no models are displayed. This is how MDS handles permission for Administrators.

Model Object Permissions

Model object permissions, assigned on the Models tab, are required. Users cannot view any models or data if they do not have model object permissions.

When you give users permission to model objects, you are giving them the ability to edit attributes for members, based on the object you select. For example, if you set Update permission on the Product entity, all attributes for all Product members (leaf and consolidated) can be updated. If you set Update on the Color attribute of the Product entity, only the Color attribute can be updated.

In addition to giving a user the ability to update attribute values, if you assign Update model object permissions to a model, entity, or to the word “Leaf” or “Consolidated,” the user can also create and delete members. If permissions are assigned at a lower level, the user cannot create and delete members.

Note: Permissions automatically cascade to all child objects within the current model unless permissions are assigned at a lower level. You do not need to explicitly set permission on every object.

If you assign Update model object permissions to the model only, the user is an administrator, which means he or she can access the model in functional areas other than just Explorer if given to access to that functional area.

Quick Facts About Model Object Permissions

Things to remember about model object permissions include the following:

1. They are required.
2. They determine which attributes a user can view or update (as opposed to which members).
3. They apply to all lower-level objects unless another permission is explicitly assigned.
4. Update permission to Leaf or Consolidated model objects and above gives users the ability to create and delete members.
5. Update permission at just the model level makes the user an administrator.

Best Practice for Model Object Permissions

There are many different model objects you can assign permission to. Giving access to specific models, entities, or attributes should fulfill most of your security needs. In SQL Server 2012, most complicated security configurations have been simplified with the removal of attribute group and hierarchy object security.

Chapter 10: Publishing Data to External Systems

Although the ability to manage data is an important feature of Master Data Services, the ability to export data to other systems in the enterprise is equally if not more important. MDS has simplified the export process by providing subscription views that you can create on any entity or derived hierarchy object within the MDS system.

In this chapter, we describe the different types of the export views that can be created and the format of each of these views. While Master Data Services provides no direct Extract, Transform, and Load (ETL) features of any kind, the Integration Management functional area of the Master Data Manager web application (or “web UI”) can facilitate your integration processes by providing a wide variety of views to assist in the loading of downstream systems.

Exporting Data to Subscribing Systems

Master Data Services can provide value for a project that keeps the data locked in its entities by providing processes around the data management; however, to meet the operational or analytical needs of a master data management project, organizations need to transport stored data downstream to subscribing systems. To insulate organizations from the complex object model necessary to manage performance and the customization necessary within MDS, the developers created a subscription view layer.

You can create subscription views within the Integration Management functional area of the web UI, or by using the web service. The web service refers to these views as export views, but we’ll use “subscription views” and “export views” interchangeably.

Subscription View Formats

The following table shows the available subscription view formats. When you create a subscription view, you must choose which format you want to use. There are two major types of subscription views within MDS: attribute views and hierarchy views. Attribute views display the data stored for leaf, consolidated, or collection members in an easily consumable tabular view. Hierarchy views provide relationship data for all types of relationships in MDS, whether explicit or derived hierarchies or collection members. Collection member relationships can only be displayed in a parent-child format. An additional view is available for derived and explicit hierarchies. It contains a row for each member and the parentage all the way to the top consolidation in the hierarchy; this view is considered level-based.

View Format	Description of View
Leaf attributes	Shows leaf members and their associated attribute values
Consolidated attributes	Shows consolidated members and their associated attribute values
Collection attributes	Shows collection members and their associated attribute values
Collections	Shows collections and their members in a parent-child format
Explicit parent child	Shows explicit hierarchy structures for an entity in a parent-child format
Explicit levels	For the entity, shows all members in all explicit hierarchies in a level-based format
Derived parent child	Shows all derived hierarchy members in a parent-child format
Derived levels	Shows all derived hierarchy members in a level-based format

Common View Architecture

Many of the columns in Master Data Services’ subscription views are identical across view types. The columns provide either context for the data displayed in the view or additional system information for the records contained in the view. Subscribing systems can use this information to update a subset of records based on validation status or last updated statistics.

Column	Description
VersionName	The version name for the current version being displayed. If this is a view based on the version name, this value will never change.
VersionNumber	The version number for the current version being displayed. If this is a view built on the version name, this value will never change.
VersionFlag	The current version flag for the displayed version. If a view is based on a version flag, then this column will remain constant and the VersionName and VersionNumber column values will change as the version flag is moved between versions.
EnterDateTime	The date and time the member was first entered into MDS.
EnterUserName	The user who initially entered the member into MDS.
EnterVersionNumber	The initial version this member was created in.
LastChgUserName	The user who last updated this member in MDS.
LastChgVersionNumber	The number of the version this member was last changed in.
ValidationStatus	The current validation status for the member. Validation status only exists for leaf, consolidated, and collection attribute views, because there is no validation stored for relationship members.

Chapter 11: Advanced Modeling

Modeling is more art than science. You must understand not only your datasets and how they interact, but you also need to understand the related systems, people and processes.

Master Data Services is not a data warehouse or a reporting tool. It is a data management tool. This is a difficult distinction for many to understand. Master Data Services should be used to manage and store lists for the business. Security can create boundaries for multiple users. Business rules notify users of issues with the data, while the Web UI, Add-In for Excel, and Web Service give the entire organization access to work on the same datasets. All these features are designed around managing data changes.

Once data is being managed correctly in MDS, ETL processes can send this data into data marts and warehouses to provide the reports demanded by the business. These systems can focus on the reporting structures, disseminating the information without concern for the data editing and quality processes. In this chapter, we will try to provide insight into some of the more common complex problems that we have seen.

Common Modeling Mistakes

MDS is best built from the top down, not the bottom up. As we discussed in Chapter 3, the first step in managing data is to determine the major entities that need to be managed. Unfortunately, many modelers then move directly to the downstream systems to build their models, deriving all columns from these pre-existing systems. This section discusses some significant pitfalls with this approach to be aware of.

Building Outside Limitations into MDS

Whether a system is purchased or created, the data model of that system was developed through a series of compromises. If you were building a system to manage payroll, how many attributes would you add for each employee? Would you add information about certifications they hold? Each system is built to solve a business need. Some of these systems will attempt to solve multiple needs or allow for minimal customization. If you focus on a single system, you run the risk of bringing these compromises into MDS.

A couple of common compromises seen in the field are perpetuating overloaded fields into your MDM system and propagating system column names. Either of these issues can blunt the effectiveness of your MDS implementation, limiting usability for business users.

Manage Overloaded Fields

Many systems have tables that need to have a single column they use as the primary key. To ensure this column is always unique, values in this column may be a combination of three or more distinct values that have been pasted together, typically as a fixed-length combination. Do not bring this as a single column into MDS. Allow users to manage these distinct values separately. If visibility of this value is required, use business rules to concatenate the obscure codes into this overloaded value for the external systems.

Provide Easily Recognizable Names

Most systems try to use standard naming conventions and technical shorthand to name tables and fields within the database. Ensure that you adapt these names to be easily readable by your end users. This might apply to allowable values within some fields as well. Create an entity and provide useful names to each of these unique values.

Trouble Identifying Common Attributes

Each system within your enterprise comes pre-configured with some built-in nomenclature and culture. If you look at these systems at the database level, where table and column names have been abbreviated, determining meaning and use can be difficult. This can lead to confusion about how different systems or departments manage similar attributes. While these architecture diagrams can help lead the conversation, working directly with the business users is the best way to ensure you accurately model your business.

Engineering Dead Scenarios

When we rely on the existing structures of systems within the organization, we may find ourselves modeling scenarios the business does not require. This can be due to changes in the business structure over time or an incomplete understanding of the problem when the system was implemented. It is important to take advantage of the current mindshare of the business users and to build your MDS model as the business intends.

Rapid Model Development

With the advent of the MDS Add-In for Excel, Master Data Services has revolutionized the modeling process. In the past, there was a gulf between modelers who read and create data models and the rest of the business. The best way to bridge this gulf is to design a model, review it with the users, receive feedback, and repeat the process. With competing products, this process usually takes weeks or months for each iteration, whereas with the Add-In for Excel, a team can create a fully populated model in a few days.

Excel is a great intermediate location for data from any source. Many of the datasets currently managed in your organization already exist within Excel. For any data that does not exist in an Excel sheet, you can use the existing data features in Excel to load the data into a workbook. Once the data is loaded into Excel, you can create a new entity by using the Create Entity Wizard.

Managing Slowly Changing Dimensions

One of the most common concerns when helping data stewards model data in MDS is how to handle history within the system. In most cases, these concerns are not drawn from operational systems, as these systems typically focus on the processing of the current state. Analytic systems such as data warehouses or data marts need to store or report information based on changes over time. Most modeling discussions quickly turn to managing these dimensions changes in MDS. This is the wrong way to look at it. The role of MDS is to create the changes, not to store them for reporting purposes. We need to look at how to leverage the existing tools in MDS to send these changes to the reporting systems that require them.

Conclusion: Superseding MDS with Master Data Management 

Whether you have dabbled with SQL Server Master Data Service (MDS) for your master data needs or you’ve gone deep  with a full production solution, there are reasons you should be concerned that your investment in MDS is at risk and that you  may be limiting ourself unnecessarily.  

MDS was a fine tool for building small–scale, departmental data management solutions. However, once you decide to get serious about master data management, you need real tools and a vendor to partner with, like Profisee, that brings to the table a forward-looking plan and migration strategy for moving beyond MDS. 

Profisee offers a unique approach to MDM that helps organizations of all size make their data management easy, accurate and scalable.

Schedule a demo today to see first-hand how the Profisee MDM platform can solve your unique business challenges. 

Successful enterprises can grow organically or through acquisition. Either way, this will increase the volume and complexity of the data flowing through enterprise applications. Disconnected and separate systems bring various issues such as data inconsistency, fragmented data, data inaccuracy, and an increasing difficulty and effort within IT departments in reacting to changing business needs. It is this increasing task of disentangling the complex data issues that makes the case to have a centralized system that can define, integrate, cleanse, manage, and finally distribute data to various systems.

Master Data Management (MDM) defines a process of collecting enterprise data from various sources, applying standard rules and business processes, building a single view of the data, and finally distributing this ‘golden’ version of data to various systems in the enterprise, thereby making it accessible to all consumers. Here is more detailed article about MDM and its importance.

Master Data Services (MDS)

Master Data Services is a Microsoft product for developing MDM solutions that is built on top of SQL Server database technology for back end processing. It provides service-oriented architecture endpoints using Windows Communication Foundation (WCF). You can implement a hub architecture using MDS to create centralized and synchronized data sources to reduce data redundancies across systems.

Configuration Manager

Configuration Manager is a starting point for configuring Master Data Services. You can use it create and configure the MDM database. This database comes with many stored procedures, database tables, and functions which collectively support back-end processing. You can also create a web application called Master Data Manager and, finally, you can associate the database and web application together, thereby creating an MDM solution.

MDS Architecture

Master Data Services has a three-tier architecture consisting of the database layer, service layer, and UI/Add-in layer. This is illustrated in an architecture diagram below that shows MDS integrated with Data Quality Services and SQL Server Integrations Services. In order to understand MDS the area better, I have put blue rectangular box around the MDS components.

Borrowed from Microsoft website and put blue rectangle box to keep things in context.

History, installation and configuration

Microsoft first introduced Master Data Services with SQL Server 2008 and, as generally happens with first versions, there were limited features and some defects as well. Also, because there was little awareness and appreciation of master data management systems at the time, it is hardly surprising that its introduction went mostly unnoticed. Subsequent versions of SQL Server 2012, 2014, and 2016 brought some new features and product stability to MDS too. With SQL Server 2016, Master Data Management capabilities are now of similar quality and sophistication as any other enterprise-grade MDM solution.

The following installation instructions are based on SQL Server 2016 available on Microsoft Azure Cloud platform and should work fine with other MDS versions supported on SQL server as well.

• Login with your Azure account or create azure account if you have not done so already.
• Search for a virtual machine with following name “SQL Server 2016 RTM Enterprise on Windows Server 2012 R2”.
• Follow the simple ‘provisioning VM’ wizard, which mostly involves clicking ‘next-next’ with some occasional user inputs. Be sure to carefully note down the details as you go, especially the credentials.
• Start the recently-created virtual machine, and note down the public IP address that is assigned to it.
• You can ‘remote desktop’ into the virtual machine using its public IP address once it is fully up, running and operational. This might take a while depending on VM capacity.
• This virtual machine has pre-installed SQL Server 2016 and Master Data Services too. For others, the installation of Master Data Services is simple: You merely open setup.exe and follow the installation wizard.

Master Data Services is a two-step process; Firstly comes Database Configuration, and then Web Application Configuration.

MDS database configuration

Because MDS is implemented on SQL Server, most of the core logic is written in stored procedures. There are about three hundred stored procedures that get created as part of the database creation procedure. About fifty tables also get created as part of database setup along with some database views and functions. The process to set up the database is as follows:

• Launch the Configuration Manager for Master Data Services from the installed programs.
• You will see two options; Database Configuration and Web Configuration. We will create the website shortly to access the MDM features using the web user-interface.
• Select the Database Configuration option.
• Create the Database. The wizard will ask you to specify some user inputs and it will set up the database according to your user inputs. For now, let’s proceed with windows authentication only.
• You can connect to your newly-created database using SQL Server Management Studio which is also preinstalled.
• Here are some database tables that you will need to understand:
  • tblModel – Contains information about MDM models, also known as business domain like Customer, Product, etc.
  • tblEntity – Contains information about MDM entity. It stores information about entities for all models.
  • tblAttribute – Contains attributes details for each entity. It is more like a database column.
  • tblAttributeGroup – It is a logical grouping of similar attributes for a particular entity.
  • tblModelVersion – to model versions.

Model & Entity related tables from MDS database

MDS is a highly configurable system and it does not come with any predefined domains or domain entities. More database tables get created as you proceed with model and entity creation.

MDS web UI setup

MDS provides a web application for administrative tasks such as the creation of models, entities, business rules, deploying models, integration management, version management, and users and group permission management. The web application also supports some non-administrative tasks such as updating data. The web application talks to the backend MDS service which is a web service based on Windows Communication Foundation (WCF). WCF is a framework for building service oriented applications. The process to create the web site, or rather the web application, goes as follows:

• IIS is preinstalled on this virtual machine but the installation of Roles and Features is required.
• To install required IIS roles and features for the MDS web application, open Server Manager and then click ‘Manage’ ‘Add Roles and Features’
• Select default installation type which is Role-based or feature-based installation

• You will see only one server for select a server from the server pool

• Select Roles and features as per the following picture.
• Complete the installation after selecting roles and features.
• If you open MDS configuration manager and select web configuration even before installing these roles and features, you will receive errors complaining about missing roles and features.

• Open Master Data Services Configuration Manager from installed programs
• Click on Web Configuration option and select the Default Web Site option from drop down.
• Clicking ‘create’ will give you options to specify application context name (Alias). Input MDS.
• You can use the same credentials as you windows VM credentials and click ‘OK’.
• Now select recently create database and hit ‘Apply’ to create web application. ‘Apply’ will open the user-interface in your default browser.
• You should be able to access http://localhost/mds after few seconds.

Azure inbound rule setup for HTTP access over the internet

By default, any cloud provider will disable HTTP access to cloud resources from external sources, so does Azure. To access the MDS UI from your computer, you have to add an inbound rule for the HTTP protocol on port 80 or port of IIS server currently configured.

• Login to azure portal
• ‘All Resources’ Network Security Group (associated with your instance) ‘Inbound security rules’ ‘Add’
• Specify the option mentioned in the picture below and click ‘Ok’.
• Now you should be able to access MDS web application from your computer using the URL http://publicipofvm:port/mds

Tools and components of MDS

Master Data Manager

Master Data Manager (‘UI’), is a graphical user interface to handle master data services tasks. The two types of tasks that can be performed are Data tasks, and Administrative tasks.

Data Tasks

User can view entities, entity dependencies, hierarchies, collections, and change sets at entity level. Member data can be created, viewed, updated, or deleted. You can also apply business rules, view history of any member. The model list selection acts as the context when you work with data tasks.

Administrative Tasks

Administrative tasks can be grouped in following four categories:

System Administration

You can perform CRUD operations on models, entities, attributes, attribute groups, hierarchies, indexes, and business rules. You can also create model packages without data to deploy in other environments and can also deploy previously-created model packages.

Integration Management

Integration management is about ways to get data in or out of MDS. You can import data into MDS in batches. You can also create subscription views to export data out of MDS and your downstream systems can subscribe to these SQL views.

Version Management

You can create a version of master data, lock-unlock versions, view ancestry, and purge members. You can also manage version flags. By default four version flags are created; archive, current, plan, and prior. You can also add or delete version flags. When you deploy model packages with data only version is created which is neither validate nor committed. You can validate and commit versions. You can also access transactions for a particular version of the model.

Security / User and Group Permissions

On installation of MDS, only the administrator account gets created; and more users or groups can be created to grant permission to specific MDS functions or to restrict access to functions. MDS security can be managed using Master Data Manger UI or programmatically by calling web service operations. Master Data Service security is based on Windows or Active Directory domain users and groups.

To set up security, here are the general steps to follow:

• Assign functional area access, out of five functional areas like Explorer, System Administration, Integration Management, Version Management, and User and Group Permissions.
• Assign Model object permissions, to determine attribute-level access and the type of access (Read or Write) for those attributes.
• Assign Hierarchy member permissions (optional and mostly not needed), to determine member-level access and the type of access to those members.

Security best practice for any system is to always create the group first and then assign permission to groups. Having done that, you can then add or remove users from groups anytime without juggling through specific permission.

Add a Group

Add a group to local users and groups on windows host where master data services is hosted. Add users to this group. You have to add a group to the application using the UI as well.

Add a User

Add a user to local users and groups on windows host where master data services is hosted. Add this user to designated group. You have to add the user to the application using the UI as well.

Assign functional permissions

Select the group whose permission you want to change/associate and edit the permissions. You can move functions between available and assigned lists.

Assign Model permissions

Select the group whose permission you want to change/associate and edit the permissions. You can select a specific model under the ‘Models’ tab and assign permission as you need.

The granularity of security does not stop at functions and models; rather you can assign permissions for entities, Leafs, Consolidated, collections, attributes, etc.

MDSModelDeploy.exe

MDSModelDeploy.exe utility is a tool that is used to create deployment packages of existing model objects with or without data, and it can also deploy previously-created model object packages. This is another way of deploying packages apart from UI deployment wizard. MDSModelDeploy can be used together with following commands:

• listservices – returns a list of all service instances on this MDS server

  MDSModelDeploy listservices MDS services <Service, Website, Virtual Path>: MDS1, Default Web Site, MDS

• listmodels – returns a list of all models

  MDSModelDeploy listmodels Models: ChartofAccounts Customer Product

• listversions – returns a list of all versions for the specified model

  MDSModelDeploy listversions – model Customer Versions of model Customer: VERSION_1

• deployclone – deploy a clone of a model. All Names and IDs will be identical to the model in the package

  MDSModelDeploy deployclone –package samplemodel.pkg – service MDS1

• deploynew – create a new model by deploying a model package

  MDSModelDeploy deploynew –package samplemodel.pkg –model Customer –service MDS1

Deploying sample models

MDS comes with three sample model packages and are available under directory “C:Program FilesMicrosoft SQL Server130Master Data ServicesSamplesPackages”. Follow these steps to deploy these models:

• Copy models from sample/packages to ../../Configuration directory where MDSModelDeploy.exe utility is present.
• Check service name by executing command “MDSModelDeploy listservices”
• To deploy ChartsofAccount

MDSModelDeploy deploynew -package chartofaccounts_en.pkg -model ChartofAccounts -service MDS1

• To deploy Customer

MDSModelDeploy deploynew -package customer_en.pkg -model Customer -service MDS1

• To deploy Product

MDSModelDeploy deploynew -package product_en.pkg -model Product -service MDS1

MDS Web Service

Service Oriented Architecture is a standard way to go for an enterprise with disparate tools and technologies. MDS provides a web service, which can be used to extend MDS capabilities or develop custom solutions. The MDS web service provides complete set of operations to allow developers to control all those functions that are supported by the Master Data Manager UI.

Previous versions of MDS had a simple configuration to enable web service using Master Data Service Configuration Manager but recent versions including 2016 do not provide web service enablement using Configuration Manager. You have to modify web.config xml from web site configuration and set flag enableWebservice=true.

Once the web service is enabled and the server is online, you can access WSDL using following URL: http://servername:port/mds/service/service.svc?singleWsdl and here are available web service operations:

The conceptual grouping of service operations are:

• Model Operations
• Entity Operations
• Member Operations
• Attribute Hierarchy Operations
• Business Rule Operations
• Annotation Operations
• Transaction operations
• Version and Validation Operations
• Data Quality Operations
• Data Import Operations
• Data Export Operations
• Security Operations
• System Operations

Add-in for Excel

When it comes to supporting MDM features, all vendors support more or less same features. The only difference is the user-experience in using MDM systems. Master Data Services Add-in for Excel is master-stroke because it empowers business users directly using their favorite tool Excel. At the beginning of any MDM implementation, the bulk data load is the key requirement for any enterprise and most enterprises end up by investing a good chunk of budget in developing custom solutions for data migration including high vendor consulting rates. Master Data Services Add-in for Excel, allows business users to manage data while also allowing administrators to create new entities and attributes with ease. Most of the features available via Master Data Manager are also possible using the Excel plugin.

To download the Add-in for Excel, log in to the Master Data Manager UI and click on “Install Master Data Services in Excel” which will take you to Microsoft website. Once there, you can download an installer based on your computer architecture (32 bit or 64 bit). You will then see the Master Data menu in Excel after successful installation. Enable plugin from ‘File‘ ‘options‘ ‘Add-ins‘ select ‘Master Data Services Add-in for Excel’, if you don’t see Master Data at top level menu.

To connect Excel with MDS server, you have to configure MDS connection in Excel by following these steps as per below picture:

• Click on ‘Master Data’ menu
• Click Connect/Manage Connections
• Create a new connection by specifying connection name and MDS server address
• Server address should be like http://serveripaddress:port/website
• My IIS is running on port 80 which is the default http port. That is why you won’t see the port mentioned in below server address.
• The easiest way to find it out is by launching MDS Configuration Manager. Select your website and hit ‘apply’.This will open the website in your browser something like http://hostname:port/mds/gettingstarted
• Your server address for Excel Add-in will be http://hostname:port/mds

You will receive an authentication failure because the Add-in does not provide a way to provide credentials. To resolve this issue, you can login to your MDS application using the browser(s) and save the password. Saved credentials will then be used by Excel for authentication. For enterprise use, the MDS server will be integrated with the corporate LDAP/AD server and Excel can then use your Windows authentication.

Accessing sample data set using Excel

Connect and Load

You can manage one or more connections to MDS servers in different environment such as Dev, QA, or Prod. By selecting ‘entity’ from Master Data Explorer, you can click ‘refresh’ to fetch data from server into worksheet. You can also apply filters to load selective data sets. A filter gives you the capability to fetch selecting columns, a selective attribute group, a hierarchy, and so on.

Save and Send Query

Once the connection is established and data is retrieved with or without applying a filter, you can save this retrieval info as a shortcut query. This Shortcut query contains information about active connection, model, version, entity, and any applied filters. You can also email this query by clicking the ‘Send Query’ option which will email the query file as an attachment.

Publish and Validate

This has the most widely-used functions such as to publish data to the MDS database, delete selective rows, merge the publish conflicts, view selected member history and annotation, combine date from external worksheet into the entity, apply business rules, and show-hide audit info and validation status.

Build Model

If you are administrator then you can create a new entity in the MDS database by providing entity attributes information in a worksheet and then clicking “Create Entity”. It will ask you about the Model info, version info, entity name, and so on. Make sure that the model has been created using the UI or by using the web service before you create the entity under ‘new model’ or ‘select existing model’.

Conclusion

In this article, I have presented the context for Master Data Services and given a basic explanation of the internal architecture including various integration points. I’ve also described a detailed step-by-step process of installation and configuration on Azure platform. This includes Azure HTTP port permission to access the MDS system over the internet. The Tools and component section lists out the various features available in MDS, what you can do using those features and how to use those features. With this article you are ready to explore Master Data Services and possibly you can start implementing your organization’s master data management program.

Master Data Services Введение

Основные данные используются для описания данных из основного бизнес-субъекта, таких как клиенты, партнеры, сотрудники, продукты, материалы, счета, учетные записи и т. Д.; Это высокое деловое значение, которое может быть неоднократно используемому в различных бизнес-единицах в пределах Предприятие. И существуют в множестве гетерогенных приложений.

Похоже, что это, кажется, абстракция, например: простые продажи:

время	площадь	продукт	цена	количество	рекордер
2018-3-31	Сычуань	Велосипед -001.	309.00	2	user01

Эти данные не особо завершаются, упрощенная запись, но в этих данных есть управление данными, такими как «Регистратор», «Регион», «Продукт», потому что оно может быть связано с различными системами, которые использовали рекордер «Войти Имя «Некоторые системы могут быть« отображением », или имя пользователя изменилось, то предыдущая задняя запись изменится, и это изменение сложно преследовать, что приведет к более поздние данным, есть много ошибок в обработке и статистике. Поле «Регион», может быть «провинция Сычуань» в системе, а некоторые места используют «Сычуань», называемую «Китай-Сычуань», приведет к многим сообщениям об ошибках в последующей обработке.

Итак, как стандартизировать эти информационные данные, и данные каждой подсистемы объединяются. И данные могут быть повторно использованы, и когда изменения данных могут быть преследоваться, и каждая подсистема может немедленно получить последние данные, что является значением основных данных. Конечно, есть более сложные потребности, такие как: как управлять основными данными, которые могут прочитать, кто может изменить его.

SQL Server предоставляет функцию MDS в 2008 году. Это может быть легко и легко управлять MDS Management.

Установка Master Data Services

С помощью служб основных данных необходимо установить некоторые функции Windows, вы можете использовать скрипт Shower Shell ниже.

Install-WindowsFeature Web-Mgmt-Console, AS-NET-Framework, Web-Asp-Net, Web-Asp-Net45, Web-Default-Doc, Web-Dir-Browsing, Web-Http-Errors, Web-Static-Content, Web-Http-Logging, Web-Request-Monitor, Web-Stat-Compression, Web-Filtering, Web-Windows-Auth, NET-Framework-Core, WAS-Process-Model, WAS-NET-Environment, WAS-Config-APIs  
​
Install-WindowsFeature Web-App-Dev, NET-Framework-45-Features -IncludeAllSubFeature –Restart  
​

Установите эту функцию во время установки SQL Server.

1. Откройте установочные файлы SQL Server, дважды щелкните Setup.exe, выполните шаги в мастере установки.

2. В разделе «Общие функции» страницы страницы выбора функций выберите «Основные службы передачи данных». Установка,

После завершения установки запустить меню

Microsoft SQL Server 2017> Master Data Services> SQL Server 2017 Master Data Services Configuration Manager

Откройте SQL Server 2017 Master Data Data Services Configuration Manager Configuration Устройство может сделать соответствующую конфигурацию, интерфейс выглядит следующим образом:

Основная конфигурация служб данных

Во-первых, вам необходимо создать базу данных, поэтому нажмите, чтобы создать базу данных, следуйте за мастером ниже, чтобы создать

Введите имя сервера базы данных и разрешения, которые вам необходимо создать.

Введите соответствующую имени базы данных и выберите правила сортировки

Имя администратора

Вы можете увидеть следующую информацию после завершения создания: содержит имя экземпляра базы данных, имя базы данных и информацию о версии. Конечно, вы также можете установить параметры соответствующей базы данных соединения, такие как тайм-аут.

После завершения конфигурации базы данных необходимо сделать веб-конфигурацию, нажмите на веб-конфигурацию, создать пул приложения

входитьСоответствующий пользовательский ключ, информация о пути можно настроить. Результаты приведены ниже:

Выберите базу данных MDS, которая должна быть подключена

После приложения завершите подсказку, можете просматривать веб-приложение, а затем откройте следующий интерфейс, если не установлен SliverLight, вам будет предложено.

Концепция Введение

Используйте MDS, чтобы сначала понять несколько концепций, моделей, объектов, свойств и бизнес-правил.

Модель:Модель является наивысшим уровнем организации данных в Услуги основных данных. Модель определяет структуру данных в вашем основном решении управления данными. Модель содержит следующие объекты: объект, атрибуты, группы атрибутов, явные иерархии и полученные иерархии, коллекция

Модель организует структуру ваших основных данных. Реализация ваших услуг основных данных может иметь одну или несколько моделей, каждая модель объединяет аналогичные типы данных. Как правило, основные данные могут быть разделены на один из следующих четырех способов: персонал, местоположение, событие или концепция. Например, вы можете создать модель продукта, чтобы включить данные, связанные с продуктом, или создать модель клиента, чтобы включить данные, связанные с нашими клиентами.

Вы можете выделить разрешения пользователя и групп для просмотра и обновления объектов в модели. Если вы не предоставляете разрешения модели, она не отображается.

Вы можете создать копию основных данных в модели в любое время. Эти копии называются версией.

После определения модели в тестовой среде вы можете принести или не развернуть ее из тестовой среды к производственной среде. Это не придется воссоздать вашу модель в производственной среде.

организация:Сущность является объектом, включенным в модель Master Data Services. Каждое сущность содержит элементы, которые являются строками основных данных, которые вы управляете. Модель может содержать любые несколько сущностей, которые вы хотите управлять. Каждая сущность должна сгруппировать аналогичные типы данных. Например, вы можете использовать сущность для всех учетных записей вашей компании или использовать сущность для главного списка сотрудника.

Как правило, есть один или несколько центральных объектов очень важен для вашего бизнеса, и они связаны с другими объектами в модели. Например, в модели продукции объект может иметь центральное предприятие именованного продукта и объекты продукта, связанные с именованными подкатегориями и категорией. Тем не менее, вы не обязательно иметь центральный объект. В зависимости от ваших потребностей, вы можете иметь несколько объектов не менее важными.

Атрибуты:Услуги основных данных — это свойство объекта, содержащегося в объекте. Описание значения атрибута Описание элементов. Свойства могут быть использованы для описания элемента листа, члена слияния или сбора.

Следующая диаграмма посмотреть модель продукта в качестве примера, показывает взаимосвязь между модульными объектами, коллекциями, свойствами

Следующие примеры продукта имеет объекты в модели для логической группы данных, связанных с продуктом

Другие общие модели:

• Субъекты могут содержать учетные записи баланса, отчеты о доходах, учетные записи, статистики и типы учетных записей и другие объекты.

• Заказчик может содержать секс, образование, занятие и семейное положение и другие организации.

• Географическая информация, она может содержать почтовый индекс, город, город, государственные, провинциальные, региональные, национальные / региональные и континентные и другие организации.

Импорт учебного пакета

Узнайте MDS могут импортировать соответствующее исследование Counte для изучения.

Следующие три примера включены в модельный пакет в Услуги Master Data. Эти примеры включают модели данных. Расположение по умолчанию Пакет модели образца составляет% ProgramFiles% Microsoft SQL Server 140 Master Data Services Образцы Пакеты

•chartofaccounts_en.pkg

•customer_en.pkg

•product_en.pkg

1 Скопируйте пакет модели образца на файлы C: Program Microsoft SQL Server 140 Master Data Configuration.

2, Откройте командную строку администратора, запустив следующую команду, а затем перейдите к MDSModeldePloy.exe.

Разверните модель образца ChartofCcounts_en.pkg 
cd c:Program FilesMicrosoft SQL Server140Master Data ServicesConfiguration
​
В списке значений, возвращаемых ниже, первое значение обслуживания - это значение, указанное для модели развертывания. 
MDSModelDeploy listservices
Разверните модель образца ChartofCcounts_en.pkg 
MDSModelDeploy deploynew -package chartofaccounts_en.pkg -model ChartofAccounts -service MDS1 
Развернуть модель образца Customer_en.pkg 
MDSModelDeploy deploynew -package customer_en.pkg -model Customer -service MDS1  
Развертывание модели образца Product_en.pkg 
MDSModelDeploy deploynew -package product_en.pkg -model Product -service MDS1  
​

После того, как развертывание является полным доступом к веб-сайтам, таким как

http://localhost/MDS

Интерфейс может выглядеть следующим образом:

Модель управления просмотром

Использование модели Explorer может добавить, модифицировать и т. Д., В качестве модели выбранного продукта, версия по умолчанию, нажмите Explorer.

Вы можете видеть, что есть объекты, их зависимости, иерархии, наборы, изменение набора операций.

Сущности, такие как выбор цвета, вы можете увидеть следующие данные

В этом интерфейсе могут быть добавлены удаленные участники, вы также можете использовать правила приложения Применять, что правило относится к бизнес-правилам модели.

Бизнес-правила являются заявлениями Master Data Manager, который вы создали в SSMDSMDM. Если указанное значение свойства, указанное условие, выполнит операцию, иначе остальные операции. Возможные действия включают установку значений по умолчанию или значения изменения. Эти операции могут заметить, что работают в сочетании с отправкой электронной почты.

Бизнес-правила (например, Color = Blue, операция выполняется) на основе конкретного значения атрибута или когда изменяется значение свойства (например, если значение цвета цвета изменяется, выполняется операция) приложения. Для получения дополнительной информации о отслеживании неспецифических изменений см. Изменение отслеживания (Master Data Services).

Чтобы использовать бизнес-правила, вы должны сначала создавать и публиковать правила, то правила будут применяться к опубликованным данным. Вы можете проверить версию, правило применяется ко всем данным или дочерней версии данных. До того, как все свойства подтверждаются бизнес-правилами, нельзя представить версию.

Если пользователь пытается добавить значение атрибута, не проверяется бизнес-правилами, вы все равно можете сохранить значение. Вы можете просматривать и исправить проблему проверки в основном диспетчере данных отображения.

Когда вы создаете пакет развертывания модели, если вы хотите включить бизнес-правила, он должен включать данные из версии пакета.

Если вы создадите для каждого условия, используя бизнес-правила Оператора или оператора, следует проводить самостоятельно, можно рассчитать оператор создает отдельные правила. Затем вы можете исключить правило по мере необходимости, чтобы обеспечить большую гибкость и упростить устранение неполадок.

Вы также можете использовать Excel для управления моделями, вам нужно загрузить надстройку MDS для Excel, как использовать,

Обратитесь к следующим ссылкам:

https://docs.microsoft.com/zh-cn/sql/master-data-services/microsoft-excel-add-in/master-data-services-add-in-for-microsoft-excel

Начните использовать MDS.

Нажмите на домашнюю страницу модели управления системой может быть создана

Нажмите Добавить, чтобы добавить модель

После добавления модели необходимо добавить объекты, атрибуты и т. Д. В общем, многое нужно сделать, мы все не можем закончить, вы можете исследовать самостоятельно, чтобы завершить следующее

Особенности и задачи служб основных данных

Функции и задачи основных данных, нам нужно завершить

1, для создания моделей, структур данных и т. Д.

2. Как только у вас есть данные, необходимость обслуживания данных

3, данные также должны улучшить качество данных

4, как импортировать и экспортировать операцию передачи данных

5, как развить пользовательские приложения

Найдите ниже ссылки на любую и все работы. Пожалуйста, обратитесь к, если нет, нажмите, пожалуйста, посетите

https://docs.microsoft.com/zh-cn/sql/master-data-services/master-data-services-features-and-tasks

Создать структуру, содержащую данные

• Модель (Master Data Services) Модели (Master Data Services)

• Предприятия Entity (Master Data Services) (Master Data Services)

• Атрибуты атрибута (Master Data Services) (Услуги для основных данных)

• Домен на основе атрибутов атрибутов на основе домена (Master Data Services) 

• Группы атрибутов (Master Data Services) группа атрибутов (Master Data Services)

Техническое обслуживание основные данные

• Master Data Services Надстройка для Microsoft Excel для Microsoft Excel Master Data Services Надстройка для Microsoft Excel

• Член (Master Data Services) ЧЛЕНЫ (Master Data Services) 

• Master Data Services Сделки (Master Data Services)

• Master Data Services аннотации (Master Data Services)

• Иерархия Иерархии (Master Data Services)

• Master Data Services Collectes (Master Data Services)

Улучшение качества данных

• Бизнес-правила Бизнес-правила (Master Data Services)

• Master Data Services Validation (Master Data Services)

• Master Data Services Версии (Master Data Services)

• Master Data Services УВЕДОМЛЕНИЯ (Master Data Services) 

• Безопасность (Master Data Services) Безопасность (Master Data Services)

экспорт данных Импорт

• Обзор: Импорт данных таблицы (Master Data Services) Обзор: Импорт данных из таблиц (Master Data Services) 

• Обзор: Экспорт данных (службы Master Data) Обзор: Экспорт данных (Master Data Services) 

• Модель развертывания (Master Data Services) Развертывание модели (Master Data Services)

Разработка пользовательских приложений

• Master Data Services Developer Document Master Data Services Документация для разработчиков

Комплексное применение

Первичное управление данным, в конечном счете применяется в соответствующих системах, MDS предоставляет услуги для связи и управления.

Во-первых изменить C: Program Files Microsoft SQL Server 140 Master Data Services WebApplication Web.config файл, найденный<serviceMetadata httpGetEnabled=»true» httpsGetEnabled=»true» />Изменение этих двух параметров верно

Доступ Подобные соединения:

http://localhost/MDS/service/service.svc

Операцию вы можете сделать это следующим образом:

Модель работы

Эти операции используются для создания, обновления и удаления моделей, а также выполнять операции для всех содержаний модели, таких как сущности, иерархия и версии. Смотрите модель (Master Data Services) для получения дополнительной информации.

|||-||MetadataClone||MetadataCreate||MetadataDelete||MetadataGet||MetadataUpdate|

операция Entity

Эти операции используются для создания, обновления и удаления членов единого целого. Для получения дополнительной информации см Master Data Services и члены (Master Data Services).

|||-||EntityMemberKeyLookup||EntityMembersCopy||EntityMembersCreate||EntityMembersDelete||EntityMembersGet||EntityMembersMerge||EntityMembersUpdate|

работа Участник

Эти операции используются для получения, обновления и удаления пользователей. Члены этой группы операций могут содержать член в нескольких лицах. См пользователей (Master Data Services) для получения дополнительной информации.

|||-||ModelMembersBulkDelete||ModelMembersBulkMerge||ModelMembersBulkUpdate||ModelMembersGet|

Атрибуты и иерархические операции

Эти операции используются для получения свойств и иерархической информации. Вы также можете изменять атрибуты и иерархии, например, metadataupdate с использованием модели операций. Для получения дополнительной информации см Master Data Services и иерархии.

|||-||EntityMemberAttributesGet||HierarchyMembersGet|

Бизнес правила

Эти операции используются для создания, обновления, удаления и публикации бизнес-правил. Для получения дополнительной информации см бизнес-правил (Master Data Services).

|||-||BusinessRulesClone||BusinessRulesCreate||BusinessRulesDelete||BusinessRulesGet||BusinessRulesPaletteGet||BusinessRulesPublish||BusinessRulesUpdate|

аннотирование

Эти операции используются для создания, обновления и удаления аннотаций. Для получения дополнительной информации см Master Data Services.

|||-||AnnotationsDelete||AnnotationsUpdate||EntityMemberAnnotationsCreate||EntityMemberAnnotationsGet||TransactionAnnotationsCreate||TransactionAnnotationsGet|

работа транзакций

Эти операции используются для получения и отменить транзакции. Для получения более подробной информации, пожалуйста, обратитесь к обслуживанию основных данных.

|||-||TransactionsGet||TransactionsReverse|

операция версий и проверка

Эти операции используются для копирования и проверки версии. Для получения дополнительной информации см. Услуги по данным и проверку основных данных (Master Data Services).

|||-||VersionCopy||ValidationGet||ValidationProcess|

Операция качества данных

Эти операции используются для выполнения задач качества данных и проверки их результатов.

|||-||DataQualityCleansingOperationCreate||DataQualityMatchingOperationCreate||DataQualityInstalledState||DataQualityKnowledgeBasesGet||DataQualityOperationStart||DataQualityOperationResultsGet||DataQualityOperationStatus|

Работа импорта данных

Эти операции используются для импорта данных в базу данных основных данных. Для получения дополнительной информации см. Обзор: Импорт данных таблицы (Master Data Services).

|||-||EntityStagingClear||EntityStagingGet||EntityStagingLoad||EntityStagingProcess|

Следующая операция используется для импорта данных с использованием временного процесса, включенного в SQL Server 2008 R2. Эти операции должны использоваться только для поддержки существующих баз данных. Для новой разработки работайте операции, перечисленные выше.

|||-||StagingClear||StagingGet||StagingNameCheck||StagingProcess|

Работа экспорта данных

Эти операции могут быть использованы для экспорта данных с использованием абонентов. Для получения дополнительной информации см. Обзор: Export Data Services.

|||-||ExportViewCreate||ExportViewDelete||ExportViewListGet||ExportViewUpdate|

Безопасная работа

Эти операции используются для изменения параметров безопасности для доступа к базе данных основных данных основных данных. Для получения дополнительной информации см. Безопасность (Услуги по сохранению данных)

|||-||SecurityPrincipalsClone||SecurityPrincipalsCreate||SecurityPrincipalsDelete||SecurityPrincipalsGet||SecurityPrincipalsUpdate||SecurityPrivilegesClone||SecurityPrivilegesCreate||SecurityPrivilegesDelete||SecurityPrivilegesGet||SecurityPrivilegesUpdate|

Система операции

Эти операции используются для получения и обновления системных настроек и пользовательских настроек.

|||-||ServiceCheck||ServiceVersionGet||SystemDomainListGet||SystemPropertiesGet||SystemSettingsGet||SystemSettingsUpdate||UserPreferencesDelete||UserPreferencesGet||UserPreferencesUpdate|

Вышеуказанные методы достаточно для удовлетворения потребностей заявки, а также подробное развитие документации по разработке:https://docs.microsoft.com/zh-cn/sql/master-data-services/develop/categorized-web-service-operations-master-data-services

Суммировать

Можно сказать, что основным управлением данных, предоставляемым SQL Server, можно сказать, что является глубоким, простым решением и разработкой предприятия на определенный уровень. Многие поставщики, продукты баз данных обеспечивают функцию первичных данных, а также продукты с открытым исходным кодом. SQL Server предоставляет набор продуктов Master Master Master Data Enterprise и включается в базу данных в качестве компонента и не требует отдельного платежа.

Макс Шен, апрель 2018

 

SQL
Server Технические статьи

Писатели: Тайлер Грэм, Сюзанн
Selhorn, Ларен Кроуфорд

Технический рецензент: Фам Минь

Опубликовано: Март 2009

Обновление: марта 2011 года

Применимо к:
SQL
Server 2008 R2
Master
Data Services

Предложенная статья является машинным переводом оригинала.

Резюме: в настоящем документе подробно описываются шаги, необходимые для настройки Microsoft ® SQL Server® 2008 R2 Master Data
Services  или Microsoft ® SharePoint ® workflow.

Введение

Master Data Services использует бизнес-правила для создания решений основных рабочих процессов. Можно автоматически обновлять и проверки данных и уведомления по электронной почте прислали по заданным
условиям. Бизнес-правила в Master Data Services предназначены для управления наиболее распространенных сценариев рабочего процесса.

Если ваш рабочий процесс требуется более сложных событие обработки, такие как multi-tiered разрешения или комплексное решение деревья, можно настроить Master Data Services для отправки данных
для пользовательской сборки, которую вы создаете или SharePoint для запуска рабочего процесса SharePoint.

Каждый раз, когда начала рабочего процесса бизнес-правила применяются к вашей сущность, записи, отвечающие условиям правила бизнес, передаются в очередь компонента Service Broker базы данных Master
данных служб. На регулярной основе, MDS рабочего процесса интеграции SQL Server служба вызывает хранимую процедуру в базе данных Master Data Services. Эта хранимая процедура извлекает записи из очереди компонента Service Broker и передает их компонент рабочего
процесса, который вы укажите.

Важное примечание: MDS рабочего процесса интеграции служба , вызывающий код настраиваемого рабочего процесса предназначен для показа простых процессов. Если пользовательский код
требует комплексной переработки, завершить обработку в отдельный поток или за пределами рабочего процесса MDS.

Предварительные требования

Перед настройкой служб данных Master для работы с рабочий процесс, необходимо иметь имеется следующее программное обеспечение:

• SQL Server 2008 R2 мастер данных служб ноября CTP или более поздней версии
• Microsoft ® Visual Studio ® 2010 Beta 2 конечной или более поздней версии

Это программное обеспечение будет установлено в следующих процедурах.

Рабочий процесс

Направлять базовых данных из службы данных Master в рабочий процесс, выполните следующие действия.

• Шаг 1: Установка служб данных Master
• Шаг 2: Установка Visual Studio
• Шаг 3: При желании установить SharePoint
• Шаг 4: Создание лока��ьного пользователя для службы интеграции MDS
• Шаг 5: Создайте обработчик рабочего процесса в Visual Studio
  
• Шаг 6: Обновление MDS рабочего процесса интеграции служба файл веб-конфигурации
• Шаг 7: Установка и запуск службы рабочего процесса интеграции
• Шаг 8: Создать и применить бизнес-правила в Master Data Manager

Шаг 1: Установка служб данных
Master

Во-первых установите службы данных Master. Для получения дополнительных сведений, в разделе электронной документации SQL Server 2008 «Как к: установить мастер данных услуги» (http://msdn.microsoft.com/en-us/library/ee633762
(SQL.105) .aspx).

Этот технический документ использует C:Program Files в папке, где установлены службы данных Master.

Шаг 2: Установка Visual Studio

Установка Visual Studio 2010 или более поздней версии. Будет использовать Visual Studio для создания рабочего процесса обработчика.

Вы можете установить Visual Studio:

http://www.Microsoft.com/VisualStudio/en-US/Products/2010/Default.mspx

Шаг 3: При желании
установить SharePoint

Если вы используете службы данных мастер для запуска рабочего процесса SharePoint, Master Data Services требуется версия SharePoint на сервере, где установлены службы данных Master. Эта версия
SharePoint могут размещаться рабочий процесс, но он не нужно. Если вы планируете использовать настраиваемый рабочий процесс вместо рабочего процесса SharePoint, не требуется для установки SharePoint.

Минимальная версия SharePoint требуется — 2010 Фонд SharePoint. Вы можете установить фонд SharePoint из:

http://TechNet.Microsoft.com/en-US/SharePoint/ee263910.aspx

В некоторых ситуациях установки SharePoint будет остановить веб-узел по умолчанию на сервере и набор узел SharePoint для обработки всех запросов, поступающих в порт 80. Если у вас набор вверх
ваш сайт Master Data Services на веб-узле по умолчанию, или использовать порт 80, они будут конфликтовать. Чтобы устранить эту проблему, используйте диспетчера Internet Information Services (IIS) для настройки служб данных Master или SharePoint для использования
порта за исключением порта 80. Если мастер Data Services установлена на веб-узле по умолчанию, вам также нужно перезапустить веб-узел по умолчанию.

Примечание: необходимые параметры сортировки для установки баз данных SharePoint Фонд является Latin1_General_C1_AS_KS_WS. Другие параметры сортировки, не поддерживаются. Для
получения дополнительных сведений о настройке сервера SharePoint, в разделе SharePoint «Параметр до развития окружающей среды для SharePoint Server» (http://msdn.microsoft.com/en-us/library/ee554869
(office.14) .aspx).

Шаг
4: Создание локального пользователя для службы интеграции MDS

Для наибольшей безопасности создать локального пользователя на сервере служб данных Master и предоставить этому пользователю только разрешения, необходимые для выполнения операций рабочего процесса,
которые подробно изложены в последующих процедур в настоящем документе. Если вы будете использовать рабочих процессов для SharePoint, можно использовать W3WP идентификация, используемая пулом приложений SharePoint в Internet Information Services (IIS), обычно
сетевой службы. Оставшаяся часть этого документа показывает, как настроить вашу систему для использования mds_workflow_service Windows пользователя, создаваемый в этом разделе. Шаги похожи, если вы используете идентификатор W3WP SharePoint.

Создание
учетной записи локального пользователя служба MDS рабочего процесса

1. Откройте диспетчер локальных пользователей и групп. Чтобы открыть этот компонент, нажмите кнопку выполнить в меню Пуск, типа…
   lusrmgr.msc, и нажмите клавишу ENTER.
2. Выберите пользователей папки.
3. Выберите новый User… из
   действий меню.
4. Введите mds_workflow_service для имени пользователя и создать надежный пароль. Нажмите кнопку
   создать кнопку для создания пользователя.

Грант
хранимой процедуры разрешения пользователям служба MDS рабочего процесса

Для наибольшей безопасности рекомендуется настроить базу данных Master Data Services использовать проверку подлинности Windows. Кроме того можно использовать смешанной проверки подлинности и создавать
SQL Server имени входа и пользователя.

1. Откройте SQL Server Management Studio и подключитесь к компонента Database Engine SQL Server экземпляр , на котором находится база данных Master Data Services.
2. Создайте новое имя входа для пользователя mds_workflow_service, созданный в предыдущем разделе. Чтобы создать новое имя входа, откройте
   безопасности узел в обозревателе объектов, щелкните правой кнопкой мыши
   логины узел, а затем выберите Новый Login…. В
   входа диалоговое окно, введите < имя сервера > mds_workflow_service для
   имя входа, убедитесь проверки подлинности Windows — выбран, измените базу данных по умолчанию в базе данных Master Data Services и нажмите кнопку
   OK.
3. Создайте пользователя для службы данных Master базу данных и сопоставьте ее на логин mds_workflow_service. Чтобы создать и сопоставить пользователя, mds_workflow_service вход в обозревателе объектов щелкните правой кнопкой
   мыши и выберите Свойства. В диалоговом окне Свойства перейдите к
   Сопоставления пользователей страницу, проверьте карта флажок для базы данных Master Data Services и нажмите кнопку
   OK.
4. Разрешение пользователя mds_workflow_service для хранимой процедуры, необходимые для MDS рабочего процесса интеграции служба. Чтобы сделать это, откройте узел службы данных Master базы данных в обозревателе объектов, открытых
   безопасности и пользователей узлы, щелкните правой кнопкой мыши пользователя mds_workflow_service и выберите
   Свойства. В диалоговом окне Свойства перейдите к защищаемые объекты выберите,
   Поиск тип объекта button и поиск всех объектов из хранимых процедур. Найти [МДМ].[udpExternalActionsGet] в списке и Грант разрешения EXECUTE на него.

Шаг
5: Создайте обработчик рабочего процесса в Visual Studio

Существует два типа обработчиков рабочих процессов, можно создать: рабочий про��есс SharePoint или настраиваемого рабочего процесса. Рабочий процесс SharePoint интегрируется с SharePoint путем
создания рабочего процесса на узле SharePoint, указанный вами. Обработчик настраиваемого рабочего процесса.NET библиотека классов, создаваемых, которая может выполнять любые действия, указанный вами.

Создание рабочего процесса SharePoint

Создайте рабочий процесс SharePoint для вашей организации.

1. В Visual Studio 2010 создайте новый проект «Последовательный рабочий процесс». Этот тип проекта находится в шаблоны SharePoint 2010.
2. OnWorkflowActivated включена по умолчанию в конструкторе для вашего решения. Щелкните правой кнопкой мыши элемент управления и выберите команду создать обработчики.
3. Visual Studio создает класс, наследующий от класса SequentialWorkflowActivity. Этот класс содержит
   workflowId свойство , содержащее Идентификатор рабочего процесса,
   workflowProperties класс, содержащий данные, связанные с деятельностью и метод с именем
   onWorkflowActivated1_Invoked , вызывается при активации рабочего процесса. Если вы хотите выполнить какие-либо пользовательскую обработку для рабочего процесса, включите его в этот метод. Данные, которые передаются из рабочего
   процесса служба хранится в виде строка в workflowProperties.InitiationData.
4. Откомпилируйте и разверните ваш рабочий процесс. Для этого щелкните правой кнопкой мыши проект в
   Solution Explorer и нажмите кнопку развернуть.

Создание настраиваемого рабочего процесса

Настраиваемый рабочий процесс.Сборку библиотеки классов NET, который реализует
Microsoft.MasterDataServices.Core.Workflow.IWorkflowTypeExtender интерфейс. Вызовы служба MDS рабочего процесса интеграции
StartWorkflow метод для запуска кода. Выполните следующие действия для создания сборки и настройки MDS рабочего процесса интеграции служба для вызова интерфейс:

1. В Visual Studio создайте новый проект библиотеки классов, использующий язык по вашему выбору. Для создания библиотеки классов C#, выберите
   Visual C# Windows типы проектов и выберите
   Библиотека классов шаблон. Введите имя для вашего проекта, такие как MDSWorkflowTest и нажмите кнопку ОК.
2. Добавить ссылку на Microsoft.MasterDataServices.Core.dll. В
   Solution Explorer панели, щелкните правой кнопкой мыши
   ссылки на и выберите Добавить Reference…. В
   Добавить ссылку диалоговое окно, перейдите на Просмотр вкладку и перейдите в каталог C:Program файлыMicrosoft SQL ServerMaster Data ServicesWebApplicationbin. Выберите Microsoft.MasterDataServices.Core.dll и нажмите кнопку
   ОК.
3. Откройте файл Class1.cs, дважды щелкнув его в
   Solution Explorer панели.
4. Добавить «с помощью Microsoft.MasterDataServices.Core.Workflow;» под другой
   с помощью заявления.
5. Переименуйте класс WorkflowTester и наследовать от IWorkflowTypeExtender.
   Класса объявление должен выглядеть как в следующем примере:
   public class WorkflowTester:
   IWorkflowTypeExtender
6. Реализуйте интерфейс. Щелкните правой кнопкой мыши IWorkflowTypeExtender в файле кода, выберите
   Реализовать интерфейсРеализовать интерфейс в меню. Это создает код-заглушку для всех членов интерфейс.
7. Добавьте любой код, вы желаете StartWorkflow метод. Этот метод вызывается MDS рабочего процесса интеграции служба запуска рабочего процесса. Полный пример кода и описание параметров StartWorkflow метод включены в последнем
   разделе настоящего документа.
8. Постройте решение.
9. Скопируйте сборку MDSWorkflowTest.dll в расположение MDS рабочего процесса интеграции служба исполняемый файл в каталог C:Program файлыMicrosoft SQL ServerMaster Data ServicesWebApplicationbin.
10. В зависимости от конфигурация вашего сервера может потребоваться предоставить пользователю mds_workflow_service разрешения Чтение и выполнение на MDSWorkflowTest.dll. В проводнике Windows щелкните правой кнопкой мыши MDSWorkflowTest.dll
    и выберите Свойства. В Свойства диалоговое окно, перейдите на
    безопасности щелкните изменить…., click
    Add…, и добавьте пользователя < имя_сервера > mds_workflow_service. Mds_workflow_service пользователю предоставляется разрешения Чтение и выполнение по умолчанию. Нажмите кнопку ОК, чтобы закрыть все диалоговые окна, которые открыли.
11. Mds_workflow_service пользователю предоставьте любые дополнительные разрешения, необходимо выполнить операции потока, такие как разрешения на чтение и запись на другую базу данных в вашей системе.

Шаг
6: Обновление MDS рабочего процесса интеграции служба файл веб-конфигурации

Отредактируйте файл конфигурация веб услуг данных мастер включить имя сервера служб данных Master и базы данных и сборки пользовательского рабочего процесса. Если вы используете рабочего процесса
SharePoint, вы не должны включать сведения о сборке.

Отредактируйте файл
веб- конфигурация Master Data Services

Если используется проверка подлинности Windows не нужно указать пользователя или пароль в файле конфигурация . Кроме того если используется проверка подлинности SQL Server , укажите Идентификатор
пользователя и пароль имени входа SQL Server , имеет необходимые разрешения на базу данных Master Data Services.

1. На сервере, где установлены службы данных Master откройте повышенной командной строки.
   
2. Откройте Microsoft.MasterDataServices.Workflow.exe.config в блокноте C:Program файлыMicrosoft SQL ServerMaster данных ServicesWebApplicationbin.

3. 
Найти этот параметр:
< имя параметра = «connectionstring» serializeAs
= «Строка» >

1. Обновите значение для отражения сервера и базы данных. Если ваши установки SQL Server использует вариант-параметры сортировки, а затем имя базы данных, необходимо ввести в этом же вариант как в базе данных. К примеру, <value>
   Server = myServer; базы данных = myDatabase; встроенная безопасность = True </value>
2. Параметр ConnectionString добавьте другой <setting> тег для вашего настраиваемого рабочего процесса. Например:
   < имя параметра = «workflowtypeextenders» serializeAs
   = «Строка» >
   <value> TEST=MDSWorkflowTestLib.WorkflowTester,
   MDSWorkflowTestLib </value>
   </setting>
   следующих рекомендаций поможет вам построить ваш <setting> Метки:

• Внутренний текст <value> тег представляет собой Идентификатор < > = < имя типа сборки рабочего процесса >.
  
• < workflow ID > Это строка , используемая для идентификации этой сборки рабочего процесса при создании бизнес-правила в Master Data Manager.
• < имя типа сборки рабочего процесса > уточненное пространством имен имя класса рабочего процесса, а затем запятую, следуют отображаемое имя сборки.
  
• Если сборка строго именованной, вы также должны включать в себя информацию о версия и его PublicKeyToken.
• Вы можете включить несколько <setting> Теги, если вы создали несколько обработчиков рабочего процесса для различных типов рабочих процессов.
• Если вы используете рабочего процесса SharePoint, не нужно включать этот <setting> тег.

1. Сохраните и закройте файл.
2. В зависимости от confugration вашего сервера может появиться сообщение об ошибке «Отказано в доступе» при попытке сохранить файл. Если это происходит, временно отключите контроль учетных записей (UAC) на сервере. Чтобы
   сделать это, откройте панель управления, нажмите кнопку системы и безопасности. Под
   Противоминный центр, нажмите кнопку Изменения параметров контроля учетных записей пользователей. В
   Параметров контроля учетных записей пользователей диалоговое окно, слайд панели в нижней, так, что вы никогда не будете оповещены. Нажмите кнопку ОК. В диалоговом окне подтверждения нажмите кнопку Да. Перезагрузите компьютер и повторите выше
   процедуру для редактирования файла конфигурация . После сохранения файла, сбросить настройки UAC по умолчанию уровень.

Шаг
7: Установка и запуск службы рабочего процесса интеграции

Установите и запустите MDS рабочего процесса интеграции SQL Server служба.

Грант
на чтение и выполнение рабочего процесса пользователю MDS разрешение

В зависимости от конфигурация вашего сервера может потребоваться предоставить пользователю mds_workflow_service разрешения Чтение и выполнение на исполняемый файл служба MDS рабочего процесса
интеграции.

1. На сервере, где установлены службы данных Master откройте проводник Windows и перейдите в каталог C:Program файлыMicrosoft SQL ServerMaster Data ServicesWebApplicationbin.
2. Щелкните правой кнопкой мыши Microsoft.MasterDataServices.Workflow.exe и выберите
   Свойства.
3. В Свойства диалоговое окно, перейдите на
   безопасности щелкните изменить…., click
   Add…, и добавьте пользователя < имя_сервера > mds_workflow_service. Mds_workflow_service пользователю предоставляется разрешения Чтение и выполнение по умолчанию.
4. Нажмите кнопку ОК, чтобы закрыть все диалоговые окна, которые открыли.
5. На сервере, где установлены службы данных Master откройте повышенной командной строки.
6. Перейти к % windir%Microsoft.NET.
7. Если у вас есть более чем одну папку Framework, определить, какие самые последние и перейти к этой папке.
8. Перейдите к вложенной папке рамки, например Frameworkv4.0.30319.
9. Убедитесь, что InstallUtil.exe находится в папке.
10. Скопируйте путь для InstallUtil.exe, например: C:WindowsMicrosoft.NETFrameworkv4.0.30319InstallUtil.exe.
11. В командной строке перейдите в каталог C:Program файлыMicrosoft SQL ServerMaster Data ServicesWebApplicationbin.
12. Тип: C:WindowsMicrosoft.NETFrameworkv4.0.30319InstallUtil Microsoft.MasterDataServices.Workflow.exe
13. Нажмите клавишу ВВОД. Отображается диалоговое окно, запрашивать имя пользователя и пароль.
14. Введите < имя_сервера > mds_workflow_service имя пользователя и пароль, указанный при создании пользователя. Пользователь должен иметь разрешения Чтение и выполнение для всех сборок, которые обрабатывают рабочего процесса
    обработки, а также соответствующий доступ к любым ресурсам, испрашивались Ассамблеей обработчика рабочего процесса, такие, как доступ чтения и записи в базу данных, которую будет изменить обработчик рабочего процесса.
15. Нажмите кнопку ОК.
16. Из меню «Пуск» выберите команду выполнить.
17. Тип: Services.msc
18. Нажмите кнопку ОК. Оснастка «службы» в открывается.
    
19. Найдите служба под названием SQL Server MDS рабочий процесс интеграции и выберите его.
20. Нажмите кнопку начать связь.
21. После запуска служба , закройте оснастку службы.

Найти утилиту установки

Установка служба

Запустите службу

Шаг
8: Создать и применить бизнес-правила в Master Data Manager

Создаете бизнес-правило в Master Data Manager. Когда данных проверяется на соответствие бизнес-правило, заполняется очередью компонента Service Broker в базе данных Master Data Services. служба
периодически проверяет очереди, отправляет данные в обработчик указанного рабочего процесса, а затем очищается очереди.

создать и опубликовать бизнес-п��авило

Необходимо сначала создать и опубликовать бизнес-правило, которое будет запускать рабочий процесс при применении.

Следует убедиться, что ваш бизнес-правило содержит действия, изменяющие значения атрибутов, таким образом, чтобы правило имеет значение false, после того, как он был применен один раз. Например
бизнес-правило может иметь значение true, когда значение атрибута цены больше чем 500 и утверждено значение атрибута является пустым. Правило затем можно включить два действия: один, чтобы набор значение атрибута утверждено ожидания и один запускать рабочий
процесс.

Кроме того вы можете создать правило, использующее состояние «изменилась» и добавить ваши атрибуты для изменения групп слежения.
Примечание: эта функция не доступна в SQL Server 2008 R2 ноября сообщества Technology Preview и более ранних версий.

Чтобы создать бизнес-правило в Master Data Manager, необходимо иметь разрешение для функциональной области Системное администрирование и быть администратором моделей для модели, вы хотите создать
бизнес-правило для. Для получения дополнительной информации, смотрите администраторов (службы данных Master).

1. В Master Data Manager, щелкните Системное администрирование.
2. В строке меню выберите пункт Управление и нажмите бизнес-правила.
3. На странице обслуживание бизнес правила выберите модель, сущность, тип элемента и атрибут.
4. Нажмите Добавить бизнес-правило. Строка добавляется в таблица и будет выделена.
5. Нажмите Изменить выбранное бизнес-правило.
6. На панели Компоненты разверните узел Условия.
7. Перетащите узел условия IF область условий.
   
8. В панели сущности-особые атрибуты щелкните атрибут и перетащите его в область редактирования действий выбора атрибута label.
9. В области изменить условие заполните все поля и нажмите кнопку Сохранить элемент.
10. В области компонентов разверните узел действия.
11. В группе внешних действиеперетащите метка действия панели этого запустить рабочий процесс.
12. В области Атрибуты сущности щелкните любой атрибут и перетащите его в область редактирования действий выбора атрибута label. Этот атрибут не имеет отношения рабочего процесса.
13. В области действий, изменить, в списке Тип рабочего процесса введите тег, определяющий обработчик рабочего процесса. Чтобы запустить рабочий процесс SharePoint, введите SPWF. В противном случае введите метку, указанную
    пользователем в файл веб- конфигурация для сборки, например, тест.
14. При необходимости установите флажок включить член данных. Выберите это включить имена и значения атрибутов в XML, который передается обработчику рабочего процесса.
15. В поле узел рабочего процесса введите имя веб-сайта. Для рабочего процесса SharePoint это должно быть узла SharePoint. Например http://site_name. Для настраиваемого рабочего процесса это может не применяться, но могут быть
    использованы для Добавлено контекстное.
16. В поле Имя рабочего процесса введите имя рабочего процесса из Visual Studio. В обозревателе решений Visual Studio это значение указано в полеотображаемое имя. Для настраиваемого рабочего процесса это может не применяться,
    но могут быть использованы для Добавлено контекстное.
17. В нижней части страницы нажмите кнопку Сохранить.
18. В верхней части страницы нажмите кнопку назад. Откроется страница Бизнес правила обслуживания.
19. Нажмите кнопку Опубликовать бизнес-правила.
20. В диалоговом окне подтверждения нажмите кнопку ОК. Состояние правила изменится на Активно.

Применить бизнес-правила

Теперь вы должны применить бизнес-правила для ваших данных. Это действие передает данные в очередь компонента Service Broker.

1. На домашней странице диспетчера данных Master выберите обозреватель.
2. Измените сущность с членами, которые вы хотите проверить.
3. Нажмите кнопку Применить бизнес-правила.

Заполняется SQL Server очереди компонента Service Broker. Когда служба проверяет очереди, он отправляет данные обработчику рабочего процесса и очищает очередь.

Устранение сбоев в работе

При настройке службы данных Master для работы с рабочим процессом, могут возникнуть какие-либо из следующих вопросов.

Обработчик рабочего процесса не получает
данных

Если обработчик рабочего процесса не получает данных, вы можете попробовать отладки рабочего процесса служба или просмотра очереди компонента Service Broker, чтобы определить, если правильные
данные все еще находится в очереди.

Выполнить отладку службы рабочего процесса

1. Остановите служба , если она запущена.
2. Откройте командную строку.
3. Перейдите в место служба и запустите служба в консольном режиме, введя: Microsoft.MasterDataServices.Workflow.exe-консоль
4. Нажмите клавишу ВВОД.
5. В Master Data Manager, обновите этот атрибут и снова применить бизнес-правила . Подробные журналы отображаются в окноконсоли.

Просмотр очереди компонента Service Broker служба

Очереди компонента Service Broker, содержащий базовые данные передаются как часть рабочего процесса: mdm.microsoft/mdm/queue/externalaction. Эта очередь можно найти в SQL Server Management Studio,
открытия базы данных Master Data Services в обозревателе объектов, а затем открытие
Компонент Service Broker и
очереди. Щелкните правой кнопкой очередь и выберите выберите
Top 1000 строк для просмотра top 1000 строк в очереди.

Если служба очереди должным образом, этой очереди должен быть пустым.

Если имеются данные в очередь, которую вы хотите удалить, запустите следующий сценарий SQL, чтобы очистить очередь. MyDatabase необходимо присвоить имя вашей базы данных Master Data Services.

Объявите uniqueidentifier @ разговор

Хотя существует (выберите 1 из [myDatabase].
[МДМ].[microsoft/МДМ/очередь/externalaction])

начать

набор @ разговор = (выберите top 1 conversation_handle от [myDatabase]. [МДМ].[microsoft/МДМ/очередь/externalaction])

конец разговора @ разговор с очистки

конец

Пример
кода пользовательского рабочего процесса и XML-описание

Когда вы создаете библиотеку классов пользовательского рабочего процесса, создается класс, реализующий интерфейсMicrosoft.MasterDataServices.Core.Workflow.IWorkflowTypeExtender. Этот интерфейс
включает в себя один методStartWorkflow, который вызывается элементом MDS рабочего процесса интеграции служба при запуске рабочего процесса. StartWorkflow метод содержит два параметра:
workflowType содержит тип рабочего процесса вы ввели в Master Data Manager, и
dataElement содержит метаданные и данные элемент для элемент , вызвавшая рабочего процесса бизнес-правила.

Пример кода

В следующем кода примере показано как вы как реализовать StartWorkflow метод для извлечения атрибутов имя, код и LastChgUserName из XML-данных для элемента, который вызвал рабочего процесса бизнес-правила
и как вызывать хранимую процедуру для вставки их в другую базу данных.

с помощью системы;

с помощью
System.Collections.Generic;

с помощью
System.Linq;

с помощью
System.Text;

с помощью
System.IO;

с помощью
System.Data.SqlClient;

с помощью
System.Xml;

с помощью
Microsoft.MasterDataServices.Core.Workflow;

пространство имен MDSWorkflowTestLib

{

    государственные
класса WorkflowTester :
IWorkflowTypeExtender

    {

# Region IWorkflowTypeExtender членов

       
государственные void StartWorkflow (строка workflowType, System.Xml.XmlElement dataElement)

       
{

            / /
Extract атрибуты, мы хотим из данных элемента.

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

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

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

           
/ / Открыть подключение на базе рабочего процесса.

           
SqlConnection
workflowConn =
новый SqlConnection(@ «источник данных = < экземплярсервера >; Первоначальный
каталог = WorkflowTest; Встроенная безопасность =
True «);

            / / Создать команду для вызова хранимой процедуры, которая добавляет нового пользователя в базу данных рабочего процесса.

           
SqlCommand addCustomerCommand =
новый SqlCommand(«addnewcustomer», workflowConn);

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

addCustomerCommand.Parameters.Add (новый
SqlParameter(«@ имя», NameNode.InnerText));

addCustomerCommand.Parameters.Add (новый
SqlParameter(«@ код», CodeNode.InnerText));

addCustomerCommand.Parameters.Add (новый
SqlParameter(«@ EnteringUser», EnteringUserNode.InnerText));

            / / Выполнить команду.

            workflowConn.Open();

            addCustomerCommand.ExecuteNonQuery();

            workflowConn.Close();

        }

        # endregion

    }

}

Данные элемента XML

StartWorkflow метод получает блок XML, который содержит метаданные и данные о элемент , вызвавший рабочего процесса бизнес-правила. В следующем примере показано, как выглядит XML:

<ExternalAction>

<тип>тест<
/тип>

<SendData>1<
/SendData>

<Server_URL>это
мой тест!< /Server_URL>

<Action_ID>тест
рабочий процесс< /Action_ID>

<Model_ID>5<
/Model_ID>

<Model_Name>клиента<
/Model_Name>

<Entity_ID>34<
/Entity_ID>

<Entity_Name>клиента<
/Entity_Name>

<Version_ID>8<
/Version_ID>

<MemberType_ID>1<
/MemberType_ID>

<Member_ID>12<
/Member_ID>

<MemberData>

<ID>12</ID>

<Version_ID>8<
/Version_ID>

<ValidationStatus_ID>3<
/ValidationStatus_ID>

<ChangeTrackingMask>0<
/ChangeTrackingMask>

<EnterDTM>2011-02-25T20:16:36.650<
/EnterDTM>

<EnterUserID>2<
/EnterUserID>

<EnterUserName>имя_пользователя<
/EnterUserName>

<EnterUserMuid>EEF91D48-B673-4D83-B95F-5A363C11DE91<
/EnterUserMuid>

<EnterVersionId>8<
/EnterVersionId>

<EnterVersionName>VERSION_1<
/EnterVersionName>

<EnterVersionMuid>52B788C2-2750-4651-9DB0-2CB05A88AA5A<
/EnterVersionMuid>

<LastChgDTM>2011-02-25T20:16:36.650<
/LastChgDTM>

<LastChgUserID>2<
/LastChgUserID>

<LastChgUserName>имя_пользователя<
/LastChgUserName>

<LastChgUserMuid>EEF91D48-B673-4D83-B95F-5A363C11DE91<
/LastChgUserMuid>

<LastChgVersionId>8<
/LastChgVersionId>

<LastChgVersionName>VERSION_1<
/LastChgVersionName>

<LastChgVersionMuid>52B788C2-2750-4651-9DB0-2CB05A88AA5A<
/LastChgVersionMuid>

<имя>испытания
клиента< /имя>

<код>TC<
/код>

< /MemberData>

< /ExternalAction>

В следующей таблица описаны некоторые из содержащихся в этом XML-теги:

Тег	Описание
<Type>	Тип рабочего процесса вы ввели в Master Data Manager для определения настраиваемого рабочего процесса сборку для загрузки.
<SendData>	Значение типа Boolean, контролируемых включить членов данных в окне сообщения флажок в Master Data Manager. Значение 1 означает, что <MemberData> раздел отправляется; в противном случае <MemberData> раздел не отправляется.
<Server_URL>	Текст, введенный в рабочий сайт надписи в Master Data Manager.
<Action_ID>	Текст, введенный в имя рабочего процесса надписи в Master Data Manager.
<MemberData>	Содержит данные элемента, вызвавшим действиерабочего процесса. Это будет включать, только если значение <SendData> равен 1.
< Enterxxx>	Этот набор тегов содержит метаданные о создании членом, например, когда он был создан и который создал его.
< LastChgxxx>	Этот набор тегов содержит metadata о последнего изменения, внесенного членом, например, когда изменения и кто это сделал.
<Name>	Первый атрибут элемента, которое было изменено. Этот член содержит только имя и код атрибуты.
<Code>	Следующий атрибут элемента, которое было изменено. Если данный элемент содержит дополнительные атрибуты, они будут следовать этот один.

Для получения дополнительной информации:

http://MSDN.Microsoft.com/en-us/library/ee633763.aspx: Master Data Services документация

http://msdn.microsoft.com/en-us/sqlserver/ff943581.aspx: Центр разработчиков служб данных
Master

http://www.Microsoft.com/SQLServer/: SQL Server веб-сайт

http://TechNet.Microsoft.com/en-US/SQLServer/: SQL Server TechCenter

http://MSDN.Microsoft.com/en-US/SQLServer/: SQL Server DevCenter

Следующая статья:

SSAS — права доступа и MDS

Предыдущая статья:

Redmine — система управления проектами

К списку статей