Reutilizar código de expresiones DAX en modelos de datos de PowerPivot

Una interesante característica del lenguaje DAX consiste en la posibilidad de aprovechar el código de una expresión perteneciente a una medida (campo calculado) como parte de otra expresión, con tan sólo invocar el nombre de la mencionada medida, lo que nos permite lograr un código más fácil de mantener, simple y legible.

 

El modelo de datos a utilizar

En el presente artículo vamos a exponer algunos ejemplos sobre la aplicación de esta cualidad del lenguaje, utilizando para ello un modelo de datos de PowerPivot construido a partir de algunas de las tablas integrantes de la base de datos AdventureWorksDW. Seguidamente se indican las tablas y columnas (o sentencias SQL en su caso) utilizadas en el asistente de importación durante la creación del modelo.

FactInternetSales
=================
ProductKey
OrderDateKey
CustomerKey
SalesTerritoryKey
SalesAmount
TaxAmt
 
DimTime
=================
TimeKey
CalendarYear
 
DimSalesTerritory
=================
SalesTerritoryKey
SalesTerritoryCountry
 
DimCustomer
=================
SELECT
CustomerKey,
CASE
WHEN MaritalStatus = 'S' THEN 'Soltero'
WHEN MaritalStatus = 'M' THEN 'Casado'
END AS EstadoCivil
FROM DimCustomer
 
CategoriaProducto
=================
SELECT
p.ProductKey
,pc.EnglishProductCategoryName
FROM DimProduct AS p
INNER JOIN DimProductSubcategory AS ps
ON p.ProductSubcategoryKey = ps. ProductSubcategoryKey
INNER JOIN DimProductCategory AS pc
ON ps.ProductCategoryKey = pc. ProductCategoryKey
 

La siguiente figura muestra el diagrama de nuestro modelo.

 

Medidas iniciales

Supongamos que entre las métricas a desarrollar en este modelo debemos calcular el número de clientes que han comprado productos, mostrando esta información por año de compra, con la particularidad de que cada cliente se contará una sola vez por año, independientemente de si en un año ha podido realizar más de un pedido. A pesar de la “aparatosidad” de este planteamiento, la solución pasa por algo tan sencillo como usar la función DISTINCTCOUNT sobre la columna CustomerKey de la tabla FactInternetSales, que realizará un recuento de los distintos valores de la columna pasada como parámetro. Para ello crearemos la medida NumClientes mostrada a continuación.

NumClientes :=
DISTINCTCOUNT ( FactInternetSales[CustomerKey] )

 

Mediante una tabla dinámica observaremos los valores resultantes, situando el campo CalendarYear en el área Filas y la medida recién creada en el área Valores.

 

Además de este cálculo también se nos solicita obtener el porcentaje anual de clientes que han efectuado pedidos, por lo que necesitamos realizar una operación que divida el número de clientes de cada anualidad entre el número total existente.

Para la medida encargada de devolver el total de clientes en todas las celdas de la tabla dinámica emplearemos una combinación de las funciones COUNTROWS y ALL.  ALL elimina de la tabla que recibe como parámetro los posibles filtros (contexto de filtro) que puedan estar activos, o dicho de otro modo, devuelve la tabla completa. Por otro lado, COUNTROWS hace un recuento del número de filas de la tabla que recibe como parámetro.

NumClientesTotal :=
COUNTROWS ( ALL ( FactInternetSales[CustomerKey] ) )

 

 

Respecto al porcentaje de clientes usaremos la función DIVIDE, que como su nombre indica, realizará una división entre los dos primeros valores (numerador y denominador) pasados como parámetro, los cuales corresponderán a las expresiones empleadas en las dos medidas creadas anteriormente.

PorcentajeClientes :=
DIVIDE (
    DISTINCTCOUNT ( FactInternetSales[CustomerKey] ),
    COUNTROWS ( ALL ( FactInternetSales[CustomerKey] ) )
)

 

Para que esta medida se visualice como un porcentaje, en la ventana de PowerPivot, dentro del menú Inicio, grupo Formato, haremos clic en el icono de porcentaje, o seleccionaremos dicho valor en la lista desplegable Formato.

 

Esta forma de crear la medida, aunque válida, sería la menos recomendable, puesto que en cada parámetro de DIVIDE estamos repitiendo el código de unas expresiones ya utilizadas anteriormente; así que como explicábamos al comienzo del artículo, vamos a aprovechar la funcionalidad de las medidas ya construidas, pasando sus nombres como parámetros a la función DIVIDE, en un estilo similar al empleado en las llamadas a funciones o procedimientos existente en otros lenguajes de programación.

PorcentajeClientes :=
DIVIDE ( [NumClientes], [NumClientesTotal] )

 

Incorporando esta nueva medida a la tabla dinámica obtendremos la información que se nos solicitaba.

 

Una vez demostradas sus ventajas, vamos a continuar extendiendo esta técnica para conseguir una estructura de medidas interdependientes.

 

Medidas adicionales. Continuando la reutilización

Supongamos que se nos plantea un nuevo requerimiento sobre el modelo de datos que estamos desarrollando, consistente en contar, a partir del número de clientes que hemos calculado previamente, cuántos están casados, así como el porcentaje que representan con respecto al total de clientes existentes.

Revisando la sentencia de importación de datos para el modelo de PowerPivot correspondiente a la tabla DimCustomer, observaremos la creación dinámica de una columna con el nombre EstadoCivil, conteniendo el dato que necesitamos para filtrar a los clientes casados.

 

A continuación crearemos una medida para contar el número de clientes casados. Recordemos que la operación de recuento de clientes ya la tenemos resuelta en la medida NumClientes.

NumClientesCasados :=
CALCULATE ( [NumClientes], DimCustomer[EstadoCivil] = "Casado" )

 

La función CALCULATE evalúa la expresión situada en el primer parámetro, pero aplicando al resultado uno o varios filtros pasados a partir del segundo parámetro, alterando por tanto el contexto de filtro previamente existente. Esto es lo que nos permite en esta medida contar el número de clientes distintos, pero sólo para aquellos en los que el campo EstadoCivil, de la tabla DimCustomer, tenga el valor “Casado”.

Por otro lado, para calcular el porcentaje de casados, en el numerador usamos la medida recién creada con el número de clientes casados, y en el denominador la medida NumClientesTotal, que devuelve el número total de clientes.

PorcentajeClientesCasados :=
DIVIDE ( [NumClientesCasados], [NumClientesTotal] )

 

Si hubiéramos elegido la vía difícil, sin reutilizar el código existente, el código de las medidas sería el mostrado a continuación.

NumClientesCasados :=
CALCULATE (
    DISTINCTCOUNT ( FactInternetSales[CustomerKey] ),
    DimCustomer[EstadoCivil] = "Casado"
)

//-----------------------------------------------------

PorcentajeClientesCasados :=
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( FactInternetSales[CustomerKey] ),
        DimCustomer[EstadoCivil] = "Casado"
    ),
    COUNTROWS ( ALL ( FactInternetSales[CustomerKey] ) )
)

 

Comparando ambas versiones de estas medidas, comprobaremos rápidamente la ventaja que supone reutilizar el código.

Para analizar más adecuadamente este nuevo conjunto de métricas, añadiremos una nueva tabla dinámica al archivo Excel que estamos usando como ejemplo, lo cual nos permitirá comparar las cifras entre las diferentes medidas.

 

Número de clientes por categoría. Aumentando la secuencia de medidas

Como eslabón final en esta cadena de medidas, expongamos un nuevo requerimiento a implementar en el modelo de datos, consistente en calcular, a partir de la medida del número de clientes casados, cuántos en este grupo han efectuado compras de productos pertenecientes a la categoría Bikes, e igualmente, el porcentaje que representan con respecto del total.

En este caso deberemos aplicar un filtro mediante el campo del modelo de datos EnglishProductCategoryName, perteneciente a la tabla CategoriaProducto, siguiendo un patrón similar al empleado en el apartado anterior. Debido a que parte de los cálculos a realizar ya están resueltos en las medidas previas NumClientesCasados y NumClientesTotal, utilizaremos estas dentro del código de las nuevas medidas a crear.

NumClientesCasadosCategBikes:=CALCULATE(
    [NumClientesCasados],
    CategoriaProducto[EnglishProductCategoryName] = "Bikes"
)

//---------------------------------------------------------
PorcentajeClientesCasadosCategBikes:=DIVIDE(
    [NumClientesCasadosCategBikes],
    [NumClientesTotal]
)

 

Si hubiéramos optado por no reutilizar las medidas existentes, el código a escribir hubiera sido, en comparación, más difícil de mantener y leer, como vemos a continuación.

NumClientesCasadosCategoria:=CALCULATE(
    DISTINCTCOUNT(FactInternetSales[CustomerKey]);
    DimCustomer[EstadoCivil]="Casado";
    CategoriaProducto[EnglishProductCategoryName]="Bikes"
)

//-----------------------------------------------------------
PorcentajeClientesCasadosCategoria:=DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(FactInternetSales[CustomerKey]);
        DimCustomer[EstadoCivil]="Casado";
        CategoriaProducto[EnglishProductCategoryName]="Bikes"
    );
    COUNTROWS(ALL(FactInternetSales[CustomerKey]))
)

 

También ahora añadiremos a nuestro archivo Excel otra tabla dinámica para poder comparar estas medidas con el resto de las creadas

Propagación de cambios desde una medida base

La propagación de cambios en una estructura de medidas como la que estamos desarrollando actualmente se fundamenta en el hecho de que una modificación realizada en una de las medidas situadas en la base desencadena una actualización en el resto de medidas de la estructura que guardan relación o dependen de aquella que ha sufrido el cambio.

Supongamos que los responsables de ventas de la compañía necesitan analizar los resultados ofrecidos por las medidas que hemos desarrollado hasta este momento, pero solamente para los pedidos realizados por clientes residentes en Reino Unido (valor “United Kingdom” del campo SalesTerritoryCountry de la tabla DimSalesTerritory), siendo además un requerimiento temporal, por lo que las medidas, una vez efectuados los análisis correspondientes, deberán volver a mostrar los valores originalmente calculados.

 

Como salvedad, hemos de indicar que la medida NumClientesTotal no debe sufrir alteraciones, ya que interesa seguir calculando el porcentaje de clientes en base a su cifra total, sin diferenciar el país de residencia.

Gracias a que el sistema de medidas que hemos construido en el modelo de datos emplea las propiedades de reutilización explicadas a lo largo del artículo, únicamente hemos de modificar una de ellas: NumClientes, para obtener las nuevas cifras recalculadas, no sólo sobre dicha medida, sino también sobre el resto.

NumClientes:=CALCULATE(
    DISTINCTCOUNT(FactInternetSales[CustomerKey]);
    DimSalesTerritory[SalesTerritoryCountry] = "United Kingdom"
)

 

Al volver a la hoja de Excel en la que hemos situado las tablas dinámicas comprobaremos que, con excepción de la medida NumClientesTotal, los valores han cambiado, adaptándose al nuevo filtro que acabamos de añadir.

 

La siguiente figura muestra la estructura de medidas que hemos construido, de forma que el lector pueda apreciar gráficamente las relaciones existentes entre las mismas, y cómo el cambio realizado en NumClientes afecta a las demás medidas dependientes.

 

Ahora bien, a pesar de haber dicho que no haremos modificaciones en la medida NumClientesTotal, de forma que los porcentajes se calculen sobre el total global de clientes, ¿qué ocurriría si quisiéramos que dichos porcentajes se calcularan solamente para el total de clientes de Reino Unido?

Al igual que en el caso anterior, aquí también será necesario cambiar una sola de las medidas del modelo: NumClientesTotal, para lograr que un nuevo cálculo de porcentajes se realice, cambiando drásticamente los valores de la tabla.

NumClientesTotal:=CALCULATE(
    [NumClientes];
    ALL(DimTime[CalendarYear])
)

 

Dado que ya hemos tratado con anterioridad estas funciones, simplemente diremos que CALCULATE aplica a la expresión del primer parámetro (recuento del número de clientes en Reino Unido) el filtro pasado en el segundo: todos los valores del campo CalendarYear de la tabla DimTime, mediante la función ALL.

Compare el lector las diferentes versiones de las tablas dinámicas, comprobando cómo unos mínimos cambios afectan profundamente a los resultados obtenidos.

 

Conclusión

El uso de las técnicas de reutilización del código escrito en lenguaje DAX para la creación de las medidas de un modelo de datos de PowerPivot supone una mejora en la productividad del desarrollador, redundando en una mayor facilidad de mantenimiento de dichas medidas a la hora de que estas necesiten modificaciones. Esperamos que los ejemplos mostrados en el presente artículo resulten de utilidad al lector.