Куби даних. Ієрархії в вимірах. Ієрархії і рівні

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

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

В рамках даної роботи будуть розглянуті наступні питання:

• Що являють собою OLAP-куби?
• Що таке заходи, вимірювання, ієрархії?
• Які види операцій можна виконувати над OLAP-кубами?

Поняття OLAP-куба

Головний постулат OLAP - багатовимірність в поданні даних. У термінології OLAP для опису багатовимірного дискретного простору даних використовується поняття куба, або гіперкуба.

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

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

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

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

На малюнку 1 показаний приклад куба, призначеного для аналізу продажів продуктів нафтопереробки деякої компанією по регіонах. Даний куб має три виміри (час, товар і регіон) і одну міру (обсяг продажів, виражений в грошовому еквіваленті). Значення заходів зберігаються у відповідних осередках (cell) куба. Кожна осередок унікально ідентифікується набором членів кожного з вимірів, званого кортежем. Наприклад, комірка, розташована в нижньому лівому кутку куба (містить значення $ 98399), задається кортежем [Липень 2005, Далекий Схід, Дизель]. Тут значення $ 98399 показують обсяг продажів (у грошовому вираженні) дизеля на Далекому Сході за липень 2005 року.

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

Мал. 1. Куб з інформацією про продажі нафтопродуктів в різних регіонах

Кінцевою метою створення подібних кубів є мінімізація часу обробки запитів, які отримають необхідну інформацію з фактичних даних. Для реалізації цього завдання куби зазвичай містять попередньо обчислені підсумкові дані, звані агрегації (Aggregations). Тобто куб охоплює простір даних більше, ніж фактичне - в ньому існують логічні, обчислювані точки. Обчислювати значення точок в логічному просторі на основі фактичних значень дозволяють функції агрегування. Найбільш простими функціями агрегування є SUM, MAX, MIN, COUNT. Так, наприклад, використовуючи функцію MAX, для наведеного в прикладі куба можна виявити, коли стався пік продажів дизеля на Далекому Сході і т.д.

Ще однією специфічною рисою багатовимірних кубів є складність визначення точки початку координат. Наприклад, як задати точку 0 для вимірювання "Товар" або "Регіони"? Вирішенням цієї проблеми є впровадження спеціального атрибута, що об'єднує всі елементи вимірювання. Цей атрибут (створюється автоматично) містить всього один елемент - All ( "Все"). Для простих функцій агрегування, наприклад, суми, елемент All еквівалентний сумі значень усіх елементів фактичного простору даного виміру.

Важливою концепцією багатовимірної моделі даних є підпростір, або подкуб (sub cube). Подкуб є частиною повного простору куба у вигляді деякої багатовимірної фігури всередині куба. Так як багатовимірний простір куба дискретно і обмежена, подкуб також дискретний і обмежений.

Операції над OLAP-кубами

Над OLAP-кубом можуть виконуватися такі операції:

• зріз;
• обертання;
• консолідація;
• деталізація.

зріз (Рисунок 2) є окремим випадком подкуба. Це процедура формування підмножина багатовимірного масиву даних, відповідне єдиному значенню одного або декількох елементів вимірювань, що не входять в це підмножина. Наприклад, щоб дізнатися, як просувалися продажу нафтопродуктів в часі тільки в певному регіоні, а саме на Уралі, то необхідно зафіксувати вимір "Товари" на елементі "Урал" і витягти з куба відповідне підмножина (подкуб).

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

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

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

Сховища даних (місце OLAP в інформаційній структурі підприємства)

Термін "OLAP" нерозривно пов'язаний з терміном "сховище даних" (Data Warehouse).

Наведемо визначення, сформульоване "батьком-засновником" сховищ даних Біллом Інмона: "Сховище даних - це предметно-орієнтоване, прив'язане до часу і незмінне збори даних для підтримки процесу прийняття управлінських рішень".

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

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

Таким чином, завдання сховища - надати "сировину" для аналізу в одному місці і в простій, зрозумілій структурі. Ральф Кімбол в передмові до своєї книги "The Data Warehouse Toolkit" пише, що якщо після прочитання всієї книги читач зрозуміє тільки одну річ, а саме: структура сховища повинна бути простою, - автор буде вважати своє завдання виконаним.

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

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

OLAP - зручний інструмент аналізу

Централізація і зручне структурування - це далеко не все, що потрібно аналітику. Адже йому ще потрібно інструмент для перегляду, візуалізації інформації. Традиційні звіти, навіть побудовані на основі єдиного сховища, позбавлені одного - гнучкості. Їх не можна "покриття", "розгорнути" або "згорнути", щоб отримати бажане уявлення даних. Звичайно, можна викликати програміста (якщо він захоче прийти), і він (якщо не зайнятий) зробить новий звіт досить швидко - скажімо, протягом години (пишу і сам не вірю - так швидко в житті не буває; давайте дамо йому години три) . Виходить, що аналітик може перевірити за день не більше двох ідей. А йому (якщо він хороший аналітик) таких ідей може приходити в голову по кілька на годину. І чим більше "зрізів" і "розрізів" даних аналітик бачить, тим більше у нього ідей, які, в свою чергу, для перевірки вимагають все нових і нових "зрізів". От би йому такий інструмент, який дозволив би розгортати і згортати дані просто і зручно! В якості такого інструменту і виступає OLAP.

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

Компоненти, що входять до типове сховище, представлені на рис. 1.

Мал. 1. Структура сховища даних

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

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

Визначення та основні поняття OLAP

Для початку розшифруємо: OLAP - це Online Analytical Processing, т. Е. Оперативний аналіз даних. 12 визначальних принципів OLAP сформулював в 1993 р Е. Ф. Кодд - "винахідник" реляційних БД. Пізніше його визначення було перероблено в так званий тест FASMI, що вимагає, щоб OLAP-додаток надавало можливості швидкого аналізу розділяється багатовимірної інформації ().

тест FASMI

Fast (Швидкий) - аналіз повинен проводитися однаково швидко з усіх аспектів інформації. Прийнятний час відгуку - 5 з менш.

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

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

Multidimensional (Багатовимірні) - це основна, найбільш істотна характеристика OLAP.

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

OLAP \u003d багатовимірне представлення \u003d Куб

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

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

Мал. 2. Приклад куба

"Розрізування" куба

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

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

Погляньте на рис. 3 - тут зображений двовимірний зріз куба для однієї заходи - Unit Sales (продано штук) і двох "нерозрізаних" вимірів - Store (Магазин) і Час (Time).

Мал. 3. Двовимірний зріз куба для однієї заходи

На рис. 4 представлено лише одне "нерозрізаного" вимір - Store, але зате тут відображаються значення декількох заходів - Unit Sales (продано штук), Store Sales (сума продажу) і Store Cost (витрати магазину).

Мал. 4. Двовимірний зріз куба для декількох заходів

Двовимірне подання куба можливо і тоді, коли "нерозрізаними" залишаються і більше двох вимірів. При цьому на осях зрізу (рядках і шпальтах) будуть розміщені два або більше вимірювань "розрізається" куба - см. Рис. 5.

Мал. 5. Двовимірний зріз куба з декількома вимірами на одній осі

Мітки

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

Ієрархії і рівні

Мітки можуть об'єднуватися в ієрархії, що складаються з одного або декількох рівнів (levels). Наприклад, мітки вимірювання "Магазин" (Store) природно об'єднуються в ієрархію з рівнями:

Country (Країна)

State (Штат)

City (Місто)

Store (Магазин).

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

Архітектура OLAP-додатків

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

Багатовимірність в OLAP-додатках може бути розділена на три рівні:

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

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

Конкретні OLAP-продукти, як правило, являють собою або засіб багатовимірного представлення даних, OLAP-клієнт (наприклад, Pivot Tables в Excel 2000 фірми Microsoft або ProClarity фірми Knosys), або багатовимірну серверну СУБД, OLAP-сервер (наприклад, Oracle Express Server або Microsoft OLAP Services).

Шар багатовимірної обробки зазвичай буває вбудований в OLAP-клієнт і / або в OLAP-сервер, але може бути виділений в чистому вигляді, як, наприклад, компонент Pivot Table Service фірми Microsoft.

Технічні аспекти багатовимірного зберігання даних

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

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

Але, як відомо, за все треба платити. І за швидкість обробки запитів до сумарних даними доводиться платити збільшенням обсягів даних і часу на їх завантаження. Причому збільшення обсягу може стати буквально катастрофічним - в одному з опублікованих стандартних тестів повний підрахунок агрегатів для 10 Мб вихідних даних зажадав 2,4 Гб, т. Е. Дані виросли в 240 разів! Ступінь "розбухання" даних при обчисленні агрегатів залежить від кількості вимірювань куба і структури цих вимірів, т. Е. Співвідношення кількості "батьків" і "дітей" на різних рівнях вимірювання. Для вирішення проблеми зберігання агрегатів застосовуються часом складні схеми, що дозволяють при обчисленні далеко не всіх можливих агрегатів досягати значного підвищення продуктивності виконання запитів.

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

• MOLAP (Multidimensional OLAP) - і детальні дані, і агрегати зберігаються в багатовимірної БД. В цьому випадку виходить найбільша надмірність, так як багатовимірні дані повністю містять реляційні.
• ROLAP (Relational OLAP) - детальні дані залишаються там, де вони "жили" спочатку - в реляційної БД; агрегати зберігаються в тій же БД в спеціально створених службових таблицях.
• HOLAP (Hybrid OLAP) - детальні дані залишаються на місці (в реляційної БД), а агрегати зберігаються в багатовимірної БД.

Кожен з цих способів має свої переваги і недоліки і повинен застосовуватися в залежності від умов - обсягу даних, потужності реляційної СУБД і т. Д.

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

Далі буде. Надалі ми поговоримо про конкретні OLAP-продуктах, що випускаються провідними виробниками.