Создание динамических таблиц в SuperSet
Однажды в студеную зимнюю пору из-за морей из-за океанов прилетело известие: лицензии не выдаем, тех. поддержку не оказываем, а можем и вовсе отключить все системы. В компании погоревали, но делать нечего, и решили переходить на новые системы, да такие, чтобы не смогли в любой момент их превратить в тыкву. И стали смотреть в сторону open-sourсe решений. Проанализировали несколько BI-систем и остановились на SuperSet. /*После QlikSense и Tableau казалось, что это так себе затея*/
Меня зовут Корнева Настя, я отвечаю за разработку BI-отчетности в Magnit Tech в рамках проекта Аналитика УЦП (управление цепочками поставок). В этой статье поделюсь нашим опытом создания динамических сводных таблиц в BI-инструменте SuperSet, что будет полезно разработчикам отчетности и всем, кто интересуется данной тематикой.
Отрисовать линейный график или построить бар-чарт — с этим сложностей не возникло. Но наши заказчики привыкли работать с Excel- таблицами, построенными на OLAP-кубах. Это удобно — пересобрал таблицу в нужный вид, вытащил необходимые поля, выставил фильтры и готово!
Как же обеспечить подобный функционал в SuperSet? Как сделать таблицы не с жестко установленными полями, а дать пользователям возможность самостоятельно выбирать метрики и разрезы?
В документации и в множестве статей решения подобной задачи мы не нашли и пришлось самостоятельно искать пути реализации. На помощь нам пришла jinja — шаблонизатор для языка программирования Python.
Теперь немного подробностей, которые будут интересны разработчикам отчетности: в качестве быстрого слоя данных мы используем аналитическую базу данных ClickHouse, которая в связке с SuperSet обеспечивает высокую скорость отработки запросов.
Итак, сказ о том, как создать динамическую таблицу в SuperSet.
Сначала создадим перечень измерений, которые пользователи будут помещать в строки или столбцы, а также перечень метрик.
Для этого создадим 2 датасета, один с измерениями и второй с метриками.
1)
SELECT 'День' as Dimension, 'День' as Dimension1 UNION ALL
SELECT 'Неделя' as Dimension, 'Неделя' as Dimension1 UNION ALL
SELECT 'Группа 20' as Dimension, 'Группа 20' as Dimension1 UNION ALL
SELECT 'Формат' as Dimension, 'Формат' as Dimension1 2)
SELECT 'Остаток, шт' as Measure UNION ALL
SELECT 'Продажи, шт' as Measure UNION ALL
SELECT 'Потери, %' as MeasureСоздадим дашборд и добавим на панель фильтров созданные измерения и метрики.
Метрики делаем обязательными к выборке
Теперь приступим к написанию динамического запроса, для этого создадим несколько переменных.
Так как пользователь на дашборде будет выбирать, например, «День», а в базе данных это поле заведено как DAY_ID, то создадим словарь через функцию {% set %}, где каждому полю будет присвоено соответствующее значение из базы данных:
{% set dimension_value = {
'День': "DAY_ID",
'Неделя': "dictGet('ucp_dims.dict_days','WEEK_ID',DAY_ID)",
'Группа 20': "dictGet('ucp_dims.dict_art','ART_GRP_LVL_0_NAME',ART_ID)",
'Формат': "dictGet('ucp_dims.dict_whs','FRMT',WHS_ID)"
}
%}Создадим словарь, где будут содержаться только переменные календаря:
{% set day_table = {
'День': "DAY_ID",
'Неделя': "WEEK_ID"
}
%}Создадим переменные, которые будут содержать значения фильтров Dimension, Dimension1 и Measure, выбранных пользователем:
{% set FV_Measure = filter_values('Measure') %}
{% set FV_Dimension = filter_values('Dimension') %}
{% set FV_Dimension1 = filter_values('Dimension1') %}Используя функции циклов {% for %} и условий {% if %}, напишем динамический запрос, который будет выводить только те поля и метрики, которые выбрал пользователь на дашборде:
SELECT /*выводим поля, выбранные пользователей из фильтра Столбцы */
{% for value in FV_Dimension %}
{{ dimension_value.get(value) }} as "{{ value }}",
{% endfor %}
/* выводим поля, выбранные пользователей из фильтра Строки */
{% for value in FV_Dimension1 %}
{{ dimension_value.get(value) }} as "{{ value }}",
{% endfor %}
/* выводим метрики, выбранные пользователей из фильтра Метрики */
{% for value in FV_Measure %}
{% if value == 'Продажи, шт' %}
Sum(SALE_QNTY) AS SALE_QNTY
{% if not loop.last %}, {% endif %}
{% elif value == 'Остаток, шт' %}
Sum(REST_QNTY) AS REST_QNTY
{% if not loop.last %}, {% endif %}
{% elif value == 'Потери, %' %}
Sum(LOST_QNTY) AS LOST_QNTY,
Sum(SALE_QNTY) AS SALE_QNTY
{% if not loop.last %}, {% endif %}
{% else %}
NULL {% if not loop.last %}, {% endif %}
{% endif %}
{% endfor %}
FROM ucp_table
WHERE 1=1 /* фильтры по неделям, месяца и т.п. работали по партиции*/
{% for value in filter if value.col in day_table %}
{% if loop.last %}
AND DAY_ID IN (SELECT DAY_ID FROM ucp_dims.days WHERE 1=1
{% for value in filter if value.col in day_table %}
AND {{ day_table.get(value.col) }} IN {{ filter_values(value.col)|where_in }}
{% endfor %})
{% endif %}
{% endfor %}
/* остальные фильтры*/
{% for value in filter if value.col not in day_table and value.col in dimension_value %}
AND {{ dimension_value.get(value.col) }} IN {{ filter_values(value.col)|where_in }}
{% endfor %}
GROUP BY /*группировка по полям , выбранных пользователей из фильтра Столбцы */
{% for value in FV_Dimension %}
"{{ value }}",
{% endfor %}
/*группировка по полям, выбранных пользователей из фильтра Столбцы */
{% for value in FV_Dimension1 %}
"{{ value }}",
{% endfor %}
'1' /*заглушка для запятой*/Для создания датасета в SQL Lab пишем простенький запрос к таблице days:
SELECT DAY_ID
FROM ucp_dims.days
WHERE DAY_ID = CURDATE()-2Затем редактируем сохраненный дата сет: на вкладке SOURCE снимаем блокировки изменений и подменяем запрос на динамический:
Теперь создадим чарт PivotTable. В Columns прописываем:
{% if filter_values('Dimension')[0] %}
"{{ filter_values('Dimension')[0] }}"
{% else %} '⠀'
{% endif %}
В зависимости от того, сколько колонок мы заведем, столько полей пользователи смогут вывести в таблицу. Для второго поля меняем значение индекса в функции filter_values:
И так далее.
Аналогично добавляем поля в Rows только для Dimension1, не забывая менять индекс:
Далее прописываем метрики:
{% if 'Продажи, шт' in filter_values('Measure') %}
Sum(SALE_QNTY)
{% else %}
NULL
{% endif %}Если пользователь не выбрал данную метрику, подставится значение NULL, которое в сводную таблицу не выводится.
Аналогично для остальных метрик. Сохраняем чарт и переходим в дашборд. Проверяем работу фильтров. С помощь CSS прописываем дополнительное форматирование и настраиваем различные красивости, чтобы таблица выглядела симпатично.
Ниже несколько примеров таблиц, которые можно получить.
Мы получили таблицу, в которой пользователей может самостоятельно выбирать измерения и метрики для анализа. Приятной работы!
Выводы
При более плотной работе с SuperSet мы поняли, что он не так уж прост, и в нем можно реализовать даже самые смелые идеи. В данной статье описаны основополагающие моменты, дополняя которые sky is the limit for you. Не бойтесь экспериментировать, SuperSet довольно гибкий инструмент, и в умелых руках станет мощным аналитическим решением.
Если ты тоже фанатеешь по SuperSet и хочешь его использовать в ежедневной работе — мы ищем коллег BI-разработчиков в команду Magnit Tech. Ссылка на карьерную страницу есть в профиле компании, откликайтесь ;)
