Для специалиста по хранилищу данных концепция совместного измерения всегда ясна, и проект схемы “звезда” всегда включает в себя объект такого типа. Тем не менее, в мире пользователей Power BI есть много пользователей, у которых нет опыта работы с хранилищами данных. Чтобы понять хорошо работающую модель Power BI, необходимо понимать некоторые концепции. Мы расскажем о некоторых концепциях простым для понимания способом в некоторых статьях. Первая из них: Что такое общее измерение и зачем оно нужно в вашей модели Power BI? В этом посте мы расскажем, как это может предотвратить многие проблемы, а также необходимость двусторонних отношений.

Образец набора данных

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

Таблица Inventory, которая показывает детали инвентаря в каждый день на каждом складе.

Таблица Sales, которая отображает торговые сделки.

Таблица Manufacturing, которая показывает обобщенную информацию о стоимости производства каждого продукта на основе даты.

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

Проблема отношений “многие ко многим”

Если вы попытаетесь создать отношение самостоятельно на основе продукта, например, между двумя таблицами Inventory и Manufacturing, вы получите всплывающее сообщение о необходимости отношения «многие ко многим»!

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

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

Проблема двусторонних отношений

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

В этом сценарии у нас есть уникальные значения поля Date в обеих таблицах, поэтому отношения не будут “многие ко многим”. Однако, поскольку мы хотим нарезать данные инвентаризации по датам, выбранным из таблицы Manufacturing, и наоборот, отношение должна быть двусторонним.

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

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

Мастер-лист не существует!

Третья проблема трех приведенных выше таблиц - это отсутствие главного списка! В каждой таблице есть несколько продуктов, и у нас не обязательно есть все продукты в каждой таблице. Или в каждой таблице есть несколько дат, и у нас не обязательно есть все даты в каждой таблице (Power BI создаст автоматическое измерение даты, которое может решить эту проблему только для полей даты, но как насчет других полей, таких как Product?).

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

Затем мы создаем таблицу с полем Date из таблицы Sales в качестве среза и тремя визуальными элементами таблицы из каждой таблицы. Слайсер даты должен иметь возможность фильтровать все три таблицы на основе выбора даты.

Если я выберу поле в срезе даты, оно отфильтрует все три таблицы (из-за двусторонних отношений). Однако, если вы внимательно посмотрите на две даты, упомянутые на снимке экрана выше, и на некоторые другие даты в таблицах Inventory и Manufacturing, поля в слайсере не существует. Поскольку срез даты происходит из таблицы Sales, а в таблице Sales нет этих дат! у нас будет такая же проблема со слайсером продукта, если мы добавим его.

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

Общее измерение: решение

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

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

Итак, в вышеприведенном проектировании, каковы наши измерения? Дата и продукт. Что такое таблицы фактов? Продажи, инвентарь и производство. Проблема в том, что нет таблицы измерений. Измерения - это поля внутри таблиц фактов, что создает несогласованность и подходы к проектированию. Что вы должны сделать, это построить две таблицы по отдельности. Поскольку таблицы Date и Product будут таблицами, которые нарезают все таблицы фактов и будут связаны со всеми таблицами фактов, мы называем их общими измерениями. Общее измерение - это просто измерение, которое совместно используется несколькими таблицами фактов.

Создание общего измерения

Теперь, когда вы знаете, что такое общее измерение и как оно может быть полезным, давайте посмотрим, как мы можем добавить его в наш дизайн. Вы можете построить общее измерение многими способами: с помощью вычисляемых таблиц DAX, с помощью t-SQL (если он получен из систем баз данных) или в Power Query. Поскольку Power Query применим независимо от выбранного источника данных, а также потому, что шаг преобразования данных лучше выполнять в Power Query, а не DAX, мы покажем вам, как это сделать в Power Query.

Перейдите к Edit Queries в Power BI Desktop:

Подготовьте подтаблицы

В окне редактора Power Query щелкните правой кнопкой мыши таблицу инвентаризации и создайте ссылку из запроса:

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

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

Щелкните правой кнопкой мыши таблицу Inventory (2) и снимите флажок Enable load для нее. Это позволяет снизить производительность и избежать загрузки дополнительных таблиц в память Power BI Desktop.

Теперь выполните то же самое с таблицами Manufacturing и Sales.

• создайте ссылку из каждой таблицы
• сохраните только таблицу Product и удаляйте другие столбцы.
• снимите флажок с Enable load в новом запросе

Теперь у вас должны быть новые три таблицы с одним столбцом Product только в каждой:

Сделайте все имена столбцов одинаковыми

Следующий шаг - убедиться, что имена столбцов совпадают. потому что мы собираемся добавить три таблицы, если у нас разные имена, то это создаст дополнительные столбцы. Имена должны точно соответствовать, и помните, что Power Query является регистрозависимым языком; продукт отличается от продукта в мире Power Query. В нашем примере модели две таблицы Inventory и Manufacturing имеют имя столбца как Product, но в таблице Sales оно называется Product Name, переименуйте его в Product.

Объедините все три таблицы

Объедините все три таблицы, чтобы создать одну таблицу со всеми значениями Product в ней. Если вы хотите узнать больше об объединении, прочитайте эту статью.

Затем в окне команды Append выберите Three or more tables и все новые таблицы в нем:

Результатом добавления будет одна таблица, включающая все значения Product; Вы можете переименовать этот запрос в Product.

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

Удалите дубликаты

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

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

• Чистая трансформация
• Обрезка трансформации
• Преобразование в верхний регистр
• Удаление дубликатов

Полная информация об этих шагах написана здесь.

Теперь у вас есть готовый продукт для измерения! Повторите этот процесс для любых других общих измерений с соответствующими полями. Однако для таблицы Date мы бы сделали это по-другому.

Измерение даты

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

Дизайн передового опыта: схема «звезда» и общие измерения

После загрузки приведенных выше таблиц вы можете создать однонаправленное отношение «один ко многим» из таблиц Product и Date во все другие таблицы фактов. Это окончательный дизайн, основанный на нашем примере:

Вышеупомянутый дизайн использует два общих измерения и избегает всех упомянутых проблем:

• Нет необходимости в двусторонних отношениях
• Нет необходимости в отношениях “многие ко многим”
• Таблицы продукта и даты являются основными таблицами, которые могут быть источником любой нарезки

Чтобы убедиться, что вы не будете использовать неправильные поля для нарезки, убедитесь, что вы скрыли столбцы Date и Product во всех трех таблицах фактов, как показано ниже:

Это решение теперь может иметь надлежащие возможности отчетности, как показано ниже:

Резюме

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

No Comments