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

Функция связей "многие ко многим" в Power BI Desktop позволяет соединять таблицы с использованием кратности многие ко многим, а также проще и быстрее создавать модели данных, содержащие несколько источников данных.With the Many-to-many relationship feature in Power BI Desktop you can join tables using a cardinality of Many to Many, and create data models that contain multiple data sources easier and more intuitively. Функция связей "многие ко многим" является частью более широкого набора возможностей составных моделей в Power BI Desktop.The many-to-many relationship 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 part of a collection of three related features:

  • Составные модели — позволяют включить в отчет несколько подключений к данным, в том числе подключения DirectQuery или импорт, в любой комбинации.Composite models - allows a report to have multiple data connections, including DirectQuery connections or import, in any combination thereof.
  • Связи "многие ко многим" — с составными моделями можно устанавливать между таблицами связи "многие ко многим". Эта устраняет требования уникальных значений в таблицах и необходимость в прежних обходных решениях, таких как введение новых таблиц только для установления связей.Many-to-many relationships - with composite models you can establish many-to-many relationships between tables, removing requirements for unique values in tables, and removing prior work-arounds such as introducing new tables just to establish relationships.
  • Режим хранения — теперь можно указать, какие визуальные элементы требуют запроса к внутренним источникам данных. Визуальные элементы, которым запрос не требуется, импортируются, даже если они основаны на DirectQuery. Это позволяет повысить производительность и снизить нагрузку на серверную часть решения.Storage mode - you can now specify which visuals require a query to back-end data sources, and those that do no require it are imported even if based on DirectQuery, improving performance and reducing back-end load. Ранее даже простые визуальные элементы, например срезы, инициировали запросы к серверным источникам.Previously, even simple visuals like slicers initiated queries being sent to back-end sources.

Все три связанные функции этой коллекции для составных моделей описаны в отдельных статьях:This collection of three related features for composite models are each described in separate articles:

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

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

Включение предварительной версии функций

Чтобы включить эту функцию, нужно перезапустить Power BI Desktop.You'll need to restart Power BI Desktop for the feature to be enabled.

запрос на перезагрузку для вступления изменений в силу

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

До появления связей "многие ко многим" при определении связи между двумя таблицами в Power BI как минимум один из столбцов. участвующих в связи, должен был содержать уникальные значения.Prior to availability of many-to-many relationships, when defining a relationship between two tables in Power BI, at least one of the columns involved in the relationship had to contain unique values. Но во многих случаях ни один из столбцов таблицы не содержал уникальные значения.In many circumstances though, no column in the table contained unique values.

Например, в двух таблицах может быть столбец со страной (Country), но значения Country не являются уникальными ни в одной из таблиц.For example, two tables may have a column containing the Country, but the values of Country were not unique in either table. Для соединения этих таблиц необходимо было использовать другие решения, например добавлять в модель дополнительные таблицы, содержащие необходимые уникальные значения.To join between such tables, it was necessary to create a workaround such as introducing additional tables into the model that contained the necessary unique values. Функция связей "многие ко многим" представляет альтернативный способ, позволяя соединять такие таблицы непосредственно с помощью связи с кратностью многие ко многим.The feature many-to-many relationships provides an alternative approach, allowing such tables to be joined directly using a relationship with a cardinality of Many-to-many.

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

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

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

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

Представьте таблицу Product, содержащую всего две строки.Then imagine the Product table contained just two rows.

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

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

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

Визуальный элемент, который отображал ProductName и Price (из таблицы Product), наряду с общим значением Qty (количество) для каждого продукта (из таблицы ProductSales), будет отображен, как показано на следующем изображении:A visual that displayed ProductName and Price (from the Product table), along with the total Qty for each product (from the ProductSales table) would display as the following image shows:

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

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

  • Любые строки в таблице ProductSales, для которых нет соответствующих строк в таблице Product, так как есть проблема с целостностью данных, как показано для продукта C в этом примере.Any rows in the ProductSales table for which there is no corresponding row in the Product table - 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 неизвестны.Because of these reasons, in both cases the blank row accounts for sales where the ProductName and Price are unknown.

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

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

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

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

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

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

Предыдущее решение проблемыThe prior workaround

В версиях Power BI Desktop до июльского (2018) выпуска нельзя было создать связь непосредственно между этими таблицами.In versions of Power BI Desktop prior to the July 2018 release, it wasn't possible to create a relationship directly between these tables. Обычным решением этой проблемы были следующие действия:A common workaround to this problem was to do the following:

  • Создание третьей таблицы, содержащей только уникальные идентификаторы штатов (State).Create a third table containing just the unique State ids. Это могла быть либо вычисляемая таблица (определенная с помощью DAX), либо таблица, определенная с помощью запроса, в свою очередь определенная в редакторе запросов, которая могла содержать уникальные идентификаторы из одной из таблиц или полный объединенный набор.This could be either a calculated table (defined using DAX), or a table defined using a query defined in Query Editor which could contain the unique ids drawn from one of the tables, or the unioned full set.

  • Связывание двух исходных таблиц с этой новой таблицей с помощью обычных связей *многие-1.Relate the two original tables to that new table, using common *Many-1 relationships.

Такая таблица могла оставаться видимой или быть скрытой для предотвращения ее отображения в списке полей.That workaround table could either be left visible, or be hidden such that it does not appear in the field list. В последнем случае связи многие к одному могли быть обычным образом заданы для фильтрации в обоих направлениях, чтобы можно было использовать поле State из любой таблицы с последующим распространением перекрестной фильтрации на другую таблицу.In the latter case, the Many-1 relationships would commonly be set to filter in both directions, such that the State field from either table could be used, with subsequent cross filtering propagating to the other table. Такой подход демонстрируется на следующем рисунке с представлением связей.That workaround approach is shown in the following image of the Relationship view.

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

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

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

Обратите внимание, что при заданном использовании штата из таблицы CityData в решении в этой таблице перечисляются только соответствующие штаты (State) (таким образом, штат Техас исключается).Note that given the use of the state from the CityData table in this workaround, only those States in that table are listed (and thus, TX is excluded). Кроме того, в отличие от связей многие к одному, хотя итоговая строка содержит весь объем продаж (Sales) (включая штат Техас), в подробные сведения не включается пустая строка, охватывающая такие несовпадающие строки.Also, unlike the case of 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 any Sales for which there was a null value for the State.

Если в визуальный элемент также добавлен город (City), то при известной численности населения для каждого City отображаемый объем продаж (Sales) для поля City просто повторит число Sales для соответствующего штата (State), как это обычно бывает при группировании по столбцу, не связанному с каким-либо статистическим показателем. Это показано на следующем рисунке.If City were also added to that visual, then while the population per City is known, the Sales shown for City would simply repeat the Sales for the corresponding State (as is normally the case when grouping on a column that is not related to some aggregate measure), as shown in the following image.

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

Если бы новая таблица Sales была определена как объединение всех столбцов States в этом решении и была видимой в списке полей, такой же визуальный элемент с отображением поля State (в новой таблице) наряду с общей численностью населения (Population) и общим объемом продаж (Sales) выглядел бы следующим образом.If the new table Sales were defined to be the union of all States in this workaround, and made visible in the field list, then the same visual showing State (on the new table) along with the total Population and total Sales would then be as follows.

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

В этом случае в визуальный элемент были бы включены TX (со значением Sales, но без известной численности населения) и New York (с известным населением, но без значения Sales).In that case and shown in the visual, TX (with Sales but unknown population), and New York (with a known population but no Sales) would be included.

Как видим, решение не оптимально и содержит много проблем.As you can see, this workaround was not optimal and has many issues. С созданием связи "многие ко многим" эти проблемы решаются, как описано в следующем разделе.With the creation of the many-to-many relationship, these issues are addressed, as described in the following section.

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

В версиях Power BI Desktop, начиная с июля 2018 г., можно напрямую связать таблицы, описанные в предыдущем разделе, без использования описанного подхода.In versions of Power BI Desktop starting with July 2018, you can directly relate such tables described in the previous section without needing to resort to such workarounds. Теперь можно задать кратность связи многие ко многим, указав тем самым, что ни одна из таблиц не содержит уникальные значения.It's now possible to set the cardinality of a relationship to Many to Many, indicating that neither table contains unique values. При использовании таких связей по-прежнему можно указывать, какая таблица фильтрует другую таблицу, или установить двунаправленную (взаимную) фильтрацию для двух таблиц.For such relationships, you can still control which table filters the other table, or to have bi-directional filtering where both tables filter each other.

Примечание

Возможность создавать связи "многие ко многим" доступна в режиме предварительной версии, поэтому вы не можете публиковать модели с использованием связей "многие ко многим" в службе Power BI.The ability to create Many to Many relationships is in Preview, and while it Preview, it's not possible to publish models using Many to Many relationships to the Power BI service.

В Power BI Desktop по умолчанию задается кратность многие ко многим, когда программа определяет, что ни одна из таблиц не содержит уникальные значения столбцов, участвующих в связи.In Power BI Desktop, cardinality defaults to Many to Many when it's determined 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, rather than being the unintended effect of a data issue.

Например, при создании связи непосредственно между CityData и Sales, где фильтры должны применяться от CityData к Sales, отображается следующее диалоговое окно изменения связи.For example, in creating a relationship directly between CityData and Sales, where Filters should flow from CityData to Sales, the relationship dialog appear as showin in the following image.

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

Итоговое представление связей будет содержать прямую связь многие ко многим между двумя таблицами.The resulting Relationship View would then contain the direct Many to Many relationship between the two tables. Вид списка Поля и последующее поведение при создании визуальных элементов будут аналогичны применению подхода, описанного в предыдущем разделе, где дополнительная таблица (с отличающимися значениями поля State) не сделана видимой.The appearance in the Fields list, and subsequent behavior when visuals are created, is then the same as employing the workaround described in the previous section, where the extra table (with the distinct States in it) is not made visible. Например, как показано в предыдущем разделе, визуальный элемент с отображением полей State наряду с общей численностью населения и объемами продаж, будет выглядеть следующим образом.For example, as in the previous section describing the workaround, a visual showing States along with total population and sales would be as follows.

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

Таким образом, основные различия между связями многие ко многим и более распространенными связями многие к одному заключается в следующем.So the major difference between Many to Many relationships and the more typical Many-1 relationships are the following.

  • Отображаемые значения не включают пустую строку, соответствующую всем несовпадающим строкам в другой таблице, равно как и строки, где используемый в связи столбец в другой таблице имеет значение Null.The values shown do not include a blank row accounting for any mismatched rows in the other table, nor for rows where the column used in the relationship in the other table is null.

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

  • При использовании функции ALL() в таблице не удаляются фильтры, примененные к другим таблицам, у которых с ней установлена связь многие ко многим.Using the ALL() function on a table will not remove filters applied to other tables related to it by a Many to Many relationship. Например, показатель, определенный в предыдущем примере, не удалит фильтры по столбцам связанной таблицы CityData:For example, a measure defined as the following in the previous example would not remove filters on columns on the related CityData table:

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

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

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

По этой причине следует внимательно следить, чтобы расчеты с использованием функции ALL(<таблица>), например % от общей суммы, возвращали желаемые результаты.Because of this, care should be taken to ensure that calculations using 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 (multidimensional) sources cannot 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 connecting to those multidimensional sources using DirectQuery, you cannot also connect to another DirectQuery source, nor combine with imported data.

Существующие ограничения для DirectQuery по-прежнему применяются при использовании связей "многие ко многим".The existing limitations of using DirectQuery still apply when using many-to-many relationships. Многие из этих ограничений теперь относятся к таблице в зависимости от режима хранения этой таблицы.Many of those 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 is still restricted to 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 not available on a model if any of the tables within it has a storage mode of DirectQuery.

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

В следующих статьях содержатся дополнительные сведения о составных моделях, а также подробно описан режим DirectQuery:The following articles describe more about composite models, and also describe DirectQuery in detail.

Статьи о DirectQuery:DirectQuery articles: