En varias oportunidades me crucé con grandes modelos que tienen varias tablas de hecho. Lo cierto es que no siempre esas tablas van a compartir las mismas dimensiones y trabajar al mismo nivel de detalle. El caso más común que puede ocurrirnos es cuando la granularidad de fechas es distinta. Hace tiempo me vengo encontrando con dudas de cómo organizar el modelo cuando sucede eso. Tenemos tablas de hecho a detalle de día, otras a mes y otras a años. El problema es que luego quieren combinar los gráficos comparando valores y con cierto challenge que yo suelo hacer muchas soluciones propuestas no saben cómo resolver esta problemática.
Este artículo va a mostrar lo que NO se debe hacer del punto de vista de data modeling para comparar los valores en un gráfico y lo que SI debemos hacer para relacionar las distintas granularidades en nuestro modelo manteniendo la multi-estrella lo más liviana posible.
Contexto
Lo primero será definir el entorno de trabajo. Elegí algo sencillo pero que casualmente haría valer el resultado. Por un lado, tenemos las tablas de hecho InternetSales y PresupuestoCompra. Por otro lado, una clásica Dimensión Fecha (TablaFecha) construida en Power Query.
Nuestra tabla InternetSales es diaria, es decir que su granularidad se ve así:
Mientras que Presupuesto compra es mensual y se ve así:
Con el contexto definido veamos lo que deberíamos evitar y como proceder.
¿Qué NO hacer?
En búsquedas de solución a esta problemática podés caer en tentación de dos escenarios que no serían la mejor forma y podría traer problemas.
Relación muchos a muchos
Al tener una granularidad mayor a la diaria en la tabla hecho es normal que nuestra tabla fecha no tenga una fila para cada periodo. La tentación aquí es relacionar la columna periodo de Presupuesto con la TablaFecha de muchos a muchos dado que periodo en TablaFecha se repite. El problema aquí, además de una relación pesada y ambigüedad en el modelo, es que si no entendemos cómo funciona la relación, nuestras DAX devolverán números monstruosos dado que por cada periodo responderá con todos los días del mes. Entonces la agregación de nuestra medida podría fallar si no la sabemos controlar.
Perder la estrella
Esta forma se refiere a construir una segunda Tabla semejante a la de fecha, pero en granularidad de Año y Mes para conseguir el *-1 deseado entre Presupuesto y la TablaPeriodo. Luego esa Tabla nueva podría relacionarse con Fecha de 1-*. El problema aquí pasa por la direccionalidad de los filtros. Ahora la TablaFecha no es la controladora maestra de fechas, sino que al trabajar el agrupamiento año-mes, siempre deberías usar la nueva tabla. Como la TablaPeriodo no va día a día complicaría los cálculos de Time Intelligence. Además del peso adicional por una nueva tabla y relaciones en el modelo.
¿Qué hacer?
Mantener las tablas que tenemos y encontrar una alternativa para que solo tengamos UNA TablaFecha que guíe el contexto de filtros. Para poder hacer esto simplemente necesitaremos agregar una columna en las tablas de hecho que tengan una granularidad más alta como lo es en PresupuestoCompra (mensual). La nueva columna sería una de tipo fecha que muestre el primer día del periodo seleccionado. Entonces para un periodo 202005 tendríamos una fecha 2020-05-01. Repito periodo porque también funcionaría en tablas de hecho anuales. Para el año 2020 la nueva columna diría 2020-01-01. De este modo tenemos una columna de tipo fecha para relacionar con la de nuestra tabla calendario que va día por día.
Muchos podrían pensar que estoy equivocado porque ahora siempre ven datos para el primer día del mes en Presupuesto y no para los otros, pero es correcto porque nunca deberían ver la diaria de presupuesto dado que NO existe. No existen valores diarios entonces pensar en analizar una tabla mensual por día es un error conceptual. Siempre que se compare a Presupuesto, debe hacerse agrupando hasta meses contra la otra tabla.
Con esta solución nuestras DAX funcionarían de manera sencilla y podemos implementar Time Intelligence igual que siempre lo hicimos. Nuestro modelo pesa el mínimo posible y mantuvimos la multiestrella impecable.
Detalle de la relación:
Modelado:
Ojalá esto les sirva para operar en distintas granularidades de tablas de hecho y no tener que complejizar sus DAX.