Introducción
Uno de los tipos de tabla que nos encontramos frecuentemente a la hora de crear un modelo de datos analítico corresponde a las tablas «desde-hasta» o de intervalo de fechas. Estas son un tipo de estructura muy versátil que utilizan las bases de datos para representar y gestionar información relacionada con valores aplicables entre determinados periodos de tiempo.
Sus usos son muy variados, ya que este tipo de estructura proporciona un almacenamiento y procesamiento altamente eficientes en un sistema transaccional. Almacenar de esta forma los datos minimiza la redundancia y optimiza el almacenamiento al eliminar la necesidad de duplicar registros para cada instancia de tiempo. Algunos de los escenarios en los que nos encontraremos con este tipo de tablas son:
- Sistemas de reservas y programación de eventos
- Gestión de RRHH
- Planificación de campañas de Marketing
- Gestión de contratos
- Listados de tarifas
- Gestión de casos e incidencias
En este artículo vamos a explorar las distintas formas de modelar y analizar este tipo de tablas, teniendo en consideración aspectos relacionados con el rendimiento de las consultas y el tamaño de los datos almacenados.
Usando Power Query para expandir la tabla
En caso de que la tabla en cuestión sea muy ligera podemos implementar una solución sencilla que consiste en expandir la tabla, de modo que tengamos una fila para cada día entre las fechas de inicio y final del rango temporal asociado a cada registro. Para ello, creamos una nueva columna que contendrá, para cada celda, una lista de las fechas que existen entre los dos extremos del rango:
= Table.AddColumn(#"Otras columnas quitadas", "Fecha",
each {
Number.From([FechaInicio])..Number.From([FechaFin])
})A continuación, solo nos queda expandir las listas en nuevas filas y cambiar el tipo de dato de la columna resultante a Fecha, y ya tendremos el resultado deseado:
Si utilizamos esta técnica hay que tener en cuenta que en ocasiones las fechas de inicio o de fin pueden contener valores null, por lo que tendremos que reemplazarlos por una fecha que consideremos que cumple con los requisitos de negocio. Esto podemos realizarlo antes de llevar a cabo el paso anterior o directamente en el código del mismo. Por ejemplo, en el siguiente código, para añadir una columna comprobamos si la fecha final es null y en tal caso le asignamos a la variable FechaFin la fecha actual. Posteriormente, generamos las listas de fechas que expandiremos en nuevas filas para obtener el resultado deseado:
let
_FechaInicio = [FechaInicio],
_FechaFin = if [FechaFin] = null
then Date.From(DateTime.LocalNow())
else [FechaFin]
in
List.Generate(()=>
[x=_FechaInicio], each [x]<=_FechaFin,
each [x=Date.AddDays([x],1)],each [x]))Como hemos comentado, estas técnicas solo son válidas si la tabla con la que estamos trabajando es realmente pequeña. El hecho de crear una columna con la lista de fechas asociadas a cada registro es una transformación que nunca hará Query Folding sobre el origen de datos, por lo que tenemos que ser muy cuidadosos y solo aplicarla en casos muy concretos.
Usando DAX para analizar sobre la tabla de intervalos de fecha
En caso de que estemos trabajando con una tabla que contenga un número importante de registros y no podamos «aplanarla» en el origen de datos, las técnicas anteriores no van a ser aplicables, al menos sin pagar un altísimo precio en tiempo de actualización de la consulta.
Para estos casos podemos utilizar perfectamente el lenguaje DAX para analizar nuestros datos sin necesidad de transformar previamente la tabla de intervalos de fecha.
Para desarrollar el ejemplo vamos a utilizar un caso muy común, las listas de precios por cliente, artículo y rango temporal, y vamos a introducir alguna complejidad adicional.
Imaginemos el siguiente escenario. Disponemos de una lista de precios, con mas de 1 millón de registros, en la que se detalla el precio asociado a cada cliente y artículo en un periodo de tiempo. La tabla también dispone de registros en los que se especifica un precio único para el cliente y el rango temporal, sin especificar ningún artículo.
El requisito de negocio consiste en obtener con una medida, es decir, sin necesidad de materializar ningún cálculo en nuestro modelo, la media aritmética del precio, teniendo en cuenta que si existe un precio para un cliente y un rango temporal tanto con artículo como sin artículo, se tomará como válido el que si dispone de artículo, y en caso contrario, el que no tiene artículo asociado.
Podemos ver el modelo con el que trabajamos en la siguiente imagen. Cabe resaltar que no hemos establecido ninguna relación entre la tabla de tarifas y la dimensión temporal:
Vamos a visualizar el código completo que resuelve nuestro requisito de negocio, y a continuación lo comentamos:
Precio medio =
AVERAGEX (
FILTER (
'Ventas',
'Ventas'[CategoriaEntrada] = "ActualSales"
),
VAR _FechaLinea = 'Ventas'[Fecha]
VAR _Cliente = 'Ventas'[IdCliente]
VAR _Articulo = 'Ventas'[IdArticulo]
VAR _PrecioConArticulo =
CALCULATE (
AVERAGE ( 'Tarifas'[Precio] ),
FILTER (
ALL (
'Tarifas'[FechaInicio],
'Tarifas'[FechaFin]
),
'Tarifas'[FechaInicio] <= _FechaLinea
&& 'Tarifas'[FechaFin] >= _FechaLinea
),
'Tarifas'[TipoRegistro] = "Con Articulo"
)
VAR _PrecioSinArticulo =
CALCULATE (
AVERAGE ( 'Tarifas'[Precio] ),
FILTER (
ALL (
'Tarifas'[FechaInicio],
'Tarifas'[FechaFin]
),
'Tarifas'[FechaInicio] <= _FechaLinea
&& 'Tarifas'[FechaFin] >= _FechaLinea
),
'Tarifas'[TipoRegistro] = "Sin Articulo",
CROSSFILTER ( 'Artículos'[IdArticulo],
'Tarifas'[IdArticulo],
NONE
)
)
RETURN
IF (
NOT ISBLANK ( _PrecioConArticulo ),
_PrecioConArticulo,
_PrecioSinArticulo
)
)Con este código lo que hacemos es, en primer lugar, crear un contexto de fila en la tabla de Ventas mediante el iterador AVERAGEX, y filtramos dicha tabla para solamente tener en cuenta los registros de las ventas reales, ya que en la tabla se han integrado también las previsiones.
Seguidamente, definimos algunas variables que utilizaremos en _PrecioConArticulo y _PrecioSinArticulo; variables en las que buscamos los precios asociados a la fecha del registro de la venta, filtrando la tabla de tarifas con la condición de que dicha fecha se encuentre entre los valores de FechaInicio y FechaFin. Cabe enfatizar que en estas variables no hemos tenido que añadir las condiciones que filtran la tabla para el cliente y el artículo asociados al registro actual; ya que como estamos ejecutando la función CALCULATE en un contexto de fila, la transición del contexto que se produce y que transforma el contexto de fila en un contexto de filtro equivalente, propaga los filtros desde ambas dimensiones a la tabla de tarifas.
Es muy importante notar que, en el caso de la variable _PrecioSinArticulo, tenemos que utilizar la función CROSSFILTER para desactivar la relación física establecida a través de la clave primaria de la dimensión Artículos.
Finalmente, devolvemos el _PrecioConArticulo en el caso de que este exista, y en caso contrario, el _PrecioSinArticulo.
De esta forma, podremos usar esta medida en cualquier contexto sin problemas, y sin necesidad de transformar la tabla ni materializar ninguna columna calculada en nuestro modelo:
En el próximo artículo veremos como llevar esta técnica un poco más allá utilizando la función TREATAS para establecer relaciones virtuales en el caso de que existan registros de precios que no dependan de las columnas de relación, en este caso el IdCliente y el IdArticulo, sino que utilicen otros atributos dimensionales para la asignación del precio.
Todo el código DAX de este artículo ha sido formateado con «DAX Formatter»
Deja un comentario