Головна Терміни Статті Курси Досвід компаній Блог Поради Завантажити Партнерам Контакти Акції

Статті > Автоматизація бюджетування та управлінського обліку >

Олександр Карпов, керівник проекту bud-tech.ru, автор серії книг «100% практичного бюджетування» та книги «Постановка та автоматизація управлінського обліку»

www.bud-tech.ru

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

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

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

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

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

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

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

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

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

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

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

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

.

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

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

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

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

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

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

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

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

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

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

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

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

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

Мал. 6. Вимірювання OLAP-КУБу програмного комплексу «ІНТЕГРАЛ»

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

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

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

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

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

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

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

Мал. 9. Приклад редагування довідника 1 Продукти та послуги у програмному комплексі «ІНТЕГРАЛ»

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

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

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

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

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

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

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

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

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

Загальні відомості

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

Отримання даних та оновити відмінності

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

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

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

Не-OLAP дані можуть бути повернуті до Microsoft Excel як діапазон зовнішніх даних або звіт зведеної таблиці або зведена діаграма. Дані OLAP можуть бути повернуті до Excel лише у вигляді звіту зведеної таблиці або зведеної діаграми.

Фоновий запит

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

Запити з параметрами

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

Оптимізація пам'яті

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

Параметри поля сторінки

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

Відмінності до розрахунку

Параметри поля сторінки

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

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

Обчислювані поля та обчислювані елементи

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

Неможливо змінити підсумкову функцію для проміжних підсумків у звіті зведеної таблиці.

OLAP-КУБ (динамічна управлінська звітність)

Не вдається відобразити проміжні результати для внутрішніх або внутрішніх полів стовпців у звіті зведеної таблиці.

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

Проміжні висновки

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

Макет та дизайн відмінності

Вимірювання та заходи

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

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

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

Вимірювання та заходи

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

Групування та розгрупування елементів

У Excel 2000 не можна групувати елементи у звіті зведеної таблиці, що базується на вихідних даних OLAP;

Перейменування полів

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

Угруповання та розгрупування елементів

Для OLAP вихідних даних елементи в новому звіті зведеної таблиці спочатку з'являються відсортовані в порядку зростання на ім'я елемента.

Детальні дані

Команда Показати сторінки не доступна у звітах зведених таблиць на основі вихідних даних OLAP.

Show Items With No Data

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

Нижче наведено список питань щодо предмету Інформаційні технології в менеджменті МФПУ/МФПА «Синергія»

… – інтерактивна автоматизована система, Що допомагає по…

OLAP у вузькому значенні слова трактується як …

OLAP-системи (online analytical processing) – це …

OLTP-системи виявилися мало придатними тому що …

Автоматизована система управління (автоматизована інформаційна система)

У програмі MS Project …

У системі OLTP оновлення даних відбувається…

Діаграма, призначена для аналізу плану робіт за допомогою методів…

Інформаційна система – це безліч взаємопов'язаних елементів.

Інформаційна технологія – це …

Інформаційне забезпечення – це …

Інформаційні технології на розвиток суспільства впливають наступним чином.

Інформаційний обмін у структурі органів управління організації про…

Виконавчі інформаційні системи (Executive Information Sys…

До ознак «малих» інформаційних систем належить …

До ознак інформаційних систем «середнього» масштабу належать …

Методи обробки інформації є …

Модульний принцип побудови бухгалтерських інформаційних систем.

На малюнку наведено фрагмент діаграми типу …, виконаної в про…

На мережевому графіку у програмі MS Project завданняіз зовнішнього ...

На мережевому графіці в програмі MS Project завдання, яке не належить …

На мережевому графіці в програмі MS Project завдання, що зав...

На мережевому графіку в програмі MS Project зведене завдання

На склад та кількість автоматизованих робочих місць, що входять до …

Наука про інформаційну діяльність, інформаційні процеси та…

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

Основне призначення системи OLAP полягає у …

Основним призначенням ERP-систем є …

Основним призначенням методології MPS є …

Основними характеристиками OLAP-систем є …

Підсистема технічного забезпечення включає …

Послідовність технологічних етапів з модифікації первинно…

При мережному об'єднанні персональних комп'ютеріву вигляді внутрішн...

Прикладне програмне забезпеченняЕОМ призначено для …

Прикладом предметної інформаційної технологіїє технологія…

Процес підтримки прийняття рішення має на увазі …

Мережа Масштабу Підприємства або Корпоративна Мережа – це інформаційна мережа.

Система штучного інтелектуявляє собою …

Системи обробки трансакцій – це системи, призначені для …

Системи обробки трансакцій відповідають …

Системи підтримки прийняття рішень (Decision Support Systems – DS...

Сучасні методи та засоби аналізу та планування процесів пр…

Створення інтегрованої автоматизованої інформаційної системи.

Створені інформаційні системи стають не придатними для використання.

Специфіка інформаційної системи підтримки керівництва виявляє…

Засобами традиційних OLTP-систем можна …

Структура корпоративних інформаційних систем є …

Прискорити та спростити роботу менеджерів з персоналу на фірмі позв..

Прискорити та спростити роботу менеджерів з персоналу на фірмі позв…

Фіксовані факти навколишнього світу, що сприймаються, являють зі…

Ланцюжок дій, що найбільш точно відображає процес управління пр...

Економічні завдання, які вирішуються в діалоговому режимі, характеризують…

Експертні системи призначені для обробки …

Є порушенням безпеки або відноситься до сфери безпеки.

OLAP - це просто

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

По ходу роботи мені часто потрібно робити складні звіти, я весь час намагався знайти в них щось спільне, щоб складати їх більш просто і універсально, навіть написав і опублікував з цього приводу статтю «Дерево Осипова». Однак мою статтю розкритикували і сказали, що всі проблеми, які я порушив, давно вже вирішені в OLAP (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), в іншому вони однакові.

Основи OLAP

Тому можливо, вам доведеться скопіювати ваші дані в один із цих файлів.
Щоб у EXCEL завантажилися дані, виберіть або напишіть свій фільтр і натисніть кнопку "Сформувати" на аркуші "Умови".
Аркуші звітів розпочинаються префіксом "Отч". Перейдіть на лист звіту, натисніть "Оновити" і дані звіту зміняться відповідно до останніх завантажених даних.
Якщо вас не влаштовують стандартні звіти, є лист ОтчШаблон. Скопіюйте його в новий аркуш і налаштуйте вигляд звіту, працюючи зі зведеною таблицею на цьому аркуші (про роботу зі зведеними таблицями в будь-якій книзі EXEL 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(On-line Analytical Processing) запропоновано в 1993 Е.Ф.Коддом (E.F.Codd) у статті, опублікованій за підтримки Arbor Software (тепер - Hyperion Software). Стаття включала 12 правил, які вже стали широко відомими і описані на сайті будь-якого постачальника OLAP додатків. Пізніше, в 1995 році, до них були додані ще шість менш відомих правил, усі вони були поділені на чотири групи та названі "характеристиками" (features). Ось ці правила, що дають визначення OLAP додатки з коментарями Найджела Пендса (Nigel Pendse), одного із творців сайту OLAP Report.

Основні характеристики OLAP включають:

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

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

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

4. Пакетне вилучення даних. Це правило вимагає, щоб продукти пропонували власні бази для зберігання аналізованих даних, так і динамічний (live) доступ до зовнішніх даних. Ми згодні з Коддом у цьому пункті і шкодуємо, що лише деякі OLAP продукти відповідають йому. Навіть ті програми, які пропонують такі функції, рідко роблять їх легкими та досить автоматизованими. В результаті Кодд підтримує багатовимірне подання даних плюс частковий попередній обрахунок великих багатовимірних баз даних з прозорим наскрізним доступом до детальної інформації. Сьогодні це розглядається як визначення гібридного OLAP, яка стає найбільш популярною архітектурою, тому Кодд дуже точно побачив основні тенденції в цій галузі.

5. Архітектура «клієнт-сервер». Кодд вважає, що не тільки кожен продукт повинен бути клієнт-серверним, але й кожна серверна компонента OLAP продуктів повинна бути достатньо інтелектуальною для того, щоб різні клієнти могли бути підключені з мінімальними зусиллями і програмуванням. Це набагато складніший тест, ніж проста клієнт-серверна архітектура і відносно мало продуктів проходить його. Ми могли б заперечити, що цей тест, можливо, складніше, ніж треба, і не варто диктувати розробникам архітектуру системи.

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

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

Спеціальні характеристики

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

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

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

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

Характеристики побудови звітів

12. Гнучка побудова звітів. Різні вимірювання повинні вибудовуватися будь-яким способом відповідно до потреб користувача. Більшість продуктів відповідає цій вимогі в рамках спеціальних редакторів звітів. Хотілося б, щоб такі можливості були доступні і в інтерактивних засобах перегляду, але це зустрічається значно рідше. Це одна з причин, через яку ми вважаємо за краще, щоб функціонал аналізу та побудови звітів був об'єднаний в одному модулі.

1. Поняття куба olap

13. Стабільна продуктивність при побудові звітів. Це означає, що продуктивність системи при побудові звітів має істотно падати зі збільшенням розмірності чи величини бази даних.

14. Автоматичне регулювання фізичного рівня. OLAP система повинна автоматично регулювати фізичну структуру для адаптації її до типу та структури моделі.

Управління розмірністю

15. Загальна функціональність. Усі виміри повинні мати однакові можливості у структурі та функціональності.

16. Необмежену кількість вимірювань та рівнів агрегування. Власне, під необмеженим числом Кодд передбачає 15-20, тобто. число, що явно перевищує максимальні потреби аналітика.

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

Подробиці про продукти Hyperion - на сайті www.hyperion.ru

Версія для друку

назад

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 і отримати його повністю в кеш на клієнті, можна використовувати код виду

PC1.CommandType = xlCmdCube

PC1.CommandText = Array(«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

Тепер зведена таблиця створена і з нею можна працювати. Однак часто необхідно виконати ще одну операцію – розкрити потрібний рівень ієрархії виміру. Наприклад, якщо нас цікавить поквартальний аналіз, потрібно розкрити рівень Quarter виміру Time (за замовчуванням показується лише найвищий рівень). Звичайно, користувач може це зробити самостійно, але не завжди можна розраховувати, що він здогадається, куди клацнути мишею. Програмним чином розкрити, наприклад, ієрархію вимірювання Time на рівень кварталів для 1997 можна за допомогою об'єктів PivotField і PivotItem:

PT1.PivotFields(«.»).PivotItems(«.»).DrilledDown = True

Механізм OLAP є на сьогодні одним із популярних методів аналізу даних. Є два основні підходи до вирішення цього завдання. Перший з них називається Multidimensional OLAP (MOLAP) – реалізація механізму за допомогою багатовимірної бази даних на стороні сервера, а другий Relational OLAP (ROLAP) – побудова кубів "на льоту" на основі SQL запитівдо реляційної СУБД. Кожен із цих підходів має свої плюси та мінуси. Їх порівняльний аналізвиходить за межі цієї статті. Ми опишемо нашу реалізацію ядра настільного ROLAP модуля.

Таке завдання виникло після застосування системи ROLAP, побудованої на основі компонентів Decision Cube, що входять до складу Borland Delphi. На жаль, використання цього набору компонентів показало низьку продуктивність великих обсягах даних. Гостроту цієї проблеми можна знизити, намагаючись відсікти якнайбільше даних перед подачею їх для побудови кубів. Але цього не завжди досить.

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

Схема роботи

Загальну схему роботи настільної системи OLAP можна представити наступним чином:

Алгоритм роботи наступний:

1. Отримання даних у вигляді плоскої таблиці або результату виконання запиту SQL.
2. Кешування даних та перетворення їх до багатовимірного куба.
3. Відображення збудованого куба за допомогою крос-таблиці або діаграми і т.п. У загальному випадку до одного куба може бути підключена довільна кількість відображень.

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

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

Крос-таблиця

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

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

Заповнюючи матрицю з фактами, ми маємо діяти так:

• На підставі даних про вимірювання визначити координати елемента, що додається в матриці.
• Визначити координати стовпців і рядків підсумків, на які впливає елемент, що додається.
• Додати елемент у матрицю та відповідні стовпці та рядки підсумків.

При цьому потрібно відзначити те, що отримана матриця буде сильно розрідженою, чому її організація у вигляді двовимірного масиву (варіант, що лежить на поверхні) не тільки нераціональна, але, швидше за все, і неможлива у зв'язку з великою розмірністю цієї матриці, для зберігання якої не не вистачить жодного обсягу оперативної пам'яті. Наприклад, якщо наш куб містить інформацію про продаж за один рік, і якщо в ньому буде всього 3 виміри – Клієнти (250), Продукти (500) та Дата (365), то ми отримаємо матрицю фактів наступних розмірів:

Кількість елементів = 250 х 500 х 365 = 45 625 000

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

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

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

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

Підготовка данних

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

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

Бібліотека компонентів CubeBase

Описані вище ідеї були покладені в основу створення бібліотеки компонентів CubeBase.

TСubeSourceздійснює кешування та перетворення даних у внутрішній формат, а також попереднє агрегування даних. Компонент TСubeEngineздійснює обчислення гіперкуба та операції з ним. Фактично він є OLAP-машиною, що здійснює перетворення плоскої таблиці в багатовимірний набір даних. Компонент TCubeGridвиконує виведення на екран крос-таблиці та керування відображенням гіперкуба. TСubeChartдозволяє побачити гіперкуб у вигляді графіків, а компонент TСubePivoteкерує роботою ядра куба.

Порівняння продуктивності

Даний набір компонент показав набагато вищу швидкодію, ніж Decision Cube. Так, на наборі з 45 тис. записів компоненти Decision Cube зажадали 8 хв. на побудову зведеної таблиці. CubeBase здійснив завантаження даних за 7сек. та побудова зведеної таблиці за 4 сек. Під час тестування на 700 тис. записів Decision Cube ми не дочекалися відгуку протягом 30 хвилин, після чого зняли завдання. CubeBase здійснив завантаження даних за 45 сек. та побудова куба за 15 сек.

На обсягах даних у тисячі записів CubeBase відпрацьовував у десятки разів швидше за Decision Cube. На таблицях у сотні тисяч записів – у сотні разів швидше. А висока продуктивність – один із найважливіших показників OLAP систем.

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

Концепція OLAP була описана в 1993 відомим дослідником баз даних і автором реляційної моделі даних Е. Ф. Коддом. В даний час підтримка OLAP реалізована в багатьох СУБД та інших інструментах.

Куб OLAP містить два типи даних:

· підсумкові значення, значення, для яких потрібно підбити підсумок, що представляють поля даних, що обчислюються;

· Описові відомості, що представляють вимірюванняабо розмірності. Описові відомості зазвичай розподіляються за рівнями деталізації. Наприклад: "Рік", "Квартал", "Місяць" і "День" у розмірності "Час". Розподіл полів за рівнями деталізації дозволяє користувачам, які працюють зі звітами, вибирати необхідний рівень деталізації для перегляду, починаючи з підсумкових даних високого рівня і потім переходячи до детальнішого подання, і навпаки.

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

Майстер створення куба OLAP є вбудований засіб Microsoft Query. Для створення куба OLAP на основі реляційної бази даних перед запуском майстра необхідно виконати такі дії.

1. Визначити джерело даних (див. рис. 6.1).

2. За допомогою Microsoft Query створити запит, включаючи в нього тільки ті поля, які будуть або полями даних, або полями розмірності куба OLAP, якщо поле в кубі використовується більше одного разу, його необхідно включити в запит потрібне число разів.

3. На останньому кроці майстра створення запитів встановити перемикач на пункті Створення куба OLAP з даного запиту (див. рис. 6.2) або після того, як запит створений засобами безпосередньо Query в меню Файлвибрати команду Створити куб OLAPПісля цього майстер створення куба OLAP буде запущений.

Робота майстра створення куба OLAP складається із трьох кроків.

На першому кроці майстра (див. рис. 6.6) визначаються поля даних–обчислювані поля, котрим необхідно визначити підсумкові значення.

Мал. 6.6. Визначення полів даних

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

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

Ім'я поля, що обчислюється, можна змінити в стовпці Ім'я поля даних.

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

Мал. 6.7. Визначення полів вимірів

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

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

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

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

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

Мал. 6.8. Вибір типу куба, що створюється на третьому кроці майстра

· Перші два варіанти передбачають створення куба при кожному відкритті звіту (якщо куб проглядається з Excel, то йдеться про зведену таблицю). У цьому випадку зберігається файл запиту та файл визначення куба *.oqy, що містить інструкції створення куба. Файл *.oqy може бути відкритий програмі Excelдля створення звітів на основі куба, а при необхідності внесення змін до куба, можна його відкрити Query для повторного запуску майстра створення куба.

За промовчанням файли визначення куба, як і файли запитів, зберігаються в папці профілю користувача в Application Data\Microsoft\Que-ries. При збереженні файлу *.oqy у стандартній папці ім'я файлу визначення куба виводиться на вкладці Куби OLAPпри відкритті нового запиту в Microsoft Query або при виборі команди Створити запит(меню Дані, підміню Імпорт зовнішніх даних) у Microsoft Excel.

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

Вибір типу куба визначається кількома чинниками: обсягом даних, що містить куб; типом та складністю звітів, які створюватимуться на основі куба; ресурсами системи (пам'ять та дисковий простір) тощо.

Окремий файл куба *.cub слід створювати у таких випадках:

1) для інтерактивних звітів, що часто змінюються, за наявності достатнього дискового простору;

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

Досить давно є мешканцем Хабра, але так і не доводилося читати статті на тему багатовимірних кубів, OLAP та MDX, хоча тема дуже цікава і з кожним днем ​​стає все більш актуальною.
Не секрет, що за той невеликий проміжок часу розвитку баз даних, електронного обліку та онлайн систем, самих даних накопичилося дуже багато. Тепер інтерес також представляє повноцінний аналіз архівів, а можливо і спроба прогнозування ситуацій для подібних моделей у майбутньому.
З іншого боку, великі компанії навіть за кілька років, місяців або навіть тижнів можуть накопичувати настільки великі масиви даних, що навіть їх елементарний аналіз потребує неординарних підходів та жорстких апаратних вимог. Такими можуть бути системи обробки банківських транзакцій, біржові агенти, телефонні операториі т.д.
Думаю, всім добре відомі два різні підходи побудови дизайну баз даних: OLTP і OLAP. Перший підхід (Online Transaction Processing - обробка транзакцій у реальному часі) розрахований на ефективний збір даних у реальному часі, другий (Online Analytical Processing – аналітична обробка в реальному часі) націлений саме на вибірку та обробку даних максимально ефективним способом.

Давайте розглянемо основні можливості сучасних OLAP кубів, і які завдання вони вирішують (за основу взято Analysis Services 2005/2008):

• швидкий доступдо даних
• преагрегація
• ієрархії
• робота з часом
• мова доступу до багатовимірних даних
• KPI (Key Performance Indicators)
• дата майнінг
• багаторівневе кешування
• підтримка мультимовності

Отже, розглянемо можливості OLAP кубів трохи докладніше.

Трохи докладніше про можливості

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

Преагрегація
Крім швидкої вибірки існуючих даних, також надається можливість переагрегувати «найбільш ймовірно використовуються» значення. Наприклад, якщо ми маємо щоденні записи про продаж якогось товару, система можепереагрегувати нам також місячні та квартальні суми продажів, а отже, якщо ми запитаємо дані помісячно чи поквартально, система миттєво дасть результат. Чому ж преагрегація відбувається не завжди – тому, що теоретично можливі комбінації товарів/часу/і т.д. можливо величезна кількість, отже, потрібно мати чіткі правила яких елементів агрегація буде побудована, а яких немає. Взагалі тема обліку цих правил і власне безпосереднього дизайну агрегацій досить велика і сама по собі заслуговує на окрему статтю.

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

Робота з часом
Так як в основному аналіз даних відбувається на тимчасових ділянках, саме часу в OLAP системах виділено особливе значення, а значить просто визначивши для системи, де у нас тут час, надалі можна з легкістю користуватися функціями типу Year To Date, Month To Date ( період від початку року/місяця і до поточної дати), Parallel Period (цього ж дня чи місяця, але минулого року) тощо.

Мова доступу до багатовимірних даних
MDX(Multidimensional Expressions) - мова запитів для простого та ефективного доступу до багатовимірних структур даних. І цим усе сказано – унизу буде кілька прикладів.

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

Дата майнінг
Інтелектуальний аналіз даних(Data Mining) - насправді, виявлення прихованих закономірностей чи взаємозв'язків між змінними у великих масивах даних.
Англійський термін «Data Mining» не має однозначного перекладу російською мовою (видобуток даних, розтин даних, інформаційна проходка, вилучення даних/інформації) тому в більшості випадків використовується в оригіналі. Найбільш вдалим непрямим перекладом вважається термін інтелектуальний аналіз даних (ІАД). Втім, це окрема, не менш цікава темадля розгляду.

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

Підтримка мультимовності
Так Так Так. Як мінімум, Analysis Services 2005/2008 (правда, Enterprise Edition) нативно підтримують мультимовність. Достатньо навести переклад рядкових параметрів ваших даних, і клієнту, який вказав свою мову, будуть надходити локалізовані дані.

Багатовимірні куби

То що ж ці багатовимірні куби?
Уявімо 3-х мірний простір, у якого по осях Час, Товари та Покупці.
Крапка в такому просторі задаватиме факт того, що хтось із покупців у якомусь місяці купив якийсь конкретний товар.

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

Трохи MDX

Отже, в чому ж принадність MDX - швидше за все в тому, що описувати потрібно не те, як ми хочемо вибрати дані, а що самеми хочемо.
Наприклад,

SELECT
( . ) ON COLUMNS,
( ., . ) ON ROWS
FROM
WHERE (., .)

Що означає – хочу кількість iPhone-ів, проданих у червні та липні в Мозамбіку.
При цьому я описую якісаме дані я хочу і яксаме я хочу їх побачити у звіті.
Красиво, чи не так?

А ось трохи складніше:

WITH MEMBER AverageSpend AS
. / .
SELECT
(AverageSpend) ON COLUMNS,
( .., .. ) ON ROWS
FROM
WHERE (.)

* Цей source code був highlighted with Source Code Highlighter.

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

Сама мова надзвичайно цікава і для вивчення і для використання, і, мабуть, заслуговує на чимало обговорень.

Висновок

Насправді, ця стаття дуже мало покриває навіть базових понятья б назвав її «appetizer» - можливість зацікавити хабра-спільноту даною тематикою та розвивати її далі. Що ж до розвитку – тут величезне неоране поле, а я буду радий відповісти на всі питання.

P.S.Це мій перший пост про OLAP та першу публікацію на Хабрі – буду дуже вдячний за конструктивний фідбек.
Update:Переніс у SQL, перенесу в OLAP, як тільки дозволять створювати нові блоги.

Теги: Додати теги