Применение Power Query в Office 365 Power BI

В предыдущей статье, «Power BI in Office 365: что необходимо знать» (опубликована в Windows IT Pro/RE № 8 за 2014 год) мы позиционировали Power Query в общей схеме Power BI с помощью схемы, показанной на рисунке.

Рисунок. Процессы Power BI

Любому пользователю, желающему создать отчет или модель данных, которая служит основанием для отчета, необходим набор данных. Этот набор данных возвращается «запросом», который пользователь может создать самостоятельно или позаимствовать из общего пула запросов, созданного другими пользователями.

В сущности, Power Query – это надстройка Microsoft Excel 2013, благодаря которой пользователи могут создавать запросы из данных, поступающих из разнообразных источников и типов данных. Кроме того, с помощью Power Query можно управлять этими запросами и обмениваться ими внутри компании.

Power Query можно рассматривать как инструмент самообслуживания ETL (извлечение, преобразование и загрузка данных) для очистки и преобразования данных из источников различных типов. Ранее в традиционной бизнес-аналитике Microsoft эту функцию отлично выполняла служба SQL Server Integration Service.

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

Установка Power Query

Загрузите надстройку с сайта загрузки Microsoft и после установки активируйте ее в меню File, Option (см. экран 1).

Экран 1. Надстройка в Excel

После активации элемент появится на ленте (см. экран 2).

Экран 2. Обновленная лента

Создание запросов

Запросы можно создавать из источников данных многих типов (см. экран 3). Это очень удобно.

Экран 3. Источники данных

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

Внешние данные:

• Сайты с таблицами HTML; таблицы HTML могут служить источником данных.
• Файлы Excel, csv, xml, text; это могут быть одиночные файлы или группы файлов в папке.
• Базы данных SQL Server, SQL Azure, Access, Oracle, DB2, MySQL, PostGreSQL, Sybase, Teradata; однако если запрос выполняется в «облаке» и нуждается в доступе к данным, сохраненным в локальном центре обработки данных (гибридные сценарии), сейчас поддерживаются только базы данных SQL Server и Oracle.
• Другие источники: списки SharePoint, канала oData, Windows Azure MarketPlace, Hadoop (для больших данных), Windows Azure Blog Storage, табличное хранилище Windows Azure, Active Directory, Exchange, SAP Business Object, Facebook.

Данные Excel: таблица в текущей рабочей книге Excel.

Рассмотрим весь процесс и покажем, как создавать запросы, совместно использовать и извлекать их.

Создание запросов

Очень простой пример: мы создали список пищевых продуктов в списке SharePoint и хотим направить запросы к этому списку.

Подключите Excel 2013 к данному списку SharePoint Online, указав URL-адрес сайта SharePoint (см. экран 4).

Экран 4. Подсоединение к списку SharePoint

Power Query позволяет получить и показать списки веб-сайта SharePoint (см. экран 5).

Экран 5. Списки веб-сайта SharePoint

Выберем список (в данном случае PicNicItems) и создадим запрос, вызвав редактор Power Query (см. экран 6).

Экран 6. Вызов редактора Power Query

Затем Excel загрузит выбранные данные (из списка SharePoint) в редактор Power Query (см. экран 7). Требуется преобразовать данные (или реорганизовать их), чтобы сосредоточиться только на нужном материале.

Экран 7. Редактор Power Query

Редактор Power Query — превосходный инструмент для создания запросов, но с его помощью также можно работать с данными различными способами; все действия в этом редакторе автоматически вносятся в скрипт на языке, именуемом «M", и записываются таким образом, чтобы их можно было повторить при запуске запроса пользователем или моделью данных.

Например, в нашем случае мы переименуем столбец Name1 в Name, Type1 в Type и поменяем местами ItemID и Type, как показано на экране 8.

Экран 8. Переименование столбцов

Удалим столбцы, в которых нет необходимости (см. экран 9).

Экран 9. Удаление столбцов

Как отмечалось выше, все действия в редакторе записываются (см. экран 10). Вы всегда можете удалить некоторые записанные шаги и вернуться к предыдущему шагу.

Экран 10. История действий

Расширенный редактор (см. экран 11) позволяет при необходимости изменить программный код, хотя рекомендуется предоставить программе Excel возможность генерировать код»M«.

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

Запрос может быть документирован и загружен в рабочий лист Excel (Load to worksheet) или сохранен в модели данных Excel 2013 (Load to Data Model) (см. экран 12).

Экран 12. Описание запроса

Запросы, связанные с рабочей книгой, можно увидеть на панели Workbook Queries («Запросы книги»), см. экран 13. Но как организовать общий доступ к запросу?

Экран 13. Сохраненные в рабочей книге запросы

Общий доступ к запросам

После того как запрос создан, появляется возможность предоставить доступ к нашей работе другим пользователям, сохранив ее в Office 365 (см. экран 14). Но для этого требуется программная лицензия Power BI, чтобы развернуть инфраструктуру бизнес-аналитики в Office 365.

Экран 14. Общий доступ к ?запросу

Можно предоставить дополнительные подробности, в частности о лицах, которым разрешено использовать запрос (Share with) (см. экран 15), и даже дать ссылку на документ, описывающий запрос.

Экран 15. Расширенные возможности общего доступа

Параметр Certify this query for others («Сертифицировать этот запрос для других») дает дополнительное свидетельство, что запрос протестирован. Этот параметр доступен только членам определенной группы бизнес-аналитики: data steward («управляющий данными»). Кроме того, он предоставляется при условии приобретения лицензии Power BI. Через центр администрирования Power BI (см. экран 16) можно добавлять пользователей в группу управляющих данными (см. экран 17).

Экран 16. Центр администрирования Power BI

Экран 17. Добавление пользователей

Поиск общих запросов

Поиск существующего запроса легко выполнить с помощью Power Query. Необходимо сначала войти в организацию с учетной записью Office 365 (см. экран 18).

Экран 18. Регистрация

Пользователь может получить доступ к кнопке Online Search («Поиск в Интернете») на ленте Power Query (см. экран 19).

Экран 19. Поиск общего запроса

Появится панель поиска в Интернете (см. экран 20). Здесь пользователь может ввести любое ключевое слово, соответствующее имени запроса или его описанию.

Экран 20. Поиск и использование общего запроса

Результаты поиска можно сузить до запросов, объединенных в каталоге данных (внутри организации, см.»2«на экране 20), или до запросов, общих для государственных органов США и общественных организаций (см.»PUBLIC«на экране 20). Пользователь может без труда выбрать запрос для просмотра данных, возвращенных запросом (см.»3«на экране 20).

Значок сертификации появляется рядом с именем запроса, чтобы пользователи могли без труда различать сертифицированные запросы (см.»4" на экране 20).

Запрос может быть выбран, загружен в Excel или добавлен в модель данных (путем обращения к ленте PowerPivot, как показано на экране 21).

Экран 21. Добавление запроса в модель данных

Управление общими запросами

Управление запросами означает обновление, перечисление и общий доступ к существующим общим запросам.

Кнопка Shared («Совместное использование») (см. экран 22) — обычный способ перечисления общих запросов, которые удобно ввести в каталог данных Office 365. Как отмечалось выше, любые общие действия требуют выполнения входа и потому — лицензионного соглашения на использование ПО Power BI.

Экран 22. Управление общими запросами

Участники запросов также могут управлять собственными запросами с помощью функции Power BI App. Это приложение может быть добавлено на любой сайт SharePoint (и работает при наличии хотя бы одной лицензии Power BI).

Для доступа к совместно используемым запросам достаточно щелкнуть правой кнопкой мыши и выбрать View Statistics («Просмотреть статистику»), как показано на экране 23, а затем Query Analytics («Аналитика запроса») (см. экран 24).

Экран 23. Статистика запроса

Экран 24. Аналитика запроса

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

Экран 25. Общие запросы в каталоге данных

Несколько запросов могут быть связаны с одним источником данных. В случае с запросом Contoso Food, описанным в начале статьи, источником данных является список SharePoint.

Подробно о запросах

В следующей статье мы перейдем к более подробному описанию Power Query и остановимся на языке M.