Проблеми аналітики, OLAP, сховищ даних викликають все більший інтерес у російських ІТ-фахівців. До теперішнього часу в нашій комп'ютерного друку та Інтернеті опубліковано чимало хороших, академічно за способом викладу матеріалів з цієї тематики, в тому числі і вступного характеру. Ми ж пропонуємо вашій увазі статтю, в якій свідомо намагаємося пояснити OLAP "на пальцях", на конкретному прикладі. Практика показує, що таке пояснення необхідно і деяким ІТ-фахівцям і особливо кінцевим користувачам.
Отже, OLAP * 1 в першому наближенні, "на пальцях", можна визначити як особливий спосіб аналізу даних і отримання звітів. Його суть полягає в наданні користувачу багатовимірної таблиці, автоматично підсумовує дані в різних розрізах і дозволяє інтерактивно керувати обчисленнями і формою звіту. У цій статті буде розказано про технології та описано основні операції OLAP на прикладі аналізу рахунків-фактур підприємства, зайнятого оптовою торгівлею продуктами харчування.
* 1. OLAP - On-Line Analytical Processing, оперативний аналіз даних.
Як інструмент буде розглянута OLAP-система самого простого і недорогого класу - OLAP-клієнт * 1. Для прикладу обрана найбільш проста продукт з числа OLAP-клієнтів - "Контур Стандарт" компанії Intersoft Lab. (Для наочності далі в статті загальноприйняті терміни OLAP будуть позначатися жирним шрифтом і супроводжуватися англомовними аналогами.)
* 1. Більш докладно про класифікацію OLAP-систем розказано в статті "OLAP, зроблено в Росії" в PC Week / RE, №3 / 2001.
Отже, приступимо до роботи з системою. Для початку потрібно описати Джерело даних (data source) - шлях до таблиці і її поля. Це завдання користувача, який знає фізичну реалізацію бази даних. Для кінцевих користувачів він переводить назву таблиці і її полів в терміни предметної області. За "джерелом даних" стоїть локальна таблиця, таблиця або подання (view) SQL-сервера або збережена процедура.
Швидше за все в конкретній базі даних рахунку-фактури зберігаються не в одній, а в декількох таблицях. Крім того, частина полів або записів може не використовуватися для аналізу. Тому далі створюється Вибірка (result set або query), - в якій налаштовуються: алгоритм об'єднання таблиць по ключових полях, умови фільтрації і набір повертаються полів. Назвемо нашу вибірку "Рахунки-фактури" і помістимо в неї все поля джерела даних "Рахунки-фактури". Таким чином, ІТ-фахівець, створюючи семантичний шар, приховує фізичну реалізацію бази даних від кінцевого користувача.
Потім налаштовується OLAP-звіт. Цим може зайнятися фахівець в предметній області. Спочатку поля плоскої вибірки даних розбиваються на дві групи - факти (facts або measures) і вимірювання (dimensions). Факти - це цифри, а вимірювання - "розрізи", в яких будуть підсумовуватися факти. У нашому прикладі вимірами стануть: "Регіон", "Місто", "Покупець", "Товар", "Дата", а факт буде один - поле "Сума" рахунки-фактури. Для факту потрібно вибрати один або кілька алгоритмів агрегації. OLAP здатний не тільки підсумувати підсумки, а й виконувати більш складні обчислення, аж до статистичного аналізу. При виборі декількох алгоритмів агрегації будуть створені віртуальні, обчислювані факти (calculated facts). У прикладі вибрано один алгоритм агрегації - "Сума".
Особлива властивість OLAP-систем - генерація вимірювань і даних по старшим часових періодів з дати і автоматичне обчислення підсумків за цими періодами. Виберемо періоди "Рік", "Квартал" і "Місяць", при цьому даних за кожен день в звіті не буде, але з'являться згенеровані вимірювання "Рік", "Квартал" і "Місяць". Назвемо звіт "Аналіз продажів" і збережемо його. Робота по створенню інтерфейсу аналітичного додатки закінчена.
Тепер, щодня або щомісяця запускаючи цей інтерфейс, користувач буде бачити таблицю і графік, в яких рахунки-фактури підсумовані по товарах, покупцям і періодів.
Для того щоб маніпуляції з даними були інтуїтивно зрозумілі, інструментами управління динамічної таблицею є елементи самої таблиці - її колонки і рядки. Користувач може переміщати їх, видаляти, фільтрувати і виконувати інші OLAP-операції. При цьому таблиця автоматично обчислює нові проміжні і остаточні підсумки.
Наприклад, перетягнувши (операція "move") колонку "Товар" на перше місце, ми отримаємо звіт про порівняння - "Порівняння обсягів продажів товарів за рік". Щоб агрегувати дані за рік, досить перетягнути колонки "Квартал" і "Місяць" у верхню частину таблиці - "область неактивних вимірювань". Вимірювання "Квартал" і "Місяць", перенесені в цю область, будуть закриті (операція "close dimension"), т. Е. Виключені зі звіту; при цьому факти підсумуйте за рік. Незважаючи на те що вимірювання закриті, по ним можна задавати конкретні роки, квартали і місяці для фільтрації даних (операція "filter").
Для більшої наочності змінимо тип графіка, що ілюструє OLAP-таблицю, і його розташування на екрані.
Поглиблення в дані (операція "drill down") дозволяє отримати більш детальні відомості про продажі цікавить нас товару. Клацнувши на знаку "+" напроти товару "Кава", ми побачимо обсяги його продажів в розрізі регіонів. Розкривши регіон "Урал", отримаємо обсяги продажів в розрізі міст Уральського регіону, заглибившись в дані по "Катеринбургу", зможемо переглянути дані по оптовим покупцям цього міста.
Для установки фільтрів можна використовувати і відкриті вимірювання. Щоб порівняти динаміку продажів цукерок у Москві і Єкатеринбурзі, встановимо фільтри на вимірювання "Товар" і "Місто".
Закриємо непотрібні вимірювання і виберемо тип графіка "Лінія". На отриманому графіку можна простежити динаміку продажів, оцінити сезонні коливання і зв'язок падінь і зростання збуту товару в різних містах.
Таким чином, ми переконалися, що OLAP-технологія дозволяє користувачеві з одного інтерфейсу випустити десятки видів самих різних звітів, керуючи динамічної OLAP-таблицею за допомогою миші. Завданням програміста, який володіє таким інструментом, стає рутинне кодування звітних форм, а настройка OLAP-клієнта на бази даних. При цьому способи управління звітом інтуїтивно зрозумілі кінцевому користувачеві.
Дійсно, OLAP - це природне продовження і розвиток ідеї електронних таблиць. По суті, візуальний інтерфейс OLAP - це теж електронна таблиця (spreadsheet), але оснащена потужною машиною обчислень і особливим стандартом представлення даних і управління ними. Більш того, деякі OLAP-клієнти реалізовані як add-in до MS Excel. Тому мільйонна армія "білих комірців", впевнено володіє електронними таблицями, дуже швидко освоює і OLAP-інструменти. Для них це "оксамитова революція", що надає нові можливості, але не сполучена з необхідністю перевчатися.
Якщо читач, прочитавши цю статтю, чи не втратив інтересу до OLAP, він може звернутися до згаданих на початку матеріалами. Збірники таких матеріалів розміщені на ряді сайтів в Інтернеті, включаючи сайт Intersoft lab - www.iso.ru. З нього можна також завантажити демонстраційну версію системи "Контур Стандарт" з описаним в статті прикладом.
Перший інтерфейс зведених таблиць, званих також зведеними звіти, був включений до складу Excel ще в 1993м році (версії Excel 5.0). Незважаючи на безліч корисних функціональних можливостей, Він практично не застосовується в роботі більшістю користувачів Excel. Навіть досвідчені користувачі часто мають на увазі під терміном «зведений звіт» щось побудоване за допомогою складних формул. Спробуємо популяризувати використання зведених таблиць в повсякденній роботі економістів. У статті обговорюються теоретичні основи створення зведених звітів, даються практичні рекомендації по їх використанню, а також наводиться приклад доступу до даних на основі декількох таблиць.
Терміни багатовимірного аналізу даних
Більшість економістів чули терміни «багатовимірні дані», «віртуальний куб», «OLAP-технології» і т.п. Але при детальному розмові зазвичай з'ясовується, що багато хто не дуже уявляють, про що йде мова. Тобто люди мають на увазі щось складне і зазвичай не має відношення до своєї основної діяльності. Насправді це не так.
Багатовимірні дані, вимірювання
Можна з упевненістю стверджувати, що економісти практично постійно стикаються з багатовимірними даними, але намагаються представити їх в обумовленому вигляді за допомогою електронних таблиць. Під багатомірністю тут мається на увазі можливість введення, перегляду або аналізу однієї і тієї ж інформації зі зміною зовнішнього вигляду, застосуванням різних угруповань і сортувань даних. Наприклад, план продажів можна проаналізувати за наступними критеріями:
- види або групи товарів;
- бренди або категорії товарів;
- періоди (місяць, квартал, рік);
- покупці або групи покупців;
- регіони продажів
- і т.п.
Кожен з наведених критеріїв в термінах багатовимірного аналізу даних називається «виміром». Можна сказати, що вимір характеризує інформацію за певним набором значень. Спеціальним типом вимірювання багатовимірної інформації є «дані». У нашому прикладі даними плану продажів можуть бути:
- об'єм продажу;
- ціна продажу;
- індивідуальна знижка
- і т.п.
Теоретично дані можуть також бути стандартним виміром багатовимірної інформації (наприклад, можна згрупувати дані за ціною продажу), але зазвичай все-таки дані є спеціальним типом значень.
Таким чином, можна сказати, що в практичній роботі економісти використовуються два типи інформації: багатовимірні дані (фактичні та планові числа, мають безліч ознак) і довідники (характеристики або вимірювання даних).
OLAP
Абревіатура OLAP (online analytical processing) в дослівному перекладі звучить як «аналітична обробка в реальному часі». Визначення на дуже конкретне, під нього можна підвести практично будь-який звіт будь-якого програмного продукту. За змістом OLAP має на увазі технологію роботи зі спеціальними звітами, включаючи програмне забезпечення, для отримання і аналізу якраз багатовимірних структурованих даних. Одним з популярних програмних продуктів, що реалізують OLAP-технології, є SQL Server Analysis Server. Деякі навіть помилково вважають його єдиним представником програмної реалізації даної концепції.
Віртуальний куб даних
«Віртуальний куб» (багатовимірний куб, OLAP-куб) - це спеціальний термін, запропонований деякими постачальниками спеціалізованого програмного забезпечення. OLAP-системи зазвичай готують і зберігають дані у власних структурах, а спеціальні інтерфейси аналізу (наприклад, зведені звіти Excel) звертаються до даних цих віртуальних кубів. При цьому використання подібного виділеного сховища зовсім не обов'язково для обробки багатовимірної інформації. У загальному випадку, віртуальний куб - це і є масив спеціально оптимізованих багатовимірних даних, який використовується для створення зведених звітів. Він може бути отриманий як через спеціалізовані програмні засоби, так і через простий доступ до таблиць бази даних або будь-який інший джерело, наприклад до таблиці Excel.
Зведена таблиця
«Зведений звіт» (зведена таблиця, Pivot Table) - це призначений для користувача інтерфейс для відображення багатовимірних даних. За допомогою даного інтерфейсу можна групувати, сортувати, фільтрувати і змінювати розташування даних з метою отримання різних аналітичних вибірок. Оновлення звіту проводиться простими засобами призначеного для користувача інтерфейсу, дані автоматично агрегируются за заданими правилами, при цьому не потрібно додатковий або повторне введення будь-якої інформації. інтерфейс зведених таблиць Excel є, мабуть, найпопулярнішим програмним продуктом для роботи з багатовимірними даними. Він підтримує в якості джерела даних як зовнішні джерела даних (OLAP-кубів і реляційних баз даних), так і внутрішні діапазони електронних таблиць. Починаючи з версії 2000 (9.0), Excel підтримує також графічну форму відображення багатовимірних даних - зведена діаграма (Pivot Chart).
Реалізований в Excel інтерфейс зведених таблиць дозволяє розташувати вимірювання багатовимірних даних в області робочого листа. Для простоти можна уявляти собі зведену таблицю, як звіт, що лежить зверху діапазону комірок (насправді є певна прив'язка форматів осередків до полів зведеної таблиці). Зведена таблиця Excel має чотири області відображення інформації: фільтр, стовпці, рядки та дані. Вимірювання даних іменуються полями зведеної таблиці. Ці поля мають власні властивості і формат відображення.
Ще раз хочеться звернути увагу, що зведена таблиця Excel призначена виключно для аналізу даних без можливості редагування інформації. Ближче за змістом було б повсюдне вживання терміна «зведений звіт» (Pivot Report), і саме так цей інтерфейс і називався до 2000го року. Але чомусь в наступних версіях розробники від нього відмовилися.
Редагування зведених таблиць
За своїм визначенням OLAP-технологія, в принципі, не має на увазі можливість зміни вихідних даних при роботі зі звітами. Проте, на ринку сформувався цілий клас програмних систем, Що реалізують можливості як аналізу, так і безпосереднього редагування даних в багатовимірних таблицях. В основному такі системи орієнтовані на вирішення завдань бюджетування.
Використовуючи вбудовані засоби автоматизації Excel, можна вирішити безліч нестандартних завдань. Приклад реалізації редагування для зведених таблиць Excel на основі даних робочого листа можна знайти на нашому сайті.
Підготовка багатовимірних даних
підійдемо до практичного застосування зведених таблиць. Спробуємо проаналізувати дані про продажі в різних напрямках. файл pivottableexample.xls складається з декількох листів. лист прикладмістить основну інформацію про продажі за певний період. Для простоти прикладу будемо аналізувати єдиний числовий показник - обсяг продажу в кг. Є такі ключові вимірювання даних: продукція, покупець і перевізник (транспортна компанія). Крім того, є кілька додаткових вимірів даних, які є ознаками продукту: тип, бренд, категорія, постачальник, а також покупця: тип. Ці дані зібрані на аркуші Довідники. На практиці подібних вимірів може бути набагато більше.
лист прикладмістить стандартна програма аналізу даних - автофильтр. Дивлячись на приклад заповнення таблиці, очевидно, що нормальному аналізу піддаються дані про продажі по датах (вони розташовані за стовпцями). Крім того, використовуючи автофильтр можна спробувати підсумувати дані по сполученням одного або кількох ключових критеріїв. Абсолютно відсутня інформація про бренди, категоріях і типах. Немає можливості згрупувати дані з автоматичним підсумовуванням за певним ключу (наприклад, по покупцям). Крім того, набір дат зафіксовано, і переглянути підсумкову інформацію за певний період, наприклад, 3 дні, автоматичними засобами не вдасться.
Взагалі, наявність визначеного розташування дати в даному прикладі - головний недолік таблиці. Розташувавши дати по стовпцях, ми як би визначили вимір цієї таблиці, таким чином, позбавивши себе можливості використовувати аналіз за допомогою зведених таблиць.
По-перше, треба позбутися цього недоліку - тобто прибрати зумовлене розташування одного з вимірів вихідних даних. Приклад коректної таблиці - лист продажі.
Таблиця має форму журналу введення інформації. Тут дата є рівноправним виміром даних. Також слід зауважити, що для подальшого аналізу в зведених таблицях абсолютно байдуже відносне положення рядків один щодо одного (інакше кажучи, сортування). Цими властивостями володіють записи в реляційних базах даних. Саме на аналіз великих обсягів баз даних орієнтований в першу чергу інтерфейс зведених таблиць. Тому необхідно дотримуватися цих правил і при роботі з джерелом даних у вигляді діапазонів осередків. При цьому ніхто не забороняє використовувати в роботі інтерфейсні засоби Excel - зведені таблиці аналізують тільки дані, а форматування, фільтри, угрупування і сортування вихідних осередків можуть бути довільними.
Від автофильтра до зведеного звіту
Теоретично на даних листа Продажі вже можна проводити аналіз в трьох вимірах: товари, покупці і перевізники. Дані про властивості продукції та покупців на даному аркуші відсутні, що, відповідно, не дозволить показати їх і у зведеній таблиці. У нормальному режимі створення зведеної таблиці для вихідних даних Excel не дозволяє пов'язувати дані декількох таблиць за певними полях. Обійти це обмеження можна програмними засобами - див. Приклад-додаток до даної статті на нашому сайті. Щоб не вдаватися до програмних методів обробки інформації (тим більше, що вони і не універсальні), слід додати додаткові характеристики безпосередньо в форму введення журналу - см. Лист ПродажіАналіз.
Застосування функцій VLOOKUP дозволяє легко доповнити вихідні дані відсутніми характеристиками. Тепер, застосовуючи автофильтр, можна аналізувати дані в різних вимірах. Але залишається невирішеною проблема угруповань. Наприклад, відстежити суму тільки за брендами на певні дати досить проблематично. Якщо обмежуватися формулами Excel, то потрібно будувати додаткові вибірки, використовуючи функцію SUMIF.
Тепер подивимося які можливості дає інтерфейс зведених таблиць. на аркуші СводАналізпобудовано кілька звітів на основі діапазону комірок з даними листа ПродажіАналіз.
Перша таблиця аналізу побудована через інтерфейс Excel 2007 Стрічка \\ Вставка \\ Зведена таблиця (В Excel 2000-2003 меню Дані \\ Зведена таблиця).
Друга і третя таблиці створені через копіювання і подальшу настройку. Джерело даних для всіх таблиць один і той же. Можете це перевірити, змінивши вихідні дані, потім треба оновити дані зведених звітів.
З нашої точки зору, переваги в наочності інформації очевидні. Ви можете міняти місцями фільтри, стовпці і рядки і, приховувати певні групи значень будь-яких вимірювань, застосовувати ручне перетягування і автоматичне сортування.
Властивості і форматування
Крім безпосереднього відображення даних, є великий набір можливостей по відображенню зовнішнього вигляду зведених таблиць. Зайві дані можна приховувати, використовуючи фільтри. Для одиничного елемента або поля простіше користуватися пунктом контекстного меню видалити(У версії 2000-2003 приховати).
Задавати відображення інших елементів зведеної таблиці також бажано не через форматування комірки, а через настройку поля або елемента зведеної таблиці. Для цього необхідно підвести курсор миші до потрібного елементу, дочекатися появи спеціальної форми курсору (у вигляді стрілки), потім через одинарний клік виділити обраний елемент. Після виділення можна змінювати вид через стрічку, контекстне меню або викликати стандартний діалог формату комірки:
Крім того, в Excel 2007 з'явилося безліч визначених стилів відображення зведеної таблиці:
Зверніть увагу, що в діаграмі активні керуючі фільтри і області перетягування.
Доступ до зовнішніх даних
Як уже зазначалося, мабуть, найбільший ефект від застосування зведених таблиць можна отримати при доступі до даних зовнішніх джерел - OLAP-кубів і запитам до баз даних. Такі джерела зазвичай зберігають великі обсяги інформації, а також мають визначену реляционную структуру, Що дозволяє легко визначити вимірювання багатовимірних даних (поля зведеної таблиці).
Excel підтримує безліч типів джерел зовнішніх даних:
Найбільшого ефекту від використання зовнішніх джерел інформації можна домогтися, застосовуючи засоби автоматизації (програми VBA) як для отримання даних, так і для їх попередньої обробки в зведених таблицях.
Аналітична аналітична обробка (OLAP) - це технологія, яка використовується для впорядкування баз даних великого бізнесу і підтримки бізнес-аналітики. Бази даних OLAP діляться на один або кілька кубів, і кожен з них організовується адміністратором куба так, щоб він відповідав способу отримання та аналізу даних, щоб полегшити створення і використання звітів зведеної таблиці і звітів зведеної діаграми, які вам знадобляться.
В цій статті
Що таке бізнес-аналітика?
Бізнес-аналітик часто хоче отримати більшу картину бізнесу, щоб переглядати ширші тенденції на основі агрегованих даних, а також переглядати тенденції з розбивкою на будь-яку кількість змінних. Бізнес-аналітика - це процес отримання даних з бази даних OLAP і аналізу цих даних для отримання відомостей, які можна використовувати для прийняття поінформованих бізнес-рішень і виконання дій. Наприклад, за допомогою OLAP і бізнес-аналітики ви можете відповісти на наступні питання про бізнес-даних.
Як загальна сума продажів всіх продуктів в 2007 порівнюється з обсягом продажів з 2006?
Як це порівнюється з датою і часом за вигідними періодом за останні п'ять років?
Скільки грошей клієнти витратили на 35 в минулий рік і яким чином змінилося це поведінка з плином часу?
Скільки продуктів було продано в двох певних країнах / регіонах у цьому місяці, на відміну від того ж місяця минулого року?
Для кожної групи вікових груп клієнтів Яке поділ рентабельності (як відсоток маржі, так і підсумок) за категоріями товарів?
Пошук кращих і нижніх продавців, дистриб'юторів, постачальників, клієнтів, партнерів і клієнтів.
Що таке аналітична обробка в Інтернеті (OLAP)?
Бази даних OLAP (Online Analytical Processing) спрощують запити бізнес-аналітики. OLAP - це технологія баз даних, оптимізована для запитів і звітів, а не для обробки транзакцій. Джерело даних для OLAP - це оперативні бази даних обробки транзакцій (OLTP), які зазвичай зберігаються в сховищах даних. Дані OLAP витягуються з цих історичних даних і об'єднуються в структури, які допускають складний аналіз. Дані OLAP також упорядковуються ієрархічно і зберігаються в кубах, а не в таблицях. Це складна технологія, яка використовує багатовимірні структури для забезпечення швидкого доступу до даних для аналізу. У цій Організації для звіту зведеної таблиці або звіту зведеної діаграми можна легко відобразити зведені дані високого рівня, такі як підсумки продажів по всій країні або регіону, а також відобразити відомості про сайти, де продажі особливо великі або слабкі.
Бази даних OLAP призначені для прискорення завантаження даних. Оскільки OLAP-сервер, а не Microsoft Office Excel, обчислює узагальнені значення, при створенні або зміні звіту необхідно відправляти в Excel менші дані. Цей підхід дозволяє працювати з великим обсягом вихідних даних, ніж в разі, якщо дані були організовані в традиційній базі даних, де Excel витягує всі окремі записи і обчислює узагальнені значення.
Бази даних OLAP містять два основних типи даних: заходи, які являють собою числові дані, кількості і середні значення, які використовуються для прийняття поінформованих бізнес-рішень, і вимірювання, які є категоріями, використовуваними для впорядкування цих заходів. Бази даних OLAP допомагають упорядкувати дані за допомогою безлічі рівнів деталізації, використовуючи ті ж категорії, які ви знаєте для аналізу даних.
У наступних розділах кожен компонент докладно описаний нижче.
кубічних Структура даних, що об'єднує заходи за рівнями і ієрархій кожного вимірювання, яке ви хочете проаналізувати. Куби об'єднують кілька вимірів, таких як час, географія і рядки продукту, з підсумковими даними, такими як продажі і запаси. Куби - це не "Куби" в строгому математичному сенсі, так як вони не обов'язково мають однакові сторони. Тим не менш, вони представляють собою метафору АПТ для складного поняття.
вимірювань Набір значень в Кубі, заснованих на стовпці в таблиці фактів куба і який зазвичай є числовим значенням. Заходи - це центральні значення в Кубі, які попередньо обробляються, обробляються і аналізуються. Найбільш поширеними прикладами є продажу, доходи, доходи і витрати.
Member Елемент в ієрархії, що представляє один або кілька входжень даних. Елемент може бути як унікальним, так і неунікальним. Наприклад, 2007 і 2008 представляють унікальні елементи на рівні року вимірювання часу, в той час як січня подають неунікальні елементи на рівні місяця, так як у вимірі часу є більш одного січня, так як в ньому містяться дані для більш ніж одного року.
обчислюваний елемент Елемент вимірювання, значення якого обчислюється під час виконання за допомогою виразу. Значення обчислюваних елементів можуть бути похідними від значень інших членів. Наприклад, обчислюваний елемент "прибуток" може бути визначений шляхом віднімання значення елемента, а також витрат з значення елемента, продажу.
вимір Набір з однієї або декількох впорядкованих ієрархій рівнів Куба, які користувач розуміє і використовує як основу для аналізу даних. Наприклад, географічний вимір може включати рівні для країни / регіону, штат / район і місто. Крім того, вимірювання часу може включати ієрархію з рівнем "рік", "квартал", "місяць" і "день". У звіті зведеної таблиці або звіті зведеної діаграми кожна ієрархія стає набором полів, які можна розгортати і згортати, щоб показати більш низькі або більш високі рівні.
ієрархія Логічна структура дерева, яка впорядковує елементи вимірювання, так що у кожного елемента є один батьківський елемент і нуль або більше дочірніх елементів. Дочірній елемент - це член більш ранньої групи в ієрархії, безпосередньо пов'язаний з поточним членом. Наприклад, в ієрархії часу, що містить рівні квартал, місяць і день, Январь є дочірнім елементом Qtr1. Батьківський елемент - це член нижчого рівня в ієрархії, безпосередньо пов'язаний з поточним членом. Батьківським значенням зазвичай є консолідація значень всіх дочірніх елементів. Наприклад, в ієрархії часу, що містить рівні "квартал", "місяць" і "день", Qtr1 є батьківським для січня.
рівень В ієрархії дані можуть бути організовані в більш низькі і більш високі рівні деталізації, такі як роки, квартали, місяці і денні рівні в ієрархії часу.
Функції 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 версії 2005, що підтримує цю функцію.
дії сервера Дія сервера - це необов'язкова функція, яку адміністратор куба OLAP може визначати на сервері, який використовує елемент куба або міру як параметр в запиті для отримання відомостей в Кубі, або для запуску іншої програми, наприклад браузера. В Excel підтримується URL-адреси, звіти, набори рядків, деталізація і розгортання докладних серверних дій, але не підтримує власний власний оператор і набір даних.
КПЕ Ключовий показник ефективності - це особлива обчислюється міра, певна на сервері, що дозволяє відстежувати "ключові показники ефективності", в тому числі стан (поточне значення відповідає певному номеру). і тенденція (значення з плином часу). Коли вони відображаються, сервер може надсилати відповідні значки, схожі на новий значок Excel, щоб вони збудували вище або нижче рівнів стану (наприклад, для значка зупинки), а також розкрутка значення вгору або вниз (наприклад, значок стрілки напрямку).
Форматування на сервері Адміністратори кубів можуть створювати заходи і обчислювані елементи за допомогою колірного форматування, форматування шрифту і правил умовного форматування, Які можуть бути призначені як корпоративне стандартне бізнес-правило. Наприклад, серверний формат для доходу може являти собою числовий формат валюти, колір осередки зеленого кольору, якщо значення більше або дорівнює 30 000, і червоний, якщо значення менше 30 000, і стиль шрифту напівжирний, якщо значення менше 30 000, і при позитивному значенні - звичайний. більше або дорівнює 30 000. додаткові відомості можна знайти .
Мова інтерфейсу Office Адміністратор куба може визначати переклади для даних і помилок на сервері для користувачів, які повинні переглядати інформацію зведеної таблиці на іншу мову. Ця функція визначена як властивість з'єднання з файлом, а регіональні параметри комп'ютера користувача і країни повинні відповідати мові інтерфейсу.
Програмні компоненти, необхідні для доступу до джерел даних OLAP
Постачальник OLAP Для настройки джерел даних OLAP для Excel необхідний один із зазначених нижче провайдерів OLAP.
Постачальник Microsoft OLAP В Excel входить драйвер джерела даних і клієнтське програмне забезпечення для доступу до баз даних, створеним за допомогою служб Microsoft SQL Server olap версії 7,0, Microsoft SQL Server olap версії 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, ви можете засновувати на ньому звіти і повертати дані OLAP в Excel у вигляді звіту зведеної таблиці або звіту зведеної діаграми, а також у функції листа, перетвореної зі звіту зведеної таблиці.
Microsoft Query За допомогою Query можна отримувати дані із зовнішньої бази даних, наприклад Microsoft SQL або Microsoft Access. Для отримання даних зі зведеної таблиці OLAP, пов'язаної з файлом куба, використовувати запит не потрібно. Додаткові відомості .
Відмінності у функціях OLAP і не-OLAP вихідних даних
Якщо ви працюєте зі звітами зведених таблиць і зведеними діаграмами з OLAP вихідні дані та інших типів вихідних даних, ви побачите деякі відмінності функцій.
витяг даних Сервер OLAP повертає нові дані в Excel при кожній зміні макета звіту. При використанні інших типів зовнішніх джерел даних ви запитуєте все вихідні дані за один раз або можете задавати параметри для запиту лише при відображенні різних елементів полів фільтра звіту. Крім того, у вас є ще кілька варіантів оновлення звіту.
У звітах, заснованих на вихідних даних OLAP, параметри поля фільтра звіту недоступні, фоновий запит недоступний, а параметр оптимізації пам'яті недоступний.
Примітка: Параметр оптимізації пам'яті також недоступний для джерел даних OLEDB і звітів зведених таблиць, заснованих на діапазоні осередків.
типи полів Вихідні дані OLAP. поля вимір можуть використовуватися тільки як рядки (ряди), стовпці (категорія) або поля сторінки. Поля заходів можна використовувати тільки в якості полів значень. Для інших типів вихідних даних все поля можна використовувати в будь-якій частині звіту.
Доступ до докладним даними Для вихідних даних OLAP сервер визначає доступні рівні деталізації і обчислює зведені значення, тому записи з докладними відомостями, які становлять сумарні значення, можуть бути недоступні. Проте, сервер може надати поля властивостей, який ви можете відобразити. Інші типи вихідних даних не мають полів властивостей, але ви можете відобразити основні відомості для значень полів даних і елементів, а також відобразити елементи без даних.
У полів фільтра звіту OLAP може бути не усе елементи, а команда Показати сторінки фільтра звіту недоступна.
Початковий порядок сортування Для вихідних даних OLAP елементи спочатку відображаються в тому порядку, в якому вони повертаються OLAP сервером. Ви можете впорядкувати або вручну змінити порядок елементів. Для інших типів вихідних даних елементи нового звіту спочатку сортуються по імені елемента в зростаючому порядку.
ними Сервери OLAP надають узагальнені значення безпосередньо для звіту, тому неможливо змінити підсумкові функції для полів значень. Для інших типів вихідних даних ви можете змінити статистичну функцію для поля значення і використовувати кілька підсумкових функцій для одного і того ж поля значення. Не можна створювати обчислювані поля і обчислювані елементи в звітах з вихідними даними OLAP.
проміжних підсумків У звітах з вихідними даними OLAP можна змінити підсумкову функцію для проміжних підсумків. З іншими типами вихідних даних ви можете змінити підсумкові функції для проміжних підсумків і відобразити або приховати проміжні підсумки для всіх полів рядків і стовпців.
Для вихідних даних OLAP ви можете включити або виключити приховані елементи при розрахунку проміжних і загальних підсумків. Для інших типів вихідних даних ви можете включати приховані елементи полів фільтра звіту в проміжні підсумки, але приховані елементи в інших полях будуть виключені за замовчуванням.
Робота з OLAP-кубом в MS Excel
1. Отримуємо дозвіл на доступ до OLAP-кубу SQL Server Analysis Services (SSAS)
2. На вашому комп'ютері повинен бути встановлений MS Excel 2016/2013/2010 року (можна і MS Excel 2007, але в ньому працювати не зручно, і зовсім бідна функціональність MS Excel 2003)
3. Відкриваємо MS Excel, запускаємо майстер настройки з'єднання з аналітичною службою:
3.1 Вказуємо ім'я або IP-адреса діючого сервера OLAP (іноді потрібно вказати номер відкритого порту, Наприклад, 192.25.25.102:80); використовується доменна аутентифікація:
3.2 Вибираємо багатовимірну базу даних і аналітичний куб (в разі наявності прав доступу до кубу):
3.3 Налаштування з'єднання з аналітичною службою будуть збережені в odc-файлі на Вашому комп'ютері:
3.4 Вибираємо вид звіту (зведена таблиця / графік) і вказуємо місце для його розміщення:
Якщо в книзі Excel вже створено підключення, то їм можна скористатися повторно: головне меню «Дані» -\u003e «Існуючі підключення» -\u003e вибираємо підключення в цій книзі -\u003e вставляємо зведену таблицю в зазначену осередок.
4. Успішно підключилися до кубу, можна приступати до інтерактивного аналізу даних:
Приступаючи до інтерактивного аналізу даних необхідно визначити, які з полів будуть брати участь у формуванні рядків, стовпців і фільтрів (сторінок) зведеної таблиці. У загальному випадку зведена таблиця є тривимірною, і можна вважати, що третій вимір розташоване перпендикулярно екрану, а ми спостерігаємо перетину, паралельні площині екрану і визначаються тим, яка «сторінка» обрана для відображення. Фільтрацію можна здійснити шляхом перетягування відповідних атрибутів вимірювань в область фільтрів звіту. Фільтрація обмежує простір куба, зменшуючи навантаження на сервер OLAP, тому краще в першу чергу встановити необхідні фільтри. Потім слід розміщувати атрибути вимірювань в областях рядків, стовпців і показники в область даних зведеної таблиці.
Кожен раз, коли змінюється зведена таблиця, на сервер OLAP автоматично відправляється MDX-інструкція, по виконанні якої повертаються дані. Чим більше і складніше обсяг оброблюваних даних, розраховуються показників, тим довше час виконання запиту. Скасувати виконання запиту можна натисканням клавіші Escape. Останні виконані операції можна скасувати (Ctrl + Z) або повернути (Ctrl + Y).
Як правило, для найчастіше вживаних швидких атрибутів вимірювань в кубі зберігаються заздалегідь розраховані агреговані дані, тому час відгуку таких запитів кілька секунд. Однак все можливе комбінації агрегацій прорахувати неможливо, так як для цього може знадобитися дуже багато часу і місця для зберігання. Для виконання масивних запитів до даних на рівні деталізації можуть знадобитися значні обчислювальні ресурси сервера, тому час їх виконання може бути тривалим. Після читання даних з дискових накопичувачів сервер поміщає їх в кеш оперативної пам'яті, Що дозволяє наступним такими запитами виконуватися миттєво, оскільки дані будуть вилучатись вже з кешу.
Якщо Ви вважаєте, що ваш запит буде часто використовуватися і час його виконання незадовільно, Ви можете звернутися в службу супроводу аналітичних розробок для оптимізації виконання запиту.
Після розміщення ієрархії в області рядків / стовпців можливо приховати окремі рівні:
У ключових атрибутів (Рідше - для атрибутів вище за ієрархією) вимірювань можуть бути властивості - описові характеристики, які можуть відображатися як у підказках, так і у вигляді полів:
Якщо потрібно відобразити відразу кілька властивостей полів, то можна скористатися відповідним діалоговим списком:
Визначені користувачем набори
В Excel 2010 з'явилася можливість інтерактивного створення власних (визначаються користувачем) наборів з елементів вимірювання:
На відміну від наборів створюваних і зберігаються централізовано на стороні куба, призначені для користувача набори зберігаються локально в книзі Excel і можуть використовуватися в подальшому:
Просунуті користувачі можуть створювати набори, використовуючи MDX конструкції:
Налаштування властивостей зведеної таблиці
За допомогою пункту "Параметри зведеної таблиці ..." контекстного меню (клацання правою кнопкою миші в рамках зведеної таблиці) надається можливість настройки зведеної таблиці, наприклад:
- вкладка "Висновок", параметр "Класичний макет зведеної таблиці" - зведена таблиця стає інтерактивною, можна перетягувати поля (Drag & Drop);
- вкладка "Висновок", параметр "Показувати елементи без даних в рядках" - у зведеній таблиці будуть відображатися порожні рядки, що не містять жодного значення показника за відповідними елементами вимірювань;
- вкладка "Розмітка і формат", параметр "Зберігати форматування осередків при оновленні" - у зведеній таблиці можна перевизначити і зберегти формат осередків при оновленні даних;
Створення зведених діаграм
Для наявної зведеної OLAP-таблиці можна створити зведену діаграму - кругову, лінійчату, гистограмму, графік, точкову і інші види діаграм:
При цьому зведена діаграма буде синхронізована зі зведеною таблицею - при зміні складу показників, фільтрів, вимірювань в зведеній таблиці також оновлюється зведена таблиця.
Створення інформаційних панелей
Виділимо вихідну зведену таблицю, скопіюємо її в буфер обміну (Ctrl + C) і вставимо її копію (Ctrl + V), в якій змінимо склад показників:
Для одночасного управління декількома зведеними таблицями вставимо зріз (новий функціонал, доступний, починаючи з версії MS Excel 2010). Підключимо наш Slicer до зведених таблицях - клацання правою кнопкою миші в рамках зрізу, вибір в контекстному меню пункту "Підключення до зведеної таблиці ...". Слід зазначити, що може бути кілька панелей зрізів, які можуть обслуговувати одночасно зведені таблиці на різних аркушах, що дозволяє створювати скоординовані інформаційні панелі (Dashboard).
Панелі зрізів можна налаштовувати: необхідно виділити панель, потім см. Пункти "Розмір та властивості ...", "Налаштування зрізу", "Призначити макрос" в контекстному меню, що активується по правому щілинку миші або пункт "Параметри" головного меню. Так, можливо встановити кличество стовпців для елементів (кнопок) зрізу, розміри кнопок зрізу і панелі, визначити для зрізу колірну гамму і стиль оформлення з наявного набору (або створити свій стиль), визначити власний заголовок панелі, призначити програмний макрос, за допомогою якого можна розширити функціонал панелі.
Виконання MDX запиту з Excel
- Перш за все, необхідно виконати операцію DRILLTHROUGH на якомусь показнику, тобто спуститися до деталізованим даними (деталізовані дані відображаються на окремому аркуші), і відкрити список підключень;
- Відкрити властивості підключення, перейти на вкладку «Визначення»;
- Вибрати тип команди за замовчуванням, а в поле тексту команди розмістити заздалегідь підготовлений MDX запит;
- При натисканні кнопки після перевірки правильності синтаксису запиту і наявності відповідних прав доступу запит виповниться на сервері, а результат буде представлений в поточному аркуші у вигляді звичайної плоскої таблиці.
Подивитися текст MDX-запиту, що генерується Excel, можна за допомогою установки безкоштовного доповнення, яке надає також і інші додаткові функціональні можливості.
Переклад на інші мови
Аналітичний куб підтримує локалізацію на російську та англійську мови (при необхідності можлива локалізація на інші мови). Переклади поширюються на найменування вимірювань, ієрархій, атрибутів, папок, заходів, а також елементи окремих ієрархій в разі наявності для них перекладів на стороні облікових систем / сховища даних. Щоб змінити мову, необхідно відкрити властивості підключення і в рядку підключення додати наступну опцію:
Extended Properties \u003d "Locale \u003d 1033"
де 1033 - локалізація на англійську мову
1049 - локалізація на російську мову
Додаткові розширення Excel для Microsoft OLAP
Можливості роботи з OLAP-кубами Microsoft зростуть, якщо використовувати додаткові розширення, наприклад, OLAP PivotTable Extensions, завдяки якому можна користуватися швидким пошуком по вимірюванню:
