В этой статье я хотел бы поделиться своим первым профессиональным опытом применения 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-системе.
Формальные требования бизнес-заказчика можно кратко описать следующим образом:
На основании имеющихся вводных данных и бизнес-требований заказчику было предложено следующее решение:
Итак, поехали по-порядку. Сначала создадим в хранилище данных новое измерение «Произвольные подразделения» (dbo.dimDerivedDivisions) и свяжем его с измерением «Сотрудники» (dbo.dimEmploees). SQL-скрипт для этой задачи выглядит следующим образом:
Теперь добавим новое измерение в многомерную модель данных. Для этого откроем проект многомерной модели данных в SQL Server Data Tools и добавим новую таблицу dbo.dimDerivedDivisions в Data Source View. Результат выглядит следующим образом:
Чтобы не уходить далеко от темы, кратко опишу процесс доработки многомерной модели. В многомерной модели данных создаем новое измерение «Произвольные подразделения», настраиваем связь нового измерения с существующими таблицами фактов через измерение «Сотрудники», деплоим и процессим куб:
Теперь все структуры данных для подготовки аналитической отчетности в разрезе нового измерения «Произвольные измерения» готовы, приступаем к самому главному — к настройке 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:
Итак, модель «Сотрудники» (Employees) и сущности «Сотрудники» (Employees) и «Подразделения» (Divisions) готовы, теперь необходимо наполнить их данными. Сущность «Подразделения» (Divisions) пользователи будут заполнять вручную. Для демонстрации этого процесса перейдем на главную страницу web-интерфейса Master Data Services, в подразделе Information Worker Tasks выберем модель Employees и перейдем по ссылке Explorer:
Выберем иерархию «Подразделения» (Divisions):
Выберем тип элементов, которыми мы хотим управлять. Сначала создадим несколько групповых элементов (Consolidated Member):
Нажмем кнопку Add, введем наименование подразделения-группы «Отдел продаж» и нажмем кнопку OK:
Аналогично добавим другие подразделения и, таким образом, создадим структуру подразделений, изображенную на следующем рисунке:
Обратите внимание, жирным шрифтом выделены подразделения-группы, а нежирным — конечные элементы. На одном уровне иерархии могут быть как те, так и другие типы элементов.
Теперь необходимо импортировать данные о сотрудниках в Master Data Servises из существующего хранилища данных ( для дальнейшего сопоставления сотрудников и подразделений, и экспорта этих данных обратно в хранилище). Для загрузки данных в MDS в SQL базе данных, обеспечивающей работу Master Data Servises, существуют специальные промежуточные таблицы (Staging Tables), в которые мы можем вставить данные SQL-запросом или создать специальный SSIS-пакет, который будет импортировать новые записи о сотрудниках из хранилища данных во временные таблицы для их дальнейшей обработки в Master Data Servises. Откроем SSMS и найдем временные таблицы в базе данных Master Data Servises. Вот они:
В качестве примера импортируем произвольные 10 записей о сотрудниках из хранилища данных в промежуточную таблицу базы данных Master Data Services. Для этого выполним следующий SQL-запрос:
Вернемся в web-интерфейс Master Data Services и на главной странице перейдем по ссылке Intergation Management:
В открывшемся окне видим пакет Employees_Leaf_Batch00001, который был только что создан SQL-запросом. Запустим его, нажав на кнопку Start Batches:
После отработки пакета увидим вот такую информацию о статусе, времени начала и окончания выполнения, ошибках:
Теперь давайте перейдем в режим управления данными и посмотрим, как загрузились записи о сотрудниках из промежуточной таблицы. Для этого перейдем на главную страницу web-интерфейса Master Data Services, в подразделе Information Worker Tasks выберем модель Employees и перейдем по ссылке Explorer. В открывшемся окне видим, что новые данные о сотрудниках были добавлены в Master Data Services и находятся в состоянии Waiting to be validated:
Обратите внимание, что данные о подразделении (Division) у сотрудников не заполнены. Нам необходимо для каждого сотрудника выбрать подразделение, в котором он работает и нажать на кнопку ОК:
После того как данные о подразделениях и принадлежности сотрудников к подразделениям введены, необходимо импортировать их обратно в хранилище данных. Для этого необходимо создать специальные представления (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, с помощью которого в будущем могут быть решены задачи, связанные с обработкой и хранением эталонной НСИ.
Вводные данные
Мой клиент использует 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, с помощью которого в будущем могут быть решены задачи, связанные с обработкой и хранением эталонной НСИ.
