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

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

Примечание

Общие сведения о связях в моделях в этой статье не приводятся. Если у вас есть пробелы в знаниях о связях, их свойствах или настройке, рекомендуем сначала прочитать статью Связи модели в Power BI Desktop.

Вы также должны иметь представление о проектировании схемы типа "звезда". Дополнительные сведения см. в статье Общие сведения о схеме типа "звезда" и ее значении в Power BI.

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

Примечание

Power BI теперь изначально поддерживает связи "многие ко многим". Дополнительные сведения см. в статье Применение связей "многие ко многим" в Power BI Desktop.

Связывание измерений "многие ко многим"

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

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

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

Diagram showing a model containing three tables. The design is described in the following paragraph.

Первая таблица называется Счет и содержит два столбца: ИдСчета и Счет. Вторая таблица называется СчетКлиент и содержит два столбца: ИдСчета и ИдКлиента. Третья таблица называется Клиент и содержит два столбца: ИдКлиента и Клиент. Между этими таблицами пока нет никаких связей.

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

Diagram showing that the model now contains four tables. One-to-many relationships have been added to relate all tables.

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

Примечание

Строки таблиц невозможно отобразить на схеме модели в Power BI Desktop. В этой статье это было сделано для наглядности.

Diagram showing that the model now reveals the table rows. The row details for the four tables are described in the following paragraph.

В приведенном ниже списке описываются строки каждой из четырех таблиц.

  • В таблице Счет две строки:
    • ИдСчета 1 для счета "Счет-01";
    • ИдСчета 2 для счета "Счет-02".
  • В таблице Клиент две строки:
    • ИдКлиента 91 для клиента "Клиент-91";
    • ИдКлиента 92 для клиента "Клиент-92".
  • В таблице СчетКлиент три строки:
    • ИдСчета 1 связан с ИдКлиента 91;
    • ИдСчета 1 связан с ИдКлиента 92;
    • ИдСчета 2 связан с ИдКлиента 92.
  • В таблице Транзакция три строки:
    • Дата 1 января 2019 г., ИдСчета 1, Сумма 100;
    • Дата 2 февраля 2019 г., ИдСчета 2, Сумма 200;
    • Дата 3 марта 2019 г., ИдСчета 1, Сумма -25.

Давайте посмотрим, что происходит при запросе к модели.

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

Diagram showing two report visuals sitting side by side. The visuals are described in the following paragraph.

Первый визуальный элемент называется Остаток на счете и содержит два столбца: Счет и Сумма. В нем отображаются следующие результаты:

  • остаток на счете "Счет-01" равен 75;
  • остаток на счете "Счет-02" равен 200;
  • сумма равна 275.

Второй визуальный элемент называется Баланс клиента и содержит два столбца: Клиент и Сумма. В нем отображаются следующие результаты:

  • баланс клиента "Клиент-91" равен 275;
  • баланс клиента "Клиент-92" равен 275;
  • сумма равна 275.

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

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

Проследите за направлениями фильтров связей от таблицы Клиент к таблице Транзакция. Очевидно, что связь между таблицами Счет и СчетКлиент имеет неправильное направление. В качестве направления фильтра для этой связи следует выбрать Оба.

Diagram showing that the model has been updated. It now filters in both directions.

Diagram showing the same two report visuals sitting side by side. The first visual has not changed, while the second visual has.

Как и следовало ожидать, визуальный элемент Остаток на счете не изменился.

Визуальный элемент Баланс клиента, однако, теперь содержит другой результат:

  • баланс клиента "Клиент-91" равен 75;
  • баланс клиента "Клиент-92" равен 275;
  • сумма равна 275.

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

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

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

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

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

  • Добавьте каждую сущность, участвующую в связи "многие ко многим", как таблицу модели со столбцом уникальных идентификаторов.
  • Добавьте сопоставительную таблицу для хранения связываемых сущностей.
  • Создайте связь "один ко многим" между тремя таблицами.
  • Настройте одну двунаправленную связь, чтобы обеспечить распространение фильтра до таблицы фактов.
  • Если отсутствие идентификаторов недопустимо, установите свойство Допускает значение NULL для столбцов идентификаторов в значение FALSE. В этом случае при обнаружении отсутствующих значений обновление данных будет завершаться сбоем.
  • Скройте сопоставительную таблицу (если только она не содержит дополнительные столбцы или меры, необходимые для отчета).
  • Скройте столбцы идентификаторов, которые не нужны для формирования отчета (например, суррогатные ключи).
  • Если имеет смысл оставить столбец идентификаторов видимым, он должен находиться на стороне "один" связи — всегда скрывайте столбцы на стороне "многие". Это обеспечит максимальную производительность фильтра.
  • Чтобы избежать путаницы или неправильной интерпретации, предоставьте объяснения пользователям отчета. Вы можете добавить описания с помощью текстовых полей или подсказок для заголовков визуальных элементов.

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

Связывание фактов "многие ко многим"

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

Рассмотрим пример, в котором используются две таблицы фактов: Заказ и Выполнение. В таблице Заказ содержится по одной строке на каждую позицию заказа, а в таблице Выполнение может содержаться от нуля и более строк на позицию. Строки в таблице Заказ представляют заказы на продажу. Строки в таблице Выполнение представляют отгруженные элементы заказов. Связь "многие ко многим" связывает два столбца ИдЗаказа, причем фильтр распространяется только из таблицы Заказ (таблица Заказ фильтрует таблицу Выполнение).

Diagram showing a model containing two tables: Order and Fulfillment.

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

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

Diagram showing that the model now reveals the table rows. The row details for the two tables are described in the following paragraph.

В приведенном ниже списке описываются строки каждой из двух таблиц.

  • В таблице Заказ пять строк:
    • ДатаЗаказа 1 января 2019 г, ИдЗаказа 1, ПозицияЗаказа 1, ИдТовара Товар-А, Количество 5, Продажи 50;
    • ДатаЗаказа 1 января 2019 г, ИдЗаказа 1, ПозицияЗаказа 2, ИдТовара Товар-Б, Количество 10, Продажи 80;
    • ДатаЗаказа 2 февраля 2019 г, ИдЗаказа 2, ПозицияЗаказа 1, ИдТовара Товар-Б, Количество 5, Продажи 40;
    • ДатаЗаказа 2 февраля 2019 г, ИдЗаказа 2, ПозицияЗаказа 2, ИдТовара Товар-В, Количество 1, Продажи 20;
    • ДатаЗаказа 3 марта 2019 г, ИдЗаказа 3, ПозицияЗаказа 1, ИдТовара Товар-В, Количество 5, Продажи 100.
  • В таблице Выполнение четыре строки:
    • ДатаВыполнения 1 января 2019 г, ИдВыполнения 50, ИдЗаказа 1, ПозицияЗаказа 1, ВыполненноеКоличество 2;
    • ДатаВыполнения 2 февраля 2019 г, ИдВыполнения 51, ИдЗаказа 2, ПозицияЗаказа 1, ВыполненноеКоличество 5;
    • ДатаВыполнения 2 февраля 2019 г, ИдВыполнения 52, ИдЗаказа 1, ПозицияЗаказа 1, ВыполненноеКоличество 3;
    • ДатаВыполнения 1 января 2019 г, ИдВыполнения 53, ИдЗаказа 1, ПозицияЗаказа 2, ВыполненноеКоличество 10.

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

Diagram showing a table visual with three columns: OrderID, OrderQuantity, and FulfillmentQuantity.

Визуальный элемент содержит правильные результаты. Однако полезность модели ограничена — фильтрацию или группировку можно производить только по столбцу ИдЗаказа таблицы Заказ.

Рекомендации по связыванию фактов "многие ко многим"

Как правило, не рекомендуется связывать две таблицы фактов напрямую, используя кратность "многие ко многим". Главная причина в том, что модель будет недостаточно гибкой в плане фильтрации и группировки визуальных элементов в отчете. В приведенном примере фильтрация и группировка в визуальном элементе возможны только по столбцу ИдЗаказа таблицы Заказ. Еще одна причина связана с качеством данных. Если в данных есть проблемы с целостностью, некоторые строки могут быть опущены при выполнении запроса из-за самого характера ограниченной связи. Дополнительные сведения см. в разделе Вычисление связей из статьи "Создание связей модели в Power BI Desktop".

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

Давайте улучшим решение.

Diagram showing a model includes six tables: OrderLine, OrderDate, Order, Fulfillment, Product, and FulfillmentDate.

Обратите внимание на указанные ниже изменения.

  • В модели теперь есть четыре дополнительных таблицы: ПозицияЗаказа, ДатаЗаказа, Товар и ДатаВыполнения.
  • Все дополнительные таблицы являются таблицами измерений и связаны с таблицами фактов связями "один ко многим".
  • Таблица ПозицияЗаказа содержит столбец ИдПозицииЗаказа, который представляет значение ИдЗаказа, умноженное на 100, плюс значение ПозицияЗаказа, — уникальный идентификатор для каждой позиции.
  • В таблицах Заказ и Выполнение теперь есть столбец ИдПозицииЗаказа и больше нет столбцов ИдЗаказа и ПозицияЗаказа.
  • В таблице Выполнение теперь есть столбцы ДатаЗаказа и ИдТовара.
  • Таблица ДатаВыполнения связана только с таблицей Выполнение.
  • Все столбцы уникальных идентификаторов скрыты.

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

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

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

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

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

Diagram showing a model including four tables: Date, Sales, Product, and Target.

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

Diagram showing the Target table has three columns: TargetYear, Category, and TargetQuantity.

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

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

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

Совет

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

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

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

Diagram showing a matrix visual revealing the year 2020 target quantity as 270.

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

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

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

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

Diagram showing a matrix visual revealing the year 2020 target quantity as 270 with blank monthly values.

Связывание фактов с более высокой детализацией (не дат)

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

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

Diagram showing a model of the Target and Product tables. A many-to-many relationship relates the two tables.

Теперь давайте посмотрим на строки таблиц.

Diagram showing a model containing two tables: Target and Product. A many-to-many relationship relates the two Category columns.

В таблице Цель четыре строки: две для каждого целевого года (2019 и 2020) и две категории ("Одежда" и "Аксессуары"). В таблице Товар три товара. Два из них относятся к категории "Одежда", а один — к категории "Аксессуары". Один из товаров зеленый, а остальные два синие.

Группирование в табличном визуальном элементе по столбцу Категория из таблицы Товар дает следующий результат:

Diagram showing a table visual with two columns: Category and TargetQuantity. Accessories is 60, Clothing is 40, and the total is 100.

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

Diagram showing a table visual with two columns: Color and TargetQuantity. Blue is 100, Green is 40, and the total is 100.

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

Применение фильтра к столбцу Цвет из таблицы Товар дает две строки. Одна из них относится к категории "Одежда", а другая — к категории "Аксессуары". Значения для двух этих категорий распространяются как фильтры в таблицу Цель. Иными словами, поскольку синий цвет имеют товары из двух категорий, эти категории используются для фильтрации целевых показателей.

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

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

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

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

Diagram showing a table visual with two columns: Color and TargetQuantity. Blue is BLANK, Green is BLANK, and the total is 100.

Итоговая схема модели выглядит следующим образом:

Diagram showing a model with Date and Target tables related with a one-to-many relationship.

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

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

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

Дальнейшие действия

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