Зв'язки між таблицями в моделі даних

Зв’язки засновані на стовпцях у кожній таблиці, які містять однакові дані. Наприклад, можна зв'язати таблицю "Клієнти " з таблицею "Замовлення ", якщо кожен із них містить стовпець, у якому зберігається ідентифікатор клієнта. У наведеному прикладі назви стовпців однакові, однак це необов’язкова умова. Один стовпець може називатися CustomerID, а інший – CustomerNumber, однак у всіх рядках таблиці Orders має зберігатися ідентифікатор, який також зберігається в таблиці Customers.

У реляційній базі даних є кілька типів ключів. Ключ – це зазвичай стовпець зі спеціальними властивостями. Розуміння призначення кожного ключа допоможе керувати моделлю даних, що складається з кількох таблиць, яка надає дані до зведеної таблиці, зведеної діаграмі або звіту Power View.

Хоча є багато типів ключів, це найважливіше для нашої мети тут:

• Первинний ключ– унікальний ідентифікатор рядка в таблиці, наприклад CustomerID у таблиці Customers .

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

• Зовнішній ключ– стовпець, який посилається на унікальний стовпець в іншій таблиці, наприклад CustomerID у таблиці "Замовлення ", який посилається на CustomerID у таблиці "Клієнти".

У моделі даних на первинний ключ або на альтернативний ключ посилаються як на пов’язаний стовпець. Якщо таблиця містить як первинний ключ, так і альтернативний, будь-який з них можна використовувати як основу для зв’язку між таблицями. Зовнішній ключ називають стовпцем-джерелом або просто стовпцем. У нашому прикладі зв'язок буде визначено між CustomerID у таблиці "Замовлення " (стовпець) і "Ідентифікатор клієнта " в таблиці "Клієнти " (стовпець підстановки). Якщо дані імпортуються з реляційної бази даних, за замовчуванням програма Excel вибирає зовнішній ключ з однієї таблиці та відповідний первинний ключ із іншої. Однак замість стовпця підстановки можна використовувати будь-який стовпець, який містить унікальні значення.

Зв'язок між клієнтом і замовленням – це зв'язок "один-до-багатьох". У кожного клієнта може бути кілька замовлень, але для одного замовлення є лише один клієнт. Ще один важливий зв'язок між таблицями – "один-до-одного". У нашому прикладі таблиця CustomerDiscounts , яка визначає єдину дисконтну ставку для кожного клієнта, має зв'язок "один-до-одного" з таблицею "Клієнти".

У цій таблиці показано зв'язки між трьома таблицями (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, він буде зв’язком за замовчуванням у формулах, якщо ви не зазначите інше.

Зв’язок можна створити, коли дотримано такі вимоги:

Умови	Опис
Унікальний ідентифікатор для кожної таблиці	У кожній таблиці повинен бути один стовпець, який унікально визначатиме кожний рядок у цій таблиці. Цей стовпець часто називають первинним ключем.
Унікальні стовпці підстановки	Значення даних у стовпці підстановки мають бути унікальними. Іншими словами, у таблиці не повинно бути повторень. У моделі даних нульові значення та пусті рядки рівноцінні пустим значенням, які є окремим значенням даних. Це означає, що у стовпці підстановки не може бути кілька нульових значень.
Пов’язані типи даних	Типи даних у вихідному стовпці та стовпці підстановки мають бути сумісними. Докладні відомості про типи даних див. в статті Типи даних, які підтримуються в моделях даних.

У моделі даних не можна створити зв’язок між таблицями, якщо ключ – це складений ключ. Також є обмеження для створення зв’язків один до одного та один до багатьох. Інші типи зв’язків не підтримуються.

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

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

Щоб створити зв’язок між двома таблицями, у яких кілька стовпців визначатимуть первинний і зовнішній ключі, об’єднайте значення та створіть один стовпець для ключа, перш ніж створювати зв’язок. Це можна зробити, перш ніж імпортувати дані, або створивши обчислюваний стовпець у моделі даних за допомогою надбудови Power Pivot.

Зв’язки "багато-до-багатьох"

Зв’язки "багато-до-багатьох" не можна використовувати в моделі даних. Не можна просто додати з’єднувальні таблиці до моделі. Однак для моделювання зв’язків "багато-до-багатьох" можна використовувати функції DAX.

Рефлексивні з’єднання та цикли

Рефлексивні з’єднання та цикли не можна використовувати в моделі даних. Рефлексивне з’єднання – це рекурсивний зв’язок таблиці із самою собою. Рефлексивні з’єднання часто використовуються для визначення батьківсько-дочірніх ієрархій. Наприклад, можна об’єднати таблицю Employees із нею самою, щоб отримати ієрархію для відображення ланцюжка керівництва на підприємстві.

Програма Excel не дозволяє створення циклів між зв’язками в книзі. Іншими словами, наведений далі набір зв’язків заборонений.

Від Таблиці 1, стовпець a   до   Таблиці 2, стовпець f

Від Таблиці 2, стовпець f   до   Таблиці 3, стовпець n

Від Таблиці 3, стовпець n   до   Таблиці 1, стовпець a

Якщо ви спробуєте створити зв’язок, який призведе до створення циклу, буде видано помилку.

Одна з переваг використання 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 – "багато-до-багатьох".