Применение связей "многие ко многим" в Power BI DesktopApply many-many relationships in Power BI Desktop

С помощью связей с кратностью "многие ко многим" в Power BI Desktop можно соединять таблицы, использующие кратность многие ко многим.With relationships with a many-many cardinality 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.Relationships with a many-many cardinality are part of the larger composite models capabilities in Power BI Desktop.

Связь "многие ко многим" в области "Изменение связи", Power BI Desktop

Создание связей с кратностью "многие ко многим" в Power BI Desktop реализовано в наборе из трех связанных функций.A relationship with a many-many cardinality in Power BI Desktop is composed of one of three related features:

  • Составные модели. Составные модели позволяют включить в отчет несколько подключений к данным в любом сочетании, в том числе подключения DirectQuery или импорт.Composite models: A composite model allows a report to have two or more data connections, including DirectQuery connections or Import, in any combo. Дополнительные сведения см. в статье Использование составных моделей в Power BI Desktop.For more information, see Use composite models in Power BI Desktop.

  • Связи с кратностью "многие ко многим" . В составных моделях между таблицами можно установить связи с кратностью "многие ко многим" .Relationships with a many-many cardinality: With composite models, you can establish relationships with a many-many cardinality 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.

Проблемы, решаемые связями с кратностью "многие ко многим"What a relationship with a many-many cardinality solves

Прежде чем стали доступны связи с кратностью "многие ко многим" , связь между двумя таблицами определялась в Power BI.Before relationships with a many-many cardinality 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.

Например, две таблицы могли иметь столбец "Страна".For example, two tables might have had a column labeled Country. Однако значения стран не уникальны в одной таблице.The values of Country weren't unique in either table, though. Чтобы соединить такие таблицы, требовалось обходное решение.To join such tables, you had to create a workaround. Одним из возможных решений мог быть ввод дополнительных таблиц с необходимыми уникальными значениями.One workaround might be to introduce extra tables with the needed unique values. С помощью связей с кратностью "многие ко многим" такие таблицы можно соединять напрямую с помощью связей с кратностью многие ко многим.With relationships with a many-many cardinality, you can join such tables directly, if you use a relationship with a cardinality of many-to-many.

Использование связей с кратностью "многие ко многим"Use relationships with a many-many cardinality

При определении связи между двумя таблицами в 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 each product has many sales, 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, so the cardinality that you select matches the actual data.

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

ProductSales и таблица Product, представление "Связи", Power BI Desktop

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

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

Также представьте таблицу 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.

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

Столбцы 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:

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

Как показано на предыдущем рисунке, есть пустая строка ProductName, которая связана с продажами продукта C. Она отвечает за следующее.As you can see in the preceding image, a blank ProductName row is 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.

Бывают случаи, когда таблицы соединены по двум столбцам и при этом ни один из столбцов не уникален.Sometimes the tables are joined by two columns, yet neither column is unique. Например, рассмотрим следующие две таблицы.For example, consider these 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.

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

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

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

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

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

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

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

    • Вычисляемая таблица (определяется на языке выражений анализа данных [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.
  • Затем с помощью обычных связей многие к одному выполняется связывание двух исходных таблиц с этой новой таблицей.Then relate the two original tables to that new table by using common Many-1 relationships.

Можно оставить таблицу возможного решения видимой.You could leave the workaround table visible. Кроме того, можно скрыть таблицу возможного решения, чтобы она не отображалась в списке Поля.Or you may hide the workaround table, so it doesn't appear in the Fields list. Если скрыть таблицу, обычно для связей многие к одному включается фильтрация в обоих направлениях, что дает возможность использовать поле "Штат" любой из таблиц.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 later cross filtering would propagate to the other table. Этот подход показан на следующем рисунке:That approach is shown in the following image:

Скрытая таблица "Штат", представление "Связи", Power BI Desktop

Визуальный элемент с отображением поля 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:

Таблицы штата, населения и продаж, Power BI Desktop

Примечание

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

Предположим, что вы также добавляете "Город" в этот визуальный элемент.Suppose 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 scenario normally occurs when the column grouping is unrelated to some aggregate measure, as shown here:

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

Предположим, что вы определили новую таблицу "Продажи" как сочетание штатов здесь и сделали ее видимой в списке Поля.Let's say you define the new Sales table as the combination of all States here, and we make it visible in the Fields list. Тот же визуальный элемент будет отображать штат (в новой таблице), общее население и общий объем продаж.The same visual would display State (on the new table), the total Population, and total Sales:

Визуальный элемент со сведениями о штате, населении и продажах, Power BI Desktop

Как вы видите, включен штат 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. Связи с кратностью "многие ко многим" позволяют решить эти проблемы, как описано в следующем разделе.For relationships with a many-many cardinality, the resulting issues are addressed, as described in the next section.

Использование связей с кратностью "многие ко многим" вместо временного решенияUse a relationship with a many-many cardinality instead of the workaround

Начиная июльской версии 2018 г., в Power BI Desktop можно напрямую связывать таблицы, например те, которые мы упоминали выше, не прибегая к использованию аналогичных временных решений.With 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 may still control which table filters the other table. Либо можно применить двунаправленную фильтрацию, где каждая таблица фильтрует другую.Or you can apply bidirectional filtering, where each table filters the other.

В Power BI Desktop по умолчанию задается кратность многие ко многим, когда программа определяет, что ни одна из таблиц не содержит уникальные значения столбцов связи.In Power BI Desktop, the cardinality defaults to many-to-many when it determines neither table contains unique values for the relationship columns. В таких случаях отображается предупреждение с сообщением о том, что вы хотите установить связь, и изменение не является случайным результатом проблемы с данными.In such cases, a warning message confirms you want to set a relationship, and the change isn't 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 dialog box:

Диалоговое окно "Изменение связи", Power BI Desktop

Итоговое представление связей будет содержать прямую связь "многие ко многим" между двумя таблицами.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 later behavior when the visuals are created, are similar to when we applied the workaround. Там мы создали дополнительную скрытую таблицу уникальных штатов.In the workaround, the extra table that displays the distinct State data isn't made visible. Как было сказано выше, отображается визуальный элемент со сведениями о штате, населении и продажах.As described earlier, a visual that shows State, Population, and Sales data would be displayed:

Таблицы штата, населения и продаж, Power BI Desktop

Основные различия между связями с кратностью "многие ко многим" и более распространенными связями многие к одному заключается в следующем.The major differences between relationships with a many-many cardinality 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.Also, the values don't account for rows where the column used in the relationship in the other table is null.

  • Использовать функцию RELATED() (так как связанными могут быть несколько строк) не удастся.You can't 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 here wouldn't remove filters on columns in the related CityData table:

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

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

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

Учитывая перечисленные различия, убедитесь, что вычисления, использующие ALL(<Table>), такие как % от общей суммы, возвращают желаемые результаты.With the preceding differences in mind, make sure 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 relationships with a many-many cardinality and composite models.

Следующие (многомерные источники) Live Connect нельзя использовать с составными моделями.The following Live Connect (multidimensional) 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 multidimensional 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 relationships with a many-many cardinality. Многие ограничения теперь относятся к отдельным таблицам в зависимости от режима хранения для каждой таблицы.Many 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 whole model if any tables within the model are DirectQuery. Например, функции "Краткая аналитика" и "Вопросы и ответы" недоступны в модели, если какая-либо из таблиц в ней используется в режиме хранения DirectQuery.For example, the QuickInsights and Q&A features are unavailable on a model if any table within it has a storage mode of DirectQuery.

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

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