Підводні камені використання Excel Power Query і MySQL для автоматизації звітності +9
- 06.01.16 5:19 •
- kpimaker •
- # 274615 •
- Хабрахабр •
- 0 •
- 8400
- такий же як Forbes, тільки краще.
Якщо звіти потрібно оновлювати щотижня або навіть щодня, то ця процедура стає вельми напряжно навіть для самих терплячих. За допомогою надбудови Excel Power Query і записи даних в MySQL можна звести оновлення більшості звітів до простого натискання кнопки «Оновити»:
1. Дані з будь-якої кількості джерел імпортуються через SQL-запити в звичайні таблиці Excel.
2. Навіть з великої бази можна записувати в Excel тільки невелику частину даних (наприклад, підсумкові суми за потрібний діапазон дат з угрупованням тільки по потрібним стовпцях).
3. Зміни до звіту можна вносити просто помінявши SQL-запит. Далі формуємо потрібний звіт стандартними засобами Excel.
У цій статті я покажу як налаштовувати і автоматично заповнювати прості бази даних MySQL (на прикладі вивантаження статистики всіх ключових слів з Яндекс Метрики), а потім однією кнопкою оновлювати звіти в Excel, використовуючи надбудову Power Query. Power Query має вельми дивні особливості роботи при складанні SQL-запитів (особливо динамічних), які ми розберемо в другій частині статті.
Вибір MySQL (або будь-який інший популярної бази даних) цілком очевидний - безкоштовно, щодо просто, можливість працювати з досить великими базами даних без технічних хитрощів. Як приклад використовуватимемо Amazon Web Services: дешево (в більшості випадків використовується інстанси буде безкоштовний для вас протягом 12 місяців).
Отже, почнемо (якщо у вас уже є бази даних з готовими даними, то можна відразу переходити до розділу з Excel):
1. Реєструємося на AWS (якщо ще немає учеткі), запускаємо найпростіший інстанси t2.micro і заходимо на нього по SSH. Можна подивитися коротку інструкцію в минулому пості habrahabr.ru/post/265383. Зверніть увагу, що нам буде потрібно перший в списку варіант інстанси на Amazon Linux AMI. Необхідно виставити правила, що дозволяють звернення до інстанси по потрібним портам:
2. Виконуємо поспіль команди, описані в документації docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdf. Нам потрібна глава «Tutorial: Installing a LAMP Web Server on Amazon Linux». Запам'ятайте пароль, який вводите при виконанні команди «sudo mysql_secure_installation». Для зручності встановіть phpMyAdmin як описано в кінці цієї глави. Якщо будете копіпаст з документації рядок «sudo sed -i -e 's / 127.0.0.1 / your_ip_address / g' / etc / ht tpd / conf.d / phpMyAdmin.conf», то зверніть увагу, що іноді при копіюванні в «httpd »з'являється зайвий пробіл.
Після цих дій на вашому інстанси повинна відкриватися така сторінка:
3. Заходимо під користувачем root і паролем, який вводили при налаштуванні. Для доступу до бази даних «ззовні» (т. Е. З Excel) нам буде потрібно користувач, відмінний від root. Заводимо його в інтерфейсі phpMyAdmin в меню Користувачі -> Додати користувача. Додамо користувача stats, задамо пароль і призначимо йому привілеї SELECT і INSERT. Разом отримаємо:
4. Тепер створимо базу даних data:
5. В даному прикладі будемо наповнювати базу статистикою відвідувань за ключовими словами з Яндекс Метрики. Для цього створимо таблицю seo (зверніть увагу, що у стовпці id треба відзначити опцію A_I (auto increment)):
Код PHP для вивантаження даних Яндекс Метрики
Якщо виникнуть помилки при з'єднанні з базою, то вони відобразяться в консолі і виконання буде перервано. У разі успішного виконання отримаємо статистику ключових слів за обраний період:
Відмінно, дані отримані. Подивимося як отримувати їх в Excel.
Використання Power Query для вивантаження даних в Excel
Power Query є надбудову, яка розширює можливості Excel по вивантаженню даних. Завантажити можна тут www.microsoft.com/en-us/download/details.aspx?id=39379. Для роботи з MySQL може знадобитися MySQL Connector і Visual Studio (пропонуються при установці з дистрибутива).
1. Після установки вибираємо MySQL:
2. В якості бази вказуємо ID нашого інстанси (як було в скрипті) ec2-. compute.amazonaws.com. База даних data. Для введення логіна вибираємо «База даних»:
3. У вікні, двічі натискаємо на таблицю seo і отримуємо:
В цьому вікні можна управляти запитами, змінюючи стовпці і кількість рядків. Коли база даних невелика, то це працює. Однак якщо розмір даних перевищує навіть 20MB, то Excel на більшості комп'ютерів просто зависне від такого запиту. До того ж непогано б міняти дати запиту або інші параметри.
Динамічні запити в Power Query можна робити за допомогою вбудованої мови M msdn.microsoft.com/en-us/library/mt253322.aspx. однак запити вкрай нестійкі в плані зміни будь-яких параметрів в них. Щоб запит залишався «постійним» зробимо наступний прийом:
1. Спочатку складаємо таблицю, в якій вказуємо потрібні нам параметри. У нашому прикладі це дата вивантаження. Формат ячеек зі значеннями краще виставити як тестовий, т. К. Excel любить змінювати формат осередків на свій розсуд:
2. Створимо запит Power Query «З таблиці», який буде просто дублювати цю таблицю:
3. В опціях запиту обов'язково вкажіть формат другого шпальти як Текст, інакше наступний SQL-запит буде некоректним. Далі тиснемо «Закрити і завантажити».
Разом ми отримали запит Power Query до звичайної таблиці, з якого буде брати значення початку і кінця вивантаження.
Щоб зробити SQL-запит буде потрібно відключити одну опцію: заходимо в Параметри і настройки -> Налаштування пошуку -> Конфіденційність і вибираємо «Ігнорувати рівні конфіденційності для можливого поліпшення продуктивності». Тиснемо Ок.
В параметрах startDate і endDate вказуються значення в таблиці з пункту 3. При запиті «Для виконання цього власного запиту до бази даних необхідні дозволи» тиснемо «Редагувати дозвіл», перевіряємо, що всі параметри підтягнулися коректно і виконуємо запит. Тепер отриману відповідь від SQL-запиту можна обробити звичайними формулами Excel в звичному вам вигляді.
5. Важливо! Коли ви будете оновлювати вивантаження в наступний раз, то це доводиться робити в такий спосіб (інші чомусь дають помилку):
- міняємо дати в таблиці з пункту 1
- заходимо в меню Дані -> Підключення та натискаємо «Оновити все»:
В цьому випадку всі запити виконуватися коректно і ваші звіти для поновлення всіх. Разом для поновлення звіту вам буде потрібно тільки змінити параметри запиту та натиснути "Оновити все».
Ви можете допомогти і перевести трохи коштів на розвиток сайту