Sistema de Fechas para soluciones Business Intelligence

Cómo construir un sistema de control de tiempos para nuestra solución BI potente, flexible y bajo nuestro completo control.

Una de las cosas que hace interesante trabajar en una empresa de servicios informáticos es que uno nunca se aburre.

Un día te llama un cliente y te dice que su cubo OLAP ha dejado de mostrar datos a partir de cierta fecha. Te pones manos a la obra. Compruebas los procesos y ves que todos se han ejecutado sin errores. Compruebas los datos. Están correctos y en su sitio pero la tabla de fechas sólo contiene registros hasta la fecha en que dejan de aparecer los datos.
La causa y la resolución del problema parecen claros. Si no hay fechas para el momento de un hecho, el cubo no puede mostrarlo por tanto basta con añadir los registros necesarios a la tabla, reprocesar el cubo y todo volverá a la normalidad.

Solucionado el problema inmediato surge las preguntas: ¿Por qué ha pasado? y ¿Cómo evitarlo en el futuro?

LA TRAMPA DEL ASISTENTE DE DIMENSIONES

Esto ocurre cuando la fecha de los hechos que se procesan supera a la fecha máxima almacenada en la tabla de fechas que emplea nuestro cubo. Es muy frecuente -por lo cómodo- que la tabla de fechas se genere con el asistente de dimensiones que proporciona SQL Server Data Tools. Y sus ventajas acaban ahí, en la comodidad.

Para empezar contiene la curiosa opción de generar la tabla de tiempos en el servidor de Analysis Services y no en el origen de datos donde tenemos el resto de nuestro modelo dimensional. Esto mantiene la tabla de tiempos oculta y hace que no se pueda modificar, actualizar o ampliar. Podemos optar también por generar la tabla de tiempos en el origen de datos junto con el resto de nuestro modelo dimensional. En él podremos mantener la tabla de la manera que nos sea más conveniente.

En el siguiente paso del asistente se nos permite escoger el intervalo de fechas que contendrá la tabla , los periodos de tiempo y el idioma de los textos. Los problemas aquí son dos. El primero es que el asistente no incorpora ningún mecanismo para añadir fechas a la tabla tras su creación. El segundo es que no aparece ninguno de los idiomas de España.

Más adelante nos permite incorporar varios calendarios a la tabla además del calendario natural, como son el calendario fiscal de marketing o de fabricación. No sé el lector pero yo jamás me he encontrado con nadie que emplease esos calendarios por no hablar de que el fiscal puede cambiar de un país a otro.

Llegados a este punto, es fácil ver que el asistente nos sirve sólo para realizar una prueba de concepto o una puesta en marcha rápida pero que no podemos quedarnos con lo que nos proporciona.El formato de fecha estadounidense es bastante molesto y confuso para nosotros. Y si somos serios tampoco nos gustará la idea de que la tabla de fechas se quede sin fechas aunque eso ocurra mucho después de nuestra baja en la empresa.

UN SISTEMA DE GESTIÓN PARA LAS DIMENSIONES DE TIEMPO

La solución pasa por desarrollar nuestro propio sistema de gestión para las dimensiones de tiempo: fechas y horas.

Dos dimensiones separadas son adecuadas para la mayoría de necesidades. La dimensión de fecha con granularidad a nivel de día, la dimensión de horas con granularidad a nivel de segundo.Eso nos da una tabla de fechas con 365 (o 366) registros por año introducido y una tabla de horas con 86400.Una tabla que combinase fechas y horas a nivel de segundos sería posible pero prohibitiva ya que para un solo año incluiría 31.536.000 registros.

Nuestro sistema debería ser capaz de:

– Crear las tablas de fechas y horas
– Llenar la tabla de horas
– Llenar la tabla de fechas con un intervalo adecuado a nuestras necesidades
– Poner los textos descriptivos en todos los idiomas que necesitemos
– Poner las fechas y horas en todos los formatos que necesitemos
– Generar claves primarias para ambas
– Actualizar periódicamente la tabla de fechas para evitar que se quede sin fechas útiles
– Establecer registros neutros en ambas tablas

Si sabemos algo de T-SQL, escribir dos procedimientos que creen y llenen cada tabla no es especialmente difícil.La creación de las tablas no es diferente de la de cualquier otra. Aquí os proponemos estos ejemplos que pueden ser fácilmente modificados para adaptarlos a las necesidades de cada uno.

CREACIÓN DE LAS TABLAS

TABLA PARA LA DIMENSIÓN DE FECHAS

CREATE TABLE [esquema].[dimFecha] ([ClaveFecha] [int] NOT NULL,[Fecha] [date] NOT NULL,[Descripcion_Fecha] [nvarchar](100) NULL,[NumeroDiaSemana] [smallint] NULL,[NumeroDiaMes] [smallint] NULL,[NumeroDiaAño] [smallint] NULL,[NumeroSemanaAño] [smallint] NULL,[NumeroMesAño] [smallint] NULL,[NumeroTrimestreAño] [smallint] NULL,[NumeroSemestreAño] [smallint] NULL,[NumeroAño] [smallint] NULL,[ClaveSemanaAño] [nvarchar](25) NULL,[ClaveMesAño] [nvarchar](25) NULL,[ClaveTrimestreAño] [nvarchar](25) NULL,[ClaveSemestreAño] [nvarchar](25) NULL,[Descripcion_SemanaAño] [nvarchar](25) NULL,[Descripcion_MesAño] [nvarchar](25) NULL,[Descripcion_TrimestreAño] [nvarchar](25) NULL,[Descripcion_SemestreAño] [nvarchar](25) NULL,[Descripcion_Año] [nvarchar](25) NULL,[NombreDiaSemana_Idioma 1] [nvarchar](11) NULL,[NombreDiaSemana_Idioma 2] [nvarchar](11) NULL,[NombreDiaSemana_Idioma n] [nvarchar](11) NULL,[NombreMesAño_Idioma 1] [nvarchar](11) NULL,[NombreMesAño_Idioma 2] [nvarchar](11) NULL,[NombreMesAño_Idioma n] [nvarchar](11) NULL
CONSTRAINT [PK_dimFecha_ClaveFecha] PRIMARY KEY CLUSTERED
([ClaveFecha] ASC
),
CONSTRAINT [AK_dimFecha_Fecha] UNIQUE NONCLUSTERED
([Fecha] ASC
)
)

Las columnas de esta tabla cumplen las siguientes funciones:
Dos columnas de clave, principal y candidata (ClaveFecha y Fecha)
Ocho columnas con los números de los diferentes elementos de una fecha (NumeroElementoFecha)
Cuatro columnas con claves compuestas para facilitar la posterior creación de jerarquías en un cubo OLAP (ClaveElementoFecha)
Seis columnas con las descripciones de los diferentes elementos de una fecha en el idioma de uso principal (Descripcion_ElementoFecha)
Varias columnas, seis en nuestro ejemplo, para los nombres de días y meses en idiomas alternativos (NombreDiaSemana_Idioma x y NombreMesAño_Idioma x)

TABLA PARA LA DIMENSIÓN DE HORAS

CREATE TABLE [esquema].[dimHora] ([ClaveHora] [int] NOT NULL,[Descripcion_Hora12] [nvarchar](11) NOT NULL,[Descripcion_Hora24] [nvarchar](8) NOT NULL,[Descripcion_Hora] [nvarchar](5) NOT NULL,[Descripcion_Minuto] [nvarchar](8) NOT NULL,[NumeroHora12] [smallint] NOT NULL,[NumeroHora24] [smallint] NOT NULL,[NumeroMinuto] [smallint] NOT NULL,[NumeroSegundo] [smallint] NOT NULL,[NumeroMediaHora] [smallint] NOT NULL,[NumeroCuartoHora] [smallint] NOT NULL,[ClaveMediaHora] nvarchar(4) NULL,[ClaveCuartoHora] nvarchar(4) NULL,[Descripcion_MediaHora] nvarchar(20) NOT NULL,[Descripcion_CuartoHora] nvarchar(20) NOT NULL, [AMPM] [nvarchar](2) NOT NULL
CONSTRAINT [PK_dimHora_ClaveHora] PRIMARY KEY CLUSTERED
(

[ClaveHora] ASC
)
)

Las columnas de esta tabla cumplen las siguientes funciones:
Una columna de clave, principal(ClaveHora)
Dos columnas con la hora, minuto y segundo en formatos de 12 y 24 horas (Descripcion_Hora12 y Descripcion_Hora24)
Una columna con la hora y el indicador de mediodía (Descripción_Hora)
Una columna con la hora, el minuto y el indicador de mediodía ((Descripción_Minuto)
Seis columnas con los números la hora en formatos de 12 y 24 horas, el minuto, el segundo, la media hora y el cuarto de hora. (NumeroElementoHora)
Dos columnas con claves compuestas para facilitar la posterior creación de jerarquías en un cubo OLAP (ClaveElementoHora)
Dos columnas con la descripción de las medias horas y los cuartos de hora
Una columna con el indicador de mediodía

LLENADO DE LAS TABLAS

El llenado de ambas tablas puede realizarse mediante procedimientos almacenados. En el caso de la tabla de fechas, el mismo procedimiento puede usarse tal cual o ligeramente modificado para irla actualizando periódicamente.La tabla de horas es la más sencilla de llenar. El procedimiento almacenado debería tener en cuenta el idioma del usuario y, siempre según la tabla de nuestro ejemplo, los sufijos para media hora y cuarto de hora.
Para el idioma aconsejamos usar variables o parámetros que reciban valores de la vista de sistema sys.syslanguages (https://msdn.microsoft.com/es-es/library/ms190303(v=sql.120).aspx) o de la lista de culturas de .NET (https://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396#CultureNames) ej. es-es, en-us, ca-es.)
El uso de esos valores nos permitirá emplear las instrucciones SET LANGUAGE y FORMAT de T-SQL para obtener las fechas y horas en diferentes formatos e idiomas con facilidad, lo que en combinación con las funciones de fecha y hora de T-SQL (https://msdn.microsoft.com/es-es/library/ms186724%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396) nos facilitará generar los valores de las diferentes columnas.
La tabla de fechas es algo más compleja y debe tener en cuenta no sólo el idioma sino el primer día de la semana, prefijos (o sufijos) para las semanas, trimestres, semestres y años así como la fecha de inicio.

Como hemos dicho, en nuestras tablas tendremos un idioma principal pero puede ser necesario tener otros preparados. SQL Server tiene una serie de idiomas “oficiales” cuyo uso determina el día inicial de la semana, los nombres de los meses completos y abreviados, los nombres de los días de la semana y el formato de la fecha. Para el resto de idiomas y culturas habremos de recurrir a la función FORMAT (https://msdn.microsoft.com/es-es/library/hh213505%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396) y a programar lo que nos haga falta manualmente.

Los sufijos y prefijos no son más que cadenas de texto que añadiremos a los números de meses, semestres, semanas y otras partes de las fechas y horas para clarificar los datos a mostrar. De ahí que los tamaños de las columnas de nuestros ejemplos y los valores que se les asignen son perfectamente modificables a gusto del usuario.

Mientras que en la tabla de horas, introduciremos todas con un intervalo de un segundo desde las 00:00:00 a las 23:59:59, en la tabla de fechas queda a nuestra elección cuáles serán las fechas inicial y final. La idea es proporcionar a nuestro procedimiento almacenado una fecha inicial y una cantidad de años a añadir por delante del último día del año en curso. De esta manera sabemos que la última fecha es un 31 de diciembre y podemos programar una carga anticipada de fechas de, por ejemplo, un año más cada 15 de diciembre.

Para las claves primarias de las tablas, tenemos dos opciones: emplear la fecha u hora correspondientes o generar una clave sustituta. Nosotros nos decantamos por la segunda opción por dos motivos: una clave sustituta es más sencilla de manejar y nos permite tratar fechas y horas erróneas o no informadas. Para las fechas podemos usar la conocida fórmula AÑO*10000 + MES*100 + DIA que nos genera un entero que además nos permite saber la fecha sin más transformaciones. Con la hora podemos usar el número del segundo dentro del día que nos da la fórmula HORA24*3600 + MINUTO*60 + SEGUNDO + 1 o simplemente una columna Identity (1, 1).

La incorporación de registros neutros nos permite gestionar las fechas y horas erróneas o no informadas. Se trata de que todos los hechos que no puedan asociarse a una fecha u hora reales, se asocien con la fecha u hora neutras al cargar las tablas de hechos. Nosotros usamos registros cuya clave sustituta es de tipo integer y valor -1. Los valores de los atributos son: -1 para los valores numéricos, ‘?’, ‘N/D’ o ‘DESCONOCIDO’ para los de texto en función de su longitud, 31/12/9999 para los de fecha.

Con estas directrices y algo de trabajo de programación para completarlas y adaptarlas a las necesidades de cada uno, tendremos un sistema de control de tiempos para nuestra solución BI potente, flexible y bajo nuestro completo control.

NOTA: Todo el artículo está escrito pensando en la plataforma SQL Server y sus servicios adjuntos (Integration Services, Analysis Services etc.) sin embargo los conceptos explicados son perfectamente adaptables a cualquier otra plataforma BI.