Посібник. Розширення зв’язків моделі даних за допомогою Excel, Power Pivot і DAX

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

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

Зміст посібника:

• Додавання зв'язку за допомогою подання схеми в надбудові Power Pivot
• Розширення моделі даних за допомогою обчислюваних стовпців
• Create a hierarchy
• Використання ієрархій у зведених таблицях
• Контрольна точка й вікторина

Наприкінці цього посібника пропонується вікторина, за допомогою якої можна перевірити свої знання.

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

1. Імпорт даних у програму Excel і створення моделі даних
2. Розширення зв'язків моделі даних за допомогою excel, Power Pivot і DAX
3. Створення звітів Power View на основі карт
4. Включення даних з Інтернету й установлення стандартних параметрів для звітів Power View
5. Довідка Power Pivot
6. Створення вражаючих звітів Power View. Частина 2

Ми радимо вивчати посібники по черзі.

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

Додавання зв'язку за допомогою подання схеми в надбудові Power Pivot

У цьому розділі ви використовуєте надбудову Microsoft Office Power Pivot у програмі Excel 2013, щоб розширити модель. Використання подання схеми в Microsoft SQL Server Надбудова Power Pivot для Excel спрощує створення зв'язків. Спочатку потрібно переконатися, що надбудову Power Pivot увімкнуто.

Примітка. Надбудова Power Pivot у Microsoft Excel 2013 входить до складу Office Professional Plus. Докладні відомості див. в статті Запуск надбудови Power Pivot у Microsoft Excel 2013.

Додавання надбудови Power Pivot до стрічки Excel за допомогою надбудови Power Pivot

Якщо надбудову Power Pivot увімкнуто, у програмі Excel 2013 з'явиться вкладка стрічки з назвою POWER PIVOT. Щоб увімкнути надбудову Power Pivot, виконайте наведені нижче дії.

1. Перейдіть до розділу Надбудови параметрів > файлу>.
2. У діалоговому вікні Керування внизу клацніть Елемент Надбудови> COM Перейти.
3. Установіть прапорець Microsoft Office Power Pivot у Microsoft Excel 2013, а потім натисніть кнопку OK.

Стрічка Excel тепер має вкладку POWER PIVOT .

Додавання зв'язку за допомогою подання схеми в надбудові Power Pivot

Книга Excel містить таблицю з назвою Hosts. Ми імпортували hosts , скопіювавши його та вставивши в Excel, а потім відформатували дані як таблицю. Щоб додати таблицю Hosts до моделі даних, потрібно встановити зв'язок. Давайте використовуватимемо надбудову Power Pivot, щоб візуально представити зв'язки в моделі даних, а потім створити зв'язок.

1. В Excel перейдіть на вкладку Hosts (Хости ), щоб зробити її активним аркушем.

2. На стрічці виберіть елемент Додати до моделі даних таблиці > POWER PIVOT>. Цей крок додає таблицю Hosts до моделі даних. Вона також відкриває надбудову Power Pivot, яка використовується для виконання решти кроків у цьому завданні.

3. Зверніть увагу, що у вікні Power Pivot відображаються всі таблиці в моделі, включно з Hosts. Клацніть кілька таблиць. У надбудові Power Pivot можна переглянути всі дані, які містить модель, навіть якщо вони не відображаються в жодному аркуші Excel, наприклад дані про дисципліни, події та медалі нижче, а також дані про S_Teams, W_Teams та спорт.

   

4. У вікні Power Pivot у розділі Подання натисніть кнопку Подання схеми.

5. Використовуйте панель слайдів, щоб змінити розмір схеми, щоб відобразити всі об'єкти на схемі. Перевпорядкуйте таблиці, перетягуючи їхній рядок заголовка, щоб вони відображались і розташовувалися поруч один з одним. Зверніть увагу, що чотири таблиці не пов'язані з іншими таблицями: Hosts, Events, W_Teams і S_Teams.

   

6. Ви помітите, що в таблиці Medals і Events є поле DisciplineEvent. Під час подальшої перевірки ви визначаєте, що поле DisciplineEvent у таблиці Events складається з унікальних неповсюджених значень.

1. Створіть зв'язок між таблицею Medals і таблицею Events . У поданні схеми перетягніть поле DisciplineEvent із таблиці Events до поля DisciplineEvent у medals. Між ними з'явиться лінія, яка вказує на встановлення зв'язку.

2. Клацніть лінію, яка з'єднує події та медалі. Виділені поля визначають зв'язок, як показано на наступному екрані.

   

3. Щоб підключити Hosts до моделі даних, нам потрібне поле зі значеннями, які однозначно ідентифікують кожен рядок у таблиці Hosts . Потім ми зможемо виконати пошук у моделі даних, щоб дізнатися, чи є ці дані в іншій таблиці. Пошук у поданні схеми не дозволяє нам робити це. Якщо вибрано параметр Hosts (Хости ), поверніться до подання даних.

4. Розглянувши стовпці, ми розуміємо, що в Hosts немає стовпця унікальних значень. Нам доведеться створити його за допомогою обчислюваного стовпця та виразів аналізу даних (DAX).

Це зручно, коли дані в моделі даних містять усі поля, необхідні для створення зв'язків, і зіставте дані, щоб візуалізувати дані в надбудові Power View або зведених таблицях. Але таблиці не завжди так кооперативні, тому в наступному розділі описано, як створити новий стовпець, використовуючи DAX, який можна використовувати для створення зв'язку між таблицями.

Розширення моделі даних за допомогою обчислюваних стовпців

Щоб установити зв'язок між таблицею Hosts і моделлю даних і тим самим розширити модель даних, щоб включити таблицю Hosts , Hosts має мати поле, яке однозначно ідентифікує кожен рядок. Крім того, це поле має відповідати полю в моделі даних. Ці відповідні поля ( по одному в кожній таблиці) дають змогу пов'язувати дані таблиць.

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

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

У Hosts можна створити унікальний обчислюваний стовпець, поєднавши поле Edition (рік проведення Олімпійських ігор) і поле Season (літо або зима). У таблиці Medals також є поле Edition і поле Season, тому, якщо створити обчислюваний стовпець у кожній із цих таблиць, що поєднує поля Edition і Season, ми зможемо встановити зв'язок між Hosts і Medals. На знімку екрана нижче показано таблицю Hosts з вибраними полями "Випуск" і "Сезон"

        

Створення обчислюваних стовпців за допомогою DAX

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

У надбудові Power Pivot можна використовувати вирази аналізу даних (DAX) для створення обчислень. DAX – це мова формул для надбудов Power Pivot і зведених таблиць, призначена для реляційних даних і контекстного аналізу, доступних у надбудові Power Pivot. Формули DAX можна створювати в новому стовпці Power Pivot і в області обчислення в надбудові Power Pivot.

1. У надбудові Power Pivot виберіть пункт Подання даних HOME > View>, щоб переконатися, що подання даних вибрано, а не подання схеми.

2. Виберіть таблицю Hosts у надбудові Power Pivot. Суміжний із наявними стовпцями пустий стовпець під назвою "Додати стовпець". Power Pivot надає цей стовпець як покажчик місця заповнення. Додати новий стовпець до таблиці в надбудові Power Pivot можна багатьма способами, один із яких – просто вибрати пустий стовпець із заголовком Додати стовпець.

   

3. Введіть зазначену нижче формулу DAX у рядку формул. Функція CONCATENATE об'єднує два або кілька полів в одне. Під час введення автозаповнення дає змогу вводити повні імена стовпців і таблиць, а також списки доступних функцій. Скористайтеся клавішею табуляції, щоб вибрати варіанти автозаповнення. Крім того, можна просто клацнути стовпець, ввівши формулу, і надбудова Power Pivot вставить ім'я стовпця у формулу.

   =CONCATENATE([Edition],[Season])

4. Завершивши створення формули, натисніть клавішу Enter, щоб прийняти її.

5. Значення заповнюються для всіх рядків обчислюваного стовпця. Якщо прокрутити таблицю вниз, ви побачите, що кожен рядок унікальний, тому ми успішно створили поле, яке однозначно ідентифікує кожен рядок у таблиці Hosts . Такі поля називаються первинним ключем.

6. Перейменовувати обчислюваний стовпець на EditionID. Ви можете перейменувати будь-який стовпець, двічі клацнувши його або клацнувши його правою кнопкою миші та вибравши команду Перейменувати стовпець. Після завершення таблиця Hosts у надбудові Power Pivot матиме такий вигляд:

   

Таблиця Hosts готова. Далі давайте створимо обчислюваний стовпець у medals , який відповідає формату стовпця EditionID, створеного в Hosts, щоб створити зв'язок між ними.

1. Спочатку створіть новий стовпець у таблиці Medals , як у Hosts. У надбудові Power Pivot виберіть таблицю Medals і натисніть кнопку Додати стовпці > макета>. Зверніть увагу, що вибрано додати стовпець . Це має такий самий ефект, як і просто натисніть кнопку Додати стовпець.

2. Стовпець Edition у medals має інший формат, ніж стовпець Edition у Hosts. Перш ніж об'єднувати або об'єднувати стовпець Edition зі стовпцем Season, щоб створити стовпець EditionID, потрібно створити проміжне поле, яке переходить у правильний формат. У рядку формул над таблицею введіть наведену нижче формулу DAX.

   = YEAR([Edition])
   
   

3. Завершивши створення формули, натисніть клавішу Enter. Значення заповнюються для всіх рядків обчислюваного стовпця на основі введеної формули. Якщо порівняти цей стовпець зі стовпцем Edition у Hosts, ці стовпці мають однаковий формат.

4. Перейменуйте стовпець, клацнувши правою кнопкою миші обчислюваний стовпець1 і вибравши команду Перейменувати стовпець. Введіть Рік і натисніть клавішу Enter.

5. Під час створення нового стовпця надбудова Power Pivot додала ще один стовпець покажчика місця заповнення під назвою "Додати стовпець". Далі ми хочемо створити обчислюваний стовпець EditionID, тому виберіть Додати стовпець. У рядку формул введіть наведену нижче формулу DAX і натисніть клавішу Enter.

   =CONCATENATE([Year],[Season])

6. Перейменуйте стовпець, двічі клацнувши Обчислюваний_стовпець1 і ввівши EditionID.

7. Відсортуйте стовпець за зростанням. Тепер таблиця Medals у надбудові Power Pivot має такий вигляд:

   

Зверніть увагу, що в полі Medals таблиці EditionID повторюється багато значень. Це нормально і очікується, оскільки під час кожного випуску Олімпійських ігор (зараз представлено значенням EditionID) було нагороджено багато медалей. Унікальність у таблиці Medals – це кожна нагороджена медаль. Унікальним ідентифікатором для кожного запису в таблиці Medals і призначеного первинного ключа є поле MedalKey.

Наступний крок – створити зв'язок між хостами та медалями.

Створення зв'язку за допомогою обчислюваних стовпців

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

1. У вікні Power Pivot на стрічці виберіть Подання схеми в > головному поданні>. Ви також можете переключатися між поданням "Сітка" та "Схема", використовуючи кнопки в нижній частині вікна PowerView, як показано на наступному екрані.

   

2. Розгорніть вузол Хости , щоб переглянути всі його поля. Ми створили стовпець EditionID, щоб виступати в ролі первинного ключа таблиці Hosts (унікальне поле, яке не повторюється) і створили стовпець EditionID у таблиці Medals , щоб забезпечити встановлення зв'язку між ними. Ми повинні знайти їх обох, і створити відносини. Надбудова Power Pivot містить функцію пошуку на стрічці, яка дає змогу шукати відповідні поля в моделі даних. На знімку екрана нижче показано вікно Пошук метаданих із параметром EditionID, введеним у поле Знайти .
   

3. Розташуйте таблицю Hosts поруч із таблицею Medals.

4. Перетягніть стовпець EditionID у Medals до стовпця EditionID в Hosts. Power Pivot створює зв'язок між таблицями на основі стовпця EditionID і креслює лінію між двома стовпцями, що позначає зв'язок.

   

У цьому розділі ви вивчили новий метод додавання нових стовпців, створили обчислюваний стовпець за допомогою DAX і використали цей стовпець для встановлення нового зв'язку між таблицями. Тепер таблицю Hosts інтегровано в модель даних, а її дані доступні у зведеній таблиці аркуша 1. Пов'язані дані також можна використовувати для створення додаткових зведених таблиць, зведених діаграм, звітів Power View тощо.

Create a hierarchy

Більшість моделей даних містять дані, які за своєю суттю є ієрархічними. Поширені приклади: дані календаря, географічні дані та категорії продуктів. Створювати ієрархії в надбудові Power Pivot зручно, оскільки можна перетягувати один елемент до звіту (ієрархії), а не збирати й упорядкувати однакові поля знову й знову.

Дані про Олімпійські ігри також ієрархічні. З точки зору спорту, спортивних змагань і подій корисно розуміти ієрархію Олімпійських ігор. Для кожного виду спорту є одна або кілька пов'язаних дисциплін (іноді їх багато). І для кожної дисципліни відбувається одна або кілька подій (знову ж таки, іноді в кожній дисципліні багато подій). На зображенні нижче показано ієрархію.

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

Створення ієрархії "Вид спорту"

1. У надбудові Power Pivot перейдіть до подання схеми. Розгорніть таблицю Події , щоб полегшити перегляд усіх її полів.

2. Натисніть і утримуйте клавішу Ctrl, а потім клацніть поля Sport, Discipline і Event. Виділивши ці три поля, клацніть правою кнопкою миші та виберіть команду Створити ієрархію. У нижній частині таблиці створюється батьківський вузол ієрархії (Ієрархія 1), а вибрані стовпці копіюються під ієрархією як дочірні вузли. Переконайтеся, що поле Sport відображається спочатку в ієрархії, а потім – Discipline, а потім – Event.

3. Двічі клацніть назву Ієрархія1 і введіть SDE , щоб перейменувати нову ієрархію. Тепер у вас є ієрархія, яка включає спорт, дисципліну та подію. Тепер таблиця "Події" має такий вигляд:

   

Створення ієрархії розташування

1. У поданні схеми в надбудові Power Pivot виберіть таблицю Hosts і натисніть кнопку Створити ієрархію в заголовку таблиці, як показано на знімку екрана нижче.
   

   У нижній частині таблиці з'явиться пустий батьківський вузол ієрархії.

2. Введіть "Розташування " як ім'я нової ієрархії.

3. Додати стовпці до ієрархії можна багатьма способами. Перетягніть поля Season, City та NOC_CountryRegion на ім'я ієрархії (у цьому випадку "Розташування"), доки не буде виділено ім'я ієрархії, а потім відпустіть, щоб додати їх.

4. Клацніть правою кнопкою миші EditionID і виберіть Додати до ієрархії. Виберіть Розташування.

5. Переконайтеся, що дочірні вузли ієрархії впорядковано. Згори вниз порядок має бути: Season, NOC, City, EditionID. Якщо дочірні вузли вийшли з ладу, просто перетягніть їх у відповідне впорядкування в ієрархії. Таблиця має виглядати так:
   

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

Використання ієрархій у зведених таблицях

Тепер, коли ми маємо ієрархію "Спорт" і "Розташування", ми можемо додати їх до зведених таблиць або надбудови Power View та швидко отримати результати, які містять корисні групи даних. Перш ніж створювати ієрархії, потрібно було додати окремі поля до зведеної таблиці та впорядкувати поля так, як їх потрібно переглядати.

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

1. Поверніться до Excel.
2. У Вікні 1 видаліть поля з області РЯДКИ полів зведеної таблиці, а потім видаліть усі поля з області СТОВПЦІ. Переконайтеся, що вибрано зведену таблицю (яка зараз досить маленька, щоб вибрати клітинку A1, щоб переконатися, що зведену таблицю вибрано). У полях зведеної таблиці залишаються лише поля Medal в області FILTERS і Кількість медалей в області ЗНАЧЕННЯ. Майже пуста зведена таблиця має виглядати так:
   
3. З області Поля зведеної таблиці перетягніть SDE з таблиці Events до області ROWS. Потім перетягніть елемент Розташування з таблиці Hosts до області СТОВПЦІ . Просто перетягнувши ці дві ієрархії, зведена таблиця заповнюється великою кількістю даних, усі вони розташовані в ієрархії, визначеній на попередніх кроках. Екран має виглядати так:
   
4. Давайте трохи відфільтруймо ці дані та лише побачимо перші десять рядків подій. У зведеній таблиці клацніть стрілку в розділі Підписи рядків, виберіть пункт (Виділити все), щоб видалити всі виділені елементи, а потім клацніть поля поруч із першою десятою спортивною таблицею. Тепер зведена таблиця має такий вигляд:
   
5. Ви можете розгорнути будь-який із цих видів спорту у зведеній таблиці , яка є верхнім рівнем ієрархії SDE, і переглянути інформацію на наступному рівні вниз в ієрархії (дисципліні). Якщо для цієї дисципліни існує нижчий рівень ієрархії, ви можете розширити дисципліну, щоб побачити її події. Ви можете зробити те ж саме для ієрархії розташування, верхній рівень якого – Season, який відображається як "Літо" та "Зима" у зведеній таблиці. Коли ми розширюємо спорт Aquatics, ми бачимо всі його елементи дисципліни дитини та їх дані. Коли ми розширюємо дисципліну дайвінгу під водних видів спорту, ми також бачимо її дитячі події, як показано на наступному екрані. Ми можемо зробити те ж саме для Water Polo, і побачити, що в ньому є тільки одна подія.
   

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

Тепер давайте створимо ту саму зведену таблицю без переваги ієрархій.

1. В області Поля зведеної таблиці видаліть розташування з області СТОВПЦІ. Потім видаліть SDE з області ROWS. Ви повернулися до простої зведеної таблиці.
2. З таблиці Hosts перетягніть поле Season, City, NOC_CountryRegion і EditionID до області COLUMNS і розташуйте їх у такому порядку згори вниз.
3. З таблиці Events перетягніть поле Sport, Discipline і Event до області ROWS і розташуйте їх у такому порядку згори вниз.
4. У зведеній таблиці відфільтруйте позначки рядків до першої десятки видів спорту.
5. Згорніть всі рядки і стовпці, а потім розгорніть водні види спорту, потім дайвінг і водне поло . Книга виглядатиме так:
   

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

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

Контрольна точка й вікторина

Стислий огляд вивченого матеріалу

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

Ви дізналися, що створення ієрархій дає змогу визначити в даних невід'ємну структуру та швидко використовувати ієрархічні дані у звітах.

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

Посібник 3. Створення звітів Power View на основі карт

ВІКТОРИНА

Хочете перевірити, наскільки добре запам’ятали пройдений матеріал? Ось ваш шанс. Наведена нижче вікторина стосується функцій, можливостей і вимог, описаних у цьому посібнику. Відповіді наведено в нижній частині сторінки. Бажаємо успіхів!

Запитання 1: Яке з наведених нижче подань дає змогу створити зв'язки між двома таблицями?

В. Ви створюєте зв'язки між таблицями в надбудові Power View.

Б. Зв'язки між таблицями створюються за допомогою режиму конструктора в надбудові Power Pivot.

В. Ви створюєте зв'язки між таблицями за допомогою подання сітки в надбудові Power Pivot

Г. Усі перелічені вище.

Запитання 2: TRUE або FALSE. Можна встановити зв'язки між таблицями на основі унікального ідентифікатора, створеного за допомогою формул DAX.

A: TRUE

B: FALSE

Запитання 3: У якому з наведених нижче параметрів можна створити формулу DAX?

В. В області обчислення надбудови Power Pivot.

Б. У новому стовпці в надбудові Power Pivotf.

В. У будь-якій клітинці Excel 2013.

Г. І A, і B.

Запитання 4: Яка з наведених нижче умов стосується ієрархій?

В. Під час створення ієрархії включені поля більше не будуть доступні окремо.

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

В. Під час створення ієрархії базові дані в моделі даних об'єднуються в одне поле.

Г. У надбудові Power Pivot не можна створювати ієрархії.

Відповіді на вікторину

1. Правильна відповідь: Г
2. Правильна відповідь: A
3. Правильна відповідь: Г
4. Правильна відповідь: Б