Введение

Одним из важнейших моментов при работе с крупными Хранилищами данных является построение OLAP-кубов для достижения максимальной производительности. В данной статье рассмотрено построение кубов при помощи MicrosoftR SQL Server&trade 2000 and Analysis Services на примере "пробной" базы данных. Вы сможете ознакомиться с результатами ряда тестов и анализом производительности кубов в заданной среде.

Наиболее важными критериями при построении кубов являются режим хранения данных и уровень агрегирования. Основные режимы хранения в Analysis Services приведены в таблице:

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

Результаты тестов включают в себя:

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

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

Конфигурация тестируемой системы

Оборудование, использованное для проведения теста:

• Два сервера одинаковой конфигурации (Unisys e-@ction Aquanta ES5045R)
  • Процессор 4 Intel Xeon 550 МГц
  • 512 Мб кэш
  • 4 GB RAM
• Устройство хранения данных (Unisys OSM7700 OSM7700 Fiber channel data storage):
  • Система RAID (матрица независимых дисковых накопителей) из пяти дисков (по 9 Гб)
• Сеть: 100 Мб Ethernet

При тестировании были использованы следующие серверы:

• bbnt13 - сервер РСУБД, на котором работает SQL Server 2000, содержащий базу данных VLDBMart по реляционной схеме "звезда" (relational star schema).
• bbnt16 - аналитический сервер, на котором работает Analysis Services, хранящий OLAP-кубы и многомерные данные.

Выбор среды для тестирования

Формулировки вопросов

Создание описаний банковских данных

Исходя из перечисленных вопросов, определим соответствующий источник данных в существующей ER диаграмме (entity-relationship diagram, диаграмма "объекты-отношения") очень большой БД (VLDB, Very Large DataBase). Таблицы можно определить следующим образом:

• Таблица Product содержит информацию о счетах банка (например, текущий или срочный счет).
• Таблица Customer Segment предоставляет возможность разделить клиентов банка на категории (например, клиенты-вкладчики или клиенты-должники).
• Таблица Period содержит данные о временном периоде для расчета показателей прибыли. Рассматриваемая база данных содержит информацию за 2 года (1996 и 1997).
• Таблица Region содержит информацию о географическом положении каждого банка.
• В таблицу Household занесены данные о клиентах, которые могут иметь несколько счетов.

Построение схемы "звезда" (пространственная модель) для генерируемого куба

Для получения ответов на вопросы об эффективности того или иного продукта была разработана схема "звезда" (для OLAP-куба). Таблица Account_Prof_Fact была построена из таблиц о доходности по счетам за все периоды (см. Рис. 2). В ней содержатся данные о доходности той или иной банковской услуги, например, ежемесячные доходы и расходы для различных продуктов; иными словами, таблица содержит значения totals для всех мер за каждый месяц. Были определены пять измерений: Product, Period, Region, Household и Customer Segment.

Создание и заполнение витрины данных SQL-сервера

Для заполнения таблиц фактов и измерений в витрине данных были использованы службы трансформации данных (Data Transformation Services, DTS). Таблицы, хранящие данные по каждому из периодов, были объединены в одну таблицу фактов, названную Account_prof_fact и содержащую информацию за все 24 месяца. Таблица фактов содержит приблизительно 13 млн. записей.

Построение OLAP-кубов

Далее была создана многомерная база данных OLAP, названная AccountProfitabilityOLAPDatabase, которая содержит 12 кубов одинаковой структуры, но с различными режимами хранения и уровнями агрегирования. На рисунке 3 показана структура одного из этих кубов.

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

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

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

Очевидно, что занимаемое OLAP-кубом место составляет примерно 7% от объема, требуемого для схемы "звезда". Даже при 90%-ом уровне агрегирования удается достичь почти такой же степени сжатия. Дополнительное пространство, необходимое для построения MOLAP-куба, зависит от количества уровней в измерении, количества мер и типа данных.

Сравнение запросов MDX и SQL

Приведенная таблица показывает время обработки запросов MDX и SQL ("Чему равен экономический доход за первые кварталы 1996 и 1997 гг. и разница между ними по каждому из сегментов?").

Очевидно, что MDX-запрос проще и выполняется значительно быстрее.

Также можно провести сравнение скорости обработки запросов в разных средах: SQL-запросы, выполняемые на SQL-сервере, и MDX-запросы на OLAP-сервере, хранящем MOLAP-куб. (Каждый запрос выполнялся после перезагрузки сервера, чтобы гарантировать, что в кэше отсутствуют результаты запросов).

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

Время выполнения MDX-запросов при различных режимах хранения данных

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

Среднее время обработки ("Холодный" кэш)

На рисунке 7 показаны зависимость среднего времени обработки запросов для MOLAP, HOLAP и ROLAP от уровня агрегации при "холодном" кэше.

Следующий рисунок представляет собой увеличенный вариант предыдущего и более детально иллюстрирует изменение производительности в интервале 30 - 90%.

Информация, приведенная на графике, показывает, что:

• MOLAP позволяет достичь наибольшей скорости при обработке запросов, причем производительность существенно возрастает при увеличении уровня агрегирования с 0 до 60% (также, как и для ROLAP и HOLAP).
• При увеличении уровня агрегирования с 60 до 90% рост производительности является несущественным для всех режимов.

Среднее время обработки ("Теплый" кэш)

Приведенный график показывает зависимость среднего времени обработки для MOLAP, HOLAP и ROLAP от уровня агрегации в случае с "теплым" кэшем.

Таким образом:

• Если результат запроса имеется в кэш-памяти, его обработка происходит практически мгновенно (менее 1 секунды) для всех запросов независимо от режима хранения или уровня агрегирования. На графике значение равно 1, так как на предыдущих рисунках время также округлялось до секунд.

Среднее время обработки при "холодном" кэше в сравнении с "теплым" кэшем

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

Использование центрального процессора (CPU) при обработке запросов

Следующий график иллюстрирует время работы процессора при обработке запросов к MOLAP-, ROLAP-, and HOLAP-кубам. Красным цветом отмечено время работы SQL-сервера, синим - OLAP-сервера.

Данный рисунок говорит о том, что:

• MOLAP требует работы центрального процессора только на OLAP-сервере.
• ROLAP и HOLAP используют работу процессора в большей степени. Даже HOLAP интенсивно использует RDBMS-сервер. Это может быть вызвано тем, что выбранные для данного эксперимента запросы не содержат много агрегатов, уже имеющихся в Analysis Services (их необходимо рассчитывать во время исполнения).

Некоторые рекомендации по оптимизации производительности

Существует две категории наиболее эффективных способов оптимизации OLAP-кубов: снижение времени обработки данных OLAP-куба и снижение времени выполнения запросов.

Рекомендации по уменьшению времени обработки данных куба

Нам удалось снизить время обработки с нескольких часов до пары минут, используя следующие приемы:

• Использование пространственной схемы для витрины данных. Схемы "звезда" хорошо подходят для OLAP-кубов. Кроме первичного ключа, связанного с каждым измерением таблицы фактов, мы описали связи по внешнему ключу между фактами и таблицами измерений. Мы создали составной индекс по всем внешним ключам в таблице фактов. Помимо этого, мы определили индексы по отдельным внешним ключам, чтобы ускорить операции обработки.
• Использование Cube Editor для оптимизации структуры схемы и минимизации количества соединений, необходимых при обработке данных куба. Мы увеличили размер буфера для процесса (диалоговое окно Properties для OLAP-сервера) до 1 Гб на компьютере с 4Гб памяти. Важно также уделить внимание выбору уровня агрегирования. После установки значения уровеня агрегирования 90% потребовалось 8 - 9 часов, чтобы закончить обработку данных куба. Оптимальным является значение около 25%. После этого можно слегка повышать уровень агрегирования и опытным путем установить, окажет ли это положительное воздействие на скорость выполнения наиболее часто используемых запросов.

Рекомендации по снижению времени обработки запросов

Нам удалось оптимизировать время обработки MDX-запросов путем:

• Эффективного использования памяти для Analysis Services;
• Размещения Analysis Services (OLAP-кубов) и SQL-сервера (витрины данных) на разных компьютерах;
• Использования режима хранения MOLAP.

Также рекомендуется использовать мастер "Usage-Based Optimization Wizard" для расчета дополнительных агрегатов данных, необходимых для повышения производительности обработки запросов.

Заключение

В данной статье мы провели исследование производительности SQL Server 2000 Analysis Services при использовании различных режимов хранения (ROLAP, MOLAP, и HOLAP) и различных уровней агрегирования при использовании больших объемов информации.

Основные выводы:

• По мере увеличения уровня агрегирования больше времени требуется для обработки ROLAP-кубов по сравнению с кубами MOLAP and HOLAP.
• MOLAP требует больше места на диске, чем HOLAP и ROLAP; HOLAP требует меньше всего дискового пространства.
• Когда OLAP-кубы используют режим хранения данных МOLAP, занимаемое ими место составляет лишь 7% по сравнению с размером исходной схемы "звезда".
• Использование MDX-запросов может существенно ускорить работу системы, так как OLAP-кубы содержат предварительно рассчитанные агрегаты данных.
• Максимальная скорость обработки запросов достигается при использовании режима хранения данных MOLAP.
• MOLAP использует процессор только на OLAP-сервере.

Одним из ключевых преимуществ, которые дает использование Хранилищ данных, является возможность проведения анализа. А одно из основных достоинств интерактивного анализа (иначе - FASMI, Fast Analysis of Shared Multidimensional Information, Быстрый анализ используемой совместно многомерной информации) - возможность использования Analysis Services. Данная статья дает опытное подтверждение высокой производительности кубов, построенных с использованием Analysis Services.