Estructura de la Dimensión Tiempo y script de carga para SQLServer

By il_masacratore on 29 Julio, 2009 - 11:36
Business Intelligence

En un data warehouse hay una serie de dimensiones comunes como son la geografica y la de tiempo. Para quién le pueda servir de ayuda dejo aquí el script de creación de la dimensión tiempo y un procedimiento para hacer la carga entre fechas de la tabla:

 

/*Base de datos destino*/
use PAnalisys

/*Creación de la tabla*/
create table DIM_TIEMPO
(
    FechaSK int not null,
    Fecha date not null, 
    Año smallint not null,
    Trimestre smallint not null,
    Mes smallint not null,
    Semana smallint not null,
    Dia smallint not null,
    DiaSemana smallint not null,
    NTrimestre char(7) not null,
    NMes char(15) not null,
    NMes3L char(3) not null,
    NSemana char(10) not null,
    NDia char(6) not null,
    NDiaSemana char(10) not null
    constraint PK_DIM_TIEMPO PRIMARY KEY CLUSTERED
    (
        Fecha asc
    )
)

/*Script de carga*/
DECLARE @FechaDesde as smalldatetime, @FechaHasta as smalldatetime
DECLARE @FechaAAAAMMDD int
DECLARE @Año as smallint, @Trimestre char(2), @Mes smallint
DECLARE @Semana smallint, @Dia smallint, @DiaSemana smallint
DECLARE @NTrimestre char(7), @NMes char(15)
DECLARE @NMes3l char(3)
DECLARE @NSemana char(10), @NDia char(6), @NDiaSemana char(10)
--Set inicial por si no coincide con los del servidor
SET DATEFORMAT dmy
SET DATEFIRST 1

BEGIN TRANSACTION
    --Borrar datos actuales, si fuese necesario
    --TRUNCATE TABLE FROM DI_TIEMPO
   
    --RAngo de fechas a generar: del 01/01/2006 al 31/12/Año actual+2
    SELECT @FechaDesde = CAST('20060101' AS smalldatetime)
    SELECT @FechaHasta = CAST(CAST(YEAR(GETDATE())+2 AS CHAR(4)) + '1231' AS smalldatetime)
   
    WHILE (@FechaDesde <= @FechaHasta) BEGIN
    SELECT @FechaAAAAMMDD = YEAR(@FechaDesde)*10000+
                            MONTH(@FechaDesde)*100+
                            DATEPART(dd, @FechaDesde)
    SELECT @Año = DATEPART(yy, @FechaDesde)
    SELECT @Trimestre = DATEPART(qq, @FechaDesde)
    SELECT @Mes = DATEPART(m, @FechaDesde)
    SELECT @Semana = DATEPART(wk, @FechaDesde)
    SELECT @Dia = RIGHT('0' + DATEPART(dd, @FechaDesde),2)
    SELECT @DiaSemana = DATEPART(DW, @FechaDesde)
    SELECT @NMes = DATENAME(mm, @FechaDesde)
    SELECT @NMes3l = LEFT(@NMes, 3)
    SELECT @NTrimestre = 'T' + CAST(@Trimestre as CHAR(1)) + '/' + RIGHT(@Año, 2)
    SELECT @NSemana = 'Sem ' +CAST(@Semana AS CHAR(2)) + '/' + RIGHT(RTRIM(CAST(@Año as CHAR(4))),2)
    SELECT @NDia = CAST(@Dia as CHAR(2)) + ' ' + RTRIM(@NMes)
    SELECT @NDiaSemana = DATENAME(dw, @FechaDesde)
    INSERT INTO PAnalisys.dbo.DIM_TIEMPO
    (
        FechaSK,
        Fecha,
        Año,
        Trimestre,
        Mes,
        Semana,
        Dia,
        DiaSemana,
        NTrimestre,
        NMes,
        NMes3L,
        NSemana,
        NDia,
        NDiaSemana
    ) VALUES
    (
        @FechaAAAAMMDD,
        @FechaDesde,
        @Año,
        @Trimestre,
        @Mes,
        @Semana,
        @Dia,
        @DiaSemana,
        @NTrimestre,
        @NMes,
        @NMes3l,
        @NSemana,
        @NDia,
        @NDiaSemana
    )
   
    --Incremento del bucle
    SELECT @FechaDesde = DATEADD(DAY, 1, @FechaDesde)
    END
    COMMIT TRANSACTION

A partir de aquí cada uno puede modificarla a su gusto añadiendo o quitando atributos

SQL Server
time
dwh

Subido por bernabeu_dario el 29 Julio, 2009 - 17:31

Excelente aporte!

Hacia rato que tenía ganas de mostrar un ejemplo de la estructura de la dimensión tiempo y cómo realizar la carga de la misma, así que aproveché tu post para crear una entrada en mi blog, en donde básicamente traduje lo que tú has hecho en MySQL.

Gracias por compartir. Saludos!

Subido por Juan Pablo Lastra (no verificado) el 16 Marzo, 2010 - 03:37

 Muy buen ejemplo...

Subido por Oscar López Osorio el 24 Julio, 2011 - 10:18

Buenos días, estimados

Los saludo desde el Edo. De México, México.

Primero que nada gracias por compartir el script, les comento que al intentar implementar una jerarquía de la dimensión tiempo con la siguientes estructura:

Año -> NTrimestre -> NMes en Analysis Services 2008 se produce el siguiente error:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_Dim_Tiempo', Column: 'NMes', Value: 'Diciembre'. The attribute is 'N Mes'.

Al parecer es porque los meses no son distintos entre cada año, para solucionarlo hice unos pequeños cambios a su script y le agregue el campo semestre, el cual les comparto:

/*Creación de la tabla*/ create table Dim_Tiempo ( FechaSK int not null, Fecha date not null, Anio smallint not null, Semestre smallint not null, Trimestre smallint not null, Mes smallint not null, Semana smallint not null, Dia smallint not null, DiaSemana smallint not null, NSemestre char(7) not null, NTrimestre char(7) not null, NMes char(15) not null, NMes3L char(6) not null, NSemana char(10) not null, NDia char(6) not null, NDiaSemana char(10) not null constraint PK_DIM_TIEMPO PRIMARY KEY CLUSTERED ( Fecha asc ) ) /*Script de carga*/ DECLARE @FechaDesde as smalldatetime, @FechaHasta as smalldatetime DECLARE @FechaAAAAMMDD int DECLARE @Año as smallint, @Semestre char(2), @Trimestre char(2), @Mes smallint DECLARE @Semana smallint, @Dia smallint, @DiaSemana smallint DECLARE @NSemestre char(7), @NTrimestre char(7), @NMes char(18) DECLARE @NMes3l char(6) DECLARE @NSemana char(10), @NDia char(6), @NDiaSemana char(10) --Set inicial por si no coincide con los del servidor SET DATEFORMAT dmy SET DATEFIRST 1 BEGIN TRANSACTION --Borrar datos actuales, si fuese necesario --TRUNCATE TABLE FROM DI_TIEMPO --RAngo de fechas a generar: del 01/01/2006 al 31/12/Año actual+2 SELECT @FechaDesde = CAST('20060101' AS smalldatetime) SELECT @FechaHasta = CAST(CAST(YEAR(GETDATE())+2 AS CHAR(4)) + '1231' AS smalldatetime) WHILE (@FechaDesde <= @FechaHasta) BEGIN SELECT @FechaAAAAMMDD = YEAR(@FechaDesde)*10000+ MONTH(@FechaDesde)*100+ DATEPART(dd, @FechaDesde) SELECT @Año = DATEPART(yy, @FechaDesde) SELECT @Trimestre = DATEPART(qq, @FechaDesde) SELECT @Semestre = CASE WHEN @Trimestre <= 2 THEN 1 ELSE 2 END SELECT @Mes = DATEPART(m, @FechaDesde) SELECT @Semana = DATEPART(wk, @FechaDesde) SELECT @Dia = RIGHT('0' + DATEPART(dd, @FechaDesde),2) SELECT @DiaSemana = DATEPART(DW, @FechaDesde) SELECT @NMes = DATENAME(mm, @FechaDesde) + '/' + RIGHT(@Año, 2) SELECT @NMes3l = LEFT(@NMes, 3) + '/' + RIGHT(@Año, 2) SELECT @NSemestre = 'S' + CAST(@Semestre as CHAR(1)) + '/' + RIGHT(@Año, 2) SELECT @NTrimestre = 'T' + CAST(@Trimestre as CHAR(1)) + '/' + RIGHT(@Año, 2) SELECT @NSemana = 'Sem ' +CAST(@Semana AS CHAR(2)) + '/' + RIGHT(RTRIM(CAST(@Año as CHAR(4))),2) SELECT @NDia = CAST(@Dia as CHAR(2)) + ' ' + RTRIM(@NMes) SELECT @NDiaSemana = DATENAME(dw, @FechaDesde) INSERT INTO dbo.DIM_TIEMPO ( FechaSK, Fecha, Anio, Semestre, Trimestre, Mes, Semana, Dia, DiaSemana, NSemestre, NTrimestre, NMes, NMes3L, NSemana, NDia, NDiaSemana ) VALUES ( @FechaAAAAMMDD, @FechaDesde, @Año, @Semestre, @Trimestre, @Mes, @Semana, @Dia, @DiaSemana, @NSemestre, @NTrimestre, @NMes, @NMes3l, @NSemana, @NDia, @NDiaSemana ) --Incremento del bucle SELECT @FechaDesde = DATEADD(DAY, 1, @FechaDesde) END COMMIT TRANSACTION

Un saludo…
OLO

Subido por AnaMaria (no verificado) el 29 Octubre, 2013 - 21:15

Muchas gracias por tu ejemplo, práctico y claro! Me ha ayudado a entender la estructura y lo que significa. Yo soy novata en el mundo del BI, dando mis primeros pasos con mi primera práctica. He hecho mi script personalizado a lo que necesito, tengo un par de preguntas, si fueras tan amable de contestarme te lo agradecería mucho. Es la parte donde se definen los parámetros: -- 01.01.13 -- Supongo que sería así, sólo que en el año necesitaría sólo los dos últimos --------dígitos... SELECT @Fecha = DATEPART(dd, @FechaDesde) + '.' + MONTH(@FechaDesde)*100 + '.' + YEAR(@FechaDesde)*10000 -- 2013 -- Sólo el año, creo que bien SELECT @ANYO = YEAR(@FechaDesde)*10000 -- 1 -- El mes, pero sólo el dígito de la derecha SELECT @Mes = MONTH(@FechaDesde)*100 -- Fecha en este formato 2006/01, creo que bien SELECT @Anyo_Mes = YEAR(@FechaDesde)*10000 + '/' + MONTH(@FechaDesde)*100 -- 31 -- El último día del mes, depende del mes será 28, 29, 30 0 31, eso ni idea SELECT @Ultimo = -- 0,08493151, porcentaje del dia con respecto al año. Hay meses que tienen 30, 31 o incluso 28 o 29 dias, sería un día entre los días totales de un año.. SELECT @Porcentaje = me podrías ayudar por favor? Un saludo * Ana

Subido por AnaMaria (no verificado) el 30 Octubre, 2013 - 13:06

Hola, estoy intentando hacer un Script para mi dimensión Fecha/Tiempo y quería preguntaros a vosotros expertos, ya que yo soy totalmente novata en este mundo si las siguiente definiciones de parámetros son correctas. -- 2002 SELECT @Anyo = YEAR(@FechaDesde)*10000 -- 01.01.02 SELECT @Fecha = DATEPART(dd, @FechaDesde) + '.' + MONTH(@FechaDesde)*100 + '.' + Right(@Anyo, 2) -- 1 SELECT @Mes = RIGHT(MONTH(@FechaDesde)*100,1) -- 2006/01 SELECT @Anyo_Mes = YEAR(@FechaDesde)*10000 + '/' + MONTH(@FechaDesde)*100 Si pudierais ayudarme, lo agradecería. Gracias, Un saludo

Subido por AnaMaria (no verificado) el 30 Octubre, 2013 - 13:08

Una pregunta corta, he ido a ejecutar mi Script y me da el siguiente error: Msg 155, Level 15, State 1, Line 69 'dmy' is not a recognized dateadd option. por qué puede ser? gracias

Subido por Carlos el 30 Octubre, 2013 - 23:12

Cuál es la sentencia que te devuelve el error? Si has utilizado dateadd, 'dmy' no es un parámetro válido para la especificación del formato. Tienes que indicar lo que quieras sumar o restar a la fecha, pero sólo una cosa: dd->días, mm->meses, yy->años, por ejemplo.

Subido por Javier (no verificado) el 23 Enero, 2015 - 20:54

Bueno el ejemplo. Pero veo que el primer dia de la semana es Monday.¿Que tengo que hacer para que el primer dia de la semana sea Sunday?

Saludos,

Javier Vega.