Cuando trabajamos con fuentes de datos y tablas de gran volumen, entre las operaciones que nos resulta ya común trabajar está la de actualizar, pero sucede que en ocasiones solo nos interesa hacerlo para un numero determinado de tablas del conjunto de datos.
Una de las principales ventajas de esto es que reducimos la latencia y el tráfico de datos, además de que solo actualizaremos lo que necesitemos y eso nos ahorrara mucho tiempo. Los compañeros de Stratebi, especialistas y partners de Microsoft, nos cuentan como hacerlo:
Desde el servicio en línea de PowerBI para lograr este objetivo se puede realizar de varias formas, pero en todas el principal inconveniente es que necesitaremos PowerBI premium o premium por usuario.
Aunque se muestren algunas soluciones en el que el usuario puede actualizarlas manualmente la idea es que se automatice este proceso liberando de cargas de trabajo y perdidas en coste por tiempo.
Para las soluciones previstas será necesario conocer previamente varias aplicaciones dentro de Azure, así como algunos conceptos que nos ayudaran a entender un poco mejor en que consiste. A continuación, están los enlaces a toda la documentación y se comentaran brevemente previo a pasar a la solución paso a paso.
Referencias
Solución principal (Automate Power BI single table refresh using Azure Data Factory and Azure Automation (co authored by Paulien van Eijk, Dave Ruijter):
Actualizar todas las tablas con PowerBI (Antecedentes):
https://microsoft-bitools.blogspot.com/2022/03/refresh-power-bi-datasets-with-adf-or.html
Setup Azure Data Factory and using Key Vault to store authentication elements(by Dave Ruijter):
https://www.moderndata.ai/2019/05/powerbi-dataset-refresh-using-adf/
Blog de Jorg Klein (Process Azure Analysis Services databases from Azure Automation):
https://jorgklein.com/2017/02/02/process-azure-analysis-services-databases-from-azure-automation/
Video resumen con otras soluciones posibles:
https://www.youtube.com/watch?v=OYYnoMa-93g
Otra solución usando XMLA y ADF: https://microsoft-bitools.blogspot.com/2022/05/refresh-1-table-in-power-bi-dataset.html
Antecedentes
Actualizar el set de datos y sus tablas ya se podía hacer y automatizar, pero el inconveniente que tenia es que no permitía elegir que se quería actualizar y lo hacia sobre todas las tablas. Esta opción venia incluida con la API REST de PowerBI premium siempre y cuando se tengan los permisos necesarios (rol de administrador u otro rol asignado con permisos).
El administrador deberá otorgar los permisos para que el Service Principal (SP) pueda usar la API de PowerBI y además otorgarle permisos para acceder al workspace donde esta el modelo y el dataset.
Para un mayor control y certeza de que las credenciales se almacenan de forma segura es conveniente usar Azure Key Vault (AKV). En AKV se va a guardar el token de AAD pero para generarlo antes necesitamos el id del SP, su clave y el id de AAD del tenant.
En Azure Data Factory (ADF) con una actividad tipo “Web Activity” vamos a recuperar las tres credenciales de antes que se guardaban en AKV.
Las tres credenciales formaban parte del token de AAD que ahora vamos a recuperar también y los haremos con el mismo tipo de actividad.
A continuación, vamos a actualizar las tablas y para el siguiente paso necesitamos el ID obtenido previamente y además los ID del workspace y del dataset. Estos últimos los guardamos como parámetros en caso de que cambiemos de dataset o workspace a futuro. Para actualizar usamos el mismo tipo de actividad que hará uso de la API.
Hasta aquí ha sido un breve resumen y en el enlace de la sección anterior está el enlace con documentación más en detalle. Se incluyen además otros enlaces relacionados con métodos similares a este sin necesidad de usar el SP.
1. Conceptos Principales
A continuación, se explicara brevemente los conceptos más relevantes.
WebHooks:
Son eventos que desencadenan acciones y se usan para la comunicación entre aplicaciones web. Dicho de otro modo, son puntos de retro llamada HTTP que almacenan datos de un evento en formato JSON o XML. El webhook envía una solicitud POST a la URL indicada cuando ocurre el evento.
Azure Automation (AA) y RunBooks
Es una herramienta de Azure en la nube encargada de la automatización, actualización y configuración de sistemas operativos, entornos externos de Azure y tareas de negocio mediante la ejecución de runbooks gráficos de PowerShell o Python.
Uno de los elementos importante de Automation es el recurso compartido de las credenciales, es decir se pueden usar para almacenar de forma segura la información confidencial y la configuración de los runbooks.
Automation Runbook (AAR) es una herramienta Azure dentro de Automation que sirve para automatizar eventos y procedimientos mediante los runbooks. Para importar o crear runbooks hay que usar Service Manager y Orchestrator. Si además queremos visualizar o editarlos Service Manager se ocupa de los runbooks.
Los tipos de runbooks:
Azure Key Vault (AKV):
Se utiliza para almacenar de forma segura las claves, secretos, tokens, certificados y además permite administrar el control de acceso a los mismos y el cifrado de los datos. En el caso que estamos tratando aquí se usara para almacenar el clientID y la clave por ejemplo.
2. Solucion Principal
Requisitos previos:
Para la solución final será necesario usar diferentes servicios de Azure, como ADF, AKV, AAD y AA, así como los runbooks y webhook.
A modo de ilustrar la idea de la solución se tiene el siguiente esquema simplificado con la conexión entre los servicios que se usará.
Paso 1 Configurar ADF y AKV:
El primer paso será configurar Azure Data Factory y AKV tal y como se describe en el enlace de Dave Ruijter.
Paso 2 Configurar AA:
El segundo paso será configurar Azure Automation. Para ello podemos hacerlo mediante el uso conjunto con Azure Analysis Service (AAS) como se describe en el blog de Jorg Klein.
Para este ejemplo en su lugar usaremos AKV para almacenar las credenciales y recuperarlas con ADF posteriormente.
Para comenzar con AA primero creamos una cuenta y al instante tendremos también nuestro runbook (seleccionar el tipo PowerShell) con la plantilla de configuración que mas adelante editaremos. El código del script de PowerShell es el siguiente donde el “credentialName” habrá que editarlo acorde a nuestros datos.
Param ( # To retrieve dynamic values in a webhook request, only this WebhookDate object parameter is supported, we need to pass it in the body of the webhook request. [Parameter(Mandatory=$False,Position=1)] [object] $WebhookData ) Write-Output "Runbook kicking off.." $errorActionPreference = "Stop" Write-Output "Get all parameters from the WebhookData parameter.." $Parameters = (ConvertFrom-Json -InputObject $WebhookData.RequestBody) Write-Output $Parameters Write-Output "Get the parameter values.." $WorkspaceName = $Parameters.WorkspaceName $Server = "powerbi://api.powerbi.com/v1.0/myorg/$WorkspaceName" $Database = $Parameters.Database $TableName = $Parameters.TableName $RefreshType = $Parameters.RefreshType $CallBackUri = $Parameters.callBackUri Try { Write-Output "Retrieve the Service Account credentials from the Automation Account credential.." $Credential = Get-AutomationPSCredential -Name '{YourCredentialName}' Write-Output "Invoke the table process.." $result = Invoke-ProcessTable -Server $Server -Database "$Database" -TableName "$TableName" -RefreshType $RefreshType -Credential $Credential Write-Output "Results:" $result Write-Output "Result.XmlaResults:" $result.XmlaResults $Body = @{ StatusCode = "200" } Write-Output "Invoke call back to ADF with status 200.." Invoke-RestMethod -Method Post -Uri $CallBackUri -Body $Body } Catch{ Write-Output "An error occurred:" Write-Output $_ # Return error in case of failure $Body = @{ StatusCode = "400" } Write-Output "Invoke call back to ADF with status 400.." Invoke-RestMethod -Method Post -Uri $CallBackUri -Body $Body } Write-Output "Runbook finished."
Además de referenciar las credenciales para enviar información desde ADF a AA vamos a usar los webhooks que agregaremos en Azure Runbook. Mientras creamos nuestro webhook es importante guardar URL que se genera pues nos permitirá ejecutar el runbook mas adelante. Para su almacenado seguro usaremos AKV.
En Azure Automation, la solución tendrá tres elementos necesarios para conectarla a Azure Data Factory.
Webhook: la configuración del webhook es la URL de activación que guardaremos en Azure Key Vault. Este webhook se usa para enviar un disparador al Runbook para que comience a ejecutarse.
Credenciales: las credenciales se guardan (encriptan) en la cuenta de Azure Automation y manejan la autenticación entre el runbook y PowerBI. Puede ser una cuenta de servicio o una entidad de servicio. En Azure Data Factory, leemos las credenciales de Key Vault, donde tenemos que reconfigurarlas para el runbook.
Script: el script es el script de PowerShell que se activa a través del webhook para ejecutarse y usa las credenciales guardadas en la cuenta de automatización. En el cuerpo de la llamada a la API, el script recibe varios parámetros enviados por Data Factory en el cuerpo de la solicitud. Estos parámetros son:
Workspace de PowerBI: se usará para concatenar en el script con la cadena predeterminada para obtener el XMLA.
Nombre del dataset: el nombre del conjunto de datos se usa en el script TMSL para especificar el conjunto de datos para activar la actualización de la tabla especificada.
Nombre de la tabla: la tabla que debe actualizarse en el conjunto de datos especificado.
Política de actualización: define el tipo de actualización que debe ejecutarse para la tabla especificada en el conjunto de datos especificado.
Paso 3 Desarrollar el pipeline en ADF:
A continuación, vamos a construir el pipeline en ADF y la primera actividad a usar es “Web Activity”. Con ella crearemos uno por cada una de las credenciales que queremos recuperar, es decir las credenciales(TenantID, ClientID, Secret, URL del webhook) guardadas en AKF. En la siguiente foto se pueden ver cada una de ellas.
En la configuración de cada una de estas actividades hay que usar en URL las credenciales de AKV
@activity('Get webhookurl from AKV').output.value
Además, en el Body incluimos los parámetros para actualizar las tablas, como el workspace, el nombre de la base datos y el tipo de actualización. Con estos parámetros podremos crear el XMLA que nos permitirá comunicarnos con nuestro dataset de PowerBI. Estos parámetros también están en el script de PowerShell mencionado anteriormente.
powerbi://api.powerbi.com/v1.0/myorg/{WorkspaceName}
@json( concat(' {"WorkspaceName": "',pipeline().parameters.workspacename,'", "Database": "',pipeline().parameters.database,'", "TableName": "',pipeline().parameters.table2,'", "RefreshType":"Full"}' ) )
El paso siguiente es recuperar el Token de Active Directory (AAD) pues las credenciales de antes nos sirven para autenticarnos en PowerBI y al autenticarnos obtenemos el token de AAD. Este paso es necesario para llamar a la API de cara a actualizar la tabla.
El tercer paso es crear un dataflow por cada actualización que vayamos a realizar y configuraremos las actualizaciones según nuestras necesidades.
El ultimo paso es ejecutar los runbooks por cada tabla que se quiera actualizar y para eso hemos usado la actividad “WebHook” pues esta recibe las URL de los pasos previos que necesita para lanzar la ejecución de los runbooks.
El resultado final del pipeline debería quedar así:
3. Comparativa con otras alternativas
Puntos fuertes
Usar una solución basada en scripts de PowerShell nos permite la integración con ADF y con ello automatizar la solución, además de que podemos conectar con AKV y guardar nuestras credenciales.
Puntos débiles
Requiere del conocimiento para crear el script y construir el pipeline, así como hacer uso de varios servicios de Azure.
Alternativas
La solución presentada relata de octubre de 2021 y ya hay otras soluciones mas recientes del año 2022 como las presentadas en el video que se enlaza u otras como las de este enlace. Son muy similares a la solución propuesta en este documento pues usan también ADF aunque según pase el tiempo será preferible optar por las soluciones mas recientes ya que en los últimos dos años Microsoft ha hecho muchos cambios en sus servicios y no hay garantía de que la solución aquí presentada sea del todo factible.
4. Conclusiones
Como hemos visto ya la necesidad de actualizar tablas concretas es una operación necesaria y que tenemos distintas opciones para resolverlo y que en algunos casos requiere el uso de ADF para su automatizado o con XMLA si se quiere una opción mas simple pero en cualquiera de los casos que se elija la limitación que aun esta por resolver y no depende de nosotros es tener la opción premium.
Una operación como esta que podría ser tan necesaria y común estaría bien que fuera mas accesible al usuario sin necesidad de tener premium pues muchos usuarios usan el pro.
Más info sobre PowerBI:
- Integracion SAP - PowerBI
- PowerBI Trucos (Vol I)
- PowerBI Trucos (Vol II)
- PowerBI + Synapse Analytics (paso a paso)
- 30 Consejos y Buenas Prácticas para hacer un proyecto de Power BI con éxito
- Cómo crear diseños de Dashboards espectaculares con PowerBI
- Videotutorial: Trabajando con Python en Power BI
- Aplicación PowerBi Turismo
- Aplicación PowerBI Financiera I
- Aplicación PowerBI Financiera II
- Aplicación PowerBI eCommerce
- Aplicación PowerBI Salud
- Aplicación PowerBi Smart City
- Aplicación PowerBI Energía
- Aplicación PowerBI Sports Analytcis
- Power BI Premium Utilization and Metrics
- PowerBI Embedded: Funcionamiento y costes
- Bravo para PowerBI
- Como integrar Power BI con Microsoft Dynamicssalesfo
- SQL Server Profiler para Power BI
- Como usar Report Analyzer en PowerBI, para mejorar el rendimiento
- Power BI embebido en Jupyter Notebook
- Tabular Editor para Power BI: Videotutorial y manual en español
- Personaliza tus gráficas en Power BI con Charticulator y Deneb
- Comparativa PowerBI vs Amazon QuickSight
- Como usar emoticonos en PowerBI
- Buenas prácticas con Dataflows en Power BI
- Power Automate para Power BI: Cómo funciona
- ALM Toolkit para Power BI
- Os presentamos Goals in Power BI para hacer Scorecards
- Tutorial gratuito en español sobre Power BI Report Builder
- Conoce PowerBI Diagram View (Visual Data Prep). Paso a paso
- Futbol Analytics, lo que hay que saber
- Dashboard de medicion de la calidad del aire en Madrid
- Como funciona Microsoft Power BI? Videoturial de Introducción
- Big Data para PowerBI
- Quieres crear aplicaciones empresariales usando PowerBI, PowerApps y Power Automate de forma conjunta?
- Power BI tip: Uso de parámetros what-if
- Como integrar Salesforce y PowerBI
- Videotutorial: Usando R para Machine Learning con PowerBI
- Las 50 claves para aprender y conocer PowerBI
- PowerBI: Arquitectura End to End
- Usando Python con PowerBI
- PowerBI + Open Source = Sports Analytics
- Comparativa de herramientas Business Intelligence
- Use Case Big Data “Dashboards with Hadoop and Power BI”
- Todas las presentaciones del Workshop ‘El Business Intelligence del Futuro’
- Descarga Paper gratuito: Zero to beautiful (Data visualization)
- SAP connection tools for process automation: Microsoft, Pentaho, Talend (User Guide)
- PowerBI Trucos (Vol. III)
