NOTA del autor: Aunque el procedimiento descrito en este artículo es operativo, este post ya tiene algún tiempo y puede mostrar información algo obsoleta, habiendo actualmente posibilidad de acceder desde excel a los propios datasets almacenados en el servicio de Power BI (en caso de usuarios Pro). Fin nota.
Has construido un modelo de datos perfecto y pantallas con complejas visuals tipo matriz para analizarlo en Power BI. Pero hay pantallas que algún usuario, usualmente de perfil financiero y acostumbrado a utilizar Excel, desea analizar con esta herramienta para realizar sus ajustes, proyecciones, etc… Esa fue la inspiración para este post.
En este post:
“¿Y no se puede exportar a Excel?”
Quizá fuese suficiente exportar matrices en reports de Power BI a Excel, y a partir de las mismas que el usuario realizase las labores necesarias. Pero hay un inconveniente grande, y es que desde Power BI Desktop no es posible hacerlo. O al menos, si esperas poder exportar a Excel la matriz tal y como se muestra, no va a ser posible. Sí los datos que han permitido generarla y con los mismos construirla de nuevo, pero poco más. Algo manual y muy limitado.
Puede darse también el caso de que determinadas pantallas quizá no se hayan desarrollado por cumplir un propósito demasiado específico y, con buen criterio, para que los interesados no acaben perdidos en un bosque de informes “irrelevantes”, se ha decidido que no formen parte del inventario de reports de la empresa.
¿No sería interesante tener la posibilidad de acceder este modelo desde fuera del entorno de Power BI, desde Excel, por ejemplo?. De este modo los usuarios con podrían elaborar sus reports específicos, o las tareas que requieran, partiendo de un modelo de datos único y validado dentro de la organización.
Power BI es una herramienta en constante evolución, por lo que no descartemos que en breve la funcionalidad de exportación directa de matrices sea implementada, al menos me apunté a la solicitud en la lista de ideas para Power BI…
Power BI incorpora una ETL (Query Editor) para crear el modelo de datos, que puede fusionar datos heterogéneos, con complejas transformaciones, homogenizaciones, comprobaciones y validaciones de valores… Algo susceptible de llamarse Data Warehouse a partir del cual se construye un modelo multidimensional…
No es fácil, y ni mucho menos intuitivo. Pero sí, es posible.
Para empezar arranca Power BI desktop y abre el archivo “pbix” que desees…
Antes debes saber que Power BI Desktop es un SSAS limitado
Una de las mejores herramientas de análisis de datos con las que sigo trabajando es Sql Server Analysis Services (SSAS), por eso no pude ocultar mi satisfacción tras descubrir que:
Power BI desktop, cuando está abierto trabaja como un servidor SSAS, creando una instancia accesible vía “localhost:NumPuerto”.
Desde luego, como servidor, no es comparable SSAS con Power BI; Microsoft no va a tirar piedras sobre su propio tejado y hace que esta instancia tenga limitaciones operativas, muchas respecto a SSAS. Una de esas limitaciones es la accesibilidad desde otras herramientas. Por lo pronto, aunque sabemos que el servidor está en localhost, el puerto es dinámico, y para cada archivo pbix que abramos se abrirá una instancia diferente con diferentes números de puerto, generados aleatoriamente.
En estas circunstancias va a ser complicado acceder desde Excel a los datos de PowerBI. Pero se puede…
Conocer el número de puerto
- La primera opción es ejecutar el comando netstat en la línea de comandos de Windows. Este comando lista todos los puertos abiertos en el equipo. No es una forma muy evidente, en realidad un prueba-error en el que es posible intuir cuáles son los puertos abiertos por que siguen un patrón similar al de la imagen (que arroja el número 50761). No es método que me guste, pero había que indicarlo.
- Otra manera sería abrir un archivo “msmdsrv.port.txt”. Este muestra el número de puerto. Cada vez que se abre un archivo pbix se crea dentro de carpeta:
C:\Users\USUARIO\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
… una subcarpeta con detalles del servidor, entre los que encontraremos el archivo buscado:
C:\Users\USUARIO\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaceXXXXXXX\Data\msmdsrv.port.txt
La subcarpeta AnalysisServicesWorkspaceXXXXXXX, las XXXXXX son aleatorias en cada sesión y cuando se cierra PowerBI se borra.
Parece un método interesante. Pero vamos a ver otro, que por lo menos me servirá para presentar una herramienta que deberías conocer.
Conocer el número del puerto con DaxStudio
Una de las formas más utilizadas de hacerlo es usando la herramienta DaxStudio, una herramienta, al margen de la funcionalidad que motiva que la mencione hoy, muy útil.
Tras abrir un archivo pbix concreto, abre DaxStudio y al conectar elige la instancia que desees analizar:
Tras conectar podrás observar el número de puerto en la barra de estado de la aplicación:
Y ya que estás dentro de DaxStudio, podrías realizar las pruebas o análisis que desees.
Conectar con Power BI desde Excel
Analizando en Excel (Versión 1)
Con DaxStudio, o cualquier otro de los métodos mencionados, ya conocemos el puerto y será fácil crear una conexión en Excel con el modelo de datos PBI como se indica:
Y podrás generar los reports en Excel que requieras:
NOTA: Necesitarás tener instalado el complemento PowerPivot en Excel.
Analizando en Excel sin saber el puerto (Versión 2 y mejor)
Nuestro director financiero está algo más satisfecho y el resultado final evidentemente le ha gustado. Pero eso de tener que abrir PowerBI, después el DaxStudio, después crear una conexión en Excel a SSAS… le gusta tan poco como a mi.
Así que tras bucear por blogs me encuentro con una hoja de macros de Excel que permite conectar al vuelo con el servidor Power BI, con lo que nos ahorramos algunos pasos. La hoja podrás descargarla de la siguiente dirección (Excelator BI, añadelo a tus favoritos):
EXCELATOR BI: Excel Workbook Connection to Local Power BI Desktop
En la Excel descargada vamos crear una conexión a PowerBI pulsando el botón indicado:
Seguimos el asistente…
Vemos que ha detectado el servidor:
Conectamos y a analizar:
Guardamos los cambios para que en las próximas sesiones no haga falta conectar de nuevo, bastando sólo con pulsar el botón “Refresh…”.
A nuestro director financiero esta vez sí que le ha gustado y, en un alarde de generosidad, nos invita a un café para celebrarlo 😉
NOTA:
En el momento de publicar este post, Microsoft informa de que la actual versión ya permite conectar a otros archivos PowerBI como origen de datos. De momento sólo es posible hacerlo dentro de PowerBI y no desde Excel, pero no descartemos que en breve sí lo sea.
Apasionado de las tecnologías de análisis de datos y Business Intelligence, y con larga experiencia en el uso de las mismas en empresas de todos los sectores, tamaños, y proyectos de diversa complejidad, en los cuales disfruto creando, planteando y desarrollando soluciones.
Tengo una curiosidad insaciable y demasiadas aficiones, entre ellas tocar el bajo con mi grupo.
