#power bi python

Primero que nada un poco de teoría. Existen escenarios web los cuales requeiren de Gateway para su tratamiento. Aun si estamos en power query online (dataflows) necesitaremos uno. Hay tres funciones cláscias que son de interés y funcionan distinto. WebContent, WebPages y WebContentBrowser. Si quieren conocer la diferencia y entender cual pide gateway y cual no, pueden leer la siguiente doc: https://learn.microsoft.com/en-us/power-query/connectors/web/web-troubleshoot

Me gustaría comenzar aclarando que no voy a hacer una introducción a Azure Functions. No voy a explicar que es, cómo funciona y cómo setear el entorno. Para eso ya hay excelentes videos en internet o podemos leer más en la siguiente doc de microsoft:

https://learn.microsoft.com/es-es/azure/azure-functions/create-first-function-vs-code-python?pivots=python-mode-configuration

Para este post necesitamos conocimientos previos en Python básico y Visual Studio Code. Asumiendo que ya tenemos el entorno seteado con las extensiones de Visual Studio code, vamos a comenzar con un ejemplo sencillo conectando a una API. Ya logueados en el apartado de Azure y con visibilidad a nuestra suscripción, vamos a crear una Function App. Podemos pensarlo como el servidor de procesamiento de muchas Azure Functions. En ese espacio podemos tener muchas functions, pensemos a cada una como un request.

Al momento de crearla tenemos 4 pasos

Ese nombre será participe de la URL de la API que estamos generando. 

Ahora podremos crear la función. Se almacenará en la carpeta que tengamos apuntando el Visual Studio Code. Clickeamos el rayito para crear una función dentro de la Function App

La aplicación creada se verá así:

Lo que necesitamos saber es que tenemos una función main donde se ejecutará el código principal. Luego depende si llamamos a la función con get o post si podemos capturar items de parametros de url o body. Eso nos ayudaría a incrementar la seguridad puesto que sin los parametros correctos o autenticación no podríamos obtener la respuesta. A modo de ejemplo vamos a hacer una simple lógica para que la url retorne el resultado cada vez que sea llamada sin necesidad de nada más puesto que considero que no es sensible la data del nombre de los workspaces en mi tenant de demo.

Veamos que simple es escribir código dentro de main devolviendo un dict o json en el return bajo el status_code deseado. Podemos aprovecharnos de los mensajes para ser claros en fallas para recibirlas en Power Bi. 

Código de la imagen en mi Github.

Usando SimplePBI para obtener grupos, pueden ver que simplemente generamos token, creamos objeto de grupo (workspaces) y llamamos a los workspaces que nuestro Service Principal puede ver.

Luego agregue al return una aclaración adicional para cuando lo que queremos devolver no es un “texto” literal sino un dict o json que es el “mimetype”.

NOTA: Si no sabes que es SimplePBI podes pasar por aqui.

IMPORTANTE: aclaro que tenemos un secret expuesto en este código, lo mejor para una azure function así sería usar un Azure KeyVault a nuestras contraseñas y secretos para que no queden expuestos.

Si vamos a usar una librería importada tendremos que buscar el archivo requirements.txt en el panel de recursos y agregarla. Yo lo hice para SimplePBI.

Si necesitamos utilizar pandas para tomar datos de un origen estructurado, podemos utilizar “ DataFrame.to_dict(orient="records") ” en el json.dumps del return para convertir nuestro frame al formato de mimetype json.

Get data

Mucho sobre python y funciones, vamos a PowerBi Desktop a conectarnos. Usaremos el conector web para traer la información con credenciales anónimas.

Dependiendo como orientamos nuestro json devuelto en la API que nos generamos en Azure Functions vamos a tener que efectuar transformaciones en power query. En este caso la devuelta por SimplePBI es muy uniforme y el motor practicamente lo resuelve solo.

Veamos como queda:

Ahora tenemos nuestra data cargada en Power Bi usando python sin necesidad de un gateway personal. Ya podemos publicar nuestro informe al servicio de power bi y configurar las credenciales como anónimas.

ACLARACIÓN: las Azure Functions tiene un límte de uso (timeout) en 5 minutos. Nuestra ejecución no puede durar más de eso o fallaría y nuestro propósito quedaría perdido.

Conclusión

Pre-Requisitos

Lo primero que necesitas son dos prerequisitos. 

1- La versión 0.1.4 de SimplePBI o superior. En esa versión incorporaron la clase azpause que nos ayudará. Para actualizarla pueden usar pip:

pip install simplepbi --upgrade

2- Una App Registrada en Azure con secreto creado para usar como Service Principal. Copiaremos el Tenant Id, Client o App Id y Secret generado.

Ejecución 

Para iniciar vamos a ir a nuestro recurso de Azure (Fabric, PBI Embedded o AAS) y daremos permiso en el control de accesos (IAM) al Service Principal (App Registrada) como “Contributor”. Este permiso le dará posibilidad de ejecutar acciones como prendido y apagado. A partir de ese momento seremos libres de ejecutar el sencillo código.

from simplepbi import azpause # Initialize the object authenticating Azure azure = azpause.Azpause(TENANT_ID, client_id, client_secret) # Run method of the object for pause or resume azure.resume_resource(subscriptionId, resourceGroupName, resourceType, resourceName) azure.pause_resource(subscriptionId, resourceGroupName, resourceType, resourceName)

Así de simple con tres líneas. Importar librería, autenticar creando objeto y llamar el método del objeto especificando valores que podemos copiar del “Overview” del recurso:

suscriptionId: el id de la suscripción, no del tenant.

resourceGroupName: nombre del grupo de recursos donde creamos el recurso

resourceType: tipo de recurso, recibe uno de tres valores posibles “FABRIC”, “PBI” o “AAS”

resourceName: nombre del servidor AAS o la capacidad Fabric/Embedded

Enlace al repo con su doc: https://github.com/ladataweb/SimplePBI/blob/main/AzPauseResume.md

Automatizarlo en Azure

Uno de los mejores usos de esa acción es programar la ejecución de dichas líneas conociendo las brechas de tiempo en que no se usan. Para ello podemos hacerlo de manera local con Windows Schedule o en Azure para no depender de una VM. Dentro de Azure hay diversos servicios, podríamos usar por ejemplo Azure Functions. En mi caso voy a mostrar el ejemplo con una cuenta de Automation creando un Runbook.

En el portal de Azure crearemos una cuenta de automatización o en ingles Automation Account. El código que usaremos estará en Python 3.8. Una vez creada la cuenta busquemos la opción Python Packages donde agregaremos la librería:

Para agregar la librería primero descargaremos el archivo Wheel de SimplePBI. Pueden encontrarlo en: https://pypi.org/project/SimplePBI/#files

Con el archivo descargado basta con seleccionar “Add a Python Package” con signo +. Tras añadir el archivo “whl” debería reconocer el nombre y ustedes seleccionen 3.8 en la versión de python

Todo esto es para poder importar simplepbi en el código de nuestro runbook. Luego de cargar la librería, que puede tardar varios minutos, crearemos un runbook en el menú de la izquierda dentro de la cuenta de automatización

El ejemplo lo haremos con el Pausado. El código sería igual para el Resumir el servicio solo que cambiaría el último método. Creado nos guiará a una interfaz para comenzar a escribir nuestro código Python. Esto sería bastante sencillo. Algo así:

Sin embargo, recordemos que exponer claves en código es una falta de seguridad grave. Entonces lo mejor sería crear un Azure KeyVault para guardar nuestros secretos o IDs y que no queden expuestos. Para ello basta con crear el recurso KeyVault, añadir su cuenta como Key Vault Administrator y crear el secreto. Si quieren conocer más sobre esto pueden buscar ejemplos o seguir el training: https://learn.microsoft.com/en-us/training/modules/configure-and-manage-azure-key-vault/

Si usan KeyVaults se vería así:

Guardar y publicar. Luego podemos darle play para ver que todo funcione correctamente.

Lo último que haremos será calendarizar la corrida del script. Si ya conocemos el uso del recurso sabremos sus tiempos y horarios. Puede ser que se apague de noche o los fines de semana. Para ello, vamos al runbook y nos fijamos en el menú “Schedule” y seguimos las indicaciones de la imagen:

De ese modo podemos configurar corridas recurrentes del código para asegurarnos que todo esté en orden.

Para poder realizar esta acción con código nos vamos a nutrir de la Librería SimplePBI que construyó LaDataWeb, pero antes de salir y ver el código vamos a explicar como funciona.

Hoy por hoy el catálogo de consultas de la API no provee un método que nos permita copiar pbix pero si tenemos otros métodos que nos van a ayudar a llegar a este resultado. Para realizarlo serán necesarios los siguientes permisos:

Report.ReadWrite.All and Dataset.ReadWrite.All

Lo primero será determinar el ambiente en el cual escribir nuestro código para copiar los informes. Recomiendo usar un entorno cloud como una AzureVM, Azure Function, Automation Runbook, etc. Veamos porque es necesario y recomendado que sea cloud.

Como podemos apreciar, al no existir un request nativo para esto, la forma de realizarlo será exportando y volviendo a importar el archivo pbix. Si nuestro entorno es local tendremos que descargarlo y subirlo, lo cual puede demorar bastante dependiendo del tamaño del archivo. Si utilizamos un entorno cloud podremos reducir bastante el tiempo que esta ajustado al ancho de banda local. 

Nuestra librería contendrá el método que necesitamos bajo la categoría de “Reports”. La ejecución es bastante sencilla. Primero importamos los paquetes necesarios.

from simplepbi import token from simplepbi import reports

Inicializamos objetos por categorías para realizar acciones de forma más cómoda:

t = token.Token(tenant_id, power_bi_client_id, power_bi_username, power_bi_password, None, use_service_principal=False ) r = reports.Reports(t.token) 

NOTA: Recordemos que podemos crear nuestro token autenticando con service principal si ubicamos el con secret en donde vemos el None y cambiamos el último argumento use_service_principal=True

Una vez creado todo eso simplemente basta llamar el método con nuestros valores deseados.

r.simple_copy_reports_between_groups(workspace_id_origin, report_id, workspace_id_destination, displayName=None, nameConflict="CreateOrOverwrite")

Los argumentos son:

workspace_id_origin: área de trabajo de origen

report_id: id del informe de origen para exportar e importar

workspace _id_destination: área de trabajo de destino

displayName: nombre deseado para el destino caso que querramos cambiar el nombre. Por defecto None le colocará el mismo que tenía en el origen.

nameConflict: acción en caso de encontrar el mismo nombre, por defecto creara o reemplazará

Restricciones: este método solo permite copiado de informes que contengan el conjunto de datos. No funciona con Live Connection.

¿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.

¿Dónde puedo usarlo?

Antes de comenzar es importante tener configurado nuestro entorno deseado de python sobre nuestra herramienta. Para ello vamos a las Opciones de configuración de Power Bi y validamos que el path a python.exe sea el correcto. En mi casi tengo un entorno de anaconda. Es por ello que no tengo una carpeta “Python” como parte del directorio.

Para comprender como usarlo es importante comenzar definiendo en que parte de la herramienta se puede correr código del lenguaje. Existen tres formas de usar Python dentro de Power Bi:

Conector “Python Script”

Transformación de datos con Python

Visualización de Python

Conector Python

El conector python nos permite ejecutar cualquier código inicial dentro de las librerías instaladas dentro de nuestro entorno. El conector ejecutará el código como un script reteniendo todas las variables creadas dentro de las líneas. La gran ventaja la verán a continuación puesto que un dataframe de la famosa librería Pandas es interpretada como tabla.

import pandas as pd df = pd.read_csv("https://raw.githubusercontent.com/ibarrau/PowerBi-code/master/DatosPrueba/InternetSales.csv ", delimiter=";")

Al terminar nos aparecerá una pantalla de navegación preguntando cual de dichas variables queremos cargar en nuestro modelo.

Transformación de datos con Python

El segundo método para utilizar python es posterior a la conexión de Power Bi contra un origen de datos. En lugar de utilizar a python como el medio para llegar a un origen, continuo con la conexión nativa de Power Bi para utilizarlo posteriormente como transformación. Este proceso solo se aplica dentro del “editor de consultas” al cual accedemos en el menú de “Transformar Datos”.

Aquí Power Bi nos comentará que para transformar algo de nuestra tabla actual tenemos que usar el nombre “dataset”. Veamos un sencillo ejemplo de filtro para solo ver las ventas de las tiendas con clave entre 300 y 400.

La ejecución de éste código nos dará como resultado un data frame, como indica la siguiente imagen, que podremos desplegar dando click en “Tabla” 

Asi lograremos ejecutar un script de transformaciones sobre nuestro origen de datos leído con Power Bi. Las funciones ejecutadas en este espacio están limitadas a las instaladas en el entorno donde se ejecuta al igual que el método anterior.

Visualización de Python

La tercera y última opción es la de ejecutar código sobre un modelo de datos ya creado con objetivo de plotear una visualización. Esta opción está en el menú de visualización y nos desplegará un pequeño espacio para escribir el código.

Cuando arrastremos alguna columna sobre la visualización habilitaremos el editor que nos permitirá interactuar con dicha columna. El script nos guiará con un mensaje explicativo.

En ese mensaje ya vemos incluida las columnas que agregamos. Observen el siguiente ejemplo con la columna categórica “Cal” y un valor numérico “Val”.

Importando una librería de visualización y ejecutando el plot tendremos la posibilidad de darle “Play” al botón de arriba a la derecha y nuestra visualización ejecutará el código mostrando el ploteo.

Limitaciones

Hay ciertos detalles que debemos tener en cuenta al momento de utilizar Python sobre Power Bi Desktop.

Para la primera y segunda opción es necesario que contemos con un On Premise Data Gateway en su modo PERSONAL para actualizar los datos. El gateway instalado en modo personal permite la lectura de un entorno Python instalado en la computadora o máquina virtual donde ésta puerta de enlace. Solo así podremos programar las actualizaciones de nuestro conjunto de datos.

La tercera opción ésta limitada en librerías a utilizar. A diferencia de las opciones anteriores que ejecutan el código en el entorno del gateway, ésta debe ejecutar código Python si o si sobre el servicio de Microsoft. Es por ésta razón que tendremos un listado limitado de librerías disponibles para utilizar. Las librerías son:

matplotlib

numpy

Pandas

scikit-learn

scipy

seaborn

statsmodels

xgboost

Por cualquier error o problema les dejo una documentación más detallada de microsoft que podría definir otros limites más finos de la lectura de Python sobre Power Bi Service

https://docs.microsoft.com/es-es/power-bi/connect-data/service-python-packages-support