Решения типа «один к одному» (L4L) необходимы для сравнения элементов. Рассмотрим решение, построенное на основе семантической модели.
Делиться
Что такое принцип «один к одному» (L4L)?
L4L — это механизм, обеспечивающий сравнение только сопоставимых элементов.
Элементами могут быть товары, магазины, группы клиентов и т. д.
Здесь вы можете прочитать подробное объяснение этой темы.
В данном случае я разработаю решение для магазинов.
Магазины могут открываться, закрываться или даже временно приостанавливаться для проведения ремонта, реконструкции или по другим причинам.
Таким образом, магазины могут быть сопоставимы или несопоставимы при сравнении текущих результатов с результатами предыдущего года. Это означает, что если магазин не работал в течение определенного периода в предыдущем году, он несопоставим в текущем году, если он работал в течение того же периода.
L4L обеспечит пользователю отчета возможность выбора, включать или исключать из него магазины, не подлежащие сравнению.
Для выбора состояния L4L я создаю таблицу DIM_L4L:
Я могу использовать столбцы L4L_Test и Reason в качестве иерархии в срезе или в матричной визуализации.
Магазины
Я выбрал несколько магазинов из набора данных ContosoRetailDW (подробная информация о наборе данных ContosoRetailDW приведена в разделе «Ссылки» ниже).
В данном случае я выбрал магазины в Италии.
Вот список итальянских магазинов с датами открытия и закрытия, а также указанием регионов L4L:
В эту таблицу я добавил два столбца с датами открытия и закрытия каждого магазина в конце месяца.
В этой таблице представлены все магазины, которые не поддаются сравнению.
Как видите, магазины 224 и 226 должны были открыться в 2024 году, магазин 222 — закрыться в 2024 году, а магазины 222 и 225 были временно закрыты в 2023 и 2024 годах.
Во время подготовки данных для решения все остальные магазины будут настроены на сопоставимый режим (L4LKey = 1).
На что обратить внимание
Итак, каковы требования?
- Мы всегда оглядываемся на прошедший год. В 2025 году мы смотрим на 2024 год, а в 2024 году — на 2023 год.
- Пользователь должен иметь возможность выбрать каждый из штатов L4L. Если ни один штат не выбран, данные не фильтруются, и отображаются все магазины.
- Мы хотим контролировать результаты по месяцам. Нет необходимости менять ежедневные результаты.
- Если в предыдущем году магазин изменил свой статус с 1 (Сопоставимый) на другой, данные необходимо отфильтровать в текущем году.
Например, магазин открывается в августе 2024 года. Если мы рассмотрим только сопоставимые данные за 2025 год, то не должны увидеть никаких результатов за период с января по июль 2025 года. - Показатели, используемые в отчетах, не следует изменять для достижения необходимых результатов.
Подготовка данных
Во-первых, мне нужно создать таблицу, содержащую все месяцы. Кроме того, она должна включать первую и последнюю даты каждого месяца как в текущем, так и в предыдущем году.
Для этого я создаю таблицу в Power Query, используя в качестве ссылки таблицу Date.
Я оставляю только следующие столбцы и удаляю все остальные:
- Ключ месяца
- MonthKeyPY
- Первый день месяца
- Последний день месяца
- Первый день месяца
- Последний день месяца
После этого я удаляю все дубликаты.
Таблица L4L_Months выглядит следующим образом:
Далее я создал решение в Power Query, объединив таблицы Store, L4L_Months и таблицу с магазинами и датами открытия и закрытия (название таблицы: L4L_Dates).
Создание решения Power Query
Я создал таблицу, на которую ссылается таблица «Store», и переименовал ее в «Bridge_L4L».
Я удаляю все столбцы, кроме столбца StoreKey.
Далее мне нужна отдельная строка для каждого магазина и каждого месяца.
Для этого я добавляю столбец в таблицу L4L_Months:
Если развернуть все столбцы таблицы L4L_Month, я получу таблицу, содержащую одну строку для каждой комбинации магазина и месяца:
Теперь каждый магазин встречается в списке несколько раз. Чтобы для каждого магазина было уникальное значение «ключ-значение», я добавляю столбец StoreMonthKey:
Далее я подготавливаю таблицу с данными о магазине под названием «L4L_Dates».
Что касается таблицы Bridge_L4L, я добавил таблицу L4L_Months к таблице stores, которая содержит даты открытия и закрытия (см. рисунок 2).
И снова, как и прежде, я разворачиваю все столбцы из таблицы L4L_Months.
Опять же, каждый магазин встречается в списке несколько раз. Я добавляю один и тот же уникальный ключ-значение для каждого магазина (StoreMonthKey):
Text.From([StoreKey]) & "_" & Text.From([MonthKey])На данный момент у меня есть вся необходимая информация для выбора строк с правильным состоянием L4L.
Я должен сделать это в соответствии с датами открытия и закрытия и сравнить их со столбцами First-DateOfMonthPY, используя необходимую логику для каждого состояния L4L.
Для этого я добавляю пользовательский столбец со следующим выражением:
if [L4LKey] = 2 and [OpenDate] >= [FirstDayOfMonthPY] then true else if [L4LKey] = 3 and [CloseDate] <= [LastDayOfMonthPY] then true else if [L4LKey] = 4 and ([OpenDate] >= [FirstDayOfMonthPY] and [CloseDate] <= [LastDayOfMonthPY]) then true else falseЯ называю этот столбец «Valid», поскольку он отмечает правильные строки для каждого состояния L4L.
Далее я фильтрую данные, чтобы оставить только допустимые строки:
Следующий шаг — объединение таблицы Bridge_L4L с таблицей L4L_Dates с использованием ранее созданных столбцов StoreMonthKey:
На данном этапе мне нужен только столбец L4LKey из таблицы L4L_Dates в таблице Bridge_L4L:
В большинстве строк столбец L4LKey содержит значение NULL.
Все эти строки относятся к магазинам и месяцам, которые являются сопоставимыми.
По этой причине я заменяю все нулевые значения на 1:
Наконец, я удалил все столбцы, кроме необходимых:
Выполнив эти шаги, я создал таблицу Bridge_L4L, которая может служить фильтром на основе выбранного состояния L4L.
Что еще осталось сделать в Power BI?
Теперь мне нужно разместить новую таблицу Bridge_L4L между таблицами Store и таблицей фактов «Розничные продажи».
Затем я смогу добавить связь между новой таблицей DIM_L4L и таблицей Bridge_L4L.
Но чтобы добавить связь между таблицей Bridge_L4L и таблицей фактов «Розничные продажи», мне необходимо добавить тот же StoreMonthKey в таблицу «Розничные продажи», чтобы однозначно идентифицировать магазин для каждого месяца.
Я делаю это в SQL-запросе для получения данных фактов:
SELECT [F].[SaleLineCounter] AS [Sale Line Counter] ,CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey])) AS [DateKey] ,[F].[channelKey] ,[F].[StoreKey] ,CONCAT(CONVERT(nvarchar(25), [F].[StoreKey]) ,'_' ,CONVERT(nvarchar(25), YEAR(CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey])))) ,RIGHT('00' + CONVERT(nvarchar(25), MONTH(CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey])))), 2) ) AS [StoreMonthKey] ,[F].[ProductKey] ,[F].[PromotionKey] ,[F].[CurrencyKey] ,[F].[UnitCost] ,[F].[UnitPrice] ,[F].[SalesQuantity] ,[F].[ReturnQuantity] ,[F].[ReturnAmount] ,[F].[DiscountQuantity] ,[F].[DiscountAmount] ,[F].[TotalCost] ,[F].[SalesAmount] ,[F].[DateKeyYear] FROM [dbo].[v_FactSales] AS [F];Теперь в таблице фактов отображается следующий столбец:
В итоге, модель данных для задействованных таблиц выглядит следующим образом:
Как видите, у меня только однонаправленные отношения «один ко многим», как и должно быть.
Результаты
После добавления в отчет матричной визуализации с иерархией L4L, магазинами и месяцами в столбцах, я получаю следующий результат для суммы продаж за 2025 год:
Рассмотрим различные сценарии:
- Открытие магазинов во Флоренции и Милане:
Они открылись в мае и октябре 2024 года. Поскольку данные о продажах за эти месяцы отсутствуют, они считаются несопоставимыми. Как видите, данные о продажах переключаются между состояниями «Несопоставимые – Открытие» и «Сопоставимые». - Закрытие магазина Contoso Roma:
Здесь та же картина. Магазин в Риме закрылся в августе 2024 года. Любые результаты после этого месяца будут видны как сопоставимые. Помните, что это демонстрационные данные, и в реальном мире не будет продаж за ноябрь и декабрь. Но при желании вы можете добавить к данным магазина затраты, например, для анализа в отчете о прибылях и убытках. - Обновленный магазин Contoso Torino
Этот магазин был закрыт в период с марта по июль 2024 года. Следовательно, продажи за эти месяцы следует считать несопоставимыми.
Даже если посмотреть на 2024 год, мы видим, что римский магазин правильно обозначен как Refresh, и все остальные магазины, за исключением магазинов во Флоренции и Милане, имеют аналогичную маркировку:
Результаты полностью соответствуют моим ожиданиям.
Напоминаю, что я работаю с демонстрационными данными, и я намеренно не удалял данные о закрытых магазинах. Таким образом, результаты лучше видны.
Как это сделать по-другому
Этот подход работает, но есть и другие способы. Всё зависит от требований и от того, какой подход лучше всего подходит для вашей ситуации.
- Вы можете перенести эту логику из Power Query в язык программирования по вашему выбору, например, SQL или Python.
- Этот подход с использованием промежуточной таблицы просто замечательный, поскольку позволяет установить двустороннюю фильтрацию связи между магазином и промежуточной таблицей и скрыть магазины, которые не соответствуют выбранному состоянию L4L. Все таблицы фактов связаны с промежуточной таблицей, что исключает возникновение циклических зависимостей.
- Более удачным решением может быть интеграция состояния L4L в таблицу(ы) фактов. Это позволит избежать необходимости создания таблицы моста.
- Вы можете решить добавить логику историзации в логику измерения «Хранилище» и добавить в неё состояние L4L. В этом случае вам необходимо включить иерархию L4L в таблицу «Хранилище». Это может быть наилучшим подходом, поскольку он будет включать стандартную логику SCD2. В то же время это более сложный вариант, поскольку он усложняет подготовку таблицы измерения «Хранилище».
Выбор оптимального подхода к моделированию зависит от ваших требований и имеющихся навыков.
Заключение
Сегодня я показал вам, как создать решение для сопоставления данных по магазинам за разные годы.
Цель создания решения без изменений в показателях DAX достигнута. Всё решение полностью основано на данных.
Это важная тема. Логика, основанная на DAX, может быть нежизнеспособной, поскольку она вводит необходимость включения дополнительной логики DAX в вашу модель данных. Об этом всегда следует помнить при добавлении новых мер.
Кроме того, это может привести к проблемам с производительностью, поскольку код может стать более сложным и потенциально работать медленнее, чем без этого.
Я большой поклонник решений, основанных на данных. В большинстве случаев они лучше, чем сложный код DAX.
Надеюсь, вы узнали что-то новое и интересное. До скорой встречи!
Ссылки
Здесь представлено видео на YouTube от SQLBI о создании решения L4L для брендов:
Как и в моих предыдущих статьях, я использую пример набора данных Contoso. Вы можете бесплатно скачать набор данных ContosoRetailDW с сайта Microsoft здесь.
Данные Contoso можно свободно использовать в соответствии с лицензией MIT, как описано в этом документе. Я обновил набор данных, чтобы перевести данные в современный формат, и удалил все таблицы, не необходимые для этого примера.
Сальваторе Кальяри Посмотреть все магазины Сальваторе Кальяри
Источник: towardsdatascience.com
