На одном из внедрений аналитической платформы rapeed в крупном банке мы столкнулись с проблемой: данные по среднему времени обработки кредитной заявки у нас и в их исторической BI-системе существенно расходились. Несколько дней ушло на выверку данных. Итог оказался шокирующим: выяснилось, что их старая BI-система годами показывала топ-менеджменту неверные показатели эффективности!

Другой факт - очень крупная розничная сеть из сотен миллиардов строк своих чеков отдает пользователям в их BI-инструмент 5 миллионов строк или меньше, чтобы они на этих 5 миллионах строк смотрели свои сложные показатели. И пользователи даже не догадываются, что всё, кроме сумм и количеств, у них считается неправильно.

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

Давайте разберем механику этой проблемы на фундаментальном уровне. Почему системы, в которые инвестированы миллионы, показывают фейк?

Ошибка 1: Ошибочное усреднение средних

Возьмем классический неаддитивный показатель - средний чек в рознице (или то же среднее время обработки заявки в банке).

Очевидно, что формула такая:

[Средний чек] = [Сумма продаж] / [Количество уникальных чеков]

Если считать этот показатель по сырым данным, проблем не возникает. Но сырых данных в быстрой витрине нет (вспомним лимит в 5 млн строк). Инженеры данных заранее рассчитывают средние чеки по магазинам и месяцам, потому что так будет быстрее работать BI-инструмент.

И вот руководитель открывает дашборд и хочет посмотреть средний чек в целом по сети за первый квартал. Что делает SQL-движок классической BI-системы? Он берет три готовых месячных средних чека каждого магазина, складывает их и делит на три.

SELECT     region_name,    

EXTRACT(QUARTER FROM year_month) AS quarter,    

AVG(avg_receipt_amount) AS total_average_check 

FROM dwh.store_monthly_aggregated

WHERE EXTRACT(QUARTER FROM year_month) = 1

GROUP BY     region_name,    

EXTRACT(QUARTER FROM year_month);

Математика уровня шестого класса неумолима: A/B + C/D ≠ (A+B) / (C+D).

Система придает одинаковый вес гипермаркету с 10 000 чеков в день и киоску с 50 чеками. Месяц с пиковыми продажами уравнивается с «мертвым» месяцем. В результате реальные цифры отличаются на десятки процентов. 

Возьмём 19 реальных строк чеков за разные года, как на скриншоте снизу. Среди них встретились 2 чека с 2 строками каждый, остальные состоят из одной строки. Допустим, а хранилище попадают средние чеки в динамике по годам (нижняя сводная таблица). 

Если на этих данных посчитать средний чек по магазинам в целом (верхняя сводная таблица с верными значениями среднего чека), то отклонение от верного значения варьируется от -6% до +7%:

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

Хуже всего, когда знаменатели случайно совпадают - усредненное среднее магическим образом оказывается верным, ИТ-специалисты убеждают аналитиков в верности подхода, и эта бомба замедленного действия остается в DWH навсегда.

Ошибка 2: Ловушка подсчёта уникальных объектов (count distinct)

Вторая частая задача - подсчет уникальных объектов: покупателей, товаров, чеков. Уникальность сохраняется только до тех пор, пока мы не пересекаем границы объектов агрегации.

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

• Чек №101 | Бакалея

• Чек №101 | Напитки

• Чек №102 | Напитки

Реальное количество уникальных чеков - 2.

Теперь сожмем данные для витрины в разрезе категорий:

• Бакалея: 1 чек (№101)

• Напитки: 2 чека (№101, №102)

В современных BI-системах (например, Power BI или основанных на SQL) эта ловушка остаётся невидимой на уровне языка (DAX или SQL). Разработчик пишет стандартную меру:

Total Distinct Checks = SUM('Category_Sales_Summary'[Distinct_Checks_Count])

Пока вы смотрите на матрицу в разрезе категорий - всё абсолютно верно. Но как только бизнес-пользователь сворачивает иерархию, чтобы увидеть общий итог продаж, DAX применяет агрегацию суммы к текущему контексту фильтра. Итог системы: 1 + 2 = 3 уникальных чека. Данные задвоились просто от смены ракурса. Моментально «едет» средний чек и вся зависимая от него аналитика.

Ошибка 3: Бесконечные склады (полуаддитивные показатели)

Последний класс проблем, которые я затрону в этой статье - полуаддитивные показатели (snapshots). Это остатки на складах, балансы счетов и продуктов. Их можно складывать по магазинам, но категорически нельзя суммировать по оси времени.

Чтобы обойти эту проблему на предагрегированных данных, разработчикам приходится возводить громоздкие конструкции из кода, заставляя систему искать «последнее непустое значение» (LastNonEmpty). В многомерном кубе (MS OLAP / SSAS) это выглядит как сложный MDX-запрос:

CREATE MEMBER CURRENTCUBE.[Measures].[Остаток на конец периода] AS    NonEmpty

(        Descendants

(            [Date].[Calendar].CurrentMember,

             [Date].[Calendar].[Date]         ),

        [Measures].[Базовый остаток]    ).Item(0),

    VISIBLE = 1;

А в современных табличных моделях (DAX в Power BI и SSAS Tabular) пользователям нужно писать формулы с функцией LASTNONBLANK типа таких:

Остаток на конец периода = 

CALCULATE (SUM('Inventory'[Остаток]),

    LASTNONBLANK ('Calendar'[Date], 

        CALCULATE(SUM('Inventory'[Остаток]))

    ))

В чем проблема подобного кода? Во-первых, это ресурсоемко. Функция NonEmpty в MDX заставляет движок перебирать потомков по оси времени при каждом изменении контекста фильтров. На больших объемах это просто «вешает» куб.

Во-вторых, это не учитывает жизненных реалий. Если 30-го числа касса зависла (или пропал интернет) и не передала данные, а расчет идет на 31-е, классический BI покажет вам пустой склад. Система только что "потеряла" товар на сотни тысяч рублей, потому что жесткая связка кода и календаря не умеет гибко искать ближайшую актуальную дату.

В-третьих, создание DAX-кода требует, чтобы пользователь был разработчиком и разбирался в нюансах конструкций CALCULATE и строении календарей - при нескольких активных бизнес-календарях придётся кратно усложнить и так нечитаемый код.

Смена парадигмы: динамическое связывание данных

Мы видим, что попытки втиснуть сложную, многомерную реальность бизнеса в плоские, жестко агрегированные витрины приводят к архитектурному тупику. Вместо того чтобы заниматься поиском инсайтов, дата-инженеры тратят врем на написание «костылей» на DAX, MDX или SQL, чтобы заставить систему не врать.

Именно поэтому мы в платформе rapeed полностью отказались от жестких схем (вроде Unified Star Schema и её бридж-таблиц) и предагрегаций. Платформа работает иначе: она способна связывать данные прямо на лету, обращаясь к сырым детализированным массивам.

Хороший пример - расчет сложного показателя «Обеспеченность» (остатки товара относительно количества продаж), который строится на основе нескольких совершенно разных источников. В классическом BI вам пришлось бы сначала жестко связать миллиардный массив сырых остатков с хранилищем строк чеков на уровне DWH, создав очередную тяжелую витрину с ограничениями в уровнях аналитики.

В rapeed этот показатель собирается на лету прямо в интерфейсе через встроенную функцию MEASURE. Платформа комбинирует динамический расчет остатков на конец периода (из одного источника) с количеством продаж (из другого источника). Ядро системы само сводит эти метрики, пересчитывая обеспеченность товаром математически точно при любом клике на фильтр или смене группировки, без единой строчки кода. Система сама формирует временные коридоры и ищет актуальные остатки, поднимая данные в память только на момент вычисления.

Вместо заключения

Возвращаясь к истории с банком и розничной сетью из начала статьи: знаете, что происходит, когда бизнес получает в руки инструмент, способный мгновенно считать метрики по миллиардам сырых строк без искажающих витрин? Не могу сказать, что наступает прозрение - уж слишком долго люди верили усреднению средних, но они начинают задумываться. Увидев реальные цифры в процессе выдачи кредитов, бизнес-руководители банка смогли задать правильные вопросы и увидеть реальные проблемы. Бизнес розничной сети уже не хочет впихивать сложную аналитику в прокрустово ложе 5 миллионов строк, теряя математический смысл.

Качественная бизнес-аналитика должна быть точным цифровым отражением реального процесса, а не сжатой jpeg-фотографией, в которой потерялись детали и уже ничего не отредактировать, в отличие от RAW (сырого) оригинала. Развитие технологий даёт возможность пересмотреть фундаментальные подходы к тому, как мы анализируем данные.