Урок 5-3. Определение отношения "многие ко многим"

Статья
05/08/2023

Применимо к: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

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

В SQL Server Analysis Services определяется связь "многие ко многим" между измерением и группой мер, указывая промежуточную таблицу фактов, присоединенную к таблице измерений. В свою очередь, промежуточная таблица фактов соединена с промежуточной таблицей измерения, с которой связана эта таблица фактов. Связи «многие ко многим» между промежуточной таблицей фактов, таблицами измерений в этой связи и промежуточным измерением создают связи «многие ко многим» между элементами первичных измерений и мерами группы мер, которая указана данной связью. Чтобы определить связь «многие ко многим» между измерением и группой мер через промежуточную группу мер, промежуточная группа мер должна иметь одно или несколько общих измерений с исходной группой мер.

С измерением «многие ко многим» значения суммируются отдельно, что означает, что они не вычисляются более одного раза для элемента «Все».

Примечание

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

Дополнительные сведения см. в разделах Связи измеренийи Определение связей "многие ко многим" и свойств связей "многие ко многим".

В задачах этого раздела предстоит определить измерение Sales Reasons и группу мер Sales Reasons, а также создать связь «многие ко многим» между измерением Sales Reasons и группой мер Internet Sales через группу мер Sales Reasons.

Добавление необходимых таблиц к представлению источника данных

Представление открытого источника данных Designer для представления источников данных Adventure Works DW 2019.
Щелкните правой кнопкой мыши панель Организатор диаграмм , выберите команду Создать диаграммуи укажите Причина заказа через Интернет в качестве имени созданной диаграммы.
Перетащите таблицу InternetSales с панели Таблицы на панель Диаграмма .
Щелкните правой кнопкой мыши панель Диаграмма и выберите команду Добавить или удалить таблицы.
В диалоговом окне Добавление или удаление таблиц добавьте в список Включенные объекты таблицы DimSalesReason и FactInternetSalesReason , а затем нажмите кнопку ОК.

Обратите внимание, что связи первичного и внешнего ключей между участвующими таблицами устанавливаются автоматически, так как эти связи определены в базовой реляционной базе данных. Если эти связи не определены в базовой реляционной базе данных, их следует определить в представлении источника данных.
В меню Формат выберите команду Автоматический макети щелкните значок Диаграмма.
В окне свойств измените свойство FriendlyName таблицы DimSalesReason на SalesReason, затем измените свойство FriendlyName таблицы FactInternetSalesReason на InternetSalesReason.
На панели Таблицы разверните узел InternetSalesReason (dbo.FactInternetSalesReason), щелкните столбец SalesOrderNumberи просмотрите в окне свойств свойство DataType для этого столбца данных.

Обратите внимание, что в качестве типа данных для столбца SalesOrderNumber указан тип данных string.
Просмотрите типы данных для других столбцов таблицы FactInternetSalesReason .

Обратите внимание, что для остальных двух столбцов этой таблицы указаны числовые типы данных.
На панели Таблицы щелкните правой кнопкой мыши таблицу InternetSalesReason (dbo.FactInternetSalesReason)и выберите пункт Просмотр данных.

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

Определение промежуточной группы мер

Перейдите в Designer куба SQL Server Analysis Services Tutorial и перейдите на вкладку Структура куба.
Щелкните правой кнопкой мыши панель Меры и выберите команду Создать группу мер. Дополнительные сведения см. в разделе Создание мер и групп мер в многомерных моделях.
В диалоговом окне Создание группы мер в списке Выберите таблицу из представления источников данных выберите таблицу InternetSalesReason и нажмите кнопку ОК.

Обратите внимание, что на панели Меры теперь отображается группа мер Internet Sales Reason .
Разверните группу мер Internet Sales Reason .

Обратите внимание, что для этой группы мер определена единственная мера, Internet Sales Reason Count .
Выберите меру Internet Sales Reason Count и просмотрите свойства этой меры в окне свойств.

Обратите внимание, что свойство AggregateFunction для этой меры определено как Число , а не Сумма. SQL Server Analysis Services выбрать Вариант, так как базовый тип данных является строковым типом данных. Два других столбца в базовой таблице фактов не были выбраны в качестве мер, так как SQL Server Analysis Services обнаружили их как числовые ключи, а не как фактические меры. Дополнительные сведения см. в разделе Определение полуаддитивного режима.
В окне «Свойства» измените значение свойства Visible меры Internet Sales Reason Count на False.

Эта мера будет использована только для соединения измерения Sales Reason, которое предстоит определить после группы мер Internet Sales. Пользователи не смогут просматривать эту меру.

На следующем рисунке демонстрируются свойства меры Internet Sales Reason Count .

Определение измерения «многие ко многим»

В Обозреватель решений щелкните правой кнопкой мыши Пункты и выберите пункт Создать измерение.
На странице Вас приветствует мастер измерений нажмите кнопку Далее.
На странице Выбор метода создания выберите параметр Использовать существующую таблицу и нажмите кнопку Далее.
На странице Указание сведений об источнике убедитесь, что выбрано представление источника данных Adventure Works DW 2019.
В списке Основная таблица выберите таблицу SalesReason.
Убедитесь, что в списке Ключевые столбцы присутствует столбец SalesReasonKey .
В списке Столбец имени выберите SalesReasonName.
Щелкните Далее.
На странице Выбор атрибутов измерения атрибут Sales Reason Key автоматически выбран, поскольку он является ключевым. Установите флажок рядом с атрибутом Sales Reason Reason Type , измените его имя на Sales Reason Typeи нажмите кнопку Далее.
На странице Завершение работы мастера нажмите кнопку Готово , чтобы создать измерение Sales Reason.
В меню Файл выберите команду Сохранить все.
На панели Атрибуты конструктора измерений для измерения Sales Reason выберите Sales Reason Keyи в окне свойств задайте для свойства Name значение Sales Reason.
На панели Иерархии конструктора измерений создайте пользовательскую иерархию Sales Reasons , которая будет содержать уровни Sales Reason Type и Sales Reason (в указанном порядке).
В окне свойств задайте значение Все причины покупки для свойства AllMemberName иерархии Sales Reason.
Укажите значение Все причины покупки для свойства AttributeAllMemberName измерения Sales Reason.
Чтобы добавить созданное измерение в куб SQL Server Analysis Services Tutorial в качестве измерения куба, перейдите в раздел Куб Designer. На вкладке Структура куба щелкните правой кнопкой мыши панель Измерения и выберите команду Добавить измерение куба.
В диалоговом окне Добавление измерения куба выберите Sales Reason и нажмите кнопку ОК.
В меню Файл выберите команду Сохранить все.

Определение связи «многие ко многим»

Переключитесь на Designer куб SQL Server Analysis Services Tutorial и перейдите на вкладку Использование измерений.

Обратите внимание, что измерение Sales Reason имеет обычную связь, определенную группой мер Internet Sales Reason , но не имеет связи, определенной группами мер Internet Sales или Reseller Sales . Обратите внимание, что измерение Подробности заказа через Интернет имеет обычную связь, определенную измерением Причина продажи через Интернет , которое в свою очередь имеет Fact Relationship с группой мер Internet Sales . Если измерение не найдено (или не найдено другое измерение со связью с обеими группами мер: Причина покупки через Интернет и Продажи через Интернет ), не получится определить связь "многие ко многим".
Щелкните ячейку на пересечении группы мер Продажи через Интернет и измерения Причина покупки , а затем нажмите кнопку обзора (...).
В диалоговом окне Задание связи в списке Выберите тип связи укажите значение Многие ко многим .

Требуется определить промежуточную группу мер, соединяющую измерение Sales Reason и группу мер Internet Sales.
В списке Промежуточная группа мер выберите значение Internet Sales Reason.

На следующем рисунке отражены изменения, сделанные в диалоговом окне Задание связи .
Нажмите кнопку ОК.

Обратите внимание на значок «многие ко многим», который отражает связь между измерением Sales Reason и группой мер Internet Sales.

Просмотр куба и измерения «многие ко многим»

В меню Сборка выберите команду Развернуть Analysis Services Tutorial.
После успешного завершения развертывания перейдите на вкладку Браузер в кубе Designer куба SQL Server Analysis Services Tutorial и нажмите кнопку Повторно подключиться.
В область данных панели "Данные" добавьте меру Продажи через Интернет — объем продаж .
Добавьте определенную пользователем иерархию Причины покупки из измерения Причина покупки в область строк панели данных.
На панели метаданных разверните узлы Заказчик, Расположение, География заказчика, Члены, Все заказчики, Австралия, щелкните правой кнопкой мыши элемент Квинсленди выберите команду Добавить в фильтр.
Разверните каждый член уровня "Тип причины продаж ", чтобы просмотреть значения в долларах, связанные с каждой причиной, которую клиент в Квинсленде дал для покупки продукта Adventure Works через Интернет.

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

На следующем рисунке показаны панели конструктора кубов Фильтр и Данные .