Метод моделирования "Свод данных"

Формирование сущностей-связей

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

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

Таблица "Позиции заказа" ( OrderDetails ) находится в отношении "многие ко многим", и поэтому для нее будет построена сущность-связь "Связь_Позиции_заказа" ( LNK_OrderDetails ).

Таблица "Заказы" ( Orders ) является родительской таблицей для таблицы "Позиции заказа" OrderDetails, поэтому для нее будет построена сущность-связь "Связь_Заказы" ( LNK_Orders ).

Таблица "Покупатель Покупатель" ( CustomerCustomerDemo ) находится в отношении "многие ко многим", и поэтому для нее будет построена сущность-связь "Связь_Покупатель_Покупатель" ( LNK_CustomerCustomerDemo ).

Таблица "Служащий Территория" ( EmployeeTerritories ) находится в отношении "многие ко многим", и поэтому для нее будет построена сущность-связь "Связь_Служащий_Территория" ( LNK_EmployeeTerritories ).

Таблица "Территория" ( Territories ) находится в отношении "многие ко многим", и поэтому для нее будет построена сущность-связь "Связь_Территория" ( LNK_Territories ).

Продолжим исследование модели учебного примера. Нетрудно заметить, что некоторые таблицы, являющиеся кандидатами в сущности-концентраторы, находятся в отношении "родитель-потомок" по отношению к внешнему ключу. Для таблицы "Товары" ( Products ) это ключи "Идентификатор категории" ( CategoryID ) и "Идентификатор поставщика" ( SupplierID ). Поэтому целесообразно построить таблицу LNK_Products сущности-связи, включающую "Идентификатор товара" ( ProductID ), "Идентификатор поставщика" ( SupplierID ) и "Идентификатор категории" ( CategoryID ). Заметим, что для этой таблицы не требуется суррогатный ключ, поскольку "Идентификатор товара" ( ProductID ) является достаточным для представления поставщиков и категорий.

Таблица "Служащие" ( Employees ) имеет рекурсивное отношение. Для его представления введем таблицу сущности-связи "Связь_Служащие" ( LNK_ Employees ).

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

увеличить изображение
Рис. 18.11. Идентификация сущностей-концентраторов и сущностей-связей модели "Свод данных" для учебного примера

Таблица БД для сущности-связи создается командой CREATE TABLE, как показано для связи "Связь_Товары" ( LNK_Products ) ниже. Аналогично в БД создаются остальные сущности-связи модели.

CREATE TABLE LNK_Products (
ProductID int NOT NULL,
CategoryID int NOT NULL,
SupplierID int NOT NULL,
CTO_LOAD_DTS datatime NOT NULL,
CTO_REC_SRC nvarchar(20) NOT NULL,
PRIMARY KEY (ProductID),
FOREING KEY(SupplierID) REFERENCES HUB_Suppliers,
FOREING KEY(CategeoryID) REFERENCES HUB_Categories
);

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

Формирование сущностей-сателлитов

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

• таблица "Категории" (Categories) - сущность-сателлит CAT_CATEGORIES ;
• таблица "Товары" (Products) - сущность-сателлит CAT_PRODUCTS ;
• таблица "Поставщики" (Suppliers) - сущность-сателлит CAT_SUPPLIERS ;
• таблица "Заказы" (Orders) - сущность-сателлит CAT_ORDERS ;
• таблица "Покупатели" (Customers) - сущность-сателлит CAT_CUSTOMERS ;
• таблица "Грузоперевозчики" (Shippers) - сущность-сателлит CAT_SHIPPERS ;
• таблица "Служащие" (Employees) - сущность-сателлит SAT_EMPLOYEES ;
• таблица "Территории" (Territories) - сущность-сателлит SAT_TERRITORIES ;
• таблица "Регион" (Region) - сущность-сателлит SAT_REGION ;
• таблица "Демография покупателей" (CustomerDemographics) - сущность-сателлит SAT_CUSTOMERDEMOGRAPHICS.

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

Теперь мы можем объединить сущности-сателлиты с сущностями-концентраторами и сущностями-связями в схеме модели, как показано на рис. 18.12.

Рис. 18.12. Идентификация сущностей-концентраторов, сущностей-связей и сущностей-сателлитов модели "Свод данных" для учебного примера

увеличить изображение
Рис. 18.12. Идентификация сущностей-концентраторов, сущностей-связей и сущностей-сателлитов модели "Свод данных" для учебного примера

Таблица БД для сущности-сателлита создается командой CREATE TABLE, как показано для сателлита "Сателлит_Товары" ( CAT_Products ) ниже. Аналогично в БД создаются остальные сущности-сателлиты модели.

CREATE TABLE SAT_Products (
ProductID int NOT NULL,
PRD_LOAD_DTS DateTime NOT NULL,
QuantityPerUnit nvarchar(20),
UnitPrice money,
UnitsInStock smallint,
UnitsOnOrder smallint,
ReOrderLevel smallint,
Discontinued bit,
PRD_REC_SRC nvarchar(20) NOT NULL,
PRIMARY KEY (ProductID, PRD_LOAD_DTS)
FOREING KEY (ProductID) REFERENCES HUB_Products
);

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

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

Заполнение данными объектов "Свода данных"

На практике для заполнения объектов "Свода данных" целесообразно использовать виртуальные таблицы или представления, по одному на каждый объект модели.

В сущности-концентраторы данные только вставляются: при создании ХД заносятся бизнес-ключи. Сущности-связи заполняются аналогичным образом. В сущности-сателлиты данные вставляются, когда происходят изменения данных в системах источниках.

При загрузке сущности-концентратора выбираются только те бизнес-ключи (вместе с их суррогатными ключами), которых еще нет в концентраторе. Для реализации этого процесса можно использовать представление, приведенное ниже, на примере "Концентратора_Категории".

CREATE VIEW V_INS_HUB_CATEGORIES AS

SELECT DISTINCT
A.CATEGORYID,
GETDATE() LOAD_DATE,
'NORTHWIND' RECORD_SOURCE
FROM NORTHWIND..[CATEGORIES] A with (NOLOCK)
WHERE NOT EXISTS
(SELECT * FROM HUB_CATEGORIES WITH (NOLOCK))

При загрузке сущности-связи выбираются только те составные ключи (вместе с их суррогатными ключами), которых еще нет в связи. Для реализации этого процесса можно использовать представление, приведенное ниже, на примере "Связь_Заказы".

CREATE VIEW V_INS_LNK_ORDERS AS
SELECT DISTINCT
A.ORDERID,
A.CUSTOMERID,
A.EMPLOYEEID,
A.SHIPVIA,
GETDATE() LOAD_DATE,
'NORTHWIND' RECORD_SOURCE
FROM NORTHWIND..[ORDERS] A with (NOLOCK)
WHERE NOT EXISTS
(SELECT * FROM LNK_ORDERS WITH (NOLOCK))

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

CREATE VIEW V_UPD_SAT_EMPLOYEES AS SELECT
A.EMPLOYEEID,A.LASTNAME, A.FIRSTNAME, A.TITLE, A.TITLEOFCOURTESY,
A.BIRTHDATE,
A.HIREDATE, A.ADDRESS, A.CITY, A.REGION, A.POSTALCODE, A.COUNTRY,
A.HOMEPHONE, A.EXTENSION, A.PHOTO, A.NOTES, A.REPORTSTO,
A.PHOTOPATH, GETDATE() LOAD_DATE, 'northwind' RECORD_SOURCE
FROM northwind..[employees] A with (NOLOCK),
SAT_EMPLOYEES B with (NOLOCK)
WHERE A.EMPLOYEEID = B.EMPLOYEEID
AND (isnull(A.LASTNAME,'x') != isnull(B.LASTNAME,'x')
OR isnull(A.FIRSTNAME,'x') != isnull(B.FIRSTNAME,'x')
OR isnull(A.TITLE,'x') != isnull(B.TITLE,'x')
OR isnull(A.TITLEOFCOURTESY,'x') != isnull(B.TITLEOFCOURTESY,'x')
OR isnull(A.BIRTHDATE,convert(datetime,'01/01/1960')) !=
         isnull(B.BIRTHDATE,convert(datetime,'01/01/1960'))
OR isnull(A.HIREDATE,convert(datetime,'01/01/1960')) !=
         isnull(B.HIREDATE,convert(datetime,'01/01/1960'))
OR isnull(A.ADDRESS,'x') != isnull(B.ADDRESS,'x')
OR isnull(A.CITY,'x') != isnull(B.CITY,'x')
OR isnull(A.REGION,'x') != isnull(B.REGION,'x')
OR isnull(A.POSTALCODE,'x') != isnull(B.POSTALCODE,'x')
OR isnull(A.COUNTRY,'x') != isnull(B.COUNTRY,'x')
OR isnull(A.HOMEPHONE,'x') != isnull(B.HOMEPHONE,'x')
OR isnull(A.EXTENSION,'x') != isnull(B.EXTENSION,'x')
OR isnull(CONVERT(varbinary(2000),A.PHOTO),0) != isnull(CONVERT(varbinary(2000),B.PHOTO),0)
OR isnull(CONVERT(varchar(2000),A.NOTES),'x') != isnull(CONVERT(varchar(2000),B.NOTES),'x')
OR isnull(A.REPORTSTO,0) != isnull(B.REPORTSTO,0)
OR isnull(A.PHOTOPATH,'x') != isnull(B.PHOTOPATH,'x')
)

Представления работают хорошо, когда БД-источник и "Свод данных" являются сущностями одной реляционной БД. Если это не так, то возможны два решения: 1) применение промежуточной области (stage) для источника данных так, чтобы при этом представления могли быть использованы; 2) применение ETL-инструментов для преобразования, сравнения и загрузки данных.

Резюме

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

"Свод данных" имеет три основных "строительных блока".

• Сущность-концентратор (Hub) – таблица, содержащая список уникальных бизнес-ключей. Используется для идентификации направлений хозяйственной деятельности организации.
• Сущность-связь (Link) – таблица связей, содержащая уникальные взаимосвязи между ключами. Используется для фиксации взаимосвязей между сущностями-концентраторами и сущностями-связями.
• Сущность-сателлит (Satellite) – таблица, содержащая описательные и исторические данные. Используется для хранения описательной информации для сущностей-концентраторов и сущностей-связей.

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

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

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

Метод "Свод данных" целесообразно использовать в следующих случаях:

1. для создания динамических ХД (Dynamic Data Warehousing), когда возникает необходимость учитывать динамику изменения как обработки данных, так и структур данных;
2. для создания Data mining/Exploration Warehousing, когда пользователям нужно менять структуру данных без потери информации;
3. при встраивании процедур DM в ХД.

Таким образом, мы рассмотрели еще один метод моделирования ХД.