Cómo utilizar la funcionalidad de consulta directa de Power BI – techsyncer

Cuando creas informes en Power BI, los espectadores esperan que el informe se actualice periódicamente y que los datos se mantengan constantemente actualizados. ¿Cómo? La actualización manual de los informes funciona, pero Power Bi Direct Query ofrece una característica mucho mejor, la actualización programada.

En este tutorial, aprenderás cómo aprovechar la funcionalidad de Power Bi Direct Query conectando un conjunto de datos almacenado en un servidor y ejecutando consultas contra tus datos.

Mantente atento y aumenta la productividad ahorrando tiempo en el proceso!

Prerrequisitos

Este tutorial será una demostración práctica. Para seguirlo, asegúrate de tener lo siguiente:

Power Bi desktop – Este tutorial utiliza la versión 2.109.1021.0 de Power Bi desktop.

Un servidor SQL.

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

Microsoft On-premises Data Gateway.

SQL Server Management Studio (SSMS) – Este tutorial utiliza SQL Server Management Studio 18.12.1.

Creación de una base de datos SQL para administrar con Power BI Direct Query

Power Bi Direct Query te permite conectarte directamente a un conjunto de datos y proporciona a tu proyecto la opción de cargar datos en vivo. Pero antes de crear una base de datos SQL y una conexión, debes conocer el nombre del servidor y el nombre de la fuente de datos para obtener los detalles correctos.

Para crear una base de datos SQL, sigue estos pasos:

1. Busca ODBC en tu barra de búsqueda, busca y haz clic en Administrador de origen de datos ODBC (64 bits) para abrirlo.

2. A continuación, ve a la pestaña DSN del sistema en la ventana del Administrador de origen de datos ODBC y haz clic en Agregar para iniciar la adición de una nueva fuente de datos.

3. Selecciona el controlador de SQL Server de la lista a continuación y haz clic en Terminar para crear una fuente de datos SQL.

DirectQuery no admite todas las fuentes de datos. MySQL no es compatible, mientras que SQL sí lo es.

4. Ahora, nombre su fuente de datos (MssqlDataSource), seleccione una instancia de SQL (POWERSERVER\POWERSERVER) instalada en su máquina y haga clic en Terminar.

Tome nota del Nombre del Servidor SQL y del Nombre de la Fuente de Datos, ya que los necesitará para su cadena de conexión Python.

5. Revise los detalles de su fuente de datos y haga clic en Probar fuente de datos para probar su fuente de datos.

Si la conexión es buena, recibirá un mensaje de PRUEBA COMPLETADA CON ÉXITO, como se muestra a continuación.

Verifying testing the data source completed

Conexión al Servidor SQL

Ahora que ha creado una Fuente de Datos para su servidor SQL, utilizará esa Fuente de Datos para crear una cadena de conexión para su código Python.

1. Inicie su estudio visual y presione CTRL+SHFT+` para abrir una nueva terminal.

2. A continuación, ejecute el comando sqlcmd a continuación para iniciar sesión en su instancia de servidor SQL.

sqlcmd -S SQL_SERVER -E

3. Una vez iniciada la sesión, ejecute las siguientes consultas para crear una nueva base de datos (CREATE DATABASE) llamada MSSQLDB.

CREATE DATABASE MSSQLDB;
GO

4. Cree un archivo Python DBconnect.py y agregue el código a continuación, que le permite conectarse a su base de datos SQL usando el ORM SQLAlchemy para Python.

El motor marca el punto de partida de su aplicación SQLAlchemy. El motor describe el grupo de conexiones y el dialecto para la Especificación de API de Base de Datos de Python (DBAPI). Python DBAPI es una especificación dentro de Python para definir patrones de uso comunes para todos los paquetes de conexión a bases de datos. Esta especificación se comunica con la base de datos especificada.

//DBconnect.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
# Creando una cadena de conexión, usando urllib para formatear el texto.
conn = urllib.parse.quote_plus(
# Valores para la cadena de conexión tomados de la fuente de datos
'Data Source Name=MssqlDataSource;'

'Driver={SQL Server};'

'Server=POWERSERVER\POWERSERVER;'

'Database=MSSQLDB;'

'Trusted_connection=yes;'

)

try:
# Creando conexión con el motor sqlalchemy
	coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
	print("Passed")
except:
	print("failed!")

5. Crea un archivo Python llamado CreateTable.py en tu carpeta principal y agrega el siguiente código, y ejecútalo. El código siguiente crea una tabla llamada students en tu base de datos SQL.

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

# Crea una estructura de Metadatos que contiene definiciones de tablas
  # y objetos asociados como índices, vistas, disparadores, etc.
meta = MetaData()

# Representa el CREATE TABLE en la sintaxis SQL regular para crear una tabla.
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# Utiliza el objeto engine para crear todos los objetos de tabla definidos
  # y guarda la información en los metadatos.
meta.create_all(coxn)

6. A continuación, abre tu SSMS y verifica la base de datos, tabla y columnas creadas.

7. Crea un archivo Python llamado WriteToTable.py, agrega el siguiente código y ejecútalo.

Este código contiene la lógica para escribir valores en la tabla de la base de datos.

//WriteToTable.py
# Importando la tabla students desde el archivo CreateTable.py.
from CreateTable import students
# Importando la cadena de conexión coxn desde el archivo DBconnect.py.
from DBconnect import coxn
# Insertando múltiples valores en la tabla students
ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])
conn = coxn.connect()
conn.execute(ins)

Preparación de la Puerta de Enlace de Datos local

Ahora que has creado una cadena de conexión y logrado conectarte a la base de datos, necesitarás crear una Puerta de Enlace de Datos. Esta Puerta de Enlace de Datos será responsable de conectar tu base de datos con Power BI.

1. Abre la puerta de enlace de datos local e inicia sesión.

2. Verifica el estado de tu puerta de enlace de datos local y confirma que esté en línea, como se muestra a continuación.

Checking if the On-premises data gateway is online

3. A continuación, abre Power BI, haz clic en Obtener datos bajo la pestaña Inicio y elige SQL Server para iniciar la conexión de tu origen de datos con Power BI.

4. Ahora, conecta con DirectQuery con lo siguiente:

Inserta el nombre de tu Servidor y el nombre de tu Base de Datos en los campos correspondientes.Asegúrate de que la opción de DirectQuery esté seleccionada.Haz clic en Aceptar para conectarte a DirectQuery.

5. Selecciona una tabla (students) de la base de datos y haz clic en Cargar para cargar los datos.

6. A continuación, haz clic en el icono de tabla para usar una visualización de tabla y ver los datos.

La tabla a continuación muestra los datos que cargaste de la tabla de la base de datos

7. Haz clic en el menú Archivo para acceder a las acciones a realizar en Power BI.

8. Ahora, haz clic en Publicar → Publicar en Power BI para publicar tu informe de datos.

9. Haz clic en Abrir <tu informe> una vez que se haya realizado la publicación con éxito, lo que redirigirá tu navegador a la lista de actividades recientes que has realizado en Power BI (paso 10).

10. Haz clic en el informe para abrirlo, como se muestra a continuación.

En la primera configuración, es posible que te encuentres con un problema de puerta de enlace como se muestra a continuación, ya que aún no has conectado la fuente de datos del proyecto a la puerta de enlace de datos de Power BI.

11. Haz clic en el icono de configuración bajo la columna Acciones para iniciar la adición de una nueva conexión de puerta de enlace. Al hacerlo, podrás solucionar el error de configuración de la puerta de enlace.

Initiating adding a new gateway connection

12. Configura la nueva conexión de puerta de enlace y haz clic en Crear para establecer una nueva conexión entre tu fuente de datos y la puerta de enlace.

13. Por último, una vez que veas que la puerta de enlace está funcionando, selecciona el nombre de la fuente de datos (MssqlDataSource) del menú desplegable Asociado con y haz clic en Aplicar.

Visualización y Gestión de Informes de Datos

Después de establecer una conexión entre la puerta de enlace y Power BI, podrás ver tu informe y configurar un horario de actualización.

1. Ve a la página de inicio de informes de Power BI.

El rendimiento de Power Bi Direct Query depende de la fuente de datos subyacente. El tiempo que tarda la fuente de datos subyacente en responder a las solicitudes determinará qué tan rápido el usuario puede ver el informe. O si verá los informes con éxito en primer lugar.

2. A continuación, haz clic en el informe, como se muestra a continuación, para abrirlo.

Después de abrir el informe, deberías ver los datos, como se muestra a continuación.

3. Vuelve a la página de inicio del informe y haz clic en Actualizar → Programar actualización para establecer una actualización programada del informe.

Además de crear informes en vivo, Power Bi Direct Query te permite programar una actualización automática de tus informes.

4. Ahora, selecciona el intervalo de actualización de tu elección desde el campo desplegable y haz clic en Aplicar para aplicar los cambios.

5. Abre tu script en Python WriteToTable.py y agrega el código siguiente para insertar más entradas en tu base de datos, luego ejecuta el script.

La ejecución de este código no proporciona una salida, pero verificarás los valores insertados en la tabla más adelante en los siguientes pasos.

No hay Editor de Consulta de Power Query para fuentes multidimensionales como SAP Business Warehouse, lo que limita lo que puedes lograr con tus datos.

//WriteToTable.py
# Importar la tabla de estudiantes desde el archivo CreateTable.py.
from CreateTable import students
# Importar la cadena de conexión coxn desde el archivo DBconnect.py.
from DBconnect import coxn

# Inserción de varios valores en la tabla de estudiantes.
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'}
])
# Creación de un cursor de conexión.
conn = coxn.connect()
# Ejecución del comando insert().
conn.execute(ins)

6. A continuación, cambia a tu navegador Power BI y haz clic en la pestaña Conjuntos de datos + flujos de datos.

Si todo va bien, verás la última hora de actualización y la próxima actualización del conjunto de datos como se muestra en la captura de pantalla a continuación.

Ten en cuenta que Power Bi Direct Query es sensible a cambios y formato. Puedes recibir un error si tu Editor de Consulta de Power Query contiene pasos complejos.

Verifying the last refreshed time and next refresh of the dataset

Como se esperaba, la recarga ocurre automáticamente después de 15 minutos, y la siguiente actualización está programada para ocurrir en otros 15 minutos.

Conclusión

A lo largo de este tutorial, has aprendido cómo utilizar la funcionalidad de Consulta Directa de Power BI para conectar tu conjunto de datos almacenado en tu servidor. También has aprendido a ejecutar consultas directamente contra tus datos.

Crear informes es un proceso largo que implica recopilar, limpiar, cargar en una fuente de datos, cargar datos en Power BI y trabajar en las visualizaciones de Power BI. Este proceso mencionado se da. Pero después, puedes actualizar el conjunto de datos manualmente o actualizar los datos automáticamente según el horario establecido.

La Consulta Directa de Power BI tiene sus contras al manipular datos en tu conjunto de datos. Pero el hecho de que puedas crear un informe una vez y nunca más trabajar en el backend es un incentivo masivo cuando se utiliza esta función.