Populando dimensão tempo no SQL Server 2012

Populando dimensão tempo no SQL Server 2012 

Neste vídeo demonstro como gerar script para criar tabelas do DW, no ERwin data modeler 7.3 e populo a dimensão tempo com uma procedure que estarei disponibilizando no BLOG da Q-TI.

Script:

--criar tabela dimensao tempo

CREATE TABLE DIM_TEMPO(

 SK_TEMPO int IDENTITY(1,1) NOT NULL PRIMARY KEY,

 DATA date NOT NULL,

 ANO smallint NOT NULL,

 MES smallint NOT NULL,

 DIA smallint NOT NULL,

 DIA_SEMANA smallint NOT NULL,

 DIA_ANO smallint NOT NULL,

 ANO_BISSEXTO char(1) NOT NULL,

 DIA_UTIL char(1) NOT NULL,

 FIM_SEMANA char(1) NOT NULL,

 FERIADO char(1) NOT NULL,

 PRE_FERIADO char(1) NOT NULL,

 POS_FERIADO char(1) NOT NULL,

 NOME_FERIADO nvarchar(30) NULL,

 NOME_DIA_SEMANA nvarchar(15) NOT NULL,

 NOME_DIA_SEMANA_ABREV char(3) NOT NULL,

 NOME_MES nvarchar(15) NOT NULL,

 NOME_MES_ABREV char(3) NOT NULL,

 QUINZENA smallint NOT NULL,

 BIMESTRE smallint NOT NULL,

 TRIMESTRE smallint NOT NULL,

 SEMESTRE smallint NOT NULL,

 NR_SEMANA_MES smallint NOT NULL,

 NR_SEMANA_ANO smallint NOT NULL,

 ESTACAO_ANO varchar(15) NOT NULL,

 DATA_POR_EXTENSO varchar(50) NOT NULL)

------ procedure para inserir na dim_tempo

Create procedure ISP_insertDimTempo

@dataInicial date, @dataFinal date

as

begin

declare  @data date, 

@ano smallint, @mes smallint, @dia smallint, 

@diaSemana smallint, @diaUtil char(1), @fimSemana char(1), 

@feriado char(1), @preFeriado char(1), @posFeriado char(1), 

@nomeFeriado varchar(30), @nomeDiaSemana varchar(15), 

@nomeDiaSemanaAbrev char(3), @nomeMes varchar(15), 

@nomeMesAbrev char(3), @bimestre smallint, @trimestre smallint, 

@nrSemanaMes smallint, @estacaoAno varchar(15), 

@dataPorExtenso varchar(50)

/*Nao utilizado mais

--informe aqui o período para o qual deseja criar os dados

--set @dataInicial = '01/01/2012'

--set @dataFinal = '31/12/2012'

*/

while @dataInicial <= @dataFinal

begin

set @data = @dataInicial

set @ano = year(@data)

set @mes = month(@data)

set @dia = day(@data)

set @diaSemana = datepart(weekday,@data)

--verifica se é fim de semana ou semana

if @diaSemana in (1,7) 

set @fimSemana = 'S'

else set @fimSemana = 'N'

/* feriados locais/regionais e aqueles que não possuem data fixa 

(carnaval, páscoa e corpus cristis) tb devem ser adicionados aqui */

--nome feriado fixo

if (@mes = 1 and @dia in (1,2)) or (@mes = 12 and @dia = 31) --confraternização universal

set @nomeFeriado = 'confraternização universal'

else 

if (@mes = 4 and @dia in (20,21,22)) --tiradentes

set @nomeFeriado = 'tiradentes'

else 

if (@mes = 5 and @dia in (1,2))or (@mes = 4 and @dia = 30) --dia do trabalho

set @nomeFeriado = 'dia do trabalho'

else 

if (@mes = 9 and @dia in (6,7,8)) --independência do brasil

set @nomeFeriado = 'independência do brasil'

else 

if (@mes = 10 and @dia in (11,12,13)) --nossa senhora aparecida

set @nomeFeriado = 'nossa senhora aparecida'

else

if (@mes = 11 and @dia in (1,2,3)) --finados

set @nomeFeriado = 'finados'

else

if (@mes = 11 and @dia in (14,15,16)) --proclamação da república

set @nomeFeriado = 'proclamação da república'

else

if (@mes = 12 and @dia in (24,25,26)) --natal

set @nomeFeriado = 'natal'

else set @nomeFeriado = null

--pre feriado

if (@mes = 12 and @dia = 31) or --confraternização universal

(@mes = 4 and @dia = 20) or --tiradentes

(@mes = 4 and @dia = 30) or --dia do trabalho

(@mes = 9 and @dia = 6) or --independência do brasil

(@mes = 10 and @dia = 11) or --nossa senhora aparecida

(@mes = 11 and @dia = 1) or --finados

(@mes = 11 and @dia = 14) or --proclamação da república

(@mes = 12 and @dia = 24) --natal

set @preFeriado = 'S'

else set @preFeriado = 'N'

--feriado

if (@mes = 1 and @dia = 1) or --confraternização universal

(@mes = 4 and @dia = 21) or --tiradentes

(@mes = 5 and @dia = 1) or --dia do trabalho

(@mes = 9 and @dia = 7) or --independência do brasil

(@mes = 10 and @dia = 12) or --nossa senhora aparecida

(@mes = 11 and @dia = 2) or --finados

(@mes = 11 and @dia = 15) or --proclamação da república

(@mes = 12 and @dia = 25) --natal

set @feriado = 'S'

else set @feriado = 'N'

--pos feriado

if (@mes = 1 and @dia = 2) or --confraternização universal

(@mes = 4 and @dia = 22) or --tiradentes

(@mes = 5 and @dia = 2) or --dia do trabalho

(@mes = 9 and @dia = 8) or --independência do brasil

(@mes = 10 and @dia = 13) or --nossa senhora aparecida

(@mes = 11 and @dia = 3) or --finados

(@mes = 11 and @dia = 16) or --proclamação da república

(@mes = 12 and @dia = 26) --natal

set @posFeriado = 'S'

else set @posFeriado = 'N'

--identicar mes

set @nomeMes = case when @mes = 1 then 'janeiro'

when @mes = 2 then 'fevereiro'

when @mes = 3 then 'março'

when @mes = 4 then 'abril'

when @mes = 5 then 'maio'

when @mes = 6 then 'junho'

when @mes = 7 then 'julho'

when @mes = 8 then 'agosto'

when @mes = 9 then 'setembro'

when @mes = 10 then 'outubro'

when @mes = 11 then 'novembro'

when @mes = 12 then 'dezembro' end

--nome mes abreviado

set @nomeMesAbrev = case when @mes = 1 then 'jan'

when @mes = 2 then 'fev'

when @mes = 3 then 'mar'

when @mes = 4 then 'abr'

when @mes = 5 then 'mai'

when @mes = 6 then 'jun'

when @mes = 7 then 'jul'

when @mes = 8 then 'ago'

when @mes = 9 then 'set'

when @mes = 10 then 'out'

when @mes = 11 then 'nov'

when @mes = 12 then 'dez' end

--identicar dia util

if @fimSemana = 'S' or @feriado = 'S'

set @diaUtil = 'N'

else set @diaUtil = 'S'

--identificar dia da semana

set @nomeDiaSemana = case when @diaSemana = 1 then 'domingo'

when @diaSemana = 2 then 'segunda-feira'

when @diaSemana = 3 then 'terça-feira'

when @diaSemana = 4 then 'quarta-feira'

when @diaSemana = 5 then 'quinta-feira'

when @diaSemana = 6 then 'sexta-feira'

else 'sábado' end

--identificar dia da semana abreviado

set @nomeDiaSemanaAbrev = case when @diaSemana = 1 then 'dom'

when @diaSemana = 2 then 'seg'

when @diaSemana = 3 then 'ter'

when @diaSemana = 4 then 'qua'

when @diaSemana = 5 then 'qui'

when @diaSemana = 6 then 'sex'

else 'sáb' end

--identificar bimestre

set @bimestre = case when @mes in (1,2) then 1

when @mes in (3,4) then 2

when @mes in (5,6) then 3

when @mes in (7,8) then 4

when @mes in (9,10) then 5

else 6 end

--identicar trimestre

set @trimestre = case when @mes in (1,2,3) then 1

when @mes in (4,5,6) then 2

when @mes in (7,8,9) then 3

else 4 end

--identificar numero da semana

set @nrSemanaMes = case when @dia < 8 then 1

when @dia < 15 then 2

when @dia < 22 then 3

when @dia < 29 then 4

else 5 end

--identificar estacao do ano no Brasil

if @data between cast('23/09/'+convert(char(4),@ano) as date) and cast('20/12/'+convert(char(4),@ano) as date)

set @estacaoAno = 'primavera'

else if @data between cast('21/03/'+convert(char(4),@ano) as date) and cast('20/06/'+convert(char(4),@ano) as date)

set @estacaoAno = 'outono'

else if @data between cast('21/06/'+convert(char(4),@ano) as date) and cast('22/09/'+convert(char(4),@ano) as date)

set @estacaoAno = 'inverno'

else -- @data between 21/12 e 20/03

set @estacaoAno = 'verão'

--insert

INSERT INTO DIM_TEMPO

SELECT @data

,@ano

,@mes

,@dia

,@diaSemana

,datepart(dayofyear,@data) --DIA_ANO

,case when (@ano % 4) = 0 then 'S' else 'N' end -- ANO_BISSEXTO

,@diaUtil

,@fimSemana

,@feriado

,@preFeriado

,@posFeriado

,@nomeFeriado

,@nomeDiaSemana

,@nomeDiaSemanaAbrev

,@nomeMes

,@nomeMesAbrev

,case when @dia < 16 then 1 else 2 end -- QUINZENA

,@bimestre

,@trimestre

,case when @mes < 7 then 1 else 2 end -- SEMESTRE

,@nrSemanaMes

,datepart(wk,@data)--NR_SEMANA_ANO, smallint

,@estacaoAno

,lower(@nomeDiaSemana + ', ' + cast(@dia as varchar) + ' de ' + @nomeMes + ' de ' + cast(@ano as varchar))

set @dataInicial = dateadd(day,1,@dataInicial) 

end

end

--executar pprocedure 

exec ISP_insertDimTempo '1990-01-01','2050-12-12'

Youtube: Q-TI Suporte

E-mail : qti.suporte@gmail.com

Facebook : Facebook.com/Q-TI Suporte

Instagram: Instagram/qti.suporte