Приклад створення куба в vba. OLAP-КУБ (динамічна управлінська звітність)

автономний файл куба (.cub) зберігає дані форму у кубі інтерактивної аналітичної обробки (OLAP). Ці дані можуть представляти частину бази даних OLAPз сервера OLAP або він може бути створений незалежно від будь-якої бази даних OLAP. Щоб продовжити роботу зі звітами зведених таблиць та зведених діаграм, якщо сервер недоступний або за вимкнення від мережі за допомогою файлу автономного куба.

Додаткові відомості про автономні куби

При роботі зі звітом зведеної таблиці або зведеної діаграми, що базується на джерелі даних із сервера OLAP, за допомогою майстра автономного куба для копіювання вихідних даних окремий автономний файл куба на вашому комп'ютері. Щоб створити ці автономні файли, необхідно мати постачальника даних OLAP, який підтримує ці можливості, такі як MSOLAP із Microsoft SQL Server Analysis Services, встановлених на комп'ютері.

Примітка:Створення та використання файлів автономного куба з Microsoft SQL Server Analysis Services, поширюється дія термін та ліцензування установки Microsoft SQL Server. Перегляньте відповідні відомості про ліцензування вашої версії SQL Server.

За допомогою майстра автономного куба

Створюючи файл автономного куба, використовуйте майстра автономного куба, виберіть підмножина даних у базі даних OLAP, а потім зберегти цей набір. У звіті не обов'язково включати всі поля, включити до файлу, і можна вибрати будь-який з її розмірів та поля даних, доступних у базі даних OLAP. Щоб мінімізувати розмір файлу, можна увімкнути лише дані, які ви хочете мати змогу відобразити у звіті. Можна пропустити всі розміри і для більшості типів вимірювань також опустити більше низького рівнядеталізації та елементи верхнього рівня, які не потрібно відображати. Для автономного файлу також зберігаються всі елементи, які можна увімкнути поля властивостей, доступні в базі даних для цих елементів.

Переведення даних в автономний режим, а потім перенесення даних назад в Інтернеті

Для цього спочатку необхідно створити звіт зведеної таблиці або звіт зведеної діаграми, що базується на базі даних сервера, після чого створити зі звіту автономний файл куба. Згодом під час роботи зі звітом можна у будь-який момент перемикатися між базою даних сервера та автономним файлом (наприклад, під час роботи на звіті портативному комп'ютерівдома або в дорозі та подальшому відновленні підключення комп'ютера до мережі).

Нижче описано основні етапи переведення даних в автономний режим та їх повернення до оперативного режиму.

Примітка:

Натисніть на звіт зведеної таблиці. Якщо це звіт зведеної діаграми, виберіть відповідний звіт зведеної таблиці.

На вкладці " Аналіз" в групі обчисленнянатисніть кнопку сервіс OLAPта натисніть кнопку Автономно OLAP.

Виберіть пункт OLAP за наявності зв'язку, а потім натисніть кнопку ОК.

Якщо буде запропоновано знайти джерело даних, натисніть кнопку Знайти джерелота знайдіть OLAP-сервер у мережі.

Клацніть звіт зведеної таблиці, що базується на файлі автономного куба.

В Excel 2016: На вкладці " дані" в групі запити та підключення Оновити всета натисніть кнопку Оновити.

В Excel 2013: На вкладці " дані" в групі підключенняклацніть стрілку поруч із кнопкою Оновити всета натисніть кнопку Оновити.

На вкладці " Аналіз" в групі обчисленнянатисніть кнопку сервіс OLAPта натисніть кнопку Автономно OLAP.

Натисніть кнопку Автономний режим OLAP, а потім - .

Примітка: Зупинитиу діалоговому вікні.

Попередження:

Створення автономного файлу куба із бази даних OLAP-сервера

Примітка:Якщо база даних OLAP має великий обсяг, а файл куба потрібен для забезпечення доступу до великого підмножини даних, потрібно багато вільного місцяна диску, а збереження файлу може тривати багато часу. Для підвищення продуктивності автономних файлів кубів рекомендується створювати з використанням сценарію багатовимірних виразів.

Проблема: Моя комп'ютера недостатньо місця на диску під час збереження куба.

Бази даних OLAP призначені для керування великими обсягами докладних даних, тому база даних, розміщена на сервері, може займати значно більше місця, ніж на локальному жорсткому диску. Якщо для автономного куба даних вибрано великий обсяг даних, вільного місця на диску може вистачити. Наведений нижче підхід допоможе скоротити розмір автономного файлу куба.

Звільнення місця на диску або вибір іншого дискаПерш ніж зберігати файл куба, видаліть з диска непотрібні файлиабо збережіть файл на мережному диску.

Включення в автономний файл куба меншої кількості данихПодумайте, як можна звести до мінімуму обсяг даних, що включаються до файлу, щоб файл містив усі дані, необхідні для звіту зведеної таблиці або зведеної діаграми. Спробуйте виконати наведені нижче дії.

Підключення автономного файлу куба до бази даних OLAP-сервера

Оновлення та повторне створення автономного файлу куба

Оновлення автономного файлу куба, що створюється на основі останніх даних, отриманих з серверного куба або з нового автономного файлу куба, може зайняти значний час і зажадати великий обсяг тимчасового місця на диску. Запускайте цей процес, коли не потрібний негайний доступ до інших файлів, попередньо переконавшись у наявності достатнього місця на жорсткому диску.

Проблема: Нові дані не відображаються у звіті, коли оновлюватимуться.

Перевірка доступності вихідної бази данихМожливо, автономний файл куба не вдається підключитися до бази даних сервера для отримання нових даних. Переконайтеся, що вихідна база даних на сервері, яка є джерелом даних для куба, не була перейменована або переміщена в інше місце. Переконайтеся, що сервер доступний, і до нього можна підключитися.

Перевірка наявності нових данихЗ'ясуйте у адміністратора бази даних, чи оновлювалися саме ті дані, які мають бути включені до звіту.

Перевірка незмінності організації бази данихЯкщо OLAP-сервер було змінено, для доступу до змінених даних може знадобитися реорганізація звіту, створення автономного файлу куба або запуск майстра створення куба OLAP. Щоб дізнатися про зміни бази даних, зверніться до адміністратора.

Включення до файлу автономного куба інших даних

Збереження зміненого файлу автономного куба може вимагати багато часу, при цьому робота в Microsoft Excelпід час збереження файлу неможливо. Запускайте цей процес, коли не потрібний негайний доступ до інших файлів, попередньо переконавшись у наявності достатнього місця на жорсткому диску.

Переконайтеся, що існує з'єднання з мережею і доступна вихідна база даних сервера OLAP, з якої автономний файл куба отримав дані.

Клацніть звіт зведеної таблиці, створений на основі автономного файлу куба, або пов'язаний звіт зведеної таблиці для звіту зведеної діаграми.

На вкладці Параметрив групі Сервіснатисніть кнопку сервіс OLAPта натисніть кнопку Автономний режим OLAP.

Натисніть кнопку Автономний режим OLAP, а потім - Змінити автономний файл даних.

Виконайте вказівки майстра автономних кубів, щоб вибрати інші дані для включення до цього файлу. На останньому кроці вкажіть ім'я та шлях до файлу, що змінюється.

Примітка:Щоб скасувати збереження файлу, натисніть кнопку Зупинитиу діалоговому вікні Створення файлу куба – хід виконання.

Видалення автономного файлу куба

Попередження:Якщо видалити файл автономного куба для якогось звіту, більше не можна буде використовувати цей звіт автономно та створити файл автономного куба для цього звіту.

Закрийте всі книги, які містять звіти, які використовують файл автономного куба, або переконайтеся, що всі звіти видалені.

У Microsoft Windowsзнайдіть та видаліть автономний файл куба (файл CUB).

додаткові відомості

Ви завжди можете поставити запитання фахівцеві Excel Tech Community , попросити допомоги у спільноті Answers community , а також запропонувати нову функціюабо покращення на веб-сайті

Куби даних OLAP (Online Analytical Processing – оперативний аналіз даних) дозволяють ефективно отримувати та аналізувати багатовимірні дані. На відміну від інших типів баз даних, бази даних OLAP розроблені спеціально для аналітичної обробки та швидкого вилучення з них різноманітних наборів даних. Насправді існує кілька ключових відмінностей між стандартними реляційними базами даних, такими як Access або SQL Server, та базами даних OLAP.

Мал. 1. Для підключення куба OLAP до книги Excel скористайтесь командою Зі служб аналітики

Завантажити замітку у форматі або

У реляційних базах даних інформація подається у вигляді записів, які додаються, видаляються та оновлюються послідовно. У базах даних OLAP зберігається лише моментальний знімок даних. У базі даних OLAP інформація заархівована як єдиного блоку даних і призначається лише виведення на запит. Хоча до бази даних OLAP і можна додавати нову інформацію, існуючі дані рідко редагуються і видаляються.

Реляційні бази даних та бази даних OLAP різняться структурно. Реляційні бази даних зазвичай складаються з набору таблиць, пов'язаних між собою. В окремих випадках реляційна база даних містить так багато таблиць, що дуже складно визначити, як вони все-таки пов'язані. У базах даних OLAP зв'язок між окремими блоками даних визначається заздалегідь та зберігається у структурі, відомій під назвою куби OLAP. У кубах даних зберігаються повні відомості про ієрархічну структуру та зв'язки бази даних, які значно спрощують навігацію по ній. До того ж, створювати звіти набагато простіше, якщо заздалегідь відомо, де розташовуються дані і які ще дані з ними пов'язані.

Основна ж різниця між реляційними базами даних та базами даних OLAP полягає у способі зберігання інформації. Дані у кубі OLAP рідко представлені у загальному вигляді. Куби даних OLAP зазвичай містять інформацію, представлену в попередньо розробленому форматі. Таким чином, операції угруповання, фільтрації, сортування та об'єднання даних у кубах виконуються перед заповненням їх інформацією. Це робить вилучення і виведення даних максимально спрощеною процедурою. На відміну від реляційних баз даних, немає необхідності впорядковувати інформацію належним чином перед виведенням її на екран.

Бази даних OLAP зазвичай створюються та підтримуються адміністраторами IT-відділу. Якщо у вашій організації немає структури, яка відповідає за управління базами даних OLAP, то можете звернутися до адміністратора реляційної бази даних з проханням реалізувати в корпоративній мережі хоча б окремі рішення OLAP.

Підключення до куба даних OLAP

Щоб отримати доступ до бази даних OLAP, спочатку потрібно встановити з'єднання з кубом OLAP. Почніть із переходу на вкладку стрічки Дані. Клацніть на кнопці З інших джерелі виберіть у розкривному меню команду Зі служб аналітики(Рис. 1).

Під час вибору зазначеної команди майстра підключення до даних (рис. 2). Основне його завдання - це допомогти вам встановити з'єднання із сервером, який буде використаний програмою Excelпід час управління даними.

1. Спочатку потрібно надати Excel реєстраційну інформацію. Введіть ім'я сервера в полях діалогового вікна, реєстраційне ім'ята пароль доступу до даних, як показано на рис. 2. Натисніть кнопку Далі. Якщо ви підключаєтеся за допомогою облікового запису Windows, то встановіть перемикач Використовувати автентифікацію Windows.

2. Виберіть у розкривному списку базу даних, з якою працюватимете (мал. 3). У цьому прикладі використовується база даних Analysis Services Tutorial. Після вибору цієї бази даних у списку, що знаходиться нижче, пропонується імпортувати всі доступні в ній куби OLAP. Виберіть необхідний куб даних та клацніть на кнопці Далі.

Мал. 3. Виберіть робочу базу даних та куб OLAP, який плануєте застосовувати для аналізу даних

3. У наступному діалоговому вікні майстра, показаному на рис. 4, вам потрібно ввести описову інформацію про підключення, що створюється. Усі поля діалогового вікна, показаного на рис. 4, не є обов'язковими для заповнення. Ви завжди можете проігнорувати поточне діалогове вікно, не заповнюючи його, і це ніяк не позначиться на підключенні.

Мал. 4. Змініть описову інформацію про з'єднання

4. Натисніть кнопку Готово, щоб завершити створення підключення. На екрані з'явиться діалогове вікно Імпорт даних(Рис. 5). Встановіть перемикач Звіт зведеної таблиціта клацніть на кнопці ОК, щоб почати створення зведеної таблиці.

Структура куба OLAP

У процесі створення зведеної таблиці на основі бази даних OLAP ви помітите, що вікно області задач Поля зведеної таблицівідрізнятиметься від такого для звичайної зведеної таблиці. Причина полягає в упорядкуванні зведеної таблиці так, щоб максимально близько відобразити структуру куба OLAP, приєднаного до неї. Щоб максимально швидко переміщатися кубом OLAP, необхідно детально ознайомитися з його компонентами та способами їх взаємодії. На рис. 6 показано базову структуру типового куба OLAP.

Як бачите, основні компоненти куба OLAP – це розмірності, ієрархії, рівні, члени та заходи:

• Розмірності. Основна характеристика аналізованих елементів даних. До найбільш загальних прикладів розмірності належать Products (Товари), Customer (Покупець) та Employee (Співробітник). На рис. 6 показано структуру розмірності Products.
• Ієрархії. Наперед визначена агрегація рівнів у зазначеній розмірності. Ієрархія дозволяє створювати зведені дані та аналізувати їх на різних рівнях структури, не вникаючи у взаємозв'язки, що існують між цими рівнями. У прикладі, показаному на рис. 6, розмірність Products має три рівні, що агреговані в єдину ієрархію Product Categories (Категорії товарів).
• рівні. Рівні є категоріями, які агрегуються в загальну ієрархію. Вважайте рівні полями даних, які можна запитувати та аналізувати окремо один від одного. На рис. 6 представлені лише три рівні: Category (Категорія), SubCategory (Підкатегорія) та Product Name (Назва товару).
• Члени. Окремий елементданих у межах розмірності. Доступ до членів зазвичай реалізується через OLАР-структуру розмірностей, ієрархій та рівнів. У прикладі на рис. 6 членів задані для рівня Product Name. Інші рівні мають члени, які у структурі не показані.
• Заходи- Це реальні дані в кубах OLAP. Заходи зберігаються у розмірності, які називаються розмірностями заходів. За допомогою довільної комбінації розмірностей, ієрархій, рівнів та членів можна вимагати заходи. Подібна процедура називається «нарізкою» заходів.

Тепер, коли ви ознайомилися зі структурою кубів OLAP, по-новому поглянемо на список полів зведеної таблиці. Організація доступних полів стає зрозумілою і не викликає нарікань. На рис. 7 показано, як у списку полів є елементи зведеної таблиці OLAP.

У списку полів зведеної таблиці OLAP заходи виводяться першими та позначаються значком підсумовування (сигма). Це єдині елементи даних, які можуть бути розташовані в області ЗНАЧЕННЯ. Після них у списку вказуються розмірності, позначені значком із зображенням таблиці. У прикладі використовується розмірність Customer. У цю розмірність вкладено низку ієрархій. Після розгортання ієрархії можна ознайомитись із окремими рівнями даних. Для перегляду структури даних куба OLAP достатньо переміщатись по списку полів зведеної таблиці.

Обмеження, що накладаються на зведені таблиці OLAP

Працюючи зі зведеними таблицями OLAP, слід пам'ятати, що взаємодія з джерелом даних зведеної таблиці здійснюється серед Analysis Services OLAP. Це означає, що кожен поведінковий аспект куба даних, починаючи з розмірності і закінчуючи заходами, які включені до куба, також контролюється аналітичними службами OLAP. У свою чергу це призводить до обмежень, що накладаються на операції, які можна виконувати в зведених таблицях OLAP:

• не можна помістити в область ЗНАЧЕННЯ зведеної таблиці поля, відмінні від заходів;
• неможливо змінити функцію, що використовується для підбиття підсумків;
• не можна створити обчислюване поле або обчислюваний елемент;
• будь-які зміни в іменах полів скасовуються відразу після видалення цього поля з зведеної таблиці;
• не дозволяється змінювати параметри поля сторінки;
• недоступна команда Показатисторінки;
• вимкнено параметр Показуватипідписиелементівза відсутності полів у сфері значень;
• вимкнено параметр Проміжні сумиза відібраними фільтром елементами сторінки;
• недоступний параметр Фоновийзапит;
• після подвійного клацанняу поле ЗНАЧЕННЯ повертаються лише перші 1000 записів з кешу зведеної таблиці;
• недоступний прапорець Оптимізуватипам'ять.

Створення автономних кубів даних

У стандартній зведеній таблиці вихідні дані зберігаються на локальному жорсткому диску. Таким чином, ви завжди можете керувати ними, а також змінювати структуру, навіть не маючи доступу до мережі. Але це жодною мірою не стосується зведених таблиць OLAP. У зведених таблицях OLAP кеш знаходиться на локальному жорсткому диску. Тому відразу ж після відключення від локальної мережіваша зведена таблиця OLAP втратить працездатність. Ви не зможете перемістити жодне поле в такій таблиці.

Якщо все ж таки потрібно аналізувати OLAP-дані за відсутності підключення до мережі, створіть автономний куб даних. Це окремий файл, який є кешем зведеної таблиці. У цьому файлі зберігаються дані OLAP, що переглядаються після відключення від локальної мережі. Щоб створити автономний куб даних, спершу створіть зведену таблицю OLAP. Помістіть курсор у зведену таблицю та клацніть на кнопці Засоби OLAPконтекстної вкладки Аналіз, що входить до набору контекстних вкладок Робота зі зведеними таблицями. Виберіть команду Автономний режим OLAP(Рис. 8).

На екрані з'явиться діалогове вікно Налаштування автономної роботи OLAP(Рис. 9). Клацніть на кнопці Створити автономний файл даних. На екрані з'явиться перше вікно майстра створення файлу даних. Клацніть на кнопці Далі, щоб продовжити процедуру

На другому кроці (рис. 10), вкажіть розмірності та рівні, які включатимуться в куб даних. У діалоговому вікні необхідно вибрати дані, які імпортуються з бази даних OLAP. Потрібно виділити лише ті розміри, які знадобляться після вимкнення комп'ютера від локальної мережі. Чим більше розмірностей вкажете, тим більший розмір матиме автономний куб даних.

Клацніть на кнопці Далідля початку третього кроку (рис. 11). У цьому вікні потрібно вибрати члени або елементи даних, які не включатимуться до куба. Якщо прапорець не встановлений, вказаний елемент не імпортуватиметься і займатиме зайве місце на жорсткому диску.

Вкажіть розташування та ім'я куба даних (рис. 12). Файли кубів даних мають розширення.

Через деяке час Excelзбереже автономний куб даних у зазначеній папці. Щоб протестувати його, двічі клацніть на файлі, що призведе до автоматичної генерації робочої книги Excel, Що містить зведену таблицю, пов'язану з вибраним кубом даних. Після створення ви можете розповсюдити автономний куб даних серед усіх зацікавлених користувачів, які працюють у режимі відключеної локальної мережі.

Після підключення до локальної мережі можна відкрити файл автономного куба даних та оновити його, а також відповідну таблицю даних. Врахуйте, що хоча автономний куб даних застосовується за відсутності доступу до мережі, він обов'язково оновлюється після відновлення підключення до мережі. Спроба поновлення автономного куба даних після розриву з'єднання з мережею призведе до збою.

Застосування функцій куба даних у зведених таблицях

Функції куба даних, які застосовуються в базах даних OLAP, можуть запускатися і зі зведеної таблиці. У застарілих версіях Excel ви отримували доступ до функцій кубів даних лише після встановлення надбудови Пакет аналізу. У Excel 2013 ці функції вбудовані в програму, а тому доступні для застосування. Щоб повною мірою ознайомитись з їхніми можливостями, розглянемо конкретний приклад.

Один з найбільш простих способіввивчення функцій куба даних полягає у перетворенні зведеної таблиці OLAP у формули куба даних. Ця процедура дуже проста і дозволяє швидко отримати формули куба даних, не створюючи їх "з нуля". Ключовий принцип – замінити всі комірки у зведеній таблиці формулами, які пов'язані з базою даних OLAP. На рис. 13 показано зведену таблицю, пов'язану з базою даних OLAP.

Помістіть курсор у будь-якому місці зведеної таблиці, клацніть на кнопці Засоби OLAPконтекстної вкладки стрічки Аналізта виберіть команду Перетворити на формули(Рис. 14).

Якщо ваша зведена таблиця містить поле фільтра звіту, на екрані з'явиться діалогове вікно, показане на рис. 15. У цьому вікні слід вказати, чи потрібно перетворювати на формули списки фільтрів даних, що розкриваються. При позитивній відповіді списки, що розкриваються, будуть видалені, а замість них будуть відображені статичні формули. Якщо ж ви надалі плануєте використовувати списки, що розкриваються, для зміни вмісту зведеної таблиці, то скиньте єдиний прапорець діалогового вікна. Якщо ви працюєте над зведеною таблицею в режимі сумісності, то фільтри даних будуть перетворюватися на формули автоматично, без попереднього попередження.

Через кілька секунд замість зведеної таблиці відобразяться формули, які виконуються в кубах даних та забезпечують виведення у вікні Excel необхідної інформації. Зверніть увагу на те, що при цьому видаляються раніше застосовані стилі (рис. 16).

Мал. 16. Погляньте на рядок формул: у комірках містяться формули куба даних

Враховуючи те, що значення, що ви переглядаєте, тепер не є частиною об'єкта зведеної таблиці, можна додавати стовпці, рядки і обчислювані елементи, комбінувати їх з іншими зовнішніми джерелами, а також змінювати звіт самими різними способами, зокрема і перетягуючи формули.

Додавання обчислень до зведених таблиць OLAP

У попередніх версіях Excel у зведених таблицях OLAP не допускалися обчислення користувача. Це означає, що до зведених таблиць OLAP було неможливо додати додатковий рівень аналізу подібно до того, як це робиться у звичайних зведених таблицях, що допускають додавання обчислюваних полів та елементів (докладніше див. ; перш ніж продовжити читання, переконайтеся, що ви добре знайомі з цим матеріалом ).

В Excel 2013 з'явилися нові інструменти OLAP - заходи, що обчислюються, і обчислювані елементи багатовимірних виразів. Тепер ви не обмежені використанням заходів та елементів у кубі OLAP, наданих адміністратором бази даних. Ви отримуєте додаткові можливостіаналізу шляхом створення обчислень користувачів.

Ознайомлення з MDX.При використанні зведеної таблиці разом з кубом OLAP ви надсилаєте базі даних запити MDX (Multidimensional Expressions – багатовимірні вирази). MDX - це мова запитів, яка використовується для отримання даних з багатовимірних джерел (наприклад, з кубів OLAP). У разі зміни або поновлення зведеної таблиці OLAP відповідні запити MDX передаються базі даних OLAP. Результати виконання запиту повертаються назад до Excel та відображаються в області зведеної таблиці. Таким чином забезпечується можливість роботи з даними OLAP без локальної копіїкеша зведених таблиць.

При створенні обчислюваних заходів та елементів багатовимірних виразів застосовується синтаксис мови MDX. За допомогою цього синтаксису зведена таблиця забезпечує взаємодію обчислень з серверною частиноюбази даних OLAP Приклади, що розглядаються в книзі, ґрунтуються на базових конструкціях MDX, що демонструють нові функції Excel 2013. Якщо необхідно створювати складні обчислювані заходи та елементи багатовимірних виразів, доведеться витратити час на більш глибоке вивчення можливостей MDX.

Створення заходів, що обчислюються.Обчислювана міра є OLAP-версію обчислюваного поля. Ідея полягає у створенні нового поля даних на основі деяких математичних операцій, що виконуються стосовно існуючих полів OLAP. У прикладі, показаному на рис. 17 використовується зведена таблиця OLAP, яка включає перелік і кількість товарів, а також дохід від продажу кожного з них. Потрібно додати новий захід, який обчислюватиме середню ціну за одиницю товару.

Аналіз Робота зі зведеними таблицями. У меню, що розкривається Засоби OLAPвиберіть пункт (Рис. 18).

Мал. 18. Виберіть пункт меню Обчислюваний захід багатовимірного виразу

На екрані з'явиться діалогове вікно Створення обчислюваного заходу(Рис. 19).

Виконайте наступні дії:

2. Виберіть групу заходів, в якій буде знаходитися новий обчислюваний захід. Якщо цього не зробити, Excel автоматично помістить новий захід у першу доступну групу заходів.

3. У полі Багатовимірний вираз(MDX) введіть код, який задає новий захід. Щоб прискорити процес введення, скористайтеся списком, що знаходиться зліва, для вибору існуючих заходів, які будуть використані в обчисленнях. Двічі клацніть потрібною мірою, щоб додати її в поле Багатовимірне вираз. Для обчислення середньої ціни продажу одиниці товару використовується такий багатовимірний вираз:

4. Натисніть OK.

Зверніть увагу на кнопку Перевірити MDX, що знаходиться у правій нижній частині вікна. Клацніть на цій кнопці, щоб перевірити коректність синтаксису багатовимірного виразу. Якщо синтаксис містить помилки, з'явиться відповідне повідомлення.

Після завершення створення нового обчислюваного заходу перейдіть до списку Поля зведеної таблиціта виберіть її (мал. 20).

Область дії заходу, що обчислюється, поширюється тільки на поточну книгу. Іншими словами, заходи, що обчислюються, не створюються безпосередньо в кубі OLAP сервера. Це означає, що ніхто не зможе отримати доступ до обчислюваної міри, якщо ви не відкриєте загальний доступдо робочої книгиабо не опублікуєте її в Інтернеті.

Створення обчислюваних елементів багатовимірних виразів.Обчислюваний елемент багатовимірного виразу є OLAP-версію звичайного обчислюваного елемента. Ідея полягає у створенні нового елемента даних, заснованого на деяких математичних операціях, що виконуються стосовно існуючих елементів OLAP. У прикладі, показаному на рис. 22 використовується зведена таблиця OLAP, що включає відомості про продаж за 2005–2008 роки (з поквартальною розбивкою). Припустимо, потрібно виконати агрегування даних, що належать до першого та другого кварталів, створивши новий елемент First Half of Year (Перша половина року). Також об'єднаємо дані, що належать до третього та четвертого кварталів, сформувавши новий елемент Second Half of Year (Друга половина року).

Мал. 22. Ми збираємося додати нові обчислювані елементи багатовимірних виразів, First Half of Year та Second Half of Year

Помістіть курсор у будь-якому місці зведеної таблиці та виберіть контекстну вкладку Аналізз набору контекстних вкладок Робота зі зведеними таблицями. У меню, що розкривається Засоби OLAPвиберіть пункт Обчислюваний елемент багатовимірного виразу(Рис. 23).

На екрані з'явиться діалогове вікно (Рис. 24).

Мал. 24. Вікно Створення обчислюваного елемента

Виконайте наступні дії:

1. Надайте обчислюваній мірі ім'я.

2. Виберіть батьківську ієрархію, для якої створюються нові обчислювані елементи. На будівництві Батьківський елементнадайте значення Усе. Завдяки цьому настроюванню Excel отримує доступ до всіх елементів батьківської ієрархії при обчисленні виразу.

3. У вікні Багатовимірний виразвведіть синтаксис багатовимірного виразу. Щоб трохи заощадити час, скористайтеся відображеним зліва списком для вибору існуючих елементів, що використовуються в багатовимірному виразі. Двічі клацніть на вибраному елементі, і Excel додасть його у вікно Багатовимірний вираз. У прикладі, показаному на рис. 24, обчислюється сума першого та другого кварталів:

..&& +

.. && +

.. && + …

4. Натисніть кнопку ОК. Excel відобразить щойно створений обчислюваний елемент багатовимірного виразу в зведеній таблиці. Як показано на рис. 25 новий обчислюваний елемент відображається разом з іншими обчислюваними елементами зведеної таблиці.

На рис. 26 ілюструється аналогічний процес, який застосовується для створення обчислюваного елемента Second Half of Year.

Зверніть увагу: Excel навіть намагається видалити вихідні елементи багатовимірного виразу (рис. 27). У зведеній таблиці, як і раніше, відображаються записи, що відповідають 2005–2008 рокам з поквартальною розбивкою. У цьому випадку це не страшно, але в більшості сценаріїв слід приховувати «зайві» елементи, щоб уникнути появи конфліктів.

Мал. 27. Excel відображає створений обчислюваний елемент багатовимірного виразу нарівні з вихідними елементами. Але все ж таки краще видаляти вихідні елементи, щоб уникнути конфліктів

Пам'ятайте: обчислювані елементи знаходяться лише у поточній робочій книзі. Іншими словами, заходи, що обчислюються, не створюються безпосередньо в кубі OLAP сервера. Це означає, що ніхто не зможе отримати доступ до обчислюваної міри або обчислюваного елемента, якщо ви не відкриєте спільний доступ до робочої книги або не опублікуєте її в Інтернеті.

Слід зазначити, що у разі зміни батьківської ієрархії або батьківського елемента в кубі OLAP елемент багатовимірного виразу, що обчислюється, перестає виконувати свої функції. Потрібно створити цей елемент повторно.

Управління обчисленнями OLAP.В Excel підтримується інтерфейс, що дозволяє управляти обчислюваними заходами та елементами багатовимірних виразів у зведених таблицях OLAP. Помістіть курсор у будь-якому місці зведеної таблиці та виберіть контекстну вкладку Аналізз набору контекстних вкладок Робота зі зведеними таблицями. У меню, що розкривається Засоби OLAPвиберіть пункт Управління обчисленнями. У вікні Управління обчисленнямидоступні три кнопки (рис. 28):

• Створити.Створення нового обчислюваного заходу або обчислюваного елемента багатовимірного виразу.
• Змінити.Зміна вибраного обчислення.
• Видалити.Видалення виділеного обчислення.

Мал. 28. Діалогове вікно Управління обчисленнями

Виконує аналіз «що, якщо» за даними OLAP.В Excel 2013 можна виконувати аналіз «що, якщо» для даних, що містяться в зведених таблицях OLAP. Завдяки цій нової можливостіможна змінювати значення у зведеній таблиці та повторно обчислювати заходи та елементи на підставі внесених змін. Також можна розповсюдити зміни назад на куб OLAP. Щоб скористатися можливостями аналізу «що, якщо», створіть зведену таблицю OLAP та виберіть контекстну вкладку Аналіз Робота зі зведеними таблицями. У меню, що розкривається Засоби OLAPвиберіть команду Аналіз «що, якщо» –> Включити аналіз «що, якщо»(Рис. 29).

Починаючи з цього моменту, можна змінювати значення зведеної таблиці. Щоб змінити обране значення у зведеній таблиці, клацніть на ньому правою кнопкою миші та в контекстному меню виберіть пункт (Рис. 30). Excel повторно виконає всі обчислення в зведеній таблиці з урахуванням внесених правок, включаючи заходи, що обчислюються, і обчислювані елементи багатовимірних виразів.

Мал. 30. Виберіть пункт Врахувати зміну при розрахунку зведеної таблиці, щоб внести зміни до зведеної таблиці

За умовчанням редагування, внесені до зведеної таблиці в режимі аналізу «що, якщо» є локальними. Якщо ви хочете розповсюдити зміни на сервер OLAP, виберіть команду для публікації змін. Виберіть контекстну вкладку Аналіз, що знаходиться у наборі контекстних вкладок Робота зі зведеними таблицями. У меню, що розкривається Засоби OLAPвиберіть пункти Аналіз «що, якщо» – > Опублікувати зміни(Рис. 31). В результаті виконання цієї команди увімкнеться «зворотний запис» на сервері OLAP, що означає можливість розповсюдження змін на вихідний куб OLAP. (Щоб поширювати зміни на сервер OLAP, потрібно мати відповідні дозволи на доступ до сервера. Зверніться до адміністратора баз даних, який допоможе вам отримати дозволи на доступ у режимі запису до бази даних OLAP.)

Нотатка написана на основі книги Джелен, Александер. . Розділ 9.

У попередній статті цього циклу (див. № 2'2005) ми розповіли про основні нововведення аналітичних служб SQL Server 2005. Сьогодні ми докладніше розглянемо засоби створення OLAP-рішень, що входять до цього продукту.

Коротко про основи OLAP

рідше ніж розпочати розмову про засоби створення OLAP-рішень, нагадаємо, що OLAP (On-Line Analytical Processing) - це технологія комплексного багатовимірного аналізу даних, концепція якої була описана в 1993 році Е. Ф. Коддом, знаменитим автором реляційної моделіданих. В даний час підтримка OLAP реалізована в багатьох СУБД та інших інструментах.

OLAP-куби

Що є OLAP-дані? Як відповідь це питання розглянемо найпростіший приклад. Припустимо, в корпоративній базі даних якогось підприємства є набір таблиць, що містять відомості про продаж товарів або послуг, і на їх основі створено подання Invoices з полями Country (країна), City (місто), CustomerName (назва компанії-клієнта), Salesperson (менеджер) з продажу), OrderDate (дата розміщення замовлення), CategoryName (категорія товару), ProductName (найменування товару), ShipperName (компанія-перевізник), ExtendedPrice (оплата за товар), при цьому останнє з перерахованих полів, власне, і є об'єктом аналізу .

Вибір даних із такого подання можна здійснити за допомогою наступного запиту:

SELECT Country, City, CustomerName, Salesperson,

OrderDate, CategoryName, ProductName, ShipperName, ExtendedPrice

FROM Invoices

Припустимо, нас цікавить, якою є сумарна вартість замовлень, зроблених клієнтами з різних країн. Для отримання відповіді на це питання необхідно зробити наступний запит:

SELECT Country, SUM (ExtendedPrice) FROM Invoices

GROUP BY Country

Результатом цього запиту буде одномірний набір агрегатних даних (у даному випадку сум):

Country	SUM (ExtendedPrice)
Argentina	7327.3
Austria	110788.4
Belgium	28491.65
Brazil	97407.74
Canada	46190.1
Denmark	28392.32
Finland	15296.35
Франція	69185.48
	209373.6
	...

Якщо ми хочемо дізнатися, яка сумарна вартість замовлень, зроблених клієнтами з різних країн і доставлених різними службами доставки, ми повинні виконати запит, який містить два параметри в пропозиції GROUP BY:

SELECT Country, ShipperName, SUM (ExtendedPrice) FROM Invoices

GROUP BY COUNTRY, ShipperName

Виходячи з результатів цього запиту, можна створити таблицю наступного виду:

Такий набір даних називається зведеною таблицею (pivot table).

SELECT Country, ShipperName, SalesPerson SUM (ExtendedPrice) FROM Invoices

GROUP BY COUNTRY, ShipperName, Year

З результатів цього запиту можна побудувати тривимірний куб (рис. 1).

Додаючи Додаткові параметридля аналізу, можна створити куб з теоретично будь-яким числом вимірювань, при цьому поряд із сумами в осередках OLAP-куба можуть міститися результати обчислення інших агрегатних функцій (наприклад, середні, максимальні, мінімальні значення, кількість записів вихідного подання, відповідне даному наборупараметрів). Поля, виходячи з яких обчислюються результати, називаються мірами куба.

Ієрархії у вимірах

Припустимо, нас цікавить не тільки сумарна вартість замовлень, зроблених клієнтами різних країнах, а й сумарна вартість замовлень, зроблених клієнтами у різних містах однієї країни. У цьому випадку можна скористатися тим, що значення, що наносяться на осі, мають різні рівні деталізації, це описується в рамках концепції ієрархії змін. Скажімо, на першому рівні ієрархії розташовуються країни, на другому міста. Зазначимо, що, починаючи з SQL Server 2000, аналітичні служби підтримують так звані незбалансовані ієрархії, які містять, наприклад, такі члени, «діти» яких утримуються не на сусідніх рівнях ієрархії або відсутні для деяких членів зміни. Типовий приклад подібної ієрархії - облік того факту, що в різних країнах можуть існувати, або відсутні такі адміністративно-територіальні одиниці, як штат або область, що розміщуються в географічній ієрархії між країнами та містами (рис. 2).

Зазначимо, що останнім часом прийнято виділяти типові ієрархії, наприклад, що містять географічні або тимчасові дані, а також підтримувати існування кількох ієрархій в одному вимірі (зокрема, для календарного та фінансового року).

Створення OLAP-кубів у SQL Server 2005

SQL Server 2005 куби створюються з допомогою SQL Server Business Intelligence Development Studio. Цей інструмент є спеціальною версією Visual Studio 2005, призначену для вирішення даного класузадач (а за наявності вже встановленого середовища розробки, список шаблонів проектів поповнюється проектами, призначеними для створення рішень на основі SQL Sever та його аналітичних служб). Зокрема, для створення рішень на основі аналітичних служб призначено шаблон Analysis Services Project (рис. 3).

Для створення OLAP-куба в першу чергу слід вирішити, на основі яких його формувати. Найчастіше OLAP-куби будуються з урахуванням реляційних сховищ даних зі схемами «зірка» чи «сніжинка» (щодо них ми розповідали у попередній частині статті). У комплекті поставки SQL є приклад такого сховища база даних AdventureWorksDW, для використання якої як джерело слід знайти в Solution Explorer папку Data Sources, вибрати пункт контекстного меню New Data Source та послідовно відповісти на запитання відповідного майстра (рис. 4).

Потім рекомендується створити Data Source View уявлення, на основі якого буде створюватися куб. Для цього необхідно вибрати відповідний пункт контекстного меню папки Data Source Views та послідовно відповісти на запитання майстра. Результатом зазначених дій стане схема даних, за допомогою яких буде побудовано уявлення джерел даних, при цьому в отриманій схемі замість вихідних можна вказати «дружні» імена таблиць (рис. 5).

Описаний таким чином куб можна перенести на сервер аналітичних служб, вибравши з контекстного меню проекту опцію Deploy та здійснити перегляд його даних (рис. 7).

Під час створення кубів нині використовуються багато особливостей нової версії SQL Server, наприклад, як подання джерел даних. Опис вихідних даних для побудови куба, так само як і опис структури куба, тепер проводиться за допомогою знайомого багатьом розробникам інструменту Visual Studio, що є чималою перевагою нової версії цього продукту вивчення розробниками аналітичних рішень нового інструментарію в цьому випадку зведено до мінімуму.

Зазначимо, що у створеному кубі можна змінювати склад заходів, видаляти та додавати атрибути вимірювань та додавати обчислювані атрибути членів вимірювань на основі наявних атрибутів (рис. 8).

Мал. 8. Додавання обчислюваного атрибуту

Крім того, в кубах SQL Server 2005 можна здійснювати автоматичне угруповання або сортування членів вимірювання за значенням атрибута, визначати зв'язки між атрибутами, реалізовувати зв'язки «багато хто до багатьох», визначати ключові показники бізнесу, а також вирішувати багато інших завдань (подробиці про те, як виконуються всі ці дії, можна знайти в розділі SQL Server Analysis Services Tutorial довідкової системиданого товару).

У наступних частинах цієї публікації ми продовжимо знайомство з аналітичними службами SQL Server 2005 і з'ясуємо, що нового з'явилося у сфері підтримки Data Mining.

Можливо, для когось використання OLAP-технології (On-line Analytic Processing) при побудові звітності здасться якоюсь екзотикою, тому застосування OLAP-КУБу для них зовсім не є однією з найважливіших вимог автоматизації бюджетування та управлінського обліку.

Насправді дуже зручно користуватися багатовимірним кубом при роботі з управлінською звітністю. При розробці форматів бюджетів можна зіткнутися з проблемою багатоваріантності форм (докладніше про це можна прочитати в Книзі 8 "Технологія постановки бюджетування в компанії" та у книзі "Постановка та автоматизація управлінського обліку").

Це з тим, що з ефективного управління компанією потрібно дедалі більше детализированная управлінська звітність. Тобто в системі використовується все більше різних аналітичних зрізів (у інформаційних системаханалітики визначаються набором довідників.

Природно, це призводить до того, що керівники хочуть отримувати звітність у всіх аналітичних зрізах, що їх цікавлять. А це означає, що звіти треба якось змусити дихати. Іншими словами можна сказати, що в даному випадку йдеться про те, що за змістом той самий звіт повинен надавати інформацію в різних аналітичних розрізах. Тому статичні звіти не влаштовують багатьох сучасних керівників. Їм потрібна динаміка, яку може дати багатовимірний куб.

Таким чином, OLAP-технологія вже зараз стала обов'язковим елементому сучасних та перспективних інформаційних системах. Тому при виборі програмного продукту потрібно звертати увагу на те, чи використовується в ньому OLAP-технологія.

Причому треба вміти відрізняти справжні КУБ від імітації. Однією з таких імітацій є зведені таблиці у MS Excel. Так, цей інструмент схожий на КУБ, але насправді не є таким, оскільки це статичні, а не динамічні таблиці. Крім того, в них набагато гірше реалізовано можливість побудови звітів, які використовують елементи з ієрархічних довідників.

Для підтвердження актуальності використання КУБу під час побудови управлінської звітності можна навести найпростіший приклад із бюджетом продажів. У прикладі для компанії актуальними є такі аналітичні зрізи: продукти, філії і канали збуту. Якщо компанії важливі ці три аналітики, то бюджет (чи звіт) продажів можна виводити у кількох варіантах.

Слід зазначити, що якщо створювати рядки бюджетів на основі трьох аналітичних зрізів (як у прикладі), це дозволяє створювати досить складні бюджетні моделіта складати деталізовані звіти з використанням КУБу.

Наприклад, бюджет продажів можна складати з використанням лише однієї аналітики (довідника). Приклад бюджету продажів, побудованого на основі однієї аналітики "Продукти", представлений на малюнку 1.

Мал. 1. Приклад бюджету продажу, побудованого на основі однієї аналітики "Продукти" в OLAP-КУБі

Цей бюджет продажів можна складати з використанням двох аналітик (довідників). Приклад бюджету продажів, побудованого на основі двох аналітик "Продукти" та "Філії" представлений на малюнку 2.

Мал. 2. Приклад бюджету продажу, побудованого на основі двох аналітик "Продукти" та "Філії" в OLAP-КУБі програмного комплексу "ІНТЕГРАЛ"

.

Якщо є потреба будувати більше детальні звіти, то можна той самий бюджет продажів складати з допомогою трьох аналітик (довідників). Приклад бюджету продажів, побудованого на основі трьох аналітик "Продукти", "Філії" та "Канали збуту" представлено на малюнку 3.

Мал. 3. Приклад бюджету продажу, побудованого на основі трьох аналітик "Продукти", "Філії" та "Канали збуту" в OLAP-КУБі програмного комплексу "ІНТЕГРАЛ"

КУБ, який використовується для формування звітів, дозволяє виводити дані в різній послідовності. на малюнку 3Бюджет продажів спочатку "розгортається" по продуктах, потім по філіях, а потім по каналах збуту.

Ті ж дані можна подати в іншій послідовності. на малюнку 4той же бюджет продажів "розгортається" спочатку по продуктах, потім каналами збуту, а потім по філіях.

Мал. 4. Приклад бюджету продажу, побудованого на основі трьох аналітик "Продукти", "Канали збуту" та "Філії" в OLAP-КУБі програмного комплексу "ІНТЕГРАЛ"

на малюнку 5той же бюджет продажів "розгортається" спочатку по філіях, потім по продуктах, а потім по каналах збуту.

Мал. 5. Приклад бюджету продажу, побудованого на основі трьох аналітик "Філіли", "Продукти" та "Канали збуту" в OLAP-КУБепрограмному комплексі "ІНТЕГРАЛ"

Насправді, це не всі можливі варіанти виведення бюджету продажів.

Крім того, потрібно звернути увагу на те, що КУБ дозволяє працювати з ієрархічною структуроюдовідників. У наведених прикладах ієрархічними довідниками є "Продукти" та "Канали збуту".

З погляду користувача він у даному прикладіотримує кілька управлінських звітів (див. Мал. 1-5), а з погляду налаштувань у програмному продукті – це один звіт. Просто за допомогою КУБу його можна переглядати кількома способами.

Природно, що на практиці можлива дуже велика кількість варіантів виведення різних управлінських звітів, якщо їх статті будуються на одній або кількох аналітиках. А сам набір аналітик залежить від потреби користувачів у деталізації. Щоправда, при цьому не слід забувати, що з одного боку чим більше аналітик, тим більше деталізовані звіти можна будувати. Але, з іншого боку, отже, і фінансова модель бюджетування буде складнішою. У будь-якому випадку за наявності КУБу компанія матиме можливість перегляду необхідної звітності в різних варіантах, відповідно до аналітичних розрізів, що цікавлять.

Необхідно згадати ще кілька можливостей OLAP-КУБа.

У багатовимірному ієрархічному OLAP-КУБ є кілька вимірів: тип рядка, дата, рядки, довідник 1, довідник 2 і довідник 3 (див. Мал. 6). Природно, у звіт виводиться стільки кнопок із довідниками, скільки є у рядку бюджету, що містить максимальна кількістьдовідників. Якщо в жодному рядку бюджету немає жодного довідника, то у звіті не буде жодної кнопки з довідниками.

Спочатку OLAP-КУБ будується за всіма вимірами. За замовчуванням при початковій побудові звіту вимірювання розташовані саме в тих сферах, як показано на малюнку 6. Тобто такий вимір, як «Дата», розташовується в області вертикальних вимірювань (вимірювання в області стовпців), вимірювання «Рядки», «Довідник 1», «Довідник 2» та «Довідник 3» – в області горизонтальних вимірів (вимірювання в області рядків), а вимір «Тип рядка» – у сфері «нерозкривних» вимірів (вимірювання у сторінці). Якщо вимір знаходиться в останній області, дані у звіті не будуть «розкриватися» за цим виміром.

Кожен із цих вимірів можна помістити в будь-яку з трьох областей. Після перенесення вимірювань звіт миттєво перебудовується відповідно до новою конфігурацієювимірів. Наприклад, можна поміняти місцями дату та рядки з довідниками. Або можна у вертикальну область вимірювань перенести один із довідників (див. Мал. 7). Іншими словами, звіт в OLAP-КУБі можна «крутити» і вибирати варіант виведення звіту, який є найбільш зручним для користувача.

Мал. 7. Приклад перебудови звіту після зміни конфігурації вимірювань програмного комплексу "ІНТЕГРАЛ"

Конфігурацію вимірів можна змінювати або в основній формі КУБу, або в редакторі карти змін (див. Мал. 8). У цьому редакторі можна мишкою перетягувати вимірювання з однієї області в іншу. Крім цього, можна міняти місцями виміру в одній області.

Крім того, в цій формі можна налаштовувати деякі параметри вимірювань. По кожному виміру можна налаштовувати розташування результатів, порядок сортування елементів та назви елементів (див. Мал. 8). Також можна задавати, яку назву елементів виводити до звіту: скорочена (Name) або повна (FullName).

Мал. 8. Редактор карти вимірювань програмного комплексу "ІНТЕГРАЛ"

Редагувати параметри вимірювання можна безпосередньо в кожному з них (див. Мал. 9). Для цього потрібно натиснути на піктограму, розташовану на кнопці поруч із назвою вимірювання.

Мал. 9. Приклад редагування довідника 1 Продукти та послуги у

За допомогою цього редактора можна вибирати елементи, які потрібно відображати у звіті. За промовчанням у звіт виводяться всі елементи, але при необхідності частину елементів або папок можна не показувати. Наприклад, якщо потрібно виводити до звіту лише одну продуктову групу, то у всіх інших необхідно прибрати галочки в редакторі вимірювань. Після цього у звіті буде лише одна продуктова група (див. Мал. 10).

Також у цьому редакторі можна сортувати елементи. Крім того, елементи можна перегруповувати у різний спосіб. Після такого перегрупування звіт миттєво перебудовується.

Мал. 10. Приклад виведення у звіті лише однієї продуктової групи (папки) у програмному комплексі "ІНТЕГРАЛ"

У редакторі виміру можна оперативно створювати свої групи, перетягувати туди елементи з довідників тощо. За замовчуванням автоматично створюється лише група інших, але можна створювати й інші групи. Таким чином, за допомогою редактора вимірювань можна налаштовувати, які елементи довідників та в якому порядку потрібно виводити до звіту.

Слід зазначити, що такі перегрупування не записуються. Тобто після закриття звіту або після його перерахунку до звіту виводитимуться всі довідники відповідно до налаштованої методики.

Насправді такі зміни можна було зробити спочатку при налаштуванні рядків.

Наприклад, за допомогою обмежень можна також задавати, які елементи або групи довідників потрібно виводити до звіту, а які – ні.

Примітка: докладніше тема цієї статті розглядається на семінарах-практикумах "Бюджетне управління підприємством"і "Постановка та автоматизація управлінського обліку", які проводить автор цієї статті – Олександр Карпов .

Якщо користувачеві практично регулярно потрібно виводити лише певні елементи або папки довідників у звіт, то подібні налаштування краще заздалегідь зробити при створенні рядків звітів. Якщо для користувача важливі різні комбінації елементів довідників у звітах, тоді при налаштуванні методики жодних обмежень ставити не потрібно. Усі такі обмеження можна буде оперативно налаштовувати за допомогою редактора виміру.

Анотація: У цій лекції розглядаються основи проектування кубів даних для OLAP-сховищ даних. На прикладі показано методику побудови куба даних за допомогою CASE-інструменту.

Ціль лекції

Вивчивши матеріал цієї лекції, ви знатимете:

• що таке куб даних у OLAP-сховище даних ;
• як проектувати куб даних для OLAP-сховищ даних ;
• що таке вимір куба даних;
• як факт пов'язаний з кубом даних;
• що таке атрибути вимірювання;
• що таке ієрархія;
• що таке метрика куба даних;

і навчіться:

• будувати багатомірні діаграми ;
• проектувати прості багатомірні діаграми.

Вступ

Технологія OLAP – це не окремо взятий програмний продукт, не мова програмування. Якщо постаратися охопити OLAP у всіх його проявах, то це сукупність концепцій, принципів та вимог, що лежать в основі програмних продуктів, що полегшують аналітикам доступ до даних.

Аналітики є основними споживачами корпоративної інформації. Завдання аналітика полягає у тому, щоб знаходити закономірності у великих масивах даних. Тому аналітик не звертатиме уваги на окремо взятий факт, що у певний день покупцю Іванову було продано партію кулькових авторучок, - йому потрібна інформація про сотні та тисячі подібних подій. Поодинокі факти у ХД можуть зацікавити, наприклад, бухгалтера чи начальника відділу продажу, у компетенції якого є супровід певного договору. Аналітику одного запису недостатньо – йому, наприклад, може знадобитися інформація про всі контракти точки продажу за місяць, квартал чи рік. Аналітика може не цікавити ІПН покупця або його телефон, - він працює з конкретними числовими даними, що становить сутність його професійної діяльності.

Централізація та зручне структурування – це далеко не все, що потрібно аналітику. Йому потрібен інструмент перегляду, візуалізації інформації. Традиційні звіти, навіть побудовані на основі єдиного ХД, позбавлені, однак, певної гнучкості. Їх не можна "покрутити", "розгорнути" або "згорнути", щоб отримати необхідне представлення даних. Чим більше "зрізів" і "розрізів" даних аналітик може досліджувати, тим більше у нього ідей, які, у свою чергу, для перевірки вимагають нових і нових "зрізів". Як такий інструмент для дослідження даних аналітиком виступає OLAP.

Хоча OLAP і не є необхідним атрибутом ХД, він все частіше і частіше застосовується для аналізу накопичених у цьому ХД відомостей.

Оперативні дані збираються з різних джерел, очищаються, інтегруються та складаються у ХД. При цьому вони вже доступні для аналізу за допомогою різних засобів побудови звітів. Потім дані (повністю або частково) готуються для OLAP-аналізу. Вони можуть бути завантажені у спеціальну БД OLAP або залишені у реляційному ХД. Найважливішим елементом використання OLAP є метадані, тобто інформація про структуру, розміщення та трансформації даних. Завдяки їм забезпечується ефективна взаємодія різних компонентів сховища.

Таким чином, OLAP можна визначити як сукупність засобів багатовимірного аналізу даних, накопичених у ХД. Теоретично засоби OLAP можна застосовувати безпосередньо до оперативних даних або їх точним копіям. Однак при цьому існує ризик аналізувати дані, які для цього аналізу не придатні.

OLAP на клієнті та на сервері

В основі OLAP лежить багатовимірний аналіз даних. Він може бути зроблений за допомогою різних засобів, які умовно можна поділити на клієнтські та серверні засоби OLAP.

Клієнтські OLAP-засоби є додатками, що здійснюють обчислення агрегатних даних (сум, середніх величин, максимальних або мінімальних значень) та їх відображення, при цьому самі агрегатні дані містяться в кеші всередині адресного простору такого OLAP-засоби.

Якщо вихідні дані містяться в настільній СУБД, обчислення агрегатних даних здійснюється самим OLAP-засобом. Якщо джерело вихідних даних - серверна СУБД , багато хто з клієнтських OLAP -засобів посилають на сервер SQL -запити, що містять оператор GROUP BY , і в результаті отримують агрегатні дані, обчислені на сервері.

Як правило, OLAP-функціональність реалізована в засобах статистичної обробкиданих (з продуктів цього класу на російському ринкушироко поширені продукти компаній Stat Soft та SPSS) та в деяких електронних таблицях. Зокрема, непоганими засобами багатовимірного аналізу володіє Microsoft Excel 2000. За допомогою цього продукту можна створити та зберегти у вигляді файлу невеликий локальний багатовимірний OLAP-куб та відобразити його дво- чи тривимірні перерізи.

Багато засоби розробкимістять бібліотеки класів або компонентів, що дозволяють створювати програми, що реалізують найпростішу OLAP-функціональність (такі, наприклад, як компоненти Decision Cube у Borland Delphi та Borland C++Builder). Крім цього багато компаній пропонують елементи керування ActiveX та інші бібліотеки, що реалізують подібну функціональність.

Зазначимо, що клієнтські OLAP-кошти застосовуються, як правило, при малій кількості вимірювань (зазвичай рекомендується не більше шести) та невеликій різноманітності значень цих параметрів - адже отримані агрегатні дані повинні вміщатися в адресному просторі подібного засобу, а їх кількість зростає експоненційно при збільшенні числа вимірювань. Тому навіть найпримітивніші клієнтські OLAP-кошти, як правило, дозволяють зробити попередній підрахунок обсягу необхідної оперативної пам'ятідля створення багатомірного куба.

Багато (але не всі) клієнтські OLAP-засоби дозволяють зберегти вміст кешу з агрегатними даними у вигляді файлу, що, у свою чергу, дозволяє не проводити їх повторне обчислення. Зазначимо, що нерідко така можливість використовується для відчуження агрегатних даних для передачі їх іншим організаціям або для публікації. Типовим прикладом таких відчужуваних агрегатних даних є статистика захворюваності в різних регіонах та різних вікових групах, яка є відкритою інформацією, що публікується міністерствами охорони здоров'я різних країн та Всесвітньою організацією охорони здоров'я. При цьому власне вихідні дані, що є відомостями про конкретні випадки захворювань, є конфіденційними даними медичних установ і в жодному разі не повинні потрапляти до рук страхових компаній і тим більше оприлюднюватися.

Ідея збереження кешу з агрегатними даними у файлі отримала свій подальший розвиток у серверних OLAP-засобах, в яких збереження та зміна агрегатних даних, а також підтримка сховища, що їх містить. окремим додаткомабо процесом, званим OLAP-сервером. Клієнтські програми можуть запитувати подібне багатовимірне сховище та у відповідь отримувати ті чи інші дані. Деякі клієнтські програми можуть також створювати такі сховища або оновлювати їх відповідно до вихідних даних, що змінилися.

Переваги застосування серверних OLAP-засобів у порівнянні з клієнтськими OLAP-засобами подібні до переваг застосування серверних СУБД у порівнянні з настільними: у разі застосування серверних засобів обчислення та зберігання агрегатних даних відбувається на сервері, а клієнтська програма отримує лише результати запитів до них, що дозволяє у загальному випадку знизити мережевий трафік, час виконаннязапитів та вимоги до ресурсів, споживаних клієнтським додатком. Зазначимо, що засоби аналізу та обробка даних масштабу підприємства, як правило, базуються саме на серверних OLAP-засобах, наприклад, таких як Oracle Express Server, Microsoft SQL Server 2000 Analysis Services, Hyperion Essbase, продуктах компаній Crystal Decisions, Business Objects, Cognos, SAS Institute. Оскільки всі провідні виробники серверних СУБД виробляють (або ліцензували в інших компаній) ті чи інші серверні OLAP-кошти, вибір їх досить широкий, і майже у всіх випадках можна придбати OLAP - сервер того самого виробника, що і сам сервер баз даних.

Зазначимо, що багато клієнтських OLAP-кошти (зокрема, Microsoft Excel 2003, Seagate Analysis та ін) дозволяють звертатися до серверних OLAP-сховищ, виступаючи в цьому випадку в ролі клієнтських додатків, що виконують подібні запити. Крім цього є чимало продуктів, що є клієнтськими додатками до OLAP-засобів різних виробників.

Технічні аспекти багатовимірного зберігання даних

У багатовимірних ХД містяться агрегатні дані різного ступеня подробиці, наприклад, обсяги продажу за днями, місяцями, роками, за категоріями товарів тощо. Мета зберігання агрегатних даних – скоротити час виконаннязапитів, оскільки у більшості випадків для аналізу та прогнозів цікаві не детальні, а сумарні дані. Тому при створенні багатовимірної бази даних завжди обчислюються та зберігаються деякі агрегатні дані.

Зазначимо, що збереження всіх агрегатних даних який завжди виправдано. Справа в тому, що при додаванні нових вимірювань обсяг даних, що становлять куб, зростає експоненційно (іноді говорять про "вибухове зростання" обсягу даних). Якщо точніше говорити, ступінь зростання обсягу агрегатних даних залежить від кількості вимірювань куба і членів вимірювань на різних рівнях ієрархій цих вимірювань . Для вирішення проблеми "вибухового зростання" застосовуються різноманітні схеми, що дозволяють при обчисленні далеко не всіх можливих агрегатних даних досягти прийнятної швидкості виконання запитів.

Як вихідні, і агрегатні дані можуть зберігатися або в реляційних, або в багатовимірних структурах. Тому в даний час застосовуються три способи зберігання даних.

• MOLAP(Multidimensional OLAP) - вихідні та агрегатні дані зберігаються в багатовимірній базі даних. Зберігання даних у багатовимірних структурах дозволяє маніпулювати даними як багатовимірним масивомзавдяки чому швидкість обчислення агрегатних значень однакова для будь-якого з вимірювань. Однак у цьому випадку багатовимірна база даних виявляється надмірною, оскільки багатовимірні дані містять вихідні реляційні дані.
• ROLAP(Relational OLAP) - вихідні дані залишаються у тій же реляційній базі даних, де вони й перебували. Агрегатні ж дані поміщають у спеціально створені їх зберігання службові таблиці у тій базі даних.
• HOLAP(Hybrid OLAP) - вихідні дані залишаються у тій же реляційній базі даних, де вони спочатку перебували, а агрегатні дані зберігаються у багатовимірній базі даних.

Деякі OLAP-засоби підтримують зберігання даних лише у реляційних структурах, деякі - лише багатомірних. Однак більшість сучасних серверних OLAP-засобів підтримують усі три способи зберігання даних. Вибір способу зберігання залежить від обсягу та структури вихідних даних, вимог до швидкості виконання запитів та частоти оновлення OLAP-кубів.

Зазначимо також, що переважна більшість сучасних OLAP-засобів не зберігає "порожніх" значень (прикладом "порожнього" значення може бути відсутність продажів сезонного товару поза сезоном).

Основні поняття OLAP

Тест FAMSI

Технологія комплексного багатовимірного аналізу даних одержала назву OLAP (On-Line Analytical Processing). OLAP – це ключовий компонент організації ХД. Концепція OLAP була описана в 1993 Едгаром Коддом, відомим дослідником баз даних і автором реляційної моделі даних. У 1995 році на основі вимог, викладених Коддом, було сформульовано так званий тест FASMI(Fast Analysis of Shared Multidimensional Information) - швидкий аналіз багатовимірної інформації, що розділяється, що включає наступні вимоги до додатків для багатовимірного аналізу:

• Fast(Швидкий) - надання користувачеві результатів аналізу за прийнятний час (зазвичай не більше 5 с), навіть ціною менш детального аналізу;
• Analysis(Аналіз) - можливість здійснення будь-якого логічного та статистичного аналізу, характерного для цього додатку, та його збереження у доступному для кінцевого користувача вигляді;
• Shared(Розділяється) - розрахований на багато користувачів доступ до даних з підтримкою відповідних механізмів блокувань і засобів авторизованого доступу;
• Multidimensional(Многомірний) - багатовимірне концептуальне подання даних, включаючи повну підтримку для ієрархій та множинних ієрархій (це ключова вимога OLAP);
• Information(Інформація) - додаток повинен мати можливість звертатися до будь-якої потрібної інформації, незалежно від її обсягу та місця зберігання.

Слід зазначити, що OLAP-функціональність може бути реалізована у різний спосіб, починаючи з найпростіших засобів аналізу даних в офісних додатках і закінчуючи розподіленими аналітичними системами, що базуються на серверних продуктах.

Багатовимірне подання інформації

Куби

OLAP надає зручні швидкодіючі засоби доступу, перегляду та аналізу ділової інформації. Користувач отримує природну, інтуїтивно зрозумілу модель даних, організуючи їх як багатомірних кубів (Cubes). Осями багатовимірної системи координат є основні атрибути аналізованого бізнес-процесу. Наприклад, для продажу це може бути товар, регіон, тип покупця. Як один із вимірів використовується час. На перетинах осей вимірів (Dimensions) знаходяться дані, що кількісно характеризують процес - заходи (Measures). Це можуть бути обсяги продажу в штуках або в грошовому вираженні, залишки на складі, витрати і т.п. по тижнях) відомості та здійснювати інші маніпуляції, які йому спадають на думку в процесі аналізу.

Як заходи у тривимірному кубі, зображеному на рис. 26.1, використані суми продажів, а як вимірювання - час, товар і магазин. Вимірювання представлені на певних рівнях угруповання: товари групуються за категоріями, магазини - країнами, а дані про час здійснення операцій - по місяцях. Трохи згодом ми розглянемо рівні угруповання (ієрархії) докладніше.

Мал. 26.1.

"Розрізання" куба

Навіть тривимірний куб складно відобразити на екрані комп'ютера так, щоб були видні значення заходів, що цікавлять. Що вже говорити про куби з кількістю вимірювань, більшою за три. Для візуалізації даних, що зберігаються в кубі, застосовуються, як правило, звичні двовимірні, тобто табличні уявлення, що мають складні ієрархічні заголовки рядків та стовпців.

Двовимірне уявлення куба можна отримати, "розрізавши" його поперек вздовж однієї або декількох осей (вимірювань): ми фіксуємо значення всіх вимірювань, крім двох, - і отримуємо звичайну двовимірну таблицю. У горизонтальній осі таблиці (заголовки стовпців) представлено один вимір , вертикальної (заголовки рядків) - інше, а в осередках таблиці - значення мір. При цьому набір заходів фактично розглядається як один із вимірів: ми або вибираємо для показу один захід (і тоді можемо розмістити в заголовках рядків і стовпців два виміри), або показуємо кілька заходів (і тоді одну з осей таблиці займуть назви мір, а іншу - значення єдиного "нерозрізаного" виміру).

(Levels). Наприклад, мітки, представлені на підтримуються далеко не всіма OLAP-засобами. Наприклад, у Microsoft Analysis Services 2000 підтримуються обидва типи ієрархії, а в Microsoft OLAP Services 7.0 лише збалансовані. Різними в різних OLAP-засобах можуть бути і кількість рівнів ієрархії, і максимально допустима кількість членів одного рівня, і максимально можлива кількість самих вимірювань.

Архітектура OLAP-додатків

Все, що говорилося вище про OLAP, по суті, стосувалося багатовимірного представлення даних. Те, як дані зберігаються, грубо кажучи, не турбує ні кінцевого користувача, ні розробників інструменту, яким клієнт користується.

Багатовимірність в OLAP-додатках може бути поділена на три рівні.

• Багатовимірне подання даних - засоби кінцевого користувача, що забезпечують багатовимірну візуалізацію та маніпулювання даними; шар багатовимірного уявлення абстрагований від фізичної структури даних та сприймає дані як багатовимірні.
• Багатомірна обробка - засіб (мова) формулювання багатовимірних запитів (традиційна реляційна) мова SQLтут виявляється непридатним) і процесор, що вміє обробити та виконати такий запит.
• Багатомірне зберігання - засоби фізичної організації даних, що забезпечують ефективне виконання багатовимірних запитів.

Перші два рівні обов'язково присутні у всіх OLAP-засобах. Третій рівень, хоч і є поширеним, необов'язковий, оскільки дані багатовимірного уявлення можуть витягуватися і з традиційних реляційних структур; процесор багатовимірних запитів у разі транслює багатовимірні запити в SQL-запити, які виконуються реляційної СУБД.

Конкретні OLAP-продукти, як правило, є або засобом багатовимірного представлення даних (OLAP-клієнт - наприклад, Pivot Tables в Excel 2000 фірми Microsoft або ProClarity фірми Knosys), або багатовимірною серверною СУБД (OLAP-сервер - наприклад, Oracle Express Server або Microsoft OLAP Services).

Шар багатовимірної обробки зазвичай буває вбудований в OLAP-клієнт та/або в OLAP-сервер, але може бути виділений у чистому вигляді, як, наприклад, компонент Pivot Table Service фірми Microsoft.