У циклі статей «Введення до баз даних», що публікувався останнім часом (див. Комп'ютерПрес №3'2000 - 3'2001), ми обговорювали різні технології та програмні засоби, що застосовуються при створенні інформаційних систем - настільні та серверні СУБД, засоби проектування даних , засоби розробки додатків, а також Business Intelligence – засоби аналізу та обробки даних масштабу підприємства, які в даний час стають все більш популярними у світі, у тому числі й у нашій країні. Зазначимо, що питання застосування засобів Business Intelligence та технології, що використовуються при створенні додатків такого класу, у вітчизняній літературі поки що висвітлені недостатньо. У новому циклі статей ми спробуємо заповнити цю прогалину і розповісти про те, що є технологіями, що лежать в основі подібних додатків. Як приклади реалізації ми будемо використовувати в основному OLAP-технології фірми Microsoft (головним чином Analysis Services у Microsoft SQL Server 2000), але сподіваємося, що основна частина матеріалу буде корисною і для користувачів інших засобів.

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

Що таке сховище даних

p align="justify"> Інформаційні системи масштабу підприємства, як правило, містять додатки, призначені для комплексного багатовимірного аналізу даних, їх динаміки, тенденцій і т.п. Такий аналіз зрештою покликаний сприяти прийняттю рішень. Нерідко ці системи і називаються - системи підтримки прийняття рішень.

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

Ральф Кімбол (Ralph Kimball), один з авторів концепції сховищ даних, описував сховище даних як «місце, де люди можуть отримати доступ до своїх даних» (див., наприклад, Ralph Kimball, The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses», John Wiley & Sons, 1996 і «The Data Webhouse Toolkit: Building the Web-Enabled Data Warehouse», John Wiley & Sons, 2000). Він також сформулював і основні вимоги до сховищ даних:

• підтримка високої швидкості отримання даних із сховища;
• підтримка внутрішньої несуперечності даних;
• можливість отримання та порівняння про зрізів даних (slice and dice);
• наявність зручних утиліт перегляду даних у сховищі;
• повнота і достовірність даних, що зберігаються;
• - підтримка якісного процесу поповнення даних.

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

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

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

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

Що таке OLAP

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

Технологія комплексного багатовимірного аналізу даних одержала назву OLAP (On-Line Analytical Processing). OLAP – це ключовий компонент організації сховищ даних. Концепція OLAP була описана в 1993 році Едгаром Коддом, відомим дослідником баз даних і автором реляційної моделі даних (див. E.F. Codd, S.B. Codd, і C.T. Technical report, 1993). У 1995 році на основі вимог, викладених Коддом, був сформульований так званий тест FASMI (Fast Analysis of Shared Multidimensional Information - швидкий аналіз багатовимірної інформації, що розділяється), що включає наступні вимоги до додатків для багатовимірного аналізу:

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

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

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

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

Для розгляду концепції OLAP скористаємося поданням Invoices та таблицями Products та Categories з бази даних Northwind, створивши запит, в результаті якого отримаємо докладні відомості про всі замовлені товари та виписані рахунки:

SELECT dbo.Invoices.Country, dbo.Invoices.City, dbo.Invoices.CustomerName, dbo.Invoices.Salesperson, dbo.Invoices.OrderDate, dbo.Categories.CategoryName, dbo.Invoices.ProductName, dbo.Invoices.ShipperName, dbo. .Invoices.ExtendedPrice FROM dbo.Products INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER JOIN dbo.Invoices ON dbo.Products.ProductID = dbo.Invoices.ProductID

У Access 2000 аналогічний запит має вигляд:

SELECT Invoices.Country, Invoices.City, Invoices.Customers.CompanyName AS CustomerName, Invoices.Salesperson, Invoices.OrderDate, Categories.CategoryName, Invoices.ProductName, Invoices.Shippers.CompanyName AS ShipperName, Invoices INNER JOIN Products ON Invoices.ProductID = Products.ProductID) ON Categories.CategoryID = Products.CategoryID;

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

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

Які агрегатні дані ми можемо одержати на основі цього подання? Зазвичай це відповіді питання типу:

• Якою є сумарна вартість замовлень, зроблених клієнтами з Франції?
• Якою є сумарна вартість замовлень, зроблених клієнтами з Франції та доставлених компанією Speedy Express?
• Яка сумарна вартість замовлень, зроблених клієнтами з Франції у 1997 році та доставлених компанією Speedy Express?

Перекладемо ці запитання запити мовою SQL (табл. 1).

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

Country	SUM (ExtendedPrice)
Argentina	7327.3
Austria	110788.4
Belgium	28491.65
Brazil	97407.74
Canada	46190.1
Denmark	28392.32
Finland	15296.35
Франція	69185.48
Німеччина	209373.6
…	…

Отриманий набір агрегатних значень (у разі - сум) може бути інтерпретований як одномірний набір даних. Цей же набір даних можна отримати в результаті запиту з пропозицією GROUP BY наступного виду:

SELECT Country, SUM (ExtendedPrice) FROM invoices1 GROUP BY Country

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

	ShipperName
Country	Federal Shipping	Speedy Express	United Package
Argentina	1 210.30	1 816.20	5 092.60
Austria	40 870.77	41 004.13	46 128.93
Belgium	11 393.30	4 717.56	17 713.99
Brazil	16 514.56	35 398.14	55 013.08
Canada	19 598.78	5 440.42	25 157.08
Denmark	18 295.30	6 573.97	7 791.74
Finland	4 889.84	5 966.21	7 954.00
Франція	28 737.23	21 140.18	31 480.90
Німеччина	53 474.88	94 847.12	81 962.58
…	…	…	…

Такий набір даних називається зведеною таблицею (pivot table) чи крос-таблицею (cross table, crosstab). Створювати подібні таблиці дозволяють багато електронних таблиць і настільних СУБД - від Paradox для DOS до Microsoft Excel 2000. Ось так, наприклад, виглядає подібний запит у Microsoft Access 2000:

TRANSFORM Sum(Invoices1.ExtendedPrice) AS SumOfExtendedPrice SELECT Invoices1.Country FROM Invoices1 GROUP BY Invoices1.Country PIVOT Invoices1.ShipperName;

Агрегатні дані для подібної зведеної таблиці можна отримати за допомогою звичайного запиту GROUP BY:

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

Country	ShipperName	SUM (ExtendedPrice)
Argentina	Federal Shipping	845.5
Austria	Federal Shipping	35696.78
Belgium	Federal Shipping	8747.3
Brazil	Federal Shipping	13998.26
…	…	…

Третій із розглянутих вище запитів має вже три параметри за умови WHERE. Варіюючи їх, ми отримаємо тривимірний набір даних (рис. 2).

Осередки куба, показаного на рис. 2 містять агрегатні дані, відповідні знаходяться на осях куба значенням параметрів запиту в пропозиції WHERE.

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

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

Якщо WHERE містить чотири або більше параметрів, результуючий набір значень (також званий OLAP-кубом) може бути 4-мірним, 5-мірним і т.д.

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

Деякі терміни та поняття

Поряд із сумами в осередках OLAP-куба можуть міститися результати виконання інших агрегатних функцій мови SQL, таких як MIN, MAX, AVG, COUNT, а в деяких випадках – та інших (дисперсії, середньоквадратичного відхилення тощо). Для опису значень даних в осередках використовується термін summary (загалом у одному кубі їх може бути кілька), для позначення вихідних даних, на основі яких вони обчислюються, - термін measure, а для позначення параметрів запитів - термін dimension (перекладається російською мовою) зазвичай як «вимірювання», коли йдеться про OLAP-куби, і як «розмірність», коли йдеться про сховища даних). Значення, що відкладаються на осях, називаються членами вимірів (members).

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

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

Зазначимо, що ієрархії може бути збалансованими (balanced), як, наприклад, ієрархія, представлена ​​на рис. 3 , і навіть ієрархії, засновані на даних типу «дата-час», і незбалансованими (unbalanced). Типовий приклад незбалансованої ієрархії - ієрархія типу "начальник-підлеглий" (її можна побудувати, наприклад, використовуючи значення поля Salesperson вихідного набору даних із розглянутого вище прикладу), представлений на рис. 4 .

Іноді таких ієрархій використовується термін Parent-child hierarchy.

Існують також ієрархії, що займають проміжне положення між збалансованими та незбалансованими (вони позначаються терміном ragged – «нерівний»). Зазвичай вони містять такі члени, логічні «батьки» яких знаходяться не на безпосередньо вищому рівні (наприклад, у географічній ієрархії є рівні Country, City та State, але при цьому в наборі даних є країни, які не мають штатів чи регіонів між рівнями Country та City ;рис.5).

Зазначимо, що незбалансовані та «нерівні» ієрархії підтримуються далеко не всіма OLAP-засобами. Наприклад, у Microsoft Analysis Services 2000 підтримуються обидва типи ієрархії, а Microsoft OLAP Services 7.0 - лише збалансовані. Різним у різних OLAP-засобах може бути і кількість рівнів ієрархії, і максимально допустима кількість членів одного рівня, і максимально можлива кількість самих вимірювань.

Висновок

У цій статті ми ознайомились із основами OLAP. Ми дізналися наступне:

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

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

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

Комп'ютерПрес 4"2001

Куби даних 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.

/ У кубістичній манері. Застосування OLAP-кубів у практиці управління великих компаній

Вконтакте

Однокласники

Костянтин Токмачов, системний архітектор

У кубістичній манері.
Застосування OLAP-кубів у практиці управління великих компаній

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

Про користь бізнес-аналітики

У контурі управління корпорацією між «сирими» даними та «важелями» на керований об'єкт розташовуються «показники роботи» – KPI. Вони утворюють хіба що «приладове табло», що відбиває стан різних підсистем керованого об'єкта. Оснастити фірму інформативними показниками роботи та контролювати їх розрахунок та отримані значення – праця бізнес-аналітика. Істотну допомогу в організації аналітичної роботи корпорації здатні надати автоматизовані служби аналізу, такі як утиліта MS SQL Server Analysis Services (SSAS) та її головний диспозитив – OLAP-куб.

Прямо тут слід зробити ще одне зауваження. Скажімо, в американській традиції спеціальність, орієнтована працювати з OLAP-кубами, називається BI (Business Intelligence). Не повинно бути жодних ілюзій, ніби американське BI відповідає російській «бізнес-аналітик». Без образ, але нерідко наш бізнес-аналітик – це «недобухгалтер» і «недопрограміст», фахівець з нечіткими знаннями та з невеликим окладом, який реально не має жодного власного інструментарію та методології.

Фахівець BI – це, по суті, прикладний математик, висококласний фахівець, який ставить на озброєння фірми сучасні математичні методи (те, що називалося Operations Researh – методи дослідження операцій). BI більше відповідає колишньому колись СРСР спеціальності «системний аналітик», що випускалася факультетом ВМК МДУ ім. М.В. Ломоносова. OLAP-куб та служби аналізу можуть стати перспективною основою робочого місця російського бізнес-аналітика, можливо після деякого підвищення його кваліфікації у бік американського BI.

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

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

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

Нарешті, програміст, який колись (завдяки освіті) провідником передових технічних ідей зі сфери науки у сфері бізнесу, перетворився на пасивного виконавця фантазій бухгалтера та менеджера, тож вже не рідкість, коли ІТ-відділами корпорацій підрулюють бухгалтери і взагалі всі, кому не ліньки. Безініціативний, малограмотний, але щодо високооплачуваний програміст 1С – справжній бич російських корпорацій. (Майже як вітчизняний футболіст.) Про так звані «економісти та юристи» я вже не кажу, про них давно все сказано.

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

Переваги OLAP-кубів

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

Деяке уявлення про OLAP-
кубі може дати "зведена таблиця" MS Excel. У цих об'єктів схожа логіка та схожі інтерфейси. Але, як видно з статті, функціональність OLAP незрівнянно багатша, а продуктивність незрівнянно вища, отже «зведена таблиця» залишається локальним настільним продуктом, тоді як OLAP – продукт корпоративного рівня.

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

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

Формулювання аналітики можливе у трьох варіантах: це або поле бази даних (вірніше, поле warehouse), або розрахункове поле calculation, що визначається на рівні дизайну куба, або вираз мови MDX при інтерактивній роботі з кубом.

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

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

Наведемо приклад. Припустимо, керівник контролює дебіторську заборгованість. Поки KPI простроченої дебіторської заборгованості «горить зеленим світлом», отже, все гаразд, жодних управлінських дій не потрібно. Якщо колір змінився на жовтий або червоний - щось не так: розрізаємо KPI по відділах продажів і одразу бачимо підрозділи "у червоному". Наступний розріз менеджерів – і продавець, чиї клієнти прострочили платежі, визначено. (Далі суму прострочення можна розрізати за покупцями, за термінами тощо). Керівник корпорації може прямо звернутися до порушників на будь-якому рівні. Але взагалі той же KPI (на своїх рівнях ієрархії) бачать і начальники відділів, і менеджери з продажу. Тому, щоб виправити ситуацію, їм навіть не потрібно чекати «виклику на килим»… Зрозуміло, сам KPI за змістом не обов'язково має бути сумою прострочення – він може бути середньозваженим терміном прострочення чи взагалі швидкістю обороту дебіторської заборгованості.

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

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

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

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

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

OLTP + OLAP: контур зворотного зв'язку в ланцюзі управління фірмою

Тепер розглянемо загальну ідею OLAP-кубів та їх точку застосування в управлінському ланцюзі корпорації. Термін OLAP (OnLine Analytical Processing) був запроваджений британським математиком Едгаром Коддом на додаток до раніше введеного терміну OLTP (OnLine Transactions Processing). Про це буде сказано, але Є. Кодд, зрозуміло, запропонував як терміни, а й математичні теорії OLTP і OLAP. Не вдаючись до деталей, у сучасній інтерпретації OLTP – це реляційна база даних, розглянута як механізм реєстрації, зберігання та вибірки інформації.

Методологія вирішення

Такі ERP-системи (Enterprice Resource Planning), як 1С7, 1С8, MS Dynamics AX, мають програмні інтерфейси, орієнтовані на користувача (введення та коригування документів тощо), та реляційну базу даних (DB) для зберігання та вибірки інформації , представлена ​​сьогодні програмними продуктами типу MS SQL Server (SS).

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

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

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

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

Як програмну реалізацію OLAP ми розглядатимемо утиліту MS Analysis Services, що входить до складу стандартної поставки MS SQL Server, скорочено SSAS. Зазначимо, що за задумом Е. Кодда OLAP-куб в аналітиці повинен дати ту саму вичерпну свободу дій, яку система OLTP та реляційна база даних (SQL Server) дають у зберіганні та вибірці інформації.

Матеріально-технічне забезпечення OLAP

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

Вважатимемо, що корпорація використовує ERP-систему, наприклад, 1С7 або 1С8, в рамках якої в звичайному порядку йде реєстрація інформації. База даних цієї ERP-системи розміщується на якомусь сервері та підтримується програмою MS SQL Server.

Вважатимемо також, що на іншому сервері встановлено матзабезпечення, що включає MS SQL Server з утилітою MS Analysis Services (SSAS), а також програми MS SQL Server Management Studio, MS C#, MS Excel та MS Visual Studio. Ці програми разом утворюють необхідний контекст: інструментарій і необхідні інтерфейси розробника OLAP-кубів.

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

На робочих станціях співробітників, у межах локальної мережі, серед іншого встановлені програми MS Excel (версії щонайменше 2003), і навіть, можливо, спеціальний драйвер забезпечення роботи MS Excel з MS Analysis Services (якщо лише відповідний драйвер не включено до MS Excel ).

Для певності вважатимемо, що на робочих станціях співробітників встановлена ​​операційна система Windows XP, а на серверах – Windows Server 2008. Крім того, нехай як SQL Server використовується MS SQL Server 2005, причому на сервері з OLAP-кубом встановлені Enterprise Edition (EE ) або Developer Edition (DE). У цих редакціях можна використовувати т.зв. «напівадитивні заходи», тобто. додаткові агрегатні функції (статистики), відмінні від звичайних сум (наприклад, екстремум чи середнє значення).

Дизайн OLAP-куба (OLAP-кубізм)

Скажемо кілька слів про дизайн самого OLAP-куба. На мові статистики OLAP-куб - це безліч показників роботи, розрахованих у всіх необхідних розрізах, наприклад, показник відвантаження в розрізах по покупцям, товарам, дат і т.п. Через прямий переклад з англійської в російській літературі по OLAP-кубах показники називаються «заходами», а розрізи – «розмірностями». Це математично коректний, але синтаксично і семантично не дуже вдалий переклад. Російські слова «міра», «вимірювання», «розмірність» майже не відрізняються за змістом та написанням, тоді як англійські «measure» і «dimension» відмінні і за написанням і змістом. Тому ми віддаємо перевагу аналогічним за змістом традиційним російським статистичним термінам «показник» та «розріз».

Існує кілька варіантів програмної реалізації OLAP-куба щодо OLTP-системи, де йде реєстрація даних. Ми розглянемо лише одну схему, найпростішу, найнадійнішу та найшвидшу.

У цій схемі OLAP і OLTP немає загальних таблиць, і аналітики OLAP розраховуються максимально детально на стадії оновлення куба (Process), попередньої стадії використання. Ця схема називається MOLAP (Multidimensional OLAP). Її мінуси – асинхронність з ERP та великі витрати пам'яті.

Хоча формально OLAP-куб можна побудувати з використанням як джерело даних всіх (тисяч) таблиць реляційної бази даних ERP-системи та всіх (сот) їх полів як показники або розрізи, реально цього робити не варто. Навпаки. Для завантаження в куб правильніше підготувати окрему базу даних, яка називається «вітрина» або «сховище» (warehouse).

Декілька причин змушують вчинити саме так.

• По перше,прив'язка OLAP-куба до таблиць реальної бази даних, напевно, створить технічні проблеми. Зміна даних у таблиці може ініціювати оновлення куба, а оновлення куба – не обов'язково швидкий процес, тому куб буде в стані перманентної перебудови; при цьому ще процедура оновлення куба може блокувати (при читанні) дані таблиць бази, гальмуючи роботу користувачів реєстрації даних в ERP-системі.
• По-друге, наявність надто великої кількості показників та розрізів різко збільшить область зберігання куба на сервері. Не забудемо, що в OLAP-кубі зберігаються не тільки вихідні дані, як в OLTP-системі, а ще й усі показники, підсумовані за всіма можливими розрізами (і навіть за всіма поєднаннями всіх розрізів). Крім того, відповідно, сповільняться швидкість оновлення куба і врешті-решт швидкість побудови та оновлення аналітик і заснованих на них звітів користувача.
• По-третє, дуже багато полів (показників і розрізів) створить проблеми в інтерфейсі розробника OLAP, т.к. списки елементів стануть неоглядними.
• По-четверте, OLAP-куб дуже чутливий до порушень цілісності даних. Куб не може бути побудований, якщо ключові дані не знаходяться за посиланням, прописаним у структурі зв'язків полів куба. Тимчасове чи постійне порушення цілісності, незаповнені поля – звичайна справа у базі даних ERP-системи, але це категорично не годиться для OLAP.

Можна ще додати, що ERP-систему та OLAP-куб слід розташовувати на різних серверах, щоб розділити навантаження. Але тоді за наявності загальних таблиць для OLAP та OLTP виникає ще й проблема мережного трафіку. Практично нерозв'язні проблеми з'являються в цьому випадку при необхідності консолідації в один OLAP-куб декількох різнорідних ERP-систем (1С7, 1С8, MS Dynamics AX).

Напевно, можна й надалі нагромаджувати технічні проблеми. Але найголовніше, згадаємо, що на відміну від OLTP, OLAP – не засіб реєстрації та зберігання даних, а засіб аналітики. Це означає, що не потрібно "про всяк випадок" вантажити і вантажити "брудні" дані з ERP в OLAP. Навпаки, потрібно спочатку виробити концепцію управління фірмою, хоча б на рівні системи KPI, і далі сконструювати прикладне сховище даних (warehouse), розташоване на тому ж сервері, що і OLAP-куб, і невелика рафінована кількість даних, що містить, з ERP, необхідних для управління .

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

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

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

Архітектура рішення

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

На сервері OLAP ми створюємо образи (порожні копії) бази даних всіх цих ERP-систем. На ці порожні копії ми періодично виконуємо часткову реплікацію баз даних відповідних активно працюючих ERP.

Далі запускаються SP (stored procedure), які на тому ж сервері OLAP без мережного трафіку на основі часткових реплік баз даних ERP-систем створюють (або поповнюють) сховище (warehouse) – джерело даних OLAP-куба.

Потім запускається стандартна процедура оновлення/побудови куба за даними warehouse (операція Process в інтерфейсі SSAS).

Прокоментуємо окремі моменти технології. Яку роботу виконують SP?

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

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

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

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

Далі, головне завдання SP – перетворити дані ERP-систем до формату warehouse. Якщо є лише одна ERP-система, то завдання перетворення в основному зводиться до викопування і, можливо, переформатування потрібних даних. Але якщо в тому самому OLAP-кубі необхідно консолідувати кілька ERP-систем різної структури, то перетворення ускладнюються.

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

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

Приділимо деяку увагу архітектурі сховища warehouse. Зазвичай схему OLAP-куба представляють як «зірки», тобто. як таблицю даних, оточену «променями» довідників – таблицями значень вторинних ключів. Таблиця – це блок показників, довідники – це їх розрізи. При цьому довідник може бути довільним незбалансованим деревом або збалансованою ієрархією, наприклад, багаторівневою класифікацією товарів або контрагентів. У OLAP-кубі числові поля таблиці даних із warehouse автоматично стають «показниками» (або вимірами measures), а за допомогою таблиць вторинних ключів можуть бути визначені розрізи (або розмірності dimensions).

Це наочне «педагогічне» опис. Насправді архітектура OLAP-куба може бути значно складнішою.

По-перше, warehouse може складатися з кількох «зірочок», можливо, пов'язаних через загальні довідники. У цьому випадку OLAP-куб буде об'єднання кількох кубів (кілька блоків даних).

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

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

По-четверте, на базі існуючих показників та розрізів при використанні виразу мови MDX можуть бути визначені нові показники (calculations). Важливо, нові куби, нові показники, нові розрізи автоматично повністю інтегровані з вихідними елементами. Слід зазначити, що невдало сформульовані показники calculations і ієрархічні розрізи можуть помітно загальмувати роботу OLAP-куба.

MS Excel як інтерфейс з OLAP

Окремий інтерес представляє інтерфейс користувача з OLAP-кубами. Звичайно найбільш повний інтерфейс надає сама утиліта SSAS. Це і інструментарій розробника OLAP-кубів, інтерактивний конструктор звітів, і вікно інтерактивної роботи з OLAP-кубом за допомогою запитів на мові MDX.

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

Інтерфейс з MS Excel забезпечує спеціальний драйвер, що окремо завантажується або включений у постачання Excel. Він не охоплює всієї функціональності OLAP, але зі зростанням номерів версій MS Excel це охоплення стає дедалі ширшим (скажімо, у MS Excel 2007 з'являється графічне зображення KPI, чого не було в MS Excel 2003 тощо).

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

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

Щонічний цикл обробки facubi

Тепер опишемо щоденний (щонощовий) обчислювальний цикл експлуатації OLAP. Розрахунок ведеться під контролем програми facubi, написаної на C# 2005 та запускається за допомогою Task Scheduler на сервері з warehouse та SSAS. На початку facubi звертається до інтернету та зчитує поточні курси валют (використовуються для представлення низки показників у валюті). Далі виконуються такі действия.

По-перше, facubi запускає SP, що виконують часткову реплікацію баз даних різних ERP-систем (елементів холдингу), доступних у локальній мережі. Реплікація виконується, як ми говорили, на заздалегідь підготовлені подвір'я – образи віддалених ERP-систем, розташовані на сервері SSAS.

По-друге, за допомогою SP виконується відображення з реплік ERP на сховище warehouse - особливу DB, яка є джерелом даних OLAP-куба і розташована на сервері SSAS. При цьому вирішуються три основні завдання:

• дані ERPпідбиваються під необхідні формати куба; мова йде і про таблиці, і про поля таблиць. (Іноді необхідну таблицю потрібно «виліпити», скажімо, з кількох аркушів MS Excel.) Аналогічні дані можуть мати різний формат у різних ERP, наприклад, ключові поля ID у довідниках 1С7 мають 36-значний символьний код довжиною 8, а поля _idrref у довідниках 1С8 - шістнадцяткові числа довжиною 32;
• по ходу обробки ведеться логічний контроль даних (зокрема прописування «умовчань» default дома пропущених даних, де це можливо) і контроль цілісності, тобто. перевірка наявності первинних та вторинних ключів у відповідних класифікаторах;
• консолідація кодів об'єктів, що мають один і той же зміст у різних ERP. Наприклад, відповідні елементи довідників різних ERP можуть мати той самий сенс, скажімо, це один і той же контрагент. Завдання консолідації кодів вирішується за допомогою побудови таблиць мепінгу, де різні коди тих самих об'єктів приводяться до єдності.

По-третє, facubi запускає стандартну процедуру поновлення даних куба Process (зі складу процедур утиліти SSAS).

Згідно з контрольними списками, програма facubi розсилає поштові повідомлення про хід виконання етапів обробки.

Виконавши facubi, Task Scheduler запускає кілька файлів excel, у яких заздалегідь створені звіти з урахуванням показників OLAP-куба. Як ми говорили, MS Excel має спеціальний програмний інтерфейс (окремо завантажуваний або вбудований драйвер) для роботи з OLAP-кубами (з SSAS). Під час запуску MS Excel включаються програми на MS VBA (типу макросів), які забезпечують оновлення даних у звітах; звіти за необхідності модифікуються та розсилаються поштою (програма blat) користувачам згідно з контрольними списками.

Користувачі локальної мережі, які мають доступ до сервера SSAS, отримають «живі» звіти, налаштовані на OLAP-куб. (В принципі вони самі, без будь-якої пошти, можуть оновлювати OLAP-звіти в MS Excel, що лежать на їх локальних комп'ютерах.) Користувачі поза локальною мережею або отримають оригінальні звіти, але з обмеженою функціональністю, або для них (після оновлення OLAP-звітів у MS Excel) будуть обчислені спеціальні «мертві» звіти, які не звертаються до сервера SSAS.

Оцінка результатів

Ми говорили вище про асинхронність OLTP та OLAP. У варіанті технології цикл оновлення OLAP-куба виконується вночі (скажімо, запускається в 1 годину ночі). Це означає, що у поточному робочому дні користувачі працюють із вчорашніми даними. Оскільки OLAP – це засіб реєстрації (подивитися останню редакцію документа), а засіб управління (зрозуміти тенденцію процесу), таке відставання зазвичай не критично. Втім, при необхідності навіть в описаному варіанті архітектури куба (MOLAP) оновлення можна проводити кілька разів на добу.

Час виконання процедур оновлення залежить від особливостей конструкції OLAP-куба (більшої чи меншої комплексності, більш менш вдалих визначень показників і розрізів) та від обсягу баз даних зовнішніх OLTP-систем. За досвідом процедури побудови warehouse займають від кількох хвилин до двох годин, процедура оновлення куба (Process) – від 1 до 20 хвилин. Йдеться про комплексні OLAP-куби, що об'єднують десятки структур типу «зірочка», про десятки загальних «променів» (довідників-розрізів) для них, про сотні показників. Оцінюючи обсяги баз даних зовнішніх ERP-систем за документами відвантаження, ми говоримо про сотні тисяч документів і, відповідно, мільйони товарних рядків на рік. Історична глибина обробки, яка цікавить користувача, становила три – п'ять років.

Описана технологія експлуатується у низці великих корпорацій: з 2008 року в «Російській рибній компанії» (РРК) та компанії «Російське море» (РМ), з 2012 року в компанії «Санта-Бремор» (СБ). Частина корпорацій є переважно торгово-закупівельними фірмами (РРК), інші – виробничими (заводи з переробки риби і морепродуктів РМ і СБ). Всі корпорації є великими холдингами, що об'єднують кілька фірм з незалежними і різними системами комп'ютерного обліку – починаючи від стандартних ERP-систем типу 1C7 і 1C8 і закінчуючи «реліктовими» обліковими системами на базі DBF і Excel. Додам, що описана технологія експлуатації OLAP-кубів (без урахування етапу розробки) або взагалі не вимагає спеціальних співробітників, або входить до кола обов'язків одного штатного бізнес-аналітика. Завдання роками крутиться в автоматичному режимі, щодня забезпечує різні категорії співробітників корпорацій актуальною звітністю.

Плюси та мінуси рішення

Як показує досвід, варіант запропонованого рішення досить надійний та простий в експлуатації. Він легко модифікується (підключення/відключення нових ERP, створення нових показників та розрізів, створення та модифікація Excel-звітів та списків їх поштового розсилання) при інваріантності керуючої програми facubi.

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

«Рафінована» БД warehouse, в якій консолідовано (у ході побудови куба) кілька різнорідних ERP-систем, навіть без будь-якого OLAP дозволяє вирішувати (на сервері SSAS, методом запитів на мові Transact SQL або методом SP та ін) безліч прикладних завдань управління. Нагадаємо, структура БД warehouse уніфікована і значно простіше (у плані кількості таблиць і числа полів таблиць), ніж структури БД вихідних ERP.

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

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

На закінчення наведемо ще один аргумент на користь OLAP, який, можливо, був би доречнішим у Середні віки. Оскільки його доказова сила лежить на авторитеті. Скромний, явно недооцінений британський математик Е. Кодд наприкінці 60-х розробив теорію реляційних БД. Сила цієї теорії була така, що зараз, через 50 років, вже важко знайти базу даних не реляційного типу і мову запиту до БД, відмінний від SQL.

Технологія OLTP, заснована на теорії реляційних БД, була першою ідеєю Е. Кодда. По суті концепція OLAP-кубів – це друга його ідея, висловлена ​​ним на початку 90-х років. Навіть не будучи математиком, можна очікувати, що друга ідея виявиться настільки ж ефективною, як перша. Тобто в плані комп'ютерної аналітики ідеї OLAP незабаром захоплять світ і витіснять усі інші. Просто тому, що тема аналітики знаходить в OLAP своє вичерпне математичне рішення, і це рішення «адекватне» (термін Б. Спінози) практичному завданню аналітики. «Адекватно» означає у Спінози, що й сам Бог не придумав би краще…

1. Ларсон Б. Розробка бізнес-аналітики в Microsoft SQL Server 2005. - СПб.: "Пітер", 2008.
2. Codd E. Relational Completeness of Base Base Sublanguages, Data Base Systems, Courant Computer Science Sumposia Series 1972, v. 6, Englwood cliffs, N.Y., Prentice – Hall.

Вконтакте

Механізм 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 систем.

07.04.2011 Дерек Комінгор

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

Що таке куб?

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

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

Куб – центральна конструкція даних в оперативній системі аналізу даних OLAP аналітичних служб SQL Server (SSAS). Куби зазвичай будуються з основної реляційної бази даних, званої моделлю розмірностей, але є окремі технічні сутності. Логічно куб є складом даних, що складається з розмірностей (dimensions) та вимірювань (measures). Розмірності містять описові ознаки та ієрархії, у той час як виміри – це факти, які ви описуєте у розмірностях. Вимірювання об'єднані у логічні поєднання, які називаються групами вимірів. Ви прив'язуєте розмірності до груп вимірів на основі ознаки - ступеня деталізації.

У файловій системі куб реалізований як послідовність пов'язаних бінарних файлів. Бінарна архітектура куба полегшує швидке вилучення великих обсягів багатовимірних даних.

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

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

Вимоги до програмного забезпечення

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

Мій приклад куба «Продажі через Інтернет» буде побудований на основі тестової бази даних AdventureWorksDW 2005. Я буду будувати тестовий куб із підмножини таблиць, знайдених у тестовій базі даних, які будуть корисні для аналізу даних про збут через Інтернет. На малюнку 1 представлено основну схему таблиць бази даних. Оскільки я використовую версію 2005, ви можете дотримуватися моїх вказівок, застосовуючи SQL Server 2005 або SQL Server 2008.

Рисунок 1. Підмножина вітрини даних Adventure Works Internet Sales

Навчальну базу даних Adventure WorksDW 2005 можна знайти на веб-сайті CodePlex: msftdbprodsamples.codeplex.com. Знайдіть посилання "SQL Server 2005 product sample databases are still available" (http://codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004). Навчальна база даних міститься у файлі AdventureWorksBI.msi (http://msftdbprodsamples.codeplex.com/releases/view/4004#DownloadId=11755).

Як уже згадувалося, необхідно мати доступ до екземпляра SQL Server 2008 або 2005, у тому числі SSAS і компонентів Business Intelligence Development Studio (BIDS). Я буду використовувати SQL Server 2008, так що ви можете побачити деякі тонкі відмінності, якщо ви використовуєте SQL Server 2005.

Створення проекту SSAS

Перше, що ви повинні зробити, це створити проект SSAS, використовуючи BIDS. Знайдіть BIDS у меню Start і далі в меню Microsoft SQL Server 2008/2005 підпункт SQL Server Business Intelligence Development Studio. При натисканні на цю кнопку запуститься BIDS з екраном заставки за промовчанням. Створіть новий проект SSAS, обравши File, New, Project. Ви побачите діалогове вікно New Project (новий проект), яке показано на екрані 1. Виберіть папку проекту Analysis Services Project і вкажіть опис цього проекту «SQLMAG_MyFirstCube». Натисніть кнопку ОК.

Коли проект буде створено, клацніть правою кнопкою миші в Solution Explorer і виберіть в контекстному меню пункт властивостей Properties. Тепер виберіть розділ Deployment у лівій частині діалогового вікна SQLMAG_MyFirstCube: Property Pages та перевірте установки значень для параметрів Target Server та Database settings, як показано на екрані 2. Якщо ви працюєте в розподіленому середовищі SQL Server, вам необхідно уточнити значення властивості Target Server іменем сервер на який ви збираєтеся робити розгортання. Натисніть OK, коли влаштують встановлені параметри розгортання для цього проекту SSAS.

Визначення джерела даних

Перший об'єкт, який потрібно створити, – це джерело даних. Об'єкт джерела даних забезпечує схему та дані, що використовуються при побудові пов'язаних з кубом і розташованих у його основі об'єктів. Щоб створити об'єкт джерела даних у BIDS, використовуйте майстер джерел даних Data Source Wizard.

Почніть роботу майстра джерела даних клацанням правою кнопкою миші по папці Data Source на панелі Solution Explorer з вибору пункту New Data Source. Ви виявите, що створення об'єктів SSAS у BIDS має характер розробки. Спочатку майстер проводить вас через процес створення об'єкта та загальні налаштування. А потім ви відкриваєте отриманий об'єкт SSAS у проектувальнику і детально підлаштовує його, якщо потрібно. Як тільки ви проходите екран запрошення, визначте нове з'єднання з даними, натискаючи кнопку New. Виберіть і створіть нове з'єднання на основі Native OLEDB SQL Server Native Client 10, що вказує на бажаний для вас сервер SQL Server, який володіє потрібним екземпляром бази даних. Ви можете використовувати автентифікацію Windows або SQL Server, залежно від налаштувань навколишнього середовища SQL Server. Натисніть кнопку Test Connection, щоб переконатися, що ви правильно визначили з'єднання з базою даних, а потім кнопку OK.

Далі слідує Impersonation Information (інформація про налаштування запозичення прав), яка, як і зв'язок з даними, залежить від того, як влаштовано середовище SQL Server. Запозичення прав - це контекст безпеки, який покладається SSAS, обробляючи свої об'єкти. Якщо ви керуєте розгортанням на основному, єдиному сервері (або ноутбуці), як, на мою думку, більшість читачів, ви можете просто вибрати варіант використання облікового запису служби Use the service account. Натисніть Next, щоб завершити роботу майстра джерела даних, і вкажіть AWDW2005 як ім'я джерела даних. Дуже зручно, що можна задіяти цей метод для тестування, але в реальному виробничому середовищі це не найкраща практика - використовувати обліковий запис служби. Найкраще вказати доменні облікові записи для запозичення прав підключення SSAS до джерела даних.

Подання джерела даних

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

Підемо далі, клацніть правою кнопкою миші по папці DSV і оберемо пункт New Data Source View, щоб запустити майстер створення нових уявлень DSV. У діалоговому вікні на кроці Select a Data Source виберіть з'єднання з реляційною базою даних і натисніть кнопку Next. Виберіть таблиці FactInternetSales, DimProduct, DimTime, DimCustomer і натисніть кнопку з одиночною стрілкою праворуч, щоб перенести ці таблиці в колонку Included. Нарешті, клацніть Next і завершіть роботу майстра, приймаючи стандартне ім'я і натискаючи кнопку Finish.

На даному етапі у вас має бути представлення DSV, яке розташоване під папкою Data Source Views Solution Explorer. Виконайте подвійне клацання по новому DSV, щоб запустити конструктор DSV. Ви повинні побачити всі чотири таблиці для цього DSV, як показано на малюнку 2.

Створення розмірності бази даних

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

Розмірності бази даних та куба забезпечують витончене рішення для концепції, відомої як «рольові розмірності». Рольові розмірності застосовуються, коли вам необхідно використовувати єдину розмірність у кубі багаторазово. Дата - чудовий приклад у даному екземплярі куба: ви будуватимете єдину розмірність дати і посилатиметеся на неї один раз для кожної дати, для якої хочете аналізувати продажі через Інтернет. Календарна дата буде першою розмірністю, яку ви створите. Клацніть правою кнопкою мишки по папці Dimensions у Solution Explorer і виберіть New Dimension, щоб запустити майстер розмірності Dimension Wizard. Виберіть пункт Use an existing table і клацніть Next на кроці вибору методу створення Select Creation Method. На кроці визначення джерела інформації Specify Source Information вкажіть таблицю DimTime в списку Main table, що розкривається, і натисніть кнопку Next. Тепер на кроці вибору ознаки розмірності Select Dimension Attributes вам необхідно відібрати атрибути розмірності часу. Виберіть кожний атрибут, як показано на екрані 3.

Натисніть кнопку Next. На завершальному кроці введіть Dim Date у полі Name і натисніть кнопку Finish, щоб завершити роботу майстра розмірності. Тепер ви повинні побачити нову розмірність дати Dim Date, розташовану під папкою Dimensions у Solution Explorer.

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

Створення куба продажів через Інтернет

Тепер, підготувавши розмірність бази даних, ви можете приступити до будівництва куба. У Solution Explorer клацніть правою кнопкою миші на папці Cubes і виберіть New Cube, щоб запустити майстра створення кубів Cube Wizard. У вікні Select Creation Method виберіть варіант використання існуючих таблиць Use existing tables. Виберіть таблицю FactInternetSales для Measure Group на кроці вибору таблиці груп вимірювання Select Measure Group Tables. Видаліть прапорець поруч із вимірюваннями Promotion Key, Currency Key, Sales Territory Key та Revision Number на кроці Select Measures і натисніть Next.

На екрані Select Existing Dimensions переконайтеся, що всі існуючі розмірності бази даних вибрані, щоб використовувати їх як розмірності куба. Оскільки я хотів би зробити цей куб настільки простим, наскільки це можливо, скасуйте вибір розмірності FactInternetSales на кроці Select New Dimensions. Залишаючи розмірність вибраної FactInternetSales, ви створили б те, що називається розмірністю факту або виродженою розмірністю. Розмірності факту - це розмірності, створені з допомогою основний таблиці фактів на противагу традиційної таблиці размерностей.

Натисніть кнопку Next, щоб перейти до кроку Completing the Wizard, і введіть "Мій перший куб" у поле імені куба. Натисніть кнопку Finish, щоб завершити роботу майстра створення куба.

Розгортання та обробка куба

Тепер все готове до розгортання та обробки першого куба. Клацніть правою кнопкою миші по значку нового куба в Solution Explorer і виберіть Process. Ви побачите вікно з повідомленням про те, що зміст видається застарілим. Натисніть Yes, щоб розгорнути новий куб на цільовому сервері SSAS. При розгортанні куба ви надсилаєте файл XML for Analisis (XMLA) на цільовий сервер SSAS, який створює куб на самому сервері. Як уже згадувалося, обробка куба заповнює його двійкові файли на диску даними з основного джерела, а також додатковими метаданими, які ви додали (розмірності, вимірювання та налаштування куба).

Після завершення процесу розгортання з'являється нове діалогове вікно Process Cube. Натисніть кнопку Run, щоб розпочати процес обробки куба, який відкривається вікном Process Progress. Після завершення обробки натисніть кнопку Close (два рази, щоб закрити обидва діалогові вікна) для завершення процесів розгортання та обробки куба.

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

Дерек Комінгор ( [email protected]) - старший архітектор у компанії B. I. Voyage, що має статус Microsoft Partner у галузі бізнес-аналітики. Має звання SQL Server MVP та кілька сертифікатів Microsoft