Связи "многие ко многим" в Power BI Desktop (предварительная версия)Many-to-many relationships in Power BI Desktop (preview)

С помощью связей "многие ко многим" в Power BI Desktop можно соединить таблицы, использующие кратность многие ко многим.With the many-to-many relationships feature in Power BI Desktop, you can join tables that use a cardinality of Many to Many. Это позволяет более простым и интуитивно понятным образом создавать модели данных, которые содержат несколько источников данных.You can more easily and intuitively create data models that contain two or more data sources. Функция связей "многие ко многим" является частью более широкого набора возможностей составных моделей в Power BI Desktop.The many-to-many relationships feature is part of the larger composite models capabilities in Power BI Desktop.

Отношение "многие ко многим" в области "Изменение связи"

Возможность создания связей "многие ко многим" в Power BI Desktop реализована в наборе из трех связанных функций:The many-to-many relationships capability in Power BI Desktop is one of three related features:

  • Составные модели позволяют включить в отчет несколько подключений к данным в любом сочетании, в том числе подключения DirectQuery или импорт.Composite models: Allows a report to have two or more data connections, including DirectQuery connections or Import, in any combination. Дополнительные сведения см. в статье Составные модели в Power BI Desktop (предварительная версия).For more information, see Composite models in Power BI Desktop (preview).

  • Связи "многие ко многим". В составных моделях вы можете установить между таблицами связи "многие ко многим".Many-to-many relationships: With composite models, you can establish many-to-many relationships between tables. Они избавляют от необходимости поддерживать уникальные значения в таблицах.This approach removes requirements for unique values in tables. Также они позволяют обойтись без предыдущих обходных путей, например создания новых таблиц исключительно для образования связей.It also removes previous workarounds, such as introducing new tables only to establish relationships. Эта функция подробно описана далее в этой статье.The feature is described further in this article.

  • Режим хранения позволяет указать, в каких визуальных элементах будут использоваться запросы к источникам данных серверной части.Storage mode: You can now specify which visuals require a query to back-end data sources. Визуальные элементы, которым не нужны такие запросы, всегда импортируются, даже если они основаны на DirectQuery.Visuals that don't require a query are imported even if they're based on DirectQuery. Эта функция помогает повысить производительность и снизить нагрузку на серверную часть.This feature helps improve performance and reduce back-end load. Ранее даже простые визуальные элементы, например срезы, инициировали запросы к серверным источникам.Previously, even simple visuals such as slicers began queries that were sent to back-end sources. Дополнительные сведения см. в статье Режим хранения в Power BI Desktop (предварительная версия).For more information, see Storage mode in Power BI Desktop (preview).

Включение предварительной версии функции связей "многие ко многим"Enable the many-to-many relationships preview feature

Функция связей "многие ко многим" должна быть включена в Power BI Desktop.The many-to-many relationships feature must be enabled in Power BI Desktop. Чтобы включить составные модели, последовательно выберите пункты Файл > Параметры и настройки > Параметры > Функции предварительной версии и установите флажок Составные модели.To enable composite models, select File > Options and Settings > Options > Preview Features, and then select the Composite Models check box.

Панель "Функции предварительной версии"

Чтобы включить эту функцию, перезапустите Power BI Desktop.To enable the feature, you need to restart Power BI Desktop.

Окно "Для этой возможности требуется перезапуск"

Какие задачи решают связи "многие ко многим"What many-to-many relationships solves

Прежде чем связи "многие ко многим" стали доступны, связь между двумя таблицами определялась в Power BI.Before the many-to-many relationships feature became available, the relationship between two tables was defined in Power BI. Хотя бы один из столбцов таблиц, участвующих в связи, должен был содержать уникальные значения.At least one of the table columns involved in the relationship had to contain unique values. Часто столбцы не содержали уникальные значения.Often, though, no columns contained unique values.

Например, в двух таблицах может быть столбец со страной (Country), но значения Country не являются уникальными ни в одной из таблиц.For example, two tables might have had a column labeled Country, but the values of Country weren't unique in either table. Чтобы соединить такие таблицы, было необходимо обходное решение.To join such tables, it was necessary to create a workaround. Одно из возможных решений — ввести в модель дополнительные таблицы с необходимыми уникальными значениями.One such workaround might be to introduce into the model additional tables with the necessary unique values. С помощью связей "многие ко многим" такие таблицы можно соединять непосредственно с помощью связей с кратностью многие ко многим.With the many-to-many relationships feature, you can join such tables directly by using a relationship with a cardinality of Many to-Many.

Использование связей "многие ко многим"Use many-to-many relationships

При определении связи между двумя таблицами в Power BI необходимо определить кратность связи.When you define a relationship between two tables in Power BI, you must define the cardinality of the relationship. Например, связь между ProductSales и Product—с использованием столбцов ProductSales[ProductCode] и Product[ProductCode]— будет определена как многие к одному.For example, the relationship between ProductSales and Product—using columns ProductSales[ProductCode] and Product[ProductCode]—would be defined as Many-1. Мы определяем связь таким образом, так как есть много продаж для каждого продукта и столбец (ProductCode) таблицы Product является уникальным.We define the relationship in this way because there are many sales for each product, and the column in the Product table (ProductCode) is unique. При определении кратности связи как многие к одному, один ко многим или один к одному Power BI проверяет ее, чтобы обеспечить соответствие кратности фактическим данным.When you define a relationship cardinality as Many-1, 1-Many, or 1-1, Power BI validates it to help ensure that the cardinality that you select matches the actual data.

Например, рассмотрим простую модель на следующем изображении.For example, take a look at the simple model in the following image:

Представление связи

Теперь представим, что в таблице Product отображаются лишь две строки, как показано:Now, imagine that the Product table displays just two rows, as shown:

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

Также представьте таблицу Sales, содержащую всего четыре строки, включая строку для продукта C, которого не существует в таблице Product из-за ошибки ссылочной целостности.Also imagine that the Sales table has just four rows, including a row for a product C. Because of a referential integrity error, the product C row doesn't exist in the Product table.

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

Столбцы ProductName и Price (из таблицы Product), наряду с общим значением Qty (количество) для каждого продукта (из таблицы ProductSales), будут отображены, как показано на следующем изображении:The ProductName and Price (from the Product table), along with the total Qty for each product (from the ProductSales table), would be displayed as shown:

Визуальный элемент с названием продукта, ценой и количеством

Как показано на предыдущем рисунке, есть пустая строка ProductName, которая связана с продажами продукта C. Она отвечает за следующее:As you can see in the preceding image, there's blank ProductName row that's associated with sales for product C. This blank row accounts for the following:

  • Все строки таблицы ProductSales, для которых не существует соответствующей строки в таблице Product.Any rows in the ProductSales table for which no corresponding row exists in the Product table. Существует проблема целостности данных, как показано в этом примере для продукта C.There's a referential integrity issue, as we see for product C in this example.

  • Любые строки в таблице ProductSales, для которых столбец внешнего ключа имеет значение NULL.Any rows in the ProductSales table for which the foreign key column is null.

По этим причинам в обоих случаях пустая строка отвечает за продажи, в которых ProductName и Price неизвестны.For these reasons, the blank row in both cases accounts for sales where the ProductName and Price are unknown.

Но иногда бывают случаи, когда таблицы соединены по двум столбцам, и при этом ни один из столбцов не уникален.It sometimes happens that the tables are joined by two columns, yet neither column is unique. Например, рассмотрим следующие две таблицы:For example, consider the following two tables:

  • Таблица Sales содержит данные о продажах по штатам (State), где в каждой строке указан объем продаж, соответствующий типу продаж в указанном штатеThe Sales table displays sales data by State, and each row contains the sales amount for the type of sale in that state. (включая штаты CA, WA и TX).The states include CA, WA, and TX.

    Таблица продаж, отображение продаж по штатам

  • Таблица CityData содержит данные по городам, в том числе сведения о населении и штате (включая штаты Калифорния, Вашингтон и Нью-Йорк).The CityData table displays data on cities, including the population and state (including CA, WA, and New York).

    Таблица продаж, отображение города, штата и населения

Хотя в обеих таблицах есть столбец State и имеет смысл создать отчет по общим продажам по штатам, наряду с общей численностью населения в каждом штате, есть и одна проблема: столбец State не является уникальным ни в одной из таблиц.Although there's a column for State in both tables and it's reasonable to want to report on both total sales by state and total population of each state, a problem exists: the State column isn't unique in either table.

Использовавшийся ранее обходной путьThe previous workaround

В версиях Power BI Desktop, предшествующих выпуску от июля 2018 г., пользователи не могли создать прямую связь между такими таблицами.In earlier Power BI Desktop versions than the July 2018 release, users couldn't create a direct relationship between these tables. Обычным решением этой проблемы были следующие действия:A common workaround was to do the following:

  • Создание третьей таблицы, содержащей только уникальные идентификаторы штатов (State).Create a third table that contains only the unique State IDs. Это может быть:The table could be any or all of the following:

    • Вычисляемая таблица (определяется на языке выражений анализа данных [DAX]).A calculated table (defined by using Data Analysis Expressions [DAX]).
    • Таблица на основе запроса, который определен в редакторе запросов; он может отображать уникальные идентификаторы, извлеченные из одной из таблиц.A table based on a query that's defined in Query Editor, which could display the unique IDs drawn from one of the tables.
    • Объединенный полный набор.The combined full set.
  • Связывание двух исходных таблиц с этой новой таблицей производится с помощью обычных связей многие к одному.Relate the two original tables to that new table by using common Many-1 relationships.

Можно оставить дополнительную таблицу видимой или скрыть ее, чтобы она не отображалась в списке Поля.You could either leave the workaround table visible or hide it so that it doesn't appear in the Fields list. Если скрыть таблицу, обычно для связей многие к одному включается фильтрация в обоих направлениях, что дает возможность использовать State любой из таблиц.If you hide the table, the Many-1 relationships would commonly be set to filter in both directions, and you could use the State field from either table. Последующие кросс-фильтры распространяются и на другую таблицу.The subsequent cross filtering would propagate to the other table. Этот подход показан на следующем рисунке:That approach is shown in the following image:

Представление связи

Визуальный элемент с отображением поля State (из таблицы CityData) вместе с общей численностью населения (Population) и общим объемом продаж (Sales) будет выглядеть следующим образом.A visual that displays State (from the CityData table), along with total Population and total Sales, would then appear as follows:

Визуальный элемент таблицы

Примечание

При заданном использовании штата из таблицы CityData в решении в этой таблице перечисляются только соответствующие штаты (таким образом, штат Техас исключается).Because the state from the CityData table is used in this workaround, only the states in that table are listed and, therefore, TX is excluded. Кроме того, в отличие от связей многие к одному, хотя итоговая строка содержит весь объем продаж (Sales) (включая штат Техас), в подробные сведения не включается пустая строка, отвечающая за такие несовпадающие строки.Also, unlike Many-1 relationships, while the total row includes all Sales (including those of TX), the details do not include a blank row covering such mismatched rows. Аналогично, не будет пустой строки, соответствующей каким-либо продажам (Sales), которым соответствует значение NULL поля State.Similarly, there would be no blank row covering Sales for which there's a null value for the State.

Если также добавить City в этот визуальный элемент, хотя население в расчете на значение City известно, то значение Sales, показанное для City, просто повторит значение Sales для соответствующего штата State.If you also add City to that visual, although the population per City is known, the Sales shown for City simply repeats the Sales for the corresponding State. Это обычно происходит при группировании в столбце, не связанном с агрегатными мерами, как показано на следующем рисунке:This is normally the case when the grouping in a column is unrelated to some aggregate measure, as shown in the following image:

Визуальный элемент таблицы

Если мы определим новую таблицу Sales в качестве сочетания всех штатов в этом обходном решение и сделаем ее видимой в списке Поля, тот же визуальный элемент будет отображать штат (для новой таблицы), общее население и общие продажи, как показано на следующем рисунке:If we define the new Sales table as the combination of all States in this workaround and we make it visible in the Fields list, the same visual would display both State (on the new table) and the total Population and total Sales, as shown in the following image:

Визуальный элемент таблицы

Как вы видите, включен штат TX—с данными продаж, но неизвестными данными о населении,—и Нью-Йорк —с известным населением, но неизвестными данными о продажах—.As you can see, TX—with Sales data but unknown Population data—and New York—with known Population data but no Sales data—would be included. Этот способ не оптимален и вызывает множество проблем.This workaround isn't optimal, and it has many issues. С созданием связей "многие ко многим" эти проблемы решаются, как описано в следующем разделе.With the creation of the many-to-many relationships, the resulting issues are addressed as described in the next section.

Использование связей "многие ко многим" вместо описанного подходаUse many-to-many relationships instead of the workaround

Начиная с версии от июля 2018 г., в Power BI Desktop можно напрямую связывать таблицы, например те, которые мы упоминали выше, не прибегая к использованию аналогичных обходных путей.As of the July 2018 version of Power BI Desktop, you can directly relate tables, such as the ones we described earlier, without having to resort to similar workarounds. Теперь можно задать кратность связи многие ко многим.It's now possible to set the relationship cardinality to Many to Many. Этот параметр указывает, что ни одна из таблиц не содержит уникальные значения.This setting indicates that neither table contains unique values. При использовании таких связей по-прежнему можно указывать, какая таблица фильтрует другую таблицу, или установить двунаправленную (взаимную) фильтрацию для двух таблиц.For such relationships, you can still control which table filters the other table, or apply bi-directional filtering where each table filters the other.

Примечание

Возможность создания связи "многие ко многим" находится в предварительной версии.The ability to create many-to-many relationships is in preview. На этапе предварительной версии невозможно публиковать в службе Power BI модели, использующие связи "многие ко многим".While it's in preview, it's not possible to publish to the Power BI service models that use many-to-many relationships.

В Power BI Desktop по умолчанию задается кратность многие ко многим, когда программа определяет, что ни одна из таблиц не содержит уникальные значения столбцов, участвующих в связи.In Power BI Desktop, the cardinality defaults to Many to Many when it determines that neither table contains unique values for the columns in the relationship. В таких случаях отображается предупреждение для подтверждения того, что такая настройка связи является целевым поведением, а не непреднамеренным следствием проблемы с данными.In such cases, a warning is displayed to confirm that relationship-setting is your intended behavior and not the unintended effect of a data issue.

Например, при создании связи непосредственно между CityData и Sales—, где фильтры должны применяться от CityData к Sales, в —Power BI Desktop отображается следующее диалоговое окно изменения связи.For example, when you create a relationship directly between CityData and Sales—where filters should flow from CityData to Sales—Power BI Desktop displays the Edit relationship window as shown in the following image:

Диалоговое окно изменения связи

Итоговое представление связей будет содержать прямую связь "многие ко многим" между двумя таблицами.The resulting Relationship view would then display the direct, many-to-many relationship between the two tables. Внешний вид таблиц в списке Поля и их последующее поведение при создании визуальных элементов аналогично ситуации, в которой мы применили обходной путь.The tables' appearance in the Fields list, and their subsequent behavior when the visuals are created, is similar to when we applied the workaround. Там мы создали дополнительную скрытую таблицу уникальных штатов.In the workaround, the extra table that displays the distinct State data isn't made visible. Например, как описано в предыдущем разделе, визуальный элемент со штатами, населением и продажами будет отображаться следующим образом:For example, as described in the preceding section, a visual showing State, Population, and Sales data would be displayed as follows:

Визуальный элемент таблицы

Основные различия между связями многие ко многим и более распространенными связями многие к одному заключается в следующем.The major differences between many-to-many relationships and the more typical Many-1 relationships are as follows:

  • Значения, показываемые в них, не содержат пустую строку, отвечающую за несовпадающие строки в другой таблице.The values shown don't include a blank row that accounts for mismatched rows in the other table. Кроме того, значения не отвечают за строки, в которых столбец, используемый для связи в другой таблице, имеет значение NULL.Nor do the values account for rows where the column used in the relationship in the other table is null.

  • Использовать функцию RELATED() (так как связанными могут быть несколько строк) невозможно.It isn't possible to use the RELATED() function, because more than one row could be related.

  • При использовании функции ALL() в таблице не удаляются фильтры, примененные к другим таблицам, у которых с ней установлена связь "многие ко многим".Using the ALL() function on a table doesn't remove filters that are applied to other, related tables by a many-to-many relationship. В предыдущем примере мера, определенная согласно следующему скрипту, не удалит фильтры по столбцам связанной таблицы CityData:In the preceding example, a measure that's defined as shown in the following script would not remove filters on columns in the related CityData table:

    Пример скрипта

    Визуальный элемент с отображением штата (State), объема продаж (Sales) и общего объема продаж (Sales total) будет следующим:A visual showing State, Sales, and Sales total data would result in the following:

    Визуальный элемент таблицы

Учитывая перечисленные различия, убедитесь, что вычисления, использующие ALL(\<Table>), такие как % от общей суммы, возвращают желаемые результаты.With the preceding differences in mind, make sure that the calculations that use ALL(\<Table>), such as % of grand total, are returning the intended results.

Рекомендации и ограниченияLimitations and considerations

В этом выпуске для связей "многие ко многим" и составных моделей есть несколько ограничений.There are a few limitations for this release of many-to-many relationships and composite models.

Следующие (многомерные) источники Live Connect невозможно использовать с составными моделями:The following Live Connect (multi-dimensional) sources can't be used with composite models:

  • SAP HANASAP HANA
  • SAP Business WarehouseSAP Business Warehouse
  • Службы SQL Server Analysis ServicesSQL Server Analysis Services
  • Наборы данных Power BIPower BI datasets
  • Azure Analysis ServicesAzure Analysis Services

При подключении к этим многомерным источникам в режиме DirectQuery невозможно одновременно подключиться к другому источнику DirectQuery или сочетать его с импортированными данными.When you connect to these multi-dimensional sources by using DirectQuery, you can't connect to another DirectQuery source or combine it with imported data.

Существующие ограничения для DirectQuery по-прежнему применяются при использовании связей "многие ко многим".The existing limitations of using DirectQuery still apply when you use many-to-many relationships. Многие из этих ограничений теперь относятся к отдельным таблицам в зависимости от режима хранения для каждой таблицы.Many of these limitations are now per table, depending upon the storage mode of the table. Например, вычисляемый столбец в импортированной таблице может ссылаться на другие таблицы, но вычисляемый столбец в таблице DirectQuery по-прежнему ограничен ссылками только на столбцы той же таблицы.For example, a calculated column on an imported table can refer to other tables, but a calculated column on a DirectQuery table can still refer only to columns on the same table. Если какие-то таблицы в пределах модели являются таблицами DirectQuery, к модели в целом применяются и другие ограничения.Other limitations apply to the model as a whole if any of the tables within the model are DirectQuery. Например, функции "Краткая аналитика" и "Вопросы и ответы" недоступны в модели, если какая-либо из таблиц в ней используется в режиме хранения DirectQuery.For example, the QuickInsights and Q&A features are unavailable on a model if any of the tables within it has a storage mode of DirectQuery.

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

Дополнительные сведения о составных моделях и DirectQuery см. в следующих статьях:For more information about composite models and DirectQuery, see the following articles: