Связи между таблицами в модели данных

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.
Видеоролик: связи в Power View и PowerPivot

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

CustomerID

Name

EMail

DiscountRate

OrderID

OrderDate

Product

Quantity

1

Эштон

chris.ashton@contoso.com

0,05

256

01.07.2010

Компактный цифровой

11

1

Эштон

chris.ashton@contoso.com

0,05

255

01.03.2010

Однообъективный зеркальный фотоаппарат

15

2

Яворски

michal.jaworski@contoso.com

0,10

254

01.03.2010

Недорогая видеокамера

27

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

Customers

[CustomerID]

Name

Email

1

Эштон

chris.ashton@contoso.com

2

Измайлов

maksim.izmaylov@contoso.com

CustomerDiscounts

[CustomerID]

DiscountRate

1

0,05

2

0,10

Orders

[CustomerID]

OrderID

OrderDate

Product

Quantity

1

256

01.07.2010

Компактный цифровой

11

1

255

01.03.2010

Однообъективный зеркальный фотоаппарат

15

2

254

01.03.2010

Недорогая видеокамера

27

В модели данных есть связи, которые создаются либо вами, либо Excel от вашего лица при одновременном импорте нескольких таблиц. Кроме того, вы можете воспользоваться надстройкой Power Pivot для создания модели и управления ею. Дополнительные сведения см. в статье Создание модели данных в Excel.

Во время импорта таблицы из одной базы данных с помощью надстройки Power PivotPower Pivot может обнаруживать связи между таблицами, основанными на столбцах, заключенных в [квадратные скобки], и воспроизводить эти связи в модели данных, создаваемой в фоновом режиме. Дополнительные сведения см. в разделе Автоматическое обнаружение и вывод связей этой статьи. Если таблицы импортируются из нескольких источников, можно вручную создать связи, как это описано в статье Создание связей между двумя таблицами.

К началу страницы

В этом разделе...

Столбцы и ключи

Типы связей

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

Множественные связи между таблицами

Требования к связям между таблицами

Функции, неподдерживаемые в связях между таблицами

Составные ключи и столбцы подстановки

Связи «многие ко многим»

Самосоединения и циклы

Автоматическое обнаружение и вывод связей в Power Pivot

Автоматическое обнаружение именованных наборов

Вывод связей

Столбцы и ключи

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

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

В данном случае наибольший интерес представляют следующие ключи.

  • Первичный ключ: однозначно определяет строку в таблице, например CustomerID в таблице Customers.

  • Альтернативный ключ (или потенциальный ключ): уникальный столбец, который не является первичным ключом. Например, таблица Employees может хранить идентификатор работника и номер карточки социального страхования, при том что оба они являются уникальными.

  • Внешний ключ: столбец, который ссылается на уникальный столбец в другой таблице, например CustomerID в таблице Orders, который ссылается на CustomerID в таблице Customers.

В модели данных первичный или резервный ключ называется связанным столбцом. Если таблица содержит первичный и резервный ключ, любой из них можно использовать как основу для связи между таблицами. Внешний ключ называется исходным столбцом или просто столбцом. В этом примере связь будет установлена между CustomerID в таблице Orders (столбцом) и CustomerID в таблице Customers (столбцом подстановки). Если данные импортируются из реляционной базы данных, по умолчанию Excel выбирает внешний ключ из одной таблицы и соответствующий первичный ключ из другой таблицы. Тем не менее для столбца подстановки можно использовать любой столбец, содержащий уникальные значения.

Типы связей

Связь между таблицами Customers и Orders является связью один ко многим. Каждый клиент может иметь несколько заказов, однако ни один из заказов не может иметь несколько клиентов. Также существуют связи один к одному и многие ко многим. Таблица CustomerDiscounts, которая определяет по одному льготному тарифу для каждого клиента, находится в связи «один к одному» с таблицей Customers.

В следующей таблице представлены связи между тремя таблицами.

Связь

Тип

Столбец подстановки

Столбец

Customers-CustomerDiscounts

один к одному

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers — Orders

один ко многим

Customers.CustomerID

Orders.CustomerID

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

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

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

Множественные связи между таблицами

Модель данных может содержать несколько связей между двумя таблицами. Для создания точных вычислений Excel необходим единственный путь от одной таблицы к другой. Поэтому одновременно активной может быть только одна связь между каждой парой таблиц. Другие связи остаются неактивными, но подобные неактивные связи можно задавать в формулах и запросах. В представлении диаграммы активная связь отмечается сплошной линией, неактивная — штриховыми. Например, в базе данных AdventureWorksDW2012 содержится таблица DimDate со столбцом DateKey, который связан с тремя различными столбцами из таблицы FactInternetSales: OrderDate, DueDate и ShipDate. Если есть активная связь между столбцами DateKey и OrderDate, эта связь и будет использоваться по умолчанию в формулах, если не указано иное.

К началу страницы

Требования к связям между таблицами

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

Условие

Описание

Уникальный идентификатор для каждой таблицы

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

Уникальные столбцы подстановки

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

Совместимые типы данных

Типы данных в исходном столбце и в столбце подстановки должны быть совместимыми. Дополнительные сведения о типах данных см. в разделе Типы данных, поддерживаемые вмоделях данных.

Функции, неподдерживаемые в связях между таблицами

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

Составные ключи и столбцы подстановки

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

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

Связи «многие ко многим»

Модель данных не может иметь связи «многие ко многим». В модель нельзя добавлять соединяющие таблицы . Тем не менее для моделирования связей «многие ко многим» можно использовать функции DAX.

Самосоединения и циклы

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

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

  • Таблица 1, столбец «а»   к   Таблице 2, столбец «f»

  • Tаблица 2, столбец «f»   к   Таблице 3, столбец «n».

  • Таблица 3, столбец «n»   к   Таблице 1, столбец «a».

При попытке создания связи, которая приведет к образованию цикла, выдается ошибка.

К началу страницы

Автоматическое обнаружение и вывод связей в Power Pivot

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

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

Алгоритм обнаружения на основании статистических данных о значениях и метаданных столбцов формирует выводы о вероятности связей.

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

  • Для успешного обнаружения связи количество уникальных ключей в столбце подстановки должно превышать количество значений в таблице на стороне «многие». Другими словами, ключевой столбец на стороне «многие» связи не должен содержать значений, не содержащихся в ключевом столбце таблицы подстановки. Например, предположим, что имеется таблица, в которой перечислены продукты и их идентификаторы (таблица подстановки), а также таблица продаж, содержащая данные продаж всех продуктов (сторона «многие» связи). Если записи продаж содержат идентификатор продукта, не имеющего соответствующий идентификатор в таблице Products, связь нельзя создать автоматически, но можно создать вручную. Для обеспечения обнаружения связи с помощью Excel необходимо сначала обновить таблицу подстановки Product с использованием идентификаторов недостающих продуктов.

  • Убедитесь, что имя ключевого столбца на стороне «многие» совпадает с именем ключевого столбца в таблице подстановки. Имена не должны быть абсолютно идентичны. Например, в бизнес-среде часто встречаются различные версии имен столбцов, которые содержат фактически одни и те же данные: Emp ID, EmployeeID, Employee ID, EMP_ID и так далее. Алгоритм выявляет похожие имена и задает более высокие значения вероятности столбцам, имена которых похожи или полностью совпадают. Поэтому, чтобы увеличить вероятность создания связи, можно попытаться переименовать столбцы в импортируемых данных, подобрав имена чем-то похожие на имена строк в существующих таблицах. Если Excel находит несколько возможных связей, связь не создается.

Эти сведения помогают понять, почему не удалось выявить все связи и какие изменения в метаданных (именах полей и типах данных) могут повысить эффективность автоматического обнаружения связей. Дополнительные сведения см. в разделе Устранение неполадок в связях.

Автоматическое обнаружение именованных наборов

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

Вывод связей

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

  • Products и Category — связь создается вручную

  • Category и SubCategory — связь создается вручную

  • Products и SubCategory — связь определяется автоматически

Для автоматического объединения связей в цепочки эти связи должны идти в одном направлении, как показано выше. Если исходные связи были установлены, например между таблицами Sales и Products, а также между Sales и Customers, то связь не выводится. Это вызвано тем, что связь между таблицами Products и Customers является связью «многие ко многим».

К началу страницы

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×