Mejora del rendimiento del motor de fórmula

Un usuario Pregunto ✅

charliedata

Tengo una consulta que tarda 55 segundos para el FE y 1 para el SE

DAX Studio obviamente me da los tiempos de SQL y SE, pero tampoco veo cómo

a) ver lo que está tardando 55 segundos en la FE

b) optimizar el rendimiento de FE

La consulta solo incluye 1 columna calculada (básicamente: si x está en blanco, «no», «sí») y 1 medida (suma de ventas). Los otros campos son directamente del dwh.

¿Hay alguna forma de mejorar el rendimiento de EF?

Greg_Deckler

¿Ha jugado con las opciones de diagnóstico de Power Query (pestaña Herramientas) o DAX Performance Analyzer (pestaña Ver)?

charliedata

En respuesta a Greg_Deckler

Hola @Greg_Deckler, gracias de nuevo por tu ayuda

He usado DAX PA para generar datos de rendimiento para exportarlos a DAX Studio, y he estado ejecutando consultas allí, siguiendo el enfoque de comentar campos para ver qué está causando problemas.

No he utilizado el rastreo de diagnóstico de PQ; necesitará obtener más información al respecto. ¿Ayuda a la optimización de consultas?

Greg_Deckler

En respuesta a charliedata

Sí, proporciona un desglose de básicamente cada paso de la consulta y cuánto tiempo lleva.

Greg_Deckler

En respuesta a charliedata

Sí, proporciona un desglose de básicamente cada paso de su consulta y cuánto tiempo está tardando.

mahoneypat

En respuesta a charliedata

Los diagnósticos de consultas lo ayudarán a solucionar problemas de actualización de consultas de energía, pero no a sus expresiones DAX. ¿Puede publicar su expresión DAX para que podamos proponer cambios de optimización?

Saludos,

Palmadita

charliedata

En respuesta a mahoneypat

Gracias @mahoneypat: aquí está el DAX que tarda 56 segundos en ejecutarse

DEFINE
  VAR __DS0FilterTable = 
    TREATAS(
      {"2020 Q1",
        "2020 Q2",
        "2020 Q3",
        "2020 Q4"},
      'bonus_region_sales_target'[remuneration_period]
    )

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('bonus_applications_details'[is_eligible_for_bonus])),
      NOT('bonus_applications_details'[is_eligible_for_bonus] IN {0,
        BLANK()})
    )

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'Application Received Date'[Application Recieved Date],
          'bonus_applications_details'[application_code],
          'bonus_applications_details'[fundraise_name],
          'bonus_applications_details'[investor_name],
          'adviser_contacts'[adviser_code],
          'adviser_contacts'[adviser_name],
          'current_intermediaries'[intermediary_code],
          'current_intermediaries'[company_name],
          'bonus_applications_details'[amount_on_application],
          'bonus_applications_details'[pending_payment_amount],
          'bonus_applications_details'[imputed_percentage],
          'dbt_marts oisales__bonus_accelerators'[activation_type],
          'bonus_applications_details'[sales_region],
          'bonus_applications_details'[remuneration_period],
          'bonus_applications_details'[investor_code],
          'bonus_applications_details'[is_cleared],
          'bonus_bdm_sales_target'[employee_name]
        ), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      "Amount", 'bonus_applications_details'[Amount]
    ),
    [IsGrandTotalRowTotal],
    0,
    'Application Received Date'[Application Recieved Date],
    1,
    'bonus_applications_details'[application_code],
    1,
    'bonus_applications_details'[fundraise_name],
    1,
    'bonus_applications_details'[investor_name],
    1,
    'adviser_contacts'[adviser_code],
    1,
    'adviser_contacts'[adviser_name],
    1,
    'current_intermediaries'[intermediary_code],
    1,
    'current_intermediaries'[company_name],
    1,
    'bonus_applications_details'[amount_on_application],
    1,
    'bonus_applications_details'[pending_payment_amount],
    1,
    'bonus_applications_details'[imputed_percentage],
    1,
    'dbt_marts oisales__bonus_accelerators'[activation_type],
    1,
    'bonus_applications_details'[sales_region],
    1,
    'bonus_applications_details'[remuneration_period],
    1,
    'bonus_applications_details'[investor_code],
    1,
    'bonus_applications_details'[is_cleared],
    1,
    'bonus_bdm_sales_target'[employee_name],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'Application Received Date'[Application Recieved Date],
  'bonus_applications_details'[application_code]/*,
  'bonus_applications_details'[fundraise_name],
  'bonus_applications_details'[investor_name],
  'adviser_contacts'[adviser_code],
  'adviser_contacts'[adviser_name],
  'current_intermediaries'[intermediary_code],
  'current_intermediaries'[company_name],
  'bonus_applications_details'[amount_on_application],
  'bonus_applications_details'[pending_payment_amount],
  'bonus_applications_details'[imputed_percentage],
  'dbt_marts oisales__bonus_accelerators'[activation_type],
  'bonus_applications_details'[sales_region],
  'bonus_applications_details'[remuneration_period],
  'bonus_applications_details'[investor_code],
  'bonus_applications_details'[is_cleared],
  'bonus_bdm_sales_target'[employee_name]*/

charliedata

En respuesta a charliedata

Estoy quitando el DAX para ver cómo se traduce a SQL y puedo ver que incluso si solo estoy consultando dos columnas (fecha y monto), el SQL generado está consultando cada campo en la tabla de hechos

No veo una razón para consultar campos que no son obligatorios. ¿Esto indica algo configurado incorrectamente en Power BI o Synapse?

sql.PNG

Greg_Deckler

En respuesta a charliedata

@charliedata Quizás deberíamos retroceder. ¿Cómo se ven sus datos de origen y qué está tratando de lograr? Siento que estamos tratando de sacar agua cuando podríamos simplemente tapar el agujero …

Consulte esta publicación sobre cómo obtener una respuesta rápida a su pregunta: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Las partes más importantes son:
1. Muestra datos como texto, usa la herramienta de tabla en la barra de edición
2. Resultado esperado de los datos de muestra
3. Explicación en palabras de cómo pasar del 1. al 2.

charliedata

En respuesta a Greg_Deckler

@Greg_Deckler

He aquí una oportunidad para hacer la pregunta de una mejor manera

Estoy usando un modelo compuesto, con la tabla de hechos principal en DirectQuery y otras tablas (por ejemplo, fecha) en Importar. Aquí está el modelo:

model.PNG

Tiene alrededor de 250k filas y alrededor de 40 columnas, de las cuales guardamos alrededor de 30 y luego hacemos algunas medidas en la parte superior.

Debido a que es DQ, no estoy seguro de que extraer una muestra, convertir para importar y compartir replicaría el mismo rendimiento que vemos aquí, ¿o no?

La tabla de hechos contiene ventas con datos sobre bonificaciones: ID de venta, información del cliente, información del empleado, información regional, fechas múltiples, cantidades, códigos de bonificación (por ejemplo, ID de multiplicador de bonificación).

Las otras tablas son principalmente tablas de fechas (aún no hemos implementado dimensiones de juego de roles) y tablas de bonificación (multiplicadores y objetivos).

Lo que estoy tratando de resolver aquí es mostrar las ventas («aplicaciones») que no se han procesado («liquidado») y calcular el multiplicador de bonificación para ellas mirando un par de otras tablas con valores de multiplicador de bonificación.

P.ej

sale_id período de bonificación nombre de empleado cliente Campaña región monto en ventas elegibilidad de bonificación multiplicador de bonificación cantidad de bonificación

He puesto en rojo los campos que están causando la mayoría de los problemas:

  • nombre del empleado: es solo un campo en la tabla de origen, pero eliminarlo de la imagen reduce el tiempo de consulta de 55 segundos a 7 segundos
  • multiplicador de bonificación (medida): son un par de búsquedas en otras tablas en el modelo de datos (DAX a continuación) pero me quedo sin recursos disponibles mientras actualizo el visual
  • cantidad de bonificación (medida): producto muy simple de cantidad de venta y multiplicador de bonificación, pero falla según el multiplicador

El monto de la venta también es una medida:

Cantidad = SUM (bonus_applications_details[amount])

… para poder hacer informes agregados en otras tablas

Este es el multiplicador de bonificación que está causando el mayor problema:

Multiplicador de bonificación =

var predeterminado = CALCULAR (
MAX (‘dbt_marts sales__bonus_multipliers'[bonus_multiplier]),
FILTRAR(
TODOS (‘dbt_marts sales__bonus_multipliers'[id]),
‘dbt_marts sales__bonus_multipliers'[id] = SELECTEDVALUE (bonus_applications_details[bonus_multiplier_id])
)
)

var override = CALCULATE (
MAX (‘ods_spreadsheets sales__bonus_overrides'[bonus_override]),
FILTRAR(
TODAS (‘ods_spreadsheets sales__bonus_overrides'[application_code]),
‘ods_spreadsheets sales__bonus_overrides'[application_code] = SELECTEDVALUE (bonus_applications_details[application_code])
)
)

regreso

si (no (ESBLANCO (anular)), anular, predeterminado)

(¿Hay alguna razón por la que el fragmento de código no pueda formatear DAX?)

Esto es hacer un par de búsquedas para obtener un multiplicador de bonificación predeterminado (según el período de venta) y verificar una anulación del nivel de venta (según el ID de venta. Estas son tablas de búsqueda pequeñas, pero agregar este campo da como resultado que los recursos sean excedido.

Si agrego la medida, el visual dice «se agotaron los recursos disponibles».

Tal vez sea un problema en Synapse (¿por qué SQL estaría consultando campos que no uso en el visual …?)

Tal vez sea un problema en el modelo de datos (pero incluso solo recuperar un campo de la tabla de origen lleva casi un minuto …)

Tal vez sea un problema en mi consulta (pero estoy usando tablas pequeñas e intenté usar FILTER de manera eficiente)

Aprecio que tengas vidas y trabajos, así que, sinceramente, cualquier consejo sería muy apreciado y si esta es una solicitud de ayuda irrazonable o mal redactada, la entiendo.

charliedata

En respuesta a charliedata

Pensé que publicaría aquí para compartir lo que hicimos, en caso de que se vuelva a encontrar esta publicación.

Hicimos cambios en Synapse y Power BI y, combinados, solucionaron el problema de rendimiento.

En Synapse, solo estábamos replicando tablas de BigQuery como parte de una migración de GCP a Azure. No habíamos configurado correctamente los campos (por ejemplo, el tamaño), no habíamos activado el almacenamiento en caché ni habíamos hecho nada para la distribución de datos.

En Power BI estábamos usando medidas donde deberíamos haber usado columnas calculadas

mahoneypat

En respuesta a charliedata

Una sugerencia rápida para probar es reemplazar su segunda variable

 VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('bonus_applications_details'[is_eligible_for_bonus])),
      NOT('bonus_applications_details'[is_eligible_for_bonus] IN {0,
        BLANK()})
    )

con

VAR __DS0FilterTable2 =

Filtro (Keepfilters (valores (‘bonus_applications-details'[is_eligible_for_bonus])), ‘bonus_applications-details'[is_eligible_for_bonus]> 0)

Además, ¿puede publicar la medida original de Power BI, no la MDX de DAX Studio? No estoy seguro de que eso lo aclare más, pero vale la pena intentarlo.

Saludos,

Palmadita

Deja un comentario

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