Cuantas veces no te has preguntado qué es mejor, si crear una columna calculada con DAX o hacerla con Power Query.
Bueno, me consta que muchos ni se lo plantean, sobre todo en una fase de aprendizaje inicial de Power BI donde Power Query queda relegado a un tercer o cuarto plano.
Pero conforme se avanza en el aprendizaje de Power BI, es una pregunta habitual.
¿Qué es mejor crear una columna calculada en DAX o crearla en power query?
La respuesta es tajante:
Siempre que se pueda: Power Query.
Para ello utilizamos siempre diversos chascarrillos como por ejemplo que cuanto más cerca del origen que mejor, pero vamos a intentar explicar y demostrar por qué es así.
En primer lugar, es imposible crear un post sobre este tema y no referenciar al genio Marco Russo, que en octubre de 2019 escribió este post relativo a este tema:
Comparing DAX calculated columns with Power Query computed columns – SQLBI
Siguiendo ese artículo voy a establecer en primer lugar las diferencias principales entre crear una columna calculada o crear la misma columna en Power Query:
1º Una columna calculada en DAX no va a requerir el refresco de la tabla al añadirla al modelo, mientras que si la creamos en Power Query si necesitaremos refrescar la tabla para importarla al modelo.
Esto podría ser un punto de ventaja a favor de las columnas calculadas.
2º Cuando refrescamos la tabla el motor proceda las columnas calculadas después de que los datos de todas las particiones se hayan cargado y comprimido en memoria, mientras que la columna creada en Power Query se procesa como cualquier otra columna procedente del origen de datos.
Punto para Power Query.
3º En caso de que utilicemos refresco incremental, la columna creada en Power Query sólo se va a necesitar actualizar los datos de las filas que estén dentro de la partición que se va a actualizar (los últimos días) mientras que cuando la columna es calculada, se actualizará para todas y cada una de las filas de la tabla
Otro punto muy importante para Power Query
4º Esto es fundamental:
Una columna calculada en DAX no participa de la evaluación de la columna base para ordenar por lo que la compresión puede no ser la adecuada (algo que demostraré a continuación) mientras que una columna en power query se comprimirá como cualquier otra columna de manera mucho más óptima.
Es en este punto ya donde Power Query gana por goleada y posteriormente veremos como dos columnas calculadas pueden casi duplicar el volumen de una tabla.
5º El coste de procesamiento a la hora de actualizar en la columna calculada de DAX corresponderá a la evaluación secuencial para cada fila incluida en la operación de actualización y si tienes varias columnas calculadas se procesan secuencialmente una tras otra aumentando por tanto el tiempo de actualización. Sin embargo, el coste de procesar la columna calculada en Power Query dependerá mucho del tipo de origen de datos sobre todo cuando la expresión pueda ser optimizada utilizando el plegado de consulta.
Por otro lado y muy importante como veremos en el ejemplo, la mejora de la compresión de una columna puede afectar negativamente a la compresión de otras columnas del modelo
6º Una columna con mejor compresión es más pequeña en memoria y suele ofrecer mejores niveles de rendimiento. Esto es importante en las operaciones de filtrado, agrupación y agregación que implican a la columna.
La compresión de una columna calculada en DAX será menor que la de una columna calculada en Power Query.
En resumen, gana por goleada la no utilización de columnas calculadas en DAX siempre que sea posible la utilización de columnas en Power Query.
Pero para sostener esto vamos a verlo con un ejemplo:
He creado dos tablas completamente iguales en dos pbix diferentes para analizarlas con Dax Studio.
La tabla contiene 12.627.608 Registros, la fact sales online de Contoso y me he quedado sólo con los campos que podéis ver en la imagen:
DateKey
SalesQuantity
ReturnQuantity
ReturnAmount
DiscountQuantity
DiscountAmount
UnitPrice
SalesAmount
Categoria
Sales Amount y Categoría son campos calculados en un PBIX con DAX y en el otro con Power Query.
En Power Query Sales Amount = ([SalesQuantity]*[UnitPrice]) + ([ReturnQuantity]*-1*[ReturnAmount]) – [DiscountQuantity]*[DiscountAmount]) en una columna personalizada
Y Categoria otra: if [UnitPrice]<10 then «C»
else if [UnitPrice]>100 then «A»
else «B», type text)
En DAX las columnas calculadas idénticas:
Sales Amount Calculada = (FactOnlineSales_Calculada[UnitPrice]*FactOnlineSales_Calculada[SalesQuantity]) + (FactOnlineSales_Calculada[ReturnQuantity]*-1*FactOnlineSales_Calculada[ReturnAmount])-(FactOnlineSales_Calculada[DiscountQuantity]*FactOnlineSales_Calculada[DiscountAmount])
Categoria Calculada = SWITCH( TRUE(),
FactOnlineSales_Calculada[UnitPrice]<10, «C»,
FactOnlineSales_Calculada[UnitPrice]>100, «A»,
«B»)
Si utilizamos el analizador de Vertipaq en ambos Pbix, el asombroso resultado es el siguiente:
Como podemos observar el tamaño de la FactSalesOnline_Calculada, la que tiene las columnas calculadas casi duplica al de la FactSalesOnline que viene íntegramente de PQ.
Si observamos las columnas cardinalidad, Dictionary y Hier Size son idénticas en ambos modelos. Sin embargo, la compresión del dato es muchísimo mayor en el caso de las columnas que vienen definidas en Power Query.
Además, en el ejemplo podemos advertir lo que decíamos antes de que la compresión de una columna afecta a la compresión de las demás. Como podemos observar, La columna DateKey se comprime mucho peor en el modelo que viene íntegro de Power Query, a pesar de ello, la peor compresión de las columnas Unit Price y DiscountAmount y, sobre todo la muy peor compresión de las columnas calculadas en DAX hacen que simplemente por razones de volumen sea infinitamente más efectiva la creación de las columnas en Power Query.
Espero que a partir de ahora os penséis muchísimo el crear o no una columna calculada.
Por último recordad que hemos introducido un nuevo curso imprescindible para empezar a trabajar con Power BI del MVP de Microsoft Salvador Ramos en nuestra academia. 100% recomendado
4 comentarios