La guía definitiva sobre la Dimension Tiempo en un Data Warehouse

Obtén el máximo valor de la Dimensión Tiempo (tratamiento de Fechas y Horas) en tu solución de BI & Analytics. Incluyo scripts que puedes utilizar.

La Dimensión Tiempo suele encontrarse en la inmensa mayoría de nuestros proyectos y siempre estará presente en nuestro Data Warehouse.

Tratémosla como se merece y seremos recompensados en nuestros análisis de información.

En este artículo vamos a ver con todo detalle cómo diseñarla e implementarla.

Dimensiones Fecha y Hora para perfiles de IT y usuarios de Power BI y otras herraminentas

Si tu perfil no es de TI y no estás familiarizado con el lenguaje SQL, también te interesa leer este artículo, ya que todo lo que aquí se explica debes conocerlo y aplicarlo, pide a tu departamento de TI que te genere estas tablas en una base de datos para que tú puedas utilizarlas.

Además, se puede aplicar creando estas tablas con Excel, ya que realmente partimos de un rango de fechas que se puede generar fácilmente arrastrando a partir de una fecha. Y prácticamente, lo que viene a continuación, se obtiene aplicando funciones de fecha (el mes, el día de la semana, etc.) e introduciendo datos.

Introducción

1. Diseñar las tablas y columnas que va a tener, y su contenido
2. Elegir su granularidad
3. Elegir el proceso de carga
4. Decidir la frecuencia de carga

Diseño de la dimensión Tiempo (Fechas y Horas)

Nota: No dejes de leer los comentarios insertados a lo largo de los diversos bloques de código, donde también se incluyen alternativas a tener en cuenta, según el diseño que realicemos, para cada uno de nuestros proyectos. Se detallan los formatos en los que irá la información almacenada, y se hace referencia a alternativas según la versión de SQL Server utilizada.

Tabla de dimensión Fecha

Tabla de dimensión Hora

T-SQL 2. Estructura de la tabla de dimensión Hora

** Quiero agradecer a Jordi Rambla, creador del script en el que me basé para construir estos.

Como has podido comprobar, hemos dejado nuestras tablas diseñadas para registrar un nivel de granularidad hasta el nivel de segundos (muy rara vez podríamos necesitar milisegundos, pero si se diese el caso también se podría con-templar). Independientemente de la granularidad que vayamos a utilizar, podemos dejar nuestras tablas diseñadas de forma que incluyan columnas que describan ese máximo nivel.

Elegir la granularidad de cada tabla

La granularidad de la dimensión tiempo puede ser variada según la tabla de hechos con la que se vaya a relacionar. En su diseño, deberemos tener en cuenta el máximo nivel que vayamos a necesitar y además tener columnas que nos permitan acceder a un nivel incluso menor del requerido en ese momento.

Imaginemos que tenemos en nuestro Data Warehouse tablas de hechos con la información de Presupuestos, Ventas y Producción. Los presupuestos los realizamos a nivel mensual, mientras en el análisis de Producción los requisitos nos indican que el máximo nivel de detalle que necesitaremos será el día. En cambio, en Ventas, necesitaremos saber hasta el detalle de la hora y el minuto en que se realizó cada operación. En este caso nuestra Dimensión Tiempo deberá tener información detallada hasta el nivel de minutos. Ello no impide que la estructura de mi tabla esté preparada para soportar una granularidad de segundos, y que con un simple cambio en el proceso de ETL que alimentada con dicha información, como veremos más adelante.

Elegir el proceso de carga

Una vez que tenemos diseñada nuestra tabla y estudiados los posibles niveles de granularidad que vamos a necesitar, debemos proceder a realizar un proceso ETL que permita alimentar estas tablas.

• Hay diversas formas de realizar el proceso para la carga de la Dimensión Tiempo, estas son algunas de las más comunes:
  Realizarlo mediante T-SQL. Hacer un procedimiento almacenado que inserte una fila para una fecha. Luego se hace un bucle con T-SQL que recorra un rango de fechas seleccionado y que por cada una de estas fechas llame al procedimiento almacenado y la inserte
• Realizarlo mediante Integration Services. Utilizaremos un bucle For Loop, en el que por cada fecha, llamaremos a un Data Flow en el que haremos los cálculos de las columnas derivadas que necesitemos para esa fecha e insertaremos la fila correspondiente a esa fecha
• Si eres un usuario que no tiene conocimientos de SQL, también la puedes crear con Excel

Otra cosa a tener en cuenta, es que dada la poca frecuencia con la que se suele ejecutar este proceso de carga, igual una vez al año o incluso nunca (casi nunca). Además, cuando ejecutemos estos procesos el número de filas afectadas no será muy grande. Esto hace que no sea muy significativo el método de carga utilizado, si es con Integration Services, si es con T-SQL, si usa cursores (aunque nunca lo recomendaría), etc.

Tenga en cuenta que lo visto anteriormente son simplemente aproximaciones, y podemos hacer sobre ellas las variaciones que estimemos oportunas. Podemos no utilizar procedimientos almacenados e incluir en el script todos los cálculos de columnas. Podemos utilizar una CTE en lugar de un bucle, o cualquier otra variante que estimemos oportuna.

Carga de la dimensión Tiempo basada en T-SQL

Veamos a continuación un ejemplo de carga basado en instrucciones T-SQL, que nos puede servir de base para la carga de nuestra dimensión tiempo, simplemente con añadir nuevos cálculos para las columnas que finalmente incluyamos en el diseño de nuestra dimensión. En este caso vamos a considerar que la máxima granularidad de nuestra tabla será el día, y vamos a llamar a dicha tabla Dimension.Fecha, partiendo inicialmente de muy pocas columnas, para más adelante, cuando hagamos su diseño definitivo, incorporar las columnas que estimemos oportunas.

Bucle de carga de Fechas

declare @StartDate datetime, @EndDate datetime
--Deberá ser la fecha más antigua de la que tengamos datos

set @StartDate = '19900101'

--Podemos bien dejar cargado un periodo muy amplio
--set @EndDate = '21001231'
--O bien, cargar los cinco próximos años completos
set @EndDate = cast(cast(year(getdate()) + 5 as char(4)) + '1231' as datetime)
while @StartDate <= @EndDate begin
   exec InsertarFecha @StartDate
   -- Podemos incrementar contador en años, meses o días
   -- poniendo year, month o day en la función dateadd
   set @StartDate = dateadd(day,1,@StartDate)
   -- Lo más habitual es tener la granularidad día
end

T-SQL 3. Bucle de carga de un rango de fechas, según granularidad indicada en el incremento de la variable @startdate

Procedimiento almacenado de carga de cada fila en la tabla Fecha

create procedure InsertarFecha
   @CurrentDate datetime
as
insert into Dimension.Fecha([DateKey], [Date],
       [DayNumberOfWeek], [EnglishDayNameOfWeek], [DayNumberOfMonth],
       [DayNumberOfYear], [WeekNumberOfYear], [EnglishMonthName],
       [MonthNumberOfYear], [CalendarQuarterOfYear], [CalendarYear],
       [CalendarSemesterOfYear], [CalendarYearWeek], [CalendarYearMonth],
       [CalendarYearQuarter], [CalendarYearSemester])
   values(
      (DATEPART(year , @CurrentDate) * 10000) + (DATEPART(month , @CurrentDate)*100)
       + DATEPART(day , @CurrentDate)
      , @CurrentDate
      , DATEPART(dw , @CurrentDate)
      , DATENAME(dw, @CurrentDate)
      , DATEPART(day , @CurrentDate)
      , DATEPART(dayofyear , @CurrentDate)
      , DATEPART(wk , @CurrentDate)
      , DATENAME(month, @CurrentDate)
      , DATEPART(month , @CurrentDate)
      , DATEPART(quarter , @CurrentDate)
      , DATEPART(year , @CurrentDate)
      , CASE WHEN DATEPART(quarter , @CurrentDate) < 3 THEN 1
           ELSE 2
        END
      , CAST(DATEPART(year , @CurrentDate) as char(4)) + '-'
        + RIGHT('0'+CAST(DATEPART(wk , @CurrentDate) AS varchar(2)),2)
      , CAST(DATEPART(year , @CurrentDate) as char(4)) + '-'
        + RIGHT('0'+CAST(DATEPART(month , @CurrentDate) AS varchar(2)),2)
      , CAST(DATEPART(year , @CurrentDate) as char(4)) + '-'
        + CAST(DATEPART(quarter , @CurrentDate) AS varchar(1))
      , CAST(DATEPART(year , @CurrentDate) as char(4)) + '-'
        + CAST(CASE WHEN DATEPART(quarter , @CurrentDate) < 3 THEN 1
                  ELSE 2
               END AS char(2))
   )
GO

T-SQL 4. Procedimiento almacenado que inserta una fila, calculando columnas derivadas elegidas, para la fecha recibida como parámetro

Actualizaciones para personalizar

A continuación mostramos la actualización de algunas columnas en función del idioma al que hacen referencia. Veremos que para algunas de ellas podemos aprovechar la propia configuración del lenguaje establecida en SQL Server, pero para otras debemos hacer nosotros esta tarea al no estar ese idioma disponible.

-- Update non-english language names
-- .. SPANISH / Spain / es-ES
SET LANGUAGE Spanish

           UPDATE Dimension.Fecha
           SET SpanishDayNameOfWeek = DATENAME(dw, [Date])
             , SpanishMonthName = DATENAME(month, [Date])
           WHERE Date >= @StartDate AND Date<= @EndDate            -- Reset the language            SET LANGUAGE us_english      -- .. CATALAN            UPDATE Dimension.Fecha            SET CatalanDayNameOfWeek =                 CASE DayNumberOfWeek                       WHEN 2 THEN 'Dilluns'                       WHEN 3 THEN 'Dimarts'                       WHEN 4 THEN 'Dimecres'                       WHEN 5 THEN 'Dijous'                       WHEN 6 THEN 'Divendres'                       WHEN 7 THEN 'Dissabte'                       WHEN 1 THEN 'Diumenge'                 END,                CatalanMonthName =                 CASE MonthNumberOfYear                       WHEN 1 THEN 'Gener'                       WHEN 2 THEN 'Febrer'                       WHEN 3 THEN 'Març'                       WHEN 4 THEN 'Abril'                       WHEN 5 THEN 'Maig'                       WHEN 6 THEN 'Juny'                       WHEN 7 THEN 'Juliol'                       WHEN 8 THEN 'Agost'                       WHEN 9 THEN 'Setembre'                       WHEN 10 THEN 'Octubre'                       WHEN 11 THEN 'Novembre'                       WHEN 12 THEN 'Desembre'                 END            WHERE Date >= @StarDate AND Date<= @EndDate

T-SQL 5. Asignación según idioma a mostrar en esa columna

Nota: Por no hacer más largo el artículo, en estos bloques de código no se incluye todo el código necesario para todas las columnas de la tabla Dimiension.Fecha, hay cálculos de columnas que no están incluidos aquí. Dejamos al lector la labor de definir las columnas que estime oportunas y realizar los cálculos correspondientes.

Anteriormente sólo hemos llegado al nivel de granularidad de fecha, es decir, de una fila por cada día. Si queremos bajar a mayor detalle tendríamos que tener niveles de horas, minutos, segundos, o incluso menores. En este caso, lo que vamos a hacer es crear una tabla llamada Dimension.Hora en la que tendre-mos sólo las horas, sin las fechas, y veremos también su proceso de carga, basado en un bucle que recorre las veinticuatro horas del día y carga dicha tabla, llaman-do a un procedimiento almacenado que obtiene todas las columnas derivadas que estimemos oportunas.

Bucle de carga de hora

declare @Time datetime
set @Time = convert(varchar,'12:00:00 AM',108)

while @Time <= '11:59:59 PM' begin
   exec InsertarHora @Time
   -- Podemos incrementar contador en Horas, Minutos o Segundos
   -- poniendo hour, minute o second en la función dateadd
   set @Time = dateadd(hour,1,@Time)
end

T-SQL 6. Bucle que carga las 24 horas del día, según granularidad indicada en el incremento de la variable @Time

Procedimiento almacenado de carga de cada fila en la tabla Hora

create procedure InsertarHora
   @Time datetime
as
   declare @IdHora int, @Hora tinyint,
           @Minuto tinyint, @Segundo tinyint, @AM char(2),
           @NombreHora char(2), @NombreHoraAM char(5),
           @NombreMinuto char(2), @NombreSegundo char(2),
           @HoraMinuto char(5), @HoraMinutoAM char(8),
           @HoraMinutoSegundo char(8), @HoraMinutoSegundoAM char(11)

   --Calcular columnas mínimas necesarias
   set @Hora = datepart(hour, @Time)
   set @Minuto = datepart(minute, @Time)
   set @Segundo = datepart(second, @Time)
   set @IdHora = (@Hora*10000) + (@Minuto*100) + @Segundo 
       --Se puede dejar tanto para granularidad minutos como
       --segundos, o bien
       --(@Hora*100) + @Minuto, para granularidad minutos,
       --pero luego no habría huecos para aumentar la
       --granularidad a segundos
       --@Hora, para granularidad horas, pero luego no habría
       -- huecos para aumentar la granularidad a minutos o segundos
   --Aquí se agregarán todos los cálculos en base a esa hora
   --que estimemos oportunos
   --Mostramos algunos de los formatos más habituales,
   --pero no su cálculo, sino que le asignamos un valor fijo
   --a modo de ejemplo
   set @AM = right(convert(varchar,@Time,109),2) -- AM/PM
   set @NombreHora = '00'        -- HH con ceros por la izquierda
   set @NombreHoraAM = '12 AM'   -- HH AM las 00 son las 12 AM
   set @NombreMinuto = '00'      -- MM con ceros por la izquierda
   set @NombreSegundo = '00'     -- SS con ceros por la izquierda
   set @HoraMinuto = '00:00'     -- HH:MM con ceros por la izquierda
   set @HoraMinutoAM = '12:00 AM'
   -- HH:MM AM las 00:00 son las 12:00 AM
   set @HoraMinutoSegundo = convert(varchar, @Time, 108) -- HH:MM:SS
   set @HoraMinutoSegundoAM = '12:00:00 AM'
   -- HH:MM:SS AM las 00:00:00 son las 12:00:00 AM

   --Insertar fila
   insert into Dimension.Hora(IdHora, Tiempo, Hora, Minuto, Segundo,
          AM, NombreHora, NombreHoraAM, NombreMinuto, NombreSegundo,
          HoraMinuto, HoraMinutoAM, HoraMinutoSegundo,
          HoraMinutoSegundoAM)
      select @IdHora, @Time, @Hora, @Minuto, @Segundo, @AM,
             @NombreHora, @NombreHoraAM, @NombreMinuto,
             @NombreSegundo, @HoraMinuto, @HoraMinutoAM,
             @HoraMinutoSegundo, @HoraMinutoSegundoAM
GO

T-SQL 7. Procedimiento almacenado que inserta una fila, calculando las columnas derivadas elegidas, para la hora recibida como parámetro

Nota: El procedimiento almacenado anterior no está terminado por completo, hay cálculos de columnas que no están realizados, y que graban un mismo valor para todas las filas a modo de ejemplo. Dejamos al lector la labor de definir las columnas que estime oportunas y realizar los cálculos correspondientes.

Por qué separar en dos tablas fecha y hora

Como ha podido comprobar, hemos hecho un tratamiento del tiempo basado en dos tablas, por un lado el tratamiento de Fechas y por otro lado el tratamiento de Horas. Si no es su caso y necesita tener una sola tabla, con la granularidad que estime oportuna, bien puede adaptar el material anterior y cambiar el diseño para que el nivel de granularidad sea mayor y que todo gire en torno a una sola tabla, o bien, puede mantenerlo, y mediante una CROSS JOIN puede obtener el resultado de una sola tabla que incluya el producto cartesiano de ambas tablas.

select * --Puede seleccionar aquí las columnas que estime oportunas
from Dimension.Fecha cross join Dimension.Hora

T-SQL 8. CROSS JOIN entre las tablas Fecha y Hora

Incluso podemos mejorar para crear la clave subrogada de la dimensión de forma inteligente, conteniendo la fecha y la hora.

select
     cast(cast(Datekey as bigint)*1000000 + IdHora as bigint) IdTime
   , * --Puede seleccionar aquí las columnas que estime oportunas
from Dimension.Fecha cross join dimension.Hora
order by DateKey, IdHora

T-SQL 9. CROSS JOIN y creación de la clave inteligente con fecha y hora, formato: AAAAMMDDhhmmss

Mi recomendación, es crear tablas separadas en el tratamiento del Tiempo, una para Fechas y otra para Horas. Queda más claro para el lector, es más fácil de gestionar y almacenamos un menor número de filas. Además, si alguien considera oportuno unirlas, puede hacerlo fácilmente con las cross join que acabo de mostrar. Deberá tener en cuenta que según la granularidad elegida, el número de filas de la tabla puede verse incrementado enormemente.

Elegir la frecuencia de carga de la dimensión Tiempo

La dimensión Tiempo es bastante atípica, en líneas generales, con respecto a la periodicidad de los procesos de carga, incluso puede que teóricamente nunca debamos volver a cargarla (al menos a lo largo de nuestra carrera profesional, cuidado que no nos ocurra como con el tratamiento del año 2000 :)). Como se indica en el Bloque de Código 3 si se indica un rango cuyo extremo inferior está en el año más antiguo del que tenemos datos en nuestra empresa y el superior en el año 2100, rara vez tendremos que cargar filas adicionales. En cambio hay otras alternativas que evitan tener todas esas filas cargadas sin necesidad durante largos periodos de tiempo. Por ejemplo se puede hacer una carga de los cinco siguientes años, y anualmente lanzar el proceso y agregar nuevas filas.

El único tema adicional que considero importante, es que en los procesos ETL de carga de hechos, habrá que tener en cuenta el tratamiento a realizar cuando entre un hecho con una fecha que no se encuentre almacenada en la Dimensión Tiempo. Podemos incluso hacer que el proceso cree una fila para esa fecha en la dimensión tiempo. Lo más habitual es que ese hecho arrastre algún error en el sistema transaccional, para lo cual debemos de alguna forma reflejar que se ha producido esa situación, bien grabando en alguna tabla de auditoría, bien llevando esas filas a otra tabla para revisión, o como estimemos oportuno, pero siempre dejando reflejada esa posible anomalía.

Consideraciones adicionales sobre la dimensión Tiempo

En lo visto anteriormente, hemos abordado de forma genérica el tratamiento de la dimensión tiempo, aunque en ciertos casos, habrá nuevas problemáticas que no estén cubiertas aquí. Vamos a citar una de las más habituales, pero no llegaremos a desarrollarla en este momento por el espacio que ocuparía. Lo dejamos pendiente para futuros artículos.

Es habitual que una empresa tenga diferentes sedes, y que estas estén en lugares geográficos distintos, en diversas ciudades a lo largo de la geografía. En ese caso, aunque una fecha es la misma en cualquier lugar del planeta, nos solemos encontrar con pequeñas variantes, como es el caso del tratamiento de los festivos, de periodos de cierre de producción en cada sede, o cualquier otro dato a tratar que pueda variar de un lugar a otro. Para estos casos, hemos visto una alternativa simple, que es tener una columna de festivos para cada lugar. Esta solución es rápida y válida en algunos casos, pero hay otros muchos que necesitan de una mayor flexibilidad, y que se puedan incorporar de forma automática nuevas sedes y por tanto nuevos calendarios, y no querremos estar añadiendo nuevas columnas a nuestra tabla y modificando nuestro proceso de ETL para que las alimente. En ese caso debemos cambiar la granularidad de nuestra tabla para tener una fecha por cada calendario que tengamos en nuestro sistema, así para un mismo día habrá tantas filas como calendarios tengamos definidos.

Le invito a realizar ciertas mejoras y adaptaciones, como utilizar el tipo de datos que mejor se adapte a sus necesidades, en base a los valores a almacenar. En lugar de utilizar los tipos de datos DateTime y SmallDateTime, que como bien sabe almacenan fecha y hora, hay disponibles otros tipos de datos que permiten almacenar sólo fechas, sólo horas, hacer tratamientos en base a zonas horarias y otras novedades que son aplicables en el diseño de la dimensión Tiempo.

Conclusiones

Hemos dado una visión general del tratamiento de la dimensión tiempo, recogiendo nuestras experiencias con ella y mostrando las problemáticas más habituales que nos hemos ido encontrando, así como la forma de resolverlas. Esperamos que este documento sirva como base a la hora de crear su propia dimensión Tiempo y adaptarla a sus necesidades, que evite el olvido de detalles importantes citados aquí y que le facilite un diseño completo y fácil de implementar en sus proyectos.