•  
  •  
  •  
  •  
1 1 1 1 1 1 1 1 1 1 Рейтинг 5.00 (1 Голос)
Подводные камни использования Excel Power Query и MySQL для автоматизации отчетности - 5.0 out of 5 based on 1 vote

Пора обновить инструменты для упрощения скучной механической работы. Отделы аналитики, маркетинга, продаж часто сталкиваются со следующими трудностями при обновлении отчетности:

  1. Данные приходится собирать воедино из нескольких источников.
  2. Отчеты составляются в Excel, что накладывает значительные ограничения на объем обрабатываемых данных.
  3. Внесение изменений в заранее настроенные разработчиками выгрузки дело как правило не самое быстрое.

Если отчеты нужно обновлять еженедельно или даже ежедневно, то эта процедура становится весьма напряжной даже для самых терпеливых. С помощью надстройки 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. Обратите внимание, что нам потребуется первый в списке вариант инстанса на Amazon Linux AMI. Необходимо выставить правила, разрешающие обращение к инстансу по нужным портам:

1

В целях безопасности лучше выставлять ограничения на IP-адрес. Если у вас динамический IP, то это проблемная опция. Также иногда ограничение доступа к MYSQL по IP вызывает ошибку в Excel. Если выставить любой IP, то все работает.

2. Исполняем подряд команды, описанные в документации docs.aws.amazon.com/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» появляется лишний пробел.

После этих действий на вашем инстансе должна открываться такая страница:

2

3. Заходим под пользователем root и паролем, который вводили при настройке. Для доступа к базе данных «извне» (т. е. из Excel) нам потребуется пользователь, отличный от root. Заводим его в интерфейсе phpMyAdmin в меню Пользователи --> Добавить пользователя. Добавим пользователя stats, зададим пароль и назначим ему привилегии SELECT и INSERT. Итого получим:

 3

 4. Теперь создадим базу данных data:

 4

5. В данном примере будем наполнять базу статистикой посещений по ключевым словам из Яндекс Метрики. Для этого создадим таблицу seo (обратите внимание, что у столбца id надо отметить опцию A_I (auto increment)):

5

6. Для получения статистики по ключевым словам из Яндекс Метрики можно использовать следующий скрипт. В качестве параметров нужно указать начальную и конечную дату выгрузки (переменные $startDate и $endDate), авторизационный токен (в коде есть описание как его получить), номер счетчика, из которого нужно получить статистику, и параметры базы данных: ID инстанса, логин (у нас «stats»), пароль и название базы (у нас «data»). Скопируйте в корневую папку инстанса этот код и запустите командой «php seo.php».

Код PHP для выгрузки данных Яндекс Метрики 

// начало и конец периода выгрузки
$startDate = '2015-10-01';
$endDate = '2015-12-31';

// токен для запросов к API Яндекс Метрики
// для получения токена создаем приложение на https://oauth.yandex.ru/
// даем приложению разрешение на чтение статистики Яндекс Метрики
// после создания берем параметр ID приложения и подставляем в конец https://oauth.yandex.ru/authorize?response_type=token&client_id=
// жмем Разрешить и получаем токен
$atoken = '';

// номер счетчика
$project = '';

// соединяемся с базой данных и проверяем все ли в порядке
$con = mysqli_connect("ec2-....compute.amazonaws.com","stats","здесь пароль", "data");

if (mysqli_connect_errno()) {
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
	exit();
} else {
	echo "Connection successfull \n";
}

if ($con->query("SET NAMES 'utf8'")) {
	echo "Names set \n";
} else {
	printf("Error: %s\n", $con->error);
	exit();
}

// файл, в который записываем результаты выгрузки (в случае ошибки записи в базу данных по нему можно увидеть какая строчка записалась последней и соответственно дала ошибку)
$fname = "data.txt";
$fp = fopen($fname, "w");
fclose($fp);

function getBatch($startDate, $endDate, $project, $offset, $limit, $atoken, $fname, $con) {
	// делаем запрос к API Яндекс Метрики
	// документация https://tech.yandex.ru/metrika/doc/api2/api_v1/attributes/visitssource_/search-docpage/
	$ch = curl_init();
	$options = array(
		CURLOPT_URL => 'https://api-metrika.yandex.ru/stat/v1/data?oauth_token='.$atoken.'&id='.$project.'&accuracy=full&date1='.$startDate.'&date2='.$endDate.'&dimensions=ym:s:<attribution>SearchPhrase&metrics=ym:s:visits&limit='.$limit.'&offset='.$offset,
		CURLOPT_RETURNTRANSFER => TRUE
	);

	curl_setopt_array($ch, $options);
	$data = curl_exec($ch);
	curl_close($ch);

	// переводим ответ запроса формата JSON в массив
	$data = json_decode($data, true);

	// смотрим какой ответ получили от Яндекс Метрики
	//var_dump($data);

	// проходим по массиву $data['data'] и записываем период выгрузки, ключевую фразу и количество визитов
	for ($i=0; $i < count($data['data']); $i++) {
		file_put_contents($fname, $startDate."\t".$endDate."\t".$data['data'][$i]['dimensions'][0]['name']."\t".$data['data'][$i]['metrics'][0]."\n", FILE_APPEND);

		if ($con->query("INSERT INTO seo (startDate, endDate, query, visits) VALUES ('".$startDate."', '".$endDate."', '".mysqli_real_escape_string($con, substr($data['data'][$i]['dimensions'][0]['name'], 0, 255))."', ".$data['data'][$i]['metrics'][0].")")) {
			//echo "Record done \n";
	    } else {
	    	printf("Error: %s\n", $con->error);
	    	exit();
	    }
	}

	if (count($data['data']) == 0) {
		return 'done';
	} else {
		return 'more';
	}
}

// https://tech.yandex.ru/metrika/doc/api2/api_v1/data-docpage/
// максимальное количество строк в одной таблице - 10 000
// в цикле выгружается по $limit строк, увеличивая при этом первую строчку выгрузки $offset на значение $limit
$offset = 1;
$limit = 10000;

$res = 'more';
do {
	$res = getBatch($startDate, $endDate, $project, $offset, $limit, $atoken, $fname, $con);
	$offset += $limit;
} while ($res == 'more');

mysqli_close($con);

 

 Если возникнут ошибки при соединении с базой, то они отобразятся в консоли и выполнение будет прервано. В случае успешного выполнения получим статистику ключевых слов за выбранный период:

6

 Отлично, данные получены. Посмотрим как получать их в Excel.

Использование Power Query для выгрузки данных в Excel
Power Query представляет собой надстройку, которая расширяет возможности Excel по выгрузке данных. Скачать можно тут www.microsoft.com/en-us/download/. Для работы с MySQL может потребоваться MySQL Connector и Visual Studio (предлагаются при установке из дистрибутива).

1. После установки выбираем MySQL:

 7

2. В качестве базы указываем ID нашего инстанса (как было в скрипте) ec2-....compute.amazonaws.com. База данных data. Для ввода логина выбираем «База данных»:

8

3. В открывшемся окне дважды кликаем на таблицу seo и получаем:

9

В этом окне можно управлять запросами, изменяя столбцы и количество строчек. Когда база данных небольшая, то это работает. Однако если размер данных превышает даже 20MB, то Excel на большинстве компьютеров просто повиснет от такого запроса. К тому же неплохо бы менять даты запроса или другие параметры.

Динамические запросы в Power Query можно делать с помощью встроенного языка M msdn.microsoft.com/en-us/library/mt253322.aspx, однако запросы крайне неустойчивы в плане изменения каких-либо параметров в них. Чтобы запрос оставался «постоянным» сделаем следующий прием:

1. Сначала составляем таблицу, в которой указываем нужные нам параметры. В нашем примере это дата выгрузки. Формат ячеек со значениями лучше выставить как тестовый, т. к. Excel любит изменять формат ячеек по своему усмотрению:

10

2. Создадим запрос Power Query «Из таблицы», который будет просто дублировать эту таблицу:

11

3. В опциях запроса обязательно укажите формат второго столбца как Текст, иначе последующий SQL-запрос будет некорректным. Далее жмем «Закрыть и загрузить».

12

Итого мы получили запрос Power Query к обычной таблице, из которого будет брать значение начала и конца выгрузки.

Чтобы сделать SQL-запрос потребуется отключить одну опцию: заходим в Параметры и настройки --> Параметры запроса --> Конфиденциальность и выбираем «Игнорировать уровни конфиденциальности для возможного улучшения производительности». Жмем Ок.

13

4. Теперь делаем запрос к нашей базе данных, указывая в качестве начала и конца периода значения таблицы из пункта 3. Снова подключаемся к базе в Power Query и нажимаем «Расширенный редактор» в меню.

14

Например, мы хотим получить сумму визитов, которые принесли ключевые слова, содержащие «2015». На языке M запрос выглядит так:

 

let
Source = MySQL.Database("ec2-....compute.amazonaws.com", "data", [Query="select sum(visits) from seo where startDate>='"&Text.From(Таблица1{0}[Значение])&"' and endDate<='"&Text.From(Таблица1{1}[Значение])&"' and query like '%2015%';"])
in
Source

 

В параметрах startDate и endDate указываются значения в таблице из пункта 3. При запросе «Для выполнения этого собственного запроса к базе данных необходимы разрешения» жмем «Редактировать разрешение», проверяем, что все параметры подтянулись корректно и выполняем запрос. Теперь полученный ответ от SQL-запроса можно обработать обычными формулами Excel в привычном вам виде.

5. Важно! Когда вы будете обновлять выгрузку в следующий раз, то это приходится делать следующим способом (другие почему-то дают ошибку):
— меняем даты в таблице из пункта 1
— заходим в меню Данные --> Подключения и нажимаем «Обновить все»:

15

В этом случае все запросы выполнятся корректно и ваши отчеты обновятся автоматически. Итого для обновления отчета вам потребуется только изменить параметры запроса и нажать «Обновить все».

Портфолио
Память о Вас и Ваших близких на многие поколения
Подробнее
Прокат металла
Подробнее
Интернет-магазин кожи и меха
Подробнее
100% оригинальная парфюмерия в Москве
Подробнее
Вьетнамский ресторан премиум класса
Подробнее
Внедрение информационных систем
Подробнее
Организация международных конференций
Подробнее
Производство молочной продукции
Подробнее
Спортивный сайт
Подробнее
Интернет-магазин мебели и аксессуаров
Подробнее
Интернет-магазин электротранспорта
Подробнее
Сайт института актуальной экономики
Подробнее
Наши клиенты
Парк развлечений Boom Zoom
Алгор
Норбит
Molga Consulting
Metrotile
Нетология
Monqi
Премиум Пак
Aasha Herbals
Аджва
Салон красоты Сударушка
Пава
ТЦ &quot;Панфиловский&quot;
Фитнес Лаборатория
Система Главбух
Vanguard
GoAsia
ТЦ «Солнечный ветер»
Teledoc
Tchernov Cable
Отзывы
Благодарю компанию web-now.pro за помощь в разработке и запуске проекта POLITSECRETS.RU. Перед нами стояла задача – внедрить проект в сжатые сроки и по оптимальной цене. Порадовало то, что мне подроб...
Вера БлашенковаСекреты успешных выборов, Москва... апр.2016
Мне очень понравился подход с которым нас встретили "Ваша задача заниматься бизнесом, наша - сделать Вам представительство в сети". После этого ребята разработали полное тз на проект, мы внесли пожела...
МаксимIT-TASK, Москва... янв.2016
Работа проделана хорошо! Дизайнер и менеджер на отлично. Надеюсь на сотрудничество в дальнейшем. Есть шероховатости в деталях по задачам, но приятно сказывается оперативность и желание исправить, внес...
БруноСоциальная сеть След Жизни, Москва... янв.2016
Работой остались очень довольны. К работе подходят ответственно, аккуратно, открыто. Проект был сдан чуть раньше срока, по ходу работы возникали изменения, все они принимались безоговорочно, работа вы...
ЕвгенийМагазин текстиля, Москва... дек.2015
Сотрудничаем с 2007 года и сделали не один проект. Самое главное - команда умеет отстаивать своё мнение и постоянно развивается.
МарияМеждународные конференции, Москва... дек.2015
Спасибо всему коллективу компании! Разработали красивый и что самое главной рабочий интернет магазин! Реклама настроена и запущена, продажи идут, бизнес развивается! Нам постоянно подсказывают о новых...
ВадимИнтернет магазин Aromatic.pro, Москва... сен.2015
Большое спасибо команде за оперативность, качественные работы, отличный креатив и привлекательные цены!
Виктория, ОАО "Фармстандарт... июль.2015
Здравствуйте уважаемые партнеры! С наступающим Новым Годом! Желаю Вам дальнейшего процветания и успехов в Вашей благородной работе! С вами приятно сотрудничать! Очень благодарен Вам за своевременное о...
Сергей ЮрченкоКинотруд, Москва... дек.2014
Благодарим команду Brand Now и лично Дениса Логинова за оригинальное видение,разнообразие идей, четкость взаимодействия и безукоризненное соблюдение сроков выполнения проекта! Планируем продолжить сот...
ТатьянаBizness Linkerz... июль.2014
Компания КУН выражает благодарность за сотрудничество: непростая задача была выполнена в требуемые сроки и полностью удовлетворила заявленному ТЗ. Приятно удивила готовность Генерального директора нач...
Мария, Компания КУНhttp://www.kuhn.com/... апр.2014
Все отзывы
Добавить отзыв