Що таке olap куб в excel. Основні параметри системи. Основні характеристики OLAP включають

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

У попередній статті даного циклу (Див. № 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" і "багатовимірні дані" стійко пов'язані в нашій свідомості. Проте той факт, що ця тема знову піднімається, сподіваюся, буде схвалений більшістю читачів, т. К. Для того, щоб уявлення про що-небудь з часом не застарівало, потрібно періодично спілкуватися з розумними людьми або читати статті в хорошому виданні ...

Сховища даних (місце 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-продуктах, що випускаються провідними виробниками.

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. У зведених таблицях OLAP кеш ніколи не зберігається на жорсткому диску. Тому відразу ж після відключення від локальної мережі ваша зведена таблиця втратить працездатність. Ви не зможете перемістити в ній жодного поля.

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

Щоб створити автономний куб даних, спочатку створіть зведену таблицю OLAP. Помістіть курсор в межах зведеної таблиці і клацніть на кнопці Засоби OLAP (OLAP Tools) контекстної вкладки Параметри (Tools), що входить в групу контекстних вкладок Робота зі зведеними таблицями (PivotTable Tools). Виберіть команду Автономний режим OLAP (Offline OLAP) (рис. 9.8).

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

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

Клацніть на кнопці Далі для переходу до наступного діалогового вікна майстра. У ньому ви отримуєте можливість вказати члени або елементи даних, які не включатимуться до куб. Зокрема, вам не потрібно міра Internet Sales-Extended Amount, тому прапорець для неї буде скинутий в списку. Скинутий прапорець вказує на те, що вказаний елемент не буде імпортуватися і займати зайве місце на жорсткому диску.

На останньому етапі вкажіть розташування та ім'я куба даних. У нашому випадку файл куба буде названий MyOfflineCube.cub і буде розташовуватися в папці Work.

Файли кубів даних мають розширення .cub

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

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