Подавите анализ данных, создав связи между разными таблицами. Отношение — это связь между двумя таблицами, содержащими данные: один столбец в каждой таблице является основой связи. Чтобы понять, чем полезны связи, представим, что отслеживаются данные для заказов клиентов в бизнесе. Вы можете отслеживать все данные в одной таблице, имеющей такую структуру:
| ИДклиента | Имя | Электронная почта | DiscountRate | Код заказа | OrderDate | Продукт | 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
| [ИД клиента] | Имя | Электронная почта |
|---|---|---|
| 1 | Эштон | chris.ashton@contoso.com |
| 2 | Измайлов | michal.jaworski@contoso.com |
CustomerDiscounts
| [ИД клиента] | DiscountRate |
|---|---|
| 1 | 0,05 |
| 2 | 0,10 |
Orders
| [ИД клиента] | Код заказа | OrderDate | Продукт | Quantity |
|---|---|---|---|---|
| 1 | 256 | 01.07.2010 | Компактный цифровой | 11 |
| 1 | 255 | 01.03.2010 | Однообъективный зеркальный фотоаппарат | 15 |
| 2 | 254 | 01.03.2010 | Недорогая видеокамера | 27 |
Связи существуют внутри модели данных: она создается явным образом или автоматически создается Excel при одновременном импорте нескольких таблиц. Для создания модели или управления ею также можно использовать надстройку Power Pivot. Дополнительные сведения см. в статье Создание модели данных в Excel.
Если вы импортируете таблицы из одной базы данных с помощью надстройки Power Pivot, Power Pivot может обнаруживать связи между таблицами на основе столбцов, которые находятся в [скобках], и воспроизводить эти связи в модели данных, которая строится в фоновом режиме. Дополнительные сведения см. в разделе Автоматическое обнаружение и вывод связей этой статьи. Если таблицы импортируются из нескольких источников, можно вручную создать связи, как это описано в статье Создание связей между двумя таблицами.
Столбцы и ключи
Связи основываются на столбцах в каждой таблице, содержащих одинаковые данные. Например, можно связать таблицу "Клиенты " с таблицей "Заказы ", если каждая из них содержит столбец, в котором хранится код клиента. В данном примере имена столбцов одинаковы, но это не является обязательным условием. Один столбец может называться CustomerID, а другой — CustomerNumber, при условии, что все строки в таблице Orders содержат идентификатор, который также хранится в таблице Customers.
В реляционной базе данных есть несколько типов ключей. Ключ обычно представляет собой столбец со специальными свойствами. Знание назначения каждого ключа помогает в управлении моделью данных с несколькими таблицами, предоставляющей данные для сводной таблицы, сводной диаграммы или отчета Power View.
Хотя существует много типов ключей, вот самые важные для нашей цели:
- Первичный ключ: однозначно определяет строку таблицы, например "Код клиента" в таблице "Клиенты ".
- Альтернативный ключ (или потенциальный ключ): столбец, отличный от уникального первичного ключа. Например, таблица Employees может хранить идентификатор работника и номер карточки социального страхования, при том что оба они являются уникальными.
- Внешний ключ. Столбец, ссылающийся на уникальный столбец в другой таблице, например "Код клиента" в таблице "Заказы ", который ссылается на "КодКлиента " в таблице "Клиенты".
В модели данных первичный или резервный ключ называется связанным столбцом. Если таблица содержит первичный и резервный ключ, любой из них можно использовать как основу для связи между таблицами. Внешний ключ называется исходным столбцом или просто столбцом. В нашем примере определяется связь между значением "Код клиента " в таблице "Заказы " (столбец) и значением "Код клиента " в таблице "Клиенты " (столбец подстановки). Если данные импортируются из реляционной базы данных, по умолчанию Excel выбирает внешний ключ из одной таблицы и соответствующий первичный ключ из другой таблицы. Тем не менее для столбца подстановки можно использовать любой столбец, содержащий уникальные значения.
Типы связей
Отношение между клиентом и заказом — это отношение "один-ко-многим". Каждый клиент может иметь несколько заказов, однако ни один из заказов не может иметь несколько клиентов. Еще одно важное отношение между таблицами — "один-к-одному". В приведенном здесь примере таблица CustomerDiscounts , определяющая единую ставку дисконтирования для каждого клиента, связана отношением "один-к-одному" с таблицей "Клиенты".
В этой таблице показаны связи между тремя таблицами ("Клиенты", "Скидки клиентов" и "Заказы").
| Отношение | Тип | Столбец подстановки | Столбец |
|---|---|---|---|
| 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, эта связь и будет использоваться по умолчанию в формулах, если не указано иное.
Требования к связям между таблицами
Связь можно создать, если выполняются следующие требования.
| Условие | Описание |
|---|---|
| Уникальный идентификатор для каждой таблицы | Каждая таблица должна иметь один столбец, однозначно определяющий каждую строку в этой таблице. Такой столбец часто именуется первичным ключом. |
| Столбцы уникального подстановки | Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок. |
| Совместимые типы данных | Типы данных в исходном столбце и в столбце подстановки должны быть совместимыми. Дополнительные сведения о типах данных см. в статье Типы данных, поддерживаемые в моделях данных. |
Неподдерживаемые функции баз данных в модели данных Excel
В модели данных нельзя создать связь между таблицами, если ключ является составным. Также существует ограничение на создание связей «один к одному» и «один ко многим». Другие типы связей не поддерживаются.
Составные ключи и столбцы подстановки
Составной ключ состоит из нескольких столбцов. Составные ключи не могут быть задействованы в моделях данных: в таблице всегда должен быть только один столбец, однозначно определяющий каждую строку. При импорте таблиц, у которых уже есть отношение на основе составного ключа, мастер импорта таблиц в Power Pivot проигнорирует эту связь, так как ее нельзя создать в модели.
Для создания связи между двумя таблицами, имеющими несколько столбцов, в которых определены первичный и внешние ключи, сначала объедините значения для создания единого ключевого столбца. Это можно сделать перед импортом данных или путем создания вычисляемого столбца в модели данных с помощью надстройки Power Pivot.
Связи «многие ко многим»
Модель данных не может иметь связи «многие ко многим». В модель нельзя добавлять соединяющие таблицы . Тем не менее для моделирования связей «многие ко многим» можно использовать функции DAX.
Самосоединения и циклы
В модели данных не разрешается использование самосоединений. Самосоединение — это рекурсивная связь таблицы с самой собой. Самосоединения часто используются для определения иерархий типа «родители-потомки». Например, можно настроить самосоединение для таблицы Employees, чтобы создать иерархию, показывающую цепочку управления на предприятии.
Excel не позволяет создавать циклы среди связей в книге. Иными словами, следующий набор связей запрещается.
Таблица 1, столбец a-таблица 2, столбец f
Таблица 2, столбец f-таблица 3, столбец n
Таблица 3, столбец n - Таблица 1, столбец a
При попытке создания связи, которая приведет к образованию цикла, выдается ошибка.
Автоматическое обнаружение и вывод связей в Power Pivot
Одно из преимуществ импорта данных с помощью надстройки Power Pivot заключается в том, что Power Pivot иногда может обнаруживать связи и создавать новые связи в модели данных, создаваемой в Excel.
При импорте нескольких таблиц Power Pivot автоматически обнаруживает все существующие связи между таблицами. Кроме того, при создании сводной таблицы Power Pivot анализирует содержащиеся в ней данные. Он обнаруживает возможные связи, которые не были определены, и предлагает столбцы, которые можно включить в них.
Алгоритм обнаружения на основании статистических данных о значениях и метаданных столбцов формирует выводы о вероятности связей.
- Типы данных во всех связанных столбцах должны быть совместимыми. Для автоматического обнаружения поддерживаются только целочисленные и текстовые типы данных. Дополнительные сведения о типах данных см. в разделе Типы данных, поддерживаемые в моделях данных.
- Для успешного обнаружения связи количество уникальных ключей в столбце подстановки должно превышать количество значений в таблице на стороне «многие». Другими словами, ключевой столбец на стороне «многие» связи не должен содержать значений, не содержащихся в ключевом столбце таблицы подстановки. Например, предположим, что имеется таблица, в которой перечислены продукты и их идентификаторы (таблица подстановки), а также таблица продаж, содержащая данные продаж всех продуктов (сторона «многие» связи). Если записи продаж содержат идентификатор продукта, не имеющего соответствующий идентификатор в таблице Products, связь нельзя создать автоматически, но можно создать вручную. Для обеспечения обнаружения связи с помощью Excel необходимо сначала обновить таблицу подстановки Product с использованием идентификаторов недостающих продуктов.
- Убедитесь, что имя ключевого столбца на стороне «многие» совпадает с именем ключевого столбца в таблице подстановки. Имена не должны быть абсолютно идентичны. Например, в бизнес-среде часто есть вариации имен столбцов, содержащих по существу одни и те же данные: EMP ID, ID сотрудника, ID сотрудника EMP_ID и т. д. Алгоритм выявляет похожие имена и задает более высокие значения вероятности столбцам, имена которых похожи или полностью совпадают. Поэтому, чтобы увеличить вероятность создания связи, можно попытаться переименовать столбцы в импортируемых данных, подобрав имена чем-то похожие на имена строк в существующих таблицах. Если Excel находит несколько возможных связей, связь не создается.
Эти сведения помогают понять, почему не удалось выявить все связи и какие изменения в метаданных (именах полей и типах данных) могут повысить эффективность автоматического обнаружения связей. Дополнительные сведения см. в разделе Устранение неполадок в связях.
Автоматическое обнаружение именованных наборов
Связи между именованными наборами и связанными полями в сводной таблице не обнаруживаются автоматически. Такие связи можно создать вручную. При необходимости использования автоматического обнаружения связей удалите каждый именованный набор и добавьте отдельные поля из именованного набора непосредственно в сводную таблицу.
Вывод связей
В некоторых случаях связи между таблицами автоматически объединяются в цепочки. Например, если создать связь между первыми двумя наборами таблиц, указанных ниже, то определяется наличие связи между другими двумя таблицами и эта связь устанавливается автоматически.
Products и Category — связь создается вручную
Category и SubCategory — связь создается вручную
Products и SubCategory — связь определяется автоматически
Для автоматического объединения связей в цепочки эти связи должны идти в одном направлении, как показано выше. Если исходные связи были установлены, например между таблицами Sales и Products, а также между Sales и Customers, то связь не выводится. Это вызвано тем, что связь между таблицами Products и Customers является связью «многие ко многим».