Руководство по связям типа "многие ко многим"

Эта статья предназначена для моделирователя данных, который работает с Power BI Desktop. В нем описаны три различных сценария моделирования "многие ко многим". Он также предоставляет рекомендации по тому, как успешно разрабатывать модели для них.

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

• Связывание двух таблиц измерений
• Связывание двух таблиц фактов
• Связывайте таблицы фактов с более высоким уровнем детализации, когда таблица фактов хранит строки на более детализированном уровне, чем строки таблицы измерений

Связь измерений по принципу "многие ко многим"

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

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

Ниже приведена упрощенная схема трех таблиц моделей.

Первая таблица называется Account, и она содержит два столбца: AccountID и Account. Вторая таблица называется AccountCustomer, и она содержит два столбца: AccountID и CustomerID. Третья таблица называется Customer, а она содержит два столбца: CustomerID и Customer. Связи не существуют между таблицами.

Для связи таблиц добавляются две связи типа «один ко многим». Ниже приведена обновленная схема модели связанных таблиц. Добавлена таблица фактов с именем Transaction. Он записывает транзакции учетной записи. Таблица бриджинга и все столбцы идентификаторов были скрыты.

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

Подробности строк для этих четырёх таблиц представлены в следующем маркированном списке:

• В таблице Account есть две строки:
  • AccountID 1Аккаунт-01
  • AccountID 2 предназначен для аккаунт-02
• В таблице Customer есть две строки:
  • CustomerID 91 предназначено для Customer-91
  • CustomerID 92 — это Customer-92
• В таблице AccountCustomer есть три строки:
  • AccountID 1 связан с CustomerID91
  • AccountID 1 связано с CustomerID92
  • AccountID 2 связано с CustomerID92
• В таблице Transaction есть три строки:
  • Date 1 января 2019, AccountID1, Amount100
  • Date 2 февраля 2019, AccountID2, Amount200
  • Date 3 марта 2019, AccountID1, Amount-25

Давайте посмотрим, что происходит, когда мы обращаемся к модели.

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

Первый визуальный элемент таблицы (Account Balance) имеет два столбца: Account и Amount. Отображается следующий результат:

• счет-01 сумма баланса 75.
• Счет-02 остаток на счете 200.
• Общая сумма составляет 275.

Второй визуальный элемент таблицы (Customer Balance) содержит два столбца: Customer и Amount. Отображается следующий результат:

• Сумма баланса клиента-91 составляет 275.
• Клиент-92 баланс составляет 275.
• Общая сумма составляет 275.

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

Однако что-то не так с визуальным элементом "Баланс клиента". Каждый клиент на этой диаграмме имеет такой же баланс, как и общий баланс. Этот результат может быть правильным, только если каждый клиент был совместным владельцем каждой учетной записи. Это не так в этом примере. Возникла проблема, и она связана с распространением фильтров. Фильтры не доходят к таблице Transaction.

Если следовать указаниям фильтра связей из таблицы Customer в таблицу Transaction, можно определить, что связь между Account и таблицами AccountCustomer распространяется в неправильном направлении. Направление фильтра для этой связи должно иметь значение Both.

Как ожидалось, не было изменений в визуальном элементе "Баланс учетной записи".

Однако визуальный элемент "Баланс клиентов" отображает следующий результат:

• Остаток по "Клиент-91" составляет 75.
• Клиент-92 сумма баланса — 275.
• Общая сумма составляет 275.

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

Кто-то, не знакомый со связями в модели, может заключить, что результат неверный. Они могут попросить: Почему не общий баланс для Customer-91 и Customer-92 равны 350 (75 + 275)?

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

Руководство по использованию реляционных измерений "многие ко многим"

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

• Добавьте каждую сущность, связанную по принципу "много ко многим", в виде таблицы модели, обеспечивая наличие столбца идентификатора.
• Добавьте таблицу бриджинга для хранения связанных сущностей.
• Создайте связи "один ко многим" между тремя таблицами.
• Задайте одну двунаправленную связь, чтобы разрешить продолжение распространения фильтров в таблицу фактов.
• Когда недопустимо иметь отсутствующие значения ID, отключите свойство Is Nullable — обновление данных завершится ошибкой, если обнаружены отсутствующие значения.
• Скрыть таблицу сопоставления (если она не содержит другие столбцы или меры, необходимые для создания отчетов).
• Скрыть столбцы ID, которые непригодны для отчетности (например, когда столбцы содержат суррогатные значения ключей).
• Если имеет смысл оставить столбец идентификатора видимым, убедитесь, что он находится на одной стороне связи, всегда скрывайте "многие" боковой столбец. Это связано с тем, что фильтры, примененные к слайду "one", приводят к улучшению работы фильтра.
• Чтобы избежать путаницы или неправильной интерпретации, передайте объяснения пользователям отчета— вы можете добавить описания с текстовыми полями или подсказками визуального заголовка.

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

Связь многих ко многим фактов

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

Рассмотрим пример, который включает две таблицы фактов: Order и Fulfillment. Таблица Order содержит одну строку для каждой строки заказа, а таблица Fulfillment может содержать нулевые или более строк на строку заказа. Строки в таблице Order представляют заказы на продажу. Строки в таблице Fulfillment представляют элементы заказа, которые были отправлены. Связь «многие ко многим» соединяет столбцы OrderID в каждой таблице, причем распространение фильтров осуществляется только из таблицы Order (то есть таблица Order фильтрует таблицу Fulfillment).

Для поддержания возможности хранения повторяющихся значений столбца OrderID в обеих таблицах кардинальность связей установлена на Many-to-many. В таблице Order повторяющиеся значения идентификаторов могут существовать, так как порядок может иметь несколько строк. В таблице Fulfillment повторяющиеся значения идентификаторов могут существовать, так как заказы могут иметь несколько строк, а строки заказов могут выполняться многими отгрузками.

Давайте рассмотрим строки таблицы. В таблице Fulfillment обратите внимание, что линии заказов могут выполняться несколькими отгрузками. (Отсутствие строки заказа означает, что заказ еще не выполнен.)

Подробности о строках для двух таблиц описаны в следующем маркированном списке:

• В таблице Order есть пять строк:
  • OrderDate 1 января 2019, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
  • OrderDate января 1 2019, OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
  • OrderDate февраля 2019, OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
  • OrderDate 2 февраля 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
  • OrderDate 3 марта 2019, OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
• В таблице Fulfillment есть четыре строки:
  • FulfillmentDate 1 января 2019, FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
  • FulfillmentDate 2 февраля 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
  • FulfillmentDate 2 февраля 2019, FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
  • FulfillmentDate 1 января 2019, FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

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

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

Руководство по использованию фактов "многие ко многим"

Как правило, мы не рекомендуем связывать две таблицы фактов напрямую с помощью кратности "многие ко многим". Основная причина заключается в том, что модель не обеспечивает гибкость в способах фильтрации или группировки визуальных элементов отчета. В примере возможно только фильтровать или группировать визуальные элементы по столбцу Order таблицы OrderID. Другая причина связана с качеством данных. Если данные имеют проблемы с целостностью, некоторые строки могут быть опущены во время запроса из-за природы кратности "многих ко многим" и ограниченных связей.

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

Рассмотрим лучшее решение.

Обратите внимание на следующие изменения дизайна:

• Теперь модель содержит четыре дополнительные таблицы: OrderLine, OrderDate, Productи FulfillmentDate.
• Четыре дополнительные таблицы — это все таблицы измерений, в которых связи "один ко многим" связаны с таблицами фактов.
• Таблица OrderLine содержит столбец OrderLineID, который сохраняет значение OrderID умножено на 100, а также значение столбца OrderLine — идентификатор для каждой строки заказа.
• Теперь таблицы Order и Fulfillment содержат столбец OrderLineID, и они больше не содержат столбцы OrderID и OrderLine.
• Теперь таблица Fulfillment содержит столбцы OrderDate и ProductID.
• Таблица FulfillmentDate имеет отношение только к таблице Fulfillment.
• Все столбцы идентификаторов скрыты.

Выделение времени на внедрение схемы "звезда" приносит следующие преимущества:

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

Относитесь к фактам с более высокой степенью детализации

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

Рассмотрим пример с четырьмя таблицами: Date, Sales, Productи Target. Таблицы Date и Product являются таблицами измерений, а отношения "один ко многим" относятся к таблице фактов Sales. На данный момент это является хорошим примером звездной схемы. Однако таблица Target еще не связана с другими таблицами.

Таблица Target содержит три столбца: Category, TargetQuantityи TargetYear. Строки таблицы показывают степень детализации года и категории продуктов. Другими словами, целевые показатели, используемые для измерения производительности продаж, устанавливаются каждый год для каждой категории продуктов.

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

Связь с более высоким периодом времени зерна

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

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

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

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

Рассмотрим следующее определение меры, которое использует функцию ISFILTERED DAX. Он возвращает значение, только если столбцы Date и Month не фильтруются.

Target Quantity =
IF(
    NOT ISFILTERED('Date'[Date])
        && NOT ISFILTERED('Date'[Month]),
    SUM(Target[TargetQuantity])
)

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

Отношение к более крупному зерну (кроме даты)

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

Столбцы Category (как из Product, так и из таблиц Target) содержат повторяющиеся значения. Таким образом, в отношении "один ко многим" нет единой "стороны". В этом случае необходимо создать связь "многие ко многим". Связь должна распространять фильтры из таблицы измерений в таблицу фактов только в одном направлении.

Давайте рассмотрим строки таблицы.

В таблице Target есть четыре строки: две строки для каждого целевого года (2019 и 2020) и две категории (одежда и аксессуары). В таблице Product есть три продукта. Два принадлежат к категории одежды, и один принадлежит категории аксессуаров. Один из цветов одежды зеленый, и остальные два являются голубыми.

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

Визуальный элемент создает неправильное представление данных. Что происходит здесь?

Фильтр по столбцу Color из таблицы Product приводит к двум строкам. Одна из строк относится к категории "Одежда", а другая — для категории "Аксессуары". Эти два значения категорий распространяются в виде фильтров в таблицу Target. Другими словами, поскольку синий цвет используется в продуктах из двух категорий, эти категории применяются для фильтрации целей.

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

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

Target Quantity =
IF(
    NOT ISFILTERED('Product'[ProductID])
        && NOT ISFILTERED('Product'[Product])
        && NOT ISFILTERED('Product'[Color]),
    SUM(Target[TargetQuantity])
)

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

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

Рекомендации по высшему качеству зерна

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

• Для более поздних дат, связанных с зерном
  • В таблице фактов сохраните первую дату периода времени.
  • Создайте связь "один ко многим" между таблицей дат и таблицей фактов.
• О других более важных фактах о зерне
  • Создайте связь "многие ко многим" между таблицей измерений и таблицей фактов.
• Для обоих типов
  • Управляйте сводкой с помощью логики измерений — возвращает BLANK, если столбцы измерения нижнего уровня используются для фильтрации или группировки.
  • Скрытие столбцов таблицы фактов с сводной таблицей фактов, которая гарантирует, что для сводки таблицы фактов можно использовать только меры.

Связанное содержимое

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

• Взаимосвязи модели в Power BI Desktop
• Понять звездную схему и её важность для Power BI
• Руководство по решению проблем в отношениях
• Вопросы? Попробуйте обратиться к сообществу Fabric
• Предложения? Предлагайте идеи для улучшения Fabric