Час звітності в Excel стрімко витрачається - тренд на зручні інструменти подання та аналізу інформації видно у всіх сферах. Ми давно обговорювали всередині цифровізацію побудови звітності та вибрали систему візуалізації та self-service аналітики Tableau. Олександр Безуглий, керівник відділу аналітичних рішень та звітності Групи «М.Відео-Ельдорадо», розповів про досвід та результати побудови бойового дашборду.
Скажу одразу, не все, що було задумано, вдалося реалізувати, але досвід був цікавий, сподіваюся, він буде корисним і вам. А якщо у когось виникнуть ідеї, як можна було зробити краще – буду дуже вдячний за поради та ідеї.
Під катом про те, з чим ми зіткнулися та про що дізналися.
З чого ми починали
У «М.Відео-Ельдорадо» є добре опрацьована модель даних: структурована інформація з необхідною глибиною зберігання та величезна кількість звітів фіксованої форми (див. докладніше ). З них аналітики роблять або зведені таблиці або форматовані розсилки в Excel, або красиві презентації PowerPoint для кінцевих користувачів.
Приблизно два роки тому замість звітів фіксованої форми ми почали створювати аналітичні звіти в SAP Analysis (надбудова на Excel, по суті зведена таблиця над OLAP движком). Але цей інструмент не зміг закрити потреб усіх користувачів, більшість так і продовжили використовувати інформацію, додатково оброблену аналітиками.
Наші кінцеві користувачі поділяються на три категорії:
Топ-менеджмент. Запитує інформацію у добре представленому та наочно зрозумілому вигляді.
Middle-менеджмент, Розвинені користувачі. Зацікавлені у дослідженні даних та здатні самостійно будувати звіти за наявності інструментів. Саме вони стали ключовими користувачами аналітичних звітів у SAP Analysis.
Масові користувачі. Чи не зацікавлені в самостійному аналізі даних, використовують звіти з обмеженим ступенем свободи, у форматі розсилок та зведених таблиць в Excel.
Наша ідея полягала в тому, щоб покрити потреби всіх користувачів та дати їм єдиний зручний інструмент. Почати вирішили із топ-менеджменту. Їм були потрібні зручні панелі для аналізу основних бізнес-результатів. Так, ми стартували з Tableau і для початку вибрали два напрямки: показники продажів у роздробі та онлайн з обмеженою глибиною та широтою аналізу, які покрили б приблизно 80% даних, які запитують топ-менеджмент.
Оскільки користувачами дашбордів був топ-менеджмент, з'явився ще один додатковий продукт KPI – швидкість відгуку. Ніхто не чекатиме 20-30 секунд, поки оновляться дані. Навігація мала укладатися в 4-5 секунд, а краще відпрацьовувати миттєво. І цього нам досягти, на жаль, не вдалося.
Ось так виглядав макет нашого основного дашборду:
Ключова ідея – об'єднати основні KPI-драйвери, яких у результаті набралося 19, ліворуч і представляти їхню динаміку та розбивку за основними атрибутами праворуч. Завдання здається нескладним, візуалізація логічною і зрозумілою, поки не занурюєшся в деталі.
Деталь 1. Обсяг даних
Основна таблиця з продажу за рік займає у нас близько 300 млн. рядків. Оскільки необхідно відобразити і динаміку до минулого і позаминулого року, обсяг даних лише за фактичними продажами близько 1 млрд. рядків. А ще окремо зберігається інформація за плановими даними та блок інтернет-продажів. Тому, навіть незважаючи на те, що ми використовували поколоночну in-memory DB SAP HANA, швидкість роботи запиту з вибором усіх показників за тиждень з поточних сховищ на льоту становила близько 15-20 секунд. Вирішення цього завдання напрошується само собою – додаткова матеріалізація даних. Але в ній також є підводні камені, про них нижче.
Деталь 2. Неадитивні показники
У нас багато KPI зав'язані на кількості чеків. І цей показник є COUNT DISTINCT кількості рядків (заголовків чеків) і показує різні суми залежно від вибраних атрибутів. Для прикладу, як цей показник та похідний від нього повинні вважатися:
Для коректності розрахунків можна:
- Проводити розрахунок подібних показників на льоту у сховищі;
- Проводити розрахунок по всьому обсягу даних у Tableau, тобто. за запитом у Tableau віддавати всі дані щодо вибраних фільтрів у гранулярності позиції чеків;
- Зробити матеріалізовану вітрину, в якій будуть розраховані всі показники у всіх варіантах вибірок, що дають різні неаддитивні результати.
Зрозуміло, що у прикладі УТЕ1 і УТЕ2 – це атрибути матеріалу, які мають товарну ієрархію. Не статична річ, крізь неї йде управління всередині компанії, т.к. за різні товарні групи відповідають різні менеджери. Ми мали багато глобальних переглядів цієї ієрархії, коли змінювалися всі рівні, коли переглядалися взаємозв'язки, і постійних точкових змін, коли одна група переходить із одного вузла до іншого. У звичайній звітності все це вважається на льоту з атрибутів матеріалу, у разі матеріалізації цих даних необхідно розробити механізм відстеження подібних змін та автоматичного навантаження історичних даних. Дуже нетривіальне завдання.
Деталь 3. Порівняння даних
Цей пункт схожий на попередній. Суть у цьому, що у компанії під час аналізу прийнято формувати кілька рівнів порівняння з минулим періодом:
Порівняння з минулим періодом (день до дня, тиждень до тижня, місяць до місяця)
У цьому порівнянні передбачається, що залежно від обраного користувачем періоду (наприклад 33 тиждень року) ми маємо показати динаміку до 32 тижня, якби ми вибрали дані протягом місяця, наприклад травень, це порівняння показало динаміку до квітня.
Порівняння з минулим роком
Тут основний нюанс полягає в тому, що при порівнянні по днях і тижнях ви берете не той самий день минулого року, тобто. ви не можете просто поставити цей рік мінус один. Ви повинні дивитися порівнюваний день тижня. А при порівнянні місяців, навпаки, треба брати рівно той самий календарний день минулого року. Також є нюанси із високосними роками. У вихідних сховищах вся інформація розподілена днями, там немає окремих полів із тижнями, місяцями, роками. Тому для отримання повного аналітичного зрізу в панелі, слід вважати не один період, наприклад тиждень, а 4 тижні, і потім ці дані ще порівняти, відобразити динаміку, відхилення. Відповідно, цю логіку формування порівняння в динаміці теж можна реалізовувати або в Tableau, або на боці вітрини. Так, і про ці деталі ми, звичайно, знали і думали ще на етапі проектування, але їх вплив на продуктивність кінцевого дашборду спрогнозувати було складно.
При впровадженні дашборду йшли ми довгим Agile-шляхом. Наше завдання було якнайшвидше надати на тестування робочий інструмент із потрібними даними. Тому ми йшли спринтами та відштовхувалися від мінімізації робіт на боці поточного сховища.
Частина 1. Віра в Tableau
Для спрощення ІТ-підтримки та швидкого впровадження змін ми вирішили робити логіку розрахунку неаддитивних показників та порівняння минулих періодів у Tableau.
Етап 1. Все в Live, ніяких доробок вітрин.
На цьому етапі ми підключили Tableau до поточних вітрин і вирішили подивитися, як буде рахуватися кількість чеків за один рік.
Результат:
Відповідь була гнітюча – 20 хвилин. Перегонка даних через мережу, високе навантаження на Tableau. Ми зрозуміли, що логіку з неаддитивними показниками слід реалізовувати на HANA. Це нас не сильно лякало, у нас вже був подібний досвід з BO та Analysis і будувати швидкі вітрини в HANA, які видають правильно пораховані неадитивні показники, ми вміли. Тепер залишалося підлаштувати їх під Tableau.
Етап 2. Тюним вітрини, ніякої матеріалізації, все на льоту.
Ми зробили окрему нову вітрину, яка на льоту видавала необхідні дані для TABLEAU. Загалом у нас вийшов добрий результат, ми скоротили час формування всіх показників за один тиждень до 9-10 секунд. І чесно очікували, що у Tableau час відгуку дашборду буде 20-30 секунд на першому відкритті і далі за рахунок кешу від 10 до 12, що загалом нас би влаштувало.
Результат:
Перші відкриті дашборди: 4-5 хвилин.
Будь-який клік: 3-4 хвилини
Такого додаткового збільшення до роботи вітрини ніхто не очікував.
Частина 2. Занурення у Tableau
Етап 1. Аналіз продуктивності Tableau та швидкий тюнінг
Ми почали аналізувати, на що Tableau витрачає основний час. І для цього є цілком добрий інструментарій, що, звичайно, плюс Tableau. Основною проблемою, яку ми виявили, стали дуже складні SQL-запити, які будував Tableau. Пов'язані вони були перш за все з:
- транспонування даних. Так як Tableau немає інструментів для транспонування dataset'ів, то для побудови лівої частини дашборду з детальним поданням всіх KPI нам довелося формувати таблицю через case. Розмір SQL-запитів БД досягав 120 000 символів.
- Вибором періоду часу. Такий запит на рівні БД займав більше часу на компіляцію, ніж на виконання:
Тобто. обробка запиту 12 секунд + 5 секунд виконання.
Ми вирішили спростити логіку обчислень на стороні Tableau та перенести ще одну частину обчислень на вітрину та рівень БД. Це дало добрі результати.
Спочатку ми зробили транспонування на льоту, зробили його через full outer join на фінальному етапі розрахунку VIEW, згідно з таким підходом, описаним на wiki и .
Тобто ми зробили таблицю налаштування - матрицю транспонування (21х21) і отримали всі показники в рядковій розбивці.
було:
стало:
На саме транспонування БД часу майже не витрачає. Запит за всіма показниками за тиждень відпрацьовував приблизно за 10 секунд. Зате втратилася гнучкість у частині побудови дашборда за конретному показнику, тобто. для правої частини дашборда де представлена динаміка і детальна розбивка конкретного показника раніше вітрина відпрацьовувала за 1-3 секунди, т.к. запит йшов за одним показником, а тепер БД завжди відбирала всі показники і фільтрувала результат перед тим як повернути результат у Tableau.
У результаті швидкість роботи дашборду скоротилася майже втричі.
Результат:
- 5 сек - парсинг дашборду, візуалізацій
- 15-20 сек - підготовка до компіляції запитів з виконанням розрахунків у Tableau
- 35-45 сек - компіляція SQL-запитів і паралельно-послідовне їх виконання в Hana
- 5 сек - обробка результатів, сортування, перерахунок візуалізацій у Tableau
- Звичайно, такі результати не влаштовували бізнес і ми продовжили оптимізацію.
Етап 2. Мінімум логіки у Tableau, повна матеріалізація
Ми розуміли, що побудувати дашборд з часом відгуку на кілька секунд на вітрині, яка працює 10 секунд неможливо, і розглядали варіанти матеріалізації даних на боці БД саме під дашборд. Але зіткнулися з глобальною проблемою, описаною вище – неадитивні показники. Ми не змогли зробити так, щоб при зміні фільтрів або розгорток Tableau гнучко перемикався між різними вітринами та рівнями, що розраховані для різних товарних ієрархій (у прикладі три запити без УТЕ, з УТЕ1 та УТЕ2 формують різні результати). Тому ми прийняли рішення спростити дашборд, відмовитися від товарної ієрархії в дашборді та подивитися, наскільки швидким він зможе бути у спрощеній версії.
Отже, на цьому останньому етапі ми зібрали окреме сховище, де склали в транспонованому вигляді всі KPI. За БД будь-який запит до такого сховища відпрацьовує за 0,1 – 0,3 секунди. У дашборді ми отримали такі результати:
Перше відкриття: 8-10 секунд
Будь-який клік: 6-7 секунд
Час, який витрачає Tableau, складається з:
- 0,3 сек. - парсинг дашборду та компіляція SQL-запитів
- 1,5-3 сек. - виконання SQL-запитів Hana для основних візуалізацій (запускається паралельно з п.1)
- 1,5-2 сек. - Рендеринг, перерахунок візуалізацій
- 1,3 сек. - Виконання додаткових SQL-запитів для отримання релевантних значень фільтрів (Бренд, Дивізіон, Місто, Магазин), парсинг результатів
Якщо підбивати короткий підсумок
Нам сподобався інструмент Tableau з погляду візуалізації. На етапі макетування ми розглядали різні елементи візуалізації та всі їх знайшли у бібліотеках, у тому числі складні багаторівневі сегментації та багатодрайверні waterfall.
Впроваджуючи дашборди з основними показниками продажів, ми зіштовхнулися зі складнощами у продуктивності, які поки що не змогли подолати. Ми витратили більше двох місяців та отримали функціонально неповний дашборд, швидкість відгуку якого на межі допустимого. І для себе зробили висновки:
- Tableau не вміє працювати з великими обсягами даних. Якщо у вихідній моделі даних у вас більше 10 Гб даних (приблизно 200 млн. х 50 рядків), то дашборд серйозно сповільнюється - від 10 секунд до кількох хвилин на кожен клік. Ми експериментували і при live-connect і при екстракті. Швидкість роботи можна порівняти.
- Обмеження при використанні кількох сховищ (датасетів). Немає можливості стандартними засобами вказувати взаємозв'язок датасетів. Якщо використовувати обхідні рішення для зв'язку датасетів, це сильно позначиться на продуктивності. У нашому випадку ми розглядали варіант матеріалізації даних у кожному потрібному розрізі уявлень і на цих матеріалізованих датасетах робити перемикання зі збереженням вибраних раніше фільтрів – це неможливо зробити в Tableau.
- У Tableau неможливо створити динамічні параметри. Ви не можете параметр, який використовується для фільтрації датасету в екстракті або при live-connecte, заповнювати результатом іншої вибірки з датасета або результату іншого SQL-запиту, лише нативне введення користувача або константа.
- Обмеження, пов'язані з побудовою дашборду з елементами OLAP | Зведеної таблиці.
У MSTR, SAP SAC, SAP Analysis, якщо до звіту додаєте датасет, всі об'єкти у ньому пов'язані друг з одним за умовчанням. У Tableau цього немає, зв'язок потрібно налаштовувати вручну. Це, напевно, гнучкіше, але для всіх наших дашбордів це обов'язкова вимога до елементів - тому це додаткові трудовитрати. Більше того, якщо ви робите пов'язані фільтри, щоб, наприклад, при фільтрації регіону список міст був обмежений тільки містами цього регіону, ви відразу потрапляєте на послідовні запити до БД або Екстракту, що помітно уповільнює дашборд. - Обмеження у функціях. Як над екстрактом, так і ТИМ БІЛЬШЕ над датасетом з Live-connecta не можна робити масові перетворення. Це можна робити через Tableau Prep, але це додаткові трудовитрати та ще один інструмент, який потрібно вивчати та підтримувати. Ви, наприклад, не можете транспонувати дані, зробити join їх самих із собою. Що закривається через перетворення на окремі стовпці або поля, які потрібно вибирати через case або if і це породжує, дуже складні SQL-запити, при яких база основний час витрачає на компіляцію тексту запиту. Ці негнучкості інструменту довелося вирішувати на рівні вітрини, що призводить до ускладнення сховища, додаткових завантажень і трансформацій.
Ми не поставили хрест на Tableau. Але як на інструмент, здатний будувати промислові дашборди та засіб, за допомогою якого можна замінити та діджіалізувати всю систему корпортативної звітності компанії, Tableau не розглядаємо.
Зараз активно розробляємо аналогічний дашборд на іншому інструменті та паралельно пробуємо переглянути архітектуру дашборду в Tableau, щоб ще більше спростити її. Якщо спільноті буде цікаво – розповімо про результати.
Також чекаємо ваших ідей чи порад, про те, як у Tabeau можна будувати швидкі дашборди над такими великими обсягами даних, адже у нас і сайт, де даних набагато більше ніж у роздробі.
Джерело: habr.com