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 🙁

Initial Scenario
Initial Scenario

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.

Initial Scenario
Initial Scenario

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:

Composite Model Summary
Composite Model Summary

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.

Storage Modes
Storage Modes

Una vez que aplicamos eso al modelo queda de la siguiente forma:

Storage Mode Transformation
Storage Mode Transformation

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:

Aggregations Summary
Aggregations Summary

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 😀

Composite Model Plus Aggregations MAGIC
Composite Model Plus Aggregations MAGIC

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:

Aggregations In The Model
Aggregations In The Model

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:

Using Year Aggregation
Using Year Aggregation
Using Year Month Aggregation
Using Year Month Aggregation

Y cuando finalmente utilizamos el detalle filtrado para un año/mes determinado obtenemos lo siguiente:

Filter Detail in Direct Query
Filter Detail in Direct Query

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 :).

Final Scenario
Final Scenario

¡Muchas gracias! Cualquier duda escribe un comentario y te respondo.

0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada.

You May Also Like

Nuevas funciones para el lenguaje de expresiones de SSIS en SQL 2012

El lenguaje de expresiones de Integration Services podemos utilizarlo en columnas derivadas, expresiones en propiedades de componentes, tareas, administradores de conexión, variables, en la nueva Expression Task, etc…  Tiene su propia sintaxis, operadores, conjuntos de funciones, etc.. (se observan similitudes con las expresiones de C++). En la versión de SQL 2012 se han agregado tres nuevas funciones que se engloban en el conjunto de funciones para el tratamiento de cadenas: Left, Token y TokenCount.
Leer más

SQSA: SolidQ Social Analyzer – Analiza tus redes sociales con SolidQ

Business Intelligence acaba siendo una herramienta para tomar decisiones de negocio rápidas, fiables y que tengan impacto en el rendimiento de nuestra compañía. Tradicionalmente, y por nuestra experiencia, los datos objeto del análisis provienen de procesos de negocio internos: ventas, stocks, logística, producción… Estos datos, seguro, nos permitirán generar información valiosa para la optimización de procesos y toma de decisiones, pero estamos dejando fuera un componente fundamental de la ecuación, si no el más importante: el cliente.