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

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

• Додання зв’язку за допомогою подання схеми в 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_ Команди, та спорту.
   
   

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

5. Скористайтеся панеллю слайдів для зміни розміру схеми, які можуть бачити всі об'єкти на діаграмі. Змінення порядку таблицями, перетягнувши їх рядок заголовка, і вони видимі та розташовані поруч із кнопкою один одного. Чотири таблиці буде непов'язані інших таблиць: Hosts, події, 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 із виділеними полями Edition і Season:

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

Почнімо з таблиці Hosts. Потрібно створити обчислюваний стовпець у таблиці Hosts, а потім – у таблиці Medals. Використовуючи ці стовпці, можна буде встановити зв’язок між таблицями.

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

1. У Power Pivot виберіть елементи Головна > Подання > Подання даних, щоб перейти з подання схеми до подання даних.

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 за допомогою функції CONCATENATE для створення стовпця EditionID, потрібно створити проміжне поле для перетворення поля Edition на правильний формат. У рядку формул над таблицею введіть указану нижче формулу DAX.

   = YEAR([Edition])
   

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

4. Перейменуйте стовпець. Для цього клацніть правою кнопкою миші напис "CalculatedColumn1" і виберіть команду Перейменувати стовпець. Введіть "Year" та натисніть клавішу Enter.

5. Якщо створити новий стовпець, у Power Pivot буде додано ще один стовпець-покажчик місця заповнення з назвою Додати стовпець. Щоб створити обчислюваний стовпець EditionID, виділіть стовпець Додати стовпець. У рядку формул введіть таку формулу DAX і натисніть клавішу Enter:
   
   =CONCATENATE([Year];[Season])

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

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

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

Далі потрібно створити зв’язок між таблицями Hosts і Medals.

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

Використаймо створені обчислювані стовпці, щоб установити зв’язок між таблицями Hosts і Medals.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. Введіть для нової ієрархії назву Locations.

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

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

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

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

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

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

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

1. Поверніться до програми Excel.

2. На аркуші Sheet1 видаліть поля з області "РЯДКИ" у вікні Поля зведеної таблиці, а потім видаліть всі поля з області "СТОВПЦІ". Переконайтеся, що зведену таблицю вибрано (зараз її розмір значно зменшився, тому, щоб вибрати зведену таблицю, досить виділити клітинку A1). У зведеній таблиці залишилися лише такі поля: Medal в області "ФІЛЬТРИ" та "Count of Medal" в області "ЗНАЧЕННЯ". Майже пуста зведена таблиця має виглядати так:

   

3. В області поля зведеної таблиці перетягніть SDE у таблиці Events до області РЯДКИ. Перетягніть розташування з таблиці Hosts до області СТОВПЦІВ . Просто перетягуючи ці дві ієрархії, буде заповнено зведену таблицю з великою кількістю даних, які розташовано в ієрархії, які визначено в попередні кроки. На екрані має виглядати так.
   

4. Давайте фільтр, що дані трохи і просто побачити перші 10 рядків події. У зведеній таблиці клацніть стрілку в Підписи рядків, натисніть кнопку (виділити все), щоб видалити всі вибрані параметри, а потім натисніть кнопку прапорці поруч з елементом спочатку десяти видів спорту. Зведена таблиця зараз виглядає так.
   

5. Можна розгорнути будь-який із цих видів спорту у зведеній таблиці, який верхнього рівня в ієрархії SDE, та відображення інформації в наступний рівень вниз в ієрархії (дисципліна). Якщо цей дисципліною нижчий рівень в ієрархії, можна розгорнути дисципліною, щоб переглянути її. Ви можете зробити так само для розташування ієрархії, верхнього рівня, з яких є поля Season, який показує, як літо "та" зима у зведеній таблиці. Якщо ми розгорнути водних видів спорту видів спорту, ми побачити всіх її дочірніх елементів дисципліною і свої дані. Якщо ми розгорнути дайвінг дисципліною в розділі водних видів спорту, ми побачимо, його дочірніх подій теж, як показано на наступному екрані. Ми можемо повторіть процедуру для води поле і бачити, що він має лише одну подію.
   

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

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

1. В області зведеної таблиці "Поля" видаліть ієрархію Locations з області "СТОВПЦІ". Потім видаліть ієрархію SDE з області "РЯДКИ". Тепер зведена таблиця набула первинного вигляду.

2. Перетягніть поля Season, City, NOC_CountryRegion та EditionID з таблиці Hosts до області "СТОВПЦІ" й розташуйте їх у такому порядку згори донизу.

3. Перетягніть поля Sport, Discipline та Event із таблиці Events до області "РЯДКИ" й розташуйте їх у такому порядку згори донизу.

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

5. Згортання всіх рядків і стовпців, а потім розгорніть водних видів спорту, а потім плавання і води поле. Книги, схожа на наступному екрані.
   

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

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

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

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

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

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

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

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

ВІКТОРИНА

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

Запитання 1. Які з цих подань дають змогу створювати зв’язки між двома таблицями?

А. Зв’язки між таблицями створюються в Power View.

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

В. Зв’язки між таблицями створюються в Power Pivot у поданні "Сітка".

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

Запитання 2. Правильно або ні? Зв’язки між таблицями створюються на основі унікального ідентифікатора, створеного за допомогою формул DAX.

А. Правильно

Б. Ні

Запитання 3. Де можна створювати формулу DAX?

А. В області обчислень Power Pivot.

Б. У новому стовпці в Power Pivot.

В. У будь-якій клітинці в програмі Excel 2013.

Г. Обидва варіанти А й Б.

Запитання 4. Яке з наведених нижче тверджень про ієрархії правильне?

А. Під час створення ієрархії додані поля більше не доступні поодинці.

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

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

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

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

1. Правильна відповідь: Г

2. Правильна відповідь: А

3. Правильна відповідь: Г

4. Правильна відповідь: Б