WebClub - Всероссийский Клуб Веб-разработчиков
WebClub.RU » Советы » Analysis Services: Повышение производительности куба, используя Microsoft SQL Server 2000 Analysis Services

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


Дата публикации: 05-09-2010

Введение

Одним из важнейших моментов при работе с крупными Хранилищами данных является построение OLAP-кубов для достижения максимальной производительности. В данной статье рассмотрено построение кубов при помощи MicrosoftR 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 Чему равен средний экономический доход отдельного клиента за последние два года (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.

 

Создание и заполнение витрины данных 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 показана структура одного из этих кубов.

 

Следующая таблица содержит краткую характеристику каждого из 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Б

Уровень агрегирования (%) Дисковое пространство для 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 от уровня агрегации при "холодном" кэше.

 

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

Популярное

Не так давно в сети появился новый сервис, под названием Dead Man Zero. Этот сервис сделал...
Рынок социальных площадок уже давно стал стабильным. Несмотря на то, что время от времени...
Artisteer 4 – единственный в своем роде продукт, позволяющий автоматизировать работу над созданием...
Март 2017 (1)
Февраль 2017 (3)
Январь 2017 (1)
Август 2016 (1)
Май 2016 (2)
Ноябрь 2015 (1)

Карта сайта: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41

Друзья сайта

Хотите продать свой сайт?
- Мы быстро и удобно для Вас сможем его купить:
  • Заявка на продажу сайта
  • Раcсматриваем цены на каждый сайт в индивидуальном порядке.

    Случайная цитата

    Билл Гейтс:

    "У кого нет бизнеса в интернете, у того скоро не будет бизнеса вообще."

    Опрос

    Какой аудио плеер Вы используете?

    Winamp
    Light Alloy
    foobar2000
    Apollo
    AIMP
    1by1
    iTunes
    jetAudio
    Другой...