En esta entrada se mostrará de forma detalla cómo podemos manejar grandes cantidades de datos con Power BI, esto se conseguirá utilizando algunas técnicas tales como:
- Tener orígenes en modo direct query e import en el mismo modelo.
- Pre-calcular los resultados de consultas más costosas.
- Analizar las consultas generadas por Power BI.
Las features de Power BI utilizadas son: Modelos compuestos, agregaciones y query analizer plane.
Primero vamos a introducirnos en el escenario inicial:
Tengo un report de Power BI, tarda mucho en visualizar los datos para mi usuario, el origen tiene demasiados datos para poder utilizar el modo import, y el modo direct query tarda demasiado ya sea por la volumetría o simplemente tarda demasiado en poder resolver la lógica de la consulta 🙁
En este punto podemos empezar a desesperar, porque no importa lo bonito y lo bien que resolvemos las preguntas analíticas de nuestros usuarios. Si esto tarda no sirve.
Para obtener el detalle del rendimiento de nuestro Power BI, vamos a centrarnos en un visual que muestra las ventas a nivel diario utilizando el Performance Analyzer, para activarlo tenemos que ir al menú view>Performance Analyzer.
- DAX Query: este es el tiempo entre el envío visual de la consulta DAX y se devuelven los resultados, incluye el tiempo del direct query.
- Direct Query: tiempo que tarda en ejecutarse la consulta en el origen.
- Visual display: tiempo requerido para que lo visual se dibuje en la pantalla, incluido el tiempo requerido para recuperar cualquier imagen web o geocodificación.
- Otro: tiempo requerido por el visual para preparar consultas, esperar a que se completen otros visuales o realizar otro procesamiento en segundo plano.
Todos los resultados son en milisegundos, en total tarda unos 2 min en poder resolver un visual con el detalle diario de las ventas, la mayoría del tiempo 55.533 (casi todo el tiempo) tarda en ejecutar la consulta direct query en el origen y también teniendo en cuenta que en el archivo de Power BI solo tenemos una sola visual, lo hace totalmente inviable, si ponemos 3 tablas o dos gráficos más nos llevaría muchos minutos visualizar.
Ahora que tenemos claro el escenario inicial, vamos a empezar a utilizar las distintas técnicas que listamos en el párrafo inicial para resolver esta situación:
Tener orígenes en modo direct query e import en el mismo modelo
Tenemos que tener claro algo, Actualmente el modo import siempre, siempre, siempre es más rápido. No tenemos que enviar querys al origen y trabaja sobre memoria, no disco. entonces todo lo que podamos tener en modo import (habrá tablas muy grandes que no podamos guardar en memoria, esas las dejaremos en Direct Query de momento 🙂
Para conseguir tener tablas de distintos direct query e import en el mismo modelo, utilizaremos los modelos compuestos:
Los modelos compuestos permitieron crear relaciones muchos a muchos que antes no eran posibles y también nos dejan definir el tipo de almacenamiento de cada una de las tablas, para hacerlo tenemos que ir al model view, seleccionar la tabla y en avanzado seleccionar el tipo de almacenamiento que queremos, tenemos disponibles:
- Import: Utiliza memoria ram para precachear las tablas.
- DirectQuery: cada vez que se utilice esta tabla, se envían las consultas al origen.
- Dual: Dependiendo del contexto, la tabla puede trabajar como import o DirectQuery.
Este último puede causar confusión, el modo dual funciona de la siguiente forma: primero las tablas que son dual, se recargan como cualquier import al momento de la actualización de las tablas, y después dependiendo si para resolver una consulta se relaciona con una tabla que tiene modo direct query, no utiliza la información pre-cacheada si no que se comporta como una DirectQuery y envía la query al origen. Así nos aseguramos que cuando son direct query el join de datos se hace en origen y no en Power BI que puede ser costoso.
Otra cosa a resaltar es que si pasamos un origen de direct query a import es irreversible, lo normal es comenzar como import y pasar a Direct Query o Dual según la necesidad.
Una vez que aplicamos eso al modelo queda de la siguiente forma:
Una vez hemos hecho, todo lo que utilice las tablas en modo import o dual, va a mejorar. Pero cuando utilicemos la tabla central de ventas seguiremos enviando la query a origen porque no pudimos meter todos los datos en memoria.
es momento de aplicar la siguiente técnica de la agenda:
Pre-calcular los resultados de consultas más costosas.
Y esto, lo conseguiremos utilizando la feature agregaciones:
La tabla de agregaciones solo es otra tabla del modelo, pero la particularidad que tiene es que podemos definir que esta tabla está relacionada con otra tabla del modelo (“Su tabla detalle”). entonces en la tabla de agregaciones definimos la referencia de cada columna y el tipo de resumen que aplica, count,group by etc.
Entonces la funcionalidad es la siguiente, cada vez que se necesite cualquier dato de la tabla detalle en este caso la tabla de ventas, primero intentara buscarla en las tablas de agregaciones (Por orden descendente según el campo precedencia) que tenga referenciadas si lo encuentra lo devuelve de la tabla de agregaciones, aquí es donde la MAGIA ocurre, como la información de las agregaciones es información agrupada en general tendremos menos líneas y si ocupan menos podremos ponerlas en modo import, debido a que ya nos cabe en memoria 😀
Esa es la MAGIA, cuando necesitemos información agregada (yo estimo que el 80% de las preguntas analíticas en un entorno de BI, son agrupadas) responderán nuestras agregaciones y cuando se quiera el detalle ira más rápido, ¿porque? esto es porque ya no nos traeremos todo el detalle,solo consultaremos la información diaria filtrada para un Año/mes en especifico de nuestra tabla de detalle.
Aplicando esa técnica al modelo anterior queda de la siguiente forma:
Ahora la mayoría de consultas: por año, año/mes irán a mis dos agregaciones que son modo import y solo las que tengan consultas a nivel de día irán a mi tabla de detalle que es DirectQuery.
Una vez hecho todo esto ya podemos hacer un par de pruebas para ver la mejora de performance:
Primero utilizando visuales que utilicen la información agregada al año y año/mes:
Y cuando finalmente utilizamos el detalle filtrado para un año/mes determinado obtenemos lo siguiente:
De esta forma para la mayoría de nuestras consultas tendremos respuesta inmediata utilizando las agregaciones, y cuando necesitemos el detalle será en direct query filtrado, obteniendo una mejora de performance bastante considerable 6 veces más rápido, ni 20 segs :).
¡Muchas gracias! Cualquier duda escribe un comentario y te respondo.
