mirando Jeffrey Wang como invitado de transmisión en vivo con Reid Havensy una de las docenas de cosas maravillosas que Jeffrey compartió con la audiencia fue la lista de optimizaciones que realiza el motor DAX al crear un plan de consulta óptimo para nuestras medidas.
Y, el que me llamó la atención fue con respecto a las llamadas “medidas escasas”:
Para simplificarlo, una vez que define la medida, Motor de fórmula en Vertipaq Agregará un filtro implícito no vacío a la consulta, que debería permitir que el optimizador evite un unión cruzada completa de las tablas de dimensión y escanee solo aquellas filas donde realmente existen los registros de la combinación de sus atributos de dimensión. Para las personas que provienen del mundo MDX, la función no vacía puede parecer familiar, pero veamos cómo funciona en DAX.
Lo que más resonó conmigo fue cuando Jeffrey aconsejó que no reemplazaran los espacios en blanco con ceros (o cualquier valor explícito) en los cálculos de potencia BI. Ya he escrito cómo puede manejar los espacios en blanco y reemplazarlos con cerospero en este artículo, quiero centrarme en las posibles implicaciones de rendimiento de esta decisión.
Estableciendo el escenario
Antes de comenzar, un descargo de responsabilidad importante: la recomendación de no reemplazar en blanco con 0 es solo eso, una recomendación. Si la solicitud comercial es mostrar 0 en lugar de en blanco, no significa necesariamente que deba negarse a hacerlo. En la mayoría de los escenarios, probablemente ni siquiera notará una disminución del rendimiento, pero dependerá de múltiples factores diferentes …
Comencemos por escribir nuestra medida de DAX simple:
Sales Amt 364 Products =
CALCULATE (
[Sales Amt],
FILTER ( ALL ( 'Product'[ProductKey] ), 'Product'[ProductKey] = 364 )
)Usando esta medida, quiero calcular el monto total de ventas para el producto con ProductKey = 364. Y, si pongo el valor de esta medida en la Visual de la tarjeta, y encienda el analizador de rendimiento para verificar los tiempos para manejar esta consulta, obtengo los siguientes resultados:
DAX Query tardó solo 11 ms para ejecutarse, y una vez que cambié a DAX Studio, el XMSQL generado por el motor de fórmula fue bastante simple:
Y, si echo un vistazo al plan de consulta (físico), puedo ver que el motor de almacenamiento encontró solo una combinación existente de valores para devolver nuestros datos:
Agregar más ingredientes …
Sin embargo, supongamos que la solicitud comercial es analizar datos para el producto Key 364 en un nivel diario. Vamos y agregamos fechas a nuestro informe:
¡Esto fue nuevamente muy rápido! Ahora verificaré las métricas dentro del estudio DAX:
Esta vez, la consulta se amplió para incluir una tabla de fechas, que afectó el motor de almacenamiento de trabajo necesario, ya que en lugar de encontrar solo 1 fila, esta vez, el número es diferente:
Por supuesto, no notará ninguna diferencia en el rendimiento entre estos dos escenarios, ya que la diferencia es de solo unos pocos milisegundos.
Pero esto es solo el comienzo; Solo estamos calentando nuestro motor DAX. En ambos casos, como puede ver, solo vemos valores “llenos”, esa combinación de filas donde se cumplen nuestros dos requisitos, la clave del producto es 364 y solo aquellas fechas en las que tuvimos ventas para este producto, si mira a fondo la ilustración anterior, las fechas no son contiguas y algunas faltan, como el 12 de enero del 14 de enero al 21 de enero y así.
Esto se debe a que Formula Engine era lo suficientemente inteligente como para eliminar las fechas en las que el producto 364 no tenía ventas usando el filtro no vacío, y es por eso que el número de registros es 58: tenemos 58 fechas distintas donde las ventas del producto 364 no estaban en blanco:
Ahora, digamos que los usuarios comerciales también quieren ver esas fechas intermedias, donde el producto 364 no había realizado ninguna venta. Entonces, la idea es mostrar 0 $ $ para todas esas fechas. Como ya se describió en el artículo anterior, hay varias formas diferentes de reemplazar los espacios en blanco con ceros, y usaré el COALESCE() función:
Sales Amt 364 Products with 0 = COALESCE([Sales Amt 364 Products],0)Básicamente, el COALESCE La función verificará todos los argumentos proporcionados (en mi caso, solo hay un argumento) y reemplazará el primer valor en blanco con el valor que especificó. Simplemente dicho, verificará si el valor de los productos AMT 364 de ventas está en blanco. Si no, mostrará el valor calculado; De lo contrario, reemplazará en blanco con 0.
Espera, ¿qué? ¿Por qué estoy viendo todos los productos, cuando filtré todo, excepto el producto 364? ¡Y mucho menos eso, mi mesa ahora tardó más de 2 segundos en renderizar! Comprobemos lo que sucedió en el fondo.
En lugar de generar una sola consulta, ahora tenemos 3 de ellas. El primero es exactamente lo mismo que en el caso anterior (58 filas). Sin embargo, las consultas restantes se dirigen a las tablas del producto y las fechas, extrayendo todas las filas de ambas tablas (la tabla de productos contiene 2517 filas, mientras que la tabla de fechas tiene 1826). No solo eso, eche un vistazo al plan de consulta:
¿4.6 millones de registros? ¿Por qué demonios sucede? Déjame hacer los cálculos por ti: 2.517 * 1.826 = 4.596.042… Entonces, aquí teníamos un unión cruzado completo entre las tablas de productos y fechas, lo que obligó a verificar cada tupla (combinación de producto de fecha). ¡Eso sucedió porque obligamos al motor a devolver 0 por cada tupla que de otro modo volvería en blanco (y en consecuencia se excluiría del escaneo)!
Esta es una visión general simplista de lo que sucedió:
Lo creas o no, hay una solución elegante para mostrar valores en blanco fuera de la caja (pero no con 0 en lugar de en blanco). Simplemente puede hacer clic en el campo Fecha y elegir Mostrar elementos sin datos:
Esto también mostrará las celdas en blanco, pero sin realizar una unión cruzada completa entre las tablas de productos y fechas:
¡Ahora podemos ver todas las celdas (incluso en blanco) y esta consulta tomó la mitad del tiempo del anterior! Verifiquemos el plan de consulta generado por el motor de fórmula:
¡No todos los escenarios son catastróficos!
La verdad que se dice, podríamos haber reescrito nuestra medida para excluir algunos registros no deseados, pero aún no sería una forma óptima para que el motor elimine los registros vacíos.
Además, hay ciertos escenarios en los que reemplazar los espacios en blanco con cero no causará una disminución significativa del rendimiento.
Examinemos la siguiente situación: estamos mostrando datos sobre el monto total de ventas para cada marca. Y agregaré mi cantidad de ventas Medida para el producto 364:
Como era de esperar, eso fue bastante rápido. Pero, lo que sucederá cuando agrego mi medida que reemplaza los espacios en blanco con 0, lo que causó estragos en el escenario anterior:
HM, parece que no tuvimos que pagar ninguna multa en términos de rendimiento. Vamos a ver el plan de consulta para esta consulta DAX:
Conclusión
Como sugirió Jeffrey Wang, debe mantenerse alejado de reemplazar los espacios en blanco con ceros (o con cualquier otro valor explícito), ya que esto afectará significativamente la capacidad del optimizador de consulta para eliminar el escaneo de datos innecesarios. Sin embargo, si por alguna razón necesita sustituir un espacio en blanco con algún valor significativo, tenga cuidado cuándo y cómo hacerlo.
Como de costumbre, depende de muchos aspectos diferentes: para columnas con baja cardinalidad, o cuando no muestra datos de múltiples tablas diferentes (como en nuestro ejemplo, cuando necesitamos combinar datos de las tablas de productos y fechas), o tipos visuales que no necesitan mostrar una gran cantidad de valores distintos (es decir, visual de la tarjeta)), puede escapar sin pagar el precio de rendimiento. Por otro lado, si usa tablas/matrices/gráficos de barras que muestran muchos valores distintos, asegúrese de verificar las métricas y los planes de consulta antes de implementar ese informe a un entorno de producción.
¡Gracias por leer!