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

Статья
05/08/2023

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

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

Введение

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

Отношение «многие ко многим» убирает это ограничение моделирования, позволяя факту (например, балансу счета) быть связанным с несколькими членами того же измерения (баланс объединенного счета может рассматриваться как атрибут двумя или несколькими владельцами объединенного счета).

Концептуально связь измерения «многие ко многим» в службах Analysis Services эквивалентна связям в реляционной модели и поддерживает те же виды сценариев. Примеры подобной связи приведены ниже.

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

С точки зрения аналитики связь «многие ко многим» решает проблему точного представления количества или суммы относительно связи измерения (исключая двойные подсчеты при обработке определенного члена измерения). Следующий пример позволяет прояснить это утверждение. Рассмотрим продукт или услугу, которые относятся более чем к одной категории. Если количество услуг подсчитывалось по категории, нужно, чтобы услуга, входящая в обе категории, была включена в каждую. В то же время нежелательно переоценивать количество предоставляемых услуг. Указание связи измерения «многие ко многим» позволяет получать более точные результаты при запросе по категории или услуге. Однако следует всегда проводить тщательную проверку правильности результатов.

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

Связь «многие ко многим» не отображается визуально в диаграмме куба. Вместо нее используйте вкладку «Использование измерения» для быстрого определения подобных связей в модели. Связь «многие ко многим» обозначается следующим значком:

Значок "

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

Кнопка

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

Создание измерения «многие ко многим»

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

Измерения в связи «многие ко многим» могут иметь соответствующие таблицы в представлении источника данных, где каждое измерение в модели основывается на таблице в источнике данных. И наоборот, измерения в конкретной модели могут основываться на меньшем числе физических таблиц (или на других таблицах) в представлении источника данных. Используя измерения Sales Reasons и Sales Orders в качестве примера, куб Adventure Works демонстрирует связь «многие ко многим» с помощью измерений, которые существуют только как структуры данных и только в модели без физических аналогов в представлении источника данных. Измерение Sales Order основано на таблице фактов, а не на таблице измерения в источнике данных.

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

Для иллюстрации шагов по созданию связи «многие ко многим» данная процедура воссоздает одну из подобных связей в кубе Adventure Works. Если у вас есть источник данных (в данном случае хранилище данных примера Adventure Works), установленный на экземпляре реляционной СУБД, вы можете выполнить следующую процедуру.

Шаг 1. Проверка связей представления источника данных

В приложении SQL Server Data Tools для многомерного проекта создайте источник данных для реляционного хранилища данных Adventure Works DW 2012, размещенного на экземпляре SQL Server Database Engine.
Создайте представление источника данных, используя следующие таблицы:
- FactInternetSales
- FactInternetSalesReason
- DimSalesReason
Проверьте, чтобы все таблицы, планируемые к использованию в связях «многие ко многим», были связаны в представлении источника данных через первичный ключ. Это обязательно для формирования ссылки на промежуточную группу мер в последующем шаге.

Примечание

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

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

Шаг 2. Создание измерений и групп мер

В среде SQL Server Data Tools для многомерного проекта щелкните правой кнопкой мыши папку Измерения и выберите Создать измерение.
Создайте новое измерение на основе существующей таблицы DimSalesReason. При указании источника примите все значения по умолчанию.

Выберите все атрибуты.
Создайте второе измерение на основе существующей таблицы Fact Internet Sales. Эта таблица фактов содержит информацию измерения Sales Order. Мы используем ее для построения этого измерения.
В окне указания сведений об источнике вы увидите предупреждение о том, что нужно указать столбец Name. Выберите SalesOrderNumber в качестве Name.
На следующей странице мастера выберите атрибуты. В данном примере можно выбрать только SalesOrderNumber.
Переименуйте измерение в Dim Sales Orders, придерживаясь согласованного порядка именования измерений.
Щелкните правой кнопкой мыши Кубы и выберите пункт Создать куб.
В таблицах групп мер выберите FactInternetSales и FactInternetSalesReason.

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

Чтобы упростить модель, уберите все меры, затем выберите в нижней части списка лишь Sales Amount и Fact Internet Sales Count . FactInternetSalesReason имеет только одну меру, поэтому выбирается автоматически.
В списке измерений будут отображаться Dim Sales Reason и Dim Sales Orders.

На странице "Выбор новых измерений" появится приглашение на создание нового измерения для Fact Internet Sales Dimension. Это измерение не требуется, поэтому его можно убрать из списка.
Присвойте кубу имя и нажмите кнопку Готово.

Шаг 3. Определение связи "многие ко многим"

В конструкторе кубов откройте вкладку Использование измерений. Обратите внимание, что между Dim Sales Reason и Fact Internet Sales уже существует связь "многие ко многим". Напомним, что связь «многие ко многим» обозначается следующим значком.

"
Щелкните ячейку на пересечении Dim Sales Reason и Fact Internet Sales, затем нажмите кнопку, чтобы открыть окно определения связи.

В этом диалоговом окне можно указать связь «многие ко многим». Если бы мы добавляли измерения с обычной связью, то в этом окне изменили бы их на «многие ко многим».
Разверните проект в многомерный экземпляр служб Analysis Services. В следующем шаге мы откроем куб в Excel, чтобы проверить его поведение.

Тестирование связи «многие ко многим»

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

Откройте куб в Excel

Разверните проект, затем откройте куб, чтобы подтвердить правильность выполнения агрегирований.
В Excel щелкните Данные | из других источников | из служб Analysis Services. Введите имя сервера, выберите базу данных и куб.
Создайте сводную таблицу, которая использует следующее:
- Sales Amount как значение
- Sales Reason Name для столбцов
- Sales Order Number для строк
Проанализируйте результаты. Мы используем примеры данных, поэтому на первый взгляд кажется, что все заказы на продажу имеют одинаковые значения. Но если выполнить прокрутку результатов вниз, то можно видеть разброс данных.

Немного ниже вы увидите сумму продаж и причины для заказа с номером SO5382. Общая сумма этого заказа — 539,99, а в качестве причин указаны Promotion, Other и Price.

",

Обратите внимание, что столбец Sales Amount для этого заказа вычислен правильно; здесь показано 539,99 за весь заказ. Хотя 539,99 указано для каждой причины, это значение не суммируется для всех трех причин, что привело бы к ошибке в общем итоге.

А зачем вообще помещать сумму продаж под каждой причиной продажи? Это позволяет идентифицировать объем продаж, который можно приписать каждой причине.
Выполните прокрутку до нижней части листа. Теперь хорошо видно, что Price (Цена) является самой важной причиной для покупок клиентов по сравнению с другими причинами и общей суммой.

"

Советы по обработке непредвиденных результатов запросов

Скрывайте в промежуточной группе мер такие меры, как подсчеты, которые не возвращают в запросе значимые результаты. Это не позволит пользователям использовать агрегирования, дающие на выходе бессмысленные данные. Чтобы спрятать меру, задайте атрибуту Видимость значение False в конструкторе измерения.
Создавайте перспективы, использующие подмножество мер, и измерения, обеспечивающие аналитический процесс, который вы желаете предоставить пользователям. Возможно, куб, который содержит много групп мер и измерений, не во всех случаях будет работать как надо. Изолируя измерения и группы мер, которые будут использоваться вместе, вы обеспечиваете более прогнозируемый результат.
Всегда помните, что нужно развернуть модель и снова подключиться к ней после ее изменения. В Excel нажмите кнопку «Обновить» на ленте «Анализ» сводной таблицы.
Избегайте использования связанных групп мер в нескольких связях типа «многие ко многим», особенно если эти связи находятся в разных кубах. Это может привести к формированию неоднозначных агрегатов.