Si ya has trabajado alguna vez con Power BI, sobre todo si te has interesado por su rendimiento, te sonará el concepto plegado de consultas (query folding en inglés). Este concepto es clave en el rendimiento de power BI y sin embargo a mucha gente se le escapa o no lo comprende muy bien. Hoy, voy a intentar arrojar algo de luz sobre este tema de manera sencilla y para todos los públicos aunque, ya os adelanto que el tema da para mucho y si os interesa al final del artículo os dejo alguna recomendación extra para continuar aprendiendo.
¿Qué es el plegado de consultas?
Empecemos por el principio, el plegado de consultas, o “Query Folding”, es un proceso por el cual el motor de Power Query (mashup engine) intenta llevar a cabo la mayor cantidad de transformaciones de datos posible en los sistemas de origen origen, en lugar de hacerlo después de que los datos se hayan cargado en la memoria. Esto puede resultar en una mejora significativa del rendimiento de Power BI, especialmente cuando se trabaja con grandes volúmenes de datos.
¿Qué es Power Query?
¡Quieto Roberto! A más de uno le acaba de volar la cabeza con el párrafo anterior. He introducido el concepto Power Query sin explicaros lo que es, perdonadme. Power Query es una característica de Power BI que cumple las funciones de ETL (extraer, transformar y cargar datos). Gracias al motor mashup engine es capaz de extraer datos de varios orígenes, transformar lo que sea necesario y por último cargarlos en un destino. Principalmente vamos a poder encontrar esta función en Power BI, en los flujos de datos de Power BI (dataflows) y en Microsoft Excel 2016 o superiores. Adicionalmente vamos a poder usar Power Query en Excel 2010 y 2013 si descargamos e instalamos manualmente el componente.
¿Cómo funciona el plegado de consultas?
Cuando creamos una consulta con lenguaje M en Power Query, estamos definiendo una serie de pasos para obtener y transformar nuestros datos. Estos pasos pueden incluir filtrado, agrupación, unión de tablas, entre otros. Idealmente, queremos que estos pasos se realicen en el origen de los datos. Esto es lo que se conoce como plegado de consultas y se podrá llevar a cabo en la mayoría de los casos si los orígenes admiten lenguaje SQL y, siempre y cuando, la instrucción M se traduzca en una sola select con funciones que existan en el lenguaje SQL.
El siguiente diagrama de la documentación oficial de microsoft explica el proceso paso a paso:
- Power Query recibe el script en código M desde el editor avanzado.
- El mecanismo de plegado de consultas envía una consulta de metadatos al origen de datos para evaluar sus características.
- El mecanismo de plegado de consultas determina qué información extraer del origen de datos y qué conjunto de transformaciones deben producirse dentro del motor de Power Query.
- Power Query consulta el origen de datos mediante una consulta nativa.
- El origen de datos devuelve los datos al motor de Power Query.
- Una vez que los datos están en Power Query, el motor de transformación de Power Query (también conocido como motor de mashup) realizará las transformaciones que no se pudieron plegar ni descargar en el origen de datos.
- Carga en el modelo de Power BI (o en excel) de los datos extraídos y transformados.
¿Por qué es importante?
El plegado de consultas es especialmente útil cuando trabajamos con grandes conjuntos de datos. Al realizar las transformaciones en el origen de los datos, reducimos la cantidad de datos que necesitamos cargar en la memoria, lo que puede resultar en un rendimiento significativamente mejorado. Si por ejemplo solo necesitamos unas pocas filas y columnas de la tabla de origen, gracias al plegado de consultas no vamos a tener que traer todos los datos para luego filtrarlos. Igualmente si, podemos realizar las uniones entre tablas, agregaciones y ordenados en el origen (que está optimizado para ello) es trabajo que le quitamos a nuestro Power BI.
Tiene otras ventajas, como que Power BI optimizará en gran medida las cargas incrementales de los datos cuando las consultas son 100% plegables ya que si no habría que leer todo el origen y cargarlo en memoria para que el mashup engine cribe las filas nuevas de las ya existentes.
¿Cómo saber si se está realizando el plegado de consultas?
Power Query Dataflows proporciona indicadores visuales que nos permiten saber si se está realizando el plegado de consultas. Estos indicadores se encuentran en la ventana de Power Query y nos muestran qué pasos se están plegando a través de iconos. Esto está muy bien resumido en la documentación oficial así que os dejo directamente el extracto.
En Power BI Desktop no va a ser tan sencillo verlo como mirar los iconos al lado de los pasos de la transformación, sin embargo, eso no significa que no podamos verlo. Simplemente con hacer clic derecho sobre el paso y verificar si nos está ofreciendo ver la consulta nativa podremos saber si se mantiene o no el plegado de consultas.
Andrés en nuestro grupo de Telegram añade: «Otra de las formas de saber si el plegado se mantiene, es con la función Value.Metadata (tabla). Devuelve información sobre si existe o no el plegado. Del mismo modo, podemos usar una instrucción para mantener el plegado, es con la función Value.NativeQuery() y con el parámetro EnableFolding=true. Esto nos permitirá enviar una consulta directa a SQL Server, mantener el plegado y poder seguir realizando pasos que mantengan el plegado. El problema es que cuando escribes directamente la consulta desde el conector a SQL Server, la consulta ya no seguirá plegando de ahí en adelante. Por suerte, la función Value.NativeQuery(…), si nos permite esto.«
Niveles de plegado de consulta
Como hemos visto a lo largo de este artículo, existen ciertas operaciones de transformación de datos incompatibles con el plegado de consultas. A esto se le llama romper el plegado de consultas y deberemos evitarlo en la medida de lo posible. Aun así, esto no quiere decir que la consulta ya no se vaya a plegar por tener un paso incompatible, la optimización del motor de Power Query sabrá plegar todos los pasos posibles y solo efectuar en el motor de transformaciones lo estrictamente necesario. En este sentido, nos vamos a encontrar con tres niveles de plegado de consultas:
- Plegado de consultas completo: Todas las transformaciones de consulta se delegan en el origen de datos. El motor de Power Query realiza un procesamiento mínimo y muy eficiente.
- Plegado parcial de consultas: Una parte de las transformaciones de la consulta, y no todas, se pueden delegar en el origen de datos. En este caso, una parte de las transformaciones las realiza el origen de datos y el resto se producen en el motor de transformaciones de Power Query.
- Sin plegado de consultas: La consulta no se puede plegar. Normalmente contiene transformaciones que no se pueden traducir al lenguaje de consulta nativo del origen de datos, ya sea porque las transformaciones no son compatibles o porque el conector no admite el plegado de consultas. En este caso, Power Query obtiene los datos sin procesar del origen de datos y utiliza el motor de Power Query para lograr los datos deseados mediante el procesamiento de las transformaciones necesarias a nivel del motor de Power Query.
Conclusión
El plegado de consultas es un concepto complejo pero crucial para el rendimiento en Power BI. Si trabajas a menudo con Power Query dedica tiempo a entender en profundidad esta funcionalidad y así mejorar considerablemente tus resultados. Por mi parte no me queda más que, como os había prometido al inicio, recomendaros el libro “Power BI Dataflows” de Francisco Mullor para convertiros en unos verdaderos maestros de la herramienta. Si solo os interesa este tema que hemos tratado hoy o si queréis usarlo como aproximación al libro, Fran ha publicado el capítulo dedicado al plegado de consultas de manera que está accesible de manera gratuita aquí. También podéis pasar por la academia virtual de Alex Ayala donde encontraréis cursos de Power BI de gran calidad.
Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!
