Онлайнова аналітична обробка (OLAP) – це технологія, яка використовується для впорядкування великих бізнес-баз даних і підтримки бізнес-аналітики. Бази даних OLAP поділено на один або кілька кубів, кожен з яких адміністратор кубів упорядковує та проектує відповідно до способу, у який отримується та аналізуються дані, що спрощує створення й використання потрібних звітів зведених таблиць і зведених діаграм.
У цій статті
- Що таке бізнес-аналітика?
- Що таке онлайнова аналітична обробка (OLAP)?
- Функції OLAP у програмі Excel
- Програмні компоненти, потрібні для доступу до джерел даних OLAP
- Відмінності між вихідними даними OLAP та інших технологій
Що таке бізнес-аналітика?
Бізнес-аналітик часто хоче отримати загальну картину бізнесу, побачити ширші тенденції на основі агрегованих даних і розбити ці тенденції за будь-якою кількістю змінних. Бізнес-аналітика – це процес видобування даних із бази даних OLAP і подальшого аналізу цих даних на предмет інформації, яку можна використовувати для прийняття обґрунтованих бізнес-рішень і вжиття дій. Наприклад, OLAP і бізнес-аналітика дають відповіді на такі типи запитань про бізнес-дані:
- Як загальний обсяг продажів всієї продукції за 2007 рік співвідноситься із загальним обсягом продажів за 2006 рік?
- Як наша прибутковість на сьогоднішній день співвідноситься з аналогічним періодом протягом останніх п'яти років?
- Скільки грошей витратили клієнти старше 35 років минулого року і як ця поведінка змінилася з часом?
- Скільки товарів було продано у двох конкретних країнах або регіонах цього місяця порівняно з аналогічним місяцем минулого року?
- Для кожної вікової групи клієнтів, яка розбивка прибутковості (як маржі, так і загальної) за категоріями товарів?
- Пошук найкращих продавців, дистриб'юторів, постачальників, клієнтів, партнерів і замовників.
Що таке онлайнова аналітична обробка (OLAP)?
Бази даних онлайнової аналітичної обробки (OLAP) полегшують запити бізнес-аналітики. OLAP – це технологія для роботи з базами даних, оптимізована, щоб створювати запити й звіти, а не обробляти транзакції. Джерелом даних для OLAP є бази даних онлайнової обробки транзакцій (OLTP), які зазвичай зберігаються в сховищах даних. Дані OLAP отримуються на основі цих історичних даних і об'єднуються в структури, які дають змогу проводити складніший аналіз. Дані OLAP також організовані ієрархічно та зберігаються в кубах, а не в таблицях. Це складна технологія, яка використовує багатовимірні структури для забезпечення швидкого доступу до даних для аналізу. Завдяки цій організації зручно для звіту зведеної таблиці або зведеної діаграми відображати загальні підсумки, наприклад підсумки збуту в усій країні або регіоні, а також докладні відомості для сайтів із особливо високими або слабкими продажами.
Бази даних OLAP призначено для прискорення отримання даних. Оскільки підсумовані значення обчислює сервер OLAP, а не Microsoft Office Excel, під час створення або змінення звіту до програми Excel потрібно надсилати менше даних. Цей підхід дає змогу працювати з значно більшими обсягами вихідних даних, ніж якби їх було впорядковано в традиційну базу даних, де Excel отримує всі окремі записи, а потім обчислює підсумовані значення.
Бази даних OLAP містять два основних типи даних: міри (числові дані), обсяги та середні значення, які використовуються, щоб приймати обґрунтовані бізнес-рішення, і виміри, які використовуються для впорядкування цих заходів. Бази даних OLAP дають змогу впорядкувати дані за багатьма рівнями деталізації, використовуючи для аналізу відомі категорії.
Кожен компонент докладніше описано в розділах нижче.
Куб Структура даних, яка агрегує міри за рівнями та ієрархіями кожного з вимірів, що потрібно проаналізувати. Куби поєднують кілька вимірів, таких як час, географія та лінійка продуктів, зі зведеними даними, такими як показники продажів або запасів. Куби не є «кубами» в строго математичному сенсі, тому що вони не обов'язково мають рівні сторони. Однак вони є влучною метафорою складного поняття.
Міра Набір значень у кубі, що базуються на стовпці в таблиці фактів куба і зазвичай є числовими значеннями. Міри – це центральні значення в кубі, які попередньо обробляються, агрегуються та аналізуються. Наприклад, це дані про продажі, прибутки, доходи й витрати.
Член сім'ї Елемент в ієрархії, який представляє один або кілька екземплярів даних. Учасник може бути унікальним або неунікальним. Наприклад, 2007 і 2008 представляють унікальних учасників на рівні року виміру часу, тоді як січень представляє неунікальних учасників на рівні місяця, оскільки, якщо вимір часу містить дані за більше одного року, може бути кілька січень.
Обчислюваний член Елемент виміру, значення якого обчислюється під час виконання за допомогою виразу. Значення обчислюваних членів може бути отримано зі значень інших членів. Наприклад, обчислюваний член "Прибуток" можна визначити відніманням вартості елемента "Витрати" від значення елемента "Продажі".
Вимір Набір з однієї або кількох організованих ієрархій рівнів у кубі, які користувач розуміє й використовує як основу для аналізу даних. Наприклад, географічний вимір може включати рівні для країн, регіону, області, провінції та міста. Вимір часу може також включати ієрархію з рівнями року, кварталу, місяця та дня. У звіті зведеної таблиці або зведеної діаграми кожна ієрархія стає набором полів, які можна розгорнути або згорнути, щоб відобразити нижчі або вищі рівні.
Ієрархія Логічна структура дерева, яка організовує члени виміру таким чином, що кожен член має одного батьківського члена та нуль або більше дочірніх членів. Дитина – це учасник на наступному нижчому рівні ієрархії, безпосередньо пов'язаній з поточним членом. Наприклад, в ієрархії часу, яка містить рівні "Квартал", "Місяць" і "День", "Січень" є дочірнім для "Квартал 1". Батьківський елемент – це учасник на наступному вищому рівні ієрархії, безпосередньо пов'язаному з поточним членом. Батьківське значення – це, як правило, об'єднання значень усіх його дочірніх елементів. Наприклад, в ієрархії часу, яка містить рівні "квартал", "місяць" і "день", "Квартал1" є батьківським значенням для січня.
Level (Рівень) В ієрархії дані можна впорядкувати за нижчими та вищими рівнями деталізації, наприклад рівнями року, кварталу, місяця та дня в ієрархії часу.
Функції OLAP у програмі Excel
Отримання даних OLAP Підключатися до джерел даних OLAP можна так само, як і до інших зовнішніх джерел даних. Ви можете працювати з базами даних, створеними за допомогою служб Microsoft SQL Server OLAP версії 7.0, Microsoft SQL Server Analysis Services версії 2000 і Microsoft SQL Server Analysis Services версії 2005 – серверних продуктів Microsoft OLAP. Програма Excel також може працювати зі сторонніми продуктами OLAP, сумісними з OLE-DB для OLAP.
Дані OLAP можна відобразити лише як звіт зведеної таблиці чи зведеної діаграми або функцію аркуша, перетворену зі звіту зведеної таблиці, але не як діапазон зовнішніх даних. Звіти зведених таблиць і зведених діаграм OLAP можна зберігати в шаблонах звітів, а також створювати файли зв'язку з даними Office (ODC), щоб підключатися до баз даних OLAP для запитів OLAP. Якщо відкрити файл ODC, у програмі Excel відобразиться пустий звіт зведеної таблиці, готовий до створення макета.
Створення файлів кубів для використання в автономному режимі Можна створити автономний файл куба (.cub) із підмножиною даних із бази даних сервера OLAP. Використовуйте автономні файли кубів для роботи з даними OLAP, коли немає підключення до мережі. Куб дає змогу працювати з більшими обсягами даних у звіті зведеної таблиці або зведеної діаграми, ніж ви могли б це робити іншим способом, і прискорює отримання даних. Файли кубів можна створити лише за використання постачальника OLAP, наприклад Microsoft SQL Analysis Services Server 2005, у якому підтримується ця функція.
Дії сервера Серверна дія – це необов'язкова, але корисна функція, яку адміністратор куба OLAP може визначити на сервері, який використовує елемент куба або вимір як параметр запиту для отримання відомостей у кубі або запуску іншої програми, наприклад браузера. Excel підтримує дії сервера за URL-адресою, звітом, набором рядків, деталізацією та розширенням до докладних відомостей, але не підтримує дії Proprietary, Statement і Dataset.
KPI KPI – це спеціальна обчислювана міра, визначена на сервері та дає змогу відстежувати «ключові показники ефективності», зокрема стан (чи відповідає поточне значення певному числу?) і тенденції (яке значення змінюється з часом?). Коли ці піктограми відображаються, сервер може надсилати пов'язані піктограми, подібні до нового набору піктограм Excel, щоб вказувати на вищий або нижчий рівень стану (наприклад, піктограма стоп-сигналу) або про те, чи значення має тенденцію до зростання чи зниження (наприклад, піктограма стрілки напрямку).
Форматування сервера Адміністратори кубів можуть створювати міри й обчислювані члени з правилами форматування кольорів, шрифту й умовного форматування, які можуть призначатися як стандартні корпоративні бізнес-правила. Наприклад, форматом сервера для прибутку може бути числовий формат грошової одиниці, колір клітинки зелений, якщо значення більше або дорівнює 30 000, і червоний, якщо значення менше 30 000, і стиль шрифту жирним, якщо значення менше 30 000, і звичайним, якщо більше або дорівнює 30 000. Докладні відомості див. в статті "Конструювання макета й формату звіту зведеної таблиці".
Мова інтерфейсу Office Адміністратор куба може визначити переклади даних і помилок на сервері для користувачів, яким потрібно переглядати відомості зведеної таблиці іншою мовою. Ця функція визначена як властивість підключення файлів, і параметри країни або регіону комп'ютера користувача мають відповідати мові інтерфейсу.
Програмні компоненти, потрібні для доступу до джерел даних OLAP
постачальник OLAP . Щоб настроїти джерела даних OLAP для Excel, знадобиться один із таких постачальників OLAP:
- Постачальник Microsoft OLAP Програма Excel включає драйвер джерела даних і клієнтське програмне забезпечення, необхідні для доступу до баз даних, створених за допомогою служб Microsoft SQL Server OLAP Services версії 7.0, служб Microsoft SQL Server OLAP Services версії 2000 (8.0) і Microsoft SQL Server Analysis Services версії 2005 (9.0).
- Сторонні постачальники OLAP Для інших продуктів OLAP потрібно інсталювати додаткові драйвери та клієнтське програмне забезпечення. Щоб використовувати функції Excel для роботи з даними OLAP, сторонній продукт має відповідати стандарту OLE-DB для OLAP і бути сумісним із Microsoft Office. Щоб отримати відомості про інсталяцію та використання технології OLAP від сторонніх постачальників, зверніться до системного адміністратора або постачальника продукту OLAP.
Серверні бази даних і файли кубів Клієнтське програмне забезпечення Excel OLAP підтримує підключення до двох типів баз даних OLAP. Якщо в мережі доступна база даних, розміщена на сервері OLAP, можна безпосередньо отримати вихідні дані з неї. Маючи автономний файл куба з даними OLAP або файл визначення куба, можна підключитися до цього файлу й отримати з нього вихідні дані.
Джерела даних Джерело даних надає доступ до всіх даних у базі даних OLAP або автономному файлі куба. Створивши джерело даних OLAP, можна взяти за основу звіти й повернути ці дані до програми Excel у вигляді звіту зведеної таблиці чи зведеної діаграми або у функції аркуша, перетвореної зі звіту зведеної таблиці.
Microsoft Query За допомогою запиту можна отримати дані із зовнішньої бази даних, як-от Microsoft SQL або Microsoft Access. Щоб отримати дані зі зведеної таблиці OLAP, зв'язаної з файлом куба, не потрібно використовувати Query. Докладні відомості див. в статті "Використання Microsoft Query для отримання зовнішніх даних".
Відмінності між вихідними даними OLAP та інших технологій
Працюючи зі звітами зведених таблиць і зведених діаграм, що використовуються як у джерелі OLAP, так і в інших типах вихідних даних, можна помітити деякі відмінності.
Видобування даних Сервер OLAP повертає нові дані до програми Excel щоразу, коли змінюється макет звіту. Для інших типів зовнішніх джерел даних можна надіслати запит на всі вихідні дані одночасно. Крім того, можна настроїти параметри запиту, лише коли відображаються різні елементи поля фільтра звіту. Крім того, оновити звіт можна кількома іншими способами.
У звітах на основі вихідних даних OLAP недоступні параметри полів фільтра звіту, фоновий запит недоступний і параметр оптимізації пам'яті недоступний.
Примітка.
Параметр оптимізації пам'яті також недоступний для джерел даних OLEDB та звітів зведених таблиць, що базуються на діапазоні клітинок.
Типи полів Поля вимірів джерела OLAP можна використовувати лише як поля рядка (ряду), стовпця (категорії) або поля сторінки. Поля мір можна використовувати лише як поля значень. Для інших типів вихідних даних усі поля можна використовувати в будь-яких частинах звіту.
Доступ до докладних даних Для вихідних даних OLAP сервер визначає рівні деталізації та обчислює зведені значення, тому записи з докладними даними, які утворюють сумарні значення, можуть бути недоступні. Проте сервер може надавати поля властивостей, які можна відображати. Інші типи вихідних даних не мають полів властивостей, але можна відобразити основні відомості для значень полів даних і елементів, а також елементи, які не містять даних.
Поля фільтра звіту OLAP можуть не містити елемента " Усі ", а команда "Показати сторінки фільтра звіту " недоступна.
Початковий порядок сортування Для вихідних даних OLAP спочатку елементи відображаються в тому порядку, у якому їх повертає сервер OLAP. Ці елементи можна відсортувати або перевпорядкувати вручну. Для інших типів вихідних даних елементи в новому звіті спочатку сортуються за зростанням за іменем елемента.
Розрахунки Сервери OLAP надають зведені значення безпосередньо у звіті, тому змінювати функції зведення для полів значень не можна. Для інших типів вихідних даних можна змінити функцію зведення для поля значення та використовувати кілька функцій зведення для одного поля значення. Створювати обчислювані поля або обчислювані елементи у звітах із вихідними даними OLAP не можна.
Проміжні підсумки У звітах із вихідними даними OLAP змінити функцію зведення для проміжних підсумків не можна. Для інших типів вихідних даних можна змінити функції зведення проміжних підсумків і показати або приховати проміжні підсумки для всіх полів рядків і стовпців.
Якщо дані отримано з OLAP, під час обчислення проміжних і загальних підсумків можна включати або виключати приховані елементи. Для інших типів вихідних даних ви можете включати приховані елементи поля фільтра звіту до проміжних підсумків, але приховані елементи в інших полях виключаються за замовчуванням.