Para aclarar las cosas: no discutiré cómo optimizar el código DAX hoy.
Seguirán más artículos, concentrándose en errores comunes y cómo evitarlos.
Pero, antes de que podamos entender las métricas de rendimiento, necesitamos comprender la arquitectura del modelo tabular en Power Bi.
La misma arquitectura se aplica a los modelos tabulares en los servicios de análisis de SQL Server.
Cualquier modelo tabular tiene dos motores:
- Motor de almacenamiento
- Motores de fórmula
Estos dos tienen propiedades distintas y cumplen diferentes tareas en un modelo tabular.
Investiguelos.
Motor de almacenamiento
El motor de almacenamiento es la interfaz entre la consulta DAX y los datos almacenados en el modelo tabular.
Este motor toma cualquier consulta DAX dada y envía consultas al motor de almacenamiento Vertipaq, que almacena los datos en el modelo de datos.
El motor de almacenamiento utiliza un lenguaje llamado XMSQL para consultar el modelo de datos.
Este lenguaje se basa en el lenguaje SQL estándar, pero tiene menos capacidades y admite solo operadores aritméticos simples (+, -, /, *, =, <> e in).
Para agregar datos, XMSQL admite SUM, MIN, MAX, COUNTy DCOUNT (Recuento distinto).
Entonces es compatible GROUP BY, WHEREy JOINs.
Ayudará si tiene una comprensión básica de las consultas SQL cuando intente comprender XMSQL. Si no conoce SQL, será útil aprender los conceptos básicos al profundizar en el análisis DAX de bajo rendimiento.
El hecho más importante es que el motor de almacenamiento es multiproceso.
Por lo tanto, cuando el motor de almacenamiento ejecuta una consulta, utilizará múltiples coro de CPU para acelerar la ejecución de la consulta.
Por último, el motor de almacenamiento puede almacenar consultas y los resultados.
En consecuencia, la ejecución repetida de la misma consulta acelerará la ejecución porque el resultado se puede recuperar del caché.
Motor de fórmula
El motor de fórmula es el motor DAX.
Todas las funciones, que el motor de almacenamiento no puede ejecutar, son ejecutadas por el motor de fórmula.
Por lo general, el motor de almacenamiento recupera los datos del modelo de datos y pasa el resultado al motor de fórmula.
Esta operación se llama materialización, ya que los datos se almacenan en la memoria para ser procesados por el motor de fórmula.
Como puede imaginar, es crucial evitar grandes materializaciones.
El motor de almacenamiento puede llamar al motor de la fórmula cuando un XMSQL-Query contiene funciones que el motor de almacenamiento no puede ejecutar.
Esta es la identificación de operación llamada CallbackDataID y debe evitarse, si es posible.
Crucialmente, el motor de fórmula es de un solo hilo y no tiene caché.
Esto significa:
- No hay paralelismo mediante el uso de múltiples núcleos de CPU
- Sin reutilización de la ejecución repetida de la misma consulta
Esto significa que queremos descargar tantas operaciones como sea posible al motor de almacenamiento.
Desafortunadamente, es imposible definir directamente qué parte de nuestro código DAX se ejecuta por qué motor. Debemos evitar patrones específicos para garantizar que el motor correcto complete el trabajo en la menor cantidad de tiempo.
Y esta es otra historia que puede llenar libros completos.
Pero, ¿cómo podemos ver cuánto tiempo usa cada motor?
Obtener los datos de rendimiento
Necesitamos tener Dax Studio en nuestra máquina para obtener métricas de rendimiento.
Podemos encontrar el enlace de descarga para DAX Studio en la sección de referencias a continuación.
Si no puede instalar el software, puede obtener una versión DAX portátil del mismo sitio. Descargue el archivo zip y desempaquetelo en cualquier carpeta local. Luego puede iniciar daxstudio.exe y obtendrá todas las características sin limitaciones.
Pero primero, necesitamos obtener la consulta DAX de Power BI.
Primero, necesitamos iniciar Analyzer de rendimiento en Power BI Desktop:
Tan pronto como vemos el panel Analyzer de rendimiento, podemos comenzar a registrar los datos de rendimiento y la consulta DAX para todas las imágenes:
Primero, debemos hacer clic en comenzar a grabar
Luego haga clic en “Revise las imágenes” para reiniciar la representación de todas las imágenes de la página real.
Podemos hacer clic en una de las filas de la lista y observar que la visual correspondiente también se activa.
Cuando nos expandimos una de las filas en el informe, vemos algunas filas y un enlace para copiar la consulta DAX al portapapeles.
Como podemos ver, Power BI necesitaba 80’606 milisegundos para completar la representación de la visual matriz.
La consulta Dax sola usó 80’194 milisegundos.
Esta es una medida de bajo rendimiento utilizada en este visual.
Ahora, podemos comenzar Dax Studio.
En caso de que tengamos instalado Dax Studio en nuestra máquina, lo encontraremos en la cinta de herramientas externas:
DAX Studio se conectará automáticamente al archivo de escritorio Power BI.
En caso de que debamos iniciar Dax Studio manualmente, también podemos conectarnos manualmente al archivo Power BI:
Después de establecer la conexión, se abre una consulta vacía en Dax Studio.
En la parte inferior de la ventana DAX Studio, verá una sección de registro donde puede ver qué sucede.
Pero, antes de pegar la consulta DAX desde el escritorio de Power BI, tenemos que iniciar los horarios del servidor en DAX Studio (esquina superior derecha de la ventana Dax Studio):
Después de pegar la consulta al editor vacío, tenemos que habilitar el botón “Borrar en ejecución” y ejecutar la consulta.
“Clear On Run” asegura que el caché del motor de almacenamiento se borre antes de ejecutar la consulta.
Limpiar el caché antes de medir las métricas de rendimiento es la mejor práctica para garantizar un punto de partida consistente para la medición.
Después de ejecutar la consulta, obtendremos una página de Tiempos de servidor en la parte inferior de la ventana DAX Studio:
Ahora vemos mucha información, que exploraremos a continuación.
Interpretando los datos
En el lado izquierdo de los horarios del servidor, veremos los tiempos de ejecución:
Aquí vemos los siguientes números:
- Total: el tiempo de ejecución total en milisegundos (MS)
- SE CPU: la suma del tiempo de CPU que pasó el motor de almacenamiento (SE) para ejecutar la consulta.
Por lo general, este número es mayor que el tiempo total debido a la ejecución paralela utilizando múltiples núcleos de CPU - Fe: el tiempo dedicado al motor de fórmula (FE) y el porcentaje del tiempo de ejecución total
- SE: el tiempo que pasa el motor de almacenamiento (FE) y el porcentaje del tiempo de ejecución total
- Consultas SE: el número de consultas de motor de almacenamiento necesarias para la consulta DAX
- Cache SE: el uso de la caché del motor de almacenamiento, si es que hay alguna
Como regla general: cuanto mayor sea el porcentaje de tiempo de almacenamiento del motor, en comparación con el tiempo del motor de la fórmula, mejor.
La sección central muestra una lista de consultas de motor de almacenamiento:
Esta lista muestra cuántas consultas SE se han ejecutado para la consulta DAX e incluye algunas columnas estadísticas:
- Línea – línea de índice. Por lo general, no veremos todas las líneas. Pero podemos ver todas las líneas haciendo clic en el caché y los botones internos en la esquina superior derecha del panel Times Times del servidor. Pero no los encontraremos muy útiles, ya que son una representación interna de las consultas visibles. A veces puede ser útil ver las consultas de caché y ver qué parte de la consulta ha sido acelerada por el caché SE.
- Subclase: normalmente “escaneo”
- Duración: tiempo dedicado a cada consulta SE
- CPU – Tiempo de CPU dedicado a cada consulta SE
- Par. – Paralelismo de cada consulta SE
- Filas y kb: tamaño de la materialización por la consulta SE
- Cascada: secuencia de tiempo de las consultas SE
- Consulta: el comienzo de cada consulta SE
En este caso, la primera consulta SE devolvió 12’527’422 filas al motor de fórmula (el número de filas en toda la tabla de hechos) utilizando 1 GB de memoria. Esto no es bueno, ya que grandes materializaciones como estas son asesinos de rendimiento.
Esto significa claramente que cometimos un gran error con su código DAX.
Por último, podemos leer el código XMSQL real:
Aquí podemos ver el código XMSQL e intentar comprender el problema de la consulta DAX.
En este caso, vemos que hay una llamada de llamada resaltada. DAX Studio resalta a todos los llamadas de llamadas en el texto de la consulta y hace todas las consultas en la lista de consultas en negrita, que contiene un CallbackDataid.
Podemos ver que, en este caso, una función if () se empuja al motor de fórmula (FE), ya que el SE no puede procesar esta función. Pero Se sabe que FE puede hacerlo. Entonces, llama al FE para cada fila en el resultado. En este caso, más de 12 millones de veces.
Como podemos ver en el momento, esto lleva mucho tiempo.
Ahora sabemos que hemos escrito el código DAX malo y el SE llama al FE muchas veces para ejecutar una función DAX. Y sabemos que usamos 1 GB de RAM para ejecutar la consulta.
Además, sabemos que el paralelismo es solo 1.9 veces, lo que podría ser mucho mejor.
Cómo debería ser
La consulta DAX contiene solo la consulta creada por Power BI Desktop.
Pero en la mayoría de los casos, necesitamos el código de la medida.
DAX Studio ofrece una característica llamada “Definir medidas” para obtener el código DAX de la medida:
- Agregue una de las dos líneas en blanco en la consulta
- Coloque el cursor en la primera línea (vacía)
- Encuentre la medida en el modelo de datos
- Haga clic con el botón derecho en la medida y haga clic en Definir medida
5. Si nuestra medida llama a otra medida, podemos hacer clic en Definir medidas dependientes. En este caso, DAX Studio extrae el código de todas las medidas utilizadas por la medida seleccionada
El resultado es un DEFINE declaración seguida de uno o más MEASURE Declaraciones que contienen el código DAX de nuestra medida de culpabilidad.
Después de optimizar el código, ejecuté la nueva consulta y tomé los horarios del servidor para compararlos con los datos originales:
Ahora, toda la consulta tomó solo 55 ms, y Se creó una materialización de solo 19 filas.
El paralelismo es de 2.6 veces, que es mejor que 1.9 veces. Parece que el SE no necesitaba tanto poder de procesamiento para aumentar el paralelismo.
Esta es una muy buena señal.
La optimización funcionó muy bien después de mirar estos números.
Conclusión
Necesitamos información cuando tenemos una visual lenta en su informe Power BI.
El primer paso es utilizar el analizador de rendimiento en el escritorio de Power BI para ver dónde se dedica el tiempo a representar el resultado de la visual.
Cuando vemos que lleva mucho tiempo ejecutar la consulta DAX, necesitamos DAX Studio para descubrir el problema e intentar solucionarlo.
No cubrí ningún método para optimizar Dax en este artículo, ya que no era mi objetivo hacerlo.
Pero ahora que he establecido las bases para obtener y comprender las métricas de rendimiento disponibles en Dax Studio, puedo escribir más artículos para mostrar cómo optimizar el código DAX, lo que debe evitar y por qué.
Estoy deseando que llegue el viaje contigo.
Descargar Dax Studio gratis aquí: https://www.sqlbi.com/tools/dax-studio/
Capacitación gratuita de herramientas SQLBI: Curso de video de Dax Tools – SQLBI
SQLBI también ofrece capacitación en optimización DAX.
Utilizo el conjunto de datos de muestra Contoso, como en mis artículos anteriores. Puede descargar el conjunto de datos ContaSoretailDW gratis desde Microsoft aquí.
Los datos de Contoso se pueden usar libremente bajo la licencia MIT, como se describe aquí.