Посібник. Розширення зв’язків моделі даних за допомогою 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 ServerSQL 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 містить таблицю, яка називається "Хости". Ми імпортували хости , скопіювавши їх і вставивши в програму Excel, а потім відформатували дані як таблицю. Щоб додати таблицю Hosts до моделі даних, потрібно встановити зв'язок. Давайте наочно представимо зв'язки в моделі даних за допомогою надбудови Power Pivot, а потім створимо зв'язок.

1. У програмі Excel перейдіть на вкладку Hosts , щоб зробити її активним аркушем.

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

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

   

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

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

   

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

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

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

   

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

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

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

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

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

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

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

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

        

Створення обчислюваних стовпців за допомогою мови 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. Спочатку створіть стовпець у таблиці медалей , як ми це зробили для господарів. У надбудові Power Pivot виділіть таблицю "Медалі" та натисніть кнопку "Додати стовпці конструктора >>". Зверніть увагу, що вибрано команду "Додати стовпець ". Результат буде такий самий, як і просте натискання кнопки "Додати стовпець".

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

   = YEAR([Edition])
   
   

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

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

5. Коли ви створили новий стовпець, надбудова Power Pivot додала ще один стовпець покажчика місця заповнення, який називається "Додати стовпець". Далі потрібно створити обчислюваний стовпець EditionID, тому виберіть Add Column (Додати стовпець). Введіть наведену нижче формулу DAX у рядку формул і натисніть клавішу Enter.

   =CONCATENATE([Year],[Season])

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

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

   

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

Наступним кроком є створення зв'язку між полями Hosts і Medals.

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

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

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

   

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

3. Розташуйте таблицю Hosts так, щоб вона знаходилася поруч із таблицями Medals.

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

   

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

Create a hierarchy

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

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

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

Створення ієрархії спортивних змагань

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

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

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

   

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

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

   Пустий батьківський вузол ієрархії відображається внизу таблиці.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ВІКТОРИНА

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

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

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

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

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

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

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

Відповідь. TRUE (істина)

Б. FALSE

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

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

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

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

Г. Варіанти А та Б.

Питання 4: Що з наведеного нижче справедливого щодо ієрархій?

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

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

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

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

Відповіді на опитування

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