Время отчётности в 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 Transpose — Wikipedia, the free encyclopedia и Elementary matrix — Wikipedia, the free encyclopedia.
То есть мы сделали настроечную таблицу – матрицу транспонирования (21х21) и получили все показатели в построчной разбивке.
Было:
Стало:
На само транспонирование БД времени почти не тратит. Запрос по всем показателям за неделю отрабатывал по прежнему примерно за 10 секунд. Но зато потерялась гибкость в части построения дашборда по конретному показателю, т.е. для правой части дашборда где представлена динамика и детальная разбивка конкретного показателя раньше витрина отрабатывала за 1-3 секунды, т.к. запрос шел по одному показателю, а теперь БД всегда отбирала все показатели и фильтровала результат уже перед тем как вернуть результат в Tableau.
В итоге скорость работы дашборда сократилась почти в 3 раза.
Результат:
- 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 можно строить быстрые дашборды над такими большими объемами данных, ведь у нас и сайт, где данных гораздо больше чем в рознице.
Автор: mvideo
