Створивши зв'язки між різними таблицями, можна зробити аналіз даних більш потужним. Зв'язок – це зв'язок між двома таблицями, які містять дані: один стовпець у кожній таблиці – основа зв'язку. Щоб зрозуміти користь від зв’язків, уявіть, що вам необхідно відстежувати замовлення клієнтів на вашому підприємстві. Структуру всіх даних можна відстежувати в одній таблиці, структурі якої показано нижче.
| Ідентифікатор клієнта | Ім’я | DiscountRate | OrderID | Дата замовлення | 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
| [Ідентифікатор клієнта] | Ім’я | Адреса електронної пошти |
|---|---|---|
| 1 | Артюхін | chris.ashton@contoso.com |
| 2 | Ярощук | michal.jaworski@contoso.com |
CustomerDiscounts
| [Ідентифікатор клієнта] | DiscountRate |
|---|---|
| 1 | 0,05 |
| 2 | 0,10 |
Orders
| [Ідентифікатор клієнта] | OrderID | Дата замовлення | 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 Pivot, щоб імпортувати таблиці з однієї бази даних, надбудова Power Pivot може визначити зв'язки між таблицями на підставі стовпців, взятих у [прямокутні дужки], і відтворити ці зв'язки в автоматично створеній моделі даних. Докладні відомості див. в розділі "Автоматичне виявлення та припущення зв'язків " у цій статті. Якщо імпортуються таблиці з різних джерел, зв'язки можна створити вручну, як описано в статті "Створення зв'язків між двома таблицями".
Стовпці та ключі
Зв’язки засновані на стовпцях у кожній таблиці, які містять однакові дані. Наприклад, таблицю "Клієнти " можна зв'язати з таблицею "Замовлення", якщо кожна з них містить стовпець, у якому зберігається ідентифікатор клієнта. У наведеному прикладі назви стовпців однакові, однак це необов’язкова умова. Один стовпець може називатися CustomerID, а інший – CustomerNumber, однак у всіх рядках таблиці Orders має зберігатися ідентифікатор, який також зберігається в таблиці Customers.
У реляційній базі даних є кілька типів ключів. Ключ – це зазвичай стовпець з особливими властивостями. Розуміння призначення кожного ключа допоможе керувати моделлю даних, що складається з кількох таблиць, яка надає дані до зведеної таблиці, зведеної діаграмі або звіту Power View.
Хоча існує багато типів ключів, вони є найважливішими для нашої мети.
- Первинний ключ – унікальний ідентифікатор рядка в таблиці (наприклад, стовпець CustomerID в таблиці Customers ).
- Альтернативний ключ (або можливий ключ) – другий унікальний стовпець, відмінний від первинного ключа. Наприклад, у таблиці Employees можуть зберігатися ідентифікатор працівника та код соціального страхування, обидва унікальні.
- Зовнішній ключ – стовпець, що посилається на унікальний стовпець в іншій таблиці, наприклад CustomerID в таблиці Orders , який посилається на стовпець CustomerID в таблиці Customers.
У моделі даних на первинний ключ або на альтернативний ключ посилаються як на пов’язаний стовпець. Якщо таблиця містить як первинний ключ, так і альтернативний, будь-який з них можна використовувати як основу для зв’язку між таблицями. Зовнішній ключ називають стовпцем-джерелом або просто стовпцем. У нашому прикладі зв'язок визначатиметься між стовпцем CustomerID в таблиці Orders (стовпець) і стовпцем CustomerID в таблиці Customers (стовпець підстановки). Якщо дані імпортуються з реляційної бази даних, за замовчуванням програма Excel вибирає зовнішній ключ з однієї таблиці та відповідний первинний ключ із іншої. Однак замість стовпця підстановки можна використовувати будь-який стовпець, який містить унікальні значення.
Типи зв’язків
Зв'язок між клієнтом і замовленням – це зв'язок "один-до-багатьох". У кожного клієнта може бути кілька замовлень, але для одного замовлення є лише один клієнт. Ще один важливий зв'язок таблиць – "один-до-одного". У нашому прикладі таблиця 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, він буде зв’язком за замовчуванням у формулах, якщо ви не зазначите інше.
Вимоги до зв’язків між таблицями
Зв’язок можна створити, коли дотримано такі вимоги:
| Умови | Опис |
|---|---|
| Унікальний ідентифікатор для кожної таблиці | У кожній таблиці повинен бути один стовпець, який унікально визначатиме кожний рядок у цій таблиці. Цей стовпець часто називають первинним ключем. |
| Унікальні стовпці підстановки | Значення даних у стовпці підстановки мають бути унікальними. Іншими словами, у таблиці не повинно бути повторень. У моделі даних нульові значення та пусті рядки рівноцінні пустим значенням, які є окремим значенням даних. Це означає, що у стовпці підстановки не може бути кілька нульових значень. |
| Пов'язані типи даних | Типи даних у вихідному стовпці та стовпці підстановки мають бути сумісними. Докладні відомості про типи даних див. в статті "Підтримувані в моделях даних" типи даних. |
Непідтримувані функції бази даних у моделі даних Excel
У моделі даних не можна створити зв’язок між таблицями, якщо ключ – це складений ключ. Також є обмеження для створення зв’язків один до одного та один до багатьох. Інші типи зв’язків не підтримуються.
Складені ключі та стовпці підстановки
До складеного ключа входять кілька стовпців. Складені ключі не можна використовувати в моделях даних: таблиця завжди має містити лише один стовпець, який однозначно ідентифікує кожний рядок у таблиці. Якщо ви імпортуєте таблиці з наявним зв'язком на основі складеного ключа, майстер імпорту таблиць у надбудові Power Pivot проігнорує цей зв'язок, тому що його не можна створити в моделі.
Щоб створити зв’язок між двома таблицями, у яких кілька стовпців визначатимуть первинний і зовнішній ключі, об’єднайте значення та створіть один стовпець для ключа, перш ніж створювати зв’язок. Це можна зробити, перш ніж імпортувати дані, або створивши обчислюваний стовпець у моделі даних за допомогою надбудови Power Pivot.
Зв’язки "багато-до-багатьох"
Зв’язки "багато-до-багатьох" не можна використовувати в моделі даних. Не можна просто додати з’єднувальні таблиці до моделі. Однак для моделювання зв’язків "багато-до-багатьох" можна використовувати функції DAX.
Рефлексивні з’єднання та цикли
Рефлексивні з’єднання та цикли не можна використовувати в моделі даних. Рефлексивне з’єднання – це рекурсивний зв’язок таблиці із самою собою. Рефлексивні з’єднання часто використовуються для визначення батьківсько-дочірніх ієрархій. Наприклад, можна об’єднати таблицю Employees із нею самою, щоб отримати ієрархію для відображення ланцюжка керівництва на підприємстві.
Програма Excel не дозволяє створення циклів між зв’язками в книзі. Іншими словами, наведений далі набір зв’язків заборонений.
Від Таблиці 1, стовпець a до Таблиці 2, стовпець f
Від Таблиці 2, стовпець f до Таблиці 3, стовпець n
Від Таблиці 3, стовпець n до Таблиці 1, стовпець a
Якщо ви спробуєте створити зв’язок, який призведе до створення циклу, буде видано помилку.
Автоматичне виявлення та припущення зв’язків у PowerPivot
Одна з переваг імпорту даних за допомогою надбудови Power Pivot полягає в тому, що надбудова Power Pivot іноді може виявляти та створювати нові зв'язки в моделі даних, створеній у програмі Excel.
Якщо імпортувати кілька таблиць, надбудова Power Pivot автоматично виявляє наявні зв'язки між таблицями. Крім того, коли ви створюєте зведену таблицю, надбудова Power Pivot аналізує дані в таблицях. У ній виявляються можливі, але ще не визначені зв’язки та пропонуються відповідні стовпці для цих зв’язків.
Алгоритм виявлення використовує статистичні дані значень і метаданих стовпців, щоб припустити ймовірність зв’язків.
- Типи даних у всіх пов'язаних стовпцях мають бути сумісними. Автоматичне виявлення підтримується лише для цілих чисел і текстових типів даних. Докладні відомості про типи даних див. в статті " Підтримувані в моделях даних" типи даних.
- Для успішного виявлення зв’язку кількість унікальних ключів у стовпці підстановки має бути більшою за значення в таблиці на стороні "багато". Іншими словами, ключовий стовпець на стороні "багато" у зв’язку не може містити значення, відсутні у ключовому стовпці таблиці підстановки. Наприклад, у вас є таблиця, у якій наведено список продуктів з ідентифікаторами (таблиця підстановки), і таблиця продажів, яка містить список продажів кожного продукту (сторона зв’язку "багато"). Якщо в одному із записів продажів міститься ідентифікатор продукту, відсутній у таблиці продуктів, зв’язок не буде створено автоматично, однак ви зможете створити його вручну. Щоб програма Excel виявила зв’язок, спочатку необхідно оновити таблицю підстановки Product відсутніми ідентифікаторами продуктів.
- Переконайтеся, що назва ключового стовпця на множинній стороні відповідає назві ключового стовпця в таблиці підстановки. Назви навіть не мають повністю збігатися. Наприклад, на підприємстві можуть часто використовуватися різновиди стовпців, які містять однакові дані: Emp ID, EmployeeID, Employee ID, EMP_ID тощо. Алгоритм виявляє схожі назви та призначає вищу ймовірність тим стовпцям, у яких назви схожі або однакові. Таким чином, для підвищення ймовірності створення зв’язку можна перейменувати назви стовпців у даних, які ви імпортуєте, на схожі назви стовпців у наявних таблицях. Якщо програма Excel знаходить кілька можливих зв’язків, то жодний зв’язок створено не буде.
Ця інформація допоможе вам зрозуміти, чому не всі зв'язки виявлено чи як зміни в метаданих – наприклад назви поля та типів даних – можуть поліпшити результати автоматичного виявлення зв'язків. Докладні відомості див. в статті "Виправлення неполадок зв'язків".
Автоматичне виявлення іменованих наборів
У зведеній таблиці зв’язки між іменованими наборами та пов’язаними полями не виявляються автоматично. Їх можна створювати вручну. Якщо потрібно використовувати автоматичне виявлення зв’язків, видаліть кожний іменований набір і додайте окремі поля з іменованого набору безпосередньо до зведеної таблиці.
Припущення зв’язків
У деяких випадках зв’язки між таблицями утворюються автоматично. Наприклад, якщо створити зв’язок між першими двома таблицями з набору, припускається існування зв’язку між іншими двома таблицями, і такий зв’язок буде встановлено автоматично.
Products і Category – створюється вручну
Category та SubCategory – створюється вручну
Products і SubCategory – зв’язок припускається
Щоб зв’язки утворювалися автоматично, такі зв’язки мають бути однаково направленими, як зазначено вище. Якщо початкові зв’язки були, наприклад, між таблицями Sales і Products, а також між таблицями Sales і Customers, зв’язок не припускається. Це відбувається тому, що зв’язок між таблицями Products і Customers – "багато-до-багатьох".