Tips de diseño de DWH: La Omnisciente Dimensión Time – Parte 2

Por Rut Almoguera Poggi 


Ahora que ya hemos visto en el post anterior lo imprescindible que es la dimensión Time, en esta entrega vamos a hablar sobre cuáles son las mejores prácticas para construirla.

La dimensión Time es especial pues no suele tener una fuente de donde llenarla, por lo que tenemos nosotros mismos que cargarla en el Datawarehouse.

A diferencia del resto de las tablas de dimensión, podemos construir la tabla Time por adelantado, cargándola con 5 o 10 años de registros cubriendo algunos años hacia atrás y/o hacia adelante (10 años = 3.650 registros, sí la granularidad es de día). 

Es recomendable que creemos un Store Procedure en la BD o algún programa para pre-cargarla.  Por lo general es común que se pre-carguen algunos años en la dimensión Time aunque también podría programarse el proceso de llenado de la dimensión Time para que revise al inicio del mismo si el año actual esta presenta ya en la data de la dimensión, y sino esta insertar el año actual completo, y en caso contrario, simplemente terminar.

Otra consideración importante al momento de crear la dimensión Time es revisar la granularidad de la misma.  Por ejemplo, si la dimensión Time por necesidades del negocio requiere tener un detalle más fino que el de día, por ejemplo, que necesitáramos guardar la hora exacta en la que sucede un evento, sería recomendable que la misma se partiera en dos dimensiones, una que contenga solo las fechas, y otra que contenga solo las horas del día, pues de lo contrario la dimensión terminaría siendo demasiado grande, y degradaría el performance de las consultas al Datawarehouse.

También al momento de diseñarla tenemos que considerar como serán los Rollups en las jerarquías de dicha dimensión.

Por ejemplo, si nuestra dimensión Time tiene una granularidad de día, y tiene una jerarquía como la siguiente:


No tendremos mayores problemas, con el Rollup.

Pero consideremos una jerarquía diferente en donde esté presente la Semana del año y el Mes como niveles de la jerarquía.  Algo como lo siguiente:


En este caso, si tendríamos un problema al hacer el Rollup, pues por lo general un mes NO termina o comienza en una semana completa, sino que puede hacerlo a la mitad de una semana, con lo cual la semana podría pertenecer a dos meses diferentes.  Veamos por ejemplo las fechas 31/03/2015 que fue martes,  y 01/04/2015 que fue miércoles:


Como podemos ver, ambas fechas están en la semana 14 del año, pero pertenecen a meses diferentes, por lo tanto NO podemos colocar el nivel Semana del Año en una jerarquía que también contenga el nivel Mes pues el resultado de hacer Rollup por dicha jerarquía podría arrojar valores incorrectos.  Si necesitamos tener los dos niveles, porque el cliente lo requiere para los reportes, se deberán tener dos jerarquías para dicha dimensión, una que contenga el Mes y otra que contenga la Semana del Año.

Otro tema importante a considerar cuando estamos diseñando una dimensión Time es sobre la decisión de cuál campo sería el Primary Key de la tabla.  Por lo general para el resto de las dimensiones lo recomendable es un usar un Surrogate Key, pero en el caso de la dimensión Time, esto no aplicaría, pues por lo general la data de las tablas Facts, suelen particionarse por la fecha, y por lo tanto, es buena idea tener como Primary Key de la dimensión Time un código inteligente que contenga el año, y nos permita particionar la Fact por la data de dicho campo.

Esto nos facilitaría poder “quitar” una partición de la tabla Fact, que corresponda a un año específico cuando ya la data relativa a ese año no sea de utilidad o se requiera consultarla con muy poca frecuencia, y el costo de mantenerla en el Datawarehouse sea mayor que los beneficios que obtendríamos en el performance de las consultas si la eliminamos.  Como por ejemplo podrían ser las ventas en una tabla Fact de Ventas que sean de 10 años atrás.
Lo más recomendable para usar como la clave de dicha dimensión es un código “inteligente” que contenga el año.  Por ejemplo si tenemos una dimensión Time cuya granularidad llega solo hasta el nivel de mes, podríamos crear el identificador del mes, concatenando el año con el mes:

YYYYMM: en donde YYYY es el año y MM el mes, y para este cálculo se debe multiplicar el año por 100 y sumarle el mes (getyear(Fecha)*100  + getmonth(Fecha)).

Si por ejemplo la granularidad de nuestra dimensión Time es de día.  Siguiendo con esta regla podríamos tener el siguiente Primary Key:

YYYYMMDD: en donde YYYY es el año, MM el mes y DD el día. Para el cálculo es necesario obtener el año, multiplicarlo por 10000, sumarle el mes multiplicado por 100 y sumarle el día (getyear(Fecha)*10000 + getmonth(Fecha)*100 + getday(Fecha)).

Así cuando se cree la tabla de la Fact particionada, se realizaría la partición por los valores del campo de la fecha, y todas las filas que correspondan a un año específico irían a una partición especial para ese año, y así será muy sencillo mantener en la tabla Fact solo la data relevante, pues, si no hacemos limpieza de la misma y dejamos que se llene de data muy vieja, el performance de los queries contra la Fact se degradaría notablemente.

Con esto terminamos la discusión sobre la dimensión Time, espero que consideres estos consejos cuando diseñes dicha dimensión.