BISM, Business Intelligence, DAX, PowerPivot, SQL Server, SQL Server 2012, Tabular Model

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

En la segunda parte de esta serie ilustrábamos cómo mediante el trabajo en conjunto de VALUES más ADDCOLUMNS, lográbamos crear consultas cuyos resultados ofrecieran al mismo tiempo la información de una tabla más una operación de agregado sobre otra tabla relacionada.

Pero habrá ocasiones en que esto no sea suficiente, siendo necesario añadir a la consulta datos de otras tablas del modelo, para ampliar y mejorar la información proporcionada al usuario.

Por ejemplo, tomando como punto de partida la consulta con la función VALUES de la entrega anterior, supongamos que además de la columna BrandName, necesitamos añadir al resultado la columna CalendarYear de la tabla DimDate y un cálculo basado en la suma de las ventas realizadas por la combinación de las dos columnas.

Si con ADDCOLUMNS intentáramos ejecutar una consulta como la siguiente se produciría un error, ya que de los parámetros admitidos por esta función, sólo el primero puede ser una tabla o expresión de tabla, siendo el resto expresiones que generan columnas calculadas.


EVALUATE
ADDCOLUMNS(
VALUES('DimProduct'[BrandName]),
VALUES('DimDate'[CalendarYear]),
"Ventas por marca y año", CALCULATE(SUM('FactSales'[SalesAmount]))
)

GENERATE. Enriqueciendo el resultado mediante la combinación de tablas

Para resolver este problema recurriremos a la función GENERATE, que recibe como parámetro dos tablas o expresiones de tabla, generando como resultado el producto cartesiano de ambas. A continuación vemos una consulta de ejemplo que emplea esta función para combinar las tablas DimStore y DimGeography.


EVALUATE
GENERATE('DimStore','DimGeography')

Observando el resultado, apreciaremos ocurrencias de filas en la tabla DimStore que tienen una correspondencia geográfica incorrecta al combinarse con la tabla DimGeography, debido precisamente al modo en el que se realiza el cruce entre los registros de ambas tablas. En breve explicaremos cómo arreglar este aspecto en particular.

La forma de aplicar GENERATE para resolver nuestro problema consiste en pasarle como primer parámetro la función VALUES, creando así la columna con las marcas de productos; y como segundo parámetro la función ADDCOLUMNS, para crear tanto la columna de años como la columna calculada con la suma de ventas. Todo ello lo vemos en la siguiente consulta.


EVALUATE
GENERATE(
	VALUES('DimProduct'[BrandName]),
	ADDCOLUMNS(
		VALUES('DimDate'[CalendarYear]),
		"Ventas por marca y año", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
	)
)
ORDER BY [BrandName], [CalendarYear]

En el conjunto de resultados obtenido, como ya hemos mencionado, habrá filas con el valor de la columna «Ventas por marca y año» vacías, que corresponderán a registros de la tabla FactSales para los que no se han producido ventas en la fecha con la que se están cruzando.

ProfundizandoUsoDAXComoLenguajeConsulta_11

 

Para evitar la visualización de las filas que no tienen valor en la columna de importe de venta agregaremos a la consulta la función FILTER, en la que situaremos como primer parámetro la expresión anterior, mientras que en el segundo parámetro escribiremos una expresión que actúe como filtro, eliminando las filas sin valor de venta.


EVALUATE
FILTER(
	GENERATE(
		VALUES('DimProduct'[BrandName]),
		ADDCOLUMNS(
			VALUES('DimDate'[CalendarYear]),
			"Ventas por marca y año", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
		)
	),
	NOT(ISBLANK([Ventas por marca y año]))
)
ORDER BY [BrandName], [CalendarYear]

Otra variante de la expresión de filtro podría ser la siguiente:


[Ventas por marca y año] > 0

Con cualquiera de estas formas de aplicar el filtro, el conjunto de resultados ya no mostrará importes vacíos.

ProfundizandoUsoDAXComoLenguajeConsulta_12

 

Con Transact-SQL podemos obtener este mismo resultado utilizando una sentencia muy similar a la última empleada, pero añadiendo la combinación con la tabla DimDate y su campo DateKey, así como el campo CalendarYear en la cláusula GROUP BY.


WITH tblFactSales AS
(
	SELECT DateKey, ProductKey, SalesAmount
	FROM FactSales
)
SELECT BrandName, CalendarYear, SUM(tblFactSales.SalesAmount) AS [Ventas por marca y año]
FROM DimProduct
INNER JOIN tblFactSales
ON DimProduct.ProductKey = tblFactSales.ProductKey
INNER JOIN DimDate
ON DimDate.Datekey = tblFactSales.DateKey
GROUP BY BrandName, CalendarYear
ORDER BY BrandName, CalendarYear

Corrección de efectos no deseados en la ordenación de números con formato

Si en la anterior consulta DAX aplicamos un formato (moneda, separador de millar, etc.) sobre la columna «Ventas por marca y año», creada dinámicamente, y ordenamos por dicha columna de la siguiente manera.


EVALUATE
FILTER(
	GENERATE(
		VALUES('DimProduct'[BrandName]),
		ADDCOLUMNS(
			VALUES('DimDate'[CalendarYear]),
			"Ventas por marca y año", FORMAT(SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount]), "Currency")
		)
	),
	LEN([Ventas por marca y año]) > 0
)
ORDER BY [Ventas por marca y año]

El resultado no se mostrará ordenado tal y como esperamos.

ProfundizandoUsoDAXComoLenguajeConsulta_13

 

Esto es debido a que la columna «Ventas por marca y año», por causa del formateo aplicado, contiene datos de tipo carácter en lugar de numéricos, lo que produce el aparentemente extraño comportamiento en la ordenación, que en realidad no es tal, ya que se trata del modo correcto de ordenar una columna de tipo string (obsérvese también que para la condición de filtro hemos utilizado la función LEN, que se emplea en el manejo de cadenas).

Vamos a solucionar este inconveniente recurriendo de nuevo a la creación de medidas mediante la instrucción DEFINE, con la que crearemos la medida que realiza la suma de las ventas, la cual emplearemos en los siguientes puntos de la consulta: dentro de ADDCOLUMNS, como expresión de filtro, y como criterio de ordenación.


DEFINE
MEASURE 'FactSales'[Ventas] = SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])

EVALUATE
FILTER(
	GENERATE(
		VALUES('DimProduct'[BrandName]),
		ADDCOLUMNS(
			VALUES('DimDate'[CalendarYear]),
			"Ventas por marca y año", FORMAT('FactSales'[Ventas], "Currency")
		)
	),
	[Ventas] > 0
)
ORDER BY [Ventas] DESC

ProfundizandoUsoDAXComoLenguajeConsulta_14

 

La obtención de resultados procedentes de dos tablas resulta una estupenda posibilidad de la que disponemos al escribir consultas DAX, aunque en escenarios en los que debamos manejar información de un mayor número de tablas resultará insuficiente. En la siguiente parte de esta serie abordaremos dicha cuestión.

2 Comentarios

  1. kiquenet

    Grandes artículos, Luismi. Saludos.

  2. lmblanco

    Hola Enrique

    Muchas gracias por tu interés en la serie y un saludo.

    Luismi

Deja un comentario

Tema creado por Anders Norén