power bi запрос sql с параметром

Power Bi SQL запросы

Power Bi SQL запросы

Добрый день, уважаемые читатели и подписчики блога! Сегодня мы снова поговорим запросах в Power Bi, но с большим уклоном в SQL. В прошлой статье мы рассматривали основные принципы работы с запросами в Power Bi.

Имеется база данных shops, в ней располагается таблица shops_by_country, из неё нужно достать первые 20 строк по стране Россия.

Тут можно пойти двумя способами:

Давайте посчитаем количество действий через конструктор:

Составляем запрос SQL

В списке коннекторов выбираем сервер SQL, а также заполним соответствующие поля.

SELECT TOP (20) [Name]
,[Country]
,[Sales]
FROM [shops].[dbo].[shops_by_country] where [Country] = ‘Russia’

На скриншоте я оставлю столбец ID, так как это профессиональная привычка, но сам запрос будет без этого столбца.

Посмотрим, что получилось.

То есть нет нужды проваливаться в «Преобразование данных», убирать лишние строки, фильтровать данные по стране, убирать столбец ID и т.д. Правильно и просто составленный запрос сэкономит нам кучу времени.

По желанию можно настроить и дополнительные эффекты, я оставлю стандартные значения этих параметров.

Источник

Автоматизация отчетности при помощи SQL и Power BI

В своей прошлой статье я рассказывал про программные возможности языка SQL и обещал поделиться кейсом по созданию автоматизированного отчета на основе стека технологий MS SQL Server и Power BI.

Почему именно эти технологии?

За время работы аналитиком, я перепробовал различные варианты сбора отчетности. Начиная с ручной выгрузки данных из кабинетов рекламных систем, с последующим сведением в Excel, и заканчивая созданием специальных отчетов в Google Analytics или дашбордов в Data Studio.

Но ни один из вариантов не был идеальным и каждый имел свои недостатки. Все изменилось, когда я открыл для себя Power BI.

Microsoft Power BI — это один из самых технологичных на данный момент инструментов по визуализации данных, обладающий большим набором коннекторов к различным системам.

Но и Power BI сам по себе не идеален и без грамотного использования будет работать медленно и неэффективно. Приведу два примера:

Вышеописанные проблемы привели меня к мысли о загрузке всех данных сначала в базу, моделировании отчета при помощи SQL и только потом их визуализации в Power BI.

Переходим к делу

Для примера возьмем задачу по автоматизации отчета по эффективности контекстной рекламы.

К данному отчету заказчиком предъявляются следующие требования:

Также отчет должен содержать следующие параметры и показатели:

Естественно, все данные должны быть предварительно загружены в хранилище, но это тема отдельного поста и обычно этим занимаются data-инженеры. Мы же с вами аналитики и используем те данные, которые для нас любезно сложили в DWH (хранилище данных).

В моем случае DWH работает на базе MS SQL Server и содержит следующие таблицы:

Для работы нам потребуется установить:

Опущу совсем уж базовые вещи, такие как регистрация аккаунтов и установка программ, с этим вы без проблем справитесь и сами.

Готовим данные

Итак, задача понятна, инструменты готовы — за дело!

Создаем таблицу

Для того чтобы создать отчет, нам необходимо свести данные по расходам, сеансам и заказам в одной таблице. Для этого напишем SQL-запрос, в котором объединим таблицы по следующим ключам:

Кстати, никакой сквозной аналитики у вас никогда не получится, если вы не умеете грамотно размечать рекламу utm-метками. О том как правильно ставить метки, читайте в одном из уроков бесплатного онлайн-курса «Digital-аналитика для новичков».

Но вернемся к задаче и после некоторых манипуляций с SQL получим вот такой скрипт:

Запустим его и порадуемся получившемуся результату:

Создаем таблицу

Скрипт работает и выдает отчет, в принципе его уже можно использовать для автоматизации вставив в Power BI при помощи встроенного коннектора. Но не советую так делать, потому что если данных в отчете будет много, например заказчик захочет посмотреть как работали рекламные кампании в течение года, на выполнение скрипта может уйти несколько часов.

Гораздо более правильным решением будет создать промежуточную таблицу в базе данных и докладывать туда ежедневно данные за прошедшие сутки. Что мы и сделаем:

Таблица будет иметь следующую структуру (подробнее о типах данных):

При сохранении таблицы укажем название:

И теперь, чтобы получить все данные из нее, достаточно выполнить простой SELECT :

Создаем хранимую процедуру

Отлично! Настало время автоматизации 😉

А поможет нам в этом функционал хранимых процедур (подробнее рассказывал о них тут).

Засучим рукава и обернем наш скрипт в код процедуры:

Теперь протестируем и вручную вызовем процедуру:

Скорость отработки процедуры 3 секунды на одном дне — вполне приемлемо. Проверим появились ли данные в ранее созданной таблице:

Осталось настроить ежедневное обновление.

Настраиваем расписание

Настроим вызов нашей процедуры каждое утро по расписанию, благо в Management Studio для этого предусмотрена специальная служба под названием «Агент SQL Server».

Зайдем в агент и добавим новое задание:

Укажем название и придумаем описание:

Далее создадим новый шаг, в котором будем вызывать процедуру с данными за прошедшие сутки (обратите внимание, объявление переменных с датами из нашего скрипта мы перенесли в расписание и немного изменили):

Настраиваем время запуска, периодичность и сохраняем:

Теперь данные автоматически будут поступать в отчет ежедневно в 9 утра.

Визуализируем данные

Данные готовы, обновление настроено, самое время приступить к визуализации.

Останавливаться на том как установить Power BI и как им пользоваться не буду, так как этой теме посвящен целый урок нашего курса.

Создаем отчет

Заходим в desktop-версию Power BI и открываем коннектор к SQL Server:

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

И это все! Никаких сложных моделей в Power BI строить не нужно, так как мы уже это сделали на стороне SQL-запроса.

Наиболее правильным считаю подход, когда инструмент визуализации используется именно для этой самой визуализации и еще для создания рассчитываемых показателей (например, CPC, CPO, ROMI). Используйте эти рекомендации и ваши отчеты будут летать.

После того как будет готов дизайн отчета, его нужно загрузить в облако Microsoft:

Настраиваем расписание

Отчет опубликован! Остался финальный шаг, для этого переходим в веб-версию Power BI и настраиваем расписание обновления.

Но перед этим не забываем поставить на компьютер, с которого будет происходить обновление, локальный шлюз Power BI (а лучше всего завести под это дело отдельную виртуальную машину):

Важно так подгадать расписание, чтобы оно запускалось в тот момент, когда на стороне SQL Server уже отработает наша процедура и положит в табличку свежие данные. Плюс нужно заложить небольшой запас времени, на возможные проблемы с сервером при его перегрузке:

Готово. Теперь можем пользоваться отчетом внутри веб-интерфейса, опубликовать отчет в интернете, либо отправить коллегам ссылку на него.

А как же Excel?

Иногда заказчики могут попросить загрузить данные в Excel для более детального анализа.

Для этого зайдем в Excel на вкладку «Данные» и создадим новое подключение к серверу баз данных:

После чего останется только указать SQL-запрос и сохранить:

С этого момента данные из нашей таблицы на сервере станут доступны в Excel.

В итоге мы получили автообновляемую отчетность, без привлечения каких-то гигантских ресурсов разработки и без особых денежных затрат.

Источник

Общие сведения о запросах в Power BI Desktop

С помощью Power BI Desktop вы можете подключаться к миру данных, создавать привлекательные и фундаментальные отчеты и совместно использовать свою работу с другими пользователями, которые затем могут использовать ее для расширения своих возможностей по бизнес-аналитике.

Power BI Desktop имеет три представления:

Доступ к этим представлениям осуществляется путем выбора одного из трех значков, расположенных вдоль левой стороны Power BI Desktop. На следующем рисунке выбрано представление Отчеты, на что указывает желтая полоска рядом со значком.

Power BI Desktop также поставляется с редактором Power Query. Используйте редактор Power Query, чтобы подключаться к одному или нескольким источникам данных, преобразовывать данные в соответствии с потребностями, а также загружать модель в Power BI Desktop.

Этот документ содержит общие сведения о работе с данными в редакторе Power Query, но это еще не все. В конце этого документа вы найдете ссылки на подробные указания по поддерживаемым типам данных. Вы также найдете руководство по подключению к данным, формированию данных, созданию связей и началу работы.

Но сначала давайте ознакомимся с Редактором Power Query.

Редактор Power Query

Чтобы открыть Редактор Power Query, в Power BI Desktop на вкладке Главная выберите Преобразование данных.

Без подключения к данным в Power Query отображается пустая область.

После загрузки запроса представление редактора Power Query становится гораздо более интересным. Если мы подключимся к указанному ниже веб-источнику данных, редактор Power Query загрузит сведения о данных и мы сможем начинать их формировать.

Вот так выглядит Редактор Power Query после подключения к данным.

На ленте многие кнопки теперь можно использовать для взаимодействия с данными в запросе.

В левой области перечислены запросы, которые можно выбирать, просматривать и формировать.

В центральной области отображаются данные из выбранного запроса, и их можно формировать.

Появляется область Параметры запроса, в которой перечислены свойства запроса и примененные действия.

Мы рассмотрим каждую из этих четырех областей — ленту, область запросов, представление данных и область параметров запроса.

Лента запроса

Лента в редакторе Power Query состоит из четырех вкладок: Главная, Преобразовать, Добавить столбец и Представление.

На вкладке Главная содержатся общие задачи запросов.

Чтобы подключиться к данным и начать процесс построения запросов, выберите Создать источник. Появится меню с наиболее распространенными источниками данных.

Дополнительные сведения о доступных источниках данных см. в разделе Источники данных. Сведения о подключении к данным, включая примеры и инструкции, см. в разделе Подключение к данным.

Вкладка Преобразовать предоставляет доступ к общим задачам преобразования данных, например:

Дополнительные сведения о преобразовании данных, включая примеры, см. в разделе Учебник. Формирование и объединение данных в Power BI Desktop.

На вкладке Добавить столбец предоставляются дополнительные задачи, связанные с добавлением столбца, форматированием данных столбца и добавлением настраиваемых столбцов. На следующем рисунке показана вкладка Добавить столбец ленты.

Вкладка Вид на ленте используется для переключения между разными областями или окнами. Она также используется для отображения Расширенного редактора. На следующем рисунке показана вкладка Вид ленты.

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

Левая область (запросы)

В левой области (Запросы) отображается число активных запросов, а также имя запроса. При выборе запроса в левой области его данные отображаются в центральной области, где их можно формировать и преобразовывать в соответствии со своими потребностями. На следующем рисунке показана левая область с запросом.

Центральная область (область данных)

В центральной области, или области Данные, отображаются данные из выбранного запроса. Здесь выполняется большая часть работы с представлением Запросы.

На следующем рисунке показано подключение к веб-данным, установленное ранее. Выбран столбец Продукт, его заголовок показывает доступные пункты меню по правому щелчку мыши. Обратите внимание, что многие из этих пунктов контекстного меню совпадают с кнопками на вкладках ленты.

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

Правая область (параметры запроса)

В области Параметры запроса справа отображаются все действия, связанные с запросом. Например, на следующем рисунке в разделе Примененные действия области Параметры запроса отражен тот факт, что мы только что изменили тип столбца Overall score (Общий рейтинг).

По мере применения к запросу дополнительных действий по формированию они регистрируются в разделе Примененные действия.

Важно помнить, что базовые данные не меняются. Вместо этого редактор Power Query настраивает и формирует представление данных. Он также формирует и корректирует представление любого взаимодействия с базовыми данными, которое происходит на основе сформированного и измененного представления данных в Редакторе Power Query.

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

Расширенный редактор

Сохранение работы

Если запрос находится в нужном месте, выберите Закрыть и применить в меню Файл в редакторе Power Query. Это действие применяет изменения и закрывает редактор.

По мере хода выполнения Power BI Desktop выводит диалоговое окно для отображения состояния.

Когда будете готовы, Power BI Desktop можете сохранить вашу работу в виде файла PBIX.

Чтобы сохранить результаты работы, выберите Файл > Сохранить (или Файл > Сохранить как), как показано на следующем рисунке.

Дальнейшие действия

Power BI Desktop предоставляет широкие возможности. Дополнительные сведения об этих возможностях см. в следующих ресурсах.

Источник

Как использовать Microsoft SQL для отчётов в Power BI. На примере Mindbox

Если вы хотите понимать, насколько эффективны вложения в рекламу, нужно всё измерять: письма, визиты, заказы, выручку. Важна и скорость получения этих метрик. Для наглядности можно строить красивые и понятные отчёты в Microsoft Power BI. А упростить получение данных для отчётов поможет их хранение в собственной базе. Это удобно, особенно когда информации много, и получать её напрямую из онлайн-сервисов затруднительно. Мы храним данные в Microsoft SQL, но можно использовать и другой сервер БД.

Проблемы построения отчётов по данным из ESP

Современные почтовые платформы (Email Sending Platform или ESP) собирают статистику по рассылкам: отправки, доставки, открытия, клики. А более продвинутые платформы, например, Mindbox, позволяют добавить сюда заказы и выручку.

Кроме того, данные можно и нужно собирать при помощи Google Analytics и/или Яндекс.Метрики.

Данные где-то хранятся, доступны через API почтовых платформ, но строить отчёты по этим сырым данным сложно, поскольку:

Для построения одного отчёта может потребоваться сотни или даже тысячи API-запросов. Например, чтобы построить отчёт по сегментам подписчиков, нужно сделать столько запросов, сколько выделено сегментов аудитории. Если нужно анализировать рост сегментов по дням, то нужно выполнить эти запросы столько раз, за сколько дней мы хотим построить отчёт. Даже если один запрос выполняется секунду, в Power BI могут возникнуть проблемы при первоначальной загрузке данных и обновлении.

Поэтому мы стали предварительно собирать данные в систему управления базами данных — Microsoft SQL Server. Можно собирать данные и в другие базы, в зависимости от наших задач. При отсутствии своего SQL-сервера часто используют облачные решения, такие как MS Azure или BigQuery.

Кейс от аналитиков: клиенту потребовалась статистика по ежедневной динамике сегментов подписчиков. Используемая ESP-система пересчитывает сегменты и отдаёт статистику только два раза в месяц. С помощью ежедневного агрегирования данных на нашем SQL-сервере мы смогли удовлетворить запрос клиента. Конечно, исторических данных нет, информация собирается с момента поступления заявки от клиента, но… уже собраны данные за 3 года!

Результат стандартного запроса к API ESP для получения размера сегмента выглядит так:

Как видим, довольно скромная информация: размер сегмента и дата пересчёта.

При агрегировании данных на SQL-сервере можем собрать информацию за необходимый период.

Выбранный для примера сегмент оказался одним из главных и пересчитывался довольно часто, поэтому с SQL-сервера обычным запросом можно получить почти ежедневную статистику.

Следите за эффективностью вашего маркетинга. Закажите

Преимущества и особенности работы с Microsoft SQL Server

В SQL-базе хранятся уже подготовленные данные, удобные для построения отчёта. Для наполнения этой базы мы пишем серверные скрипты на программной платформе Node.js. Она имеет весь необходимый инструментарий и позволяет эффективно распараллеливать API-запросы.

Скрипты при помощи API-запросов получают данные из ESP, преобразуют в удобный формат и сохраняют в таблицах БД. Мы настроили планировщик задач так, чтобы эти скрипты выполнялись периодически по ночам.

Таким образом, у нас в базе актуальные данные, проблем с загрузкой и обновлением данных в отчёты Power BI не возникает: данные хранятся столько, сколько мы захотим.

Например, мы импортируем данные о действиях по сегментам из Mindbox. В данном случае действие — это событие, которое происходило с письмом, отправленным по сегменту «отправлено→доставлено→открыто→кликнули по ссылке в письме→попало в спам».

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

«В июне 2020 года мы выпускаем новый API экспортов, который работает асинхронно и возвращает сразу большой объём данных. Например, можно будет одним запросом выгрузить все заказы за последние полгода.

В будущем мы планируем сделать стандартный провайдер для одной из BI-систем. Сырые данные в BI будут поступать автоматически без развертывания и поддержки отдельной базы данных».

Как работает сборщик данных Microsoft SQL

Алгоритм программы можно описать примерно так:

Повторный запуск программы будет дописывать данные. Так мы можем ежедневно пополнять базу данных и актуализировать её.

Когда данные собраны, мы по ним можем построить, например, вот такие отчёты.

В редакторе запросов Power BI можно организовать прямую загрузку данных из Mindbox с помощью такого запроса:

И он даже будет работать. Часа 2-3. И, возможно, данные вы соберёте, если у вас не так много подписчиков и рассылки выходят не очень часто.

В противном случае вы, скорее всего, получите сообщение, что сервер временно недоступен. И данные загружены не будут. При возникновении ошибки запрос прекратит свою работу, а все загруженные данные не сохранятся.

После апдейта в феврале 2020 в PowerQuery появилась возможность агрегировать (догружать) данные, но пока этот способ работает с множеством ограничений, да и не каждый API позволит реализовать заложенный алгоритм.

Если в Power BI загружать данные, заранее собранные на SQL-сервере, то запрос будет гораздо проще и выполняться будет гораздо быстрее — всего несколько секунд.

А выглядеть будет так (сравните с запросом к API):

А если вы заранее продумали и правильно сформировали структуру таблиц на сервере, дополнительное преобразование и обработка данных в редакторе запросов Power query не потребуются.

Как выглядит отчёт в Power BI

Вот такой отчёт можно собрать на данных из Mindbox и Google Analytics.

Следуя изложенной выше методике, каждую ночь в базу данных на SQL-сервере собираются данные из Mindbox и Google Analytics (специальный скрипт собирает данные без сэмплирования), затем собранные и агрегированные данные с SQL-сервера загружаются в отчёт на сервере Power BI.

Таким образом, пользователи уже к началу рабочего дня получают в отчёте актуальные данные.

В нашем Телеграм-канале Маркетинг за три минуты мы пересказываем самые интересные материалы про онлайн-маркетинг в формате постов-трёхминуток. А если вы хотите поболтать и поделиться своими мыслями, приходите к нам в Чат Солдат.

Узнавайте об обновлениях блога Email Soldiers первым

Спасибо!

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

Следите за обновлениями в соцсетях или получайте их от нашего телеграм-бота

Похожие статьи

Разработали для застройщиков версию наглядного отчёта с дашбордами, чтобы удобнее было контролировать продажи, рекламу, работу менеджеров.

Как оформить ваш отчёт в виде аквариума с разноцветными рыбками.

Рассказываем, какие показатели можно добавить в отчёт и как они будут взаимодействовать между собой.

Источник