Связи между моделями в Power BI Desktop

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

Для более подробного обсуждения оптимальной структуры модели, включая роли таблиц и связи, см. статью «Понимание схемы звезды и её важность для Power BI».

Назначение отношения

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

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

Это важно

Связи модели не обеспечивают целостность данных. Дополнительные сведения см. в разделе оценки отношений далее в этой статье, в котором объясняется, как работают связи модели при возникновении проблем с целостностью данных.

Вот как отношения распространяют фильтры с помощью анимированного примера.

В этом примере модель состоит из четырех таблиц: "Категория", " Продукт", " Год" и "Продажи". Таблица "Категория " относится к таблице Product , а таблица Product относится к таблице Sales . Таблица Year также относится к таблице Sales . Все связи имеют тип «один ко многим» (подробности описаны далее в этой статье).

Запрос, возможно, созданный визуальным элементом карты Power BI, требует общее количество продаж для заказов из одной категории Cat-A за один год CY2018. Именно поэтому можно просмотреть фильтры, примененные к таблицам категории и года . Фильтр таблицы "Категория " распространяется на таблицу Product , чтобы изолировать два продукта, назначенных категории Cat-A. Затем фильтры таблицы Product распространяют в таблицу Sales , чтобы изолировать только две строки продаж для этих продуктов. Эти две строки продаж представляют продажи продуктов, назначенных категории Cat-A. Их объединенное количество составляет 14 единиц. В то же время фильтр таблицы Year распространяется для дальнейшей фильтрации таблицы Продаж, что приводит только к одной строке, которая относится к продуктам, назначенным категории Cat-A, и заказанного в году CY2018. Значение количества, возвращаемое запросом, равно 11 единицам. При применении нескольких фильтров к таблице (например, таблице Sales в этом примере) это всегда операция AND, требующая, чтобы все условия были верными.

Применение принципов проектирования схемы звезд

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

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

Отключенные таблицы

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

Параметр Power BI Desktop what-if — это функция, которая создает отключенную таблицу. Дополнительные сведения см. в статье "Создание и использование параметра What if для визуализации переменных в Power BI Desktop".

Свойства отношений

Связь модели связывает один столбец таблицы с одним столбцом в другой таблице. (Существует один специализированный случай, когда это требование не соответствует действительности, и оно применяется только к связям с несколькими столбцами в моделях DirectQuery. Дополнительные сведения см. в статье о функции COMBINEVALUES DAX.)

Замечание

Невозможно связать столбец с другим столбцом в одной таблице. Эту концепцию иногда путают с возможностью определения ограничения внешнего ключа реляционной базы данных, когда таблица ссылается на саму себя. Эту концепцию реляционной базы данных можно использовать для хранения отношений "родитель-дочерний" (например, каждая запись сотрудника связана с сотрудником, которому он подчиняется). Однако нельзя использовать связи модели для создания иерархии моделей на основе этого типа отношений. Сведения о создании иерархии родительских и дочерних элементов см. в разделе "Родительские и дочерние функции".

Типы данных столбцов

Тип данных для столбцов "from" и "to" в связи должен быть одинаковым. Работа со связями, определёнными в столбцах DateTime, может работать не так, как ожидалось. Подсистема, в которой хранятся данные Power BI, использует только типы данных DateTime ; Типы данных даты, времени и даты и часового пояса — это конструкции форматирования Power BI, реализованные сверху. Все объекты, зависящие от модели, по-прежнему будут отображаться как DateTime в обработчике (например, связи, группы и т. д.). Таким образом, если пользователь выбирает дату на вкладке "Моделирование " для таких столбцов, они по-прежнему не регистрируются в качестве той же даты, так как часть времени данных по-прежнему рассматривается подсистемой. Дополнительные сведения о том, как обрабатываются типы даты и времени. Чтобы исправить поведение, типы данных столбцов следует обновить в редакторе Power Query , чтобы удалить часть времени из импортированных данных, поэтому при обработке данных обработчиком значения будут одинаковыми.

Мощность

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

Замечание

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

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

• Один ко многим (1:*)
• Многие к одному (*:1)
• Один к одному (1:1)
• Многие ко многим (*:*)

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

Кардинальность "один ко многим" (и "многие ко одному")

Варианты кратности "один ко многим" и "многие ко многим" по сути одинаковы, и они также являются наиболее распространенными типами кратности.

При настройке связи "один ко многим" или "многие к одному" выберите ту, которая соответствует порядку, в котором связаны столбцы. Рассмотрим, как настроить связь из таблицы Product в таблицу Sales с помощью столбца ProductID, найденного в каждой таблице. Тип кратности будет один ко многим, так как столбец ProductID в таблице Product содержит уникальные значения. Если вы связали таблицы в обратном направлении, Продажи к Продукт, то кратность будет многие к одному.

Кардинальность "один к одному"

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

Дополнительные сведения об использовании этого типа кратности см. в руководстве по отношениям "один к одному".

Кардинальность "многие ко многим"

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

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

Замечание

Тип кратности "многие ко многим" поддерживается для моделей, разработанных для сервера отчетов Power BI за январь 2024 г. и более поздних версий.

Подсказка

В представлении модели Power BI Desktop можно интерпретировать тип кратности связи, просматривая индикаторы (1 или *) на любой стороне линии связи. Чтобы определить, какие столбцы связаны, выберите или наведите курсор на линию отношений, чтобы выделить столбцы.

Направление перекрестного фильтра

Каждая связь модели определяется с перекрестным направлением фильтрации. Ваша настройка определяет направление(я), в котором будут распространяться фильтры. Возможные параметры перекрестного фильтра зависят от типа кардинальности.

Тип кратности	Параметры перекрестного фильтра
Один ко многим (или многие к одному)	Single Both
One-to-one	Both
Many-to-many	Одиночный (Table1 to Table2) Одинарный (Таблица2 к Таблице1) Both

Направление единого перекрестного фильтра означает "одно направление", а оба — "оба направления". Связь, которая фильтрует в обоих направлениях, обычно описывается как двунаправленная.

Для связей "один ко многим" направление перекрёстной фильтрации всегда идёт от "одной" стороны и, при необходимости, от стороны "многие" (то есть двунаправленное). Для связей «один к одному» направление перекрёстной фильтрации всегда идёт от обеих таблиц. Наконец, для связей "многие ко многим" перекрестное направление фильтрации может быть из одной из таблиц или из обеих таблиц. Обратите внимание, что если тип кратности включает "одну" сторону, фильтры всегда будут распространяться с этой стороны.

Если для направления перекрестного фильтра задано значение "Оба", другое свойство становится доступным. Он может применять двунаправленную фильтрацию, если Power BI применяет правила безопасности на уровне строк (RLS). Дополнительные сведения о RLS см. в разделе "Безопасность на уровне строк" (RLS) с помощью Power BI Desktop.

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

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

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

Подсказка

В представлении модели Power BI Desktop можно интерпретировать направление перекрестной фильтрации связи, замечая стрелки вдоль линии связи. Одна стрелка представляет фильтр с одним направлением в направлении стрелки; двойная стрелка представляет двунаправленную связь.

Сделать эту связь активной

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

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

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

• Нет необходимости одновременно фильтровать визуальные компоненты отчета по разным ролям.
• Функция DAX USERELATIONSHIP используется для активации конкретного отношения для соответствующих вычислений модели.

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

Подсказка

В представлении модели Power BI Desktop можно интерпретировать разницу между активным и неактивным состояниями связи. Сплошная линия представляет активную связь, а тиреная строка представляет неактивную связь.

Будем считать, что обеспечивается ссылочная целостность

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

При включении собственные запросы, отправляемые в источник данных, будут объединять две таблицы вместе с помощью INNER JOIN вместо .OUTER JOIN Как правило, включение этого свойства повышает производительность запросов, хотя оно зависит от особенностей источника данных.

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

Это важно

Если целостность данных должна стать скомпрометированной, внутреннее соединение устраняет несовпаденные строки между таблицами. Например, рассмотрим таблицу sales модели со значением столбца ProductID , который не существовал в связанной таблице Product . Распространение фильтра из таблицы Product в таблицу Sales исключает строки продаж для неизвестных продуктов, что приводит к занижению результатов продаж.

Для получения дополнительной информации см. в разделе "Настройки ссылочной целостности в Power BI Desktop".

Соответствующие функции DAX

Существует несколько функций DAX, относящихся к отношениям модели. Каждая функция кратко описана в следующем списке:

• RELATED: извлекает значение из "одной" стороны связи. Это полезно при использовании вычислений из разных таблиц, оцениваемых в контексте строки.
• RELATEDTABLE: получение таблицы строк из "многие" связи.
• USERELATIONSHIP: позволяет вычислению использовать неактивную связь. (Технически эта функция изменяет вес определенного неактивного отношения модели, что помогает влиять на его использование.) Это полезно, если ваша модель включает таблицу роли-дименсий, и вы решили создать неактивные связи, используя эту таблицу. Эту функцию также можно использовать для разрешения неоднозначности в путях фильтрации.
• CROSSFILTER: изменяет направление перекрестного фильтра (на одно или оба), или отключает распространение фильтра (ни одно). Это полезно, если необходимо изменить или игнорировать связи модели во время оценки определенного вычисления.
• COMBINEVALUES: объединяет две или несколько текстовых строк в одну текстовую строку. Эта функция предназначена для поддержки связей с несколькими столбцами в моделях DirectQuery, когда таблицы относятся к одной исходной группе.
• TREATAS: применяет результат табличного выражения в качестве фильтров к столбцам из не связанной таблицы. Это полезно в сложных сценариях, когда вы хотите создать виртуальную связь во время оценки определенного вычисления.
• Родительские и дочерние функции: семейство связанных функций, которые вы можете использовать для создания вычисляемых столбцов с целью упрощения иерархии типа "родитель-ребёнок". Затем эти столбцы можно использовать для создания иерархии фиксированного уровня.

Оценка связей

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

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

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

Однако составная модель может содержать таблицы с помощью различных режимов хранения (импорт, DirectQuery или двойной) или нескольких источников DirectQuery. Каждый источник, включая кэш Vertipaq импортированных данных, считается исходной группой. Затем связи модели можно классифицировать как внутригрупповыми или межгрупповыми. Связь внутри исходной группы связывает две таблицы в одной исходной группе, в то время как межгрупповая/перекрестная связь связывает таблицы между двумя исходными группами. Обратите внимание, что связи в моделях импорта или DirectQuery всегда находятся внутри исходной группы.

Ниже приведен пример составной модели.

В этом примере составная модель состоит из двух исходных групп: исходной группы Vertipaq и исходной группы DirectQuery. Исходная группа Vertipaq содержит три таблицы, а исходная группа DirectQuery содержит две таблицы. Одна связь между исходными группами существует для связи таблицы в исходной группе Vertipaq с таблицей в исходной группе DirectQuery.

Регулярные связи

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

В следующем примере есть две регулярные связи, которые помечены как R. Связи включают связь "один ко многим", содержащуюся в исходной группе Vertipaq, и связь "один ко многим", содержащаяся в источнике DirectQuery.

Для моделей импорта, где все данные хранятся в кэше Vertipaq, Power BI создает структуру данных для каждой регулярной связи во время обновления данных. Структуры данных состоят из индексированных сопоставлений всех значений столбцов и их назначение — ускорить присоединение таблиц во время запроса.

Во время запроса регулярные связи позволяют выполнять расширение таблицы . Расширение таблицы приводит к созданию виртуальной таблицы, включив собственные столбцы базовой таблицы, а затем расширяя связанные таблицы. Для импорта таблиц расширение таблицы выполняется в обработчике запросов; для таблиц DirectQuery он выполняется в собственном запросе, который отправляется в исходную базу данных (если свойство "Предположим, целостность ссылок " не включено). Затем обработчик запросов действует в развернутой таблице, применяя фильтры и группирование по значениям в развернутых столбцах таблицы.

Замечание

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

Для отношений типа "один ко многим" расширение таблицы происходит от стороны "многие" к стороне "один" с помощью семантики LEFT OUTER JOIN. Если соответствующее значение на стороне "многие" для "одной" стороны отсутствует, в таблицу "одной" стороны добавляется пустая виртуальная строка. Это поведение применяется только к регулярным отношениям, а не к ограниченным отношениям.

Расширение таблицы также происходит для связей одно к одному внутри исходной группы, но с помощью семантики FULL OUTER JOIN . Этот тип соединения гарантирует, что пустые виртуальные строки добавляются на любой стороне при необходимости.

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

Ниже показано, как расширение таблицы работает с анимированным примером.

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

Ограниченные связи

Связь модели считается ограниченной, если нет гарантии на существование "единственной" стороны. Ограниченная связь может произойти по двум причинам:

• Связь использует тип кратности "многие ко многим" (даже если один или оба столбца содержат уникальные значения).
• Связь является межисточниковой группой (что может быть только в случае составных моделей).

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

Для моделей импорта структуры данных никогда не создаются для ограниченных связей. В этом случае Power BI выполняет объединение таблиц во время выполнения запроса.

Расширение таблицы никогда не происходит для ограниченных связей. Соединения таблиц достигаются с помощью семантики INNER JOIN , и по этой причине пустые виртуальные строки не добавляются для компенсации нарушений целостности ссылок.

Существуют другие ограничения, связанные с ограниченными связями:

• Функция RELATED DAX не может использоваться для получения значений столбцов на стороне "один".
• Применение RLS имеет ограничения топологии.

Подсказка

В представлении модели Power BI Desktop можно интерпретировать связь как ограниченную. Ограниченная связь представлена с круглыми знаками () после индикаторов кратности.

Разрешение неоднозначности пути связи

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

Priority

Уровни приоритетов определяют последовательность правил, которые Power BI использует для разрешения неоднозначности пути связи. Первое соответствие правил определяет путь Power BI. В следующей последовательности каждое правило описывает поток фильтров из исходной таблицы в целевую таблицу.

1. Путь, состоящий из связей "один ко многим".
2. Путь, состоящий из связей "один ко многим" или "многие ко многим".
3. Путь, состоящий из взаимоотношений типа "многие к одному".
4. Путь, состоящий из связей "один ко многим" из исходной таблицы в промежуточную таблицу, за которой следует связи "многие ко одному" из промежуточной таблицы в целевую таблицу.
5. Путь, состоящий из связей "один ко многим" или "многие ко многим" от исходной таблицы к промежуточной таблице, за которыми следуют связи "многие к одному" или "многие ко многим" от промежуточной таблицы к целевой таблице.
6. Любой другой путь.

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

Weight

Каждая связь в пути имеет вес. По умолчанию каждый вес связи равен, если функция USERELATIONSHIP не используется. Вес пути — это максимальное количество всех весов связи вдоль пути. Power BI использует весы путей для разрешения неоднозначности между несколькими путями в одном и том же уровне приоритета. Он не выберет путь с более низким приоритетом, но он выберет путь с более высоким весом. Количество взаимосвязей в маршруте не влияет на вес.

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

Рассмотрим следующий пример. Мера «Продажи продукта» назначает более высокий вес отношению между Sales[ProductID] и Product[ProductID], а затем отношению между Inventory[ProductID] и Product[ProductID].

Product Sales = 
CALCULATE(
    CALCULATE(
        SUM(Sales[SalesAmount]), 
        USERELATIONSHIP(Sales[ProductID], Product[ProductID])
    ),
    USERELATIONSHIP(Inventory[ProductID], Product[ProductID])
)

Замечание

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

Предпочтения производительности

В следующем списке перечислены показатели производительности распространения фильтров от самых быстрых до самых медленных:

1. Взаимоотношения типа один ко многим внутри исходной группы источников
2. Модельные связи "многие ко многим", реализованные с помощью промежуточной таблицы и включающие как минимум одну двунаправленную связь
3. Связи кратности "многие ко многим"
4. Взаимосвязи между исходными группами

Связанный контент

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

• Общие сведения о схеме звезд и важности Power BI
• Руководство по отношениям "один к одному"
• Руководство по связям "многие ко многим"
• Руководство по активным и неактивным отношениям
• Руководство по двунаправленным отношениям
• Руководство по устранению неполадок связи
• Видео: Что делать и чего не делать в отношениях Power BI
• Вопросы? Попробуйте обратиться к сообществу Power BI
• Есть предложения? Участие в разработке идей по улучшению Power BI