Этапы решения задачи: Подготовка и создание таблиц в Google Sheets Подготовка и создание таблиц в Google BigQuery Настройка регулярного обновления данных Создание библиотеки дат в BigQuery DWH Создание отчета в Power BI 1. Подготовка и создание таблиц в Google Sheets & BigQuery Для начала нам нужно зайти в режим инкогнито, потому что Google не всегда корректно работает когда вы вошли во множество аккаунтов Google.
Авторизуемся в аккаунте
Заходим на сайт таблиц и создаем пустую таблицу
Кликаем по кнопке меню "Дополнения" ➜ "Установить дополнение"
Добавляем дополнение Google Analytics.
После установки нужно запустить расширение.
Справа появится окно в котором можно ввести имя отчета, выбрать аккаунт - ресурс - представление Google Analytics. Задать параметры и метркии и необходимый сегмент пользователей, но это уже не обязательно.
Появляется пустая таблица, либо уже с теми данными что вы ввели при запуске.
1 - название отчета 2 - ид представления GA 3 - дата начала выгрузки отчета 4 - дата окончания выгрузки отчета 5 - показатели 6 - параметры 7 - фильтры 8 - лимит строк при выгрузке Заполняем таблицу тем, что хотим увидеть в отчетах
Запускаем работу расширения.
Через некоторое время, в зависимости от количества данных отчет появится на отдельной вкладке.
2. Подготовка и создание таблиц в Google Big Query Выбираем нужный проект
Чтобы появилась строка редактирование кода и вообще вся область с которой мы будем работать нужно выбрать SQL workspace.
Для создания набора данных, то есть таблицы куда мы будем загружать данные из Google Sheets, нужно выбрать проект, и нажать на три точки справа названия проекта:
В google bigquery появится окно, в котором вводим название нашего набора данных и кнопку создать
Открываем только что созданный набор данных
Создаем таблицу в bigquery, оболочку таблицы
Выбираем "Drive" , ссылку на нашу таблицу (открыть доступ - редактировать могут все у кого есть ссылка), формат файла Google Sheets. Название дата сета, и название для таблицы источника.
Вводим названия для наших столбцов и выбираем их тип, дата/строка/число/число с плавающей точкой и нажимаем создать таблицу.
Чтобы проверить все ли правильно загрузили пишим простой запрос: select * from `путь к нашей таблице` where date is not null
В результате видим таблицу такую же как и в Google Sheets. Сравниваем все ли в порядке, нет пустых ячеек, пропусков и т.д. а так же смотрим на количество строк.
Осталось только из таблицы источника сделать результирующую таблицу bigquery, которая в дальнейшем будет подключена к power bi. Нужно выбрать настройки запроса, который мы писали ранее и изменить там:
Выбираем галочку "назначаем путь для запроса". Выбираем проект, датасет, называем таблицу (это конечная таблица). Галочка "Write if empty" и дальше "Save"
Дальше нужно еще раз запустить наш запрос
Теперь наша результирующая таблица появилась слева
Нажимаем на нее чтобы убедиться в том, что не допустили ошибок
3. Настройка регулярного обновления данных Настраиваем регулярное обновление данных в нашей Google Sheets. Для этого нужно внести изменения в нашу таблицу, добавить в ячейки "start date" & "end date" функцию "= сегодня ( ) -1 ".
Теперь опять нужно запустить работу расширения для обновления данных в таблице
Проверяем результат
Приступаем к созданию расписания для каждодневного обновления данных в таблице за вчерашний день
Галочка - автоматический старт загрузки, каждый день, в 6-7 утра (узнайте какое у вас время в аккаунте Google чтобы подкорректировать под свой часовой пояс). Сохраняем "Save"
Переходим в Google BigQuery для обновления наших данных и создания расписания. Находим путь к нашей таблице источнику.
Проверяем обновились ли данные в таблице. Пишем просто запрос.
Результат
Теперь новые данные поступившие в таблицу источник нужно отправить в результирующую таблицу
Делаем те же действия что и раньше. Только теперь галочку нужно поставить либо "append to table" либо "overwrite table", первое добавляет в таблицу данные, второе их перезаписывает, тут уже как вы выбрали ранее.
Теперь расписание работы кода
Создаем новое расписание
Запрос для проверки, сработало ли все правильно
Проверяем появилось ли наше расписание в списке
4. Создание библиотеки дат в BigQuery Для того чтобы создать библиотеку дат нужно написать простой запрос, вытащить из таблицы неповторяющиеся значения дат и результат записать в новую таблицу. Это нужно для того чтобы в дальнейшем можно было связать разные таблицы по дате. Пишем простой запрос. Запрос: выбери не дублирующую дату из таблицы …
Выбираем проект, ид датасета, и новое название для нашей библиотеки.
В результате появится еще одна таблица слева в списке
5. Создание отчета в Power BI Загружаем данные из визуализации Google Big Query в Power BI
Входим аккаунт Google в котором у нас таблица
Выбираем какие таблицы хотим загрузить из списка
Для качественной последующей визуализации данных из biguqery, настраиваем связи между таблицами
Можно приступать к созданию таблиц, фильтров, графиков и т.д. Готовый отчет в power bi будет зависеть от того какие данные выгружаете, мы же оставим один из примеров нашего отчета power bi
