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

Como ya explicábamos en el artículo sobre creación de consultas para modelos tabulares (primera y segunda parte), el lenguaje DAX puede ser utilizado para emitir consultas contra un modelo tabular, obteniendo como respuesta un conjunto de resultados, dentro de una mecánica similar a la que estamos acostumbrados a emplear cuando trabajamos con el motor relacional, es decir, mediante sentencias SQL del estilo “SELECT ListaDeCampos FROM Tabla”. Esta funcionalidad del lenguaje recibe el nombre de Consultas DAX (DAX Queries), siendo la instrucción EVALUATE la encargada de llevarla a cabo empleando sentencias del tipo “EVALUATE Expresión”.

Aunque inicialmente, las consultas DAX no estaban disponibles en el lenguaje cuando PowerPivot hizo su primera presentación, la llegada de SQL Server 2012, que posiciona a Business Intelligence Semantic Model (BISM) como el nuevo paradigma en el desarrollo de soluciones de inteligencia de negocio, ha supuesto una ampliación de las características del lenguaje, que ahora nos va a permitir escribir consultas contra los nuevos modelos tabulares pertenecientes a BISM.

Con el presente artículo iniciamos una serie en la que continuaremos la línea de exploración iniciada en el artículo antes mencionado, por lo que son recomendables, al menos, unos conocimientos básicos sobre DAX, de forma que los ejemplos que vamos a exponer puedan ser seguidos adecuadamente. Adicionalmente, en aquellos casos en que se estime oportuno, mostraremos el código de la consulta tanto en lenguaje DAX como en Transact-SQL, lo que nos permitirá observar las diferencias entre ambos a la hora de abordar la resolución de un mismo problema.

 

El proyecto de ejemplo

Como base de los ejemplos que iremos desarrollando vamos a utilizar el modelo ContosoTabular, anteriormente creado aquí; aunque en esta ocasión volveremos a abrir el proyecto de dicho modelo en SQL Server Data Tools (SSDT) para añadir la tabla DimEmployee (necesaria en alguna de las diversas pruebas a realizar), tras lo cual desplegaremos nuevamente el modelo en el servidor de análisis de SQL Server, actualizando así los cambios realizados. A continuación ejecutaremos SQL Server Management Studio (SSMS) abriendo una ventana de consulta, que a partir de este momento utilizaremos para ilustrar las funciones empleadas en la construcción de consultas DAX.

ProfundizandoUsoDAXComoLenguajeConsulta_01

 

ADDCOLUMNS. Creación dinámica de columnas

La función ADDCOLUMNS permite crear una o varias columnas calculadas a partir de una tabla o expresión de tabla. Como primer parámetro situaremos la tabla a la que se añadirá la columna(s), definiendo a continuación cada una de las columnas mediante una cadena para el título y una expresión DAX con la fórmula para el cálculo de la columna.

En la siguiente sentencia mostramos la tabla DimStore junto a una columna calculada que duplica el número de empleados de cada almacén.


EVALUATE
ADDCOLUMNS(
'DimStore',
"Empleados Duplicados", [EmployeeCount] * 2
)

ProfundizandoUsoDAXComoLenguajeConsulta_02

 

Para obtener el mismo resultado utilizando la instrucción SELECT en Transact-SQL escribiremos la siguiente consulta.


SELECT *, EmployeeCount * 2 AS [Empleados Duplicados]
FROM DimStore

Continuando con la tabla DimStore, supongamos que de la columna OpenDate, que contiene la fecha de apertura del almacén, queremos obtener la parte correspondiente al mes como nombre. Una posibilidad, aunque no la más óptima, como veremos enseguida, sería utilizar la siguiente consulta, en la que combinando ADDCOLUMNS con las funciones MONTH y SWITCH, obtenemos una nueva columna con el nombre del mes correspondiente a OpenDate.


EVALUATE
ADDCOLUMNS(
'DimStore',
"Mes Apertura", SWITCH(MONTH([OpenDate]),
	1,"Enero",
	2,"Febrero",
	3,"Marzo",
	4,"Abril",
	5,"Mayo",
	6,"Junio",
	7,"Julio",
	8,"Agosto",
	9,"Septiembre",
	10,"Octubre",
	11,"Noviembre",
	12,"Diciembre"
	)
)

ProfundizandoUsoDAXComoLenguajeConsulta_03

 

La función MONTH devuelve el número del mes de la fecha pasada como parámetro. Si este resultado lo pasamos a su vez como parámetro a la función SWITCH, para cada número retornamos una cadena con el nombre del mes correspondiente.

Pero como ya decíamos antes, existe una forma mucho más simple de obtener el mismo resultado, consistente en utilizar la función FORMAT, a la que pasaremos como primer parámetro la columna de fecha, y como segundo la cadena con el formato que deseamos aplicar. La siguiente expresión consigue el mismo resultado que la anterior pero con mucho menos código.


EVALUATE
ADDCOLUMNS(
'DimStore',
"Mes Apertura", FORMAT([OpenDate], "MMMM")
)

En Transact-SQL también disponemos de la función FORMAT que se utiliza como vemos a continuación.


SET LANGUAGE Spanish

SELECT *, FORMAT(OpenDate, 'MMMM') AS [Mes Apertura]
FROM DimStore

Compliquemos ahora un poco la expresión utilizada para crear la columna calculada: supongamos que necesitamos averiguar la cantidad de años transcurridos desde la fecha de apertura del almacén (columna OpenDate) hasta la fecha actual, teniendo en cuenta que si existe valor en la columna CloseDate significará que el almacén ya ha sido cerrado.

La expresión de columna para resolver esta situación implicará, pues, el uso de las siguientes funciones: YEAR, para extraer el año de las columnas de fecha; NOW, para obtener la fecha actual; ISBLANK, para comprobar la existencia de valor en la columna CloseDate; e IF, para determinar la operación que realiza el cálculo de años según la columna CloseDate tenga o no valor.


EVALUATE
ADDCOLUMNS(
'DimStore',
"Años actividad almacén", IF(ISBLANK([CloseDate]),
YEAR(NOW()) - YEAR([OpenDate]),
YEAR([CloseDate]) - YEAR([OpenDate])
)
)

ProfundizandoUsoDAXComoLenguajeConsulta_04

 

En SQL usaremos la función DATEDIFF para hallar la diferencia entre las fechas. La expresión CASE nos servirá para utilizar en el cálculo la fecha actual o la de cierre del almacén si la primera es nula.


SELECT *,
CASE
	WHEN CloseDate IS NULL THEN
		DATEDIFF(year, OpenDate, GETDATE())
	ELSE
		DATEDIFF(year, OpenDate, CloseDate)
END AS [Años actividad almacén]
FROM DimStore

Las columnas calculadas creadas hasta el momento con la función ADDCOLUMNS se basan en operaciones sobre las columnas de la propia tabla DimStore, pasada como primer parámetro a la función. No obstante, también es posible generar columnas dinámicas que pertenezcan a otras tablas del modelo tabular con las que DimStore guarda relación.

Observando el diagrama de tablas del modelo tabular en SSDT, vemos que DimStore mantiene relaciones con las tablas FactSales, DimGeography y DimEmployee.

ProfundizandoUsoDAXComoLenguajeConsulta_05

 

Es por lo tanto perfectamente posible crear una columna calculada con ADDCOLUMNS que muestre la información de la columna RegionCountryName, perteneciente a la tabla DimGeography, ya que se relaciona con la tabla DimStore a través de la columna GeographyKey, común en ambas tablas. Es necesario tener en cuenta que en el momento de crear la expresión de la columna calculada debemos indicar explícitamente la existencia de dicha relación utilizando la función RELATED. Esta nueva columna tendrá como título “País ubicación”.


EVALUATE
ADDCOLUMNS(
'DimStore',
"País ubicación", RELATED('DimGeography'[RegionCountryName])
)

Además, tal y como explicábamos al principio de este apartado, con ADDCOLUMNS podemos crear varias columnas calculadas al mismo tiempo, por lo que añadiremos a la consulta anterior otra expresión con el título “Ventas almacén”, que realice, empleando la función SUM, la suma de la columna SalesAmount, perteneciente a la tabla FactSales, con la que también se relaciona DimStore a través del campo StoreKey.


EVALUATE
ADDCOLUMNS(
'DimStore',
"País ubicación", RELATED('DimGeography'[RegionCountryName]),
"Ventas almacén", SUM('FactSales'[SalesAmount])
)

ProfundizandoUsoDAXComoLenguajeConsulta_06

 

Pero observando el resultado obtenido vemos que hay algo que no funciona correctamente, ya que la finalidad de la anterior consulta consiste en obtener la suma del campo SalesAmount para cada uno de los almacenes, y lo que hemos conseguido es que el valor de la columna calculada “Ventas almacén” muestre en todos los casos la suma total de SalesAmount, lo cual no es el objetivo que perseguimos.

Podemos solucionar este problema de dos formas: la primera consiste en sustituir la función SUM por SUMX, que sobre una tabla pasada como primer parámetro realiza la suma de la columna pasada en el segundo parámetro. Aquí también debemos indicar que existe una relación entre DimStore y FactSales, para lo que usaremos la función RELATEDTABLE.


EVALUATE
ADDCOLUMNS(
'DimStore',
"País ubicación", RELATED('DimGeography'[RegionCountryName]),
"Ventas almacén", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
)

De esta forma la suma ya se realizará de forma separada para cada almacén.

ProfundizandoUsoDAXComoLenguajeConsulta_07

 

La otra vía de abordar la suma de las ventas por almacén consiste en utilizar la función CALCULATE, a la que pasaremos como parámetro la operación de suma. CALCULATE se encargará de evaluar dicha expresión para cada uno de los almacenes (valores distintos del campo StoreKey) existentes en la tabla DimStore, obteniendo de esta manera el resultado deseado.


EVALUATE
ADDCOLUMNS(
'DimStore',
"País ubicación", RELATED('DimGeography'[RegionCountryName]),
"Ventas almacén", CALCULATE(SUM('FactSales'[SalesAmount]))
)

La función CALCULATE puede revestir cierta complejidad inicial, por lo que recomendamos visitar el siguiente post del blog de Marco Russo, en el que realiza una detallada explicación acerca de su funcionamiento.

Para obtener este resultado con Transact-SQL usaremos una combinación de tipo LEFT JOIN entre las tablas DimStore y DimGeography, además de una subconsulta sobre la tabla FactSales, que realice la suma del campo SalesAmount con la función SUM.


SELECT S.*, G.RegionCountryName,
(SELECT SUM(F.SalesAmount) FROM FactSales AS F WHERE F.StoreKey = S.StoreKey) AS [Ventas almacén]
FROM DimStore AS S
LEFT JOIN DimGeography AS G
ON S.GeographyKey = G.GeographyKey

Como hemos podido comprobar, la función ADDCOLUMNS resulta de gran utilidad a la hora de añadir a nuestras consultas información adicional en forma de columnas calculadas. En las siguientes entregas explicaremos diversas técnicas y funciones del lenguaje, mediante las que conseguiremos seleccionar un subconjunto de columnas de la tabla, evitando la necesidad de obtener la totalidad de las mismas.

Deja un comentario

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