Консалтинг и автоматизация в области управления
эффективностью банковского бизнеса

Журнал ВРМ World

Analysis Services: Повышение производительности куба, используя Microsoft SQL Server 2000 Analysis Services

Введение

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

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

Режим хранения данных Описание
Реляционный OLAP (ROLAP) Данные-факты и агрегаты хранятся на сервере реляционной БД.
Многомерный OLAP (MOLAP) Данные-факты и агрегаты хранятся на OLAP-сервере в оптимизированном многомерном формате.
Гибридный OLAP (HOLAP) Данные-факты хранятся на сервере реляционной БД, а агрегаты - на OLAP-сервере в оптимизированном многомерном формате.

В 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-кубы и многомерные данные.

Рис. 1. Системная конфигурация

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

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

Вопрос
1 Чему равен средний экономический доход отдельного клиента за последние два года (1996 и 1997) в результате использования каждого из продуктов?
2 Чему равен доход, полученный от клиентов за каждый год и по различным продуктам?
3 Чему равно скользящее среднее значение экономического дохода за несколько месяцев?
4 Чему равен экономический доход различных клиентов в процентах от общего дохода в пределах зоны ZIP-кода, где они проживают?
5 Чему равен средний экономический доход отдельного клиента за январь 1996 по каждому из продуктов?
6 Чему равен экономический доход за 1996 и 1997 гг.? Сравнить соответствующие значения по каждому потребительскому сегменту.
7 Чему равен экономический доход за первые кварталы 1996 и 1997 гг.? Сравнить соответствующие значения по каждому потребительскому сегменту.


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

Исходя из перечисленных вопросов, определим соответствующий источник данных в существующей 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.



Рис. 2. Схема "звезда" с фактами и измерениями


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

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

Витрина данных Количество рядов Размер
Account_Prof_Fact 13,036,152 5188.00 МБ
CustSegmentDim 7 0.03 МБ
HouseholdDim 200,001 38.56 МБ
ProductDim 14 0.03 МБ
RegionDim 51 0.04 МБ
TimeDim 24 0.03 МБ


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

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



Рис. 3. Схема куба

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

Название куба Режим хранения данных Уровень агрегирования (в процентах)
AccountProfitabilityCubeM0 MOLAP 0
AccountProfitabilityCubeM30 MOLAP 30
AccountProfitabilityCubeM60 MOLAP 60
AccountProfitabilityCubeM90 MOLAP 90
AccountProfitabilityCubeH0 HOLAP 0
AccountProfitabilityCubeH30 HOLAP 30
AccountProfitabilityCubeH60 HOLAP 60
AccountProfitabilityCubeH90 HOLAP 90
AccountProfitabilityCubeR0 ROLAP 0
AccountProfitabilityCubeR30 ROLAP 30
AccountProfitabilityCubeR60 ROLAP 60
AccountProfitabilityCubeR90 ROLAP 90

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

Показатель Комментарий
Экономический доход Доход, который банк получает от индивидуальных клиентов за период времени. Экономический доход означает чистую прибыль или убыток от операций за вычетом налогов с дохода на капитал за отдельный период времени.
Доход от разницы процентных ставок Доход, который банк получает от индивидуальных клиентов за отдельный период времени. Он представляет собой разницу между уровнем процентных ставок, по которым банк предоставляет кредит и принимает депозиты.
Доход в форме комиссий Доход, который банк получает в форме комиссий от индивидуальных клиентов за отдельный период времени.
Резервы на случай невозврата ссуд Резерв на случай невозврата ссуд индивидуальными клиентами.
Расходы по данному продукту Расходы, связанные с конкретным продуктом, за отдельный период времени.
Издержки Издержки, которые несет банк, за отдельный период времени.
Чистая прибыль Чистая прибыль означает чистую прибыль/убыток от операций за вычетом налогов, расходов по выплате процентов и дивидендов за отдельный период времени.
Операционные издержки Операционные издержки, которые несет банк при взаимодействии с индивидуальными клиентами, за отдельный период времени.

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

Измерение Количество рядов в таблице схемы "звезда" Количество уровней в измерении Размер измерения в Analysis Services
HouseholdDim 200001 2 19128 KБ
ProductDim 14 1 3 KБ
RegionDim 51 2 8 KБ
TimeDim 24 3 5 KБ
CustSegmentDim 7 1 2 KБ


На рисунке 4 показаны данные куба после его обработки.



Рис. 4. Структура куба после обработки

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

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

Время обработки для каждого режима хранения

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



Рис. 5. Время обработки для каждого режима хранения

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

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

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

Рис. 6. Требуемый объем дискового пространства

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

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

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


Уровень агрегирования (%) Дисковое пространство для MOLAP-куба Размер схемы "звезда" (таблицы фактов и измерений с индексами) Степень сжатия данных при построении кубов MOLAP
60 335.75 5188 93.53
90 353.11 5188 93.19

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

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

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

Тип запроса Время обработки
MDX 4 сек.
SQL 88 сек.

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

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

Номер запроса (см. табл.) Время, затраченное на обработку MDX-запроса на OLAP-сервере (используя режим хранения MOLAP и уровень агрегирования 60%) Время, затраченное на обработку SQL-запроса на SQL-сервере Прибл. число записей
1 4 секунды 88 секунд 13 млн.
6 10 секунд 36 секунд 13 млн.
7 4 секунды 89 секунд 13 млн.

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

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

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

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

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



Рис. 7. Сравнение значений времени обработки

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



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

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

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

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



Рис. 9. Время обработки запросов

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

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

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

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

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


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

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



Рис. 11. Время загрузки процессора для различных режимов хранения

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

  • 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.