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

Додайте більше потужності аналізу даних, створюючи взаємозв'язки різних таблиць. Зв’язок - це зв’язок між двома таблицями, що містять дані: один стовпець у кожній таблиці є основою для зв’язку. Щоб зрозуміти, чому стосунки корисні, уявіть, що ви відстежуєте дані для замовлень клієнтів у вашому бізнесі. Ви можете відстежувати всі дані в одній таблиці, що має таку структуру:

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

Взаємозв'язки існують в рамках моделі даних - такої, яку ви явно створюєте, або такої, яку Excel автоматично створює від вашого імені, коли ви одночасно імпортуєте кілька таблиць. Ви також можете використовувати надбудову Power Pivot для створення моделі або керування нею. Детальніше див. У розділі Створення моделі даних у Excel.

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

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

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

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

Первинний ключ: однозначно ідентифікує рядок у таблиці, наприклад CustomerID в Замовниках таблиця.

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

Зовнішній ключ: стовпець, який посилається на унікальний стовпець в іншій таблиці, наприклад CustomerID у Наказах таблиця, яка посилається на CustomerID у таблиці Клієнти.

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

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

Ця таблиця показує взаємозв'язок між трьома таблицями (Customers, CustomerDiscounts, та замовлення):

Примітка: Відносини багато-до-багатьох не підтримуються в моделі даних. Прикладом відносин багато-до-багатьох є прямий зв'язок між Продуктами та Клієнтами, коли клієнт може придбати багато товарів, а той самий продукт - багато клієнтів.

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

Модель даних може мати кілька взаємозв’язків між двома таблицями. Для побудови точних розрахунків Excel потребує єдиний шлях від однієї таблиці до іншої. Тому одночасно активним є лише одне відношення між кожною парою таблиць. Хоча інші неактивні, ви можете вказати неактивний зв'язок у формулах та запитах.

У режимі перегляду діаграми активний зв’язок - суцільна лінія, а неактивні - пунктирні. Наприклад, у AdventureWorksDW2012 таблиця DimDate містить стовпець DateKey, що пов'язано з трьома різними стовпцями таблиці FactInternetSales: OrderDate, DueDate, та ShipDate. Якщо активний зв'язок знаходиться між DateKey та OrderDate, це відносини за замовчуванням у формулах, якщо не вказано інше.

Відносини можуть бути створені, коли виконуються наступні вимоги:

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

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

Унікальні пошукові стовпці

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

Сумісні типи даних

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

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

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

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

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

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

Самоз’єднання та петлі

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

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

Таблиця 1, колонка a до таблиці 2, колонка f

Таблиця 2, колонка f до таблиці 3, колонка n

Таблиця 3, колонка n до таблиці 1, колонка a

Якщо ви намагаєтеся створити зв'язок, що призведе до створення циклу, генерується помилка.

Однією з переваг імпорту даних за допомогою надбудови Power Pivot є те, що Power Pivot іноді може виявляти зв’язки та створювати нові зв’язки в моделі даних, яку вона створює в Excel.

Коли ви імпортуєте кілька таблиць, Power Pivot автоматично виявляє всі існуючі взаємозв'язки між таблицями. Також під час створення зведеної таблиці Power Pivot аналізує дані в таблицях. Він виявляє можливі зв’язки, які не були визначені, і пропонує відповідні стовпці для включення в ці зв’язки.

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

Типи даних у всіх пов'язаних стовпцях повинні бути сумісними. Для автоматичного виявлення підтримуються лише цілі типи та текстові типи даних. Для отримання додаткової інформації про типи даних див. Типи даних, що підтримуються в моделях даних.

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

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

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

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

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

Висновок про стосунки

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

Товари та категорія - створюються вручну

Категорія та підкатегорія - створюються вручну

Товари та підкатегорія - висновок про взаємозв'язок

Для того, щоб відносини були автоматично ланцюговими, відносини повинні йти в одному напрямку, як показано вище. Якщо початкові відносини були, наприклад, між "Продажами та продуктами" та "Продажами" та "Клієнтами", зв'язок не виводиться. Це пов’язано з тим, що відносини між Продуктами та Клієнтами є відносинами багато-до-багатьох.