A raíz de las consultas desarrolladas en la entrega anterior, supongamos que ahora debemos visualizar, además de la marca del producto y el año de venta, el nombre del país y tipo de almacén en el que ésta se ha producido; datos que encontramos en las tablas DimGeography y DimStore respectivamente.
CROSSJOIN. Aumentando la cantidad de tablas a combinar
Puesto que la función GENERATE admite solamente dos tablas en su lista de parámetros debemos modificar nuestra consulta, de forma que soporte la inclusión de nuevas columnas, por lo que vamos a recurrir a la función CROSSJOIN, que al igual que GENERATE, realiza un producto cartesiano de las tablas que recibe como parámetro, pero con la diferencia de poder utilizar más de dos tablas, como vemos en la siguiente consulta.
EVALUATE CROSSJOIN( VALUES('DimGeography'[ContinentName]), VALUES('DimStore'[StoreType]), VALUES('DimProduct'[BrandName]), VALUES('DimDate'[CalendarYear]) ) ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]
Para obtener el mismo resultado en Transact-SQL podemos utilizar la cláusula CROSS JOIN, pero con el inconveniente de que el tiempo de ejecución de la consulta será notablemente mayor que en DAX.
SELECT DISTINCT g.ContinentName,s.StoreType,p.BrandName,d.CalendarYear FROM DimGeography AS g CROSS JOIN DimStore AS s CROSS JOIN DimProduct AS p CROSS JOIN DimDate AS d ORDER BY g.ContinentName,s.StoreType,p.BrandName,d.CalendarYear
Para añadir a la consulta DAX la columna con las cifras de ventas emplearemos ADDCOLUMNS como uno más de los parámetros de CROSSJOIN.
EVALUATE CROSSJOIN( VALUES('DimGeography'[ContinentName]), VALUES('DimStore'[StoreType]), VALUES('DimProduct'[BrandName]), ADDCOLUMNS( VALUES('DimDate'[CalendarYear]), "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency") ) ) ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]
Tras ejecutar la consulta, con toda seguridad habremos advertido que el cálculo resultante en la columna de ventas es incorrecto, ya que para cada fila únicamente se está teniendo en cuenta la columna ‘DimDate'[CalendarYear] al realizar la suma de ‘FactSales'[SalesAmount]. Este resultado para cada grupo de años es el mismo que si utilizáramos la siguiente consulta SQL.
SELECT YEAR(DateKey) AS AñoVenta, FORMAT(SUM(SalesAmount), 'C', 'es-es') AS ImporteVentas FROM FactSales GROUP BY YEAR(DateKey) ORDER BY YEAR(DateKey)
Sin embargo, nuestro objetivo consiste en efectuar la suma de ‘FactSales'[SalesAmount] para cada combinación de ‘DimGeography'[ContinentName], ‘DimStore'[StoreType], ‘DimProduct'[BrandName] y ‘DimDate'[CalendarYear]. Supongamos que tuviéramos que construir esta consulta para ejecutarla en el motor relacional. La sentencia SQL a emplear, utilizando CTEs, sería parecida a la siguiente.
WITH tblGeography AS ( SELECT DISTINCT GeographyKey, ContinentName FROM DimGeography ), tblStore AS ( SELECT StoreKey, StoreType, tblGeography.ContinentName FROM DimStore INNER JOIN tblGeography ON DimStore.GeographyKey = tblGeography.GeographyKey ), tblProduct AS ( SELECT ProductKey, BrandName FROM DimProduct ), tblDate AS ( SELECT Datekey, CalendarYear FROM DimDate ) SELECT ContinentName, StoreType, BrandName, CalendarYear, FORMAT(SUM(SalesAmount), 'C', 'es-es') AS Ventas FROM FactSales INNER JOIN tblStore ON FactSales.StoreKey = tblStore.StoreKey INNER JOIN tblProduct ON FactSales.ProductKey = tblProduct.ProductKey INNER JOIN tblDate ON FactSales.DateKey = tblDate.Datekey GROUP BY ContinentName, StoreType, BrandName, CalendarYear ORDER BY ContinentName, StoreType, BrandName, CalendarYear
Para obtener un resultado equivalente en DAX debemos cambiar la posición de las funciones CROSSJOIN y ADDCOLUMNS dentro de la consulta.
Como primer parámetro de ADDCOLUMNS pasaremos la llamada a CROSSJOIN, y como segundo la expresión que crea la columna calculada que realiza la suma de ‘FactSales'[SalesAmount]. Adicionalmente situaremos todo este código dentro de la función FILTER, añadiendo una condición para quitar las filas que carezcan de importe de venta.
EVALUATE FILTER( ADDCOLUMNS( CROSSJOIN( VALUES('DimGeography'[ContinentName]), VALUES('DimStore'[StoreType]), VALUES('DimProduct'[BrandName]), VALUES('DimDate'[CalendarYear]) ), "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency") ), LEN([Ventas]) > 0 ) ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]
Como alternativa a lo anterior podemos volver a usar GENERATE, pasando en el primer parámetro la llamada a CROSSJOIN y en el segundo la función ADDCOLUMNS.
EVALUATE FILTER( GENERATE( CROSSJOIN( VALUES('DimGeography'[ContinentName]), VALUES('DimStore'[StoreType]), VALUES('DimProduct'[BrandName]) ), ADDCOLUMNS( VALUES('DimDate'[CalendarYear]), "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency") ) ), LEN([Ventas]) > 0 ) ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]
Deja un comentario