Acceder a datos de múltiples empresas de Dynamics 365 business central en cloud con funciones de Power BI

Vamos a ver cómo acceder a datos de múltiples empresas de Dynamics 365 business central en cloud con funciones de Power BI.

El servicio Dynaics 365 BC en Cloud no permite acceder directamente a la base de datos SQL Server como haríamos en una instalación on-premises y debemos recurrir a Web Services que el servicio expone y a los que podemos añadir más en caso de requerirlo.

En SQL server con un simple script SQL podemos copiar datos de diferentes orígenes en un DataWarehouse (DW) que nutra nuestro proyecto Power BI. Pero con datos procedentes de Web Services este proceso no es tan directo y, a no ser que programemos tareas IS o usemos ETL para realizar el mismo proceso de unificar en un DW, nos veremos obligados a acceder directamente a los Web Services desde Power BI.

No es un proceso complicado, pero si hay que agregar más empresas en la misma, o una diferente cuenta del servicio, debemos pensar en crear un proceso genérico y reutilizable que nos permite aglutinar en nuestro Power BI los datos de un modo cómodo y fácil de mantener.

Qué tenemos en Dynamics 365 Business Central

En este ejemplo vamos a conectar a https://businesscentral.dynamics.com/ en la cual hay dos CRONUS (tendremos la empresa de pruebas y un duplicado de la misma), pero supongamos que son dos empresas diferentes. Más adelante veremos qué pasa si añadimos una tercera…

Dentro de BC, si buscamos “Web Services“, podremos ver la lista de todos los puntos que hay expuestos por el servicio:

Si pulsamos sobre el link URL de oData V4, previa validación usuario, se abrirá una ventana con los datos en modo texto que expone el objeto, por ejemplo “Movs de Cliente”:

Conectar desde Power BI a Dynamics Business 365 Central

Power BI ofrece un conector específico accesible desde “Get Data”:

Tras seleccionarlo nos solicitará datos de acceso al servicio:

Y una vez conectado nos muestra las diferentes empresas en el mismo, y dentro de cada una, los puntos de acceso expuestos:

Vamos a cargar datos del diario de clientes (“CustLedgerentries”):

Esto nos creará una tabla con los datos que antes hemos podido ver como texto desde el servicio. Podemos eliminar de la tabla varias columnas, formatear… realizar las transformaciones que requiramos.

Añadir más empresas a Power BI: Problemas

El script M resultante de la operación anterior, en el que hemos eliminado varias columnas, es el siguiente:

En caso de necesitar añadir más empresas podríamos copiar y pegar el anterior proceso, cambiar la empresa y hacer una unión de todas las tablas resultantes en una sola para analizar en Power BI. El código de todas ellas será idéntico y sólo cambiará el código de empresa (destacado). Quédate con esto para después…

Pero supongamos que quisiéramos añadir una columna: Tendríamos que cambiar en todas las tablas. Esto, evidentemente, es laborioso, difícil de mantener, máxime cuando lo habitual en un proyecto es conectar a más tablas, como lista de clientes, productos, compras, ventas, datos financieros, etc… El siguiente esquema muestra la situación en la que nos veríamos con tan sólo tres WS y tres empresas:

Solucionar el caos con funciones

Estamos de acuerdo en que la situación anterior no gusta, resulta caótica y no es sostenible.

¿Y si pudiésemos crear funciones que simplificasen el proceso? Algo que resultase en un esquema como el siguiente, muy diferente al inicial, pero que ofrece la misma información con un proceso mucho más simple y sostenible, sin importar si hay una o 30 empresas:

Podemos y es lo que vamos a hacer.

Volvamos a Power BI, donde vamos a crear una tabla en la que meteremos la lista de empresas que han de estar en nuestro cuadro de mando. Éstas han de coincidir en su código con las del servicio BC, respetando mayúsculas-minúsculas:

Convertir una consulta en función

La idea de cómo convertir una query en una función es una de las muchas cosas que pude ver en Power BI Virtual Conf de 2020, en concreto de la conferencia de Mayte López ((23) Funciones en Power Query de 0 a 100 Mayte Lopez – YouTube). Dicho sea de paso, ésta y otras conferencias del ciclo fueron excepcionales: Gracias a tod@s.

El proceso pasa por abrir la query en el editor avanzado y añadir los parámetros al comienzo del script. En el caso de “Cust_LedgerEntries” vamos a añadir un parámetro “empresa”, que podemos añadir como se muestra, al principio del script, y reemplazamos el código de empresa, que ahora está como texto, por la variable “empresa” (eso que anteriormente he dicho que te quedes con ello…):

Cuando aceptemos, la query se convertirá en una función:

Renombramos como “f_movs_clientes”.

La idea ahora es que esta función se ejecute por cada una de las empresas de la lista de empresas. Vamos a crear una referencia a la tabla empresas (NO una copia):

Renombramos esta referencia como “Movs Clientes” y sobre esta referencia vamos a añadir una columna que será el resultado de invocar la anterior función, a la que pasamos como parámetro la columna empresa:

La columna resultante es una tabla que expandiremos para mostrar su contenido. Podremos ver que está ajecutando tantas llamadas al WS como empresas tengamos en la tabla:

Y podríamos hacer lo mismo con otro WS, por ejemplo, uno de finanzas:

Hemos simplificado muchísimo el modelo, será mucho más fácil de mantener , y añadir-quitar una empresa será tan fácil como editar la tabla “Empresas”en el sentido deseado. Es más, podríamos añadir más campos a esta tabla, como descripción larga, corta… y la relacionaremos con las otras en el modelo. Aplicamos cambios:

Agregamos una empresa

Editaremos la tabla empresas, añadiendo una nueva línea con el correspondiente código:

Procesamos de nuevo y veremos que los datos de la misma aparecen en el cuadro de mando, sin hacer nada más:

Espero que os sea útil.