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

На практике до дашборда было ещё далеко.

Между записью в рабочей системе и цифрой на экране находится целая цепочка решений. Какую дату считать отчётной? Что делать с изменившимся статусом? Какая запись является основной, если один объект встречается несколько раз? Как связать справочники, которые в разных системах ведутся по-разному? И главное - как потом объяснить конкретную цифру, если она не совпала с привычным отчётом?

В итоге в проекте появились BI-витрина, модель данных и отчётность, которая использовалась для анализа динамики и поиска расхождений. Но самым важным результатом для меня стал не сам дашборд. Я на практике увидел, что аналитическая витрина начинается не с SELECT и не с выбора схемы данных. Она начинается в тот момент, когда мы решаем, во что именно должна превратиться исходная запись.

Данные были, но готовой аналитики в них не было

Рабочая система хранит данные для выполнения процесса. Ей нужно создать запись, изменить статус, связать её с другими объектами и дать сотруднику продолжить работу. Аналитический отчёт решает другую задачу: собирает множество таких записей в сопоставимую картину за период.

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

Есть ещё одна проблема, которую легко пропустить. Поле в базе отвечает на вопрос, что записано в системе сейчас. Отчёт часто должен ответить, что происходило в выбранном периоде. Это не одно и то же.

Например, у операции может быть дата создания, дата выполнения, дата оплаты и дата последнего изменения. Если взять первую попавшуюся, запрос отработает без ошибки и даже построит красивую динамику. Только эта динамика будет отвечать не на тот вопрос. Технически расчёт корректен, а по смыслу нет.

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

Это довольно скучная часть работы. Её сложно показать на демонстрации, зато именно здесь чаще всего решается, будет ли отчёт заслуживать доверия.

Между источником и витриной оказалось несколько разных задач

Слово ETL создаёт впечатление, что данные просто извлекаются, немного преобразуются и загружаются в нужную таблицу. Формально так и есть. Но под словом «преобразуются» обычно скрывается почти вся аналитическая логика.

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

Первый слой нужен для сохранения исходного состояния. Здесь данные ещё не пытаются сделать удобными для отчёта. Я стараюсь не переименовывать спорные значения в более понятные и не исправлять их «по смыслу». Если в источнике пришёл неизвестный код или пустое поле, это нужно сохранить как факт, а не незаметно заменить догадкой аналитика.

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

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

Допустим, два источника используют разные обозначения одного подразделения. Можно быстро соединить их по названию, но такое решение сломается после первого переименования, опечатки или сокращения. Значит, нужен устойчивый ключ или отдельная таблица соответствий. При этом неизвестное значение лучше вынести в отдельную категорию и показать как проблему качества, чем просто потерять строку при соединении.

Отдельно пришлось следить за детализацией. Если в одной таблице одна строка соответствует операции, а в другой - нескольким связанным начислениям или изменениям, обычный JOIN размножит сумму. Итог может выглядеть правдоподобно, особенно на большом объёме, поэтому я не доверяю объединению только потому, что запрос успешно выполнился.

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

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

Почему я не оставил всю логику внутри Power BI

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

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

Я старался выносить в подготовку данных всё, что не должно меняться от одного экрана к другому. Если правило одинаково для нескольких показателей, ему лучше находиться до BI. Тогда отчёт получает уже согласованные сущности, а не собирает их заново для каждой визуализации.

Это особенно важно, когда появляются второй отчёт или ещё один аналитик. Пока вся логика находится в одном файле Power BI, расхождений может быть не видно. Потом файл копируют, один фильтр меняется, второй забывают перенести, и через несколько месяцев два отчёта с одинаковым названием показателя показывают разные значения.

При этом я не пытался заранее собрать одну огромную витрину на все будущие случаи. Такая таблица быстро обрастает полями, которые никто не понимает, и сложными соединениями на всякий случай. Общую логику лучше держать в подготовленном слое, а витрину делать под понятную аналитическую задачу.

Для отчёта по динамике доходов важна одна структура данных, для анализа действий пользователей мобильного приложения - другая. У них могут быть общие справочники и правила качества, но единица наблюдения, временная логика и набор событий будут разными. Само слово «витрина» не означает, что все данные нужно сложить в одну таблицу.

Проверка витрины началась до первой диаграммы

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

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

Я сверял общие итоги с контрольными данными, отдельно разбирал несколько записей вручную и проверял граничные периоды. Ноль и отсутствие данных тоже рассматривались отдельно. Ноль означает, что расчёт выполнен и дал нулевой результат. Отсутствие может говорить о задержке загрузки, потерянной связи или записи, которая не прошла правило отбора.

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

  • сколько записей прочитано из источника и сколько дошло до следующего слоя;

  • появились ли дубли по ожидаемому ключу;

  • остались ли неизвестные значения справочников;

  • до какой даты источник фактически обновлён;

  • совпадает ли итог витрины с контрольным расчётом за закрытый период;

  • можно ли от строки витрины вернуться к исходной записи.

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

После запуска пришлось заниматься и производительностью ETL, и нагрузкой на хранилище. Здесь нет одного универсального решения: всё зависит от объёма, инфраструктуры и того, как меняются записи в источнике. В моём случае важным было не заставлять BI каждый раз повторять тяжёлую подготовку данных. Сложные и общие преобразования выполнялись раньше, а отчёт работал уже с подготовленной моделью.

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

Что я добавил бы в такой контур сейчас

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

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

Второе дополнение - технические метрики самого конвейера. Обычно мы хорошо считаем показатели бизнеса и намного хуже следим за тем, как они были получены. Для каждого запуска полезно сохранять время начала и завершения, количество прочитанных и записанных строк, максимальную дату обновления источника и результаты проверок. Тогда можно отличить реальное падение показателя от неполной загрузки.

Третье - lineage, то есть прослеживаемость происхождения данных. В небольшом проекте её можно поддерживать на уровне схемы и документации. Когда источников и преобразований становится много, связи уже имеет смысл собирать автоматически. Например, открытый стандарт OpenLineage описывает наборы данных, задания обработки и их запуски, чтобы фиксировать, какие входы участвовали в формировании результата. В этом проекте я OpenLineage не использовал, поэтому не выдаю его за часть реализованного решения. Но сама идея автоматически видеть путь от источника до витрины хорошо закрывает проблему, с которой я столкнулся при разборе расхождений.

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

Что у нас в итоге?

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

Источник остаётся владельцем операционного факта. Подготовленный слой приводит разные записи к общей модели. Витрина отдаёт BI-инструменту данные в той форме, которая соответствует конкретной аналитической задаче. А проверки связывают итоговую цифру с тем, что действительно находилось в системе.

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

Именно это обычно и происходит между исходной таблицей и аккуратной аналитической витриной. Просто большая часть этой работы остаётся за экраном.