La cláusula ROLLUP perteneciente a la función SUMMARIZE genera, en las columnas de agregación que especifiquemos, filas adicionales de resultados acumulados (totales parciales) para las columnas numéricas utilizadas en la consulta.
A modo de ejemplo, en la siguiente consulta utilizamos ROLLUP para la columna StoreType de la tabla DimStore, por lo que obtendremos, además de las cifras de venta por tipo de almacén, una última fila adicional con el total de las ventas.
EVALUATE SUMMARIZE ( 'DimStore', ROLLUP ( 'DimStore'[StoreType] ), "Ventas", SUM ( 'FactSales'[SalesAmount] ) )
Partiendo de este resultado, supongamos ahora que necesitamos que los tipos de almacén aparezcan ordenados, pero manteniendo la fila de total al final. Si usamos la cláusula ORDER BY en la consulta, el resultado no será totalmente satisfactorio, ya que se ordenarán los nombres, pero la fila de total quedará al principio.
EVALUATE SUMMARIZE ( 'DimStore', ROLLUP ( 'DimStore'[StoreType] ), "Ventas", SUM ( 'FactSales'[SalesAmount] ) ) ORDER BY [StoreType]
Aplicando el modificador DESC a la cláusula ORDER BY solucionamos el problema solamente en parte, ya que conseguimos que el total vuelva a colocarse al final del conjunto de resultados, pero como es lógico, los nombres quedan ordenados en sentido descendente.
EVALUATE SUMMARIZE ( 'DimStore', ROLLUP ( 'DimStore'[StoreType] ), "Ventas", SUM ( 'FactSales'[SalesAmount] ) ) ORDER BY [StoreType] DESC
Controlando la ubicación de los acumulados mediante ISSUBTOTAL
Si queremos ordenar los nombres en sentido ascendente, pero manteniendo la ubicación del total al final, debemos recurrir a ISSUBTOTAL, otra de las cláusulas de SUMMARIZE, la cual crea una columna de tipo lógico, en la que cada valor nos indica si la fila actual del conjunto de resultados corresponde a un acumulado (total parcial) o bien se trata de una fila normal de datos. El truco en este caso reside en aplicar una doble ordenación: primero por la columna de ISSUBTOTAL y a continuación por la de datos.
EVALUATE SUMMARIZE ( 'DimStore', ROLLUP ( 'DimStore'[StoreType] ), "Ventas", SUM ( 'FactSales'[SalesAmount] ), "Subtotal_StoreType", ISSUBTOTAL ( 'DimStore'[StoreType] ) ) ORDER BY [Subtotal_StoreType], [StoreType]
Ahora vamos a añadir la columna ContinentName, de la tabla DimGeography, como nueva columna de agrupación a la consulta. De esta manera, cada fila de acumulado resultante se corresponderá con el total de ventas de un continente para todos los tipos de almacén.
EVALUATE SUMMARIZE ( 'DimStore', ROLLUP ( 'DimStore'[StoreType] ), 'DimGeography'[ContinentName], "Ventas", SUM ( 'FactSales'[SalesAmount] ) )
La interpretación de los datos que obtenemos, no obstante, es poco amigable, por lo que nuevamente usaremos ISSUBTOTAL y ordenaremos las columnas tal y como muestra el siguiente bloque de código, consiguiendo que las filas de acumulado se sitúen proporcionando la información de un modo más legible.
EVALUATE SUMMARIZE ( 'DimStore', ROLLUP ( 'DimStore'[StoreType] ), 'DimGeography'[ContinentName], "Ventas", SUM ( 'FactSales'[SalesAmount] ), "Subtotal_StoreType", ISSUBTOTAL ( 'DimStore'[StoreType] ) ) ORDER BY [ContinentName], [Subtotal_StoreType], [StoreType]
Ya que ROLLUP admite más de una columna de agregación como parámetro, vamos a añadirle la columna ContinentName para obtener en esta ocasión, además de los acumulados anteriormente mencionados, una nueva fila de total con el importe de las ventas para todos los tipos de almacén y continentes. Al mismo tiempo crearemos con ISSUBTOTAL sendas columnas para StoreType y ContinentName, que junto a un adecuado orden facilitarán la lectura de los datos.
EVALUATE SUMMARIZE ( 'DimStore', ROLLUP ( 'DimStore'[StoreType], 'DimGeography'[ContinentName] ), "Ventas", SUM ( 'FactSales'[SalesAmount] ), "Subtotal_StoreType", ISSUBTOTAL ( 'DimStore'[StoreType] ), "Subtotal_ContinentName", ISSUBTOTAL ( 'DimGeography'[ContinentName] ) ) ORDER BY [Subtotal_StoreType], [StoreType], [Subtotal_ContinentName], [ContinentName]
Agrupar los acumulados con ROLLUPGROUP
A pesar de existir la posibilidad de utilizar varias columnas con ROLLUP, en determinadas situaciones quizá nos interese tener una única fila de acumulado para todas las columnas de agregación incluidas en esta cláusula, lo que podemos lograr mediante ROLLUPGROUP, otra de las cláusulas de SUMMARIZE.
Esta cláusula se utiliza como parámetro de ROLLUP, colocando las columnas de agregación como parámetros de la llamada a la función ROLLUPGROUP.
EVALUATE SUMMARIZE ( 'DimStore', ROLLUP ( ROLLUPGROUP ( 'DimStore'[StoreType], 'DimGeography'[ContinentName] ) ), "Ventas", SUM ( 'FactSales'[SalesAmount] ), "Subtotal_StoreType", ISSUBTOTAL ( 'DimStore'[StoreType] ) ) ORDER BY [Subtotal_StoreType], [StoreType], [ContinentName]
Cabe destacar que la información sobre ROLLUPGROUP disponible en la documentación online de la función SUMMARIZE es una aportación de Marco Russo, puesto que dicha entrada, correspondiente a la referencia de las funciones del lenguaje DAX, no disponía inicialmente de reseña alguna acerca de esta cláusula.
Para lograr un comportamiento similar en Transact-SQL haremos uso del operador ROLLUP cuando especifiquemos las columnas a agrupar, así como de la función GROUPING en la lista de columnas a mostrar de la instrucción SELECT.
SELECT CASE WHEN (GROUPING(StoreType)=1) THEN '--Total General--' ELSE StoreType END AS StoreType, CASE WHEN (GROUPING(ContinentName)=1) THEN '--Acumulado por tipo almacén--' ELSE ContinentName END AS ContinentName, SUM(SalesAmount) AS Ventas FROM DimStore INNER JOIN DimGeography ON DimStore.GeographyKey = DimGeography.GeographyKey INNER JOIN FactSales ON DimStore.StoreKey = FactSales.StoreKey GROUP BY StoreType, ContinentName WITH ROLLUP ORDER BY StoreType, GROUPING(ContinentName), ContinentName
Y llegados a este punto damos por concluida esta serie de artículos en los que hemos expuesto diversas técnicas para la creación de consultas contra modelos tabulares empleando el lenguaje DAX, espero que os resulten de utilidad.
Deja un comentario