Как использовать функциональность прямого запроса Power BI

Когда вы создаете отчеты Power BI, зрители ожидают, что отчет будет периодически обновляться, и данные будут постоянно обновляться. Как? Ручное обновление отчетов работает, но Power Bi Direct Query предлагает гораздо лучшую функцию – запланированное обновление.

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

Следите за обновлениями и повышайте производительность, экономя время в процессе!

Необходимые условия

Этот учебник будет практической демонстрацией. Чтобы следовать за ним, убедитесь, что у вас есть следующее:

• Power Bi desktop – В этом учебнике используется версия Power Bi desktop 2.109.1021.0.

• SQL-сервер.

Связанный:Начало работы с Microsoft SQL Server Standard Edition

• A code editor – This tutorial uses VS code version 1.71.

Связанный:Обучающий курс по Visual Studio Code, который стоит изучить

• Шлюз данных Microsoft On-premises.

Связанные:Шлюз данных Power BI: подключение различных источников данных

• SQL Server Management Studio (SSMS) – В этом руководстве используется SQL Server Management Studio 18.12.1.

Связанные:Начало работы с SQL Server Management Studio (SSMS)

Создание SQL-базы данных для управления с помощью Power BI Direct Query

Power Bi Direct Query позволяет подключаться напрямую к набору данных и предоставляет вашему проекту возможность загружать живые данные в ваш проект. Но перед созданием SQL-базы данных и подключением вы должны знать имя сервера и имя источника данных, чтобы получить правильные сведения.

Для создания SQL-базы данных выполните следующие действия:

1. Найдите ODBC в строке поиска, найдите и нажмите “Администратор источников данных ODBC (64-разрядные)” для его открытия.

2. Затем перейдите на вкладку “Система” в окне “Администратор источников данных ODBC” и нажмите “Добавить”, чтобы начать добавление нового источника данных.

3. Выберите драйвер SQL Server из списка ниже и нажмите “Готово”, чтобы создать источник данных SQL.

DirectQuery поддерживает не каждый источник данных. MySQL не поддерживается, в то время как SQL – да.

4. Теперь укажите источник данных (MssqlDataSource), выберите экземпляр SQL (POWERSERVER\POWERSERVER), установленный на вашем компьютере, и нажмите “Готово”.

Обратите внимание на имя сервера SQL и источник данных, так как вам они понадобятся для вашей строки подключения Python.

5. Проверьте детали вашего источника данных и нажмите “Проверить источник данных”, чтобы протестировать его.

Если соединение установлено успешно, вы получите сообщение “Тест завершен успешно”, как показано ниже.

Подключение к серверу SQL

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

1. Запустите вашу среду разработки Visual Studio и нажмите CTRL+SHFT+`, чтобы открыть новый терминал.

2. Затем выполните команду sqlcmd ниже, чтобы войти в экземпляр вашего SQL-сервера.

sqlcmd -S SQL_SERVER -E

3. После входа выполните следующие запросы для создания новой базы данных (CREATE DATABASE) под названием MSSQLDB.

CREATE DATABASE MSSQLDB;
GO

4. Создайте файл Python DBconnect.py и добавьте нижеуказанный код, который позволит вам подключиться к вашей SQL-базе данных с использованием ORM SQLAlchemy для Python.

Движок обозначает точку начала вашего приложения SQLAlchemy. Движок описывает пул подключений и диалект для Python Database API Specification (DBAPI). Python DBAPI – это спецификация в Python, определяющая общие шаблоны использования для всех пакетов подключения к базам данных. Эта спецификация взаимодействует с указанной базой данных.

//DBconnect.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
# Создание строки подключения, используя urllib для форматирования текста.
conn = urllib.parse.quote_plus(
# Значения для строки подключения берутся из источника данных
'Data Source Name=MssqlDataSource;'

'Driver={SQL Server};'

'Server=POWERSERVER\POWERSERVER;'

'Database=MSSQLDB;'

'Trusted_connection=yes;'

)

try:
# Создание соединения с движком sqlalchemy
	coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
	print("Passed")
except:
	print("failed!")

5. Создайте файл Python с именем CreateTable.py в вашей основной папке и добавьте нижеприведенный код, а затем запустите его. Ниже приведенный код создает таблицу с именем students в вашей SQL базе данных.

//CreateTable.py
from DBconnect import coxn
from sqlalchemy import Table, Column, Integer, String, MetaData

# Создает конструкцию Metadata, которая содержит определения таблиц 
  # и связанные объекты, такие как индексы, представления, триггеры и т. д.
meta = MetaData()

# Представляет оператор CREATE TABLE в обычном синтаксисе SQL для создания таблицы.
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# Использует объект движка для создания всех определенных объектов таблиц 
  # и сохраняет информацию в метаданных.
meta.create_all(coxn)

6. Затем откройте вашу SSMS и проверьте базу данных, созданную таблицу и столбцы.

7. Создайте файл Python с именем WriteToTable.py, добавьте нижеприведенный код, а затем запустите его.

Этот код содержит логику для записи значений в таблицу базы данных.

//WriteToTable.py
# Импортирование таблицы students из файла CreateTable.py.
from CreateTable import students
# Импортирование строки подключения coxn из файла DBconnect.py.
from DBconnect import coxn
# Вставка нескольких значений в таблицу students
ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])
conn = coxn.connect()
conn.execute(ins)

Подготовка шлюза данных на месте

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

1. Откройте шлюз данных на месте и войдите в систему.

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

3. Затем откройте Power BI, щелкните “Получить данные” на вкладке “Домой” и выберите SQL Server, чтобы начать подключение вашего источника данных к Power BI.

4. Теперь подключитесь к DirecQuery следующим образом:

• Вставьте имя вашего Сервера и имя Базы данных в соответствующие поля. Убедитесь, что выбрана опция DirectQuery. Нажмите OK, чтобы подключиться к DirectQuery.

5. Выберите таблицу (студенты) из базы данных и нажмите “Загрузить”, чтобы загрузить данные.

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

Связано:Удивительная визуализация данных с Power BI Python

В таблице ниже показаны данные, которые вы загрузили из таблицы базы данных

7. Щелкните меню “Файл”, чтобы получить доступ к действиям, выполняемым в Power BI.

8. Теперь щелкните “Опубликовать” → “Опубликовать в Power BI”, чтобы опубликовать отчет по вашим данным.

9. Нажмите Открыть <ваш отчет>, когда ваше публикование успешно завершится, перенаправляя ваш браузер на список последних действий, которые вы сделали в Power BI (шаг 10).

10. Нажмите на отчет, чтобы открыть его, как показано ниже.

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

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

12. Настройте новое подключение к шлюзу и нажмите Создать, чтобы создать новое подключение между вашим источником данных и шлюзом.

13. Наконец, когда вы увидите, что шлюз работает, выберите имя источника данных (MssqlDataSource) из выпадающего списка “Соответствует” и нажмите Применить.

Просмотр и управление отчетами по данным

После установления соединения между шлюзом и Power BI вы можете просматривать свой отчет и настроить расписание обновления.

1. Перейдите на домашнюю страницу отчетов Power BI.

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

2. Затем нажмите на отчет, как показано ниже, чтобы открыть его.

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

3. Вернитесь на домашнюю страницу отчета и нажмите Обновить → Запланировать обновление, чтобы установить запланированное обновление отчета.

Кроме создания живых отчетов, Power Bi Direct Query позволяет установить запланированное обновление, автоматически обновляя ваши отчеты.

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

5. Откройте ваш скрипт Python WriteToTable.py и добавьте нижеследующий код, чтобы вставить больше записей в вашу базу данных с последующим запуском скрипта.

Запуск этого кода не дает вывода, но вы позже проверите вставленные значения в таблице в следующих шагах.

Отсутствует редактор Power Query для многомерных источников, таких как SAP Business Warehouse, что ограничивает ваши возможности работы с данными.

//WriteToTable.py
# Импорт таблицы студентов из файла CreateTable.py.
from CreateTable import students
# Импорт строки подключения coxn из файла DBconnect.py.
from DBconnect import coxn

# Вставка нескольких значений в таблицу студентов
ins = students.insert().values([
{'name':'Damian','lastname':'Marley'},
{'name':'Rita','lastname':'Marley'},
{'name':'Ziggy','lastname':'Marley'},
{'name':'Sam','lastname':'Dylan'},
{'name':'Jakob','lastname':'Dylan'},
{'name':'Maria','lastname':'Dylan'}
])
# Создание курсора подключения.
conn = coxn.connect()
# Выполнение команды insert()
conn.execute(ins)

6. Затем перейдите в ваш браузер Power BI и нажмите на вкладку Datasets + dataflows.

Если все пройдет успешно, вы увидите последнее время обновления и следующее обновление набора данных, как на скриншоте ниже.

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

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

Заключение

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

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

Связанные:Удивительная визуализация данных с помощью Power BI Python

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