Hace un tiempo que se me viene presentando requerimientos de personas no tan afines a Power Bi pero si al análisis de datos más duro que va directo con lenguajes de programación como Python y R.
Los pedidos pasan por poder fácilmente llegar a los datos de modelos tabulares completamente funcionales para respuestas o análisis adhoc que se solicitan en el momento. Naturalmente el pedido solía resultar en comentarles que podía correr Python desde el interior de Power Bi Desktop, exportar sus datos, etc. Todo eso puede ser una solución preventiva pero no creo que se compare a la nueva posibilidad de la API que ahora esta disponible (GA) en Power Bi.
Éste artículo relata las restricciones del request y como formarlo desde el lenguaje de programación Python formando un Pandas DataFrame.
¿De que se trata DAX Query API?
Recientemente Microsoft liberó un nuevo request en su API. Se trata de ejecutar un Post a un Dataset con XMLA Endpoint en el servicio para correr DAX desde la API. Esto nos permitiría obtener rápidas respuestas a preguntas inmediatas como así también acompañar exploraciones o análisis más duros realizados con Python.
Antes de comenzar veamos un poco las restricciones necesarias
- Esta operación solo puede ejecutarse en la Nueva Experiencia de Areas de Trabajo (V2)
- El usuario que se autentique para realizar el tiro debería tener permisos de construcción en el dataset (build).
- La opción Allow XMLA endpoints and Analyze in Excel with on-premises datasets de la configuración de inquilinos del portal de administración debe estar encendida.
- Los Datasets almacenados en Azure Analysis Services y conectados directamente no son soportados.
- Hay un limite de 100k filas y solo puede ejecutarse una consulta a la vez.
Bastaría con aclarar que necesitamos una app registrada con permisos de lectura/escritura sobre datasets y que una respuesta 400 se refiere a una mala formación de la query o del body. Para más información podemos leer el detalle en la documentación:
https://docs.microsoft.com/es-es/rest/api/power-bi/datasets/execute-queries
Ejecución
Vamos a partir la demo considerando que ya todos saben obtener un Bearer Token para autenticar la App. En caso que no sepas hacerlo simplemente abrí el enlace a mi github más abajo con el código completo.
El Post a ejecutar consiste de tres argumentos necesarios para construirlo. La consulta (código DAX que devuelva una tabla), el token (autenticador de la API) y dataset (el id que podemos tomar de la URL de Power Bi Service cuando abrimos el mismo). Con estos tres vamos a construir el request para la API.
Considerando un argumento query, auth_token y dataset veamos como queda en python las tres partes necesarias
url= "https://api.powerbi.com/v1.0/myorg/datasets/"+dataset+"/executeQueries"
body = {"queries": [{"query": query}], "serializerSettings": {"incudeNulls": "true"}}
headers={'Content-Type': 'application/json', "Authorization": "Bearer {}".format(auth_token)}
En un principio todo se ve sencillo y ajustado. Ahora bien, hay un detalle que debemos tener presentes. Los diccionarios de python, como el formado en el body, no son un json aceptable por la API de microsoft. Es por eso que antes de ejecutarlo vamos a usar la librería Json para convertirlo de la siguiente manera:
res = requests.post(url, data = json.dumps(body), headers = headers)
Fíjense que el body estará rodeado de json.dumps que nos ayudará a no recibir un 400 por mal armado del tiro.
Ahora logramos obtener nuestra respuestas que sería un código bastante complicado de interpretar. Veamos un ejemplo sencillo de como regresan los datos. Ejecutando una DAX que solo pide los nombres de meses distintivos de una TablaFecha
EVALUATE VALUES(TablaFecha[Mes])
Tendríamos una respuestas así:
Como podemos ver la respuestas es un diccionario con varios hijos anidados. Comenzando con results, siguiente por tables hasta llegar a rows. Las rows son un diccionario nieto con key nombre de columna (TablaFecha[Mes]) y value como dato (abril).
Para poder interpretar esto vamos a ir extrayendo distintos datos que formen nuestro DataFrame. Cabe aclarar que vamos a importar pandas as pd. Un frame se forma por datos y columnas. Ahora bien para obtener los datos de semejante diccionario sería necesario recorrer y usar un for. Veamos entonces el siguiente código que nos regresaría las columnas, las filas (cantidad para recorrer) y una list comprehension que forme los datos.
columnas = list(res.json()['results'][0]['tables'][0]['rows'][0].keys())
filas = len(res.json()['results'][0]['tables'][0]['rows'])
datos = [list(res.json()['results'][0]['tables'][0]['rows'][n].values()) for n in range(filas-1)]
Extrapolamos las columnas obteniendo las keys del diccionario nieto de rows. Luego vemos la cantidad de filas involucradas con un len del diccionario de rows. Finalmente hacemos un for que recorra los values del diccionario de rows la cantidad de veces que obtuvimos hace un momento -1 por tema de índices.
De este modo podemos construir nuestro dataframe:
df = pd.DataFrame(data=datos, columns=columnas)
Llegamos a algo tipo:
Ahora si estamos listos para explotar nuestros datos en Python jugando con pandas.
Espero que esto les sea de ayuda para diversas situaciones. Aquí les dejo un enlace a mi github con el código completo.