Механизмы интеграции внутрикорпоративных справочников Текст научной статьи по специальности «Компьютерные и информационные науки»

№ 6 (36) 2011

К. С. Гудков, инженер ФГУП «Государственный научно-исследовательский

институт авиационных систем», г. Москва

Механизмы интеграции внутрикорпоративных справочников

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

Введение

Задача создания корпоративной системы поддержки нормативно-справочной информации (КСП НСИ) рано или поздно встает перед любым предприятием [1]. Перед ее созданием необходимо разделить совокупность используемых справочников на две группы: справочники, содержимое которых не зависит от специфики деятельности предприятия, и внутрикорпоративные справочники, формируемые в процессе деятельности предприятия. Общий алгоритм работы со справочниками из первой группы приведен в [2]. Для успешного использования справочников из второй группы необходимо провести интеграцию используемых до внедрения КСП НСИ справочников и утвердить протокол их обновления в рамках КСП НСИ. Как и в работе [2], предполагается наличие в корпоративной информационной системе консолидированной базы данных нормативно-справочной информации. Тиражирование данных при помощи гетерогенной системы репликации данных остается таким же, как и в случае справочников из первой группы. Общий механизм управления нормативно-справочной информацией показан на рис. 1. К недостаткам существующих до внедрения КСП НСИ внутрикорпоративных справочников, которые следует учитывать при интеграции данных, следует отнести неполноту, неактуальность, а также возможную противоречи-

Внутрикорпоративные справочники

Внешние источники

нормативно-справочной

информациии

ПСИВС

АСИВС

Внутренние справочники

Внешние справочники

LRO-таблицы

LRO-таблицы

HTTP

Сокеты поверх TCP/IP

DCOM

Территориально удаленные участки Рис. 1. Механизм управления НСИ

14

№ 6 (36) 2011

вость содержащихся в них данных [3]. Цель настоящей статьи — подробный анализ процесса интеграции внутрикорпоративных справочников с решением наиболее типичных проблем, возникающих на практике. В рамках анализа будет приведена созданная для описания интеграции справочников математическая модель, описан реализующий ее программный продукт, приведены создаваемые с его помощью SQL-сценарии на языке Transact SQL. В предлагаемой математической модели использованы те же обозначения, что и в [2, 4]. Интеграция внутрикорпоративных справочников позволяет избежать финансовых потерь, связанных с неполнотой, неактуальностью и противоречивостью данных в исходных справочниках, повысить точность отчетных данных и принять более правильные управленческие решения.

Сравнение естественных и суррогатных первичных ключей

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

Первичный ключ (primary key) — это один или несколько атрибутов, которые уникально характеризуют кортеж отношения. Если используется несколько атрибутов, первичный ключ называется составным (composite primary key). Если первичный ключ полностью составлен из атрибутов, несущих смысловую нагрузку в рамках предметной области, он называется естественным (natural primary key). Если первичный ключ состоит из атрибутов, добавленных с целью уникальной идентификации кортежа и не несущих смысловой нагрузки в рамках предметной области, он называется суррогатным (artificial primary key). В большинстве практических задач естественный первичный ключ является составным, а суррогатный первич-

ный ключ — атомарным. При дальнейшем § сравнении имеется в виду именно такая ситуация. ^

Приведем аргументы против использования естественных первичных ключей:

• Сложности по написанию SQL-сценари-ев по добавлению и модификации записей таблиц (например, разработчики вынуждены использовать конструкции вида "where A.a1 = B.a1 and A.a2 = B.a2 and ... A. an = B.An"). Они приводят к увеличению срока создания прикладного программного обеспечения и увеличивают вероятность возникновения ошибок в SQL-сценариях.

• Неэффективность индексации при использовании естественных первичных ключей по сравнению с индексацией при использовании суррогатных первичных ключей — в большинстве случаев запросы к таблицам на основе естественных первичных ключей выполняются медленнее запросов к таблицам, построенным на основе суррогатных первичных ключей.

• Сложности при изменении полей, входящих в состав естественного первичного ключа (например смена номера паспорта). Каждое такое изменение — это искусственное появление новой сущности, никак не связанной с имеющейся сущностью. Предположим, что человеку выдали второй паспорт, а номер паспорта повсеместно является естественным первичным ключом. Законопослушный гражданин не сможет воспользоваться собственным банковским счетом, получить квалифицированную медицинскую помощь, а мошенник уйдет от ответственности.

• Сложности, связанные с исправлением ошибок при вводе естественного первичного ключа при ручном заполнении таблиц баз данных.

Изложим аргументы против использования суррогатных первичных ключей:

• Отсутствие смысловой нагрузки суррогатного ключа. При его создании в эквивалентных локальных таблицах территориально удаленного участка информационной системы одинаковые кортежи могут иметь

№ 6 (36) 2011

различные значения суррогатного ключа, а принципиально разные — одинаковые. Данный недостаток характерен для суррогатных первичных ключей на основе автоинкрементных полей или их аналогов. Его можно устранить путем использования при проектировании в последнее время набирающих популярность UUID (Universally Unique IDentifier) значений [5, 6].

• Несемантическое наименование полей, используемых в качестве суррогатных первичных ключей (например ID). В результате при запросах к базе данных возможно создание ложных, вводящих в заблуждение связей. В качестве примера приведем использование конструкции "where A.ID = B.ID", не несущей смысловой нагрузки. Возникновение подобной ситуации при использовании естественных первичных ключей значительно менее вероятно.

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

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

00

g могут быть показаны с использованием как

5 естественных, так и суррогатных первичных

§ ключей.

6

и Ч

=g Математическая модель

s

S^ Пусть необходимо объединить справоч-|= ники, у которых множество совпадающих ат-| рибутов включает естественный первичный ^ ключ. Введем следующие обозначения: | • R( A1,..., An, B1,...,Bm) — первое из объе-■§. диняемых отношений; & • S(D1,...,Dn ,C1,...,Ck) — второе из объеме диняемых отношений; | • Т(Д,..., An ,B1,...,Bm ,Cv...,Ck) — результи-g рующее отношение. | Единственное отличие атрибутов A1,..., An и D1,..., Dn — это название. Естественный

первичный ключ — это некоторое подмножество атрибутов А1,...,An для отношения R и некоторое подмножество атрибутов D1,...,Dn для отношения S. Приведем алгоритм интеграции справочников R и S:

• Переименуем атрибуты D1,...,Dn отношения S в соответствующие им атрибуты из набора A1,..., An. При этом на основании объединения естественных первичных ключей отношений R и S можно утверждать, что существует естественный первичный ключ A1,..., А,,l < n для обоих отношений. Далее будем считать, что атрибуты перенумерованы в соответствии с естественным первичным ключом. Это допустимо, так как в реляционной модели данных кортежи считаются неупорядоченными.

• Выберем из отношения R те кортежи, для которых не существует соответствующих значений естественного первичного ключа А1,...,А, в отношении S. Соответствующий предикат обозначим С1, а оператор выбора — oc(R).

• Выберем из отношения S те кортежи, для которых не существуют соответствующие значения естественного первичного ключа А1,...,А, в отношении R. Соответствующий предикат обозначим C2, а оператор выбора — oc2(S).

• Возьмем декартово произведение полученного в результате переименования атрибутов D1,...,Dn отношения S и исходного отношения R: L(А1,...,An,B1,...,Bm,C1,...,Ck) = R(А.....An,B.....Bm)x S(аА.....An,C.....Ck).

• Выберем из отношения L те кортежи, для которых выполняются равенства R.A1 = S.A1, R.A2 = S.A2,..., R.A, = S.A,. Соответствующий предикат обозначим C3, а оператор выбора — оСз (L).

• Отношение T выражается посредством объединения операторов выбора следующим образом:

T (А.....An,B.....Bm C.....Ck ) =

= Oc1 (R) UOc2(S) UOc3(L).

Общая формула, выражающая отношение T через отношения R и S, записывается так:

№ 6 (36) 2011

Т (А.....Ап&.....Вт с.....с) =

= °С1(Я(А.....А , В.....Вт,)) и

иас2^(А.....Ап,с1.....ск)(^Ц,...,Ц1 ,с1,...,ск))) и

иаСз(Я( А.....А, В.....Вт) •

А.....А„С1.....Ск,с1,...,ск))).

Объединение справочников, в которых использованы суррогатные первичные ключи

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

• Удаление суррогатного первичного ключа из отношения Я.

• Удаление суррогатного первичного ключа из отношения S.

• Получение отношения Т аналогично ранее рассмотренному случаю:

т (А.....АВ1.....вт с.....ск) =

= ас (Я) и ас^) и а^).

• Добавление в отношение Т нового суррогатного первичного ключа.

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

Противоречия в данных

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

• Обнаружение противоречий в данных внутрикорпоративных справочников при помощи автоматизированных средств.

• Сбор экспертов в предметной области справочников, в которых были обнаружены противоречия.

• Разрешение противоречий исходя из специфики предметной области.

• Внесение изменений в исходные справочники с устранением противоречий.

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

Программная реализация

ПСИВС (программное средство интеграции внутренних справочников) — это программный продукт, созданный в среде программирования Delphi, который реализует описанный выше алгоритм объединения справочников на практике. Входными данными для ПСИВС служат две таблицы реляционной базы данных, для которых существуют естественные первичные ключи, и таблицы соответствий для их столбцов. Причем естественные первичные ключи для обеих таблиц должны быть подмножеством столб-

№ 6 (36) 2011

»

0

со

1

U

к

1

со

5

6

SS

I

.S

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

• Список найденных противоречий в объединяемых таблицах. Данный режим рекомендуется использовать перед каждой процедурой объединения справочников. В частности, отсутствие ошибок на данном этапе гарантирует, что при совпадении значений полей, входящих в состав первичного ключа, будут совпадать и значения совпадающих атрибутов. В результате, в отличие от приведенной математической модели, в создаваемых при помощи ПСИВС SQL-сценариях для проверки идентичности записей в объединяемых таблицах будет проводиться сравнение только по ключевым полям.

• SQL-сценарии для выполнения объединения переданных таблиц на языке Transact SQL. При реализации ПСИВС рассматривалась альтернатива: создание SQL-сцена-риев на заданном пользователем диалекте языка SQL или использование предварительной конвертации «ФОРМАТ СУБД — CSV — ФОРМАТ MS SQL». Было отдано предпочтение второму варианту. В результате перед объединением таблиц, находящихся под управлением СУБД, отличных от MS SQL Server, рекомендуется при помощи ПСИВС преобразовать их к формату MS SQL Server, а уже затем использовать основную функциональность ПСИВС для интеграции данных.

• Набор данных, соответствующий объединению переданных таблиц. При помощи всплывающего меню пользователь оп-

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

• Созданная в заданной пользователем базе данных под управлением СУБД MS SQL Server таблица, соответствующая объединению переданных справочников.

Практический пример интеграции таблиц при помощи ПСИВС

Пусть необходимо объединить таблицу FirstTable (соответствует отношению R) и таблицу SecondTable (соответствует отношению S). В обеих таблицах использован имеющий идентичный смысл в данной предметной области составной первичный ключ (Primary1, Primary2). Атрибуты Attr1 и Attri-bute1 различаются только наименованием. Атрибуты Attribute2 и Attribute3 уникальны для первой таблицы, а атрибут Attr2 — для второй. Сценарии по созданию таблиц показаны в листинге 1, а их содержимое — в табл. 1 и 2.

В рассматриваемом практическом примере входными параметрами для ПСИВС являются:

• Параметры соединения с реляционной таблицей FirstTable, которая находится под управлением СУБД MS SQL Server.

• Параметры соединения с реляционной таблицей SecondTable, которая находится под управлением СУБД MS SQL Server.

• Список соответствий между полями FirstTable и SecondTable. Для рассматриваемого примера он состоит из одной строки вида Attr1=Attribute1.

Таблица 1

Первая из объединяемых таблиц (FirstTable)

Primary1 Primary2 Attribute1 Attribute2 Attribute3

1 1 5 10 15

1 2 3 8 19

2 1 4 96 7

18

№ 6 (36) 2011

Листинг 1

if isnull(objectproperty(object_id('dbo.FirstTable') ,'IsTable'), -1) = 1

drop table [dbo].[FirstTable]

create table [dbo].[FirstTable] (

[Primary1] varchar(10) COLLATE Cyrillic_General_ CI_AS NOT NULL,

[Primary2] varchar(10) COLLATE Cyrillic_General_ CI_AS NOT NULL,

[Attribute1] int NULL,

[Attribute2] int NULL,

[Attribute3] int NULL);

alter table [dbo].[FirstTable]

add constraint[PK_FirstTable] primary key clustered ([Primaryi],

[Primary2]);

if isnull(objectproperty(object_id('dbo.SecondTable' ),'IsTable'), -1) = 1

drop table [dbo].[SecondTable]

create table [dbo].[SecondTable] (

[Primary1] varchar(10) COLLATE Cyrillic_General_CI _AS NOT NULL,

[Primary2] varchar(10) COLLATE Cyrillic_General_CI _AS NOT NULL,

[Attr1] int NULL,

[Attr2] int ); NULL

alter table [dbo].[SecondTable]

add constraint[PK_SecondTable] primary key clustered ([Primary!],

[Primary2]);

Таблица 2 в качестве формата выходных данных было выбрано получение SQL-сценариев по объединению таблиц FirstTable и Sec-ondTable. Созданные для тестового примера SQL-сценарии показаны в листинге 2. Результаты показаны в табл. 3.

Код содержит следующие логические части:

• Объявление табличных переменных @FirstTable и @SecondTable. Перенос в них содержимого объединяемых справочников.

Таблица 3

Результат объединения

Primary1 Primary2 Attribute1 Attribute2 Attribute3 Attr2

1 1 5 10 15 25

1 2 3 8 19 9

2 1 4 96 7 NULL

3 1 8 NULL NULL 64

Вторая из объединяемых таблиц (SecondTable)

Primary1 Primary2 Attr1 Attr2

1 1 5 25

1 2 3 9

3 1 8 64

№ 6 (36) 2011

Листинг 2

declare @FirstTable table(Primary1 varchar(10) Attribute1 int, Attribute2 int, Attribute3 int) declare @SecondTable table(Primary1 varchar(10) Attribute! int. Attr2 int)

Primary2 varchar(10) Primary2 varchar(10)

use MergeTest

insert into @FirstTable

select Primaryl, Primary2, from "FirstTable"

Attribute!, Attribute2, Attribute3

use MergeTest

insert into @SecondTable

select Primaryl, Primary2, Attrl as Attributel, Attr2 from "SecondTable"

select l.Primaryl, l.Primary2, l.Attributel, l.Attribute2, l.Attribute3, r.Attr2

from @FirstTable as l, @SecondTable as r

where (l.Primaryl = r.Primaryl) and (l.Primary2 = r.Primary2) union

select l.Primaryl, l.Primary2, l.Attributel, l.Attribute2, l.Attribute3, null as Attr2

from @FirstTable as l where not exists (select *

from @SecondTable as r

where (l.Primaryl = r.Primaryl) and (l.Primary2 = r.Primary2) )

select r.Primaryl, r.Primary2, r.Attributel, null as Attribute2, null as Attribute3, r.Attr2 from @SecondTable as r where not exists (select *

from @FirstTable as l

where (l.Primaryl = r.Primaryl) and (l.Primary2 = r.Primary2) )

CO

=1 &

is §

I

.s

• Первый DML оператор select: извлечение строк, содержащихся в обеих таблицах. В математической модели ему соответствует оператор выбора aC (L). В практическом примере он возвращает набор данных, состоящий из первых двух записей табл. 3.

• Второй DML оператор select: извлечение строк, содержащихся в первой таблице, но отсутствующих во второй таблице. В математической модели ему соответствует оператор выбора aC (R). В практическом примере он возвращает набор данных, состоящий из третьей записи табл. 3.

20

№ 6 (36) 2011

• Третий DML оператор select: извлечение строк, содержащихся во второй таблице, но отсутствующих в первой таблице. В математической модели ему соответствует оператор выбора aC (S). В практическом примере он возвращает набор данных, состоящий из четвертой записи табл. 3.

• Объединение результатов запросов union. В математической модели ему соответствует образование отношения T по формуле: T = aC (R)uoC (S)uoC (L). В практическом примере результатом объединения является набор данных, показанный в табл. 3.

Поддержка внутренних справочников в КБД НСИ

В результате работы ПСИВС в рамках консолидированной базы данных нормативно-справочной информации были созданы таблицы, соответствующие ранее используемым внутрикорпоративным справочникам. После их создания любое использование предыдущих справочников должно быть прекращено. В противном случае справочники в составе КБД НСИ рискуют оказаться еще одним набором справочников, находящимся в противоречиях с остальными и содержащим неполные и неактуальные данные. Так как внутренние справочники формируются в процессе деятельности предприятия, любые изменения в них должны вноситься специально назначенными экспертами предметной области, отвечающими за их поддержку. При этом в целях унификации алгоритмов работы с КБД НСИ рекомендуется тот же, что и в статье [2], механизм поддержки репликации посредством LRO-таблиц. Механизм распространения данных можно представить следующим образом:

drk ^ сГк ^ cSm ^ .

На основании изменений в справочнике КБД НСИ drk при помощи триггеров формируется LRO-таблица изменений crk

. По известной связи между справочником § drk в составе КБД НСИ и справочником /го территориально удаленного участка s'm ^ формируется набор данных с^гп, содержащий изменения, которые необходимо внести в справочник локальной базы данных территориально удаленного участка s'т. В справочник slm вносятся изменения, соответствующие набору данных с^гп. Связь между сгк и с^т отражена в формуле:

cSm [ p - 1,p] = Oc (п

C\"'A,A2..AN VHS(A1A2...A

(Ps

(°CTlme (СГк )))).

При помощи р и р -1 пронумерованы моменты синхронизации данных между локальной базой данных /-го территориально удаленного участка и КБД НСИ. Набор данных с^гп[р-1,р] содержит только изменения, накопленные в промежутке между этими моментами. Оператор оСТтпе отвечает за выборку в таблице сгк изменений, произошедших между моментами времени р и р -1.

Пусть, например, справочники нескольких территориально удаленных участков формируются на основе одного и того же справочника. Изменения в этом внутреннем справочнике в составе КБД НСИ фиксируются в LRO-таблице. Времена синхронизации локальных баз данных территориально удаленных участков и консолидированной базы данных нормативно-справочной информации могут не совпадать. Кортеж LRO-таблицы может быть удален только после того, как соответствующие ему изменения внедрены в каждый из рассматриваемых справочников территориально удаленных участков. Чтобы избежать повторной передачи одних и тех же изменений, и используется оператор оСТтте. В реальности базы данных могут разрушаться. Для минимизации связанных с этим потерь создаются их резервные копии. Предположим, что в справочник локальной базы данных территориально удаленного участка корпоративной информационной системы были переданы данные справочника КБД НСИ, которые не вошли в последнюю резервную копию. Тогда после

21

№ 6 (36) 2011

восстановления КБД НСИ из резервной копии данные ее справочника не будут согласованы с данными справочника территориально удаленного участка. Чтобы избежать возникновения этой проблемы, достаточно тиражировать только те данные, которые уже были сохранены в резервной копии. Тогда на момент разрушения данных, которые одновременно и были тиражированы, и не были сохранены, не будет, т. е. согласованность данных не нарушится. Оператор выбора ас, оператор проекции кАЛ А и оператор переименования р^д^ д) отвечают за связь между таблицами Сгк и .

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

этого и используются операторы aC

« »

0 «

1

U

Ё

1

«

5

6

SS S

I

S .s

и A ) ■ Докажем, что произвольные изменения, которые происходят в справочнике в составе КБД НСИ, могут быть отражены в реляционной таблице.

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

Data х Operation ^ Data■

Входными данными для нее являются дублеты, состоящие из модифицируемого кортежа и типа операции, т. е. любое изменение можно представить в виде строки, состоящей из данных кортежа и числового поля, соответствующего типу изменения (например, -1 для удаления записи; 0 для модификации записи; 1 для добавления записи). Это означает, что существует изоморфизм между множеством функций, производящих изменения в справочнике, и реляционной таблицей. Следовательно, произвольные изме-

нения, происходящие в справочнике, могут быть отражены при помощи реляционной таблицы, и переходы drk ^ crk и cs'm ^ s!m правомерны.

Заключение

В работе рассмотрена задача интеграции внутрикорпоративных справочников в рамках проблемы управления нормативно-справочной информации корпоративных информационных систем. Описана созданная для ее решения математическая модель и разработанный на ее основе программный продукт ПСИВС. Входными данными для этого продукта являются таблицы, которые необходимо объединить, а выходными могут служить список противоречий в объединяемых таблицах, SQL-сценарии для их объединения, результирующий набор данных или таблица под управлением СУБД MS SQL Server.

aa2.an Список литературы

1. Joshi A. MDM Governance: A Unified Team Approach // Cutter It Journal. 2007. Vol. 20. № 9.

2. Гудков К. С. Математическая модель управления справочниками административно-территориального деления стран СНГ в корпоративных информационных системах // Прикладная информатика. 2010. № 5 (29).

3. Сеничев Г. С., Виер И. В., Курбан В. В., Кап-цан Ф. В., Урцев В. Н, Фомичев А. В. Корпоративная система нормативно-справочного сопровождения // Сталь. 2005. № 5.

4. Гарсиа-Молина Г., Ульман Д., Уидом Д. Системы баз данных. М.: Вильямс, 2003. — 1088 с.

5. Hunt T. D. Implementing a UUID Primary Key in a Distributed Email Client // Proceedings of the 1st Annual Conference of Computing and Information Technology Education and Research in New Zealand (CITRENZ) / 23rd Annual Conference of the National Advisory Committee on Computing Qualifications. Hamilton, New Zealand, 2010.

6. Hunt T. D. Natural or artificial primary key // New Zealand Journal of Applied Computing and Information Technology. 2010. № 14 (1).

22