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

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

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

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 — Перевірте наявність порожніх значень

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

Завдання

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

Завдання зосереджене на тих самих навичках діагностики, що демонструвалися у відео: спочатку перевірити вихідні таблиці, виправити дані на аркуші, оновити Data Model, а потім перевірити результат за допомогою PivotTable.

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

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

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

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

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

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

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

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

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

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

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

Після внесення виправлень перейдіть до Data → Refresh All, щоб оновити Data Model.

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

Крок 4 — Перевірка за допомогою PivotTable

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

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

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

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

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

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

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

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