Выходя за рамки традиционного подхода: создание финансовой модели корпоративного уровня в Power BI.

Пошаговое руководство по преобразованию данных, моделированию звездообразной схемы и анализу вариативности DAX с извлеченными уроками.

Делиться

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

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

Я решил бросить себе вызов и сдать экзамен PL-300: Microsoft Data Analyst Associate. Но вместо того, чтобы просто проходить пробные тесты или заучивать определения, я перехожу в «практический режим». Если я хочу получить сертификат, я хочу доказать, что могу решать проблемы, с которыми действительно сталкиваются реальные компании.

Задача: Комплексное финансовое решение корпоративного уровня.

В качестве своего первого проекта я займусь разработкой пакета программ для оценки финансового состояния руководителей (Executive Financial Health Suite ).

Почему именно финансы? Потому что в корпоративном мире это высшее испытание ваших навыков моделирования данных и работы с DAX . В большинстве «общих» руководств используется одна плоская таблица. Но в реальной компании данные фрагментированы. У вас есть «Фактические данные» (что произошло) в одном месте, а «Бюджеты» (цель) — в другом, обычно на разных уровнях детализации.

В этом проекте я собираюсь задокументировать, как я:

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

Я делюсь своим опытом публично, чтобы, если вы тоже готовитесь к PL-300, вы могли следить за процессом, создавать эти решения вместе со мной и понимать, почему та или иная архитектура важна, а не только как она работает.
Для этого проекта мы используем пример финансовой отчетности Microsoft . Это идеальный «чистый лист», поскольку он представляет собой плоскую, «неупорядоченную» таблицу, которую нам предстоит профессионально переработать.

Как это получить: В Power BI Desktop перейдите в раздел «Главная» > «Пример набора данных» > «Загрузить пример данных» . Выберите таблицу «Финансы» .

Давайте попробуем поработать с Power Query на практике.

Этап 1: Преобразование данных (Power Query)

Прежде чем прикасаться к DAX или визуализации, я замедлял темп и уделял достаточно времени Power Query . Раньше я всегда торопился, а теперь рассматриваю это как основу для всего последующего.
Если модель данных ненадежна, никакие хитрые приемы DAX вас не спасут.

Шаг 1: Профилирование данных (быстрая проверка реальности)

После загрузки демонстрационного набора данных Microsoft Financial, первым делом я включил профилирование столбцов :

• Качество колонки
• Распределение столбцов
• Профиль столбца

Когда я включаю проверку качества, распределения и профиля столбцов , я не пытаюсь быть дотошным просто ради тщательности. Я сканирую данные на наличие проблем, которые могут привести к сбоям в работе модели, прежде чем они превратятся в головную боль, связанную с DAX.

Профилирование столбцов сразу же покажет вам:

• Где скрываются нули
• Какие столбцы выдают себя за размеры?
• Какие поля выглядят как числовые, но ведут себя как текстовые?

1. Нулевые значения и несоответствия типов данных

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

2. Кардинальность: что претендует на роль измерения?

Кардинальность — это просто количество уникальных значений в столбце . Power BI сразу же отображает это в разделе «Распределение столбцов» , и как только вы начнете обращать на это внимание, принятие решений в моделировании станет намного проще.

Вот моё эмпирическое правило:

• Низкая мощность множества (значения часто повторяются) → вероятно, это измерение.
• Высокая кардинальность (значения в основном уникальны) → детализация на уровне фактов

Когда я включаю распределение по столбцам, я задаю себе два вопроса:

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

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

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

Некоторые столбцы сразу же продемонстрировали низкую мощность — они часто повторялись и вели себя как настоящие категории:

• Сегмент
• Страна
• Продукт
• Дисконтный лейбл
• Производственная цена
• Цена продажи
• Атрибуты даты (Год, Номер месяца, Название месяца)

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

Затем были колонки на другом конце спектра.

Меры, подобные следующим:

• Продано единиц
• Валовая выручка
• Скидки
• Продажи
• Себестоимость проданных товаров
• Выгода

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

Это открытие напрямую повлияло на мой следующий шаг: использование функции «Ссылка» в Power Query для выделения полей Dim_Product и Dim_Geography, вместо того чтобы угадывать или навязывать структуру.

Шаг 2: Вращение размеров с указанием эталонных значений (без дубликатов)

На этом этапе я перестал рассматривать набор данных как готовую к составлению отчета таблицу и начал воспринимать его как модель, находящуюся в процессе разработки .
В Power Query возникает соблазн щелкнуть правой кнопкой мыши по таблице и выбрать «Дублировать» . Я раньше постоянно так делал. Это работает, но незаметно создает проблемы, которые становятся очевидными только позже.

Вместо этого я использовал ссылку .
Почему используется ссылка вместо дублирования? — можете спросить вы.

При создании таблицы, на которую есть ссылки:

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

В реальных условиях это просто… безопаснее.

Вот как я создал Dim_Product и Dim_Geography.

Начиная с основной финансовой таблицы:

• Я щёлкнул правой кнопкой мыши по запросу и выбрал пункт «Ссылка».
• Новый запрос переименован в Dim_Product.
• Оставил только столбцы, относящиеся к продукту (Продукт, Сегмент, Диапазон скидок).
• Удалены дубликаты.
• Обеспечена корректность типов данных и их именования.

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

Я применил тот же подход и к географии:

• Обратитесь к таблице фактов.
• Колонка «Сохранить страну»
• Удалить дубликаты
• Очистка текстовых значений

P.S. В этом наборе данных география представлена только на уровне страны. Вместо того чтобы навязывать несуществующую иерархию регионов или городов, я смоделировал страну как лаконичное измерение в одном столбце.

Шаг 3: Создание динамической таблицы дат

Именно здесь я часто вижу, как модели Power BI незаметно не соответствуют стандартам PL-300.

• Я не импортировал статический календарь.
• Я не создавал даты вручную.
• Я создал динамическую таблицу дат в Power Query на основе самих данных.

Почему это важно:

• Это гарантирует отсутствие пропущенных дат.
• Она автоматически корректируется при поступлении новых данных.
• Это идеально соответствует передовым методам моделирования Microsoft.

Чтобы создать динамическую таблицу дат, просто нажмите «Загрузить» -> «Пустой запрос» -> «Расширенный редактор» и вставьте этот код.

Ниже приведён точный код M, который я использовал.

let Source = Financials, MinDate = Date.From(List.Min(Source[Date])), MaxDate = Date.From(List.Max(Source[Date])), DateList = List.Dates( MinDate, Duration.Days(MaxDate — MinDate) + 1, #duration(1, 0, 0, 0) ), DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}), AddYear = Table.AddColumn(DateTable, “Year”, each Date.Year([Date]), Int64.Type), AddMonthNum = Table.AddColumn(AddYear, “Month Number”, each Date.Month([Date]), Int64.Type), AddMonthName = Table.AddColumn(AddMonthNum, “Month Name”, each Date.MonthName([Date]), type text), AddQuarter = Table.AddColumn(AddMonthName, “Quarter”, each “Q” & Number.ToText(Date.QuarterOfYear([Date])), type text), AddDay = Table.AddColumn(AddQuarter, “Day”, each Date.Day([Date]), Int64.Type) in AddDay

Этот календарь:

• Охватывает каждую дату в наборе данных.
• Масштабируется автоматически
• Готов к обработке данных в режиме реального времени с момента их поступления в модель.

После загрузки я отметил его как таблицу дат в представлении модели — это обязательное условие для PL-300.

К концу первого этапа у меня было:

• Удобная таблица фактов
• Правильно созданные таблицы размеров с помощью ссылки.
• Динамическая таблица дат без пропусков.
• Преобразования, которые я мог бы объяснить другому аналитику.

Пока ничего особенного, но именно этот этап делает все последующие этапы проще, быстрее и надежнее.

В следующем разделе я перейду к моделированию данных и связям , где эта структура действительно начнет приносить свои плоды.

Этап 2: Моделирование данных (от таблиц к звездообразной схеме)

На этом этапе Power BI начинает вести себя как семантическая модель .

К тому моменту, когда я переключился в режим просмотра модели , у меня уже было:

• Удобная таблица фактов
• Бережливые измерения, созданные с помощью эталонных данных.
• Динамическая таблица дат без пропусков.

Теперь цель была проста: соединить всё чётко и целенаправленно .

Шаг 1: Создание звездообразной схемы

Я стремился к классической звездообразной схеме :

• Единая центральная таблица фактов (финансовые показатели)
• Окружающие таблицы измерений (Dim_Date, Dim_Product, Dim_Geography)

В любых отношениях необходимо было ответить на три вопроса:

• Какая сторона стола является «одной»?
• Какая из таблиц относится к «многосторонней» части?
• Имеет ли эта взаимосвязь смысл, исходя из имеющихся данных?

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

Вот как выглядит модель в целом:

Шаг 2: Направление отношений (Одиночество, сознательное стремление)

Все взаимоотношения были установлены следующим образом:

• Многие к одному
• Однонаправленное течение , исходящее из измерения → факта

Как для PL-300, так и для реальных моделей, по умолчанию используются однонаправленные фильтры, пока не появится веская причина их не использовать.

Шаг 3: Таблица дат в качестве опорной точки

Созданная мной ранее динамическая таблица дат теперь стала основой модели.

Я:

• Сопоставьте Dim_Date[Date] со столбцом даты в таблице фактов.
• Таблица Dim_Date помечена как официальная таблица дат.
• Скрыть столбец с исходными датами в таблице фактов.

Это позволяет достичь трех важных результатов:

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

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

Шаг 4: Скрытие ненужной информации для пользователей

Это небольшой шаг, имеющий огромное значение. Стандарт PL-300 явно проверяет идею о том, что модель должна быть не просто правильной — она должна быть пригодной для использования .

Я спрятался:

• Внешние ключи (Дата, Продукт, Страна). Если столбец существует только для создания связей, он не обязательно должен отображаться в панели «Поля».
• Числовые столбцы без форматирования, которые следует использовать только в мерах. После создания мер DAX (например, «Общий объем продаж», «Общая прибыль») я могу скрыть числовые столбцы без форматирования (например, «Продано единиц», «Валовая выручка», «Скидки», «Продажи», «Себестоимость проданных товаров», «Прибыль») из таблицы фактов. Это подталкивает пользователей к использованию правильных и согласованных агрегированных данных.
• Дублирующиеся атрибуты даты в таблице фактов (Год, Месяц, Номер месяца). Они уже существуют в таблице дат.

Шаг 5: Проверка модели (перед написанием кода на DAX)

Прежде чем предпринимать какие-либо действия, я быстро проверил свою адекватность:

• Работает ли функция сегментации по странам корректно?
• Фильтрация по продуктам и сегментам работает должным образом?
• Даты корректно суммируются по году и месяцу?

Если здесь что-то сломается, это будет проблема моделирования, а не проблема DAX.

Чтобы это проверить, я быстро создал визуализацию суммы прибыли по годам. Вот что получилось:

Пока все хорошо! Теперь мы можем перейти к созданию мер DAX.

Этап 3: Анализ показателей DAX и дисперсии (где модель начинает показывать свои лучшие качества)

На этом этапе работа, проделанная мной в Power Query и с моделью, действительно начала приносить свои плоды. Честно говоря, впервые за долгое время написание DAX-запросов не ощущалось как борьба со столом. Звездная схема сделала все… предсказуемым.

Шаг 1: Базовые меры — основа здравомыслия

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

Общий объем продаж := SUM ( financials[ Sales]) Общая прибыль := SUM ( financials[Profit] ) Общее количество проданных единиц := SUM ( financials[Units Sold] ) Общая себестоимость проданных товаров := SUM ( financials[COGS])

Шаг 2: Планирование времени без неожиданностей

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

Продажи с начала года

Продажи с начала года := TOTALYTD ( [Общий объем продаж], 'Dim_Date'[Дата] )

Продажи за предыдущий год

Sales PY := CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Dim_Date'[Date] ) )

Шаг 3: Анализ отклонений — преобразование чисел в полезную информацию.

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

Разница между продажами и фактическими показателями за год := [Общий объем продаж] — [Продажи за прошлый год] Процент разницы между продажами и фактическими показателями за год := РАЗДЕЛИТЬ ( [Разница между продажами и фактическими показателями за год], [Продажи за прошлый год] )

Аналогичный подход применяется и к анализу данных по месяцам:

Продажи в месяц := CALCULATE ( [Общий объем продаж], DATEADD ( 'Dim_Date'[Date], -1, MONTH ) ) Отклонение продаж в месяц := [Общий объем продаж] — [Продажи в месяц] Процент продаж в месяц := DIVIDE ( [Отклонение продаж в месяц], [Продажи в месяц] )

Шаг 4: Почему это на самом деле кажется «легким»

Честно говоря, написание DAX не казалось мне самым сложным делом. Самым сложным было всё, что было до этого:

• Очистка данных
• Профилирование колонок
• Создание размеров с помощью ссылки.
• Создание надежной таблицы дат

К тому времени, как я сюда приехал, DAX лишь приносил пользу, а не заделывал дыры.

Хороший DAX не отличается оригинальностью — он предсказуем, заслуживает доверия и прост для объяснения.

Заключение

Секрет заключался не в какой-либо отдельной формуле DAX, а в том, как складывалась модель. Благодаря предварительному анализу данных, пониманию кардинальности и использованию Reference для управления измерениями, я создал структуру, которая просто работает. Динамическая таблица дат и четкие связи позволили без труда выполнять измерения временной шкалы и расчеты отклонений.

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

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

Хотите пообщаться? Не стесняйтесь поздороваться на любой из платформ ниже.

Середина

LinkedIn

Твиттер

YouTube

Источник: towardsdatascience.com