Excel olap куби лабораторна варіант. Введення в багатомірний аналіз

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

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

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

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

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

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

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

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

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

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

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

Мал. 2. Приклад бюджету продажів, побудованого на основі двох аналітик "Продукти" і "Філії" в OLAP-кубі програмного комплексу "Інтеграл"

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

Мал. 3. Приклад бюджету продажів, побудованого на основі трьох аналітик "Продукти", "Філії" і "Канали збуту" в OLAP-кубі програмного комплексу "Інтеграл"

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

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

Мал. 4. Приклад бюджету продажів, побудованого на основі трьох аналітик "Продукти", "Канали збуту" і "Філії" в OLAP-кубі програмного комплексу "Інтеграл"

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

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

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

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

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

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

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

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

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

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

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

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

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

Мал. 8. Редактор карти вимірів програмного комплексу "Інтеграл"

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

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

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

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

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

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

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

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

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

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

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

У попередній статті даного циклу (див. № 2'2005) ми розповіли про основні нововведення аналітичних служб SQL Server 2005. Сьогодні ми докладніше розглянемо засоби створення OLAP-рішень, що входять в цей продукт.

Коротко про основи OLAP

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

OLAP-куби

Що являють собою OLAP-дані? Як відповідь на це питання розглянемо найпростіший приклад. Припустимо, в корпоративній базі даних якогось підприємства є набір таблиць, що містять відомості про продажі товарів або послуг, і на їх основі створено уявлення Invoices з полями Country (країна), City (місто), CustomerName (назва компанії-клієнта), Salesperson (менеджер з продажу), OrderDate (дата розміщення замовлення), CategoryName (категорія товару), ProductName (найменування товару), ShipperName (компанія-перевізник), ExtendedPrice (оплата за товар), при цьому останній із зазначених полів, власне, і є об'єктом аналізу .

Вибір даних з такого уявлення можна здійснити за допомогою наступного запиту:

SELECT Country, City, CustomerName, Salesperson,

OrderDate, CategoryName, ProductName, ShipperName, ExtendedPrice

FROM Invoices

Припустимо, нас цікавить, яка сумарна вартість замовлень, зроблених клієнтами з різних країн. Для отримання відповіді на це питання необхідно зробити наступний запит:

SELECT Country, SUM (ExtendedPrice) FROM Invoices

GROUP BY Country

Результатом цього запиту буде одновимірний набір агрегатних даних (в даному випадку сум):

Country	SUM (ExtendedPrice)
Argentina	7327.3
Austria	110788.4
Belgium	28491.65
Brazil	97407.74
Canada	46190.1
Denmark	28392.32
Finland	15296.35
France	69185.48
	209373.6
	...

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

SELECT Country, ShipperName, SUM (ExtendedPrice) FROM Invoices

GROUP BY COUNTRY, ShipperName

Виходячи з результатів цього запиту можна створити таблицю наступного виду:

Такий набір даних називається зведеною таблицею (pivot table).

SELECT Country, ShipperName, SalesPerson SUM (ExtendedPrice) FROM Invoices

GROUP BY COUNTRY, ShipperName, Year

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

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

Ієрархії в вимірах

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

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

Створення OLAP-кубів в SQL Server 2005

SQL Server 2005 куби створюються за допомогою SQL Server Business Intelligence Development Studio. Цей інструмент являє собою спеціальну версію Visual Studio 2005, призначену для вирішення даного класу задач (а при наявності вже встановленої середовища розробки список шаблонів проектів поповнюється проектами, призначеними для створення рішень на основі SQL Sever і його аналітичних служб). Зокрема, для створення рішень на основі аналітичних служб призначений шаблон Analysis Services Project (рис. 3).

Для створення OLAP-куба в першу чергу слід вирішити, на основі яких даних його формувати. Найбільш часто OLAP-куби будуються на основі реляційних сховищ даних зі схемами «зірка» або «сніжинка» (про них ми розповідали в попередній частині статті). У комплекті поставки SQL є приклад такого сховища база даних AdventureWorksDW, для використання якої в якості джерела слід знайти в Solution Explorer папку Data Sources, вибрати пункт контекстного меню New Data Source і послідовно відповісти на питання відповідного майстра (рис. 4).

Потім рекомендується створити Data Source View уявлення, на основі якого буде створюватися куб. Для цього необхідно вибрати відповідний пункт контекстного меню папки Data Source Views і послідовно відповісти на питання майстра. Результатом зазначених дій стане схема даних, за допомогою яких буде побудовано уявлення джерел даних, при цьому в отриманій схемі замість вихідних можна вказати «дружні» імена таблиць (рис. 5).

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

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

Відзначимо, що в створеному кубі можна міняти склад заходів, видаляти і додавати атрибути вимірювань і додавати обчислювані атрибути членів вимірювань на основі наявних атрибутів (рис. 8).

Мал. 8. Додавання обчислюється атрибута

Крім того, в кубах SQL Server 2005 можна здійснювати автоматичну угруповання або сортування членів вимірювання за значенням атрибута, визначати зв'язки між атрибутами, реалізовувати зв'язку «багато до багатьох», визначати ключові показники бізнесу, а також вирішувати багато інших завдань (подробиці про те, як виконуються всі ці дії, можна знайти в розділі SQL Server Analysis Services Tutorial довідкової системи даного продукту).

У наступних частинах цієї публікації ми продовжимо знайомство з аналітичними службами SQL Server 2005 і з'ясуємо, що нового з'явилося в області підтримки Data Mining.

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

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

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

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

Тривимірне представлення таблиці. Сірим сегментом показано, що для Аргентини в 1988 році даних немає

Ось саме такий тривимірний масив в термінах OLAP і називається кубом. Насправді, з точки зору суворої математики кубом такий масив буде далеко не завжди: у справжнього куба кількість елементів у всіх вимірах має бути однаковим, а у кубів OLAP такого обмеження немає. Проте, не дивлячись на ці деталі, термін "куби OLAP" зважаючи на свою стислості та образності став загальноприйнятим. Куб OLAP зовсім не обов'язково повинен бути тривимірним. Він може бути і дво-, і багатовимірним - в залежності від розв'язуваної задачі. Особливо запеклим аналітикам може знадобитися близько 20 вимірювань - і серйозні OLAP-продукти саме на таку кількість і розраховані. Простіші настільні додатки підтримують десь 6 вимірювань.

вимірювання OLAP-кубів складаються з так званих міток або членів (members). Наприклад, вимір "Країна" складається з міток "Аргентина", "Бразилія", "Венесуела" і так далі.

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

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

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

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

приклад ієрархії

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

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

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

Відповісти на питання:

Що таке куб OLAP?

Що таке мітки конкретного виміру? Привести приклади.

чи можуть заходи в кубі OLAP, містити нечислові значення.

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

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

Що таке куб?

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

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

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

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

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

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

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

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

Мій приклад куба «Продажі через Інтернет» буде побудований на основі тестової бази даних AdventureWorksDW 2005. Я буду будувати тестовий куб з підмножини таблиць, знайдених в тестовій базі даних, які будуть корисні для аналізу даних про збут через Інтернет. На малюнку 1 представлена \u200b\u200bосновна схема таблиць бази даних. Оскільки я використовую версію 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\u003d4004). Навчальна база даних міститься в файлі AdventureWorksBI.msi (http://msftdbprodsamples.codeplex.com/releases/view/4004#DownloadId\u003d11755).

Як уже згадувалося, необхідно мати доступ до примірника 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 c екраном заставки за замовчуванням. Створіть новий проект 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

Куби даних 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). Файли кубів даних мають расшіреніе.cub.

Через деякий час 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. Натисніть ОК.

Зверніть увагу на кнопку перевірити 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.