Optimización de PERCENTILEX

[AlB]

Hola a todos

Muestra pbix adjunta.

Tenemos un modelo de una tabla muy simplificado (véase el cuadro 1 a continuación) y dos medidas simples:

Total revenue = SUM(Table1[Revenue])

PercentileM = 
PERCENTILEX.INC(ALL(Table1[Client_code]), [Total revenue], .25)

Colocamos Client_code y [PercentileM] en un objeto visual de tabla. Anote la definición de Table1 en el archivo. El número de clientes se puede cambiar en el código, VAR numClients_

Si el número de clientes es 50K, el objeto visual tarda unos 10 segundos en actualizarse. Si son 100K, sube a casi 40 segundos. Si es 1M, explota, toma más de una hora y finalmente se estrella.

2 preguntas

1. ¿Hay alguna manera de optimizar esto, evitar que tome tanto tiempo para N grande? Consulte la página 1 en el archivo adjunto

2. (Vea la página 2 en el archivo adjunto) Puesto que el valor proporcionado por [PercentileM] es realmente el mismo para cada fila, estamos siendo increíblemente ineficientes al hacer que se ejecute para cada una de las filas N en el objeto visual. De hecho, el resultado que buscamos es lo que obtenemos si colocamos [PercentileM] en un objeto visual de tarjeta, que es muy rápido. ¿Hay alguna manera de obtener el resultado del objeto visual de la tarjeta y colocarlo en cada fila del objeto visual de la tabla? ¿Así que [PercentileM] se ejecuta sólo una vez? Para aclarar lo que queremos decir, podríamos crear una tabla calculada de una fila y una columna MeasureResult con ese resultado:

MeasureResult = {[PercentileM]}

y luego una medida que sólo lee ese resultado estático que se usará en el objeto visual de la tabla. Vea esto en la página 2 del archivo adjunto

ReadFromTable = MAX(MeasureResult[Value])

Sin embargo, se trata de una solución inflexible, ya que la tabla calculada es estática y el usuario no pudo aplicar filtros, por ejemplo.

Muchas gracias

@MFelix @Zubair_Muhammad @mahoneypat @TomMartens @GilbertQ @camargos88 @OwenAuger

Tabla 1

Client_code	Ingresos
1	1
1	2
1	3
1	4
2	1
2	2
2	3
2	4
3	1
3	2
3	3
3	4
…	…
N	1
N	2
N	3
N	4

[mahoneypat · Accepted solution]

@AlB Este es un enfoque que te acerca y podría perfeccionarse aún más para hacerlo bien. Agregué un número aleatorio a su tabla original para probarlo (así que no los totales eran los mismos para todos los clientes) como este

Tabla1 ?

Fue numClients_ 1000000 //Cambiar aquí el número de clientes

Clientes de Fue5rows ?

SELECTCOLUMNS (

CROSSJOIN (

SELECTCOLUMNS ( GENERATESERIES ( 1, numClients_ ), "Client_code", [Valor] ),

GENERATESERIES ( 1, 5 )

),

"Client_code", [Client_code],

"Ingresos", CONVERT([Valor], DOBLE)

)

var withrandom á ADDCOLUMNS(clients5rows, "number", RANDBETWEEN(1,10000))

volver con la renatura

A continuación, se realizó una medida que crea la tabla virtual, busca el rango, lo usa para encontrar el rango en un 25% en función de la clase de filas/clientes y, a continuación, filtra hasta la suma de esa fila (o cerca de ella). Podría refinarlo para encontrar los valores por encima y por debajo de ese nivel e interpolar como hace PERCENTILEX. Para los clientes de 1M, esto se ejecutó en 3.5 s en mi máquina.

Percentil M3 ?
Fue vSummary =
ADDCOLUMNS (
todo ( Tabla1[Client_code] ),
"cSum",
Calcular (
Suma ( Tabla1[número] )
)
)
Fue vAddRank =
ADDCOLUMNS (
vSummary,
"CRank",
Fue vThisValue • [cSum]
devolución
RANKX (
vSummary,
[cSum],
vThisValue,
Asc
)
)
Fue v25rank =
0.25
* COUNTROWS ( vSummary )
devolución
Minx (
Filtro (
vAddRank,
[cRank] > v25rank
),
[cSum]
)

saludos

palmadita

[AlB]

@TomMartens

Gran. Muchas gracias. Lo miraré en los días siguientes. Podría volver con algunas preguntas más

Gracias

Por favor, marque la pregunta resuelta cuando haya terminado y considere dar un pulgar hacia arriba si las publicaciones son útiles.

Póngase en contacto conmigo de forma privada para obtener asistencia con cualquier necesidad de BI a gran escala, tutoría, etc.

Salud

[AlB]

@TomMartens

Muchas gracias por su respuesta. Esto se ve GRAN,muy interesante. Acabo de ver que los resultados que produce su medida no son exactamente los mismos que lo que PERCENTILEX. Rendimientos de INC. Sin embargo, creo que es simplemente la interpolación y probablemente no tomará mucho para arreglar. Unas preguntas. Sé que estás de vacaciones. Sin prisas 😊

1. No he trabajado con objetos PATH. Haré mi propia investigación, pero ¿le importaría explicar, de alto nivel, la lógica detrás del enfoque?

2. ¿Cómo se crea la mesa de Client_code dedicada? ¿Es sólo una agregación, con una fila por cliente y sus ingresos totales?

3. "la medida se beneficiará de la propagación del filtro en lugar de escanear permanentemente la tabla" ¿Qué quieres decir exactamente con esto?

4. "Con respecto a su 2a pregunta, tal vez existe la posibilidad de crear una tabla que solo contenga una sola fila usando GROUPBY o SUMMARIZE. A continuación, esta tabla se calculará solo una vez durante la actualización de datos" ¿Es esto diferente de lo que propuse inicialmente en la pregunta 2, con la tabla MeasureResult y [ReadFromTable]? Si no, todavía tendríamos el problema de que sea una solución estática, no responder a los filtros

5. "Puede utilizar una medida más simple para combinar el valor PERCENTILE en su tabla de hechos" ¿Puedes elaborarlo?

Muchas gracias y una feliz Navidad para ti también

Por favor, marque la pregunta resuelta cuando haya terminado y considere dar un pulgar hacia arriba si las publicaciones son útiles.

Póngase en contacto conmigo de forma privada para obtener asistencia con cualquier necesidad de BI a gran escala, tutoría, etc.

Salud

[TomMartens]

Hola @AlB ,

sus preguntas ...

1 - el objeto de ruta

Básicamente es una cadena (no tengas miedo, esta cadena puede llegar a ser muy larga), lo que hace que esta cadena sea especial, es el signo de la cadena que separa los elementos. Hay algunas funciones que son muy eficientes como PATHITEM (devolver el elemento de una ruta de acceso en una posición determinada. El valor en la posición 25 se puede determinar así
PATHITEM( ruta, 25)

Desde mi experiencia, navegar por una cadena que contiene el signo de -como carácter especial (este carácter se utiliza b todo el PATH... funciones) es muy rápido.

Tal vez usted encontrará este blog interesante: El valor anterior - Mincing Data - Obtener información de datos (minceddata.info)

Yo creo un path-objeto usando la función de iterador de tabla CONCATENATEX(...) En nuestro ejemplo estoy usando los clientes como tabla y SalesValue como expresión, también uso los parámetros de orden por y dirección de pedido de la función. Cuando termine p contiene 1M valores de ventas ordenados.

2 - la tabla de clientes
Utilizo este código DAX para crear una tabla client_code de Table1:

Client_code = DISTINCT( ALLNOBLANKROW( 'Table1'[Client_code] ) ) 

El uso de una tabla dedicada me permite crear una tabla de dimensiones (en un lado) y una tabla de hechos (en muchos lados) proporciona el uso de la propagación del filtro.

3 - propagación del filtro

La propagación de filtros es uno de los principios principales del conjunto de datos de Power BI (básicamente, este concepto ordena todo lo tabular). A veces, la propagación de filtros es por medios más rápido que escanear una sola tabla. El escaneo se vuelve necesario ya que hay varios valores por client_code.

4 - RESUMEN o GROUPBY

Básicamente no es tan diferente de una tabla de resultados de medida, pero no entendí cómo quería crear la tabla de resultados de la medida, lo siento por la confusión. Sí, esta solución será estática.

5 - utilizando una medida simplificada

Tan pronto como hay una tabla de resultados una fila una columna, la celda, se puede mezclar en la tabla de hechos Table1 por una medida simple como MAX(...) en lugar de evaluar la medida compleja 1M veces.

Con suerte, esto responde a algunas de sus preguntas. Por cierto, las vacaciones son puntos de vista ideales para puestos más largos.

Manténgase seguro, manténgase saludable, tenga datos y una Feliz Navidad

Tom

[TomMartens · Accepted solution]

Hey @AlB , Hey @mahoneypat ,

nos enfrentamos a dos problemas

• la falta de estructuras de datos ordenadas (necesarias para PERCENTILE... RANKX, y muchas cosas más)
• una medida se evaluará dentro de un contexto de filtro (1M filas, 1M contextos de filtro). Este es un principio dominante, para esto, esto no se puede cambiar.

Para superar el primer problema, comencé a usar un objeto PATH. Mi medida contiene esta línea:

var p = CONCATENATEX( ALLSELECTED( 'Table1'[Client_code] ) , [Total revenue] , "|" , [Total revenue] , ASC )

p contiene una estructura ordenada que se puede utilizar con todas las funciones PATH, como PATHLENGTH o PATHITEM.

La medida completa tiene este aspecto:

percentile with path and Client_code = 
var __Percentile = 0.25
var p = CONCATENATEX( ALLSELECTED( 'Client_code'[Client_code] ) , [Total revenue] , "|" , [Total revenue] , ASC )
var position = PATHLENGTH( p ) * __Percentile
var valueAtPosition = VALUE( PATHITEM( p , position , TEXT ) )
return
valueAtPosition

Como puede ver, también estoy usando una tabla de Client_code dedicada. Recomiendo usar una tabla dedicada. Hay más de una fila por cliente, por esta razón la medida se beneficiará de la propagación del filtro en lugar de examinar permanentemente la tabla.

La medida anterior excuta en 3,2 s en mi máquina (Intel i7-9750H). Considero que esto no es una ganancia sustancial sobre la solución @mahoneypat significado proporcionado, tal vez tenemos que hacer frente al hecho de que ya hemos alcanzado el 🙂 óptimo

Con respecto a su 2a pregunta, tal vez existe la posibilidad de crear una tabla que solo contenga una sola fila usando GROUPBY o SUMMARIZE. A continuación, esta tabla se calculará solo una vez durante la actualización de datos. Puede utilizar una medida más simple para combinar el valor PERCENTILE en la tabla de hechos.

Manténgase seguro, manténgase saludable, tenga datos (y por supuesto una feliz Navidad)

Tom

[TomMartens]

Hey @AlB , Hey @mahoneypat

a lo que nos enfrentamos es la falta de estructuras ordenadas que contengan datos ordenados.

Por esta razón estoy creando estructuras ordenadas usando el path objeto

var p = CONCATENATEX( ALLSELECTED( Client_code[Client_code] ) , [Total revenue] , "|" , [Total revenue] , ASC )

la línea anterior es parte de esta medida

percentile with path and client table = 
var __Percentile = 0.25
var p = CONCATENATEX( ALLSELECTED( Client_code[Client_code] ) , [Total revenue] , "|" , [Total revenue] , ASC )
var position = PATHLENGTH( p ) * __Percentile
var valueAtPosition = VALUE( PATHITEM( p , position , TEXT) )
return
valueAtPosition

Con los clientes de 1M, mi máquina (Intel Core i7-9750H) tarda 3,2 s en crear esta tabla visual

[mahoneypat]

Estoy seguro de que podría hacer lo mismo en R o Python y también sería rápido. No creo que Charticulator se aplicaría aquí, pero es una idea interesante para poder editar el MDX detrás de una tabla / matriz visual directamente. Deberías proponerlo.

saludos

palmadita

saludos

palmadita

[AlB]

@mahoneypat @TomMartens (y todos los demás)

Pat, gracias por crear la idea de las variables de nivel visual.

Estaba echando un vistazo más a la pregunta 2. Si tenemos un objeto visual de tabla simple con Table1[Client_code] y esta medida que utiliza el PERCENTILEX integrado. Inc:

PercentileM_INC with NumberCol = 
VAR wantedPerc_ = [Wanted_perc]
RETURN
PERCENTILEX.INC(ALL(Table1[Client_code]), CALCULATE(SUM(Table1[number])), wantedPerc_)    

donde [Wanted_perc] es simplemente una constante, entonces podemos obtener la consulta para este objeto visual desde el Analizador de rendimiento:

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Table1'[Client_code], "IsGrandTotalRowTotal"),
      "PercentileM_INC_with_NumberCol", 'Table1'[PercentileM_INC with NumberCol]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Table1'[Client_code], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Table1'[Client_code]

Si ejecutamos esto en DAX Studio y N (número de clientes) es 1M, tarda más de una hora y se bloquea, como se indicó anteriormente. Sin embargo, podemos modificar la consulta para aplicar esa variable de nivel visual:

// DAX Query
DEFINE
    VAR valForAllRows_ = 'Table1'[PercentileM_INC with NumberCol] //Executed only ONCE for the visual 

    VAR __DS0Core_V2 = //This is the more efficient variation
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL ( 'Table1'[Client_code], "IsGrandTotalRowTotal" ),
            "PercentileM_INC_with_NumberCol", valForAllRows_ //Using the variable  here
        )

    VAR __DS0PrimaryWindowed =
        TOPN (
            502,
            __DS0Core_V2, //CHANGE here to swap between versions
            [IsGrandTotalRowTotal], 0,
            'Table1'[Client_code], 1
        )
EVALUATE
__DS0PrimaryWindowed
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'Table1'[Client_code]

lo anterior se ejecuta en 2 segundos

Me preguntaba si algo como esto se puede implementar en un objeto visual personalizado, donde se puede determinar cómo se ejecuta el objeto visual internamente?? ¿Quizás con Charticulator o con un objeto visual Python?

¿Pensamientos?

Gracias

Por favor, marque la pregunta resuelta cuando haya terminado y considere dar un pulgar hacia arriba si las publicaciones son útiles.

Póngase en contacto conmigo de forma privada para obtener asistencia con cualquier necesidad de BI a gran escala, tutoría, etc.

Salud

[mahoneypat]

@AlB Me alegro de que haya ayudado. Me encanta un buen desafío DAX o M, y este fue divertido. La mayoría de las veces no necesitamos usar el término de valor de RANKX, pero es útil. Gracias por compartir su expresión para ver las mejoras que ha realizado. Si pudiéramos hacer funciones personalizadas como en M, esto sería una buena opción para mantener a mano.

También destaca el potencial de tener medidas de nivel visual a las que se podría hacer referencia en cada fila, lo que haría una mejora mucho mayor todavía. Agregué esa idea aquí.

saludos

palmadita

[AlB]

@TomMartens

Gracias. Espero con ansias su respuesta

@mahoneypat

¡Eso es genial! Muchas gracias. La mejora es masiva. Pensé en algo así inicialmente, pero asumí (equivocadamente, claramente) que la implementación incorporada de PERCENTILEX ya estaría (cuasi)optimizada. Estoy realmente, muy sorprendido de que su rendimiento es tan pobre en comparación con su solución 🤔

Hice un par de cambios para tener en cuenta la parte de interpolación. No es difícil, pero me tomó un tiempo encontrar en línea una definición clara del algoritmo exacto uso EXCEL / DAX. Esta es una primera versión:

PercentileINC M3 (PatMahoney's) V3 = //Added interpolation 
VAR wantedPerc_ = [Wanted_perc]
VAR baseT_ = ALL(Table1[Client_code])
VAR vSummary = ADDCOLUMNS ( baseT_, "cSum", CALCULATE ( SUM ( Table1[number] ) ) )
VAR vAddRank = ADDCOLUMNS ( vSummary, "cRank", RANKX (vSummary, [cSum],,ASC,Skip))
VAR percentileRank_ = (wantedPerc_ * (COUNTROWS(baseT_) - 1)) + 1 //Difference with .EXC
VAR percRankFloor_ = FLOOR(percentileRank_,1)
VAR percRankCeiling_ = CEILING(percentileRank_,1)
VAR ranksT_ = TOPN(percRankCeiling_-percRankFloor_+1, FILTER(vAddRank, [cRank]<=percRankCeiling_), [cRank], DESC) 
VAR val1_ = MINX(ranksT_,[cSum])
VAR val2_ = MAXX(ranksT_,[cSum])
VAR interpolated_ = val1_ + ((val2_ - val1_) * (percentileRank_ - percRankFloor_))
RETURN
interpolated_

Ejecuta aprox. al mismo tiempo que su versión; por lo que la adición de la interpolación no tiene ningún impacto

Y otra versión, un 20% más rápida que la anterior:

PercentileINC M3 (PatMahoney's) V4 = //Can it run faster than V3??
VAR wantedPerc_ = [Wanted_perc]
VAR baseT_ = ALL(Table1[Client_code])
VAR vAddRank =    ADDCOLUMNS ( baseT_, "cRank", RANKX (baseT_, CALCULATE (SUM ( Table1[number] )),,ASC,Skip))
VAR percentileRank_ = (wantedPerc_ * (COUNTROWS(baseT_) - 1)) + 1 //Difference with .EXC
VAR percRankFloor_ = FLOOR(percentileRank_,1)
VAR percRankCeiling_ = CEILING(percentileRank_,1)
VAR valsT_ = TOPN(percRankCeiling_-percRankFloor_+1, FILTER(vAddRank, [cRank]<=percRankCeiling_), [cRank], DESC) 
VAR auxT_ = ADDCOLUMNS(valsT_, "@Value", CALCULATE(SUM(Table1[number]))) 
VAR val1_ = MINX(auxT_,[@Value]) 
VAR val2_ = MAXX(auxT_,[@Value])
VAR interpolated_ = val1_ + ((val2_ - val1_) * (percentileRank_ - percRankFloor_))
RETURN
interpolated_

Definitivamente señalaré su respuesta como solución, pero quiero dejar esto todavía abierto a discusión sobre la pregunta 2.

Por cierto, ¿sabe si es posible en DAX Studio ejecutar, por ejemplo, una medida varias veces y obtener el promedio del tiempo de ejecución? Es decir, en lugar de ejecutar la consulta manualmente 10 veces, comprobar los tiempos y extraer el promedio manualmente, hacer que DAX Studio lo haga automáticamente. Creo que había leído en algún lugar que era posible, pero no puedo encontrar donde. Gracias

Por favor, marque la pregunta resuelta cuando haya terminado y considere dar un pulgar hacia arriba si las publicaciones son útiles.

Póngase en contacto conmigo de forma privada para obtener asistencia con cualquier necesidad de BI a gran escala, tutoría, etc.

Salud

[TomMartens]

Hola AIB,

Voy a investigar su pregunta en los próximos días libres, por lo que puede tomar un tiempo 🙂

saludos

Tom

[mahoneypat · Accepted solution]

@AlB Este es un enfoque que te acerca y podría perfeccionarse aún más para hacerlo bien. Agregué un número aleatorio a su tabla original para probarlo (así que no los totales eran los mismos para todos los clientes) como este

Tabla1 ?

Fue numClients_ 1000000 //Cambiar aquí el número de clientes

Clientes de Fue5rows ?

SELECTCOLUMNS (

CROSSJOIN (

SELECTCOLUMNS ( GENERATESERIES ( 1, numClients_ ), "Client_code", [Valor] ),

GENERATESERIES ( 1, 5 )

),

"Client_code", [Client_code],

"Ingresos", CONVERT([Valor], DOBLE)

)

var withrandom á ADDCOLUMNS(clients5rows, "number", RANDBETWEEN(1,10000))

volver con la renatura

A continuación, se realizó una medida que crea la tabla virtual, busca el rango, lo usa para encontrar el rango en un 25% en función de la clase de filas/clientes y, a continuación, filtra hasta la suma de esa fila (o cerca de ella). Podría refinarlo para encontrar los valores por encima y por debajo de ese nivel e interpolar como hace PERCENTILEX. Para los clientes de 1M, esto se ejecutó en 3.5 s en mi máquina.

Percentil M3 ?
Fue vSummary =
ADDCOLUMNS (
todo ( Tabla1[Client_code] ),
"cSum",
Calcular (
Suma ( Tabla1[número] )
)
)
Fue vAddRank =
ADDCOLUMNS (
vSummary,
"CRank",
Fue vThisValue • [cSum]
devolución
RANKX (
vSummary,
[cSum],
vThisValue,
Asc
)
)
Fue v25rank =
0.25
* COUNTROWS ( vSummary )
devolución
Minx (
Filtro (
vAddRank,
[cRank] > v25rank
),
[cSum]
)

saludos

palmadita

[mahoneypat]

Lo pensaré, pero lo dudo. Buen trabajo en compartirlo de una manera inteligente que es fácilmente escalable para probar soluciones potenciales. Esto realmente pone de relieve la necesidad de variables de nivel visual y/o de página. No estoy seguro de si esa idea ya está sugerida o no, pero deberías si no.

saludos

palmadita

[V-pazhen-msft]

@AlB

Pensamientos interesantes. Ha creado una columna [Value] basada en la medida PercentileM, pero supongo que no puede evitar el cálculo de la medida una vez que agregue el código de cliente en el objeto visual.

saludos
Paul