Діагностика та усунення проблем із зв'язками

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

Чотири основні причини

1. Дублікати значень у первинному ключі
   
   У таблиці вимірів одне й те саме значення ключа зустрічається в кількох рядках. Оскільки для сторони «один» у зв'язку потрібні унікальні значення, Power Pivot відмовляється створювати зв'язок і показує помилку.
   
   

Помилка: "relationship cannot be created because each column contains duplicate values"

Виправлення: Вкладка Дані → Видалити дублікати. Позначте "Мої дані мають заголовки", виберіть стовпець ключа, натисніть OK. Оновіть Power Pivot перед повторною спробою.

2. Невідповідність типів даних
   
   Стовпець ключа у таблиці вимірів зберігається з іншим типом даних, ніж зовнішній ключ у таблиці фактів — наприклад, Product ID є числом у таблиці Products, але текстом у таблиці Sales. Power Pivot дозволяє створити зв'язок, але аналіз буде некоректним, оскільки значення насправді не збігаються.
   
   

Симптом: зв'язок створюється без помилки, але підсумки у зведеній таблиці неправильні або відсутні рядки.

Виправлення: вирівняйте типи даних у обох таблицях, щоб обидва стовпці мали однаковий тип і форматування. Оновіть і перебудуйте модель.

3. Осиротілі зовнішні ключі
   
   

У таблиці фактів міститься значення ключа, якого немає у таблиці вимірів. Наприклад, рядок у Sales посилається на Customer ID C099, але такого клієнта немає у таблиці Customers. Такі рядки непомітно виключаються з аналізу — вони не з'являються у зведених таблицях, а їхні значення не враховуються у підсумках.

Симптом: підсумки у зведених таблицях трохи менші, ніж очікується; у деяких розбивках може з'явитися порожній рядок.

Виправлення: використайте COUNTIF, щоб знайти ключі таблиці фактів, яких немає у таблиці вимірів. Додайте відсутні рядки у таблицю вимірів або виправте значення ключів у таблиці фактів.

4. Порожні значення ключа
   
   

   Порожні клітинки у стовпці ключа будь-якої з таблиць. Порожнє значення у стовпці ключа таблиці вимірів унеможливлює зіставлення таких рядків. Порожнє значення у стовпці ключа таблиці фактів означає, що ці рядки продажів не можуть бути віднесені до жодного елемента вимірів — вони групуються у безіменний порожній рядок у зведених таблицях, спотворюючи кожну розбивку.
   
   Симптом: у розбивках зведених таблиць з'являється порожній рядок, у який потрапляють продажі, що належать невизначеним клієнтам або продуктам.
   
   Виправлення: відфільтруйте стовпець ключа за порожніми значеннями за допомогою випадаючого списку. Видаліть рядок або заповніть правильне значення ключа. Оновіть усе та перебудуйте зведену таблицю.

Контрольний список для діагностики

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

• Крок 1 — Перевірте наявність зв'язку

Відкрийте Power Pivot → вкладка Конструктор → Керування зв'язками. Переконайтеся, що потрібний зв'язок є у списку, з правильними таблицями та правильними стовпцями з обох сторін. Легко випадково зв'язати не той стовпець.

• Крок 2 — Перевірте напрямок

У режимі діаграми подивіться на позначки 1 і * на лінії зв'язку. Кінець таблиці вимірів повинен мати 1, а кінець таблиці фактів — *. Якщо вони переплутані, видаліть зв'язок і створіть його заново, перетягнувши від таблиці вимірів.

• Крок 3 — Перевірте дублікати у первинному ключі

На аркуші відкрийте таблицю вимірів. Виберіть стовпець ключа і скористайтеся Дані → Видалити дублікати (на копії) або формулою COUNTIF, щоб перевірити, чи якесь значення зустрічається більше одного разу. Швидкий спосіб: додайте тимчасовий допоміжний стовпець із формулою =COUNTIF($A:$A, A2) і відфільтруйте значення, що перевищують 1.

• Крок 4 — Перевірте типи даних

  Клацніть будь-яку клітинку у стовпці ключа таблиці вимірів і подивіться на формат у групі Число на вкладці Основне. Зробіть те саме для стовпця зовнішнього ключа у таблиці фактів. Обидва мають бути одного типу — Текст або Число, не змішані.

• Крок 5 — Перевірте осиротілі зовнішні ключі

  Використайте COUNTIF у стовпці зовнішнього ключа таблиці фактів, посилаючись на стовпець ключа таблиці вимірів: =COUNTIF(Customers[CustomerID], Sales[CustomerID]). Будь-який рядок із нульовим результатом має значення зовнішнього ключа, якого немає у таблиці вимірів. Перевірте та виправте ці рядки.

• Крок 6 — Перевірте наявність порожніх значень

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

Завдання

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

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

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

Крок 1 — Перевірка моделі

Відкрийте робочу книгу та перегляньте всі чотири аркуші: Customers, Products, Dates і Sales.

Далі відкрийте Power Pivot → Manage та перемкніться у Diagram View або Manage Relationships.

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

Крок 2 — Пошук і виправлення проблем у даних

У цій робочій книзі є три типи проблем зі зв'язками, розглянутих у уроці. Ваше завдання — знайти їх і виправити.

Перевірте наявність:

• Дубльованих значень у ключовому стовпці таблиці вимірів;
• Порожніх значень у ключовому стовпці таблиці вимірів;
• Рядків фактів, дата яких не має відповідного рядка у таблиці Dates

Виправте проблеми безпосередньо у таблицях на аркуші.

Крок 3 — Оновлення моделі

Після внесення виправлень перейдіть до Data → Refresh All для оновлення моделі даних.

Потім поверніться до Power Pivot → Manage та переконайтеся, що модель підтримує правильні зв'язки.

Крок 4 — Перевірка за допомогою зведеної таблиці

Створіть зведену таблицю на основі This Workbook's Data Model та використайте її для перевірки, чи спрацювали ваші виправлення.

Мінімально перевірте наступне:

• Region з Customers та Total з Sales;
• Category з Products та Total з Sales;
• Year або MonthName з Dates та Total з Sales.

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

Критерії успіху

Завдання вважається виконаним, якщо:

• Виправлено проблему з дубльованим ключем;
• Виправлено проблему з порожнім ключем;
• Виправлено проблему з відсутніми датами;
• Модель підтримує коректний аналіз по всіх чотирьох таблицях.

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