Dimensión temporal en M

Una de las dimensiones críticas a la hora de crear un modelo de datos analítico es, sin duda, la dimensión temporal. En los últimos años, las herramientas de inteligencia de negocios suelen incluir una jerarquía temporal automática que se crea a partir de los campos de fecha presentes en el modelo. La pregunta es:

¿Necesito una tabla separada como dimensión temporal en el modelo de datos, o me vale con usar las jerarquías automáticas que me ofrecen estas herramientas?

La respuesta, en mi humilde opinión, es que es muy recomendable, independientemente de la complejidad del modelo de datos, tener una tabla separada como dimensión temporal. Las ventajas incluyen:

• Consistencia en el análisis de modelos con más de una tabla de hechos.
• Multitud de atributos bajo los que segmentar y filtrar los datos.
• Capacidad de utilizar las funciones de inteligencia de tiempo que nos ofrece el lenguaje DAX.

Dicho esto, en este artículo vamos a crear una dimensión temporal paso a paso con Power Query Formula Language o simplemente “M” para los amigos. ¡Allá vamos!

Función que creará la consulta

En primer lugar, crearemos una función que ejecute la consulta en vez de crear la consulta directamente. Esto nos proporcionará la capacidad de reutilizar la misma función en multitud de modelos de datos con distintas características, ya que podremos introducir una serie de parámetros clave que pasaremos a la función antes de que sea ejecutada.

Estos 3 parámetros de los que hablamos son:

• la fecha en la que se iniciará nuestro calendario (FechaInicial)
• la fecha en la que terminará (FechaFinal)
• la configuración regional sobre la que queremos que se evalúen ciertos campos como los nombres del mes (Cultura). Sobre este último parámetro hablaremos al final del artículo a la hora de llamar a nuestra función.

El código para la función es:

let CrearCalendario = (
    FechaInicial as date,
    FechaFinal as date, 
    optional Cultura as nullable text as table
  ) => in CrearCalendario

Todos los pasos que vamos a ir dando para crear nuestra consulta irán dentro del código anterior, tanto el bloque let (donde definiremos las variables), como el in (donde especificaremos el output o resultado de la consulta).

Base de la consulta

El primer paso es contar los días que hay entre nuestras fechas inicial y final, esto calculará el número de registros de nuestra tabla:

let
  NumeroDias = Duration.Days(Duration.From(FechaFinal - FechaInicial)) + 1,

Seguidamente, utilizaremos la función List.Dates() para generar una lista continua con todos los días que existen entre nuestras dos fechas. La sintaxis de List.Dates() es:

List.Dates(<Fecha inicial>,<nº de valores>,<incremento>)

En nuestro ejemplo, la fecha inicial la tenemos definida en los parámetros de la función que invocaremos para crear nuestra consulta y el número de valores a devolver por List.Dates() lo hemos definido en las variables de la propia consulta justo en el paso anterior. La duración será #duration(1,0,0,0) con lo que especificamos que el incremento para cada registro sea de 1 día. Los otros parámetros del tipo de dato duración son hora, minuto y segundo. Por lo tanto, el código para generar nuestro objeto de lista será:

Origen = List.Dates(FechaInicial,NumeroDias,#duration(1,0,0,0)),

El resultado de nuestra consulta hasta el momento es una lista de fechas, pero como nosotros necesitamos añadir más columnas vamos a transformar la lista en una tabla. Este proceso se puede realizar con el editor de consultas de Power BI:

O, simplemente, añadiendo el siguiente código a nuestra consulta en el editor avanzado:

TablaDesdeLista = Table.FromList(Origen, Splitter.SplitByNothing()),

Una vez tenemos nuestra tabla, cambiamos el tipo de dato de la columna a tipo Fecha y renombramos el campo. Yo lo llamaré simplemente Fecha.

Columnas adicionales

Ahora es cuestión de ir añadiendo las columnas que nos ofrecerán la posibilidad de filtrar y segmentar los datos de nuestro modelo. La mayoría de ellas podemos incorporarlas a nuestra tabla con el editor de consultas de la siguiente manera:

Este paso añade una columna con el año de cada fecha a nuestra consulta y nos genera el siguiente paso:

AñoInsertado = Table.AddColumn(ColumnasConNombreCambiado, "Año", each Date.Year([Fecha])),

Podemos entonces añadir las columnas que formarán parte de nuestra dimensión temporal y que usaremos tanto para filtrar y segmentar datos directamente como para usar en forma de parámetros en la creación de nuestras medidas con el lenguaje DAX. Algunos campos se utilizarán principalmente para ordenar otras columnas. Por ejemplo, para que el campo “Mes” se muestre ordenado cronológicamente en las visualizaciones tenemos previamente que ordenarlo por el campo “MesNumero” desde la vista de datos, como se muestra en la siguiente imagen:

El código para añadir el resto de columnas de nuestra dimensión temporal es:

 AñoInsertado = Table.AddColumn(ColumnasConNombreCambiado, "Año", each Date.Year([Fecha])),
 TrimestreInsertado = Table.AddColumn(AñoInsertado, "TrimestreNumero", each Date.QuarterOfYear([Fecha]), Int64.Type),
 MesInsertado = Table.AddColumn(TrimestreInsertado, "MesNumero", each Date.Month([Fecha]), Int64.Type),
 DiaInsertado = Table.AddColumn(MesInsertado, "DiaDelMes", each Date.Day([Fecha]), Int64.Type),
 FechaIntInsertado = Table.AddColumn(DiaInsertado, "FechaInt", each [Año] * 10000 + [MesNumero] * 100 + [DiaDelMes]),
 NombreMesInsertado = Table.AddColumn(FechaIntInsertado, "Mes", each Date.ToText([Fecha], "MMMM", Cultura), type text),
 MesEnCalendarioInsertado = Table.AddColumn(NombreMesInsertado, "MesDelAño", each (try(Text.Range([Mes],0,3)) otherwise [Mes]) & " " & Number.ToText([Año]),type text),
 TrimestreEnCalendarioInsertado = Table.AddColumn(MesEnCalendarioInsertado, "TrimestreDelAño", each "T" & Number.ToText([TrimestreNumero]) & " " & Number.ToText([Año]),type text),
 DiaEnSemanaInsertado = Table.AddColumn(TrimestreEnCalendarioInsertado, "DiaEnSemana", each Date.DayOfWeek([Fecha])+1),
 NombreDiaInsertado = Table.AddColumn(DiaEnSemanaInsertado, "Dia", each Date.ToText([Fecha], "dddd", Cultura), type text),
 InicioSemanaInsertado = Table.AddColumn(NombreDiaInsertado, "InicioSemana", each Date.StartOfWeek([Fecha]), type date),
 FinalSemanaInsertado = Table.AddColumn(InicioSemanaInsertado, "FinalSemana", each Date.EndOfWeek([Fecha]), type date),
 SemanaDelAñoInsertado = Table.AddColumn(FinalSemanaInsertado, "Semana", each "Semana " & Number.ToText(Date.WeekOfYear([Fecha])), type text),
 FinDeSemanaInsertado = Table.AddColumn(SemanaDelAñoInsertado, "FinDeSemana", each if [DiaEnSemana] = 6 or [DiaEnSemana] = 7 then "Fin de semana" else "Entre semana" ),

La mayoría de los campos anteriores pueden crearse con el editor de consultas como comentamos anteriormente. Para algunas de ellas como FechaInt o MesDelAño tendremos que escribir el código manualmente. Mención especial merece la fórmula de “DiaEnSemana”, ¿por qué le sumamos 1 al resultado de la función Date.DayOfWeek()? la respuesta es que en el lenguaje M, dicha función nos devuelve los valores desde un vector con base de indexación 0. Esto es inconsistente con cómo estamos acostumbrados los que hemos trabajado con Excel, ya que la función DIASEM() devuelve los valores desde un vector con base 1. Lo mismo ocurre con la función WEEKDAY() de DAX. Haciendo esta modificación obtendremos el valor 1 para el lunes y el 7 para el domingo en nuestra consulta.

Es muy común también la necesidad en muchos negocios de analizar algún valor agregado para el día de hoy o para el mes actual. Por lo que yo suelo añadir estas dos columnas también:

EsHoy = Table.AddColumn(FinDeSemanaInsertado, "EsHoy", each Date.IsInCurrentDay([Fecha])),
EsMesActual = Table.AddColumn(EsHoy, "EsMesActual", each Date.IsInCurrentMonth([Fecha])),

El lenguaje M tiene muchas más funciones de este tipo que usan la fecha y la hora del sistema y nos devuelven un valor lógico, como Date.IsInPreviousWeek() y Date.IsInCurrentYear() que pueden ser también muy interesantes como parte de la dimensión temporal en muchos negocios. En algunas ocasiones estas funciones pueden también ahorrarnos algo de código a la hora de trabajar con las funciones de inteligencia de tiempo de DAX.

El código completo y cómo utilizarlo

Para crear directamente la dimensión temporal a partir del código, simplemente desde la pestaña inicio del editor de consultas seleccionamos Nuevo Origen -> Consulta en blanco:

Y, posteriormente, con la nueva consulta seleccionada abrimos el editor avanzado, reemplazamos el código por defecto por el código completo de nuestra función, que podemos encontrar al final del artículo y pinchamos Listo:

El editor de consultas nos muestra ahora la definición de la función y un diálogo para que introduzcamos los parámetros necesarios.

Introducimos las fechas de inicio y final de nuestro calendario y en Cultura podemos introducir es-ES para seleccionar el idioma español. Este último parámetro es opcional y si lo dejamos en blanco se utilizará la configuración regional del inglés de EEUU. La información relativa a las configuraciones culturales disponibles puede obtenerse en esta documentación de MSDN.

Finalmente pinchamos en invocar y tendremos la consulta generada al instante. ¡Todo hecho!

Código completo

let CrearCalendario = (FechaInicial as date, FechaFinal as date, optional Cultura as nullable text) as table =>
   let
   NumeroDias = Duration.Days(Duration.From(FechaFinal - FechaInicial))+1,
   Origen = List.Dates(FechaInicial,NumeroDias,#duration(1,0,0,0)),
   TablaDesdeLista = Table.FromList(Origen, Splitter.SplitByNothing()),
   TipoCambiado = Table.TransformColumnTypes(TablaDesdeLista,{{"Column1", type date}}),
   ColumnasConNombreCambiado = Table.RenameColumns(TipoCambiado,{{"Column1", "Fecha"}}),
   AñoInsertado = Table.AddColumn(ColumnasConNombreCambiado, "Año", each Date.Year([Fecha])),
   TrimestreInsertado = Table.AddColumn(AñoInsertado, "TrimestreNumero", each Date.QuarterOfYear([Fecha]), Int64.Type),
   MesInsertado = Table.AddColumn(TrimestreInsertado, "MesNumero", each Date.Month([Fecha]), Int64.Type),
   DiaInsertado = Table.AddColumn(MesInsertado, "DiaDelMes", each Date.Day([Fecha]), Int64.Type),
   FechaIntInsertado = Table.AddColumn(DiaInsertado, "FechaInt", each [Año] * 10000 + [MesNumero] * 100 + [DiaDelMes]),
   NombreMesInsertado = Table.AddColumn(FechaIntInsertado, "Mes", each Date.ToText([Fecha], "MMMM", Cultura), type text),
   MesEnCalendarioInsertado = Table.AddColumn(NombreMesInsertado, "MesDelAño", each (try(Text.Range([Mes],0,3)) otherwise [Mes]) & " " & Number.ToText([Año]),type text),
   TrimestreEnCalendarioInsertado = Table.AddColumn(MesEnCalendarioInsertado, "TrimestreDelAño", each "T" & Number.ToText([TrimestreNumero]) & " " & Number.ToText([Año]),type text),
   DiaEnSemanaInsertado = Table.AddColumn(TrimestreEnCalendarioInsertado, "DiaEnSemana", each Date.DayOfWeek([Fecha])+1),
   NombreDiaInsertado = Table.AddColumn(DiaEnSemanaInsertado, "Dia", each Date.ToText([Fecha], "dddd", Cultura), type text),
   InicioSemanaInsertado = Table.AddColumn(NombreDiaInsertado, "InicioSemana", each Date.StartOfWeek([Fecha]), type date),
   FinalSemanaInsertado = Table.AddColumn(InicioSemanaInsertado, "FinalSemana", each Date.EndOfWeek([Fecha]), type date),
   SemanaDelAñoInsertado = Table.AddColumn(FinalSemanaInsertado, "Semana", each "Semana " & Number.ToText(Date.WeekOfYear([Fecha])), type text),
   FinDeSemanaInsertado = Table.AddColumn(SemanaDelAñoInsertado, "FinDeSemana", each if [DiaEnSemana] = 6 or [DiaEnSemana] = 7 then "Fin de semana" else "Entre semana" ),
   EsHoy = Table.AddColumn(FinDeSemanaInsertado, "EsHoy", each Date.IsInCurrentDay([Fecha])),
   EsMesActual = Table.AddColumn(EsHoy, "EsMesActual", each Date.IsInCurrentMonth([Fecha])),
   PrimeraEnMayusculas = Table.TransformColumns(EsMesActual,{{"Mes", Text.Proper, type text}, {"MesDelAño", Text.Proper, type text}, {"Dia", Text.Proper, type text}})
   in
   PrimeraEnMayusculas
in
 CrearCalendario