La primera vez que estaba haciendo algo parecido a BI, la dimensión tiempo se generaba a partir de un campo de tipo datetime en la tabla de hechos. Para ese entonces estaba empleando Cognos y era lo único que necesitaba para generar la jerarquía, con todos los niveles e inclusive los típicos acumulados por año, mes, trimestre, etc.
No obstante, es mucho más útil tener una verdadera dimensión tiempo definida y cargada en el datawarehouse, lo cual, genera algunas dudas como ¿de dónde la cargo?, ¿cada cuánto la actualizo?, ¿qué atributos incluyo?, ¿cuál nivel de granularidad? Entre otras.
Y si a eso se le suma complejidad que podría presentar el tener una implementación en varios husos horarios o el hecho de que sea multilenguaje. Todos estos factores deben ser tomados en cuenta desde el inicio del diseño del datawarehouse.
Un parámetro básico del cual se puede partir para obtener los primeros atributos de la dimensión es considerar todos aquellos datos que podemos derivar desde el motor de base de datos. Para el caso particular de Microsoft SQL Server, todo lo que se puede obtener de la función datepart (mes, año, día, nombre de mes, etc.).
Independientemente al tipo de arquitectura o diseño empleado, hay un aspecto fundamental: el campo llave inteligente. Cruzar datos desde la tabla de hechos y la dimensión tiempo vía un valor date o datetime sería sumamente costoso. Por ende, debe realizarse un trabajo en el procesamiento de la dimensión (tiempo de ejecución ETL), en el cual se crea una llave especial para cada registro de esta dimensión. Por ejemplo, un registro con fecha 27-ene-2010, deberá tener como llave para el cruce con la tabla de hechos a un número entero, a saber: 20100127. También puede usarse la notación tipo offset, como la que emplea Microsoft Excel, en la cual la fecha se representa como la cantidad de días pasados desde el 01 de enero del año 1900. En cualquiera de los dos casos, un join con números enteros será mucho más eficiente que una operación comparativa con datetimes o dates.
Tipos de Calendarios
Hasta el momento no se ha profundizado sobre el tipo de calendario que se empleará. Para un diseñador sería mucho más fácil que toda organización que implementa una solución BI/DW cuente con un calendario de actividades uniformes, no obstante, eso está muy lejos de la realidad.
Las variantes generalmente son de dos tipos:
- En granularidad
- En distribución del tiempo
En el primer caso se tienen que tomar decisiones sobre cuál es la unidad de tiempo más pequeña en el DW. Día suele ser una buena decisión, pero algunas compañías, como telecoms, probablemente necesitarán realizar consultas con ventanas de tiempo más pequeñas, como minutos e inclusive segundos. Acá es donde el arquitecto debe encontrar el equilibrio entre rendimiento y funcionalidad. Mientras más pequeña sea la unidad de tiempo, mayor volumen de datos. Un día tiene 86400 segundos, así que un año con granularidad al segundo, contará con más de 31 millones de registros.
En el segundo caso, el problema radica en que la organización no emplea el calendario natural o Gregoriano para registrar sus transacciones. Se presenta comúnmente en empresas de manufactura, en las cuales los ciclos de producción están representados por 4 semanas (28 días) y el año cuenta con 13 periodos. Por ende, hay que personalizar la dimensión tiempo o bien crear las equivalencias en cada registro.
Ejemplo: diseño básico de dimensión tiempo
Supóngase que se tiene una empresa de producción. Por razones corporativas, todas las operación de la planta se basan en ciclos de 4 semanas y se agrupan en periodos. El año cuenta con 13 periodos y no siempre comienza el mismo día. El primer día del año 2010 comenzó el 28 de diciembre de 2009. Todos los reportes de producción, manufactura y logística deben ser realizados tomando en cuenta este calendario.
Por otro lado, los departamentos financieros y administrativos, realizan los cortes mensuales con base en el calendario Gregoriano o natural. Es de suma importancia determinar cuáles son los últimos días de mes, natural o corporativo, ya que se dan picos de actividades propias del negocio.
¿Cómo quedaría el diseño de esta dimensión?
En la imagen adjunta se aprecia un diseño, no exhaustivo, pero representativo de los campos básicos para crear una dimensión tiempo que cumpla con los requisitos de análisis, tanto para la perspectiva del usuario que emplea el calendario natural, como aquel que emplea el calendario corporativo.
Nótese que se incluyeron campos que indican si se encuentra en el último día de mes o si es feriado. Así como también la dualidad entre el calendario natural y el calendario corporativo.
La forma más práctica de llenar esta tabla es a través de un script de SQL o bien, sentarse algunas horas frente a una hoja electrónica y llenar manualmente cada campo, cosa poco remomendada y propensa a muchos errores.
En caso de que se usaran otros lenguajes, es probable que existiesen campos adicionales con los captions respectivos para cada uno de ellos.
Por último, si se cuenta con transacciones en más de un huso horario, es común incorporar una hora corporativa y una hora local en los registros de la tabla de hechos para ubicar correctamente en el tiempo el evento.
Enfoque Práctico 
17 enero 2010 at 3:47 pm
Interesante blog, e interesante artículo. Felicidades.
Como dices, la dimensión tiempo es muy especial dentro del diseño de un DWH y merece la atención que le prestas en este post.
Sin embargo, no estoy de acuerdo en que sea aconsejable crear un «campo llave inteligente» como clave de esta tabla. Yo también utilizaba estas «llaves inteligentes». El tipo date o datetime es perfectamente válido, y si existe alguna diferencia de rendimiento, es absolutamente inapreciable.
Creo que crear una clave subrogada para la dimensión tiempo es innecesario, y que si se sigue haciendo es sólo por tradición. Siempre se ha hecho así. Sí. Pero no hay ninguna razón técnica o funcional que lo recomiende.
¿Cuales piensas que son las ventajas de tratar las fechas como enteros?
17 enero 2010 at 6:36 pm
Buen día,
Gracias por el comentario. Dichosamente el mundo de BI no está escrito en piedra y el aprendizaje se da contianuamente.
Generalmente, la implementación del «join» en una base de datos está optimizada para campos de tipo entero. Aunque la diferencia sea despreciable para unos cuantos registros, la experiencia me dice que esos «poquitos» sumados cuando se manejan altísimos volúmenes de información, llegan a ser significativas.
Ese básicamente mi criterio para recomendar tener una «llave inteligente» en la dimensión tiempo.
Que tenga un excelente día!
25 enero 2010 at 3:29 pm
Creo que teneis razon. Yo empeze a diseñar mi dimensión tiempo con una clave intelligente, y a la hora del diseño fisico de las tablas de hechos, utilizando MySql con particionado aplicando la funcion year sobre la fecha como elemento para este particionado, la optimización de mysql funcionaba si el campo en cuestion era date (pero no cuando utilizaba la clave «inventada» del tipo entero), tal y como se recomiendan en los libros de teoria de BI.
Como dices tu, Jose, sobre el camino vamos aprendiendo.
Felicidades por tu blog!!
25 enero 2010 at 4:57 pm
Hola!
Bienvenido al blog. Muchas gracias por el comentario. La idea es que esto sea una comunidad abierta en donde todos aprendemos un poco de los demás día a día.
Saludos!
11 mayo 2010 at 5:09 pm
[…] se habla de los diferentes enfoques para realizar la numeración de semanas. Igualmente, en el blog Enfoque Práctico de Jose Cano también se hacen algunas reflexiones interesantes sobre la dimensión tiempo. […]
25 julio 2010 at 5:23 pm
[…] La relación con la dimensión tiempo se hace a través de una llave (surrogate key) inteligente. Este punto fue discutido anteriormente acá. […]
21 marzo 2011 at 8:49 am
Yo estoy haciendo algo parecido y tengo la siguiente disyuntiva. Tengo una Fac de Ventas que esta a nivel de Hora y tego una Fac del Presupuesto de Ventas que esta a nivel de Mes, en este caso como manejamos al dimension Tiempo, tendrian que ser 2 dimensiones Tiempo o tengo las tablas de la dimension tiempo normalizadas???
21 marzo 2011 at 9:31 pm
Buen día David,
Encontrar medidas a diferente nivel de granularidad es algo muy común. Específicamente para el caso de valores de presupuesto, tendría que visualizarse desde dos enfoques, el primero, determinar si es posible llevar el valor que está a nivel de mes al nivel más bajo, en este caso hora. Para ello debe validarse si existe alguna regla de negocio que permitra distribuir (allocation) el valor proyectado entre los valores detalle. Si eso es posible, puede mantener una sola dimensión tiempo.
De lo contrario, el camino que generaría menor trabajo a corto plazo es la generación de dos dimensiones tiempo, una a nivel de la fact hora y otra a nivel de la fact mes.
Un tercer enfoque, o inclusive una variante del segundo approach es agrupar la información de la fact hora a nivel de fact mes para tener reportes comparativos al mismo nivel.
Como toda decisión de diseño, depende de la situación específica y lo que definitivamente definirá el camino a seguir son los requerimientos del usuario.
2 noviembre 2011 at 2:03 pm
Alguien tiene el script para generar la dimension tiempo del ejemplo?
Abrazo
26 septiembre 2012 at 7:41 pm
Muy buen blog felicidades !!!!!
Me aclaro muchas dudas…
16 octubre 2012 at 9:45 am
Iniciándome en el mundo de BI, solicitando ayuda, tengo dudas con respecto a los atributos de la dimensión de tiempo, en los tipos de datos, mis campos son básicos: día, mes, trimestre, año y fecha, pero parece que hay problema al momento de relacionar los meses con los años si no se especifica el año al que pertenece el mes, no se si colocar les tipos de datos int o char al mes por ejemplo o crear un campo mes int (1 al 12) y otro mesAño char(2012-09)
Gracias 🙂