Загальний принцип Power QueryНайпростіший спосіб спіймати шість левів: зловити десять і відпустити чотири
Коротка відповідь на питання з заголовку – так.
По-перше, щоб якнайшвидше набути табличне мислення і перестати використовувати інструмент як Excel «на стероїдах», або розмальовку.
По-друге, щоб розуміти, про що йдеться у цій статті. Дуже глибокі пізнання тут насправді непотрібні.
Для демонстрації використаємо Microsoft Power BI, майже все може бути застосовано і в інших інструментах, що містять Power Query.
MS Power BI здійснює підключення до багатьох джерел, зокрема реляційних баз даних. Стандартно підтримуються найбільш популярні SQL Server, MySQL, PostgreSQL.
При цьому використовується таке явище як query folding. Визначення в офіційній документації як завжди хірургічно точне та майже марне:
Згортання запиту — це можливість для запиту Power Query генерувати єдиний оператор запиту для отримання та перетворення вихідних даних.
Це означає, що Power Query може надсилати деякі перетворення у джерело його «рідною» мовою і ці перетворення відбуваються саме в джерелі, раніше ніж будь-які дані надсилаються до Power Query.
Вже трішки зрозуміліше, але найкращий спосіб – побачити це у дії з використанням популярного набору даних Adwenture Works (сервер: «xlgdemos.database.windows.net», база даних: «AdventureWorks») При підключенні до джерела обираєте відповідний конектор (в даному випадку SQL Server)
В діалоговому вікні ви можете натиснути «Додаткові параметри» і – якщо ви майстер SQL – одразу ввести інструкцію-запит. Застереження: введення будь-якого власного запиту автоматично вимикає подальше згортання запитів. Тому залишаємо його пустим.
Після підключення бачимо перелік таблиць у базі, обираємо лише потрібні:
Я роблю це в інтерфейсі Power Query, тому після вибору достатньо просто натиснути «ОК». Якщо ви підключаєтесь безпосередньо з Power BI – там буде ще кнопка «Перетворити дані». Вона вам і потрібна.
Маємо 3 запити: довідник користувачів (847 рядків), довідник адрес (450) і своєрідний міст між ними (417).
Завдання: надіслати поштові листівки користувачам із Великої Британії (які дуже консервативно відносяться до традицій). Таких в нас 38. Як ви розумієте, приклад дуже умовний.
«Так треба було одразу писати SELECT FROM JOIN WHERE писати!» – скажуть одні. І будуть праві.
«Треба робити об’єднання запитів, фільтрацію, скорочення кількості стовпців. Комп’ютер тільки б потужніший…» – скажуть інші. І теж будуть праві.
Але якщо ви можете отримати результативний набір у 38 рядків, не написав жодного на SQL і без навантаження на ваш комп’ютер? «Магія» – скажете ви? Ні, це і є query folding.
Щоб це працювало, треба додержуватись невеличкої кількості простих правил, які ми спробуємо зібрати наприкінці статті. Зараз важливо, як розуміти, що це працює.
Як відомо, кожен крок перетворення даних фіксується в Power Query на панелі «Застосовані кроки» праворуч на екрані. Якщо клікнути правою клавішею миші на будь-якому з кроків, у контекстному меню можна побачити пункт «Переглянути власний запит». Якщо він активний – на цьому кроці згортання відбувається, якщо його клікнути – можна побачити сам запит. Якщо затінений – згортання зламалось. Поки цього достатньо.
Тож спочатку об’єднаємо запити Customer і CustomerAddress (сподіваюсь, скорочення зрозумілі) по полю CustomerID:
Після об’єднання з’явився новий крок і ми можемо бачити, що гортання для нього працює:
Обравши цей пункт ми бачимо…справжнісінький SQL-запит!
Але це ще не цікаво, далі буде. Продовжимо.
Розгортаємо стовпець об’єднання, залишаємо тільки AddressID
Новий крок, новий стовпець, запит згортається:
А сам запит? Запит змінився! І саме він (останній) буде переданий в базу даних:
Тепер маючи AddressID можемо зробити аналогічне об’єднання з таблицею адрес (тільки обрати всі необхідні поля). Пропустимо всі дії, зосередимось на результаті:
Нові кроки, нові поля, новий запит:
Всі стовпці нової таблиці нам точно непотрібні. Тож приберемо зайві:
Змінилась не лише кількість стовпців на екрані, а й у запиті.
Але ж нас цікавила Велика Британія. Відфільтровуємо – запит згортається!
І наостанок об’єднаємо ПІБ та адреси наших отримувачів
І маємо результат: 38 повних адрес наших користувачів з Великої Британії з їх повними іменами.
А також фінальний SQL-запит, який і буде виконаний нашою БД (з подякою від її адміністратора)
Для перевірки можна створити нове підключення до БД, але на цей раз вставити скопійований запит у відповідне поле діалогового вікна (перший скрін статті). Результат буде той самий. Вітаю, ви SQL-ніндзя! 😊
Інші запити Power Query нам більше не потрібні, тож їх завантаження можна вимкнути (права клавіша миші – зняти галку з «Увімкнути завантаження»)
Тож підсумуємо:
- Згортання запитів – простий та чудовий спосіб прискорити завантаження ваших даних та зменшити їх обсяг
- Згортання відбувається при виконанні операцій, підтримуваних вашою БД з одного джерела (видалення, перейменування стовпців; фільтрація рядків; об’єднання запитів; групування; додавання користувацьких стовпців з простою логікою; поворот і відміна; інші)
- Операції з різними джерелами та складна логіка доданих стовпців не згортаються.
Насправді, вичерпного переліку підтримуваних операцій не існує. Як то кажуть в нашому світі «це залежить».
- Написання власної SQL-інструкції при підключенні до БД унеможливлює згортання
- Коли ви вставляєте непідтримуваний крок, згортання ламається. У джерело надсилається запит, сформований до нього, а всі подальші перетворення відбуваються вже у Power Query. Відповідно треба ретельно слідкувати за черговістю перетворень і за можливості перебудовувати їхню комбінацію, щоб їх максимальний обсяг відбувався на боці БД.
- Завжди читайте документацію: https://learn.microsoft.com/en-us/power-query/power-query-folding
Долучайтесь до нашої спільноти Telegram
* Data Life UA
* Data Analysis UA
* DATA ENGINEERING UA
Долучайтесь до нашої спільноти FaceBook
* Data-Life-UA
