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

Створення завдання
Зв’язки в Power View і PowerPivot (відео)

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

CustomerID

Name

EMail

DiscountRate

OrderID

OrderDate

Product

Quantity

1

Артюхін

artem.artyuhin@contoso.com

0,05

256

01.07.2010

Компактна цифрова фотокамера

11

1

Артюхін

artem.artyuhin@contoso.com

0,05

255

01.03.2010

Дзеркальна фотокамера

15

2

Ярощук

mykola.yaroshchuk@contoso.com

0,10

254

01.03.2010

Бюджетна відеокамера

27

Цей підхід можливий, однак він призводить до збереження надлишкових даних, наприклад адреси електронної пошти для кожного замовлення. Це дуже просто, однак якщо адреса електронної пошти клієнта зміниться, її необхідно буде оновити в кожному рядку для цього клієнта. Одне із можливих рішень цієї проблеми – розподіл даних між кількома таблицями та визначення зв’язків між цими таблицями. Такий підхід використовується в реляційних базах даних, таких як SQL Server. Наприклад, імпортована база даних може зберігати дані замовлень у трьох пов’язаних таблицях:

Customers

[CustomerID]

Name

Email

1

Артюхін

artem.artyuhin@contoso.com

2

Ярощук

mykola.yaroshchuk@contoso.com

CustomerDiscounts

[CustomerID]

DiscountRate

1

0,05

2

0,10

Orders

[CustomerID]

OrderID

OrderDate

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 може визначити зв’язки між таблицями на підставі стовпців, взятих у [прямокутні дужки], і відтворити ці зв’язки в автоматично створеній моделі даних. Докладні відомості див. в розділі Автоматичне виявлення та припущення зв’язків. Якщо імпортуються таблиці з різних джерел, зв’язки можна створити вручну, як описано в статті Створення зв’язків між двома таблицями.

На початок сторінки

У цій статті

Стовпці та ключі

Типи зв’язків

Зв’язки та продуктивність

Кілька зв’язків між таблицями

Вимоги до зв’язків між таблицями

Непідтримувані зв’язки між таблицями

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

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

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

Автоматичне виявлення та припущення зв’язків у PowerPivot

Автоматичне виявлення іменованих наборів

Припущення зв’язків

Стовпці та ключі

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

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

Для нас найцікавішими будуть такі ключі:

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

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

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

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

Типи зв’язків

Зв’язок між таблицями Customers і Orders – це зв’язок "один-до-багатьох". У кожного клієнта може бути кілька замовлень, але для одного замовлення є лише один клієнт. Також можуть бути зв’язки "один-до-одного" та "багато-до-багатьох". Таблиця 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, він буде зв’язком за замовчуванням у формулах, якщо ви не зазначите інше.

На початок сторінки

Вимоги до зв’язків між таблицями

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

Умови

Опис

Унікальний ідентифікатор для кожної таблиці

У кожній таблиці повинен бути один стовпець, який унікально визначатиме кожний рядок у цій таблиці. Цей стовпець часто називають первинним ключем.

Унікальні стовпці підстановки

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

Пов’язані типи даних

Типи даних у вихідному стовпці та стовпці підстановки мають бути сумісними. Докладні відомості про типи даних див. у статті Типи даних, що підтримуються вмоделях даних.

Непідтримувані зв’язки між таблицями

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

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

До складеного ключа входять кілька стовпців. Складені ключі не можна використовувати в моделях даних. Лише один стовпець у таблиці має містити унікальний ідентифікатор, який визначає кожний рядок у таблиці. Якщо спробувати імпортувати таблиці з наявним зв’язком на основі складеного ключа, майстер імпорту таблиць у 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 аналізує дані в таблицях. У ній виявляються можливі, але ще не визначені зв’язки та пропонуються відповідні стовпці для цих зв’язків.

Алгоритм виявлення використовує статистичні дані значень і метаданих стовпців, щоб припустити ймовірність зв’язків.

  • Типи даних у всіх пов’язаних стовпцях мають бути сумісними. Автоматичне виявлення підтримується лише для цілих чисел і текстових типів даних. Докладні відомості про типи даних див. у статті Типи даних, що підтримуються в моделях даних.

  • Для успішного виявлення зв’язку кількість унікальних ключів у стовпці підстановки має бути більшою за значення в таблиці на стороні "багато". Іншими словами, ключовий стовпець на стороні "багато" у зв’язку не може містити значення, відсутні у ключовому стовпці таблиці підстановки. Наприклад, у вас є таблиця, у якій наведено список продуктів з ідентифікаторами (таблиця підстановки), і таблиця продажів, яка містить список продажів кожного продукту (сторона зв’язку "багато"). Якщо в одному із записів продажів міститься ідентифікатор продукту, відсутній у таблиці продуктів, зв’язок не буде створено автоматично, однак ви зможете створити його вручну. Щоб програма Excel виявила зв’язок, спочатку необхідно оновити таблицю підстановки Product відсутніми ідентифікаторами продуктів.

  • Переконайтеся, що назва ключового стовпця на множинній стороні відповідає назві ключового стовпця в таблиці підстановки. Назви навіть не мають повністю збігатися. Наприклад, на підприємстві можуть часто використовуватися різновиди стовпців, які містять однакові дані: Emp ID, EmployeeID, Employee ID, EMP_ID та інші. Алгоритм виявляє схожі назви та призначає вищу ймовірність тим стовпцям, у яких назви схожі або однакові. Таким чином, для підвищення ймовірності створення зв’язку можна перейменувати назви стовпців у даних, які ви імпортуєте, на схожі назви стовпців у наявних таблицях. Якщо програма Excel знаходить кілька можливих зв’язків, то жодний зв’язок створено не буде.

Ця інформація допоможе вам зрозуміти, чому не всі зв’язки виявлено чи як зміни в метаданих – наприклад назви поля та типів даних – можуть поліпшити результати автоматичного виявлення зв’язків. Докладні відомості див. в статті Виправлення неполадок зв’язків.

Автоматичне виявлення іменованих наборів

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

Припущення зв’язків

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

  • Products і Category – створюється вручну

  • Category та SubCategory – створюється вручну

  • Products і SubCategory – зв’язок припускається

Щоб зв’язки утворювалися автоматично, такі зв’язки мають бути однаково направленими, як зазначено вище. Якщо початкові зв’язки були, наприклад, між таблицями Sales і Products, а також між таблицями Sales і Customers, зв’язок не припускається. Це відбувається тому, що зв’язок між таблицями Products і Customers – "багато-до-багатьох".

На початок сторінки

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×