Практичні роботи з olap технологія в excel. Olap куби в excel. Створення інформаційних панелей

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

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

Щоб створити автономний куб даних, спочатку створіть зведену таблицю OLAP. Помістіть курсор у межах зведеної таблиці та клацніть на кнопці Засоби OLAP (OLAP Tools) контекстної вкладки Параметри (Tools), що входить до групи контекстних вкладок Робота зі зведеними таблицями (PivotTable Tools). Виберіть Автономний режим OLAP (Offline OLAP) (мал. 9.8).

Рис. 9.8. Створення автономного куба даних

На екрані з'явиться діалогове вікно параметрів автономного куба даних OLAP. Клацніть на кнопці Створити автономний файл даних (Create Offline Data File). Ви запустили майстер створення файлу куба даних. Натисніть кнопку Далі (Next), щоб продовжити процедуру.

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

Натисніть кнопку Далі, щоб перейти до наступного діалогового вікна майстра. У ньому ви отримуєте можливість вказати члени або елементи даних, які не включатимуться до куба. Зокрема, вам не знадобиться міра Internet Sales-Extended Amount, тому прапорець для неї буде скинутий у списку. Скинутий прапорець вказує на те, що вказаний елемент не імпортуватиметься і займатиме зайве місце на жорсткому диску.

На останньому етапі вкажіть розташування та ім'я куба даних. У нашому випадку файл куба буде названий MyOfflineCube.cub і розташовуватиметься в папці Work.

Файли кубів даних мають розширення .cub

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

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

Робота з файлами автономного куба

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

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

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

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

Робота з майстром автономного куба

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

Переклад даних у автономний режимта їх зворотне підключення

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

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

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

Створення автономного файлу куба на комп'ютері. В розділі Створення файлу автономного куба із бази даних OLAP-сервера(Нижче в цій статті).

Відключення від мережі та робота з файлом автономного куба.

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

Оновлення файлу автономного куба з новими даними та повторне створення автономного файлу куба. Ознайомтеся з розділом оновлення та повторне створення файлу автономного куба(Нижче в цій статті).

БЛОГ

Тільки якісні пости

Що таке Зведені таблиці Excel та OLAP куби

Дивіться відео до статті:

OLAP- Це англ. online analytical processing, аналітична технологіяобробки даних у реальному часі. Простою мовою – сховище з багатовимірними даними (Куб), ще простіше – просто база даних, з якої можна отримати дані в Excel та проаналізувати за допомогою інструмента Excel – Зведені таблиці.

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

Щоб було зрозуміло, порівняємо «Звичайну таблицю» зі «Зведеною таблицею»

Звичайна таблиця:

Зведена таблиця:

Основна відмінність Зведених таблиць– це наявність вікна « Список полів зведеної таблиці», з якого можна вибирати потрібні поля та отримувати будь-яку таблицю автоматично!

Як користуватися

Відкрийте файл Excel, який підключений до OLAP-кубу, наприклад «BIWEB»:

Тепер що це означає і як цим користуватися?

Перетягніть поля, щоб отримати, наприклад, таку таблицю:

« Плюсики» Дозволяють деталізувати звіт. У цьому вся прикладі «Бренд» деталізується до «Скорочених назв», а «Квартал» до «Місяць», тобто. так:

Аналітичні функції у Excel (функції кубів)

Microsoft постійно додає в Excel нові можливості щодо аналізу та візуалізації даних. Роботу з інформацією Excel можна представити у вигляді відносно незалежних трьох шарів:

• «правильно» організовані вихідні дані
• математика (логіка) обробки даних
• подання даних

Рис. 1. Аналіз даних в Excel: а) вихідні дані; б) міра в Power Pivot; в) дашборд; щоб збільшити зображення клацніть на ньому правою кнопкою миші та виберіть Відкрити картинку в новій вкладці

Завантажити нотатку у форматі Word або pdf, приклади у форматі Excel

Функції кубів та зведені таблиці

Найбільш простим і водночас дуже потужним засобом представлення даних є зведені таблиці. Вони можуть бути побудовані на основі даних, що містяться: а) на листі Excel; б) кубі OLAP або в) моделі даних Power Pivot. В останніх двох випадках, крім зведеної таблиці, можна використовувати аналітичні функції(функції кубів) для формування звіту на аркуші Excel. Зведені таблиці простіші. Функції кубів складніші, але надають більше гнучкості, особливо в оформленні звітів, тому вони широко застосовуються у дашбордах.

Подальший виклад відноситься до формул кубів і зведених таблиць на основі моделі Power Pivot і в декількох випадках на основі кубів OLAP.

Простий спосіб отримати функції кубів

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

Рис. 2. Перетворення зведеної таблиці на набір функцій куба

Числа збережуться, причому це не значення, а формули, які витягують дані з моделі даних Power Pivot (рис. 3). Таблицю, що вийшла, ви може відформатувати. У тому числі, можна видаляти та вставляти рядки та стовпці усередину таблиці. Зріз залишився, і він впливає дані в таблиці. При оновленні вихідних даних числа таблиці також оновляться.

Рис. 3. Таблиця на основі формул кубів

Функція КУБЗНАЧЕННЯ()

Це, мабуть, основна функція кубів. Вона еквівалента області Значеннязведеної таблиці. КУБЗНАЧЕННЯ витягує дані з куба або моделі Power Pivot, і відображає їх поза зведеною таблицею. Це означає, що ви не обмежені межами зведеної таблиці і можете створювати звіти з численними можливостями.

Написання формули "з нуля"

Вам не обов'язково перетворювати готову зведену таблицю. Ви можете написати будь-яку формулу куба "з нуля". Наприклад, в комірку С10 введено таку формулу (рис. 4):

Рис. 4. Функція КУБЗНАЧЕННЯ в комірці С10 повертає продажі велосипедів за всі роки, як і в зведеній таблиці

Маленька хитрість. Щоб зручніше було читати формули кубів, бажано, щоб у кожному рядку містився лише один аргумент. Можна зменшити вікно Excel. Для цього клацніть на значку Згорнути у вікно, що знаходиться у правому верхньому куткуекран. Потім відрегулюйте розмір вікна по горизонталі. Альтернативний варіант – примусово переносити текст формули на новий рядок. Для цього в рядку формул поставте курсор там, де хочете зробити перенесення і натисніть Alt+Enter.

Рис. 5. Згорнути вікно

Синтаксис функції КУБЗНАЧЕННЯ()

Довідка Excel абсолютно точна і абсолютно марна для початківців:

КУБЗНАЧЕННЯ(підключення; [вираз_елемента1]; [вираз_елемента2]; …)

Підключення- Обов'язковий аргумент; текстовий рядок, який представляє ім'я підключення до куба.

Вираз_елемента- Необов'язковий аргумент; текстовий рядок, що представляє багатовимірний вираз, який повертає елемент чи кортеж у кубі. Крім того, «вираз_елемента» може бути безліччю, визначеною за допомогою функції КУБМНОЖ. Використовуйте "вираз_елемента" як зріз, щоб визначити частину куба, для якої необхідно повернути агреговане значення. Якщо в аргументі «вираз_елемента» не вказано міру, використовуватиметься міра, задана за умовчанням для цього куба.

Перш, ніж перейти до пояснення синтаксису функції КУБЗНАЧЕННЯ, пару слів про куби, моделі даних, та загадкове кортежі.

Деякі відомості про куби OLAP та моделі даних Power Pivot

Куби даних OLAP ( O n l ine A nalytical P rokssing - оперативний аналіз даних) були розроблені спеціально для аналітичної обробки та швидкого вилучення з них даних. Уявіть тривимірний простір, де по осях відкладено періоди часу, міста та товари (рис. 5а). У вузлах такої координатної сітки розташовані значення різних заходів: обсяг продажів, прибуток, витрати, кількість проданих одиниць та ін. Це буде багатомірний куб OLAP. Створення, налаштування та підтримка в актуальному стані кубів OLAP – справа ІТ-фахівців.

Рис. 5а. Тривимірний куб OLAP

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

Power Pivot – відносно нова фішка Microsoft. Це вбудоване в Excel і частково незалежне середовище зі звичним інтерфейсом. Power Pivot значно перевершує за своїми можливостями стандартні зведені таблиці. Разом з тим, розробка кубів у Power Pivot відносно проста, а найголовніше – не потребує участі ІТ-фахівця. Microsoft реалізує своє гасло: "Бізнес-аналітику - в маси!". Хоча моделі Power Pivot не є кубами на 100%, про них також можна говорити, як про куби (докладніше див. вступний курс Марк Мур. Power Pivot і більш об'ємне видання Роб Коллі. Формули DAX для Power Pivot).

Основні компоненти куба – це виміри, ієрархії, рівні, елементи (або члени; англійською members) та заходи (measures). Вимірювання –основна характеристика аналізованих даних. Наприклад, категорія товарів, період часу, географія продажів. Вимір - це те, що ми можемо помістити на одну з осей зведеної таблиці. Кожне вимір крім унікальних значень включає елемент , що виконує агрегацію всіх елементів цього виміру.

Вимірювання побудовані на основі ієрархії. Наприклад, категорія товарів може розбиватися на підкатегорії, далі – на моделі, і нарешті – на назви товарів (рис. 5б). Ієрархія дозволяє створювати зведені дані та аналізувати їх на різних рівнях структури. У прикладі ієрархія Категоріявключає 4 рівня.

Елементи(окремі члени) присутні всіх рівнях. Наприклад, на рівні Category є чотири елементи: Accessories, Bikes, Clothing, Components. Інші рівні мають елементи.

Заходи– це значення, що обчислюються, наприклад, обсяг продажів. Міри в кубах зберігаються у власному вимірі, що називається (див. нижче рис. 9). Заходи немає ієрархій. Кожна міра розраховує і зберігає значення для всіх вимірів і всіх елементів, і нарізається залежно від того, які елементи вимірів помістимо на осі. Ще кажуть, які задамо координати, або який задамо контекст фільтра. Наприклад, на рис. 5а у кожному маленькому кубику розраховується той самий захід – Прибуток. А значення, що повертається мірою, залежить від координат. Справа на малюнку 5а показано, що Прибуток (у трьох координатах) по Москві в жовтні на яблуках = 63 000 грн. Міру можна трактувати, і як один із вимірів. Наприклад, на рис. 5а замість осі Товари, розмістити вісь Заходиз елементами Об'єм продажу, Прибуток, Продані одиниці. Тоді кожен осередок і буде якимось значенням, наприклад, Москва, вересень, обсяг продажу.

Кортеж– кілька елементів різних вимірів, що задають координати по осях куба, у яких ми розраховуємо міру. Наприклад, на рис. 5а Кортеж= Москва, жовтень, яблука. Також допустимий кортеж – Перм, яблука. Ще один – яблука, серпень. Виміри, що не ввійшли в кортеж, присутні в ньому неявно, і представлені членом за умовчанням . Таким чином, осередок багатовимірного простору завжди визначається повним набором координат, навіть якщо деякі з них опущені в кортежі. Не можна включити два елементи одного виміру до кортежу, не дозволить синтаксис. Наприклад, неприпустимий кортеж Москва та Перм, яблука. Щоб реалізувати такий багатовимірний вираз, знадобиться набір двох кортежів: Москва і яблука + Перм і яблука.

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

Автозавершення на допомогу

Повернемося до синтаксису функції КУБЗНАЧЕННЯ. Скористаємося автозавершенням. Почніть введення формули в комірці:

Excel запропонує всі доступні в книзі Excel підключення:

Рис. 6. Підключення до моделі даних Power Pivot завжди називається ThisWorkbookDataModel

Рис. 7. Підключення до кубів

Продовжимо введення формули (у нашому випадку для моделі даних):

Автозавершення запропонує всі доступні таблиці та заходи моделі даних:

Рис. 8. Доступні елементи першого рівня – імена таблиць та набір заходів (виділено)

Виберіть піктограму Measures. Поставте крапку:

КУБЗНАЧЕННЯ(» ThisWorkbookDataModel » ; » .

Автозавершення запропонує всі доступні заходи:

Рис. 9. Доступні елементи другого рівня в наборі заходів

Виберіть міру. Додайте лапки, що закриває дужку, натисніть клавішу Enter.

КУБЗНАЧЕННЯ(» ThisWorkbookDataModel » ; » . »)

Рис. 10. Формула КУБЗНАЧЕННЯ в комірці Excel

Аналогічним чином можете додати третій аргумент у формулу:

VBA в Excel Об'єкт Excel.PivotTable та робота зі зведеними таблицями та кубами OLAP в Excel

10.8 Робота зі зведеними таблицями (об'єкт PivotTable)

Об'єкт Excel.PivotTable, програмна робота зі зведеними таблицями та кубами OLAP в Excel засобами VBA, об'єкт PivotCache, створення макета зведеної таблиці

У процесі роботи більшості підприємств накопичуються звані необроблені дані (raw data) про діяльність. Наприклад, для торгового підприємства можуть накопичуватися дані про продаж товарів - по кожній купівлі окремо, для підприємств стільникового зв'язку- статистика навантаження на базові станціїі т.п. Найчастіше менеджменту підприємства необхідна аналітична інформація, що генерується з урахуванням необробленої - наприклад, порахувати внесок кожного виду товару у доходи підприємства чи якість обслуговування у зоні даної станції. З необробленої інформації такі відомості отримати дуже важко: потрібно виконувати дуже складні SQL-запити, які виконуються довго і часто заважають поточній роботі. Тому все частіше нині необроблені дані зводяться спочатку у сховище архівних даних - Data Warehouse, а потім - у куби OLAP, які дуже зручні для інтерактивного аналізу. Найпростіше уявити куби OLAP як багатовимірні таблиці, у яких замість стандартних двох вимірів (стовпці і рядки, як і звичайних таблицях), вимірів може бути дуже багато. Зазвичай для опису вимірювань у кубі використовується термін «у розрізі». Наприклад, відділу маркетингу може бути потрібна інформація у тимчасовому розрізі, у регіональному розрізі, у розрізі типів продукту, у розрізі каналів продажів тощо. За допомогою кубів (на відміну від стандартних SQL-запитів) дуже просто отримувати відповіді на запитання на кшталт «скільки товарів такого типу було продано в четвертому кварталі минулого року в Північно-Західному регіоні через регіональних дистриб'юторів.

Звичайно, у звичайних базах даних такі куби не створити. Для роботи з кубами OLAP потрібні спеціалізовані програмні продукти. Разом із SQL Server поставляється база даних OLAP від ​​Microsoft, яка називається Analysis Services. Є OLAP-рішення від Oracle, IBM, Sybase і т.п.

Для роботи з такими кубами Excel вбудований спеціальний клієнт. Російською він називається Зведена таблиця(на графічний екранвін доступний через меню Дані -> Зведена таблиця), а англійською - Pivot Table. Відповідно, об'єкт, який представляє цей клієнт, називається PivotTable. Необхідно відзначити, що він вміє працювати не тільки з кубами OLAP, але і зі звичайними даними в таблицях Excel або баз даних, але багато можливостей при цьому втрачаються.

Зведена таблиця та об'єкт PivotTable - це програмні продукти компанії Panorama Software, які були придбані Microsoft і інтегровані в Excel. Тому робота з об'єктом PivotTable дещо відрізняється від роботи з іншими об'єктами Excel. Здогадатися, що треба зробити, часто буває непросто. Тому рекомендується для отримання підказок активно використовувати макрорекордер. У той самий час під час роботи зі зведеними таблицями користувачам часто доводиться виконувати одні й самі повторювані операції, тому автоматизація у багатьох ситуаціях необхідна.

Як виглядає програмна робота зі зведеною таблицею?

Перше, що нам потрібно зробити, - створити об'єкт PivotCache, який представлятиме набір записів, отриманих з джерела OLAP. Дуже умовно цей об'єкт PivotCache можна порівняти з QueryTable. Для кожного об'єкта PivotTable можна використовувати лише один об'єкт PivotCache. Створення об'єкта PivotCache здійснюється за допомогою методу Add() колекції PivotCaches:

Dim PC1 As PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches - стандартна колекція, і з методів, які заслуговують на докладний розгляд, в ній можна назвати тільки метод Add(). Цей метод приймає два параметри:

• SourceType- обов'язковий визначає тип джерела даних для зведеної таблиці. Можна вказати створення PivotTable на основі діапазону Excel, даних з бази даних, у зовнішньому джерелі даних, інший PivotTable і т.п. На практиці зазвичай OLAP є сенс використовувати лише тоді, коли даних багато – відповідно потрібно спеціалізоване зовнішнє сховище (наприклад, Microsoft Analysis Services). У цій ситуації вибирається значення xlExternal.
• SourceData- обов'язковий у всіх випадках, крім тих, коли значення першого параметра - xlExternal. Власне, визначає той діапазон даних, на основі якого і створюватиметься PivotTable. Зазвичай приймає об'єкт Range.

Наступне завдання – налаштувати параметри об'єкта PivotCache. Як мовилося раніше, цей об'єкт дуже нагадує QueryTable, і набір властивостей і методів він дуже схожий. Деякі найважливіші властивості та методи:

• ADOConnection- можливість повернути об'єкт ADO Connection, який автоматично створюється для підключення зовнішнього джерела даних. Використовується для додаткового налаштуваннявластивостей підключення
• Connection- працює так само, як і однойменна властивість об'єкта QueryTable. Може приймати рядок підключення, готовий об'єкт Recordset, текстовий файл, Web-запит. файл Microsoft Query. Найчастіше під час роботи з OLAP прописується рядок підключення безпосередньо (оскільки отримувати об'єкт Recordset, наприклад зміни даних, великого сенсу немає - джерела даних OLAP практично завжди доступні лише читання). Наприклад, налаштування цієї властивості для підключення до бази даних Foodmart (навчальна база даних Analysis Services) на сервері LONDON може виглядати так:

PC1.Connection = "OLEDB; Provider = MSOLAP.2; Data Source = LONDON1; Initial Catalog = FoodMart 2000"

• властивості CommandTypeі CommandTextтак само описують тип команди, яка передається на сервер баз даних, і текст самої команди. Наприклад, щоб звернутися на куб Sales і отримати його повністю в кеш на клієнті, можна використовувати код виду

• властивість LocalConnectionдозволяє підключитися до локального куба (файлу *.cub), створеного засобами Excel. Звичайно, такі файли для роботи з "виробничими" обсягами даних використовувати дуже не рекомендується - тільки для створення макетів і т.п.
• властивість MemoryUsedповертає кількість оперативної пам'яті, що використовується PivotCache. Якщо PivotTable на основі цього PivotCache ще не створена і не відкрита, повертає 0. Можна використовувати для перевірок, якщо ваша програма працюватиме на слабких клієнтах.
• властивість OLAPповертає True, якщо PivotCache підключено до сервера OLAP.
• OptimizeCache- Можливість оптимізувати структуру кешу. Початкове завантаження даних проводитиметься довше, але потім швидкість роботи може зрости. Для джерел OLE DB не працює.

Інші властивості об'єкта PivotCache збігаються з аналогічними властивостями об'єкта QueryTable і тому тут розглядатися не будуть.

Головний метод об'єкта PivotCache – це метод CreatePivotTable(). За допомогою цього і проводиться наступний етап - створення зведеної таблиці (об'єкта PivotTable). Цей метод приймає чотири параметри:

• TableDestination- Єдиний обов'язковий параметр. Приймає об'єкт Range, у верхній лівий кут якого буде розміщена зведена таблиця.
• TableName- Ім'я зведеної таблиці. Якщо не вказано, автоматично згенерується ім'я виду «Зведена Таблиця1».
• ReadData- якщо встановити в True, весь вміст куба буде автоматично поміщено в кеш. З цим параметром потрібно бути дуже обережним, оскільки неправильне застосування може різко збільшити навантаження на клієнта.
• DefaultVersion- ця властивість зазвичай не вказується. Дозволяє визначити версію створюваної зведеної таблиці. За замовчуванням використовується найсвіжіша версія.

Створення зведеної таблиці в першому осередку першого аркуша книги може виглядати так:

PC1.CreatePivotTable Range («A1»)

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

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

Покладатися на користувача, що він правильно розмістить елементи у всіх чотирьох областях, важко. Крім того, це може зайняти певний час. Тому часто потрібно розмістити дані у зведеній таблиці програмним чином. Ця операція здійснюється за допомогою об'єкта CubeField. Головна властивість цього об'єкта - Orientation, воно визначає, де буде те чи інше поле. Наприклад, поміщаємо вимір Customers в область стовпців:

PT1.CubeFields («»).Orientation = xlColumnField

Потім - вимір Time в область рядків:

PT1.CubeFields («»).Orientation = xlRowField

Потім - вимір Product в область сторінки:

PT1.CubeFields («»).Orientation = xlPageField

І нарешті, показник (числові дані для аналізу) Unit Sales:

PT1.CubeFields(«.»).Orientation = xlDataField

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

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

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

Надбудови до пакета програм Microsoft Officeдля вилучення та обробки даних є ряд функцій, що забезпечують доступ до можливостей вилучення та обробки даних із додатків Microsoft Office, і тим самим дозволяють здійснювати прогностичний аналіз на локальному комп'ютері. Завдяки тому, що вбудовані в служби платформи Microsoft SQL Server алгоритми вилучення та обробки даних доступні з середовища додатків Microsoft Office, бізнес-користувачі можуть легко отримувати цінну інформацію зі складних наборів даних лише кількома клацаннями миші. Надбудови до пакету програм Officeдля вилучення та обробки даних дають кінцевим користувачамможливість виконувати аналіз безпосередньо в програмах Microsoft Excel та Microsoft Visio.

До складу Microsoft Office 2007 входять три окремі OLAP-компоненти:

1. клієнт вилучення та обробки даних для Excel дозволяє створювати проекти вилучення та обробки даних на базі служб SSAS та керувати ними з Excel 2007;
2. засоби аналізу таблиць для програми Excel дозволяють використовувати вбудовані служби SSAS функції вилучення та обробки інформації для аналізу даних, що зберігаються в таблицях Excel;
3. шаблони отримання та обробки даних для програми Visio дозволяють візуалізувати дерева рішень, дерева регресії, кластерні діаграми та мережі залежностей на діаграмах Visio.

Дивовижне поруч...

По ходу роботи мені часто потрібно робити складні звіти, я весь час намагався знайти в них щось спільне, щоб складати їх більш просто і універсально, навіть написав і опублікував з цього приводу статтю «Дерево Осипова». Однак мою статтю розкритикували і сказали, що всі проблеми, які я порушив, давно вже вирішені в MOLAP.RU v.2.4 (www.molap.rgtu.ru) і порекомендували подивитися зведені таблиці в EXCEL.
Це виявилося настільки простим, що приклавши до цього свої геніальні рученята, у мене вийшло дуже проста схемадля вивантаження даних з 1С7 або будь-якої іншої бази даних (надалі під 1С мається на увазі будь-яка база даних) та аналізу в OLAP.
Я думаю, багато схем вивантаження в OLAP занадто ускладнені, я вибираю простоту.

Характеристики :

1. Для роботи потрібно лише EXCEL 2000.
2. Користувач може конструювати звіти без програмування.
3. Вивантаження із 1С7 у простому форматі текстового файлу.
4. Для бухгалтерських проводок вже є універсальна обробка для розвантаження, що працює у будь-якій конфігурації. Для вивантаження інших даних є обробки-зразки.
5. Можна заздалегідь сформулювати форми звітів, а потім застосовувати їх до різних даних без їх повторного конструювання.
6. Досить хороша продуктивність. На першому тривалому етапі дані спочатку імпортуються в EXCEL з текстового файлу і будується куб OLAP, потім досить швидко на основі цього куба може бути побудований будь-який звіт. Наприклад, дані про продаж товару по магазину за 3 місяці з асортиментом 6000 товарів, завантажуються в EXCEL 8 хвилин на Cel600-128M, рейтинг товарів і груп (OLAP-звіт) перераховується за 1 хвилину.
7. Дані вивантажуються з 1С7 повністю за вказаний період (всі рухи, за всіма складами, фірмами, рахунками). При імпорті в EXCEL можливе використання фільтрів, що завантажують для аналізу лише потрібні дані (наприклад, з усіх рухів, лише продажу).
8. В даний час розроблені способи аналізу рухів або залишків, але не рухів та залишків разом, хоча це в принципі можливо.

Що таке OLAP : (www.molap.rgtu.ru)

Припустимо, у вас є торгова мережа. Нехай дані про торгові операції вивантажені у текстовий файл або таблицю виду:

Дата - дата операції
Місяць – місяць операції
Тиждень – тиждень операції
Вид - закупівля, продаж, повернення, списання
Контрагент - зовнішня організація, яка бере участь у операції
Автор - людина, яка виписала накладну

У 1С, наприклад, один рядок цієї таблиці буде відповідати одному рядку накладної, деякі поля (Контрагент, Дата) при цьому беруться з накладної шапки.

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

Ця таблиця є вихідною для OLAP-аналізу.

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

Як використовувати у себе :

Дані з дистрибутива розпакувати саме до каталогу c:\fixin (для торгової системи можливо в c:\reports). Прочитайте readme.txt та виконайте всі інструкції в ньому.

Спочатку ви повинні написати обробку, яка вивантажує дані з 1С текстовий файл (таблицю). Вам потрібно визначити склад полів, які вивантажуватимуться.
Наприклад, вже готова універсальна обробка, яка працює у будь-якій конфігурації та вивантажує для OLAP-аналізу проводки за період, вивантажує для аналізу наступні поля:

Дата|ДеньТижня|Тиждень|Рік|Квартал|Місяць|Документ|Фірма|Дебет|ДтНоменклатура
|ДтГрупаНоменклатура|ДтРозділНоменклатура|Кредит|Сума|ВалСумма|Кількість
|Валюта|ДтКонтрагенти|ДтГруппаКонтрагенти|КтКонтрагенти|КтГруппаКонтрагенти|
КтРізніОб'єкти

Де під префіксами Дт(Кт) йдуть субконто Дебету (Кредиту), Група – це група даного субконто (якщо є), Розділ – група групи, Клас – група розділу.

Для торгової системи поля можуть бути такі:

Напрямок|ВідРуху|ЗаНал|Товар|Кількість|Ціна|Сума|Дата|Фірма
|Склад|Валюта|Документ|ДеньТижня|Тиждень|Рік|Квартал|Місяць|Автор
|КатегоріяТовара|КатегоріяРуху|КатегоріяКонтрагенту|ГрупаТовара
|ВалСумма|Собівартість|Контрагент

Для аналізу даних використовують таблиці "Аналіз рухів.xls" ("Аналіз бухгалтерії.xls"). Відкриваючи їх, не відключайте макроси, інакше ви не зможете оновлювати звіти (вони запускаються макросами на мовою VBA). Вихідні дані ці файли беруть із файлів C:fixinmotions.txt (C:fixinbuh.txt), в іншому вони однакові. Тому можливо, вам доведеться скопіювати ваші дані в один із цих файлів.
Щоб у EXCEL завантажилися дані, виберіть або напишіть свій фільтр і натисніть кнопку "Сформувати" на аркуші "Умови".
Аркуші звітів розпочинаються префіксом "Отч". Перейдіть на аркуш звіту, натисніть "Оновити" і дані звіту зміняться відповідно до останніх завантажених даних.
Якщо вас не влаштовують стандартні звіти, є лист ОтчШаблон. Скопіюйте його в новий аркуш і налаштуйте вигляд звіту, працюючи зі зведеною таблицею на цьому аркуші (про роботу зі зведеними таблицями - у будь-якій книзі EXCEL 2000). Рекомендую налаштовувати звіти на невеликому наборі даних, та був запускати їх у великому масиві, т.к. немає жодної можливості відключити перемалювання таблиць при кожній зміні макета звіту.

Технічні коментарі :

При вивантаженні даних із 1С користувач вибирає папку, куди йому вивантажувати файл. Я зробив це тому, що цілком ймовірно в найближчому майбутньому будуть вивантажуватися кілька файлів (залишки та рухи). Потім за натисканням у Провіднику кнопки "Надіслати" --> "На OLAP-аналіз в EXCEL 2000" дані копіюються з вибраної папки до папки C:\fixin. (щоб ця команда з'явилася у списку команди "Надіслати" і потрібно скопіювати файл "На OLAP-аналіз в EXCEL 2000.bat" у каталог C:\Windows\SendTo) Тому вивантажуйте дані відразу даючи імена файлам motions.txt або buh.txt.

Формат текстового файлу:
Перший рядок текстового файлу - заголовки колонок розділені "|", решта рядків містять значення цих колонок, розділені "|".

Для імпорту текстових файлів у Excel використовується Microsoft Query ( складова частина EXCEL) для його роботи необхідна наявність у каталозі імпорту (C:\fixin) файлу shema.ini, що містить таку інформацію:

ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI

Пояснення: motions.txt і buh.txt - це назва розділу, що відповідає імені імпортованого файлу, описує, як імпортувати текстовий файл в Ексель. Інші параметри означають, що перший рядок містить назви колонок, роздільником колонок є "|", набір символів - Windows ANSI (для ДОС - OEM).
Тип полів визначається автоматично виходячи з даних, що містяться в колонці (дата, число, рядок).
Перелік полів не потрібно ніде описувати - EXCEL та OLAP самі визначать, які поля містяться у файлі за заголовками у першому рядку.

Увага, перевірте ваші регіональні налаштування "Панель управління" --> "Регіональні налаштування" . У моїх обробках числа вивантажуються з розділником кома, а дати у форматі "ДД.ММ.РРРР".

Дані при натисканні кнопки "Сформувати" завантажуються в зведену таблицю на аркуші "База", а з цієї зведеної таблиці і беруть дані всі звіти на аркушах "Отч".

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

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

Опис роботи в EXCEL (для користувачів):

Інструкція з використання звітів:
1. Надішліть на аналіз вивантажені дані (уточніть у адміністратора). Для цього натисніть правою кнопкою на папці, в яку у вас вивантажилися дані з 1С і виберіть команду "Надіслати", потім "На OLAP-аналіз в EXCEL 2000".
2. Відкрийте файл "Аналіз рухів.xls"
3. Виберіть значення фільтра, потрібні вам фільтри можна дописати на закладці "Значення".
4. Натисніть кнопку "Сформувати", при цьому вивантажені дані будуть завантажені в EXCEL.
5. Після завантаження даних у EXCEL, можна дивитися різні звіти. Для цього достатньо натиснути кнопку "Оновити" у вибраному звіті. Аркуші зі звітами розпочинаються на Відч.
Увага! Після того як ви зміните значення фільтра, потрібно ще раз натиснути кнопку "Сформувати", щоб дані в EXCEL перезавантажилися з файлу вивантаження у відповідність до фільтрів.

Обробки з демо-прикладу:

Обробка motionsbuh2011.ert – остання версія вивантаження проводок із Бухгалтерії 7.7 для аналізу в Excel. У ній є галочка «Приєднати до файлу», яка дозволяє вивантажувати дані частинами за періодами, приєднуючи їх у той самий файл, а не вивантажуючи в один і той же файл заново:

Обробка motionswork.ert вивантажує дані про продаж для аналізу в Excel.

Приклади звітів:

Шахівка з проводок:

Завантаженість операторів за видами накладних:

P.S. :

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

Обробку motionsbuh2011.ert доопрацьовано, щоб справлятися з вивантаженням великого обсягу даних.

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