Планы и факты: работаем с денормализованной таблицей17.01.2025 18:45

Привет, Хабр! В этой статье я хотел бы поговорить про особенности план-факт анализа, а также о работе с денормализованной таблицей, которая «была, есть и будет использоваться», потому что оказывается удобной для некоторых приемов работы с BI. Под катом вы найдете 7 примеров решения типовых задач план-факт анализа, включая расчет долей, отображение данных с учетом иерархии, разбивку по регионам и так далее. Всех, кому интересны эти практические аспекты, жду под катом :)

Типичной задачей является сравнение плана и факта, и сегодня я разберу несколько примеров, как сделать это на базе денормализованной таблицы фактов — то есть когда в одной таблице хранятся и фактические значения с детализацией по дням, и фактические значения за месяц. Это очень интересная сущность, ведь в реальной жизни расхождения между суммами фактических данных по дням и фактическими данными за месяц могут быть ощутимыми…а еще там же могут лежать плановые значения за месяц. Но на базе BI-платформы можно красиво свести все эти данные воедино для дальнейшего анализа.

Обычно в таком случае (то есть в такой денормализованной таблице фактов) создаются отдельные колонки (план, факт по месяцу, факт по заказу). Работать с ними немного сложнее, чем с «нормальными» таблицами, но, к счастью, уже есть наработанные практики успешного разгребания подобного бардака.

В качестве примера рассмотрим данные о продажах. На схеме приведена модель данных из Visiology.

90d4a1565a5451ea089efec7c0f2b6ed.png

Данные плана и факта хранятся в денормализованной таблице sales следующего вида:

25faccc126dcae6db12be667a3eace84.png

1. Отображение данных фактических и плановых продаж по месяцам в одной таблице

Если мы хотим провести план-факт анализ, для совмещения плановых и фактических значений в одной UI-таблице нужно подготовить денормализованную таблицу фактов, а именно — заполнить в ней столбец, по которому будет строиться UI-таблица. В нашем случае, это месяц.

За счет того, что план и факт по месяцам хранятся в разных столбцах денормализованной таблицы фактов, для проведения анализа будет достаточно суммирования — даже не потребуется создавать новую меру.

При этом платформа Visiology генерирует следующий DAX: 

EVALUATE
TOPN (
    3000,
    SUMMARIZECOLUMNS (
        'sales'[month],
        "fact", SUM ( 'sales'[fact] ),
        "plan", SUM ( 'sales'[plan] )
    ),
    'sales'[month], ASC,
    [fact], ASC,
    [plan], ASC
)

ORDER BY
    'sales'[month] ASC,
    [fact] ASC,
    [plan] ASC

В результате объединенная таблица выглядит так:

fe62624b38357b9dbf1f17cdeb993126.png

2. Отображение вместе плана, факта по месяцам и факта по дням (заказам)

Часто суммы факта по дням и за месяц расходятся, и если всё хранится в одной денормализованной таблице, можно очень просто отобразить данные вместе. Для этого достаточно добавить orderamount из отдельного столбца и провести суммирование по нему. Нужно только проследить, чтобы в денормализованной таблице при заполнении всех трех столбцов также был заполнен столбец month, чтобы вывести все результаты в одной таблице.

0fa0aa50ddd5e31bee27e9bca05dc7b9.png

Платформа Visiology генерирует следующий DAX: 

EVALUATE
TOPN (
    3000,
    SUMMARIZECOLUMNS (
        'sales'[month],
        "fact", SUM ( 'sales'[fact] ),
        "orderamount", SUM ( 'sales'[orderamount] ),
        "plan", SUM ( 'sales'[plan] )
    ),
    'sales'[month], ASC,
    [fact], ASC,
    [orderamount], ASC,
    [plan], ASC
)
ORDER BY
    'sales'[month] ASC,
    [fact] ASC,
    [orderamount] ASC,
    [plan] ASC

3. Отображение плана и факта с учетом иерархии компании

Часто от BI требуется анализ в рамках иерархии компании. Чтобы решить эту задачу, мы просто меняем тип таблицы на сводную, добавляем в нее regionname и cityname из справочников для отображения данных по регионам и городам.

Для сводной таблицы по мере детализации генерируется несколько DAX, например: 

EVALUATE
TOPN (
    3000,
    SUMMARIZECOLUMNS (
        'city'[cityname],
        FILTER ( 'region', 'region'[regionname] IN { "Центр" } ),
        "fact", SUM ( 'sales'[fact] ),
        "orderamount", SUM ( 'sales'[orderamount] ),
        "plan", SUM ( 'sales'[plan] )
    ),
    'city'[cityname], ASC,
    [fact], ASC,
    [orderamount], ASC,
    [plan], ASC
)
ORDER BY
    'city'[cityname] ASC,
    [fact] ASC,
    [orderamount] ASC,
    [plan] ASC

Итог выглядит следующим образом:

d19dae41b9540704f9861df1ad73f2ef.png

4. Расчет долей месяца в году

От аналитиков нередко требуют показать долю текущего месяца в продажах за выбранный период — то есть обычно за год. Для того чтобы проще решить эту задачу добавим фильтр с month из sales (не из calendar) и выберем в нем 12 месяцев за нужный год. Таким образом, за счет фильтра по sales[month] мы работаем с данными за актуальный период — за год:

249e300b68af1126151786fb4a7b4551.png

Используем следующую меру: 

Доля месяца в продажах за период =
SUM ( sales[orderamount] )
    / CALCULATE ( SUM ( sales[orderamount] ), REMOVEFILTERS ( calendar ) )

Добавим меру на дашборд, и в результате получим:

e076fe56c2c15c8c707b16a8021f47a9.png

Visiology генерирует следующий DAX: 

EVALUATE
TOPN (
    3000,
    SUMMARIZECOLUMNS (
        'calendar'[month],
        FILTER (
            'sales',
            'sales'[month]
                IN {
                    "202401",
                    "202402",
                    "202403",
                    "202404",
                    "202405",
                    "202406",
                    "202407",
                    "202408",
                    "202409",
                    "202410",
                    "202411",
                    "202412"
                }
        ),
        "orderamount", SUM ( 'sales'[orderamount] ),
        "Доля месяца в продажах за период", 'sales'[Доля месяца в продажах за период]
    ),
    'calendar'[month], ASC,
    [orderamount], ASC,
    [Доля месяца в продажах за период], ASC
)
ORDER BY
    'calendar'[month] ASC,
    [orderamount] ASC,
    [Доля месяца в продажах за период] ASC

Стоит отметить, что в качестве примера использовано форматирование с округлением до трех знаков после запятой: 

!(typeof @value === 'string') && @value ? 
@value.toFixed(3) : @value

d3284584a09f482d096246853982de2e.png

При необходимости можно использовать собственную логику форматирования, что описано в документации Visiology.

5. Отображение долей по регионам и городам

По аналогии с долей месяца можно рассчитать долю города и региона в продажах, это уже в некотором смысле аналог коэффициента проникновения по регионам или городам.

Для решения задачи используем подход, аналогичный примененному для календаря и месяцев. Чтобы нам было удобнее, учитываем, что в одном CALCULATE можно использовать несколько REMOVEFILTERS.

Создадим  аналогичную меру: 

Доля региона в продажах за период =
SUM ( sales[orderamount] )
    / CALCULATE (
        SUM ( sales[orderamount] ),
        REMOVEFILTERS ( region ),
        REMOVEFILTERS ( city )
    )

Новые фильтры по регионам и городам из sales можно и не создавать, в итоге получим следующий дашборд:

6abe8867c0981e70bdbb32943d43b51a.png

Платформа генерирует следующий DAX: 

EVALUATE
TOPN (
    3000,
    SUMMARIZECOLUMNS (
        'region'[regionname],
        "orderamount", SUM ( 'sales'[orderamount] ),
        "Доля города в продажах за период", 'sales'[Доля города в продажах за период]
    ),
    'region'[regionname], ASC,
    [orderamount], ASC,
    [Доля города в продажах за период], ASC
)
ORDER BY
    'region'[regionname] ASC,
    [orderamount] ASC,
    [Доля города в продажах за период] ASC

Возможность дважды использовать REMOVEFILTERS позволяет добиться необходимой детализации по городам. Также можно и использовать больше REMOVEFILTERS, например, три REMOVEFILTERS для расчета долей по городам, регионам и месяцам. Возможности здесь практически безграничны. :)

6. Отображение долей по регионам, городам и месяцам

Можно объединить расчет долей по иерархии компании (регионам и городам) и месяцам, чтобы продемонстрировать возможности нескольких REMOVEFILTERS для нескольких таблиц.

Чтобы оценить это на примере, добавим фильтр для 2024 года (точнее, для 12 месяцев из sales[month]), и создадим меру: 

Доля города в продажах за период по месяцам =
SUM ( sales[orderamount] )
    / CALCULATE (
        SUM ( sales[orderamount] ),
        REMOVEFILTERS ( region ),
        REMOVEFILTERS ( city ),
        REMOVEFILTERS ( calendar )
    )

Получим следующий дашборд.

2976ff2e55acdd82600fa12534145d86.png

Для этого дашборда сводная таблица генерирует несколько DAX запросов в зависимости от уровня детализации, например: 

EVALUATE
TOPN (
    3000,
    SUMMARIZECOLUMNS (
        'city'[cityname],
        FILTER ( 'region', 'region'[regionname] IN { "Восток" } ),
        FILTER (
            'sales',
            'sales'[month]
                IN {
                    "202401",
                    "202402",
                    "202403",
                    "202404",
                    "202405",
                    "202406",
                    "202407",
                    "202408",
                    "202409",
                    "202410",
                    "202411",
                    "202412"
                }
        ),
        "orderamount", SUM ( 'sales'[orderamount] ),
        "Доля города в продажах за период по месяцам", 'sales'[Доля города в продажах за период по месяцам]
    ),
    'city'[cityname], ASC,
    [orderamount], ASC,
    [Доля города в продажах за период по месяцам], ASC
)
ORDER BY
    'city'[cityname] ASC,
    [orderamount] ASC,
    [Доля города в продажах за период по месяцам] ASC

7. Все в одном! Анализируем план, факт по заказам, суммарный факт, долю в продажах — по иерархии компании и месяцам

Наконец, рассмотрим результаты на одном дашборде. Отобразим вместе план, факт по заказам, суммарный факт и для примера созданную ранее меру для доли города в продажах по месяцам sales[Доля города в продажах за период по месяцам], в итоге получим дашборд следующего вида со сводной таблицей:

57520176d92b6880f7d552bc903f61a3.png

Как обычно, сводная таблица генерирует несколько DAX запросов в зависимости от уровня детализации, для примера один из таких DAX запросов: 

EVALUATE
TOPN (
    3000,
    SUMMARIZE (
        SUMMARIZECOLUMNS (
            'city'[cityname],
            'sales'[month],
            FILTER ( 'region', 'region'[regionname] IN { "Центр" } ),
            "orderamount", SUM ( 'sales'[orderamount] ),
            "plan", SUM ( 'sales'[plan] ),
            "fact", SUM ( 'sales'[fact] ),
            "Доля города в продажах за период по месяцам", 'sales'[Доля города в продажах за период по месяцам]
        ),
        'city'[cityname]
    ),
    'city'[cityname], ASC
)

Заключение

В этой статье мы убедились, что на денормализованной таблице можно сравнить план и факт, рассчитать доли и решить множество других задач.

При необходимости расчета доли по столбцу (например, month) удобно создать справочник с этим столбцом (например, calendar) и использовать REMOVEFILTERS для нового справочника (например, REMOVEFILTERS(calendar)) для расчета суммы по всем значениям справочника (т.е. по году).

Для фильтрации на дашборде (например, по месяцу) можно добавить на дашборд фильтр не по справочнику calendar[month], а по таблице фактов sales[month], тогда этот фильтр не будет отменен в REMOVEFILTERS через REMOVEFILTERS(calendar).

Успехов в построении дашбордов! :)

© Habrahabr.ru