API REST de Power BI: Conecta datos SQL en tiempo real sin controladores ODBC

Power BI · API REST en lugar de un controlador

Punto Power BI en un API RESTno es un controlador de base de datos

Power BI normalmente accede a una base de datos a través de un controlador en su equipo y, para la actualización programada, mediante una puerta de enlace local. Un punto de conexión de Query Streams es una URL HTTPS simple, por lo que Power BI Desktop y el servicio Power BI la leen con el conector web integrado. El portal genera la consulta Power Query M automáticamente. Exportar → Power Query M (.pq)Pega, añade tu clave, listo. El mismo archivo funciona en Excel.

Aproximadamente 5 minutos Sin controlador, sin puerta de enlace Los datos permanecen en tu red. Excel también

Lo que obtendrás al final

Una tabla de Power BI alimentada por una consulta SQL guardada a través de HTTPS, actualizable en el servicio sin una puerta de enlace local, ya que el punto final se comporta como cualquier API web. El punto final en sí se ve así en la consola del portal:

{
  "ciudad": "Seattle"
}

curl -X POST "https://api.querystreams.com/v1/endpoints/8f2c4d1e-6b3a-4f7e-9c21-5d0a7b3e2f19" \ -H "X-API-Key: qsapi_YOUR_KEY" \ -H "Content-Type: application/json" \ -d '{"city": "Seattle"}'

{
  "columnas": [ { "nombre": "id_cliente", "tipo": "entero" }, { "nombre": "nombre", "tipo": "cadena" }, { "nombre": "ciudad", "tipo": "cadena" } ],
  "filas": [
    { "id_cliente": 1042, "nombre": "Comerciantes del Viento del Norte", "ciudad": "Seattle" },
    { "id_cliente": 1057, "nombre": "Alpine Outfitters", "ciudad": "Denver" },
    { "id_cliente": 1063, "nombre": "Comida del puerto", "ciudad": "Halifax" }
  ],
  "meta": { "row_count": 3, "agent_duration_ms": 41, "id_solicitud": "req_7Hq2mKd9" }
}

La razón de la existencia de los controladores es que Power BI necesita comunicarse mediante el protocolo de red de cada base de datos, independientemente de dónde se ejecute. Por ello, la actualización programada requiere una puerta de enlace dentro de la red. Con un punto final, el agente que ya se encuentra en la base de datos ejecuta la consulta y api.querystreams.com responde a Power BI a través de HTTPS, por lo que Power BI no necesita instalar nada ni el servicio necesita conectarse mediante un túnel.

Lo que necesitarás

Una cuenta de Query Streams

Todos los planes incluyen la API REST. El plan gratuito ofrece un endpoint, dos claves y 250 filas por llamada, suficiente para crear esta guía. Regístrese en mi.querystreams.com.

El agente en un ordenador que puede acceder a su propia base de datos, con la conexión añadida

A menudo, se trata del propio servidor de la base de datos o de un ordenador que permanece siempre encendido en la misma red.

Una consulta guardada en el Generador de consultas

El punto final encapsula exactamente una consulta guardada. Escriba la sentencia SELECT, ejecútela una vez y guárdela. Agregue un filtro dinámico (un @variable en la cláusula WHERE) si los que llaman deberían poder filtrar.

Además de Power BI Desktop o Excel 2016 o posterior. Si aún no tiene un punto final, Centro de API REST de base de datos Te permite convertir una consulta guardada en una URL en cuatro pasos.

Conecta Power BI en cuatro pasos

1. Exportar el archivo M de Power Query

   En API abre tu punto final, haz clic Exportar y elegir Power Query M (.pq)El archivo es completo. dejar… entrar expresión con la URL del punto final y sus parámetros invocables ya definidos. Tiene este aspecto:

   Exportar · Power Query M (.pq)

   dejar
     Origen = JSON.Documento(Web.Contenido(
       "https://api.querystreams.com/v1/endpoints/8f2c4d1e-6b3a-4f7e-9c21-5d0a7b3e2f19", [Encabezados = [#"X-API-Key" = "qsapi_TU_CLAVE"], Consulta = [compresión = "ninguno"]])), filas = Origen[filas], tabla = Tabla.De los registros(filas)
   en
     mesa

   La exportación siempre pide compresión=ninguna, porque Power Query no puede decodificar marcos LZ4; Power BI espera JSON sobre gzip.

2. Pégalo en Power BI.

   En Power BI Desktop, seleccione Inicio → Transformar datos → Nueva fuente → Consulta en blanco, abre el Editor avanzado, reemplace su contenido con el archivo y haga clic HechoEn Excel la ruta es Datos → Obtener datos → De otras fuentes → Consulta en blanco, luego el mismo Editor avanzado.

3. Añade tu clave

   Reemplace el marcador de posición con su clave de 49 caracteres que comienza qsapi_ desde el punto final Compartir pestaña. Cuando Power BI pregunte cómo conectarse, elija Anónimo: la llave viaja en el Clave X-API La consulta ya establece el encabezado, por lo que no se necesitan credenciales de Power BI.

4. Cargar y actualizar

   Haga clic en Cerrar y aplicar. El filas La matriz se convierte en su tabla con una columna por cada columna de consulta. Publique en el servicio Power BI y configure una programación de actualización: debido a que el origen es una URL web, no se requiere una puerta de enlace local. Cada actualización es una llamada al punto final. Actividad pestaña.

La solicitud que realiza Power BI

En el fondo es la misma llamada que puedes hacer con curl. Los parámetros marcados como Callable se pueden pasar desde Power Query como Consulta = [ciudad = "Seattle"]:

curl "https://api.querystreams.com/v1/endpoints/8f2c4d1e-6b3a-4f7e-9c21-5d0a7b3e2f19?city=Seattle" \ -H "X-API-Key: qsapi_YOUR_KEY"

{
  "columnas": [ { "nombre": "id_cliente", "tipo": "entero" }, { "nombre": "nombre", "tipo": "cadena" }, { "nombre": "ciudad", "tipo": "cadena" } ],
  "filas": [
    { "id_cliente": 1042, "nombre": "Comerciantes del Viento del Norte", "ciudad": "Seattle" },
    { "id_cliente": 1057, "nombre": "Alpine Outfitters", "ciudad": "Denver" },
    { "id_cliente": 1063, "nombre": "Comida del puerto", "ciudad": "Halifax" }
  ],
  "meta": { "row_count": 3, "agent_duration_ms": 41, "id_solicitud": "req_7Hq2mKd9" }
}

Las filas se devuelven como objetos indexados por nombre de columna, con la lista de columnas y un meta Bloque que indica cuántas filas hay, si el resultado se recortó y el ID de la solicitud que puede citar para respaldar. La misma llamada como una POST coloca los parámetros en un cuerpo JSON, que es la opción correcta para listas largas de valores:

curl -X POST "https://api.querystreams.com/v1/endpoints/8f2c4d1e-6b3a-4f7e-9c21-5d0a7b3e2f19" \ -H "X-API-Key: qsapi_YOUR_KEY" \ -H "Content-Type: application/json" \ -d '{"params": {"city": "Seattle"}}'

?formato=csv

CSV en lugar de JSON, con fila de encabezado incluida. Aceptar: texto/csv hace lo mismo. Los puntos finales de transmisión devuelven NDJSON (un objeto JSON por línea) con un final {"_summary": true, "row_count": N} línea; revisa esa última línea.

?muestra=verdadero

Máximo cinco filas, sin costo. Ideal para conectar a un cliente.

?compresión=lz4

Un cuerpo de bastidor LZ4 para llamadas de alto volumen con decodificador; Código La pestaña tiene el fragmento del decodificador por idioma. El gzip simple se realiza automáticamente cuando su cliente envía Codificación aceptada: gzip.

Autorización: Portador qsapi_…

Aceptado como alias de Clave X-APIÚsalo desde el código del navegador.

Más allá de Power BI

Lo que permiten los planes

Cada llamada tiene un tiempo de espera fijo de 60 segundos para la consulta y un límite de respuesta de 8 MiB. Los bytes que devuelve se descuentan del límite de datos mensual de tu plan, el mismo que utilizan los complementos de Excel y Google Sheets. El plan gratuito incluye un punto final y 5 MB al mes, lo cual es suficiente para una tabla pequeña que se consulta varias veces al día. Consulte la página de precios. para las asignaciones por plan.

Para un informe, limite la consulta a las columnas que necesitan los elementos visuales y deje que la programación de actualización haga el resto; Power BI lee el resultado completo en cada actualización, por lo que el límite de filas estáticas de su plan es el límite máximo para una sola tabla.

Mantenerlo seguro

Cuando algo no funciona

Cada cuerpo de error tiene la forma {"error": {"code": "…", "message": "…"}} y cada respuesta conlleva una ID de solicitud X-QS Encabezado. Cita el ID cuando escribas al soporte.

Dos detalles específicos de Power Query: a Error de origen de datos nombrar 401 significa que la clave en el archivo M es incorrecta o fue revocada; Expression.Error: No se encontró el campo 'rows' del registro. significa que el punto final devolvió un sobre de error, así que ejecute la URL en la consola del portal para leerlo.

Preguntas que hace la gente

Guías relacionadas

• API REST de base de datos: Convierta cualquier consulta SQL en un punto final.
• API REST de Microsoft SQL Server
• API REST de PostgreSQL
• Conecta las API REST a Excel sin VBA ni Power Query.