Общие сведения о связях
В этом разделе дается представление о связях, которые можно определить между таблицами в PowerPivot для Excel. Раздел включает следующие подразделы:
Что такое связь?
Требования к связям
Автоматическое обнаружение и вывод связей
После прочтения этого раздела должно быть понятно, что такое связь, какие требования предъявляются к определению связи и как PowerPivot для Excel может автоматически обнаруживать связи. Кроме того, представляются некоторые сведения о терминологии, используемой специалистами по базам данных для описания связей.
Что такое связь?
Связь представляет собой соединение двух таблиц данных на основании одного или нескольких столбцов в каждой таблице (точно по одному столбцу в каждой таблице для PowerPivot). Чтобы понять, чем полезны связи, представим, что отслеживаются данные для заказов клиентов в бизнесе. Можно отслеживать все данные в одной таблице, имеющей структуру, подобную следующей.
CustomerID |
Название |
DiscountRate |
OrderID |
OrderDate |
Товар |
Количество |
|
|---|---|---|---|---|---|---|---|
1 |
Эштон |
chris.ashton@contoso.com |
.05 |
256 |
2010-01-07 |
Компактный цифровой |
11 |
1 |
Эштон |
chris.ashton@contoso.com |
.05 |
255 |
2010-01-03 |
Однообъективный зеркальный фотоаппарат |
15 |
2 |
Яворски |
michal.jaworski@contoso.com |
.10 |
254 |
2010-01-03 |
Недорогая видеокамера |
27 |
Этот подход может быть эффективным, но он подразумевает хранение множества избыточных данных, таких как адрес электронной почты клиента для каждого заказа. Хранение не требует больших затрат, но нужно быть уверенным в том, что при изменении адреса электронной почты будет обновлена каждая строка для этого клиента. Одним из решений этой проблемы является разбиение данных на множество таблиц и определение связей между этими таблицами. Именно этот подход используется в реляционных базах данных наподобие SQL Server. Например, база данных, которая импортируется в PowerPivot для Excel, может представлять данные заказов, используя три связанные таблицы.
Клиенты
[CustomerID] |
Название |
|
|---|---|---|
1 |
Эштон |
chris.ashton@contoso.com |
2 |
Яворски |
michal.jaworski@contoso.com |
CustomerDiscounts
[CustomerID] |
DiscountRate |
|---|---|
1 |
.05 |
2 |
.10 |
Orders
[CustomerID] |
OrderID |
OrderDate |
Товар |
Количество |
|---|---|---|---|---|
1 |
256 |
2010-01-07 |
Компактный цифровой |
11 |
1 |
255 |
2010-01-03 |
Однообъективный зеркальный фотоаппарат |
15 |
2 |
254 |
2010-01-03 |
Недорогая видеокамера |
27 |
При импорте этих таблиц из одной и той же базы данных PowerPivot может обнаружить связи между таблицами, основанные на столбцах, заключенных в [квадратные скобки], и воспроизвести эти связи в окне PowerPivot. Дополнительные сведения см. в подразделе Автоматическое обнаружение и вывод связей этого раздела. Если таблицы импортируются из нескольких источников, то связи можно создавать вручную, как описано в Создание связи между двумя таблицами.
Столбцы и ключи
Связи основаны на столбцах в каждой таблице, содержащих одинаковые данные. Например, таблицы Customers и Orders могут быть связаны друг с другом, поскольку обе содержат столбец, в котором хранится идентификатор клиента. В данном примере имена столбцов одинаковы, но это не является обязательным условием. Один столбец может называться CustomerID, а другой — CustomerNumber, при условии, что все строки в таблице Orders содержат идентификатор, который также хранится в таблице Customers.
В реляционной базе данных имеется несколько типов ключей, которые обычно представлены столбцами со специальными свойствами. Для наших целей наиболее интересны следующие четыре типа ключей.
Первичный ключ: однозначно определяет строку в таблице, например CustomerID в таблице Customers.
Резервный ключ (или потенциальный ключ): столбец, отличающийся от первичного ключа, который также является уникальным. Например, таблица Employees может хранить идентификатор работника и номер карточки социального страхования, при том что оба они являются уникальными.
Внешний ключ: столбец, который ссылается на уникальный столбец в другой таблице, например столбец CustomerID в таблице Orders ссылается на столбец CustomerID в таблице Customers.
Составной ключ: ключ, составленный из нескольких столбцов. Составные ключи не поддерживаются в PowerPivot для Excel. Дополнительные сведения см. в подразделе «Составные ключи и столбцы подстановки» в этом разделе.
В PowerPivot для Excel первичный ключ или резервный ключ называется связанным столбцом подстановки или просто столбцом подстановки. Если таблица имеет и первичный и резервный ключ, в качестве столбца подстановки можно использовать любой из них. Внешний ключ называется исходным столбцом или просто столбцом. В нашем примере связь была бы определена между столбцом CustomerID в таблице Orders (столбец) и столбцом CustomerID (столбец подстановки) в таблице Customers. Если данные импортируются из реляционной базы данных, то по умолчанию PowerPivot для Excel выбирает внешний ключ из одной таблицы и соответствующий первичный ключ из другой таблицы. Однако любой столбец, содержащий уникальные значения, может использоваться в качестве столбца подстановки.
Типы связей
Связь между таблицей Customers и таблицей Orders является связью один ко многим. Каждый клиент может иметь несколько заказов, но заказ не может иметь несколько клиентов. Другими типами связей являются связи один к одному и многие ко многим. Таблица CustomerDiscounts, которая определяет по одному льготному тарифу для каждого клиента, находится в связи «один к одному» с таблицей Customers. Примером связи «многие ко многим» является прямая связь между таблицами Products и Customers, когда один клиент может купить много продуктов и один продукт может быть куплен несколькими клиентами. PowerPivot для Excel не поддерживает связи типа «многие ко многим» в пользовательском интерфейсе. Дополнительные сведения см. в главе «Связи типа "многие ко многим"» в данном разделе.
В следующей таблице представлены связи между тремя таблицами.
Связь |
Тип |
Столбец подстановки |
Столбец |
|---|---|---|---|
Customers — CustomerDiscounts |
один к одному |
Customers.CustomerID |
CustomerDiscounts.CustomerID |
Customers — Orders |
один ко многим |
Customers.CustomerID |
Orders.CustomerID |
Связи и производительность
После создания любой связи клиент PowerPivot для Excel, как правило, должен повторно вычислить формулы, использующие столбцы из таблиц, участвующих в созданной связи. Обработка может занять некоторое время, в зависимости от объема данных и сложности связей. Дополнительные сведения см. в разделе Пересчет формул.
Требования к связям
PowerPivot для Excel предъявляет несколько требований, которые должны учитываться при создании связей.
Одиночная связь между таблицами
Наличие нескольких связей может привести к неоднозначной зависимости между таблицами. Для создания точных вычислений необходимо, чтобы от одной таблицы к другой вел единственный путь. Поэтому между каждой парой таблиц может существовать только одна связь. Например, в базе данных AdventureWorksDW2012 содержится таблица DimDate со столбцом DateKey, который связан с тремя различными столбцами из таблицы FactInternetSales: OrderDate, DueDate и ShipDate. Если импортировать эти таблицы без изменений, то первая связь будет создана успешно, однако для последующих связей с участием того же столбца будет получено следующее сообщение об ошибке.
* Связь: таблица[столбец 1]-> таблица[столбец 2] — состояние: ошибка — причина: Не удается создать связь между таблицами <таблица 1> и <таблица 2>. Между двумя таблицами может существовать только одна прямая или косвенная связь.
Если имеются две таблицы, между которыми существует несколько связей, то необходимо импортировать несколько копий таблицы, содержащей столбец подстановки, и создать между каждой парой таблиц одну связь.
Наличие одной связи для каждого исходного столбца
Исходный столбец не может участвовать в нескольких связях. Если вы уже использовали столбец в качестве исходного столбца в одной связи, но его необходимо связать с другим столбцом подстановки в другой таблице, можно создать копию столбца и использовать ее в новой связи.
Создание копии столбца, имеющего те же значения, упрощается посредством применения формулы DAX в вычисляемом столбце. Дополнительные сведения см. в разделе Вычисляемые столбцы.
Применение уникального идентификатора для каждой таблицы
Каждая таблица должна иметь один столбец, который однозначно идентифицирует каждую строку в этой таблице. Такой столбец часто именуется первичным ключом.
Уникальные столбцы подстановки
Значения данных в столбце подстановки должны быть уникальными. Иными словами, столбец не может содержать повторяющиеся значения. В PowerPivot для Excel значения NULL и пустые строки считаются эквивалентными пустому значению, которое является специальным значением данных. Это значит, что столбец подстановки не может содержать несколько значений NULL.
Совместимые типы данных
Типы данных в исходном столбце и в столбце подстановки должны быть совместимыми. Дополнительные сведения о типах данных см. в разделе Типы данных, поддерживаемые в книгах PowerPivot.
Составные ключи и столбцы подстановки
Составные ключи нельзя использовать в книге PowerPivot. Необходимо иметь только один столбец, однозначно определяющий каждую строку в таблице. Если импортировать таблицы, для которых существует связь по составному ключу, мастер импорта таблиц будет игнорировать такую связь, поскольку ее нельзя создать в PowerPivot.
Если нужно создать связь между двумя таблицами в PowerPivot, а первичный и внешний ключи определяются несколькими столбцами, то перед созданием связи необходимо объединить значения, чтобы создать один ключевой столбец. Это можно сделать перед импортом данных или осуществить в PowerPivot, создав вычисляемый столбец.
Связи «многие ко многим»
PowerPivot для Excel не поддерживает связи «многие ко многим», что не позволяет непосредственно добавлять соединяющие таблицы в PowerPivot. Тем не менее можно использовать функции DAX для моделирования связи «многие ко многим».
Самосоединения и циклы
В таблицах PowerPivot не допускаются самосоединения. Самосоединение — это рекурсивная связь таблицы с самой собой. Самосоединения часто используются для определения иерархий типа «родители-потомки». Например, можно соединить таблицу Employees с самой собой, чтобы сформировать иерархию, которая показывает цепочку подчиненности на предприятии.
PowerPivot для Excel не позволяет создавать циклы из связей в книге. Иными словами, следующий набор связей запрещается.
От таблицы 1, столбец a к таблице 2, столбец f
От таблицы 2, столбец f к таблице 3, столбец n
От таблицы 3, столбец n к таблице 1, столбец a
При попытке создания такой связи, которая приводит к возникновению цикла, вырабатывается ошибка.
Автоматическое обнаружение и вывод связей
При импорте данных в окно PowerPivot мастер импорта таблиц автоматически обнаруживает существующие связи между таблицами. Кроме того, при создании сводной таблицы PowerPivot для Excel анализирует данные в таблицах. Он обнаруживает возможные связи, которые не определены, и предлагает включить соответствующие столбцы в эти связи.
Алгоритм обнаружения на основании статистических данных о значениях и метаданных столбцов формирует выводы о вероятности связей.
Типы данных во всех связанных столбцах должны быть совместимыми. Для автоматического обнаружения поддерживаются только целочисленные и текстовые типы данных. Дополнительные сведения о типах данных см. в разделе Типы данных, поддерживаемые в книгах PowerPivot.
Для успешного обнаружения связи количество уникальных ключей в столбце подстановки должно превышать количество значений в таблице на стороне «многие». Другими словами, ключевой столбец на стороне «многие» связи не должен содержать значений, не содержащихся в ключевом столбце таблицы подстановки. Например, предположим, что имеется таблица, в которой перечислены продукты и их идентификаторы (таблица подстановки), а также таблица продаж, содержащая данные продаж всех продуктов (сторона «многие» связи). Если записи продаж содержат идентификатор продукта, для которого отсутствует соответствующий идентификатор в таблице Products, связь нельзя создать автоматически, но ее можно создать вручную. Для обеспечения обнаружения связи с помощью PowerPivot для Excel необходимо сначала обновить таблицу подстановки Product с использованием идентификаторов недостающих продуктов.
Убедитесь, что имя ключевого столбца на стороне «многие» совпадает с именем ключевого столбца в таблице подстановки. Имена не должны быть абсолютно идентичны. Например, в бизнес-среде часто встречаются различные версии имен столбцов, которые содержат фактически одни и те же данные: Emp ID, EmployeeID, Employee ID, EMP_ID и т. д. Алгоритм выявляет схожие имена и назначает столбцам, имена которых схожи или полностью совпадают, более высокие значения вероятности. Поэтому, чтобы увеличить вероятность создания связи, можно переименовать столбцы в импортируемых данных, подобрав имена, схожие с именами столбцов в существующих таблицах. Если PowerPivot для Excel находит несколько возможных связей, связь не создается.
Эти сведения помогают понять, почему не удалось выявить все связи и какие изменения в метаданных (именах полей и типах данных) могут повысить эффективность автоматического обнаружения связей. Дополнительные сведения см. в разделах Устранение неполадок в связях и Behind the scenes of PowerPivot’s automatic relationship detection (на английском языке).
Автоматическое обнаружение для именованных наборов
Связи между именованными наборами и связанными полями в сводной таблице нельзя обнаружить автоматически. Такие связи можно создать вручную. При необходимости использования автоматического обнаружения связей удалите каждый именованный набор и добавьте отдельные поля из именованного набора непосредственно в сводную таблицу.
Вывод связей
В некоторых случаях связи между таблицами автоматически объединяются в цепочки. Например, если создать связь между первыми двумя наборами таблиц, указанных ниже, то определяется наличие связи между другими двумя таблицами и эта связь устанавливается автоматически.
Связь между таблицами Products и Category создается вручную.
Связь между таблицами Category и SubCategory создается вручную.
Связь между таблицами Products и SubCategory определяется автоматически.
Для автоматического объединения связей в цепочки эти связи должны идти в одном направлении, как показано выше. Если исходные связи были установлены, например между таблицами Sales и Products, а также между Sales и Customers, то связь не выводится. Это вызвано тем, что связь между таблицами Products и Customers является связью «многие ко многим».