Построение хранилищ данных средствами Informix

Informix Magazine Russian Edition (Апрель 1998г)

1. Предисловие

В последние несколько лет в компьютерной прессе появилось немало публикаций, посвященных проблемам хранилищ данных (Data Warehouse) и оперативной аналитической обработке данных (OLAP). К сожалению, большинство из них носит либо чрезмерно обобщенный характер (эти публикации предназначены в основном для руководителей и рассказывают о том, какаие выгоды для бизнеса может дать построение хранилища данных), либо рекламно-маркетинговый характер - такие публикации описывают, как правило, функциональные возможности конкретных програмных продуктов. Данная публикация ставит задачу взглянуть на проблему в целом глазами разработчика, который, как правило, очень хорошо представляет себе особенности построения систем оперативной обработки транзакций (OLTP) и хотел бы при знакомстве с OLAP "плясать от печки", т.е. рассматривать OLAP в сравнении с OLTP.

2. Основные особенности DSS-систем в сравнении с OLTP-системами

Хранилища данных, как правило, используются в системах поддержки принятия решений (DSS-системах), поэтому необходимо выделить основные характеристики DSS-систем, которые отличают их от традиционных OLTP-систем:

1. Как правило, системы поддержки принятия решений предназначены для анализа временных зависимостей, в то время как OLTP-системы обычно имеют дело с текущими значениями каких-либо параметров. Например, типичное банковское OLTP-приложение имеет дело с текущими остатками денег на клиентском счету, в то время как в DSS-системе может потребоваться анализ динамики изменения остатков на счету.

2. В отличие от OLTP-системы, где данные непрерывно изменяются, содержимое хранилища данных носит статический характер: единожды попав в DSS-систему, данные уже практически не изменяются. Подгрузка новых данных, как правило, носит эпизодический характер и выполняется в периоды низкой активности системы как пакетная операция.

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

4. Настройки сервера баз данных, как правило, принципильно отличаются для задач OLTP и DSS. В OLTP сервер оптимизируется для уменьшения времени отклика при выполнении коротких запросов и увеличения количества одновременно работающих пользователей; с этой целью обычно минимизируют ресурсы, выделяемые отдельной сессии, и увеличивают коллективные ресурсы - например, буферы данных. В DSS-системах, как правило, сильная буферизация малоэффективна (данные все равно не помещаются в кэш); ресурсы, доступные пользовательской сессии, наоборот, максимально увеличивают: большое количество памяти необходимо при выполнении операция сортировок и соединений (join). DSS-запрос обычно очень сильно ускоряется при использовании заложенных в сервере БД возможностей распараллеливания запроса (между разными CPU в рамках SMP-компьютера или даже между разными узлами кластера).

5. Методы доступа, используемые в OLTP-системах (B-tree index), нередко оказываются неэффективными и даже снижают время выполнения DSS-запроса. Для ускорения выполнения DSS-запросов в серверах БД часто реализуют специальные методы индексирования - Dicision Support Indexes. К таким методам доступа относятся Bitmap Indexes, Generalized Key Indexes, Foreing Column Join Indexes.

6. Нередко случается, что OLTP-системы не связаны между собой, обслуживая различные подразделения компании. Эти системы часто реализуются на разных программно-аппаратных платформах. Одни и те же данные в разных базах могут быть представлены в разном виде и могут не совпадать (например, данные о клиенте, который взаимодействовал с разными отделами компании, могут не совпадать в базах данных этих отделов). Все это чрезвычайно затрудняет проведение совместного анализа этих данных. При загрузке в хранилище данных информация из разных OLTP-систем должна быть синхронизирована, приведена к общим стандартам (data cleansing).

7. Структура базы данных, обслуживающей OLTP-систему, обычно довольно сложна. Она может содержать многие десятки и даже сотни таблиц, ссылающихся друг на друга через внешние ключи (foreing key). Данные в такой базе сильно нормализованы, база содержет большое количество таблиц-справочников. Аналитические запросы к подобным базам очень трудно формулируются и крайне неэффективно выполняются, поскольку запросы содержат в себе соединения (join) большого количества таблиц. При проектировании хранилища данных стараются максимально упростить схему базы данных и уменьшить количество таблиц, участвующих в запросе. С этой целью часто идут на денормализацию базы данных

8. В DSS-системах обычно выдвигают значительно менее жесткие по сравнению с OLTP требования ко времени выполнения запроса: DSS-запросы выполняются в диапазоне от нескольких минут до нескольких часов и даже суток.

Из сказанного выше можно сделать вывод, что наличие у компании развитой OLTP-системы очень часто не является гарантом того, что данные, хранящиеся в системе, можно использовать для анализа тех или иных сторон деятельности компании. Для обеспечения высокоэффективного анализа данные из OLTP-систем должны быть извлечены, "очищены" (cleansed), организованы специальным способом и помещены в хранилище данных, производительность которого оптимизирована именно для выполнения DSS-запросов.

3. Многомерные базы данных и хранилища данных

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

Более типичными запросами к хранилищу данных могут быть запросы , возвращающие какие-то обобщенные значения на основе обработанных данных. В процессе выполнения такого запроса данные фильтруются по определенным критериям (SQL - оператор WHERE), группируются (SQL- оператор GROUP BY) и агрегируются (подсчитывается сумма, или среднее значение, или максимальное/минимальное значение и т.д.).

Наиболее распространенным способом организации хранилищ для выполнения подобных аналитических запросов является использование многомерной модели данных, которая обычно ассоциируется с понятием OLAP и его частным случаем - реляционным OLAP (ROLAP).

В OLAP-системе данные параметризуются несколькими равноправными величинами (измерениями, dimensions). Например, данные по продажам в крупной торговой компании можно было бы анализировать в следующих измерениях:

• время (день, месяц, квартал, год, неделя)
• география (город, штат,страна)
• товар (фирма-производитель, тип товара)
• покупатель (пол, возраст)

Заметим, что измерения (время, география) носят иерархический характер: в различных запросах может понадобиться производить анализ с дискретностью день, месяц или квартал. Естественно, способ организации данных должен поддерживать эти возможности.

В построенной таким образом системе можно было бы достаточно легко формировать запросы типа:

• как в прошлом году распределялись продажи телевизоров (тип товара) различных производителей по разным штатам?
• какова динамика продаж по месяцам видеомагнитофонов в текущем году по городам штата Калифорния?

Видно, что подобные запросы строятся следующим образом:

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

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

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

В зависимости от способа физической реализации многомерные базы данных делятся на две основные категории:

• когда программный продукт, реализующий OLAP-сервер, использует специализированные (отличные от реляционных) внутренние структуры данных для хранения многомерных данных. Этот подход, например, реализован в таких продуктах, как Oracle Express Server и Sybase IQ.
• когда для физической реализации многомерной модели данных используется реляционный сервер баз данных. Этот подход использовала компания Informix в своем OLAP-средстве Informix MetaCube, где для хранения данных используется любой из серверов Informix (OnLine Dynamic Server, Extended Parallel Server или Informix Universal Server).

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

4. Схема "звезда" - основа реляционного OLAP.

В основе реляционного OLAP лежит схема БД "звезда" (star schema). Остановимся на ней более подробно.

Модель данных состоит из двух типов таблиц: одной таблицы фактов (fact table) - центр "звезды" - и нескольких таблиц измерений (dimension table) по числу измерений в модели данных - лучи "звезды".

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

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

Заметим, что обычно данные в таблицах-измерениях денормализованы: ценой несколько неэффективного использования дискового пронстранства удается уменьшить число участвующих в операции соединения таблиц, что обычно приводит к сильному уменьшению времени выполнения запроса. Иногда, тем не менее, требуется произвести нормализацию таблиц-измерений; такая схема носит название "снежинка" (snowflake schema).

SQL-запрос к схеме "звезда" обычно содержит в себе:

• одно или несколько соединений таблицы фактов с таблицами измерений;
• несколько фильтров (SQL-оператор WHERE), применяемых к таблице фактов или таблицам измерений;
• группировку и аггрегирование по требуемым элементам иерархии измерений (dimension elements).

Например, первый из описанных в главе 3 запросов к базе данных торговой компании на языке SQL можно было бы сформулировать так:

SELECT
 sum (sales.revenue)
FROM
 sales, time, product, geography
WHERE
 sales.time_code = time.time_code  AND
 sales.prod_code = product.prod_code  AND
 sales.geo_code = geography.geo_code AND
 time.year = 1997  AND
 product.prod_type = "TV"
GROUP BY
 product.manufuct
GROUP BY
 geography.state

Видимо, этот SQL-текст не нуждается в комментарии.

Подобные SQL-запросы можно интегрировать в готовые приложения или реализовать с помощью генератора отчетов (например, Seagate Crystal Reports компании Seagate). Такой подход вполне оправдан при выполнении регламентированных запросов.

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

Остановимся очень коротко на архитектуре этого продукта (его более подробное описание можно загрузить с Web-сервера компании Informix: http://www.informix.com).

Для хранения информации о связях структуры БД и многомерной модели данных в терминах предметной области Informix MetaCube использует специальный репозиторий метаданных (metadata), хранящийся на том же сервере БД, где находятся таблицы схемы "звезда" или "снежинка". Информация из репозитория доступна для всех приложений, входящих в состав Informix MetaCube.

Пакет Informix MetaCube включает в себя следующие компоненты (большинство из них реализованы как 32-разрядные приложения Windows-95 или Windows-NT):

• MetaCube Explorer - рабочее место бизнес-аналитика, позволяющее формировать, оперируя понятиями предметной области, запросы к БД и анализировать результаты выполнения запроса. Результаты можно представить либо в табличной форме, либо в графической с помощью развитых средств бизнес-графики, либо экспортировать во внешние файла с целью проведения дальнейшего анализа (например, статистической обработки).
• MetaCube Warehouse Manager - рабочее место проектировщика хранилища данных, позволяющее спроецировать многомерную модель данных на реляционные таблицы;
• MetaCube Warehouse Optimizer - служит для разработки стратегии аггрегирования данных с целью увеличения быстродействия выполнения запросов;
• MetaCube for Excell - приложение, позволяющее организовать рабочее место бизнес-аналитика с помощью среды Excell в тех ситуациях, когда эта среда наиболее привычна для персонала компании;
• MetaCube for Web позволяет строить запросы к многомерным данным в среде Web/Intranet;
• MetaCube Scheduler - инструмент, позволяющий администрировать работу пакетных (batch) задач на сервере, управляющих, например, подгрузкой данных в хранилище данных;
• MetaCube QueryBack позволяет исполнять пользовательские запросы, требующие значительных серверных ресурсов, в часы низкой загрузки сервера.

Вместо заключения

Начиная работу над этой статьей, автор хотел создать у читателя впечатление, что разработка хранилища данных - задача вполне посильная, хотя методы ее решения довольно сильно отличаются от тех, которые используются при разработке OLTP-систем.

Алексей Сонькин, технический консультант компании Informix
e-mail alexeis@informix.com