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

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

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

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

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

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

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

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

Отношения между клиентом и заказом — это связь "один ко многим". Каждый клиент может иметь несколько заказов, однако ни один из заказов не может иметь несколько клиентов. Еще одно важное табличное отношение — "один к одному". В нашем примере таблица CustomerDiscounts , которая определяет единую ставку скидок для каждого клиента, имеет связь "один к одному" с таблицей Customers.

В этой таблице показаны связи между тремя таблицами (Customers, CustomerDiscounts и Orders):

Связь	Тип	Столбец подстановки	Столбец
Customers-CustomerDiscounts	один к одному	Customers.CustomerID	CustomerDiscounts.CustomerID
Customers — Orders	один ко многим	Customers.CustomerID	Orders.CustomerID

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вывод связей

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

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

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

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

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