Применение связей «многие ко многим» в power bi desktopapply many-many relationships in power bi desktop

Создание и изменение отношений 1:N между сущностями

Откройте обозреватель решений.

В разделе Компоненты раскройте узел Сущности, затем раскройте сущность, с которой требуется работать.

Выберите Отношения 1:N.

Чтобы изменить отношение или просмотреть сведения для отношения, выберите отношение и нажмите на панели инструментов «Действия» кнопку Другие действия, затем выберите Изменить.
— ИЛИ —
Чтобы добавить новое отношение, выберите Создать отношение «один ко многим».

Важно!
Если кнопка Создать отношение «один ко многим» не отображается на панели инструментов «Действия», то создать отношение 1:N для этой сущности невозможно.

Для нового отношения в разделе Определение отношения выберите в списке Связанная сущность сущность для связывания.

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

Выберите, будет ли это поле доступно для поиска или нет.

В разделе Поле поиска укажите значение для поля в поле Отображаемое имя.

Важно!
При указании значения Отображаемое имя задается значение по умолчанию в поле Имя

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

В списке Требование поля выберите вариант, чтобы указать требования к данным для поля перед сохранением записи.

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

В разделе Поведение отношений выберите в списке Тип отношений один из следующих вариантов.

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

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

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

Настраиваемое каскадное. В настраиваемом каскадном отношении между двумя сущностями выбирается поведение, связанное с каждым из наборов возможных действий.

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

Дополнительные сведения:

1. Выберите Сохранить и закрыть, чтобы закрыть форму Отношение.

2. Выполнив настройки, опубликуйте их:

   • Чтобы опубликовать настройки только для компонента, изменяемого в данный момент, на панели инструментов «Действия» выберите Опубликовать.

   • Чтобы опубликовать настройки для всех неопубликованных компонентов одновременно, на панели навигации или в области переходов выберите Сущности, затем на панели инструментов «Действия» выберите Опубликовать все настройки.

Примечание

• Настраиваемая сущность не может быть основной в каскадном отношении со связанной системной сущностью. Это означает, что между основной настраиваемой сущностью и связанной системной сущностью не может быть отношений с каким-либо из действий, установленным в «Передавать всем», «Передавать активным» или «Передавать владельцу».
• У новых отношений действие не может иметь значение Передавать всем, Передавать активным или Передавать владельцу, если связанная сущность в этом отношении уже является связанной сущностью в любом другом отношении, действие которого имеет значение Передавать всем, Передавать активным или Передавать владельцу. Это позволяет избежать создания отношений с несколькими родительскими сущностями.
• После каждого изменения элементов пользовательского интерфейса или внедрения скриптов формы для сущности необходима публикация изменений. Все изменения в схеме данных приложения, таких как настраиваемые сущности, связи или поля, применяются сразу.
• Если отношение является частью управляемого решения, разработчик решения может ограничить настройку отношения пользователями.
• Установка решения или публикация настроек может помешать нормальной работе системы. Рекомендуется запланировать импорт решения в оптимальный для пользователей период.

Элемент области навигации для основной сущности

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

Использование связей с кратностью «многие ко многим» вместо временного решения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:

Итоговое представление связей будет содержать прямую связь «многие ко многим» между двумя таблицами.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:

Основные различия между связями с кратностью «многие ко многим» и более распространенными связями многие к одному заключается в следующем.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.

• Использовать функцию (так как связанными могут быть несколько строк) не удастся.You can’t use the function, because more than one row could be related.

• При использовании функции в таблице не удаляются фильтры, примененные к другим таблицам, у которых с ней установлена связь «многие ко многим».Using the 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:

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

Определение отношений

Отношения задаются в файле с классом модели. Пример:

Вы можете использовать конструктор запросов для работы с отношениями:

Или вы можете использовать свойства объекта:

Подробные определения отношений

Каждое определение — это массив, где ключ — название отношения, а значение — массив с его свойствами. Первый элемент такого массива — название класса модели, а остальные — дополнительные параметры.

Ниже приведены параметры, которые можно использовать со всеми отношениями:

Пример с использованием order и conditions:

Пример с использованием scope:

Пример с использованием count:

Целостность данных

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

• Совпадающие поля из основной таблицы являются первичным ключом или имеет уникальный индекс.
• Связанные поля имеют один и тот же тип данных. Из этого правила есть два исключения: Поле AutoNumber может быть связано с полем Number, которое имеет настройку свойства Long Integer, а поле AutoNumber, которое имеет   настройку свойства Replication ID, может быть связано с полем Number, которое имеет   настройку свойства Replication ID.
• Обе таблицы относятся к одной и той же базе данных Access. Если таблицы являются связанными таблицами, они должны быть таблицами в формате Access, и необходимо открыть базу данных, в которой они хранятся, чтобы установить целостность данных. Референтная целостность не может быть применена для связанных таблиц из баз данных в других форматах.

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

• Невозможно ввести значение во внешнем ключевом поле связанной таблицы, которое не существует в первичном ключе первичной таблицы. Тем не менее, можно ввести значение Null во внешнем ключе. Это указывает на то, что записи не связаны между собой. Например, невозможно иметь заказ, который назначается клиенту, который не существует. Тем не менее, можно иметь заказ, который не назначается никому, введя значение Null в поле CustomerID.
• Вы не можете удалить запись из основной таблицы, если в соответствующей таблице существуют соответствующие записи. Например, вы не можете удалить запись сотрудника из таблицы «Сотрудники», если в таблице «Заказы» есть заказы, назначенные сотруднику.
• Невозможно изменить основное ключевое значение в основной таблице, если эта запись имеет соответствующие записи. Например, вы не можете изменить идентификатор сотрудника в таблице «Сотрудники», если в таблице «Заказы» есть заказы, назначенные этому сотруднику.

Типы соединения

Существует три основных типа соединения: Вы можете увидеть их на следующем снимке экрана:

Вариант 1 определяет внутреннее соединение. Внутреннее соединение — это соединение, в котором записи из двух таблиц объединяются в результатах запроса только в том случае, если значения в объединенных полях соответствуют определенному состоянию. В запросе соединение по умолчанию — это внутреннее соединение, которое выбирает записи только в том случае, если значения в объединенных полях совпадают.

Вариант 2 определяет левое внешнее соединение. Левое внешнее соединение — это соединение, в котором все записи с левой стороны операции LEFT JOIN в оператора запроса SQL добавляются к результатам запроса, даже если нет соответствующих значений в объединенном поле из таблицы на правой стороне.

Вариант 3 определяет правое внешнее соединение. Правое внешнее соединение — это соединение, в котором все записи с правой стороны операции RIGHT JOIN в операторе запроса SQL добавляются к результатам запроса, даже если нет соответствующих значений в объединенном поле из таблицы на левой стороне.

Рекомендации и ограничения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.

Добавление необходимых таблиц к представлению источника данныхAdding Required Tables to the Data Source View

Откройте конструктор представлений источника данных для представления источников данных Adventure Works DW 2012 .Open Data Source View Designer for the Adventure Works DW 2012 data source view.

Щелкните правой кнопкой мыши панель Организатор диаграмм , выберите команду Создать диаграмму и укажите Причина заказа через Интернет в качестве имени созданной диаграммы.Right-click anywhere in the Diagram Organizer pane, click New Diagram, and specify Internet Sales Order Reasons as the name for this new diagram.

Перетащите таблицу InternetSales с панели Таблицы на панель Диаграмма .Drag the InternetSales table to the Diagram pane from the Tables pane.

Щелкните правой кнопкой мыши панель Диаграмма и выберите команду Добавить или удалить таблицы.Right-click anywhere in the Diagram pane, and then click Add/Remove Tables.

В диалоговом окне Добавление или удаление таблиц добавьте в список Включенные объекты таблицы DimSalesReason и FactInternetSalesReason , а затем нажмите кнопку ОК.In the Add/Remove Tables dialog box, add the DimSalesReason table and the FactInternetSalesReason table to the Included objects list, and then click OK.
Обратите внимание, что связи «первичный-внешний ключ» между задействованными таблицами устанавливаются автоматически, поскольку эти связи определяются в базовой реляционной базе данных.Notice that the primary key-foreign key relationships between the tables that are involved are established automatically because those relationships are defined in the underlying relational database. Если эти связи не определены в базовой реляционной базе данных, их следует определить в представлении источника данных.If these relationships were not defined in the underlying relational database, you would have to define them in the data source view.

В меню Формат выберите команду Автоматический макет и щелкните значок Диаграмма.On the Format menu, point to Auto Layout, and then click Diagram.

В окне свойств измените свойство FriendlyName таблицы DimSalesReason на SalesReason, затем измените свойство FriendlyName таблицы FactInternetSalesReason на InternetSalesReason.In the Properties window, change the FriendlyName property of the DimSalesReason table to SalesReason, and then change the FriendlyName property of the FactInternetSalesReason table to InternetSalesReason.

На панели Таблицы разверните узел InternetSalesReason (dbo.FactInternetSalesReason), щелкните столбец SalesOrderNumber и просмотрите в окне свойств свойство DataType для этого столбца данных.In the Tables pane, expand InternetSalesReason (dbo.FactInternetSalesReason), click SalesOrderNumber, and then review the DataType property for this data column in the Properties window.
Обратите внимание, что в качестве типа данных для столбца SalesOrderNumber указан тип данных string.Notice that the data type for the SalesOrderNumber column is a string data type.

Просмотрите типы данных для других столбцов таблицы FactInternetSalesReason .Review the data types for the other columns in the InternetSalesReason table.
Обратите внимание, что для остальных двух столбцов этой таблицы указаны числовые типы данных.Notice that the data types for the other two columns in this table are numeric data types.

На панели Таблицы щелкните правой кнопкой мыши таблицу InternetSalesReason (dbo.FactInternetSalesReason) и выберите пункт Просмотр данных.In the Tables pane, right-click InternetSalesReason (dbo.FactInternetSalesReason), and then click Explore Data.
Обратите внимание, что для каждого номера строки каждого заказа значение ключа указывает причину покупки данной позиции линии, как показано на следующем рисунке.Notice that, for each line number within each order, a key value identifies the sales reason for the purchase of that line item, as shown in the following image.

Для чего все это нужно?

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

Правильно настроив связи, можно быть уверенным, что ничего не потеряется.

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

• < Назад
• Вперёд >

Новые статьи:

• Объединение таблиц – UNION

• Соединение таблиц – операция JOIN и ее виды

• Тест на знание основ SQL

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Один-ко-многим или многие-к-одному

Это наиболее распространенный тип мощности, используемый в моделях данных. Этот тип количества элементов означает, что одна из таблиц имеет уникальные значения в каждой строке для поля отношения, а другая имеет несколько значений. Пример, который вы видели ранее между таблицами Stores и Sales на основе stor_id, представляет собой отношение «многие-к-одному» или «один-ко-многим».

Есть два способа назвать эти отношения: один-ко-многим или многие-к-одному. Зависит от того, что является исходной и целевой таблицей.

Например, приведенная ниже конфигурация означает, что от таблицы Sales до таблицы Stores есть отношение «многие-к-одному».

А ниже показано отношение «один-ко-многим» от таблицы Stores к таблице Sales:

Эти две таблицы заканчиваются созданием таких отношений:

Это означает, что нет разницы в отношении «один-ко-многим» или «многие-к-одному», кроме порядка, в котором вы читаете это. Если вы посмотрите от таблицы Stores, у вас будет отношение «один ко многим». Если вы посмотрите на это с точки зрения таблицы Sales, у вас будет отношение «многие к одному». И оба они одинаковы, без какой-либо разницы. Так что теперь, в этой статье, всякий раз, когда вы читаете «многие к одному» или «один ко многим», вы знаете, что вы можете читать их и наоборот.

В остальной части статьи мы будем использовать термины таблиц FACT и DIMENSION, которые мы объясним отдельно в другой статье. А пока вот краткое объяснение терминов:

• Таблица фактов (FACT): таблица с числовыми значениями, которые нам нужны либо в агрегированном уровне, либо в подробном выводе. Поля из этой таблицы обычно используются в качестве раздела VALUE визуальных элементов в Power BI.
• Таблица измерений (DIMENSION): таблица, содержащая описательную информацию, которая используется для нарезки данных таблицы фактов. Поля из этой таблицы часто используются в качестве слайсеров, фильтров или осей визуалов в Power BI.

Отношение «многие к одному»  между таблицами фактов и измерений

“Многие-к-одному” — это отношение, обычно используемое между таблицей фактов и таблицами измерений. Приведенный выше пример находится между таблицами Sales (таблица фактов) и Stores (таблица измерений). Если мы приведем еще одну таблицу в модель: Titles (на основе title_id в обеих таблицах: Sales и Titles), то вы увидите, что существует тот же шаблон отношений «многие-к-одному».

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

Допустим, модель отличается от того, что вы видели в этом примере: таблица Sales, таблица Product и две таблицы для информации о категории и подкатегории продукта:

Как вы можете видеть на приведенной выше диаграмме отношений, все отношения — “многие-к-одному”. Что хорошо. Однако, если вы хотите нарезать данные таблицы фактов (например, SalesAmount) по полю из таблицы DimProductCategory (например, по имени ProductCategory), для обработки потребуется три отношения:

Лучшей моделью было бы объединение таблиц категорий и подкатегорий с продуктом и наличие единого отношения «многие к одному» из таблицы фактов в таблицу DimProduct. Подробнее — в ссылке выше.

Отложенное связывание

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

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

Список всех связей

Используйте метод , чтобы получить все записи, включая отложенные. Результаты также будут включать существующие отношения.

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

Используйте такой же подход с методом :

Ленивая фиксация связей

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

Очистка осиротевших привязок

Уничтожает все привязки старше 1 дня, которые не были зафиксированы:

Please enable JavaScript to view the comments powered by Disqus.

Алиасы

Согласитесь, в прошлом примере пришлось довольно много букв написать. Чтобы этого избежать, в запросах можно использовать алиасы для имён таблиц. Для этого после имени таблицы можно написать AS alias. Давайте для таблицы users зададим алиас — u, а для таблицы profiles — p. Эти алиасы теперь можно использовать в любой части запроса:

Заметьте, запрос сократился. Писать запрос с использованием алиаса быстрее.

Как уже говорилось выше, алиас можно использовать в любой части запроса, в том числе и в условии WHERE:

Один-ко-многим

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

Добавим несколько статей:

Запросим теперь эти записи, чтобы убедиться, что всё ок

Давайте теперь выведем имена статей вместе с авторами. Для этого снова воспользуемся оператором INNER JOIN.

Как видим, у Ивана две статьи, и ещё одна у Ольги.

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

Изи!