Введение
Исходные данные часто требуют дополнительного преобразования под требования анализа. Рассмотрим типичную задачу, которая может встретиться при визуализации показателей.
Пусть у нас есть табличный виджет, в котором выведены показатели по продажам: выручка, прибыль и рентабельность. В AW BI в табличных виджетах расчетные агрегаты всегда выводятся в столбцах.
Задача, с которой могут столкнуться аналитики – это вывести те же самые показатели по строкам, т.е. необходимо транспонировать виджет и получить следующее отображение:
При большом количестве показателей или при сформированной культуре ведения отчетности в компании подобное отображение может быть предпочтительным. С помощью стандартных настроек виджета такое преобразование в AW BI не сделать.
Однако его можно выполнить на уровне модели данных, подготовив данные нужным образом. И для решения данной задачи мы применим одну из sql-функций для табличных преобразований.
Ход решения задачи
Чтобы разместить показатели в строках, нужно определить столбцы, которые отвечали бы за название показателя (например, “Выручка”, “Прибыль”) и его значение (числовое значение для каждого года).
Мы применим функцию STACK, которая преобразует данные выручки и прибыли из столбцов в строки. После такой подготовки в конструкторе виджета вы сможете вынести столбец «Показатель» в область строк и разместить поле «Год» в столбцы для сводной таблицы. В результате вы получите искомое представление, где показатели легко сравнить в разрезе лет. Этот подход также помогает чётко разделить логику расчётов.
Совет!
На уровне модели стоит рассчитывать агрегируемые показатели - те, которые можно посчитать заранее для каждой строки исходных данных. Например, Выручка, Прибыль, Количество.
На уровне виджета удобно рассчитывать относительные и производные показатели - те, которые требуют контекста всей таблицы или сравнения разных строк. Например, рентабельность, доли и проценты, а также разница между периодами.
Такое разделение делает модель более гибкой и производительной, а виджет - интерактивным и адаптивным под нужды анализа.
Практика
В AW BI поворот данных можно совершить с помощью ETL-блока - “SQL-блок”, где таблицу для транспонирования мы переносим drag-and-drop’ом в блок SQL, в настройках которого добавим код для поворота.
Вводные
У нас есть ежедневные данные продаж со столбцами выручки и прибыли,
а мы хотим повернуть данные так, чтобы показатели выручки, прибыли и рентабельности были рассчитаны в разрезе года.
Решение
В модели для переворота агрегатов в строки показатели должны быть представлены двумя столбцами: название показателя и значение показателя. Таким образом появится возможность вынести столбец с названием показателя в группы-строки и получить искомое представление. Разберем как это сделать.
Добавление SQL-блока
-
Найдем на схеме модели выпадающий список элементов “Добавить объект” и перейдем в объект “ETL-блок”
-
В реестре etl-объектов выберем SQL-блок и нажмем “Добавить”
- Перенесем таблицу “Заказы” внутрь SQL-блока
Выйдет ошибка, но не стоит её бояться, она появилась из-за отсутствия запроса в SQL-блоке. Запрос добавим в 5 пункте, продолжайте выполнять пункты дальше.
- На схеме модели у SQL-блока есть три точки, нажмем на них и перейдем в настроить параметры
- Откроется панель настройки SQL-блока, куда мы должны вписать код.
Дополнительно:
- Для работы с блоком используется Spark SQL со всеми доступными в нем функциями (Документация)
- Доступ ко вложенному объекту может выполнятся с помощью ключевого слова: child
SELECT * FROM child
- Если в блок вложены несколько несвязанных между собой объектов, то для доступа к соответствующим таблицам можно использовать их названия. Эти названия можно уточнить в подразделе “Структура”, нажав на иконку
.
Текущая структура состоит из следующих объектов:
- После каждого пункта в таблице с кодом далее можно нажать “Выполнить” для проверки результата
Код транспонирования на SQL
Перейдем к работе с кодом.
| Описание | Код | |
|---|---|---|
| 1 | Проверим вложенную таблицу | select * from jsql |
| 2 | Сгрупируем данные до года | SELECT zakaz_id, YEAR(data_zakaza) as year, produkt, kategoria,SUM(revenue) as revenue,SUM(profit) as profitFROM jsql GROUP BY zakaz_id, YEAR(data_zakaza), produkt, kategoria |
| 3 | Используем CTE конструкцию для определения группировки запроса выше и упрощения запроса в будущем | WITH cte AS (SELECT zakaz_id, YEAR(data_zakaza) as year, produkt, kategoria,SUM(revenue) as revenue,SUM(profit) as profit FROM jsql GROUP BY zakaz_id, YEAR(data_zakaza), produkt, kategoria)SELECT * from cte |
| 4 | Добавим функцию STACK для транспонирования предрассчитанных данных Рентабельность рассчитаем в виджете, поэтому сейчас только добавим строку с показателем | WITH cte AS (SELECT zakaz_id, YEAR(data_zakaza) as year, produkt, kategoria,SUM(revenue) as revenue,SUM(profit) as profit FROM jsql GROUP BY zakaz_id, YEAR(data_zakaza), produkt, kategoria) SELECT year, kategoria ,STACK(3 ,'Выручка', CAST(revenue AS FLOAT) ,'Прибыль', CAST(profit AS FLOAT) ,'Рентабельность', NULL) as (indicator, value)FROM cte |
Нажимаем “Сохранить” для результата четвертого пункта в SQL-блоке.
После данного действия у нас не будет воспроизводиться ошибка как при создании SQL-блока, а также будут видны данные в предпросмотре. Загрузим данные в хранилище.
Создание виджета
Перейдем в создание виджета из модели с загруженными в хранилище данными.
Последуем следующим пунктам для создания виджета:
- Тип виджета: Сводная таблица
- Строки: indicator
- Столбцы: year
- Агрегаты: value. В формулу промежуточного результата для данного агрегата добавим следующую функцию:
IF [indicator] = 'Рентабельность'
THEN CONCAT(REPLACE(STR(ROUND(SUM(SUM_IF([value], [indicator] = 'Прибыль') WITHIN [year]) / SUM(SUM_IF([value], [indicator] = 'Выручка') WITHIN [year]) * 100, 2)), '.', ','), '%')
ELSE CONCAT(REPLACE(STR(ROUND(SUM([value]) / 1000, 2)), '.', ','), ' тыс. ₽')
Таким образом, мы рассчитали рентабельность и сократили выручку и прибыль до удобного для просмотра варианта.
После выполнения описанных выше пунктов мы получили виджет, который решает задачу клиента:
Итог
В ходе данной статьи мы разобрали в теории и на практике один из возможных вариантов транспонирования в AW BI.
Этот приём часто используется для подготовки понятных и наглядных отчётов. Ниже еще несколько примеров его практического применения:
- Финансовые отчёты (P&L, Баланс предприятия)
Транспонирование помогает представить многоуровневую структуру доходов и расходов в компактном и читаемом виде, где статьи находятся в строках, а периоды — в столбцах.
Пример: Отчёт о прибылях и убытках (P&L).
| P&L отгрузки | Стоимость, руб. |
|---|---|
| Выручка от продажи (цена продажи по договору) | 640 000 |
| Итого доходы: | 640 000 |
| Себестоимость товара | 290 000 |
| Логистика (доставка, найм фуры) | 65 000 |
| Итого расходы: | 355 000 |
| Прогнозная прибыль: | 285 000 |
- Аналитические отчёты и панели мониторинга
Для анализа динамики ключевых показателей (KPI), таких как EBITDA, или расчёта финансовых коэффициентов (рентабельность, ликвидность) часто требуется именно «перевёрнутая» структура данных.
Пример: Анализ динамики ключевых метрик по месяцам.
| Метрика | Январь | Февраль | Март | Апрель | Май | Июнь |
|---|---|---|---|---|---|---|
| Пользователи, чел. | 1000 | 1200 | 1400 | 1600 | 1800 | 2000 |
| Регистрации, шт. | 150 | 180 | 200 | 220 | 250 | 300 |
| Активные юзеры, чел. | 500 | 600 | 700 | 800 | 900 | 1000 |
| Средний чек, руб. | 1500 | 1600 | 1700 | 1800 | 1900 | 2000 |
- Отчеты для руководства и инвесторов
Транспонирование позволяет в одном представлении сопоставить факт, план и отклонение по множеству показателей, что критически важно для принятия управленческих решений.
Пример: Сравнение плановых и фактических показателей в разрезе направлений бизнеса.
| Показатель | Продажи | Маркетинг | Производство | Финансы |
|---|---|---|---|---|
| План | 10 млн ₽ | 2 млн ₽ | 5 млн ₽ | 3 млн ₽ |
| Факт | 12 млн ₽ | 1,8 млн ₽ | 4,5 млн ₽ | 2,8 млн ₽ |
| Отклонение (+/-) | +2 млн ₽ | -0,2 млн ₽ | -0,5 млн ₽ | -0,2 млн ₽ |
| Процент отклонения (% отклон.) | +20% | -10% | -10% | -6,7% |
Таким образом, умение трансформировать данные с помощью транспонирования значительно расширяет возможности по созданию эффективных и визуально понятных отчетов.