terça-feira, 16 de julho de 2013

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


Carga da Dimensão Tempo em PL/SQL
(Time Dimension load in PL/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.

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

declare
  Data DATE;
  DataIni DATE;
  DataFim DATE;
  Data_Movel DATE;
BEGIN
  DataIni := to_date('2000/01/01','yyyy/mm/dd');
  DataFim := to_date('2040/12/31','yyyy/mm/dd');
  Data := DataIni;
WHILE Data <= DataFim LOOP
      INSERT INTO TRDIM.ANO ( ANO )
      VALUES ( TO_CHAR(extract(YEAR from Data)) );
      Data := add_months(Data,12);
      Data := add_months(Data,1) - extract(DAY from (add_months(Data,1)));
END LOOP;
COMMIT;
END;

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

declare
  Data DATE;
  DataIni DATE;
  DataFim DATE;
  Data_Movel DATE;

BEGIN
  DataIni := to_date('2000/01/01','yyyy/mm/dd');
  DataFim := to_date('2040/12/31','yyyy/mm/dd');
   Data := DataIni;
WHILE Data <= DataFim LOOP
      INSERT INTO  TRDIM.SEMESTRE(SEMESTRE, DESCRICAO_SEMESTRE, ANO )
      VALUES
          (
            TO_CHAR(extract(YEAR from Data)) || DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'01'
            ,2,'01'
            ,3,'02'
            ,4,'02')
            ,DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'1º Semestre'
            ,2,'1º Semestre'
            ,3,'2º Semestre'
            ,4,'2º Semestre'),
            TO_CHAR(extract(YEAR from Data)) 
          );
      Data := add_months(Data,6);
      Data := add_months(Data,1) - extract(DAY from (add_months(Data,1)));

END LOOP;
COMMIT;
END;

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

declare
  Data DATE;
  DataIni DATE;
  DataFim DATE;
  Data_Movel DATE;
BEGIN
  DataIni := to_date('2000/01/01','yyyy/mm/dd');
  DataFim := to_date('2040/12/31','yyyy/mm/dd');
   Data := DataIni;
WHILE Data <= DataFim LOOP
      INSERT INTO  TRDIM.TRIMESTRE(TRIMESTRE, DESCRICAO_TRIMESTRE, SEMESTRE,ANO)
      VALUES
          (
            TO_CHAR(extract(YEAR from Data)) || DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'01'
            ,2,'02'
            ,3,'03'
            ,4,'04')
            ,DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'1º Trimestre'
            ,2,'2º Trimestre'
            ,3,'3º Trimestre'
            ,4,'4º Trimestre'),  
            TO_CHAR(extract(YEAR from Data)) || DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'01'
            ,2,'01'
            ,3,'02'
            ,4,'02'),
            TO_CHAR(extract(YEAR from Data)) 
          );
      Data := add_months(Data,3);
      Data := add_months(Data,1) - extract(DAY from (add_months(Data,1)));
END LOOP;
COMMIT;
END;

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

declare
  Data DATE;
  DataIni DATE;
  DataFim DATE;
  Data_Movel DATE;
BEGIN
  DataIni := to_date('2000/01/01','yyyy/mm/dd');
  DataFim := to_date('2040/12/31','yyyy/mm/dd');
   Data := DataIni;
WHILE Data <= DataFim LOOP

      INSERT INTO TRDIM.MES (MES, DESCRICAO_MES, ANO, SEMESTRE, TRIMESTRE)
      VALUES
          (
             TO_CHAR(extract(YEAR from Data)) || TO_CHAR(Data, 'MM'),
            (CASE extract(MONTH from 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 ' || TO_CHAR(extract(YEAR from Data)),
            TO_CHAR(extract(YEAR from Data)),
            TO_CHAR(extract(YEAR from Data)) || DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'01'
            ,2,'01'
            ,3,'02'
            ,4,'02'),
            TO_CHAR(extract(YEAR from Data)) || DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'01'
            ,2,'02'
            ,3,'03'
            ,4,'04')
          );

      Data := add_months(Data,1);
      Data := add_months(Data,1) - extract(DAY from (add_months(Data,1)));
END LOOP;
COMMIT;
END;

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

declare
  Data DATE;
  DataIni DATE;
  DataFim DATE;
  Data_Movel DATE;

BEGIN
  DataIni := to_date('2000/01/01','yyyy/mm/dd');
  DataFim := to_date('2040/12/31','yyyy/mm/dd');
  Data := DataIni;
  data_movel := DataFim;
WHILE Data <= DataFim LOOP
      INSERT INTO TRDIM.DATA (DATA,DIA_UTIL,ANO,SEMESTRE,TRIMESTRE,MES)
      VALUES
      (
      Data,
      CASE WHEN ( TO_NUMBER( TO_CHAR (Data, 'D') ) IN (1,7) ) THEN ('N')
           ELSE ('S') END,
      TO_CHAR(extract(YEAR from Data)),
      TO_CHAR(extract(YEAR from Data)) || DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'01'
            ,2,'01'
            ,3,'02'
            ,4,'02'),
            TO_CHAR(extract(YEAR from Data)) || DECODE(TO_NUMBER(TO_CHAR(Data,'Q'))
            ,1,'01'
            ,2,'02'
            ,3,'03'
            ,4,'04'),
            TO_CHAR(extract(YEAR from Data)) || TO_CHAR(Data, 'MM')
      );
      Data := Data + 1;
END LOOP;
COMMIT;

END;

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