RAD з допомогою багатовимірного табличного процесора



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

Багато використовують табличні процесори (Excel, OpenOffice Calc і т. д.) для швидкого створення додатків, які виконують прості обчислення, допомагають при створенні звітів або полегшують планування. Незважаючи на те, що можливості таких програм, як правило, дуже обмежені, простота табличних процесорів робить такий підхід дуже популярним. Мова в цій статті піде про спробу розширити можливості табличних процесорів за допомогою багатовимірної моделі даних (зазвичай асоціюється з поняттям OLAP), намагаючись при цьому не занадто ускладнити роботу з новим інструментом. Кому цікаво читаємо далі.


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

  1. Зібрати дані від безлічі (5-1000) користувачів. Дані представляли собою цифри, прив'язані до декількох атрибутів, як наприклад: продажі в певній точці, за певний час, певного продукту або витрати на проект, категорію, відділ. Документ зазвичай або перебував у мережній папці, або копії надсилалися користувачам по мейлу, а відповідь вручну переносився в один документ.
  2. Порахувати статистику/результат. Excel ускладнювався протягом декількох років і крім безлічі формул його використання передбачало кілька кроків які виконувалися вручну. Також можливі симуляції обчислень з різними вхідними даними. Наприклад: що було б, якби курс долара виріс на 10%.
  3. Показати результат у вигляді таблиці. Зазвичай ця таблиця згодом використовувалася в друкованих звітах або у слайдах презентації.
Рішення на базі табличних процесорів, з якими я мав справу, як правило, використовувалися з однієї з двох цілей:
  • планування, коли плани і прогнози на майбутнє знаходяться не в транзакційних базах компанії, а в головах експертів
  • спеціальні звіти, вихідні дані яких не ведуться в транзакційних системах
З часом кількість ручної роботи, пов'язаної з використанням табличних процесорів сильно зростала, і організація вирішувалася оплатити розробку спеціального ПЗ з можливістю паралельної роботи багатьох користувачів і надійного зберігання даних в базі.

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

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


У рядках дні, а в стовпцях кількість проданих пляшок лимонаду, виручка і відсоток виконання плану. Для того щоб порівнювати плани з реальністю, ми будемо вести дані в двох варіантах: заплановані і реальні. Ми створили по робочому листу на кожного співробітника, і вважаємо за ним суму виходячи з того, що в кожному листі рядок Total має номер 33. Кожен місяць ми копіюємо листок, стираємо дані і заповнюємо знову.

Запитання: Що робити, якщо у нас з'явиться кілька продуктів (свіжовичавлений апельсиновий сік?), ми захочемо статистику по місяцях або за рік, розшириться штаб продавців і ми захочемо побачити дані по районах міста (наприклад, щоб бачити ефект реклами)? Що якщо ми захочемо ускладнити формули і додати планування витрат? Ми, звичайно, можемо ще трохи помучитися з таблицями: наплодити робочих аркушів на кожен продукт і продавця (приміром, всього 5 продуктів і 10 продавців = 50 аркушів), виправляти скопійовані формули, писати скрипти, щоб згрупувати продавців і так далі. А що, якщо…

Ідея
Згадавши університетський курс з баз даних, я відразу помітив, що багатовимірна модель даних дуже добре підходить для опису таких систем.
Моїм першим рішенням було створення на фреймворку .NET, який зберігав дані в базі у вигляді багатовимірного куба, займався контролем доступу до даних і дозволяла описувати обчислення у вигляді скриптів на IronPython. Хоча коду на кожен новий проект було потрібно значно менше, використання цього фреймворку вимагало знання C# і баз даних.
Через якийсь час з'явилася ідея створити схожу на табличні процесори багатовимірну систему, робота з якої не вимагала уміння програмувати.

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

Багатовимірні табличні процесори (Multi-dimensional spreadsheets)

У 1991 році компанія Lotus Development Corporation випустила продукт Lotus Improv, який є типовим представником цього типу програм. Незважаючи на те, що цей продукт послужив прикладом для цілого класу подібних систем, він не був дуже вдалим, і його розробка була припинена в 1996 році. Наскільки я зрозумів з статей в інтернеті, продукт був розрахований на одного користувача. Для себе я зробив наступні висновки:
  • Багатовимірна модель даних — занадто складний концепт для більшості пересічних користувачів.
  • Використовувати багатовимірний куб поодинці рідко має сенс. Дані, які вводить одна людина, частіше за все чудово помістяться в двомірну таблицю. Багатовимірна модель даних має сенс в многопользовательском сценарії.

Повноцінні Business Intelligence системи з функцією планування

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

Рішення
Написати системи, робота з якою буде такий же простий, як і використання табличних процесорів, що володіє наступними властивостями:
  • Багатовимірна і ієрархічна модель даних
  • Формули, максимально наближені до вже знайомим формулами табличних процесорів
  • Можливість працювати паралельно через веб
  • Контроль доступу до даних і формул
  • Проста інсталяція, можливість почати працювати за пару хвилин, використовуючи безкоштовну версію
  • Простота в обслуговуванні (без необхідності працювати в хмарі)
  • Тільки кілька людей, які створюють модель додатка, повинні розуміти концепти OLAP. Користувач, який вводить дані, просто заповнює таблицю.
  • Здатність зберігати і швидко прораховувати великі обсяги даних (до мільярда значень)
Дизайн

Загальна архітектура

Система (кодова назва Egeria) розрахована на багатокористувацьку роботу через Web і складається з сервера, написаного .NET/C# і веб-клієнта. Для роботи в домашніх умовах» є просте додаток (Launcher), яке дозволяє вибрати папку з даними, запускає локальний сервер і відкриває браузер з потрібною адресою.

Вимірювання та їх елементи

На відміну від табличних процесорів, де рядки і стовпці пронумеровані цифрами і літерами відповідно, члени/елементи вимірювання (dimension members) в кубах зазвичай створюються з елементів прикладної області (в нашому випадку це продавці, продукти, дні і так далі). Часто для опису елементів кожного вимірювання використовуються таблиці баз даних. Оскільки я не хотів ускладнювати життя користувачів вивченням SQL, я вирішив створити візуальний концепт для репрезентації метаданих.
Те, що вийшло, я назвав візуальний документ. Всі метадані в системі, включаючи системні об'єкти (такі як куб або вимірювання), описуються візуальним документом. Приклад такого документу, який описує форматування позиції звіту, видно на зображенні нижче:


Кожен документ має два поля: код (Code) для посилань на документ з формул та ім'я (Name) для відображення на екрані. Сам візуальний документ має ієрархічну структуру і складається зі звичайних елементів введення, як наприклад: поле для введення тексту, списки вибору, чекбокс або список елементів. Структура візуального документа описується візуальної схемою, яка в свою чергу є візуальним документом (структуру візуальної схеми можна описати візуальної схемою так само, як структуру XML Schema можна описати з допомогою інстанції XML Schema). Схема, що описує документ з попереднього зображення, визначає поле «format». Цю схему видно в розділі «Level data type» наступного зображення:


На цій картинці показано опис вимірювання «позиція звіту» (Position). У Egeria всі вимірювання ієрархічні (навіть якщо визначений лише один рівень ієрархії). Це означає, що кожне вимірювання можна представити у вигляді дерева. Як вже було сказано, елементи цього дерева — візуальні документи. Документи кожного рівня ієрархії мають свою схему, описану в Dimension model. Вимірювання Position має один рівень ієрархії, який називається position. Так само в кожне вимірювання додається нульовий елемент з кодом зірочка (*). Цей елемент не містить даних і знаходиться на самому верхньому рівні ієрархії, який теж має ідентифікатор зірка (*). На першій картинці зліва ми бачимо, що вимірювання Position складається з 5 елементів: *, P01, P02, P03, P04.

Куби

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

Подання даних: робочий аркуш

Сучасні екрани показують не більше двох вимірювань, що змушує нас проектувати дані на рядки і стовпці двомірної таблиці. Це перетворення і вибір ділянки куба, доступного для користувача, описується за допомогою концепту робочого аркуша (worksheet).
У Egeria, на відміну від табличних процесорів, робочий лист не містить даних (вони зберігаються в кубі), а просто відображає дані частини куба. Два аркуші можуть показувати один і той же регіон куба.
Працює все так само, як зведена таблиця (pivot table) в табличних процесорах. Користувач вибирає, які вимірювання будуть стовпцями, які рядками і які фільтрами з допомогою drag&drop. Після цього для кожного вимірювання вибираються елементи, які будуть відображатися на робочому листі. Після того як робочий аркуш збережено, можна вводити дані в куб і створювати формули.

Обчислення

Проста, але гнучка система обробки обчислень в кубі — головна перевага Egeria. Всі обчислення проводяться на сервері. Формули компілюються в машинний код і тому виконуються дуже швидко. На середньому ноутбуці за 10-20 секунд повністю прораховується куб з п'ятьма мільйонами значень (кількість заповнених клітин в кубі не грає ролі). Система трекінгу залежностей в кубі дозволяє прораховувати зміна однієї комірки куба миттєво (якщо, звичайно, від цієї клітинки не залежать ще 5 мільйонів осередків).
Хотілося зробити формули Egeria максимально схожими на формули звичайних табличних процесорів. Ось 3 основні відмінності, яких я не зміг уникнути:
  • Egeria трохи розширює стандартний мова формул, щоб дати можливість використовувати метадані у формулах. Вираз pos.format, наприклад, поверне вміст поля format елемента вимірювання pos поточної комірки.
  • Додана пара нових функцій, що дозволяють працювати з метаданими і форматувати комірки. Більш детально про них можна прочитати в документації системи.
  • Важливе нововведення — це система адресації в кубі:

Адресація в кубі

Адреси в табличних процесорах виглядають так: B3 або $C$4 (перший відносний, другий абсолютний). Писати кожне вимірювання в кожен адресу було б занадто складно (у нас може бути 10-15 вимірювань). До того ж Egeria відмовляється від копіювання формул з міркувань швидкодії і зрозумілості отриманої моделі обчислень (для кожної формули ми описуємо простір, в якому вона діє).
Адресація в системі відбувається завжди відносно клітинки, яка вважається в даний момент.
Кожне звернення в куб полягає у квадратні дужки. В дужках через кому перераховуються адреси по кожному вимірюванню, яке потрібно змінити, щоб дістатися до потрібної клітинки. Вираз [] поверне клітинку, яка обробляється в даний момент. Вираз [dim1:a1] поверне клітинку з кодом a1 у вимірі dim1, залишивши залишок адреси не зміненим. Так само є спеціальні функції, які дозволяють звертатися до елементів ієрархічної вимірювань. [dim1:children()], наприклад, поверне по одній клітинці для кожного елемента під поточним елементом у вимірі dim1. Таким чином можна агрегувати дані.

Як буде виглядати наш приклад в Egeria?
У нас вийде приблизно ось це:



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



Це додаток можна створити за 20 хвилин (включаючи введення даних), виконавши наступні кроки:
  1. Відкриваємо редактор метаданих і створюємо наступні вимірювання (в дужках зазначено код вимірювання, що використовується у формулах):
    • Day (day) — День продажу з елементами від 1 до 31.
    • Month (mon) — Місяць продажу з елементами від 1 до 12.

    • Data type (dty) — Тип даних з елементами: заплановані (Planned) і дійсні (Actual)
    • Position (pos) — Позиція звіту/форми вводу з елементами: кількість проданих пляшок лимонаду (Cups sold), виручка (Sales), відсоток виконання плану (Plan fulfillment) і ціна за склянку (Cup price).
    • Business unit (bu) — Наші співробітники, згруповані по вулицях, на яких вони працюють.
    • Product (prod) — Продукти, які продають наші співробітники.
  2. Створюємо наступні worksheets (робочі листи або форми вводу):
    • Prices: Введення цін по вулицях.
    • Sales: Введення проданих стаканчиків і підрахунок виручки.

    • Sales by month: Агрегація даних по місяцях.
  3. Додаємо формули.
    Формула для підрахунку продажів, наприклад, виглядає так: [day:*,dty:*,mon:*,pos:P04,bu:parent()] * [pos:P01] pos:P01 це код елемента вимірювання «позиція звіту» який називається «кількість проданих стаканчиків» (Cups sold), pos: Р04 — «ціна склянки». Оскільки ціна склянки залежить тільки від вулиці і продукту, ми зберігаємо її на нульовому (*) в елементі вимірах день (day), тип даних (dty), і місяць (mon).
    Вираз bu:parent() виходить з рівня ієрархії «продавець» (вимірювання business unit) на рівень ієрархії «вулиця».
Докладне відео з покроковим створенням цієї системи можна знайти в розділі скринкасты на сайті системи.

Хочете дізнатися більше?
На сайті http://egeria.rocks ви знайдете:
  • документацію і скринкасты до проекту (на жаль, поки тільки англійською)
  • безкоштовну попередню версію системи
  • пару прикладів додатків, створених за допомогою Egeria
Крім наведеного тут прикладу, там є:
  • система для планування продажів, яка автоматично прораховує доходи компанії з продажів і витрат, виробляючи при цьому конвертацію валют
  • система для планування інвестицій на прикладі парку розваг
  • система для оцінки нерухомості
  • система обліку часу за проектами з можливістю вводити відпустки
і це тільки невелика частина того, для чого можна використовувати систему Egeria.

Тих-дані
Для тих, кому цікаво, як це все працює:

Front-end

У зв'язку з недавнім зростанням популярності і можливостей веб додатків вибір припав на JavaScript/HTML5 (Single Page Application) в якості клієнта. У цьому випадку роль сервера обмежується наданням REST API.
Клієнт використовує AngularJS, який імплементує MVVM pattern. Наявність великої кількості безкоштовних компонентів для цієї платформи і її продумана архітектура значно спростили розробку клієнта.

Back-end

Сервер написаний на C#. Як серверного фреймворку використовується Nancy FX.
ASP.NET здався мені занадто важким для того, щоб просто створити REST API. До того ж простота Nancy і використання шаблону «фасад» (facade pattern) дозволять при необхідності замінити web framework протягом одного або двох днів.
Хотілося так, щоб користувач міг просто завантажити і запустити додаток, отримавши, таким чином, локальний веб-сервер. Це відмінно підтримує self-host сценарій використання Nancy.
Для парсингу формул використаний Sprache Framework, який дозволяє створити парсер за кілька годин. Після парсинга з синтаксичного дерева генера Expression Trees, які .NET runtime перетворює в нативний код.

База даних

Оскільки під капотом візуальний документ сериализируется в JSON документ, Egeria може зберігати дані в JSON базі даних. В даний момент існує адаптер для MongoDB. Для зберігання даних в локальних файлах використовується самописное copy-on-write сховище «ключ-значення» (key-value-store) і серіалізація даних за допомогою protobuf-net. На жаль, я не зміг знайти добре підтримується сховище «ключ-значення», написана повністю на керованому коді (managed code). Для роботи з JSON об'єктами використовується JSON.NET.

Дані куба зберігаються в стислому двійковому представленні (приблизно 30 байт на точку з точністю .NET-івського decimal), що дозволяє економити пам'ять і швидко переміщувати дані з key-value-store в пам'ять і назад. Система побудована за принципом in-memory processing і виходить з того, що всі дані помістяться в пам'ять. Куб завантажується асинхронно і система доступна майже відразу після старту.

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

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.