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

Связи основываются на столбцах в каждой таблице, содержащих одинаковые данные. Например, можно связать таблицу "Клиенты" с таблицей "Заказы", если каждая из них содержит столбец с ИД клиента. В данном примере имена столбцов одинаковы, но это не является обязательным условием. Один столбец может называться 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_ID"и так далее. Алгоритм выявляет похожие имена и задает более высокие значения вероятности столбцам, имена которых похожи или полностью совпадают. Поэтому, чтобы увеличить вероятность создания связи, можно попытаться переименовать столбцы в импортируемых данных, подобрав имена чем-то похожие на имена строк в существующих таблицах. Если Excel находит несколько возможных связей, связь не создается.

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

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

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

Вывод связей

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

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

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

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

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