Библиотека Интернет Индустрии I2R.ru

Введение

Одним из важнейших моментов при работе с крупными Хранилищами данных является построение OLAP-кубов для достижения максимальной производительности. В данной статье рассмотрено построение кубов при помощи Microsoft® 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 была построена из таблиц о доходности по счетам за все периоды. В ней содержатся данные о доходности той или иной банковской услуги, например, ежемесячные доходы и расходы для различных продуктов; иными словами, таблица содержит значения totals для всех мер за каждый месяц. Были определены пять измерений: Product, Period, Region, Household и Customer Segment.

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

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

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

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

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

Обработка результатов для каждого режима хранения данных

Все последующие графики построены для уровней агрегирования 0%, 30%, 60% и 90%, хотя в большинстве случаев используются только значения от 30% до 60% (0% и 90% были включены для сравнения). Напомним, что уровень агрегирования характеризует увеличение производительности обработки запросов по сравнению с отсутствием предварительно рассчитанных агрегатов данных.

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

Таким образом, можно сделать следующие выводы:

• При уровне агрегирования 0% ROLAP потребовалось наименьшее количество времени для обработки куба. При этом данные таблицы фактов и измерений в куб не добавляются и агрегаты не рассчитываются.
• По мере увеличения уровня агрегирования, ROLAP - по сравнению с MOLAP или HOLAP - затрачивает все больше времени на обработку куба.
• Различие между MOLAP и HOLAP в промежутке 30 - 60% незначительно.
• Время обработки MOLAP и HOLAP увеличивается в промежутке 60 - 90%, но несильно.
• Время обработки ROLAP увеличивается экспоненциально в промежутке 60 - 90%.

На следующем графике показано изменение требуемого пространства на диске в зависимости от уровня агрегирования для каждого режима хранения.

Глядя на график, можно заключить, что:

• Режим хранения MOLAP требует больше места, чем HOLAP или ROLAP. (Кубы MOLAP содержат копии исходных фактов и измерений).
• Различие в количестве потребляемого дискового пространства при режимах MOLAP и HOLAP незначительно в интервале 0 - 60% и увеличивается по мере приближения к уровню 90%.
• Режим хранения HOLAP использует наименьшее количество дискового пространства. Это обусловлено тем, что копии исходных фактов и измерений отсутствуют в базе данных OLAP, а агрегаты хранятся в оптимизированном многомерном формате.
• Режим хранения ROLAP требует дополнительного места на диске, когда уровень агрегирования превышает 30% и когда он приближается к 90%. (График учитывает объем пространства, требуемого для хранения агрегатов данных в реляционной базе данных).

Данная таблица показывает объем требуемого места на диске для MOLAP-куба по сравнению с исходной схемой "звезда" (таблица фактов и таблицы измерений) в РСУБД.

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

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

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

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

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

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

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

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

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

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

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

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

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

Следующий график иллюстрирует время работы процессора при обработке запросов к 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.