T-SQL - Carga dimensão Tempo (Time Dimension Load)

Carga da Dimensão Tempo em T-SQL

(Time Dimension load in T-SQL)

Carga da dimensão de tempo com a seguinte estrutura em snow flake:

(Load of the time dimension with the following snow flake schema)

Para efeito didático o código carrega poucos campos nestas estruturas, porém é de fácil alteração para inclusão de novos cálculos de tempo.

(For didactic purposes the code loads few fields, but it is easy to change to include new time calculations.) Note that for english language natives, the six month period is unusual and can be dropped of the structure.

DECLARE @Data DATETIME, @DataIni DATETIME, @DataFim DATETIME

SET @DataIni = '2000-01-01 00:00:00.000'

SET @DataFim = '2020-12-31 00:00:00.000'

--Carga dimensão Ano (Year dimension load)

SET @Data = @DataIni

WHILE (@Data <= @DataFim)

BEGIN

                INSERT INTO ANO ( ANO )

                VALUES ( YEAR(@Data) )

                SET @Data = DATEADD(MONTH,12,@Data)

                SET @Data = DATEADD(MONTH,1, @Data) - DATEPART(DAY,DATEADD(MONTH,1, @Data))

END

--Carga dimensão Semestre (Six month period dimension load)

SET @Data = @DataIni

WHILE (@Data <= @DataFim)

BEGIN

                INSERT INTO SEMESTRE (

                                                                SEMESTRE,

                                                                DESCRICAO_SEMESTRE,

                                                                ANO  )

                VALUES

                                (

                                YEAR(@Data) * 100 + CASE WHEN ( DATEPART(QQ ,@Data) < 3 )  THEN (1) ELSE (2) END,

                                'SEMESTRE ' + CONVERT(VARCHAR,(CASE WHEN ( DATEPART(QQ ,@Data) < 3 )  THEN (1) ELSE (2) END)) + ' - ' + CONVERT(VARCHAR,YEAR(@Data)),

                                YEAR(@Data)  )

                SET @Data = DATEADD(MONTH,6,@Data)

                SET @Data = DATEADD(MONTH,1, @Data) - DATEPART(DAY,DATEADD(MONTH,1, @Data))

END

--Carga dimensão trimestre (Quarter dimension load)

SET @Data = @DataIni

WHILE (@Data <= @DataFim)

BEGIN

                INSERT INTO TRIMESTRE (

                                                                TRIMESTRE,

                                                                DESCRICAO_TRIMESTRE,

                                                                SEMESTRE,

                                                                ANO  )

                VALUES

                                (

                                YEAR(@Data) * 100 + DATEPART ( QQ , @Data ),

                                'TRIMESTRE ' + CONVERT(VARCHAR,DATEPART(QQ,@Data)) + ' - ' + CONVERT(VARCHAR,YEAR(@Data)),

                                YEAR(@Data) * 100 + CASE WHEN ( DATEPART(QQ ,@Data) < 3 )  THEN (1) ELSE (2) END,

                                YEAR(@Data) )

                SET @Data = DATEADD(MONTH,3,@Data)

                SET @Data = DATEADD(MONTH,1, @Data) - DATEPART(DAY,DATEADD(MONTH,1, @Data))

END

--Carga dimensão mês (month dimension load)

SET @Data = @DataIni

WHILE (@Data <= @DataFim)

BEGIN

                INSERT INTO MES (

                                                                MES,

                                                                DESCRICAO_MES,

                                                                ANO,

                                                                SEMESTRE,

                                                                TRIMESTRE )

                VALUES

                                (

                                YEAR(@Data) * 100 + MONTH(@Data),

                                (CASE (MONTH(@Data))

                                                WHEN (1)            THEN ('JANEIRO')

                                                WHEN (2)            THEN ('FEVEREIRO')

                                                WHEN (3)            THEN ('MARÇO')

                                                WHEN (4)            THEN ('ABRIL')

                                                WHEN (5)            THEN ('MAIO')

                                                WHEN (6)            THEN ('JUNHO')

                                                WHEN (7)            THEN ('JULHO')

                                                WHEN (8)            THEN ('AGOSTO')

                                                WHEN (9)            THEN ('SETEMBRO')

                                                WHEN (10)          THEN ('OUTUBRO')

                                                WHEN (11)          THEN ('NOVEMBRO')

                                                WHEN (12)          THEN ('DEZEMBRO')

                                END) + ' DE ' + CONVERT(VARCHAR,YEAR(@Data)),

                                YEAR(@Data),

                                YEAR(@Data) * 100 + CASE WHEN ( DATEPART(QQ ,@Data) < 3 )  THEN (1) ELSE (2) END,

                                YEAR(@Data) * 100 + DATEPART ( QQ , @Data ) )

                SET @Data = DATEADD(MONTH,1,@Data)

                SET @Data = DATEADD(MONTH,1, @Data) - DATEPART(DAY,DATEADD(MONTH,1, @Data))

END

--Carga dimensão Data (Day dimension load)

SET @Data = @DataIni

WHILE (@Data <= @DataFim)

BEGIN

                INSERT INTO DATA (

                                                                DATA,

                                                                ANO,

                                                                SEMESTRE,

                                                                TRIMESTRE,

                                                                MES,

                                                                DIA_UTIL)

                VALUES

                (@Data,

                YEAR(@Data),

                YEAR(@Data) * 100 + CASE WHEN ( DATEPART(QQ ,@Data) < 3 )  THEN (1) ELSE (2) END,

                YEAR(@Data) * 100 + DATEPART ( QQ , @Data ),

                YEAR(@Data) * 100 + MONTH(@Data),

                CASE WHEN ( DATEPART(DW ,@Data) IN (1,7) ) THEN ('N') ELSE ('S') END )

                SET @Data = @Data + 1

END