Material de Apoio

Olá, boa tarde!

Seja bem-vindo a mais um post da sessão Material de Apoio, uma das mais antigas sessões compartilhadas aqui no meu blog.

Introdução

A cada novo post, você vai encontrar uma imagem que representa não exatamente o conteúdo do post, mas sim a época, período ou estação do ano em que o mesmo foi publicado.

Uma forma diferente e até mesmo divertida que encontrei para tentar te ajudar a se lembrar do que você acessou em mais esta visita.

Essa não é uma sugestão minha, alguns visitantes em seus comentários, destacavam um pouco de dificuldade em encontrar determinado do conteúdo ou post, como também, não conseguiam se lembrar o que havia sido acessado.

Desta maneira, tomei a decisão de adicionar uma imagem, figura ou símbolo que traga uma certa lembrança como forma de ajudar o visitante ou seguidor do meu blog a encontrar o conteúdo desejado.

O post de hoje esta sendo representado através da Figura 1 a seguir, a qual se relaciona com a atual estação do ano que tradicionalmente faz parte de reta final do ano, o Inverno.

Figura 1 – Símbolo que representa conteúdo compartilhado no post 176.

O post de hoje

Representa o terceiro post desta sessão no ano de 2023 e de número 177 no total da mesma.

Para aqueles que me acompanham, sabem que os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Todos os arquivos compartilhados neste post, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA e atualmente como Professor de Banco de Dados, foram concebidos com base nas atividades realizadas mediantes aos meus estudos e consultorias aplicadas ao Microsoft SQL Server em seus mais diversos recursos e funcionalidades.

Novidade: A partir deste post apresento uma novidade (“particularmente falando, demorei muito tempo para adotar!”), faço referência ao compartilhamento de código, código fonte ou scripts de exemplo diretamente introduzido no post ao invés de publicar o arquivo.

Destaco a seguir, os scripts que não necessariamente representam a melhor solução, os quais, na verdade satisfazem um possível entendimento da minha parte de acordo com o cenário ao qual foram analisados e implementados.

Neste post você vai encontrar os seguintes temas:

Chave Primária;
Comando Alter Database;
Comando Begin;
Comando Create Database;
Comando Create Table;
Comando Declare;
Comando End;
Comando Insert;
Comando Select;
Comando Use;
CTE;
Data Warehouse;
Dimensões;
Dimensão de Tempo;
Função Convert();
Função DateAdd();
Função Datepart();
Função Day();
Função de Classificação Row_Number();
Função GetDate();
Função Identity();
Função Month();
Função NewId();
Função Year();
Hierarquias;
Modelo de Recuperação Bulk_Logged;
Níveis de Granularidade;
Operador Lógico Condicional While;
Operador Lógico Condiconal Case;
Operador Top;
Operador Union;
Operador Union All;
Select Derivado;
Tipo de Dados Char();
Tipo de Dados Data;
Tipo de Dados DateTime;
Tipo de Dados Hora;
Tipo de Dados Int;
Tipo de Dados TinyInt; e
Tipo de Dados Varchar().

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas.

1 - Material de Apoio - Construindo uma Dimensão de Tempo.

-- Criando o Banco de Dados --
Create Database AnaliseDeDados
Go

-- Acessando --
Use AnaliseDeDados
Go

-- Alterando o Modelo de Recuperação do Banco de Dados para Bulk_Logged --
Alter Database AnaliseDeDados
Set Recovery Bulk_Logged
Go

-- Apresentando a Data Atual e Hora --
Select GetDate() As DataCalendarioAtual
Go

-- Definindo o Domingo primeiro dia da semana --
Set DateFirst 1
Go

-- Dimensionando com base no valor da data e hora os atributos relacionados ao assunto Data --
Select Day(GetDate()) As Dia,
       Datepart(DW, GetDate()) As DiaDaSemana,
	   Case Datepart(DW, GetDate())
	    When 1 Then 'Segunda-Feira'
		When 2 Then 'Terça-Feira'
		When 3 Then 'Quarta-Feira'
		When 4 Then 'Quinta-Feira'
		When 5 Then 'Sexta-Feira'
		When 6 Then 'Sábado'
		When 7 Then 'Domingo'
	   End As DiaDaSemanaPorExtenso,
	   Datepart(D, GetDate()) As DiaDoMes,
	   Datepart(DayOfYear, GetDate()) As DiaDoAno,
	   DatePart(Week, GetDate()) As Semana,
	   Datepart(Day, Datediff(day, 0, GetDate())/7 * 7)/7 + 1 As SemanaNoMes,
	   DatePart(Week, GetDate()) As SemanaNoAno,
	   Day(DateAdd(Week, Datediff(Week, 0, GetDate()), 0)) As PrimeiroDiaDaSemana,
	   Day(DateAdd(Week, Datediff(Week, 0, GetDate()), 7)) As UltimoDiaDaSemana,
	   Month(GetDate()) As Mes,
	   Case Month(GetDate()) 
	    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 As MesPorExtenso,
	   Case 
	    When DatePart(Q, GetDate()) = 1 And Month(GetDate()) = 1 Then 1
		When DatePart(Q, GetDate()) = 1 And Month(GetDate()) = 2 Then 1
		When DatePart(Q, GetDate()) = 1 And Month(GetDate()) = 3 Then 1
		When DatePart(Q, GetDate()) = 2 And Month(GetDate()) = 4 Then 2
		When DatePart(Q, GetDate()) = 2 And Month(GetDate()) = 5 Then 2
		When DatePart(Q, GetDate()) = 2 And Month(GetDate()) = 6 Then 2
		When DatePart(Q, GetDate()) = 3 And Month(GetDate()) = 7 Then 3
		When DatePart(Q, GetDate()) = 3 And Month(GetDate()) = 8 Then 3
		When DatePart(Q, GetDate()) = 3 And Month(GetDate()) = 9 Then 3
		When DatePart(Q, GetDate()) = 4 And Month(GetDate()) = 10 Then 4
		When DatePart(Q, GetDate()) = 4 And Month(GetDate()) = 11 Then 4
		When DatePart(Q, GetDate()) = 4 And Month(GetDate()) = 12 Then 4
	   End As MesQuartil,
	   DatePart(Q, GetDate()) As Quartil,
	   Case DatePart(Q, GetDate())
	    When 1 Then 'Primeiro Quartil'
	    When 2 Then 'Segundo Quartil'
	    When 3 Then 'Terceiro Quartil'
	    When 4 Then 'Quarto Quartil'
	   End As QuartilPorExtenso,
	   DatePart(Year,GetDate()) As Ano,
	   'Dois mil e vinte e três' As AnoPorExtenso,
	   Convert(Date, GetDate()) As 'Data',
	   Convert(Time, GetDate(),114) As 'Hora Atual',
	   GetDate() As 'Data e Hora Atual',
	   Datepart(HOUR, GetDate()) As Horas,
	   Datepart(MINUTE, GetDate()) As Minuto,
	   Case 
	    When Day(GetDate()) = 01 And Month(GetDate())=01 Then 'Feriado'
		When Day(GetDate()) = 21 And Month(GetDate())=04 Then 'Feriado'
		When Day(GetDate()) = 01 And Month(GetDate())=05 Then 'Feriado'
		When Day(GetDate()) = 07 And Month(GetDate())=09 Then 'Feriado'
		When Day(GetDate()) = 12 And Month(GetDate())=10 Then 'Feriado'
		When Day(GetDate()) = 02 And Month(GetDate())=11 Then 'Feriado'
		When Day(GetDate()) = 15 And Month(GetDate())=11 Then 'Feriado'
		When Day(GetDate()) = 25 And Month(GetDate())=12 Then 'Feriado'
	    Else 'Não é feriado'
	   End As FeriadoPorExtenso
Go

-- Criando a Tabela Calendario --
Create Table Calendario
 (CodigoCalendario Int Identity(1,1) Primary Key Clustered,
  DataCalendario DateTime)
Go

-- Inserindo 10000 registros lógicos na Tabela Calendario --
Declare @Contador Int

Set @Contador=1

While @Contador <=10000
 Begin

  Insert Into Calendario Values (GetDate()+@Contador)

  Set @Contador = @Contador+1

 End
Go

-- Consultando --
Select * From Calendario
Go

-- Criando a Dimensão Tempo - DimTime --
Create Table DimTime
(TimeID Int Identity(1,1) Not Null,
 Dia TinyInt Not Null,
 DiaDaSemana TinyInt Not Null,
 DiaDaSemanaPorExtenso Varchar(15) Not Null,
 DiaDoMes TinyInt Not Null,
 DiaNoAno SmallInt Not Null,
 Semana TinyInt Not Null,
 SemanaNoMes TinyInt Not Null,
 SemanaNoAno TinyInt Not Null,
 PrimerioDiaDaSemana TinyInt Not Null,
 UltimoDiaDaSemana TinyInt Not Null,
 Mes TinyInt Not Null,
 MesPorExtenso Varchar(15) Not Null,
 Quartil TinyInt Not Null,
 MesQuartil TinyInt Not Null,
 QuartilPorExtenso Varchar(20) Not Null,
 Ano Int Not Null,
 AnoPorExtenso Varchar(40) Not Null,
 DataAtual Date Not Null,
 HoraAtual Time Not Null,
 DataHora DateTime Not Null,
 Horas TinyInt Not Null,
 Minutos TinyInt Not Null,
 Segundos TinyInt Not Null,
 FeriadoPorExtenso Varchar(15) Not Null
 Constraint [PK_DimTime_TimeID] Primary Key Clustered (TimeID))
Go

-- Populando as Datas com base na Tabela Calendario na DimTime --
Insert Into DimTime ([Dia], [DiaDaSemana], [DiaDaSemanaPorExtenso], [DiaDoMes], [DiaNoAno], [Semana], [SemanaNoMes], [SemanaNoAno], [PrimerioDiaDaSemana], [UltimoDiaDaSemana], [Mes], [MesPorExtenso], [Quartil], [MesQuartil], [QuartilPorExtenso], [Ano], [AnoPorExtenso], [DataAtual], [HoraAtual], [DataHora], [Horas], [Minutos], [Segundos], [FeriadoPorExtenso])
Select Day(DataCalendario) As Dia,
       Datepart(DW, DataCalendario) As DiaDaSemana,
	   Case Datepart(DW, DataCalendario)
	    When 1 Then 'Segunda-Feira'
		When 2 Then 'Terça-Feira'
		When 3 Then 'Quarta-Feira'
		When 4 Then 'Quinta-Feira'
		When 5 Then 'Sexta-Feira'
		When 6 Then 'Sábado'
		When 7 Then 'Domingo'
	   End As DiaDaSemanaPorExtenso,
	   Datepart(D, DataCalendario) As DiaDoMes,
	   Datepart(DayOfYear, DataCalendario) As DiaDoAno,
	   DatePart(Week, DataCalendario) As Semana,
	   Datepart(Day, Datediff(day, 0, DataCalendario)/7 * 7)/7 + 1 As SemanaNoMes,
	   DatePart(Week, DataCalendario) As SemanaNoAno,
	   Day(DateAdd(Week, Datediff(Week, 0, DataCalendario), 0)) As PrimeiroDiaDaSemana,
	   Day(DateAdd(Week, Datediff(Week, 0, DataCalendario), 7)) As UltimoDiaDaSemana,
	   Month(DataCalendario) As Mes,
	   Case Month(DataCalendario) 
	    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 As MesPorExtenso,
	   DatePart(Q, DataCalendario) As Quartil,
	   Case 
	    When DatePart(Q, DataCalendario) = 1 And Month(DataCalendario) = 1 Then 1
		When DatePart(Q, DataCalendario) = 1 And Month(DataCalendario) = 2 Then 1
		When DatePart(Q, DataCalendario) = 1 And Month(DataCalendario) = 3 Then 1
		When DatePart(Q, DataCalendario) = 2 And Month(DataCalendario) = 4 Then 2
		When DatePart(Q, DataCalendario) = 2 And Month(DataCalendario) = 5 Then 2
		When DatePart(Q, DataCalendario) = 2 And Month(DataCalendario) = 6 Then 2
		When DatePart(Q, DataCalendario) = 3 And Month(DataCalendario) = 7 Then 3
		When DatePart(Q, DataCalendario) = 3 And Month(DataCalendario) = 8 Then 3
		When DatePart(Q, DataCalendario) = 3 And Month(DataCalendario) = 9 Then 3
		When DatePart(Q, DataCalendario) = 4 And Month(DataCalendario) = 10 Then 4
		When DatePart(Q, DataCalendario) = 4 And Month(DataCalendario) = 11 Then 4
		When DatePart(Q, DataCalendario) = 4 And Month(DataCalendario) = 12 Then 4
	   End As MesQuartil,
	   Case DatePart(Q, DataCalendario)
	    When 1 Then 'Primeiro Quartil'
		When 2 Then 'Segundo Quartil'
		When 3 Then 'Terceiro Quartil'
		When 4 Then 'Quarto Quartil'
	   End As QuartilPorExtenso,
	   DatePart(Year,DataCalendario) As Ano,
	   'Dois mil e Vinte e Dois' As AnoPorExtenso,
	   Convert(Date, DataCalendario) As Data,
	   Convert(Time, DataCalendario,114) As HoraAtual,
	   DataCalendario As DataAtual,
	   Datepart(HOUR, DataCalendario) As Horas,
	   Datepart(MINUTE, DataCalendario) As Minutos,
	   Datepart(MINUTE, DataCalendario) As Segundos,
	   Case 
	    When Day(DataCalendario) = 01 And Month(DataCalendario)=01 Then 'Feriado'
		When Day(DataCalendario) = 21 And Month(DataCalendario)=04 Then 'Feriado'
		When Day(DataCalendario) = 01 And Month(DataCalendario)=05 Then 'Feriado'
		When Day(DataCalendario) = 07 And Month(DataCalendario)=09 Then 'Feriado'
		When Day(DataCalendario) = 12 And Month(DataCalendario)=10 Then 'Feriado'
		When Day(DataCalendario) = 02 And Month(DataCalendario)=11 Then 'Feriado'
		When Day(DataCalendario) = 15 And Month(DataCalendario)=11 Then 'Feriado'
		When Day(DataCalendario) = 25 And Month(DataCalendario)=12 Then 'Feriado'
	   Else 'Não é feriado'
	   End As FeriadoPorExtenso
From Calendario
Go

-- Consultando 1000 registros lógicos da Dimensão DimTime --
Select Top 1000 * From DimTime
Go

-- Consultando 2000 registros lógicos da Dimensão DimTime de forma aleatória --
Select Top 2000 * From DimTime
Order By NewID()
Go

-- Consultando 2000 registros lógicos da Dimensão DimTime com base no ano --
Select Top 2000 * From DimTime
Where Ano In (2030, 2034, 2045, 2047, 2050)
Order By NewID()
Go

2 - Material de Apoio - Definindo Hieraquias e Níveis de Granularidade

-- Acessando --
Use AnaliseDeDados
Go

-- Exemplo 1 - Utilizando o Operador Union All --
Select '1 ->' As Nivel, YEAR(GetDate()) As Valor, 'Ano' As Descricao
Union All
Select 	'2 -->',		DATEPART(q, GetDate()), 'Quartil'
Union All
Select 	'3 --->',		Month(GetDate()) As Mes, 'Mes'
Union All
Select 	'4 ---->',		Day(GetDate()) As Dia, 'Dia'
Union All
Select 	'5 ----->',		DatePart(HH,GetDate()), 'Horas'
Union All
Select 	'6 ------>',		DatePart(MINUTE,GetDate()), 'Minutos'
Union All
Select 	'7 ------->',		DatePart(SS,GetDate()), 'Segundos'
Union All
Select 	'8 --------->',		DatePart(MILLISECOND,GetDate()), 'Miléssimos'
Go

-- Exemplo 2 - Utilizando Select Derivado --
Select Nivel, Valor, Descricao
From
(
Select '1 ->', YEAR(GetDate()) As Ano, 'Ano'
Union All
Select 	'2 -->',		DATEPART(q, GetDate()), 'Quartil'
Union All
Select 	'3 --->',		Month(GetDate()) As Mes, 'Mes'
Union All
Select 	'4 ---->',		Day(GetDate()) As Dia, 'Dia'
Union All
Select 	'5 ----->',		DatePart(HH,GetDate()), 'Horas'
Union All
Select 	'6 ------>',		DatePart(MINUTE,GetDate()), 'Minutos'
Union All
Select 	'7 ------->',		DatePart(SS,GetDate()), 'Segundos'
Union All
Select 	'8 --------->',		DatePart(MILLISECOND,GetDate()), 'Miléssimos') As Hierarquia (Nivel, Valor, Descricao)
Go

-- Exemplo 3 - Utilizando CTE --
;With Hierarquia (Valor, Descricao)
As
(
Select YEAR(GetDate()) As Ano, 'Ano'
Union
Select DATEPART(q, GetDate()), 'Quartil'
Union
Select Month(GetDate()) As Mes, 'Mes'
Union
Select Day(GetDate()) As Dia, 'Dia'
Union
Select DatePart(HH,GetDate()), 'Horas'
Union
Select DatePart(MINUTE,GetDate()), 'Minutos'
Union
Select DatePart(SS,GetDate()), 'Segundos'
Union
Select DatePart(MILLISECOND,GetDate()), 'Miléssimos'
)
Select Row_Number() Over (Order By (Select Null)) As Nivel, Valor, Descricao
From Hierarquia
Go

3 - Material de Apoio - Modelo Dimensional - Criando o Modelo Estrela - Star Schema

-- Acessando o Banco de Dados --
Use AnaliseDeDados
Go

-- Passo 1 -- Criando a Tabela de Vendas para armazenar dados transacionais --
-- Criando a Tabela Vendas --
Create Table Vendas
 (CodigoVenda Int Identity(1,1) Primary Key,
  CodigoDoCliente Int Not Null, 
  CodigoDoFuncionario Int Not Null, 
  CodigoDoEntregador Int Not Null,
  CodigoDoProduto Int Not Null, 
  TimeID Int Not Null,
  Itens TinyInt,
  Valor Numeric(4,2) Default 10.00,
  Contador TinyInt Default 1)
Go

-- Passo 2 - Criando a Tabela de Histórico de Vendas para trabalhar como Área de Staging --
-- Criando a Tabela Vendas --
Create Table HistoricoVendas
 (CodigoHistoricoVenda Int Identity(1,1) Primary Key,
  CodigoVenda Int Not Null,
  CodigoDoCliente Int Not Null, 
  CodigoDoFuncionario Int Not Null, 
  CodigoDoEntregador Int Not Null,
  CodigoDoProduto Int Not Null, 
  TimeID Int Not Null,
  Itens TinyInt,
  Valor Numeric(4,2),
  Contador TinyInt)
Go

-- Passo 3 - Criando e Populando dados nas Dimensões --

-- Criando a Tabela Calendario --
Create Table Calendario
 (CodigoCalendario Int Identity(1,1) Primary Key Clustered,
  DataCalendario DateTime)
Go

-- Inserindo 10000 linhas de registros lógicos na Tabela Calendario --
Declare @Contador Int

Set @Contador=1

While @Contador <=10000
 Begin

  Insert Into Calendario Values (GetDate()+@Contador)

  Set @Contador = @Contador+1

 End

-- Consultando --
Select * From Calendario
Go

-- Criando a Dimensão Tempo - DimTime --
Create Table DimTime
(TimeID Int Identity(1,1) Not Null,
 DataHora DateTime Not Null,
 Dia TinyInt Not Null,
 DiaDaSemana TinyInt Not Null,
 DiaDaSemanaPorExtenso Varchar(15) Not Null,
 DiaDoMes TinyInt Not Null,
 DiaNoAno SmallInt Not Null,
 Semana TinyInt Not Null,
 SemanaNoMes TinyInt Not Null,
 SemanaNoAno TinyInt Not Null,
 PrimeiroDiaDaSemana TinyInt Not Null,
 UltimoDiaDaSemana TinyInt Not Null,
 Mes TinyInt Not Null,
 MesPorExtenso Varchar(15) Not Null,
 Quartil TinyInt Not Null,
 MesQuartil TinyInt Not Null,
 QuartilPorExtenso Varchar(20) Not Null,
 Ano Int Not Null,
 AnoPorExtenso Varchar(40) Not Null,
 DataAtual Date Not Null,
 HoraAtual Time Not Null,
 Horas TinyInt Not Null,
 Minutos TinyInt Not Null,
 Segundos TinyInt Not Null,
 FeriadoPorExtenso Varchar(15) Not Null
 Constraint [PK_DimTime_TimeID] Primary Key Clustered (TimeID))
Go

-- Populando as Datas com base na Tabela Calendario na DimTime --
Insert Into DimTime ([DataHora],[Dia], [DiaDaSemana], [DiaDaSemanaPorExtenso], [DiaDoMes], [DiaNoAno], [Semana], [SemanaNoMes], [SemanaNoAno], [PrimeiroDiaDaSemana], [UltimoDiaDaSemana], [Mes], [MesPorExtenso], [Quartil], [MesQuartil], [QuartilPorExtenso], [Ano], [AnoPorExtenso], [DataAtual], [HoraAtual], [Horas], [Minutos], [Segundos], [FeriadoPorExtenso])
Select DataCalendario As DataAtual,
        Day(DataCalendario) As Dia,
       DATEPART(DW, DataCalendario) As DiaDaSemana,
	   Case DATEPART(DW, DataCalendario)
	    When 1 Then 'Segunda-Feira'
		When 2 Then 'Terça-Feira'
		When 3 Then 'Quarta-Feira'
		When 4 Then 'Quinta-Feira'
		When 5 Then 'Sexta-Feira'
		When 6 Then 'Sábado'
		When 7 Then 'Domingo'
	   End As DiaDaSemanaPorExtenso,
	   DATEPART(D, DataCalendario) As DiaDoMes,
	   DATEPART(DAYOFYEAR, DataCalendario) As DiaDoAno,
	   DatePart(WEEK, DataCalendario) As Semana,
	   Datepart(Day, Datediff(day, 0, DataCalendario)/7 * 7)/7 + 1 As SemanaNoMes,
	   DatePart(WEEK, DataCalendario) As SemanaNoAno,
	   Day(DateAdd(Week, Datediff(Week, 0, DataCalendario), 0)) As PrimeiroDiaDaSemana,
	   Day(DateAdd(Week, Datediff(Week, 0, DataCalendario), 7)) As UltimoDiaDaSemana,
	   Month(DataCalendario) As Mes,
	   Case Month(DataCalendario) 
	    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 As MesPorExtenso,
	   DatePart(Q, DataCalendario) As Quartil,
	   Case 
	    When DatePart(Q, DataCalendario) = 1 And MONTH(DataCalendario) = 1 Then 1
		When DatePart(Q, DataCalendario) = 1 And MONTH(DataCalendario) = 2 Then 2
		When DatePart(Q, DataCalendario) = 1 And MONTH(DataCalendario) = 3 Then 3
		When DatePart(Q, DataCalendario) = 2 And MONTH(DataCalendario) = 4 Then 4
		When DatePart(Q, DataCalendario) = 2 And MONTH(DataCalendario) = 5 Then 1
		When DatePart(Q, DataCalendario) = 2 And MONTH(DataCalendario) = 6 Then 2
		When DatePart(Q, DataCalendario) = 3 And MONTH(DataCalendario) = 7 Then 3
		When DatePart(Q, DataCalendario) = 3 And MONTH(DataCalendario) = 8 Then 4
		When DatePart(Q, DataCalendario) = 3 And MONTH(DataCalendario) = 9 Then 1
		When DatePart(Q, DataCalendario) = 4 And MONTH(DataCalendario) = 10 Then 2
		When DatePart(Q, DataCalendario) = 4 And MONTH(DataCalendario) = 11 Then 3
		When DatePart(Q, DataCalendario) = 4 And MONTH(DataCalendario) = 12 Then 4
	   End As MesQuartil,
	   Case DatePart(Q, DataCalendario)
	    When 1 Then 'Primeiro Quartil'
		When 2 Then 'Segundo Quartil'
		When 3 Then 'Terceiro Quartil'
		When 4 Then 'Quarto Quartil'
	   End As QuartilPorExtenso,
	   DatePart(Year,DataCalendario) As Ano,
	   'Dois mil e Vinte e Dois' As AnoPorExtenso,
	   Convert(Date, DataCalendario) As Data,
	   Convert(Time, DataCalendario,114) As HoraAtual,
	   DATEPART(HOUR, DataCalendario) As Horas,
	   DATEPART(MINUTE, DataCalendario) As Minutos,
	   DATEPART(MINUTE, DataCalendario) As Segundos,
	   Case 
	    When Day(DataCalendario) = 01 And Month(DataCalendario)=01 Then 'Feriado'
		When Day(DataCalendario) = 21 And Month(DataCalendario)=04 Then 'Feriado'
		When Day(DataCalendario) = 01 And Month(DataCalendario)=05 Then 'Feriado'
		When Day(DataCalendario) = 07 And Month(DataCalendario)=09 Then 'Feriado'
		When Day(DataCalendario) = 12 And Month(DataCalendario)=10 Then 'Feriado'
		When Day(DataCalendario) = 02 And Month(DataCalendario)=11 Then 'Feriado'
		When Day(DataCalendario) = 15 And Month(DataCalendario)=11 Then 'Feriado'
		When Day(DataCalendario) = 25 And Month(DataCalendario)=12 Then 'Feriado'
	   Else 'Não é feriado'
	   End As FeriadoPorExtenso
From Calendario
Go

-- Criando a Dimensao Produto - DimProduto --
Create Table DimProduto
 (CodigoDoProduto Int Identity(1,1) Primary Key Clustered,
  NumeroIdentificacao Int Not Null,
  CodigoDeBarras Int Not Null,
  Descricao Varchar(50) Not Null,
  Apelido Varchar(20) Not Null,
  Sigla Char(3) Default 'PRO',
  Peso Float Not Null,
  Quantidade Int Not Null,
  DataCadastro DateTime Default GetDate(),
  AnoCadastro As Year(DataCadastro),
  MesCadastro As Month(DataCadastro),
  DiaCadastro As Day(DataCadastro),
  DataFabricacao Date Not Null)
Go

-- Populando os dados na Dimensao Produto - DimProduto --
Declare @ContadorDeLinhas SmallInt

Set @ContadorDeLinhas = 1

While @ContadorDeLinhas <= 20000
 Begin
  
  Insert Into DimProduto (NumeroIdentificacao, CodigoDeBarras, Descricao, Apelido, Peso, Quantidade, DataFabricacao)
  Values(Rand()*10000+@ContadorDeLinhas, '78962431'+Rand()*789, Concat('Produto ',@ContadorDeLinhas), Char(Rand()*255), Rand()*100+1, Rand()*100+2, DateAdd(D,Rand()*365+1,GetDate()))

  Set @ContadorDeLinhas = @ContadorDeLinhas+1
End

-- Consultando --
Select * From DimProduto
Go

-- Criando a Dimensao Cliente - DimCliente --
Create Table DimCliente
 (CodigoDoCliente Int Identity(1,1) Primary Key Clustered,
   CPF Int Not Null,
   RG Int Not Null,
   Nome Varchar(80) Not Null,
   Apelido Varchar(20) Not Null,
   DataNascimento Date Not Null,
   AnoNascimento As Year(DataNascimento),
   MesNascimento As Month(DataNascimento),
   DiaNascimeto As Day(DataNascimento),
   Endereco Varchar(200),
   Estado Varchar(50),
   Municipio Varchar(100),
   Salario Numeric(6,2))
Go

-- Populando os dados na Dimensao Cliente - DimCliente --
Declare @ContadorDeLinhas Int

Set @ContadorDeLinhas = 1

While @ContadorDeLinhas <= 40000
 Begin
  
  Insert Into DimCliente (CPF, RG, Nome, Apelido, DataNascimento, Endereco, Estado, Municipio, Salario)
  Values(Rand()*1000000, Rand()*2000000, Concat('Cliente ',@ContadorDeLinhas), Char(Rand()*96),
               GetDate()-Rand()*6570,
			   Concat('Endereço - ',@ContadorDeLinhas,', ',Char(Rand()*255),Char(Rand()*255),Char(Rand()*255)), 
			   'São Paulo',
			   Concat('Municipio ',Char(Rand()*65),Char(Rand()*96)),
			   Rand()*9999)

  Set @ContadorDeLinhas = @ContadorDeLinhas+1
End

-- Consultando --
Select * From DimCliente
Go

-- Criando a Dimensao Funcionario - DimFuncionario --
Create Table DimFuncionario
 (CodigoDoFuncionario Int Identity(1,1) Primary Key Clustered,
   CPF Int Not Null,
   RG Int Not Null,
   Nome Varchar(80) Not Null,
   DataNascimento Date Not Null,
   AnoNascimento As Year(DataNascimento),
   MesNascimento As Month(DataNascimento),
   DiaNascimeto As Day(DataNascimento),
   Endereco Varchar(200),
   Estado Varchar(50),
   Municipio Varchar(100),
   Salario Numeric(6,2))
Go

-- Populando os dados na Dimensao Funcionario - DimFuncionario --
Declare @ContadorDeLinhas SmallInt

Set @ContadorDeLinhas = 1

While @ContadorDeLinhas <= 500
 Begin
  
  Insert Into DimFuncionario(CPF, RG, Nome, DataNascimento, Endereco, Estado, Municipio, Salario)
  Values(Rand()*1000000, Rand()*2000000, Concat('Funcionario ',Char(Rand()*255),Char(Rand()*255),Char(Rand()*255)), 
               GetDate()-Rand()*6570,
			   Concat('Endereço - ',@ContadorDeLinhas,', ',Char(Rand()*255),Char(Rand()*255),Char(Rand()*255)), 
			   'São Paulo',
			   Concat('Municipio ',Char(Rand()*65),Char(Rand()*96)),
			   Rand()*9999)

  Set @ContadorDeLinhas = @ContadorDeLinhas+1
End

-- Consultando --
Select * From DimFuncionario
Go

-- Criando a Dimensao Entregador - DimEntregador --
Create Table DimEntregador
 (CodigoDoEntregador Int Identity(1,1) Primary Key Clustered,
   CPF Int Not Null,
   Nome Varchar(80) Not Null,
   DataNascimento Date Not Null,
   Endereco Varchar(200),
   Estado Varchar(50),
   Municipio Varchar(100))
Go

-- Populando os dados na Dimensao Entregador - DimEntregador --
Declare @ContadorDeLinhas SmallInt

Set @ContadorDeLinhas = 1

While @ContadorDeLinhas <= 50
 Begin
  
  Insert Into DimEntregador(CPF, Nome, DataNascimento, Endereco, Estado, Municipio)
  Values(Rand()*1000000, Concat('Entregador ',@ContadorDeLinhas), 
               GetDate()-Rand()*6570,
			   Concat('Endereço - ',@ContadorDeLinhas), 
			   'São Paulo',
			   Concat('Municipio - ',@ContadorDeLinhas))

  Set @ContadorDeLinhas = @ContadorDeLinhas+1
End

-- Consultando --
Select * From DimEntregador
Go

-- Passo 4 - Criando a Tabela FatoVendas e seus respectivos relacionamentos --
-- Criando a Tabela FatoVendas e Estabelecendo a Métrica SomaMetricaQuantidadeVendas --
Create Table FatoVendas
 (CodigoFato Int Identity(1,1) Primary Key,
  CodigoDoCliente Int Not Null, 
  CodigoDoFuncionario Int Not Null, 
  CodigoDoEntregador Int Not Null,
  CodigoDoProduto Int Not Null, 
  TimeID Int Not Null,
  MetricaSomaItens Int Not Null,
  MetricaSomaValor Numeric(10,2) Not Null,
  MetricaSomaValorPorItem As (MetricaSomaItens / MetricaSomaValor),
  MetricaContadorDeVendas Int)
Go

4 - Material de Apoio - Populando os dados na Tabela Vendas

-- Acessando --
Use AnaliseDeDados
Go

-- Passo 5 - Iniciando o processo de carga de dados -- Realizando simulações de Vendas --
-- Declarando as variáveis de controle --
Declare @CodigoDoCliente Int, @CodigoDoFuncionario Int, @CodigoDoEntregador Int, @CodigoDoProduto Int, @TimeID Int, @Simulacoes BigInt

-- Definindo o número inicial de simulações de vendas --
Set @Simulacoes = 1

-- Processando as simulações de Vendas --
While @Simulacoes <=Rand()*100000
Begin

-- Selecionando de forma aleatório os valores para as variáveis de controle --
 Set @CodigoDoCliente = Rand()*40000
 Set @CodigoDoFuncionario = Rand()*500
 Set @CodigoDoEntregador = Rand()*50
 Set @CodigoDoProduto = Rand()*20000
 Set @TimeID = Rand()*10000
 
 Insert Into Vendas (CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador, CodigoDoProduto, TimeID, Itens, Valor, Contador)
 Select C.CodigoDoCliente, F.CodigoDoFuncionario, E.CodigoDoEntregador, P.CodigoDoProduto, T.TimeID, Rand()*255, Rand()*99.99, 1
 From DimCliente C, DimFuncionario F, DimEntregador E, DimProduto P, DimTime T
 Where C.CodigoDoCliente = @CodigoDoCliente
 And F.CodigoDoFuncionario = @CodigoDoFuncionario
 And E.CodigoDoEntregador = @CodigoDoEntregador
 And P.CodigoDoProduto = @CodigoDoProduto
 And T.TimeID = @TimeID

 Set @Simulacoes = @Simulacoes + 1
End

-- Exibindo o total de simulações realizadas --
Select @Simulacoes As 'Simulações processadas'
Go

-- Consultando --
Select CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador, CodigoDoProduto, TimeID, 
           Itens, Valor, Contador
From Vendas
Order By CodigoDoCliente
Go

5 - Material de Apoio - Populando os dados na Tabela FatoVendas

-- Acessando --
Use AnaliseDeDados
Go

-- Passo 6 - Populando "Carregando" os dados na Tabela FatoVendas --
Insert Into FatoVendas (CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador, CodigoDoProduto, TimeID, MetricaSomaItens,  MetricaSomaValor, MetricaContadorDeVendas)
Select CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador, CodigoDoProduto, TimeID, Itens, Valor, Contador
From Vendas
Order By CodigoDoCliente
Go

-- Consultando a Tabela FatoVendas --
Select CodigoFato, CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador, CodigoDoProduto, TimeID, 
       MetricaSomaItens, MetricaSomaValor, MetricaSomaValorPorItem, MetricaContadorDeVendas
From FatoVendas
Order By CodigoDoCliente
Go

-- Passo 7 - Realizando o processamento analítico de dados - Aplicando a sumarização de valores na Tabela FatoVendas afim de gerar o resultado das Métricas --
Select CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador, CodigoDoProduto, TimeID, 
           Sum(MetricaSomaItens) As SomaGeralDeItens,
		   Sum(MetricaSomaValor) As SomaGeralDeValores,
		   Sum(MetricaSomaValorPorItem) As SomaGeralPorItensVendidos,
		   Count(MetricaContadorDeVendas) As SomaGeralDeVendasRealizadas
From FatoVendas
Group By CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador, CodigoDoProduto, TimeID
Order By CodigoDoCliente, CodigoDoFuncionario
Go

-- Será necessário realizar novas simulações para podermos realmente analisar de forma analíticas os dados da Tabela FatoVendas --

-- Passo 7.1 -- Gerando o Histórico de Vendas --
Insert Into HistoricoVendas (CodigoVenda, CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador,
                                              CodigoDoProduto, TimeID, Itens, Valor, Contador)
Select CodigoVenda, CodigoDoCliente, CodigoDoFuncionario, CodigoDoEntregador, CodigoDoProduto, TimeID, Itens, Valor, Contador
From Vendas
Order By CodigoVenda
Go

-- Passo 7.2 -- Limpando os Dados de Vendas preparando para uma nova simulação --
Truncate Table Vendas
Go

-- Passo 7.3 -- Realizar nova simulação de vendas --

6 - Material de Apoio - Realizando uma Pré-Analise - Tabela FatoVendas

-- Acessando --
Use AnaliseDeDados
Go

-- Passo 7.4 -- Realizando a Pré-Analise de Dados - Tabela FatoVendas - Contagem de Produtos Vendidos --
Select CodigoDoProduto, Count(MetricaContadorDeVendas) As SomaGeralDeVendasRealizadas,
           Sum(MetricaSomaItens) As SomaGeralDeItens,
		   Sum(MetricaSomaValor) As SomaGeralDeValores,
		   Sum(MetricaSomaValorPorItem) As SomaGeralPorItensVendidos		   
From FatoVendas
Group By CodigoDoProduto
Order By SomaGeralDeVendasRealizadas Desc
Go

-- Passo 7.5 -- Realizando a Pré-Analise de Dados - Tabela FatoVendas - Contagem de Vendas Geral Por Funcionários por Time --
Select CodigoDoFuncionario, Count(TimeID) As ContagemDeVendaPorTime, 
           Sum(MetricaSomaItens) As SomaGeralDeItens,
		   Sum(MetricaSomaValor) As SomaGeralDeValores,
		   Sum(MetricaSomaValorPorItem) As SomaGeralPorItensVendidos		   
From FatoVendas
Group By  CodigoDoFuncionario
Order By ContagemDeVendaPorTime Desc
Go

Fique a vontade para copiar, editar, compartilhar e distribuir estes conteúdos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

Nota: Todos os exemplos de código, código fonte ou scripts aqui disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

Material de Apoio – Maio 2023

Material de Apoio – Janeiro 2023

Material de Apoio – Junho 2022

Material de Apoio – Fevereiro 2022

Material de Apoio – Agosto 2021

Material de Apoio – Abril 2021

Material de Apoio – Janeiro 2021

Feedback

Espero que você goste do conteúdo aqui disponibilizado, como também, possa me ajudar a torná-lo ainda melhor no decorrer do tempo com a sua participação, preenchendo o formulário abaixo:

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado brevemente.

Neste momento o melhor a fazer é se manter saudável mentalmente, fisicamente e emocionalmente.

Até mais.

Junior Galvão – MVP – Data Platform

Select 'MVP | MCC | MSTC | MIE | MTAC' As Awards

Material de Apoio – Setembro 2023

O post de hoje