Хранилища данных и их проектирование с помощью CA ERwin

Сергей Маклаков

Проблемы эффективного использования данных

Поддержка методологии Dimensional

Роль таблицы в схеме (Dimensional Modeling Role)

Тип таблицы размерности (Dimension Type)

Правила хранения данных (Data Warehouse Rules)

Создание спецификаций для источников данных

Поддержка специализированных СУБД

Проблемы эффективного использования данных

Корпоративные системы управления предприятием, созданные на основе реляционных СУБД, как правило, эффективно решают задачи учета, контроля и хранения данных. Однако в силу своей специфики реляционная структура не позволяет решать задачи анализа имеющейся информации с требуемой производительностью. Особенно остро эта проблема стоит в гетерогенных информационных средах, если в центральном офисе организации и в филиалах эксплуатируются СУБД различных производителей (рис. 1).

Такая ситуация часто возникает либо в результате слияния кoмпаний, когда нерентабельно перестраивать исторически сложившуюся информационную инфраструктуру, либо вследствие неудовлетворительного управления, когда филиалы не придерживаются корпоративного стандарта и внедряют собственные информационные системы. Одной из основных задач, решаемых в корпоративных информационных системах, является предоставление аналитической информации, необходимой для принятия решений. Для принятия решения необходимо иметь не один заранее подготовленный отчет, а целую серию разнообразных отчетов, причем менеджер не всегда представляет, какой именно отчет понадобится ему в ближайшие полчаса. Допустим, в компании при анализе продаж выясняется, что в феврале текущего года произошел спад. Чтобы выяснить причины спада, нужно просмотреть отчет о продажах в регионах. Этот отчет, в свою очередь, показывает, что спад произошел, скорее всего, по причине неудовлетворительной работы одного из филиалов, следовательно, необходим отчет о работе данного филиала и т.д. и т.п. Организовать выполнение таких отчетов в гетерогенной среде крайне сложно — в этом случае следует объединять в одном запросе данные из разнородных источников. В настоящее время существуют мониторы транзакций и генераторы отчетов (например, Crystal Reports), обладающие указанной функциональностью, однако производительность таких систем не может быть высокой. В процессе анализа данные, необходимые для принятия решений, должны поступать к потребителю в режиме реального времени. Если же данные собираются из разных источников, то, во-первых, отчет готовится недопустимо медленно, а во-вторых, другие приложения, работающие с этими же реляционными СУБД во время выполнения отчета, вероятнее всего, будут работать значительно медленнее.

Решением проблемы производительности является создание специализированной базы данных — хранилища данных (Data Warehouse), — предназначенной исключительно для обработки и анализа информации (рис. 2).

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

• менеджеру, принимающему решения, необходимы самые разнообразные отчеты, причем каждый раз новые. Не всегда есть возможность выделить специального сотрудника, который бы непрерывно готовил все новые и новые отчеты. Лучший выход — самому менеджеру научиться создавать отчеты . Существуют разнообразные инструменты (например, упомянутый выше Crystal Reports), интерфейс которых достаточно прост, чтобы непрофессионалы в области информационных технологий могли готовить отчеты. Однако в этом случае конечный пользователь непосредственно обращается к структуре данных, поэтому структура данных хранилища должна быть понятна пользователям;
• данные в хранилище должны регулярно пополняться. Требуется тщательно документировать правила пополнения и резервного копирования данных;
• в связи с тем что отчет будет создавать конечный пользователь, следует упростить требования к запросам с целью исключения таких запросов, которые могли бы требовать множественных SQL-предложений в традиционных реляционных СУБД;
• обработка запросов к хранилищу должна быть проведена с высокой производительностью, желательно в реальном масштабе времени. Поэтому должна быть обеспечена поддержка сложных SQL-запросов, для которых необходима последовательная обработка тысяч или миллионов записей.

Очевидно, что для решения этой задачи следует использовать специальные инструментальные средства. Одним из таких инструментов является ERwin ERX—CASE-средство фирмы Computer Associates International, Inc (ознакомительную версию этого продукта вы можете найти на нашем CD-ROM. — Прим. ред.).

ERwin ERX является незаменимым инструментом для проектирования хранилищ данных по нескольким причинам:

1. Хотя реализовать хранилище данных можно на любом сервере баз данных, существуют специализированные серверы, предназначенные именно для поддержки хранилищ данных. ERwin поддерживает генерацию схем баз данных для двух таких серверов — Teradata и Red Brick.
2. Как было cказано выше, при проектировании хранилища необходимо создавать подробные спецификации для всех источников данных, в том числе самых разных типов. ERwin поддерживает на физическом уровне прямое и обратное проектирование объектов более чем для 21 типа баз данных и потому является идеальным CASE-средством для работы с гетерогенными информационными системами.
3. Для эффективного проектирования хранилищ данных ERwin использует размерную (Dimensional) модель. Dimensional — это методология проектирования, специально предназначенная для разработки хранилищ данных.

Рассмотрим основные особенности техники моделирования хранилищ данных с помощью ERwin.

Поддержка методологии Dimensional

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

ERwin поддерживает методологию моделирования хранилищ благодаря использованию специальной нотации для физической модели — Dimensional. Наиболее простой способ перейти к нотации Dimensional в ERwin — при создании новой модели (меню File/New) в диалоге ERwin Template Selection выбрать из списка предлагаемых шаблонов Dimension. В шаблоне Dimension сделаны все необходимые для поддержки нотации размерного моделирования настройки, которые, впрочем, можно установить и вручную.

Размерное моделирование сходно с моделированием связей и сущностей для реляционной модели, но отличается от него целями. Если реляционная модель ориентирована на целостность данных и эффективность их ввода, то размерная (Dimensional) модель ориентирована прежде всего на выполнение сложных запросов к БД.

В размерном моделировании принят стандарт модели — схема «звезда» (star schema), которая обеспечивает высокую скорость выполнения запроса посредством денормализации и разделения данных. Невозможно создать универсальную денормализованную структуру данных, обеспечивающую высокую производительность при выполнении любого аналитического запроса. Поэтому схема «звезда» строится таким образом, чтобы обеспечить наивысшую производительность при выполнении либо одного, самого важного запроса, либо группы похожих запросов.

Схема «звезда» обычно содержит одну большую таблицу, называемую таблицей фактов (fact table), помещенную в центр, и окружающие ее меньшие таблицы, называемые таблицами размерности (dimensional table, в некоторых русскоязычных публикациях термин «dimension» переводится также как «измерение» — Прим. ред.), соединенные c таблицей факта радиальными связями в виде звезды.

В этих связях таблицы размерности являются родительскими, таблица факта — дочерней. Схема «звезда» может иметь также консольные таблицы (outrigger table), присоединенные к таблице размерности. Консольные таблицы являются родительскими, таблицы размерности — дочерними. В размерной модели ERwin роль таблицы в схеме «звезда» обозначается соответствующей пиктограммой (рис. 3).

Прежде чем создать базу данных со схемой типа «звезда», необходимо проанализировать бизнес-правила предметной области для выяснения центрального вопроса, ответ на который наиболее важен. Все прочие вопросы должны быть объединены вокруг основного вопроса, и моделирование должно начинаться с него. Данные, необходимые для ответа на этот вопрос, должны быть помещены в центральную таблицу модели — таблицу фактов. На рис. 4 приведен фрагмент учебной модели, входящей в поставку ERwin. Модель представляет собой хранилище данных фирмы, занимающейся продажей видиокассет. Например, необходимо создавать отчеты об общей сумме дохода от продаж за период, или по типу фильмов, или по рынкам фильмов. В этом случае следует разрабатывать модель так, чтобы каждая запись в таблице фактов представляла общую сумму продаж, сумму для каждого клиента за определенный период времени и для каждого рынка. В примере таблица факта содержит суммарные данные о продажах (REVENUE), а таблицы размерности содержат данные о заказчике и заказах (CUSTOMER), продуктах (MOVIE), рынках (MARKET) и периодах времени (TIME).

Таблица фактов, являющаяся центральной в схеме «звезда», может состоять из миллионов строк и содержать суммируемые или фактические данные, с помощью которых можно ответить на различные вопросы. Она объединяет в себе данные, которые иначе хранились бы во многих таблицах традиционных реляционных баз данных. Таблица фактов и таблицы размерности связаны идентифицирующими связями, при этом первичные ключи таблицы размерности мигрируют в таблицу фактов в качестве внешних ключей. В размерной модели направления связей явно не показываются, а определяются типом таблиц. Первичный ключ таблицы факта целиком состоит из первичных ключей всех таблиц размерности. В нашем примере (таблица факта REVENUE) первичный ключ составлен из четырех внешних ключей: movie_key, market_key, customer_key и time_key.

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

В примере на рис. 4 таблица REVENUE— таблица фактов CUSTOMER, TIME, MOVIE и MARKET — таблицы размерности, которые позволяют быстро извлекать информацию о том, кто и когда сделал покупку, какой продавец на какую сумму продал товары и какие именно товары были проданы.

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

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

В диалоге описания свойств таблицы Table Editor имеется закладка Dimensional, в которой задаются специфические свойства таблицы в размерной модели (рис. 5).

Роль таблицы в схеме (Dimensional Modeling Role)

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

• таблица фактов не является в связи дочерней;
• консольная таблица не является в связи родительской;
• установлена идентифицирующая связь между консольной таблицей и таблицей фактов.

Тип таблицы размерности (Dimension Type)

Каждая таблица размерности может содержать неизменяемые либо редко изменяемые данные (slowly changing dimensions). Поскольку хранилище данных имеет ненормализованную структуру, редактирование таблиц размерности может привести к коллизиям. Для того чтобы избежать противоречий при хранении данных, ERwin позволяет задать тип редко изменяемых данных, который отличается способом редактирования данных:

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

Правила хранения данных (Data Warehouse Rules)

Для каждой таблицы можно задать шесть типов правил манипулирования данными: обновление (Refresh), дополнение (Append), резервное копирование (Backup), восстановление (Recovery), архивирование (Archiving) и очистка (Purge). Для задания правила следует выбрать имя правила из соответствующего списка выбора. Каждое правило должно быть предварительно описано в диалоге Data Warehouse Rule Editor (меню Edit / Data Warehouse Rule). Для каждого правила нужно задать имя, тип, определение. Например, определение правила дополнения данных может включать частоту и время дополнения (ежедневно, в конце рабочего дня), продолжительность операции и т.д. Связать правила с определенной таблицей можно с помощью диалога Table Editor.

Создание спецификаций для источников данных

При проектировании хранилища данных важно определить источник данных (для каждой колонки), метод, которым исходные данные извлекаются, преобразовываются и фильтруются, прежде, чем они будут импортированы в хранилище данных. Хранилище данных может объединять информацию из текстовых файлов и многих баз данных — как реляционных (в том числе СУБД других типов), так и нереляционных — в единую систему поддержки принятия решений. Чтобы поддерживать регулярные обновления и проверки качества данных, необходимо знать источник для каждой колонки в хранилище данных. Для документирования информации об источниках данных используется редактор Data Warehouse Source Editor (рис. 6).

Источник данных может быть описан вручную в диалоге Data Warehouse Source Editor либо импортирован. В качестве источника при импорте используются и другие модели ERwin, хранящиеся в файле, SQL–скрипты, модели, хранящиеся в репозитарии ModelMart, либо системные каталоги СУБД, на основе которых в результате обратного проектирования могут быть созданы модели ERwin (рис. 7).

Каждому источнику можно задать имя и определение.

В редакторе Column Editor можно внести информацию об использовании источников данных для каждой колонки таблиц хранилища данных, а также дополнительную информацию о способах, режимах и периодичности переноса данных из источника в хранилище данных (рис. 8).

Поддержка специализированных СУБД

Хотя можно создать хранилище данных, используя любую СУБД, существуют специализированные СУБД, позволяющие значительно увеличить производительность обработки данных при использовании схемы «звезда». ERwin поддерживает на физическом уровне две такие СУБД — Red Brick и Teradata. При прямом и обратном проектировании поддерживаются специфические свойства как Red Brick, так и Teradata.

Для Red Brick поддерживаются специфические свойства индексов:

• уникальность (unique);
• распределение по сегментам;
• FILLFACTOR;
• определение типа индекса BTREE (только для версии Red Brick 5.0 и выше), STAR или TARGET (только для версии Red Brick 4.0 и выше) с указанием размера домена;

Редактор Red Brick Physical Object Editor (меню Server/Red Brick Physical Object) позволяет создавать сегменты (Segment) Red Brick и изменять их свойства:

• имя сегмента;
• имя файла сегмента, его максимальный размер, начальный размер (больше 16 Kбайт) и размер расширения.

Для каждой таблицы Red Brick можно указать сегменты для хранения данных и индекса первичного ключа, максимальное количество сегментов (для версии 5) и максимальное количество строк в сегменте (для версии 5).

Для Teradata ERwin также поддерживаются специфические объекты физической памяти. В диалоге Teradata Physical Object Editor Editor (меню Server / Teradata Physical Object) можно создать базы данных Teradata и определить их свойства:

• имя владельца базы данных;
• зарезервированный размер базы данных;
• возможность создания дубликатов таблиц для аварийного восстановления;
• размер spool-файлов;
• место для создания журнала (базы данных и таблицы) и его свойства;
• описание базы данных.

В закладке Physical Props диалога Teradata Table Editor можно определить параметры аудирования и восстановления после сбоя:

• имя таблицы, которая используется для ведения журнала;
• опция FALLBACK PROTECTION — создание одновременно основной и резервной копий таблицы;
• размер пространства, резервируемый для редактируемых данных;
• размер блоков данных.

Закладка Teradata MACRO диалога Teradata Table Editor позволяет создать шаблоны для хранимых процедур Teradata.

КомпьютерПресс 1'2001