Performance Tuning DAX – Parte 1

Un usuario Pregunto ✅

Greg_Deckler

Introducción

Bien, dejemos algunas cosas fuera del camino desde el principio.

En primer lugar, no me considero un experto en optimización del rendimiento de DAX ni reproduzco uno en la televisión. A decir verdad, la gran mayoría de las soluciones que publico en los foros son, con la mayor certeza, no un código DAX optimizado. Lo crea o no, eso no me molesta … en absoluto. La realidad es que la optimización de DAX es difícil y requiere mucho tiempo y, francamente, la gran mayoría de las veces la optimización no importa y es simplemente una pérdida de tiempo. O la optimización hace que el código sea menos legible. Todos recordamos los viejos tiempos del código Perl. Para la gran mayoría de los casos de uso, si el DAX se ejecuta en 600 milisegundos o 300 milisegundos, el usuario promedio no estará tan molesto. Todavía son tiempos de respuesta inferiores a un segundo. Lo que más importa es una solución. Una vez que tenga una solución, siempre se puede optimizar si es necesario.

Entonces, con eso fuera del camino, pasemos a la siguiente aclaración preventiva inútil. Esta publicación trata sobre la optimización de DAX. No optimización del modelo de datos. No optimización de Power Query. No, elija un tema, optimización. Optimización de DAX. Si desea dejar comentarios sobre otras optimizaciones que no son DAX, escriba su propio artículo de blog sobre ellas. Ese no es el propósito de esta publicación.

Tercera aclaración: no afirmo que este sea el código DAX «más» optimizado. De hecho, me ENCANTARÍA ver comentarios sobre un código DAX aún más eficiente. He adjuntado el archivo PBIX que usé a esta publicación.

Entonces, ¿de qué trata esta publicación? Sencillo. Un miembro de la comunidad presentó un problema en el que Power BI tardaba 30 minutos en representar una visualización. Deje que se hunda, 30 minutos. Eso son muchos minutos, amigos. Esta publicación simplemente documenta mis esfuerzos para optimizar el DAX tanto como sea posible. Ahora, esos 30 minutos fueron más como 10 minutos en mi máquina. Y tengo un Surface Pro 4 desvencijado con solo 16 GB de memoria, así que me siento mal por ese tipo … De todos modos, pude reducir este cálculo a unos 20 segundos, lo que es una mejora razonable. Esta publicación trata de presentar a la comunidad algunas lecciones aprendidas en este viaje. Su kilometraje puede variar porque la optimización de DAX es un área compleja, engañosa y relativamente inexplorada. Y, mejor aún, variará según las circunstancias. Pero, sentí que había suficientes lecciones aprendidas aquí para abrir la discusión a una audiencia más amplia. Tal vez tal vez no.

El problema original

Entonces, el problema original involucraba tres tablas dentro de un modelo de datos más grande que se parecía a lo siguiente:

image.png

La tabla de hechos involucrada, Tracking History, solo tenía alrededor de 60,000 filas, lo que hizo que este problema fuera aún más intrigante. Los problemas de rendimiento de DAX son relativamente raros y que ocurra uno en un conjunto de datos tan pequeño es algo sorprendente.

Había dos medidas involucradas:

No. of Orders = 
VAR StartDate = VALUE ( SELECTEDVALUE ( 'Tracking History'[Start Date]) )
VAR EndDate = VALUE ( SELECTEDVALUE ( 'Tracking History'[End Date] ) )
VAR MinDateInContext = VALUE ( MIN ( 'DateTimeTable'[Date] ) )
VAR MaxDateInContext = VALUE ( MAX ( DateTimeTable[Date] ) )
RETURN
IF (
        AND (  StartDate > MinDateInContext, EndDate < MaxDateInContext ),
        1,
        IF (
            and(AND (StartDate > MinDateInContext, EndDate>MaxDateInContext),MaxDateInContext>StartDate),
            1,
            IF (
               and( AND ( StartDate < MinDateInContext, EndDate < MaxDateInContext ),EndDate>MinDateInContext),
               1,
                IF (
                    AND ( StartDate < MinDateInContext, EndDate > MaxDateInContext ),
                    1,
                    BLANK ()
                )
            )
        )
    )

Total Orders = SUMX('Tracking History',[No. of Orders])

Esto crea la siguiente visualización:

image.png

En Performance Analyzer, este visual tardó 595,908 milisegundos en renderizarse, o aproximadamente 9 minutos, 56 segundos.

Paso 1

OK, paso uno, limpiemos el código. IF anidados. ¡Ack! Cambiemos esos a una declaración SWITCH.

No. of Orders 2a = 
    VAR StartDate = VALUE ( SELECTEDVALUE ( 'Tracking History'[Start Date]) )
    VAR EndDate = VALUE ( SELECTEDVALUE ( 'Tracking History'[End Date] ) )
    VAR MinDateInContext = VALUE ( MIN ( 'DateTimeTable'[Date] ) )
    VAR MaxDateInContext = VALUE ( MAX ( DateTimeTable[Date] ) )
RETURN
	SWITCH(TRUE(),
		AND (  StartDate > MinDateInContext, EndDate < MaxDateInContext ),1,
		AND( AND (StartDate > MinDateInContext, EndDate > MaxDateInContext),MaxDateInContext > StartDate),1,
		AND( AND ( StartDate < MinDateInContext, EndDate < MaxDateInContext ),EndDate > MinDateInContext),1,
        AND ( StartDate < MinDateInContext, EndDate > MaxDateInContext ),1,
		BLANK()
    )

También probé esto usando && en lugar de AND así:

No. of Orders 2 = 
    VAR StartDate = VALUE ( SELECTEDVALUE ( 'Tracking History'[Start Date]) )
    VAR EndDate = VALUE ( SELECTEDVALUE ( 'Tracking History'[End Date] ) )
    VAR MinDateInContext = VALUE ( MIN ( 'DateTimeTable'[Date] ) )
    VAR MaxDateInContext = VALUE ( MAX ( DateTimeTable[Date] ) )
RETURN
	SWITCH(TRUE(),
		StartDate > MinDateInContext && EndDate < MaxDateInContext,1,
		(MaxDateInContext > StartDate && StartDate > MinDateInContext) && EndDate > MaxDateInContext,1,
        (StartDate < MinDateInContext && EndDate < MaxDateInContext) && EndDate > MinDateInContext,1,
		StartDate < MinDateInContext && EndDate > MaxDateInContext,1,
		BLANK()
	)

Los resultados del analizador de rendimiento fueron los siguientes:

  • No. de Órdenes 2a, Total de Órdenes 2a, 606,392 milisegundos o 10 minutos, 6 segundos
  • No. de pedidos 2, pedidos totales 2, 613,939 milisegundos o 10 minutos, 14 segundos

Por lo tanto, las primeras lecciones no tienen un impacto real en el rendimiento al usar SWITCH en lugar de declaraciones IF anidadas. Además, no hay impacto en el uso de funciones AND frente a && en línea. Si bien estos tiempos son un poco más largos, no son estadísticamente significativos, ya que la misma consulta se ejecutará un poco más o menos dependiendo de una variedad de factores. Pero, ¡el código es definitivamente más legible!

Paso 2

Ahora que podemos ver mejor la lógica involucrada, es evidente que la lógica está filtrando ciertas filas y asignando un valor de 1, mientras que el resto que no se ajusta a los criterios recibe un valor de EN BLANCO. Sin embargo, en el objeto visual, no se muestran más de 2000 como una suma de todos los 1 y hay 60 000 filas en la tabla de hechos. Esto significa que la gran mayoría de nuestras filas tienen que ser probadas en busca de un montón de condiciones antes de que «fallan» para que se les asigne EN BLANCO. Por lo tanto, se deduce que si invertimos la lógica eliminaremos filas más rápido y, por lo tanto, habrá menos pruebas involucradas y, por lo tanto, menos procesamiento y cálculo.

No. of Orders 3 = 
    VAR StartDate = VALUE ( SELECTEDVALUE ( 'Tracking History'[Start Date]) )
    VAR EndDate = VALUE ( SELECTEDVALUE ( 'Tracking History'[End Date] ) )
    VAR MinDateInContext = VALUE ( MIN ( 'DateTimeTable'[Date] ) )
    VAR MaxDateInContext = VALUE ( MAX ( DateTimeTable[Date] ) )
RETURN
	SWITCH(TRUE(),
		AND (  StartDate > MinDateInContext, EndDate > MaxDateInContext ),BLANK(),
		AND( AND (StartDate > MinDateInContext, EndDate > MaxDateInContext),MaxDateInContext < StartDate),BLANK(),
		AND( AND ( StartDate < MinDateInContext, EndDate < MaxDateInContext ),EndDate < MinDateInContext),BLANK(),
        AND ( StartDate < MinDateInContext, EndDate < MaxDateInContext ),BLANK(),
		1
    )

Los resultados del analizador de rendimiento fueron los siguientes:

  • No. de pedidos 3, pedidos totales 3, 285,969 milisegundos o 4 minutos, 46 segundos

¡Ajá! ¡Hemos reducido el tiempo de cálculo a la mitad! Entonces, lección aprendida, al realizar pruebas de lógica condicional, estructure su código de manera que elimine la mayor cantidad de filas lo antes posible.

Paso 3

Hasta ahora, he estado creando dos medidas para cada paso, creando una nueva medida Total de pedidos junto con mi nueva medida de Número de pedidos. Esto es un fastidio y soy vago, así que combinemos esto en una sola medida:

Total Orders 4 = 
    VAR MinDateInContext = VALUE ( MIN ( 'DateTimeTable'[Date] ) )
    VAR MaxDateInContext = VALUE ( MAX ( DateTimeTable[Date] ) )
    VAR __Table =
        ADDCOLUMNS(
            'Tracking History',
            "__No of Orders",
            SWITCH(TRUE(),
                AND (  
                    'Tracking History'[Start Date] > MinDateInContext, 
                    'Tracking History'[End Date] > MaxDateInContext
                ),BLANK(),
                AND( 
                    AND (
                        'Tracking History'[Start Date] > MinDateInContext, 
                        'Tracking History'[End Date] > MaxDateInContext
                    ),
                    MaxDateInContext < 'Tracking History'[Start Date]
                ),BLANK(),
                AND( 
                    AND ( 
                        'Tracking History'[Start Date] < MinDateInContext, 
                        'Tracking History'[End Date] < MaxDateInContext 
                    ),
                    'Tracking History'[End Date] < MinDateInContext
                ),BLANK(),
                AND ( 
                    'Tracking History'[Start Date] < MinDateInContext, 
                    'Tracking History'[End Date] < MaxDateInContext 
                ),BLANK(),
                1
            )
        )
RETURN
    SUMX(__Table,[__No of Orders])

Los resultados del analizador de rendimiento fueron los siguientes:

  • Total de pedidos 4, 57,010 milisegundos, 57 segundos

¡Guau! ¡Quizás inesperadamente este rendimiento realmente mejoró! ¿La lección aprendida aquí? ¿Ser perezoso es algo bueno? Difícil de decir, pero probablemente tenga algo que ver con la optimización interna de DAX. Tener todo el código en una sola medida ayuda a DAX a optimizar la consulta, por ejemplo.

Paso 4

Ahora que hemos invertido la lógica y tenemos todo esto en una sola medida, podemos ver claramente que podríamos mover la primera prueba lógica a un FILTRO de la tabla. Así que prefiltre la tabla para que no tengamos que hacer la primera prueba lógica. Veamos qué hace eso:

Total Orders 5 = 
    VAR MinDateInContext = VALUE ( MIN ( 'DateTimeTable'[Date] ) )
    VAR MaxDateInContext = VALUE ( MAX ( DateTimeTable[Date] ) )
    VAR __Table =
        ADDCOLUMNS(
            FILTER(
                'Tracking History',
                OR (  
                    'Tracking History'[Start Date] > MinDateInContext, 
                    'Tracking History'[End Date] > MaxDateInContext
                )
            ),
            "__No of Orders",
            SWITCH(TRUE(),
                AND( 
                    AND (
                        'Tracking History'[Start Date] > MinDateInContext, 
                        'Tracking History'[End Date] > MaxDateInContext
                    ),
                    MaxDateInContext < 'Tracking History'[Start Date]
                ),BLANK(),
                AND( 
                    AND ( 
                        'Tracking History'[Start Date] < MinDateInContext, 
                        'Tracking History'[End Date] < MaxDateInContext 
                    ),
                    'Tracking History'[End Date] < MinDateInContext
                ),BLANK(),
                AND ( 
                    'Tracking History'[Start Date] < MinDateInContext, 
                    'Tracking History'[End Date] < MaxDateInContext 
                ),BLANK(),
                1
            )
        )
RETURN
    SUMX(__Table,[__No of Orders])

Los resultados del analizador de rendimiento fueron los siguientes:

  • Órdenes totales 5, 44,158 milisegundos, 44 segundos

De acuerdo, no es una mejora tan dramática, pero aún así eliminó aproximadamente un 25% del tiempo de cálculo. Lección aprendida, ¡filtre temprano!

Paso 5

Mirando de cerca nuestra lógica, las dos últimas pruebas son redundantes. Podemos deshacernos de la prueba redundante.

Total Orders 6 = 
    VAR MinDateInContext = VALUE ( MIN ( 'DateTimeTable'[Date] ) )
    VAR MaxDateInContext = VALUE ( MAX ( DateTimeTable[Date] ) )
    VAR __Table =
        ADDCOLUMNS(
            FILTER(
                'Tracking History',
                OR (  
                    'Tracking History'[Start Date] > MinDateInContext, 
                    'Tracking History'[End Date] > MaxDateInContext
                )
            ),
            "__No of Orders",
            SWITCH(TRUE(),
                AND( 
                    AND (
                        'Tracking History'[Start Date] > MinDateInContext, 
                        'Tracking History'[End Date] > MaxDateInContext
                    ),
                    MaxDateInContext < 'Tracking History'[Start Date]
                ),BLANK(),
                AND ( 
                    'Tracking History'[Start Date] < MinDateInContext, 
                    'Tracking History'[End Date] < MaxDateInContext 
                ),BLANK(),
                1
            )
        )
RETURN
    SUMX(__Table,[__No of Orders])

Los resultados del analizador de rendimiento fueron los siguientes:

  • Órdenes totales 6, 43,844 milisegundos, 44 segundos

No, no hay una mejora real (probablemente porque DAX ya optimizó esta redundancia). Pero, el código es más corto y más limpio, ¡así que es una victoria!

Paso 6

Esas declaraciones de VALOR parecen innecesarias, eliminémoslas.

Total Orders 7 = 
    VAR MinDateInContext = MIN ( 'DateTimeTable'[Date] )
    VAR MaxDateInContext = MAX ( DateTimeTable[Date] )
    VAR __Table =
        ADDCOLUMNS(
            FILTER(
                'Tracking History',
                OR (  
                    'Tracking History'[Start Date] > MinDateInContext, 
                    'Tracking History'[End Date] > MaxDateInContext
                )
            ),
            "__No of Orders",
            SWITCH(TRUE(),
                AND( 
                    AND (
                        'Tracking History'[Start Date] > MinDateInContext, 
                        'Tracking History'[End Date] > MaxDateInContext
                    ),
                    MaxDateInContext < 'Tracking History'[Start Date]
                ),BLANK(),
                AND ( 
                    'Tracking History'[Start Date] < MinDateInContext, 
                    'Tracking History'[End Date] < MaxDateInContext 
                ),BLANK(),
                1
            )
        )
RETURN
    SUMX(__Table,[__No of Orders])

Los resultados del analizador de rendimiento fueron los siguientes:

  • Total de pedidos 7, 32,109 milisegundos, 32 segundos

Vaya, otra reducción del 25%. Lección aprendida, ¡deje de usar VALOR y VALORES innecesariamente! Veo mucho de eso en los foros. ¡Te está costando rendimiento! Solo use esas funciones si realmente lo necesita.

Paso 7

Al mirar la lógica una vez más y considerar nuestro prefiltrado, esa primera prueba lógica es demasiado complicada. Simplifiquémoslo.

Total Orders 8 = 
    VAR MinDateInContext = MIN ( 'DateTimeTable'[Date] )
    VAR MaxDateInContext = MAX ( DateTimeTable[Date] )
    VAR __Table =
        ADDCOLUMNS(
            FILTER(
                'Tracking History',
                OR (  
                    'Tracking History'[Start Date] > MinDateInContext, 
                    'Tracking History'[End Date] > MaxDateInContext
                )
            ),
            "__No of Orders",
            SWITCH(TRUE(),
                MaxDateInContext < 'Tracking History'[Start Date],
                BLANK(),
                AND ( 
                    'Tracking History'[Start Date] < MinDateInContext, 
                    'Tracking History'[End Date] < MaxDateInContext 
                ),BLANK(),
                1
            )
        )
RETURN
    SUMX(__Table,[__No of Orders])

Los resultados del analizador de rendimiento fueron los siguientes:

  • Total de pedidos 8, 24,208 milisegundos, 24 segundos

¡Oye! Otra mejora del 33%. Lección aprendida, ¡la lógica más simple es mejor!

Paso 8

Funcionó antes, ¿qué pasa si movemos la prueba lógica a la cláusula de filtro?

Total Orders 9 = 
    VAR MinDateInContext = MIN ( 'DateTimeTable'[Date] )
    VAR MaxDateInContext = MAX ( DateTimeTable[Date] )
    VAR __Table =
        ADDCOLUMNS(
            FILTER(
                'Tracking History',
                AND (
                    OR (  
                        'Tracking History'[Start Date] > MinDateInContext, 
                        'Tracking History'[End Date] > MaxDateInContext
                    ),
                    MaxDateInContext > 'Tracking History'[Start Date]
                )
            ),
            "__No of Orders",
            SWITCH(TRUE(),
                AND ( 
                    'Tracking History'[Start Date] < MinDateInContext, 
                    'Tracking History'[End Date] < MaxDateInContext 
                ),BLANK(),
                1
            )
        )
RETURN
    SUMX(__Table,[__No of Orders])

Los resultados del analizador de rendimiento fueron los siguientes:

  • Total de pedidos 9, 22,467 milisegundos, 22 segundos

De acuerdo, tal vez una pequeña mejora en el rendimiento, ¡no pareció dañar nada y nuestro tiempo más rápido todavía!

Conclusión

El ajuste del rendimiento de DAX puede tener resultados espectaculares. En este caso, código que se ejecuta 30 veces más rápido que el original. Para lograr este tipo de mejoras, preste atención a lo siguiente:

  • Limpie su código, es posible que no mejore el rendimiento directamente, pero lo ayudará a comprender mejor lo que está haciendo y a encontrar optimizaciones más fácilmente.
  • Considere estructurar su lógica condicional para eliminar la mayor cantidad de filas lo antes posible. A veces, esto significa invertir su lógica.
  • Pon todo tu código en una sola medida. Este no estoy al 100%, pero en este caso, la consolidación del código tuvo el mayor impacto quizás debido a la optimización interna de DAX. También es posible que deshacerse del cálculo de las dos variables o alguna otra optimización en el uso de ADDCOLUMNS ayudó aquí.
  • Filtre sus datos tanto como sea posible antes de comenzar a realizar pruebas lógicas
  • Simplifica tu lógica
  • Deje de usar VALOR y VALORES si no tiene que usarlos

¡Estén atentos a la Parte 2, donde cubriré más optimizaciones y discutiré los peligros de la optimización excesiva!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *