Combinación de múltiples tablas con SUMMARIZE. Profundizando en el uso de DAX como lenguaje de consulta (7)

Las columnas de agrupación de los ejemplos con SUMMARIZE mostrados en la entrega anterior pertenecían a una misma tabla, lo cual puede resultar conveniente en determinados casos, pero con toda seguridad, en algún momento nos encontraremos ante situaciones en las que tengamos que crear una consulta para visualizar columnas pertenecientes a diversas tablas del modelo.

Gracias a la potencia que ofrece el mecanismo de relaciones del motor tabular, esta labor resulta tan simple como añadir tales columnas en la lista de parámetros de la función, siempre y cuando existan las oportunas relaciones entre las tablas integrantes de la consulta.

Observando el diagrama de nuestro modelo de datos de ejemplo vemos que la tabla DimStore se relaciona con las tablas DimGeography, DimEmployee y FactSales, por lo que podemos construir una consulta con SUMMARIZE en la que ofrezcamos al usuario la suma de los descuentos sobre las ventas, agregada por el tipo de almacén, país y nombre del gerente del almacén que ha realizado la venta.

EVALUATE
SUMMARIZE(
  'DimStore',
  'DimStore'[StoreType],
  'DimGeography'[RegionCountryName],
  'DimEmployee'[FirstName],
  'DimEmployee'[LastName],
  "Total descuentos", SUM('FactSales'[DiscountAmount])
)
ORDER BY [StoreType],[RegionCountryName],[FirstName],[LastName]

En SQL esta consulta es muy similar a la anterior que hemos escrito en Transact, con alguna variación en las tablas a combinar.

SELECT StoreType, RegionCountryName, FirstName, LastName,
SUM(DiscountAmount) AS [Total descuentos]
FROM DimStore
INNER JOIN DimGeography
ON DimStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN DimEmployee
ON DimStore.StoreManager = DimEmployee.EmployeeKey
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
GROUP BY StoreType, RegionCountryName, FirstName, LastName
ORDER BY StoreType, RegionCountryName, FirstName, LastName

En la tabla DimStore, la columna StoreManager representa al empleado de la organización que realiza las funciones de gerente o director del almacén, existiendo la posibilidad de que un mismo gerente se encuentre al cargo de varios almacenes. Esto nos permite complicar un poco la consulta DAX anterior, de manera que los resultados visualizados se correspondan con los directores que administran más de un almacén.

A continuación mostramos nuevamente esta consulta con los retoques que acabamos de mencionar. Por un lado quitamos la columna StoreType de la tabla DimStore, ya que uno de los objetivos consiste en contar el número de almacenes a cargo de cada director, independientemente del tipo de almacén. Dicho recuento lo realizaremos empleando una medida calculada (cláusula DEFINE) con el nombre NumeroAlmacenes, que reutilizaremos en más de un lugar de la consulta. La función FILTER, por otra parte, nos permitirá establecer la condición para obtener los gerentes a cargo de varios almacenes.

DEFINE
MEASURE 'DimStore'[NumeroAlmacenes] = COUNT('DimStore'[StoreKey])

EVALUATE
FILTER(
  SUMMARIZE(
    'DimStore',
    'DimGeography'[RegionCountryName],
    'DimEmployee'[FirstName],
    'DimEmployee'[LastName],
    "Total descuentos", SUM('FactSales'[DiscountAmount]),
    "Número almacenes gestionados", 'DimStore'[NumeroAlmacenes]
  ),
  'DimStore'[NumeroAlmacenes] > 1
)
ORDER BY [RegionCountryName],[FirstName],[LastName]

Utilizando SQL resulta necesario escribir una mayor cantidad de código para conseguir este mismo resultado, como vemos a continuación.

WITH
tblGerentes AS
(
  SELECT StoreManager, COUNT(*) AS NumeroAlmacenes
  FROM DimStore
  GROUP BY StoreManager
  HAVING COUNT(*) > 1
),
tblStore AS
(
  SELECT StoreKey, GeographyKey, DimStore.StoreManager, tblGerentes.NumeroAlmacenes
  FROM DimStore
  INNER JOIN tblGerentes
  ON DimStore.StoreManager = tblGerentes.StoreManager
)
SELECT RegionCountryName, StoreManager, FirstName, LastName,
SUM(DiscountAmount) AS [Total descuentos],
NumeroAlmacenes AS [Número almacenes gestionados]
FROM tblStore
INNER JOIN DimGeography
ON tblStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN DimEmployee
ON tblStore.StoreManager = DimEmployee.EmployeeKey
INNER JOIN FactSales
ON tblStore.StoreKey = FactSales.StoreKey
GROUP BY RegionCountryName, StoreManager, FirstName, LastName, NumeroAlmacenes
ORDER BY RegionCountryName, StoreManager, FirstName, LastName, NumeroAlmacenes

En la siguiente entrega daremos otra vuelta de tuerca al uso de la función SUMMARIZE, mostrando el modo de resolver consultas en las que se encuentren involucradas tablas no relacionadas.