Как сделать таблицу в power bi
Добавил пользователь Алексей Ф. Обновлено: 04.10.2024
Чтобы создавать понятные отчеты и обновлять их в режиме реального времени современному бизнесу необходим мощный сервис для обработки информации. В этой статье рассказывается как начать работу с аналитической платформой Microsoft Power BI.
Кратко о возможностях Microsoft Power BI
Power BI — достаточно мощная и при этом бесплатная BI платформа. Microsoft вкладывает много средств в развитие этого продукта, в связи с чем ежемесячно выходят обновления, расширяющие его возможности. Вы можете использовать такие версии программы:
- Power BI Desktop — предназначен для разработки модели данных и отчетов;
- Power BI Service — онлайн-аналог, который специализируется на мониторинге и анализе готовых отчетов, поэтому возможности конструирования и работы с данными там очень ограничены.
Откуда можно загружать данные?
Power BI имеет множество встроенных коннекторов к различным сервисам и базам данных, с помощь которых вы в считанные минуты можете загрузить в программу нужный набор данных из различных источников, связать их между собой и построить консолидированные отчеты и диаграммы. На момент написания статьи все доступные коннекторы распределены на четыре группы:
- SQL Server;
- Access;
- SQL Server Analysis Service;
- Oracle;
- IBM DB2;
- MySQL;
- PostgreSQL;
- Sybase;
- Teradata;
- SAP HANA.
- База данных Microsoft Azure SQL;
- Microsoft Azure Marketplace;
- Microsoft Azure HDInsight;
- Хранилище BLOB-объектов;
- Табличное хранилище Microsoft Azure;
- Azure HDInsight Spark;
- Microsoft Azure DocumentDB;
- Хранилище озера данных Microsoft Azure.
- Интернет;
- Список SharePoint;
- Канал OData;
- Файл Hadoop;
- Active Directory;
- Microsoft ExChage;
- Dynamics CRM online;
- Facebook;
- Google Analytics;
- Объекты Salesfore;
- Отчеты Salesforce;
- ODBC;
- R-скрипт;
- appFigures;
- GitHub;
- MailChimp;
- Marketo;
- QuickBook Online;
- Smartsheets;
- SQL Sentry;
- Stripe;
- SweetIQ;
- Twilio;
- Zendesk;
- Spark;
- Пустой запрос.
Как видите, в Power BI можно импортировать данные из наиболее известных баз данных и сервисов, используя различные форматы файлов. После загрузки информации из доступных источников в Power BI, перед вами открываются большие возможности по очистке и преобразованию данных, в связи с тем, что в платформе присутствует достаточно мощный ETL функционал. Загружаемые таблицы можно редактировать, а на основе их столбцов создавать расчетные столбцы и меры — таким образом вы можете преобразовать массивы информации в наборы данных со структурой, необходимой для построения визуализации.
Какие визуализации можно построить в Power BI?
Сформировав набор данных, загруженных из различных источников, вам станут доступны множество встроенных элементов визуализации:
- линейчатая диаграмма с накопление;
- гистограмма с накоплением;
- линейчатая диаграмма с группировкой;
- гистограмма с группировкой;
- нормированная линейчатая диаграмма;
- нормированная гистограмма;
- график;
- диаграмма с областями;
- диаграмма с областями с накоплением;
- линейная гистограмма и гистограмма с накоплением;
- линейная гистограмма и гистограмма с группировкой;
- каскадная диаграмма;
- точечная диаграмма;
- круговая диаграмма;
- диаграмма дерева;
- карта;
- таблица;
- матрица;
- заполненная карта;
- воронка;
- датчик;
- многострочная карточка;
- карточка;
- ключевой показатель эффективности;
- срез;
- кольцевой график;
- визуальный элемент r-script (на данный момент включается в параметрах программы).
Все элементы имеют достаточно широкий спектр настроек, направленных на изменение нужных параметров: цвет, фон, название, границы и так далее. Если вам будет недостаточно стандартного набора, можно загрузить пользовательские визуальные элементы.
Как построить отчёт в Power BI Desktop?
1. Установите связь Power BI с Google Analytics
1.2. Далее Power BI запросит вход в аккаунт Google.
1.3. Последний шаг — предоставить разрешение на просмотр данных в нужном представлении Google Analytics.
2. Как загрузить данные из MySQL в Power BI?
Чтобы показать функциональность Power BI, мы сгенерировали и загрузили в MySQL данные о продажах. Следуя описанным ниже инструкциям, можно подключать любые доступные базы данных и после соединять всю загруженную информацию в одну таблицу или график.
Теперь в модель данных Power BI загружены данные из двух источников: Google Analytics и MySQL.
3. Как установить связи между таблицами в Power BI
Для того, чтобы строить визуализацию на основе данных из двух различных источников необходимо настроить между ними связи.
Теперь наша модель данных состоит из пяти таблиц. Чтобы в этом убедиться, можно посмотреть в область полей либо перейти в режим визуального интерфейса просмотра модели данных (для этого кликните по иконке с изображением связей между таблицами на левой панели Power BI).
4. Как построить визуализацию в Microsoft Power BI?
В результате этих манипуляций в области отчетов будет построена диаграмма следующего вида.Как видите, диаграмма сочетает в себе информацию о сеансах и продажах из двух разных источников.
4.3. Дополнительно можно воспользоваться опциями форматирования объектов визуализации, кликнув по иконке с кисточкой. В результате чего вы можете изменять цвета, размер, шрифт, фон и прочие параметры элемента визуализации.
5. Как настроить фильтры данных в Power BI?
В результате у нас получится лист с возможностью фильтрации данных по типу пользователя, источнику / каналу и месяцу года.
Поэкспериментируйте с элементами визуализации и выберите наиболее подходящие, после чего все диаграммы и таблицы обновляйте нажатием одной кнопки.
6. Как опубликовать информацию на Power BI Service?
Заключение
В статье было кратко рассказано:
- Как загрузить в программу нужный набор данных (на примере интеграции с Google Analytics и загрузки данных из MySQL).
- Как упорядочить данные, чтобы привести их к нужному для визуализации виду.
- Как построить отчет в Power BI, где будут отображаться данные из разных источников.
- Как настроить фильтры данных, выбрать подходящие элементы визуализации и опубликовать результат для просмотра в онлайн-версии Power BI.
Microsoft Power BI — мощный и многофункциональный инструмент, поэтому достаточно тяжело описать весь его функционал в рамках одной статьи. Ответы на базовые вопросы по работе с Power BI можно узнать из официального мануала, который вы можете найти по этой ссылке.
О НАС
Bintels — консалтинговая компания, специализирующаяся на разработке и внедрении бизнес-аналитики на базе Microsoft Power BI. Проводим корпоративные тренинги по Power BI
На мой взгляд, он идеально подходит крупным организациям. Сервис превращает месиво информации из разных источников в наглядные диаграммы и графики. Впрочем, применение ему найдется и в среднем бизнесе. Возможно, для этого придется выделить отдельного специалиста. Но выгода от сквозной аналитики и устранения неочевидных проблем должна окупить его работу.
Разбираться с Power BI владельцам и сотрудникам микропредприятий не рекомендую. Продукт полезен в работе с большими объемами данных, а для меньших форматов есть инструменты попроще – например, тот же Google Data Studio.
Если информации мало или она однотипная, использование системы не окупится – больше времени и сил уйдет на настройку. Зато для предприятий с несколькими каналами продаж и рекламы, сложной CRM и распределенными базами финансовых данных Power BI станет настоящей находкой.
Продукт оптимально подходит для трех основных сценариев использования:
- Руководителям: для динамического контроля показателей и тенденций. Достаточно настроить панель мониторинга, а дальше все будет работать само по себе.
- Менеджерам: для детального обзора и ускоренного анализа бизнеса. Им пригодятся и сами отчеты, и автоанализ данных.
- Аналитикам: для поиска новых возможностей и обнаружения узких мест. Они смогут выстраивать настолько изощренные модели данных, насколько позволяет мышление.
Для начала нужно создать аккаунт Microsoft на странице Power BI. Личная учетная запись MS Live, которая есть у многих, здесь не годится, нужна именно рабочая или учебная. При регистрации обязательно вводите корпоративный email – почтовые адреса от бесплатных сервисов не подойдут.
Имейте ввиду, что Microsoft может отказать в регистрации российского аккаунта по неизвестной причине. Проблема решается просто: используйте те же данные, но смените страну на Великобританию или США.
После регистрации вы окажетесь на главной странице. На нее выводятся последние открытые отчеты и панели. Здесь же отображается избранное.
Лучше сразу скачайте и установите Power BI Desktop – он пригодится позже для подключения источников данных.
Структура сервиса
В экосистему продукта входит три элемента: веб-сервис, приложение Power BI Desktop для Windows и мобильные приложения для iOS, Android и Windows Mobile. Каждый из них отличается по функциональным возможностям.
В своей прошлой статье я рассказывал про программные возможности языка SQL и обещал поделиться кейсом по созданию автоматизированного отчета на основе стека технологий MS SQL Server и Power BI.
Почему именно эти технологии?
За время работы аналитиком, я перепробовал различные варианты сбора отчетности. Начиная с ручной выгрузки данных из кабинетов рекламных систем, с последующим сведением в Excel, и заканчивая созданием специальных отчетов в Google Analytics или дашбордов в Data Studio.
Но ни один из вариантов не был идеальным и каждый имел свои недостатки. Все изменилось, когда я открыл для себя Power BI.
Microsoft Power BI — это один из самых технологичных на данный момент инструментов по визуализации данных, обладающий большим набором коннекторов к различным системам.
Но и Power BI сам по себе не идеален и без грамотного использования будет работать медленно и неэффективно. Приведу два примера:
- Если вы попытаетесь собрать модель данных из различных источников, с большим количеством связей и рассчитываемых показателей на стороне Power BI, то отчет будет жутко тормозить, а ведь именно таким принципам работы учит большое количество курсов по данному инструменту.
- Еще пример, если вы пытаетесь загрузить в модель данные из Google Analytics при помощи встроенного коннектора, то столкнетесь как минимум с двумя проблемами — ограничениями API GA и долгой выгрузкой данных.
Вышеописанные проблемы привели меня к мысли о загрузке всех данных сначала в базу, моделировании отчета при помощи SQL и только потом их визуализации в Power BI.
Переходим к делу
Для примера возьмем задачу по автоматизации отчета по эффективности контекстной рекламы.
К данному отчету заказчиком предъявляются следующие требования:
- Отчет должен содержать исторические данные по вчерашний день;
- Отчет должен обновляться ежедневно в автоматизированном режиме;
- Помимо Power BI, должна быть возможность подключения к отчету через Excel.
Также отчет должен содержать следующие параметры и показатели:
Естественно, все данные должны быть предварительно загружены в хранилище, но это тема отдельного поста и обычно этим занимаются data-инженеры. Мы же с вами аналитики и используем те данные, которые для нас любезно сложили в DWH (хранилище данных).
В моем случае DWH работает на базе MS SQL Server и содержит следующие таблицы:
- sessions — данные из Google Analytics загруженные посредством коннектора к Reporting API v4;
- costs — данные по расходам, предварительно загруженные в Google Analytics;
- orders — данные по заказам и доходу из внутренней CRM-системы.
Для работы нам потребуется установить:
Опущу совсем уж базовые вещи, такие как регистрация аккаунтов и установка программ, с этим вы без проблем справитесь и сами.
Готовим данные
Итак, задача понятна, инструменты готовы — за дело!
Создаем таблицу
Для того чтобы создать отчет, нам необходимо свести данные по расходам, сеансам и заказам в одной таблице. Для этого напишем SQL-запрос, в котором объединим таблицы по следующим ключам:
- date ;
- sourceMedium ;
- campaign .
Но вернемся к задаче и после некоторых манипуляций с SQL получим вот такой скрипт:
Запустим его и порадуемся получившемуся результату:
Создаем таблицу
Скрипт работает и выдает отчет, в принципе его уже можно использовать для автоматизации вставив в Power BI при помощи встроенного коннектора. Но не советую так делать, потому что если данных в отчете будет много, например заказчик захочет посмотреть как работали рекламные кампании в течение года, на выполнение скрипта может уйти несколько часов.
Гораздо более правильным решением будет создать промежуточную таблицу в базе данных и докладывать туда ежедневно данные за прошедшие сутки. Что мы и сделаем:
Таблица будет иметь следующую структуру (подробнее о типах данных):
При сохранении таблицы укажем название:
И теперь, чтобы получить все данные из нее, достаточно выполнить простой SELECT :
Создаем хранимую процедуру
Отлично! Настало время автоматизации 😉
А поможет нам в этом функционал хранимых процедур (подробнее рассказывал о них тут).
Засучим рукава и обернем наш скрипт в код процедуры:
Теперь протестируем и вручную вызовем процедуру:
Скорость отработки процедуры 3 секунды на одном дне — вполне приемлемо. Проверим появились ли данные в ранее созданной таблице:
Осталось настроить ежедневное обновление.
Настраиваем расписание
Зайдем в агент и добавим новое задание:
Укажем название и придумаем описание:
Далее создадим новый шаг, в котором будем вызывать процедуру с данными за прошедшие сутки (обратите внимание, объявление переменных с датами из нашего скрипта мы перенесли в расписание и немного изменили):
Настраиваем время запуска, периодичность и сохраняем:
Теперь данные автоматически будут поступать в отчет ежедневно в 9 утра.
Визуализируем данные
Данные готовы, обновление настроено, самое время приступить к визуализации.
Останавливаться на том как установить Power BI и как им пользоваться не буду, так как этой теме посвящен целый урок нашего курса.
Создаем отчет
Заходим в desktop-версию Power BI и открываем коннектор к SQL Server:
Вводим данные для подключения к серверу, название базы данных и наш короткий SQL-запрос к ранее созданной табличке:
И это все! Никаких сложных моделей в Power BI строить не нужно, так как мы уже это сделали на стороне SQL-запроса.
Наиболее правильным считаю подход, когда инструмент визуализации используется именно для этой самой визуализации и еще для создания рассчитываемых показателей (например, CPC, CPO, ROMI). Используйте эти рекомендации и ваши отчеты будут летать.
После того как будет готов дизайн отчета, его нужно загрузить в облако Microsoft:
Настраиваем расписание
Отчет опубликован! Остался финальный шаг, для этого переходим в веб-версию Power BI и настраиваем расписание обновления.
Но перед этим не забываем поставить на компьютер, с которого будет происходить обновление, локальный шлюз Power BI (а лучше всего завести под это дело отдельную виртуальную машину):
Важно так подгадать расписание, чтобы оно запускалось в тот момент, когда на стороне SQL Server уже отработает наша процедура и положит в табличку свежие данные. Плюс нужно заложить небольшой запас времени, на возможные проблемы с сервером при его перегрузке:
А как же Excel?
Иногда заказчики могут попросить загрузить данные в Excel для более детального анализа.
После чего останется только указать SQL-запрос и сохранить:
С этого момента данные из нашей таблицы на сервере станут доступны в Excel.
В итоге мы получили автообновляемую отчетность, без привлечения каких-то гигантских ресурсов разработки и без особых денежных затрат.
Научитесь использовать функционал программных продуктов Power BI и Power Query для ускорения обработки данных и их визуализации.
С появлением мощных компьютеров, технологий программирования и, что не менее важно, с увеличением потребностей специалистов в оптимизации труда, компьютерная сфера беспрестанно развивается, создавая самые актуальные программы и приложения. Есть программы, которыми пользуются профессионалы, работающие в любой сфере во всех странах мира. Если вы еще не догадались, то речь сегодня пойдет об универсальном средстве обработки данных – Microsoft Excel.
Визуализация – это одна из возможностей, которая делает Excel одним из самых популярных приложений в мире. Качество графиков и диаграмм очень велико. В графическом функционале Excel можно найти все, что необходимо для красочной и яркой визуализации. Но создать в программе отчет или дэшборд не очень удобно, а порой и нереально!
Корпорация Microsoft позаботилась об этом и разработала приложение Power BI, полностью интегрирующееся с Excel, в котором можно было бы создавать продвинутую графику, делать объёмный анализ и обмениваться данными.
Многие пользуются Excel, но не все знают, как удобно работать с данными из Excel в Power BI. Об этом мы сегодня и расскажем.
Предположим, что вас попросили за короткий промежуток времени предоставить хороший отчет о продажах за последний период и высказать свое мнение о проведенных оптимизационных действиях компании. Это несложное задание, да. Но на него нужно время, особенно, если учесть, что информации проанализировать надо много, а находится она, скорее всего, в разных источниках. В таких ситуациях навык работы с Power BI окажется как нельзя кстати. Итак, начнем с небольшого вступления.
Power BI – это бесплатное (условно) приложение для обработки, анализа и визуализации данных. Главными преимуществами программы является возможность получать данные из любых источников и делиться ими с вашими коллегами.
Даже бегло пробежав по списку источников, доступных Power BI, понятно, что в нем есть все самое необходимое. Но нас интересует соединение Power BI именно с Excel.
Excel и Power BI хорошо работают вместе. Это позволяет нам совместно использовать два инструмента для эффективного анализа бизнес-процессов и экономить время на достаточно простой, но объемной работе. Power BI поддерживает любые книги Excel: книги с диапазонами и таблицами, с моделями данных, книги со сводными таблицами и диаграммами, а также книги, содержащие подключение к внешним источникам. В приложении можно работать с различными типами данных: числовыми (целые и десятичные числа), текстовыми, логическим (TRUE/FALSE), с датой и валютой. Если в Excel указать конкретные типы данных, то это увеличит эффективность Power BI.
Прежде, чем приступать к отправлению файлов из Excel в Power BI, необходимо данные подготовить и упорядочить.
Подготовка листа Excel
Предположим, что у нас есть книга Excel, содержащая информацию о продажах за какой-то период:
Лист с информацией по продажам
Нужно проверить, чтобы все столбцы содержали однородную информацию (одного типа) и чтобы в таблице была строка с заголовками столбцов.
С заголовками все в порядке, и данные в столбцах одного типа!
Теперь отформатируем данные в виде таблицы.
Для удобства добавим возможность фильтровать данные по столбцам.
Мы выполнили всю необходимую подготовку. Всего минута, и лист Excel готов к отправке в Power BI. Переходим к следующему шагу.
Подключение к книге Excel из Power BI
Нас интересуют данные из Excel:
После того, как мы выбрали необходимый файл, данные загрузились в программу, и теперь мы можем с ними работать и создавать отчеты.
В Power BI мы можем изменять наши данные: удалять или добавлять столбцы/строки; указывать типы данных для столбцов, если они не были указаны в Excel.
В появившемся окне можно изменять загруженную таблицу:
Но мы пока оставим этот момент и перейдем к самому интересному – к созданию отчета.
Создание визуализации
Перед нами появился пустой белый лист, на котором мы будем располагать графики.
Справа находится панель для управления визуализацией и поля с данными:
Огромное преимущество визуализации в Power BI – возможность следить за разными показателями на одной странице. Составим небольшой дэшборд на основе данных, которые мы загрузили из Excel! Наши данные содержат информацию о покупателях, покупках, ценах, прибыли, скидках, дате покупок и другие показатели. Построим график, который отображал бы информацию о продажах по странам.
Сначала определимся с типом диаграммы.
Можно обойтись обыкновенной диаграммой, а можно разнообразить отчет пузырьковой диаграммой, где вместо координатной сетки будет карта мира.
Посмотрим, как это сделать:
Получаем следующую диаграмму:
Теперь построим диаграмму продажи и прибыли по сегментам:
Для красоты мы изменили название графика, добавили заливку фона названия в цвет столбцов, выровняли по правому краю легенды и добавили подпись данных на диаграмме:
Теперь построим графики продаж и прибыли по месяцам с помощью диаграммы с областями:
Наш отчет просто не может обойтись без графика продаж, сгруппированных по продуктам.
Воспользуемся круговой диаграммой:
Отформатируем и приведем к понятному и читабельному виду:
Давайте посмотрим, что у нас получилось:
Мы смогли за довольно короткое время создать очень яркий, информативный, а главное – легкий для восприятия отчет.
А еще наш дашборд интерактивный. Это значит, что любые изменения в одной диаграмме влияют на другие. Мы можем смотреть одновременно на все показатели, а можем выделять отдельные интересующие нас данные.
Например, если мы хотим посмотреть данные только по США, то выделив на карте кружок, относящийся к Америке, все остальные диаграммы автоматически изменятся:
Теперь мы можем видеть, как менялась прибыль по месяцам, чему равна прибыль по продуктам, как распределены продажи и прибыль по сегментам и другое только для США.
Выбранные данные делаются более яркими, чтобы проще их было визуально отделить от общей информации. Это очень удобно, а главное – эффективно: не нужно создавать кучу графиков по разным показателям.
Сохранение дашборда
Если вы авторизованный пользователь, а компьютеры в организации связаны общим BI аккаунтом, то после публикации данные будут доступны всем пользователям:
Эпилог
Мы продемонстрировали всего лишь часть возможностей Power BI. Это очень удобное приложение особенно полюбится тем, кто только прошел наши курсы по изучению MS Excel.
Power BI расширяет функционал Экселя и дает возможность анализировать данные в режиме онлайн, а также встроенные методы анализа чрезвычайно выручают, когда поджимают сроки.
Безусловно, у Power BI есть конкуренты, но он уверенно держится на рынке, постоянно обновляя и пополняя свой функционал и расширяя предоставляемые возможности. А интуитивно понятный интерфейс и сходство с Excel только добавляют ему популярность.
Поэтому, Power BI – прекрасное программное средство в арсенале любого профессионала: финансиста, аналитика, менеджера или статиста.
POWER BI И POWER QUERY
Научитесь использовать функционал программных продуктов Power BI и Power Query для ускорения обработки данных и их визуализации.
Автор: Андрон Алексанян, практикующий Data Scientist и COO сети медицинских офисов.
Читайте также: