Analysis Services: Підвищення продуктивності куба, використовуючи Microsoft SQL Server 2000 Analysis Services

Виходячи з перерахованих питань, визначимо відповідний джерело даних в існуючій 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 показана структура одного з цих кубів.

Наступна таблиця містить коротку характеристику кожного з 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 млн. Рядів. У наступній таблиці наведені описи заходів, включених в куби.

Дохід, який банк отримує від індивідуальних клієнтів за період часу. Економічний дохід означає чистий прибуток або збиток від операцій за вирахуванням податків з доходу на капітал за окремий період часу. Дохід від різниці процентних ставок дохід, який банк отримує від індивідуальних клієнтів за окремий період часу. Він являє собою різницю між рівнем процентних ставок, за якими банк надає кредит і приймає депозити. Дохід у формі комісій дохід, який банк отримує у формі комісій від індивідуальних клієнтів за окремий період часу. Резерви на випадок неповернення позик Резерв на випадок неповернення позик індивідуальними клієнтами. Витрати по даному продукту Витрати, пов'язані з конкретним продуктом, за окремий період часу. Витрати витрати, які несе банк, за окремий період часу. Чистий прибуток Чистий прибуток означає чистий прибуток / збиток від операцій за вирахуванням податків, витрат по виплаті відсотків і дивідендів за окремий період часу. Операційні витрати Операційні витрати, які несе банк при взаємодії з індивідуальними клієнтами, за окремий період часу.

У наступній таблиці наведені описи п'яти вимірювань, які були включені в куби - для цього використовувалися таблиці вимірювань в вітрині даних, організованої за схемою "зірка".

200001 2 19128 Кб ProductDim 14 1 3 Кб RegionDim 51 2 8 Кб TimeDim 24 3 5 Кб CustSegmentDim 7 1 2 Кб

На малюнку 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

60 335.75 5188 93.53 90 353.11 5188 93.19

Очевидно, що займане OLAP-кубом місце становить приблизно 7% від обсягу, необхідного для схеми "зірка". Навіть при 90% -му рівні агрегування вдається досягти майже такої ж ступені стиснення. Більше простору, необхідне для побудови MOLAP-куба, залежить від кількості рівнів в вимірі, кількості заходів і типу даних.

Наведена таблиця показує час обробки запитів 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 секунду. Ви можете виконувати найбільш часті запити в якості пакетного завдання відразу після обробки даних куба. В цьому випадку витрачається час буде менше.

Наступний графік ілюструє час роботи процесора при обробці запитів до 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.