Power Pivot: Optimizar tiempos de cálculo

Hoy veremos cómo el complemento DAX Studio nos puede ayudar a entender y luego mejorar nuestros tiempos de cálculo respecto de las medidas y/o columnas calculadas.
Repasaremos dentro del complemento dos conceptos comentados en una entrada anterior: SE y FE (motor de almacenamiento y motor de procesamiento).

Para mostrar los diferente tiempos empleados, partiremos de un ejemplo sencillo con tres tablas:
TblDATOS (campos: 'Vendedor', 'artículo', 'Tipo venta' y 'Unidades vendidas') y 200.000 registros
TblPRECIOS (campos: 'artículo', 'precio' y 'Descuento_ART') y 15 registros
TblDESC_VIP (campos: 'Tipo' y 'Descuento_VIP') y 3 registros

Esto es, clásico ejemplo para calcular nuestras ventas netas. Comenzaremos Agregando las tres tablas al Modelo de datos de Power Pivot en nuestro libro de Excel.
Desde la Ficha de Power Pivot y presionando el botón 'Agregar al modelo de datos'.

Ya dentro del editor de Power Pivot, accederemos a la Vista de Diagrama donde arrastraremos los campos comunes entre las tres tablas, creando así las relaciones necesarias:

Vamos con los cálculos y sus medidas de tiempo...

Empezaremos con una columna calculada que nos devuelva fila a fila el cálculo requerido:
unidades x precio x (1-descuento artículo) x (1 - descuento vip)

Veamos este caso dentro del complemento DAX Studio.
Obvio que ya lo tienes descargado e instalado y disponible en la Ficha complementos de Excel.

Al presionar el botón de DAX Studio se nos abrirá el enlace de conexión a nuestro libro de trabajo:

Y se nos abrirá una ventana de código donde escribir nuestra estructura de DAX...

En la ventana escribiremos lo siguiente para replicar el efecto de crear una columna calculada en el modelo de datos:

EVALUATE
//añadimos una nueva columna a la TblDATOS
ADDCOLUMNS(TblDATOS,
		"Precio_descontado",	-- como producto de las unidades x precio x descto_vip x desc_art
		TblDATOS[Unidades vendidas]*
		RELATED(TblPRECIOS[precio])*
		(1-RELATED(TblDESC[Descuento_VIP]))*
		(1-RELATED(TblPRECIOS[Descuento_ART])))

Activaremos el Server Timings en la Ficha 'Home' dentro de DAX y a continuación ejecutaremos la consulta creada (también puedes presionar F5).
Esto iniciará unos contadores de tiempos y algunas estadísticas sobre la ejecución de la consulta.
Una vez finalizado el proceso no olvides presionar de nuevo el botón de Server Timings con el fin de parar los contadores...
En este caso, nuestra consulta de creación de columna calculada, nos reporta estos datos:

Veamos los datos...
Tiempo total en milisegundos: 5.203 ms (esto son 5,2 segundos... lo que no está nada mal... recuerda son 200.000 registros calculando datos a partir de otras dos tablas!!).
Además nos dice cuál ha sido el consumo de motores (SE y FE). En este caso todo ha sido motor de procesamiento!!.
Pero espera... todos los manuales nos dicen que crear columnas consume más motor de almacenamiento??.
Bueno, recuerda lo que decíamos en un artículo previo, mejor compruébalo... no des nada por sentado ;-)
También interesante (si conoces algo de SQL) es que esta ventana nos ofrece el código SQL que hay detrás de nuestra consulta.... pero eso es otra guerra.

Como resumen nos quedamos que tardamos 5,2 segundos en crear una nueva columna con 200.000 nuevos cálculos.

Y qué pasa si creamos una medida que replique el cálculo de esa columna??. Veámoslo.
En DAX añadimos una nueva ventana para otra query, donde añadiremos el siguiente código DAX:

DEFINE --definimos el cálculo de una medida
MEASURE TblDATOS[PrecioDescontado1]=	--con una doble iteración
SUMX(TblDESC,		--primero recorremos la tblDESC
	SUMX(TblPRECIOS,	--y para cada valor de la tblDESC, recorremos cada fila de tblPRECIOS
		VAR descVIP=TblDESC[Descuento_VIP]
		VAR descART=TblPRECIOS[Descuento_ART]
		VAR EUR=[Valor_Eur]		--además en cada ocasión exigimos se ejecute otra medida (con sus itereaciones)
		RETURN
		EUR*(1-descVIP)*(1-descART)	)	)
		
EVALUATE
//creamos un resumen simple donde se opera con nuestra medida
//se trata de no interferir demasiado en el cálculo
SUMMARIZECOLUMNS(TblDATOS[Vendedor],
				"importe_calculado", [PrecioDescontado1])

Repetimos el proceso para conocer las estadíticas de esta consulta... Y obtenemos lo siguiente:

Veamos los datos...
Tiempo total en milisegundos: 11 ms (sin comentarios :OO ).
Consumo de motores (FE: 4 ms y SE:7 ms )
Poco ahi que decir... sin embargo, el procedimiento empleado no parece el más óptimo, si nuestras tablas de dimensiones fueran de mayor envergadura, ya que dobles iteraciones nunca son buenas, puesto que exigen cálculos y repeticiones exponenciales, por ejemplo, un doble bucle de 200.000 registros, sobre una segunda tabla de 1.000 registros, exigiría 200.000 x 1.000 = 200.000.000 de cálculos!!

Un truco que aprendí de mi colega Alberto Ferrari (de SQLBI) viendo alguno de sus vídeos, es resumir a valores únicos los datos, para evitar una 'granularidad' alta (que haya muchos datos diferentes a evaluar).
Podríamos entonces emplear SUMMARIZE para tener una 'tabla reducida' a partir de la cual trabajar.
Vamonos de nuevo a DAX y creamos una nueva ventana de código, donde insertaremos un nuevo código.
La idea es la misma de antes... primero definimos una medida, para luego aplicarla en forma de resumen.
El código DAX en esta ocasión es:

DEFINE MEASURE 
TblDATOS[Importe_descontado]=		--definimos el cálculo de una medida
SUMX(
	ADDCOLUMNS(
			//summarize nos devuelve una tabla de tres columnas, con las combinaciones únicas
			//generadas entre los elementos únicos de esas columnas
			//en lugar de trabajar sobre los 200.000 registros!!
			SUMMARIZE(TblDATOS, TblDESC[Descuento_VIP],TblPRECIOS[Descuento_ART],TblDATOS[Vendedor]), 
				"IMPORTE",[Valor_Eur]),	-- al resultado anterior le añadimos una columna con la medida Valor_eur
	[IMPORTE]*(1-[Descuento_VIP])*(1-[Descuento_ART]))	--sobre la tabla anterior aplicamos una iteración
														--con SUMX acumulando valores con la expresión indicada

EVALUATE
SUMMARIZECOLUMNS(TblDATOS[Vendedor],
				"importe_calculado", [Importe_descontado])

Repetimos una vez más el proceso de controlar tiempos con el botón Server Timings y vemos lo siguiente:

O sea tenemos los siguientes valores:
Tiempo total en milisegundos: 3 ms (:OOOOO cómooooo ).
Consumo de motores (FE: 3 ms y SE:3 ms )

Bueno, no nos debería sorprender... era lo esperado. Al evitar esos dobles-triples bucles, los tiempos deben ser menores.. si además reducimos los datos sobre los que trabajar, aún más...
Aún así, brutal la enseñanza de Alberto Ferrari.. gracias!!.

Solo hemos visto algunos de los aspecto relevantes de esas estadísticas, pero hay otros puntos igualmente importantes, como el número de subconsultas que se generan en los procesos de cálculo (SE queries), o la caché, etc...

Ya comprobado cual es la mejor opción, nos iremos al editor de Power Pivot e implantaremos la medida elegida.
En el área de cálculo, añadimos la medida:

Precio_descontado2:=SUMX(ADDCOLUMNS(SUMMARIZE(TblDATOS; TblDESC[Descuento_VIP];TblPRECIOS[Descuento_ART];TblDATOS[Vendedor]); 
				"IMPORTE";[Valor_Eur]);
	[IMPORTE]*(1-[Descuento_VIP])*(1-[Descuento_ART]))

Ya podríamos usar en nuestra hoja de cálculo, una tabla dinámica asociada al modelo de datos, que mostrara el cálculo que queríamos para cada vendedor...