La función CROSSJOIN. Profundizando en el uso de DAX como lenguaje de consulta (4)

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]

ProfundizandoUsoDAXComoLenguajeConsulta_15

 

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]

ProfundizandoUsoDAXComoLenguajeConsulta_16

 

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)

ProfundizandoUsoDAXComoLenguajeConsulta_17

 

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

 

ProfundizandoUsoDAXComoLenguajeConsulta_18

 

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]

ProfundizandoUsoDAXComoLenguajeConsulta_19

 

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

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