Помилка під час створення файлу куба з екселю. Створення куба ОLAP засобами Microsoft Query

Мал. 2.Зріз OLAP-куба

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

..&& +

.. && +

.. && + …

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Тут зупинимося докладніше на конкретних методах обробки даних з інтерфейсу зведених таблиць. Як приклад використовуйте файл nwdata_pivot.xls.

Використання зведених таблиць

Вибір унікальних значень

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

У прикладі на аркуші Вибіркапоказаний список країн та кількість згадок у масиві даних.

У полі даних необхідно, щоб стояв вид операції – кількість. Цей параметр дозволяє обробляти в області даних зведеної таблиці нечислові поля вихідних даних. Альтернативою операції підрахунку кількості є стандартна функція COUNTIF. Сформувати набір унікальних значеннятільки за допомогою формул у принципі теж можливо (див. частина 1), але це вимагатиме дуже складних формул з обчислюваною адресацією. Тобто, використання зведеної таблиці у цій задачі – це найоптимальніший спосіб розв'язання.

Підсумовування значень

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

У прикладі на аркуші Сумасформовані підсумкові дані на замовлення по кожній країні:

Вигляд операції «Сума» у полі даних допускає використання лише числових полів. Інші види агрегації вихідних даних практично майже не використовуються.

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

Двовимірний аналіз

Описані приклади демонструють аналіз даних за одним критерієм. Електронні таблиці дозволяють наочно подати дані у двох вимірах: по стовпчиках і рядкам. Зведені таблиці також мають ці області відображення даних.

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

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

Багатовимірний аналіз

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

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

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

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

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

Робота з даними

Оновлення даних

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

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

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

Змінити діапазон джерел даних для існуючої зведеної таблиці можна через спеціальний діалог Excel 2007-2010. У попередніх версіях Excel ця інтерфейсна можливість реалізована в «Майстері роботи зі зведеними таблицями», якщо він запущений з активної зведеної таблиці. Після відкриття майстра необхідно повернутися на один крок назад:

Виправлення джерела даних можна також зробити програмним способом. Наприклад, через вікно обчислень редактора VBA (Immediate):

Щоб не замислюватися над коректністю розмірів діапазону-джерела даних зведеної таблиці, можна спочатку при побудові встановити діапазон рядків з великим запасом. Наприклад, знаючи, що передбачуваний обсяг рядків не перевищує 10000, можна відразу задати це значення як розмір діапазону. Така надмірність практично не призведе до видимим уповільненням у роботі інтерфейсу зведеної таблиці. Порожні значення у вимірах звіту можна приховати. Недолік цього виявляється, насамперед, під час роботи з полями типу «дата». Стандартний інтерфейс зведеної таблиці дозволяє реалізувати різні угруповання під час роботи з типом «дата» (по місяцях, по кварталах), але за наявності порожніх значеньці можливості стають недоступними, так як Excel визначає такий стовпець як текстовий.

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

ActiveSheet.PivotTables(1).SourceData = _ Left(ActiveSheet.PivotTables(1).SourceData, _ InStr(ActiveSheet.PivotTables(1).SourceData, "!")) & _ Range(Application.ConvertFormula(_ ActiveSheet.PivotTables (1).SourceData, xlR1C1, xlA1) _).Worksheet.UsedRange.Address(ReferenceStyle:=xlR1C1)

Найнадійнішим, але повільним способом, є послідовна перевірка рядків листа-джерела з наступним заповненням якості SourceDataактивної зведеної таблиці. Зверніть увагу, що ця властивість зберігається лише у R1C1-адресації.

Макрос можна викликати за подією Worksheet_Activateабо налаштувати «гарячу» клавішу.

Робота з результатами аналізу

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

Є альтернативні способиобробки результатів зведеної таблиці:

1. Копіювання та вставлення значень зведеної таблиці на інший аркуш (з використанням функції « Спеціальна вставка») з подальшим пошуком динних вже за цим сформованим діапазоном осередків. Порушити цілісність даних у межах простої таблиці набагато складніше, ніж у зведеній. Очевидно, що головним недоліком цього способу роботи є використання ручних операцій після кожного оновлення джерела даних.
2. Використовувати можливості функції GETPIVOTDATA (Excel 2002 та пізніші версії). Ця функціяпередбачає доступ до даних за координатами робочого листа, а, по вимірам зведеної таблиці. Для джерел даних типу OLAP-куб передбачені спеціальні функції доступу до даних та вимірювань: CUBEVALUE, CUBEMEMBER та інші (Excel 2007-2010). Цей спосіброботи незручний, а також суттєво уповільнює роботу, якщо потрібно отримати багато різних значень зведеної таблиці.
3. Відмовитись від зведеної таблиці для отримання результатів. Натомість використовувати формули робочого листа (див. Частину 1). Цей спосіб, незважаючи на складність реалізації, може виявитися найзручнішим у тому випадку, якщо на результатах ґрунтуються інші обчислення, а джерело даних часто оновлюється.

Версії інтерфейсу зведених таблиць

У новому форматі файлу xlsx (Excel 2007-2010) суттєво змінено можливості інтерфейсу зведених таблиць. У попередні версіїінтерфейсу (97-2003) вносилися лише «косметичні» зміни:

• Excel 2000 (9.0) - базова версіяінтерфейсу зведених таблиць.
• Excel XP (10.0) – нова функція GETPIVOTDATE
• Excel 2003 (11.0) – схоже, що взагалі жодних змін не вносилося
• Excel 2007 (12.0) - Нова версіяінтерфейсу зведених таблиць із підтримкою розширених діапазонів. Поліпшено продуктивність, змінено зовнішній виглядінтерфейсу. Зберігається сумісність із старим форматом.
• Excel 2010 (14.0) – підтримка надбудови PowerPivot. Робота з OLAP-кубами, що оновлюються.

Основні зміни у новому форматі файлу (2007-2010):

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

Для кращого розуміння відмінностей скачайте та відкрийте файли-приклади nwdata_pivot1.xlsxі nwdata_pivot2.xlsx(в арихіві nwdata_pivot.zip). У першому файлі представлений звіт у старому форматі, у другому – у новому, вихідні дані однакові.

Внутрішня організація інтерфейсу зведених таблиць

Для найкращого розуміння принципів роботи зведеної таблиці розглянемо внутрішню організацію інтерфейсу.

Кеш зведеної таблиці

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

Дані в кеші оновлюються при натисканні кнопки «Оновити» інтерфейсу зведеної таблиці (кнопка на стрічці або контекстному меню), або за заданим інтервалом часу, якщо така установка задана в параметрах. Режим обчислень Excel(Автоматичний або ручний) при цьому ніяк не впливає на зведену таблицю.

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

Об'єкти VBA

Доступ до даних програмними методами можливий лише на рівні об'єктів зведеної таблиці - об'єкт PivotTable. Інші об'єкти зведеної таблиці відповідають за розташування та візуальне відображення елементів та даних. До них відносяться колекції полів: PivotFields, ColumnFields, RowFields, PageFields, DataFields. Варіанти значень полів доступні через колекції об'єктів PivotItems.

Універсальна можливість звернення до даних безпосередньо в кеш (об'єкт PivotCache) чомусь не передбачена розробниками Excel. Логіка у своїй не зовсім зрозуміла. Як уже зазначалося, дані кешу зберігаються окремо і їх навіть можна побачити у форматі xlsx, якщо відкрити цей файл як zip-архів. Залежно від типу джерела даних можна спробувати використати властивість SourceData(для зведених таблиць на основі діапазону) або Recordset(Для джерел типу «запит до бази даних»).

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

Види джерел даних

Глобально можна розділити джерела даних на 3 типи:

1. Діапазони осередків
2. Запити до бази даних
3. OLAP-куби та PowerPivot2010 як один із варіантів реалізації OLAP-механізму.

Діапазони

Перший варіант роботи – найпоширеніший практично; попередні описи прикладів відносяться якраз до даних, що зберігаються в діапазоні осередків.

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

Запити до бази даних

Запити до бази даних можуть бути реалізовані за допомогою різних технічних механізмів: Microsoft Query, ADO, ODBC. Незалежно від інтерфейсу доступу до даних фактором, що об'єднує, цього варіанту роботи є заповнення кеша зведеної таблиці безпосередньо із зовнішнього джерела. При подальшій роботі зі зведеною таблицею запит може бути повторно виконаний, після чого дані будуть заново перенесені в кеш. Цей метод дозволяє аналізувати дані із зовнішніх джерел (облікових систем) у реальному часі. При розриві зв'язку з джерелом даних аналіз може проводитися на останніх даних, що потрапили в кеш.

OLAP-куби

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

До версії Office 2007 простий OLAP-куб можна було підготувати при допомоги Microsoft Query, але в останніх версіяхцю можливість із незрозумілих причин відключили. Розробники рекомендують використовувати SQL Server Analysis Service для створення та налаштування OLAP-кубів. Рекомендація корисна, але, по-перше, цей сервіс входить до складу лише платних версій SQL Server, а по-друге, вимагає серйозного вивчення як інтерфейсу, так і мови обробки MDX-запитів.

У прикладі до статті наведено архів nwdata_cube.zipз двома файлами nwdata_cube.cub, nwdata_cube.xls. Зверніть увагу на зміни в інтерфейсі зведеної таблиці під час використання OLAP-куба як джерела даних:

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

PowerPivot

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

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

Офіційний сайт PowerPivot

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

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

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

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

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

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

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

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

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

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

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

.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примітка:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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