Projeto Implantação Mega Atacadista
O cliente Mega do ramo Atacadista, necessita armazenar seus dados através de um sistema que será construído posteriormente à modelagem do banco de dados.
A Mega Atacadista contratou a Fagundes BI como consultoria para toda a modelagem e execução do projeto, desde o banco transacional até a entrega dos analises de dados;
A equipe de analistas Fagundes BI iniciou o levantamento de requisitos em 11/01/2021 conforme descrito nesse documento. Em necessidades macro, o projeto foi dividido em 04 grandes fases:
Requisitos de Projeto
Responsável Técnico: Fagundes BI Analistas: Ricardo Fagundes
Fase 01: Construção do ambiente OLTP.
Fase 02: Construção da área de Stage.
Fase 03: Construção do Data Warehouse.
Fase 04: Análise dos dados.
Fase 01
A fase 01 compreende à modelagem relacional do negócio. O SGBD utilizado será o SQL Server 2017, versão Enterprise. À Mega cabe somente a modelagem e construção do banco de dados em sua forma otimizada, ficando à cargo da Fagundes BI a sua manutenção como Backups e segurança.
Artefatos - Entrega Modelagem Lógica
Modelagem Física
CREATE DATABASE COMERCIO_OLTP GO
USE COMERCIO_OLTP GO
CREATE TABLE CLIENTE(
IDCLIENTE INT PRIMARY KEY IDENTITY, NOME VARCHAR(30) NOT NULL,
SOBRENOME VARCHAR(30) NOT NULL, EMAIL VARCHAR(60) NOT NULL,
SEXO CHAR(1) NOT NULL, NASCIMENTO DATE NOT NULL )
GO
CREATE TABLE ENDERECO(
IDENDERECO INT PRIMARY KEY IDENTITY, RUA VARCHAR(100) NOT NULL,
CIDADE VARCHAR(50) NOT NULL, ESTADO VARCHAR(20) NOT NULL, REGIAO VARCHAR(20) NOT NULL, ID_CLIENTE INT UNIQUE
) GO
CREATE TABLE VENDEDOR(
IDVENDEDOR INT PRIMARY KEY IDENTITY, NOME VARCHAR(30) NOT NULL,
SEXO CHAR(1) NOT NULL, EMAIL VARCHAR(30) NOT NULL, ID_GERENTE INT
) GO
CREATE TABLE CATEGORIA(
IDCATEGORIA INT PRIMARY KEY IDENTITY, NOME VARCHAR(30) NOT NULL
) GO
CREATE TABLE FORNECEDOR(
IDFORNECEDOR INT PRIMARY KEY IDENTITY, NOME VARCHAR(50) NOT NULL
) GO
CREATE TABLE PRODUTO(
IDPRODUTO INT PRIMARY KEY IDENTITY, PRODUTO VARCHAR(100) NOT NULL, VALOR NUMERIC(10,2) NOT NULL, CUSTO_MEDIO NUMERIC(10,2), ID_CATEGORIA INT,
ID_FORNECEDOR INT )
GO
CREATE TABLE FORMA_PAGAMENTO(
IDFORMA INT PRIMARY KEY IDENTITY, FORMA VARCHAR(40) NOT NULL
) GO
CREATE TABLE ITEM_NOTA(
IDITEMNOTA INT PRIMARY KEY IDENTITY, ID_PRODUTO INT,
ID_NOTA_FISCAL INT, QUANTIDADE INT, TOTAL NUMERIC(10,2) )
GO
CREATE TABLE NOTA_FISCAL(
IDNOTA INT PRIMARY KEY IDENTITY(1000,10), DATA DATE,
TOTAL NUMERIC(10,2), ID_FORMA INT,
ID_CLIENTE INT, ID_VENDEDOR INT )
GO
Dicionário de Dados Requisitos
A Mega necessita armazenar os seus dados de vendas. O estoque não faz parte do escopo desse projeto, porém, utilizará dos produtos cadastrados nessa modelagem.
Dos cadastros Gerais
De forma geral, seguem os requisitos de cadastro abaixo.
01 – Cadastro de Produtos 02 – Cadastro de Fornecedores 03 – Cadastro de Categorias 04 – Cadastro de Notas Fiscais
05 - Cadastro de Endereço de Clientes 06 – Cadastro de Clientes
07 - Cadastro de Formas de Pagamentos
08 – Cadastro de vendedores (Os vendedores cuidam dos pedidos online, acompanhando os mesmos desde a origem até a entrega, otimizando e solucionando problemas.).
Dos Campos
Todos os cadastros deverão ter números de identificação automáticos e aleatórios, de forma a deixar a cargo do sistema o controle de identificação de transações.
Cadastro: Produto
Produto Nome do Produto
Valor Valor de Venda do Produto Custo Médio Custo de Compra do Produto
Cadastro: Fornecedor
Nome Nome ou Razão Social do Vendedor
Cadastro: Categoria
Nome Nome da Categoria do Produto
Cadastro: Nota Fiscal
Data Data da Venda do(s) Produto(s) Quantidade Quantidade de Itens
Total Item Valor Total dos Itens Total Valor Total da Nota Fiscal
Cadastro: Endereço Rua Nome da Rua Cidade Nome da Cidade Estado Nome do Estado
Região Região (Norte, Sul, Sudeste, etc)
Cadastro: Cliente
Nome Nome do Cliente Sobrenome Sobrenome do Cliente Email Endereço de E-mail completo Sexo Sexo do Cliente
Nascimento Data de Nascimento do Cliente
Cadastro: Forma de Pagamento
Forma Nome da Forma de Pagamento
Cadastro: Vendedor
Nome Nome do Vendedor Sexo Sexo do Vendedor Email Email do Vendedor
Particularidades
Requisitos levantados quanto a detalhes do modelo de negócio da Mega. As particularidades são requisitos e são obrigatórios.
01 – Nas vendas, uma nota fiscal pode conter um ou mais produtos, diferentes ou do mesmo tipo, contendo um subtotal de produtos do mesmo tipo e um total, com a soma de todos os produtos.
02 – Os funcionários possuem gerentes, que deverão ser armazenados no banco de dados.
Regras de Negócio Essenciais
Aqui foram analisadas as regras de negócio junto aos usuários da Mega.
Listei os principais, e caso surjam duvidas ou outras regras, estas deverão ser informadas/documentadas para que sejam implementadas no banco de dados.
• Um fornecedor pode fornece vários produtos.
• Um cliente pode comprar várias vezes
• Uma nota fiscal só pode ter uma forma de pagamento
• Um vendedor pode emitir várias notas fiscais, mas uma nota só pode ser emitida por um vendedor.
• Um cliente só pode ter um endereço
• Um produto só pode pertencer a uma categoria.
• Uma nota fiscal pode ter vários itens de nota.
Constraints
Abaixo segue o dicionário de constraints criadas no banco COMERCIO_OLTP
Script
/* HABILITANDO CONSTRAINTS */
USE COMERCIO_OLTP GO
ALTER TABLE VENDEDOR ADD CONSTRAINT FK_GERENTE
FOREIGN KEY(ID_GERENTE) REFERENCES VENDEDOR(IDVENDEDOR) GO
ALTER TABLE NOTA_FISCAL ADD CONSTRAINT FK_NOTA_CLIENTE
FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE) GO
ALTER TABLE NOTA_FISCAL ADD CONSTRAINT FK_NOTA_VENDEDOR FOREIGN KEY(ID_VENDEDOR) REFERENCES VENDEDOR(IDVENDEDOR) GO
ALTER TABLE ITEM_NOTA ADD CONSTRAINT FK_ITEM_PRODUTO FOREIGN KEY(ID_PRODUTO) REFERENCES PRODUTO(IDPRODUTO) GO
ALTER TABLE ITEM_NOTA ADD CONSTRAINT FK_ITEM_NOTAFISCAL FOREIGN KEY(ID_NOTA_FISCAL) REFERENCES NOTA_FISCAL(IDNOTA) GO
ALTER TABLE PRODUTO ADD CONSTRAINT FK_PRODUTO_FORNECEDOR FOREIGN KEY(ID_FORNECEDOR) REFERENCES FORNECEDOR(IDFORNECEDOR) GO
ALTER TABLE PRODUTO ADD CONSTRAINT FK_PRODUTO_CATEGORIA FOREIGN KEY(ID_CATEGORIA) REFERENCES CATEGORIA(IDCATEGORIA) GO
ALTER TABLE ENDERECO ADD CONSTRAINT FK_ENDERECO_CLIENTE FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE) GO
ALTER TABLE NOTA_FISCAL ADD CONSTRAINT FK_NOTA_FORMA FOREIGN KEY(ID_FORMA) REFERENCES FORMA_PAGAMENTO(IDFORMA) GO
Constraints Aplicadas
Processo de Carga base OLTP
Para o processo de carga da base, utilizei a importação de dados pelo SQL Server, o De um arquivo no caso foi um arquivo excel , poderia ser, csv, poderia ser de uma tabela, somente muda o DataSource, vou demonstrar de uma tabela a de ENDERECO o arquivo CARGA ENDERECO.xls, o processo foi o mesmo De para mudando apenas o arquivo , as tabelas e os campos.
Esta é uma da forma de realizar o processo de ETL
Vou no SQL Import and Export Data (64 Bits)
No wizard escolho O Data Source (A Origem dos Dados) no caso será o arquivo do Excel, configuro o Data Source corretamente
O Destino aponto o drive SQL Native Cliente, o Servidor local, poderia ser um servidor na rede, na nuvem em qualquer lugar repassado pelo cliente, configuro a forma de autenticação e o banco de dados COMERCIO_OLTP;
Escolho copiar os dados, poderia também gerar uma query para posteriormente executar no Manager do SQL Server ou no SQL Plus;
Escolho qual aba da planilha será importada e para qual tabela na base de dados, no caso a aba Enderecos de Clientes Para a Tabela ENDERECOS, após eu mapeio os campos clicando em Edit Mapings;
Agora configurar o De/Para dos campos, a maioria ele já reconhece automático quando os campos tem nome igual ele já reconhece e mapeia, mas as vezes até sendo igual ele não reconhece;
O campo ID ignoramos pois ele é autoincremento;
Temos um resumo de todo o mapeamento, está tudo OK, com algumas mensagens de alerta, no caso aqui é porque estou trazendo de UNICODE para NÃO UNICODE um aviso;
Bom finalizando, eu executar a importação ou eu posso salvar o projeto e abrir no Integration Services para executar posteriormente, pois este processo nada mais é do que o mesmo processo que criamos no SSIS(Integration Services)
No caso se fosse um arquivo que eu tivesse que importar diariamente, ou semanalmente ( caso de importar arquivos de vendas de um caixa)
Importação realizada com sucesso;
Data Warehouse
Perguntas a serem respondidas pelo Data Warehouse
• Quem são os melhores clientes?
• Quem são os melhores vendedores?
• Qual categoria rende mais?
• Qual a minha relação com os fornecedores?
• Qual meu pior e melhor produto?
• Em qual região eu vendo mais?
Entrevista com o manager da Mega,
João Carvalho.-Hoje vendemos muito, temos lucro, mas não sei qual
categoria, fornecedor ou produto me dá mais lucro.Não estou interessado em
quantidade. Preciso de analises sumarizadas. Hoje não me interessa saber quantas vendas tem um vendedor e sim o total vendido, pois os mesmos podem recomendar produtos para os nossos clientes. Também gostaria de sabe meus custos por sazonalidade. Preciso saber em que época do ano gasto mais para controlar meus investimentos em estoque ou contratações.
Saber qual cliente compra mais comigo em termos totais
também seria uma boa ideia. Outra necessidade é um relatório com os dados dos meus clientes, pois a enviar mala direta vai ser uma prática da empresa. Não estou seguro se uma análise por
categoria ou fornecedor seria útil. A sua equipe também pode disponibilizar o que achar relevante para o negócio.Alteração de Requisitos:
No dia 21/01/2021 a gerente de MKT, Maria de xxxx, solicitou ao analista Ricardo Fagundes a inclusão/alteração de requisitos descrita abaixo:
Coluna com nome único.
Sexo – Masculino e Feminino.
Produtos em padrão Camel Case.
_________________________
Assinatura do Responsável.
Fase 02 – Construção da Stage Area
Para desenvolvemos um projeto de BI e a construção do Data Warehouse o ideal é termos os dados “limpos” em um repositório, uma área onde podemos realizar a extração,
transformação e limpeza dos dados antes de enviar os mesmos para o Data Warehouse simplesmente chamamos este repositório de Stage Área , podem haver informações de diversas origens diferentes e de diversas plataformas , a necessidade de utilização de uma Stage Area para integrar todos tipos de dados em um único formato em uma Stage Area.
Uma Stage Area garante a existência única de dados, eliminando a possibilidade de termos dados repetidos ou similares ou até mesmo com valores diferentes em um Data Warehouse, dados não sincronizados e dados “sujos”. Também garantindo a performance da base de dados OLTP(ERP's por exemplo) e da OLAP(Data Warehouse)
Modelagem Lógica
Modelagem Física
/* CRIAÇÃO DA ÁREA DE STAGE */
CREATE DATABASE COMERCIO_STAGE GO
USE COMERCIO_STAGE GO
CREATE TABLE ST_CLIENTE(
IDCLIENTE INT DEFAULT NULL, NOME VARCHAR(100) DEFAULT NULL, SEXO VARCHAR(20) DEFAULT NULL, NASCIMENTO DATE DEFAULT NULL, EMAIL VARCHAR(60) DEFAULT NULL, CIDADE VARCHAR(100) DEFAULT NULL, ESTADO VARCHAR(20) DEFAULT NULL, REGIAO VARCHAR(20) DEFAULT NULL )
GO
CREATE TABLE ST_VENDEDOR(
IDVENDEDOR INT DEFAULT NULL, NOME VARCHAR(50) DEFAULT NULL, SEXO VARCHAR(20) DEFAULT NULL, IDGERENTE INT DEFAULT NULL )
GO
CREATE TABLE ST_CATEGORIA(
IDCATEGORIA INT DEFAULT NULL, NOME VARCHAR(50) DEFAULT NULL )
GO
CREATE TABLE ST_FORNECEDOR(
IDFORNECEDOR INT DEFAULT NULL, NOME VARCHAR(100) DEFAULT NULL )
GO
CREATE TABLE ST_PRODUTO(
IDPRODUTO INT DEFAULT NULL,
NOME VARCHAR(100) DEFAULT NULL,
VALOR_UNITARIO NUMERIC(10,2) DEFAULT NULL, CUSTO_MEDIO NUMERIC(10,2) DEFAULT NULL, ID_CATEGORIA INT DEFAULT NULL
) GO
CREATE TABLE ST_NOTA(
IDNOTA INT DEFAULT NULL )
GO
CREATE TABLE ST_FORMA(
IDFORMA INT DEFAULT NULL, FORMA VARCHAR(30) DEFAULT NULL )
GO
CREATE TABLE ST_FATO(
IDCLIENTE INT DEFAULT NULL, IDVENDEDOR INT DEFAULT NULL, IDPRODUTO INT DEFAULT NULL, IDFORNECEDOR INT DEFAULT NULL, IDNOTA INT DEFAULT NULL, IDFORMA INT DEFAULT NULL, QUANTIDADE INT DEFAULT NULL,
TOTAL_ITEM NUMERIC(10,2) DEFAULT NULL, DATA DATE DEFAULT NULL,
CUSTO_TOTAL NUMERIC(10,2) DEFAULT NULL, LUCRO_TOTAL NUMERIC(10,2) DEFAULT NULL )
GO
Carga da Base Stage
A carga da base Stage será realizado apartir do Integration Services (SSIS).
Há um processo de ETL a ser realizado, pois há requisitos a serem implementados e temos que fazer a Importação dos dados da base OLTP realizando uma limpeza dos mesmos, tudo de acordo com os Requisitos;
Um exemplo que mostrarei aqui é a Carga da Tabela ST_CLIENTE.
Criei o Projeto no Integration Service, criei um pacote;
No Pacote CARGA_ST_CLIENTE, criei uma tarefa para Executar um SQL com um Truncate Table Conectando na base COMERCIO_STAGE, o Truncate para sempre ter dados novos na base Stage.
Após Conectei com a Tarefa de Fluxo de Dados CARGA ST_CLIENTE para iniciar o processo de carga dos dados
O Primeiro passo, na carga no processo de ETL configurar a Origem, o Data Source Origem CLIENTE, e um dos requisitos é ter o nome completo do cliente em um campo só, só que na tabela cliente está separado em Nome e Sobrenome.
Para solucionar irei utilizar o componente Coluna Derivada (Derived Column) e Criar o campo NOME COMPLETO.
Também a o requisito Sexo que foi solicitado que ao invés de M e F fosse alterado para Masculino e Feminino, utilizarei o mesmo componente, O campo será SEXO PALAVRA
Outro requisito é o endereço do cliente que precisamos fazer a junção pois há a necessidade do endereço completo o que está separado em vários campos da Tabela Endereço.
Para solucionar usarei o componente Pesquisa (Lookup)
A conexão com o Destino fica para o Base Stage, com a tabela ST_CLIENTE
O Mapeamento é realizado quase de forma automática, tendo apenas que alterar os campos SEXO para STRING SEXO PALAVRA e NOME para STRING NOME COMPLETO.
O Fluxo de carga fica da seguinte forma
Vamos executar o pacote
Podemos ver a tabela está zerada
Carga realizada com Sucesso
E os dados estão corretamente inseridos na tabela
Assim segue por todas as tabelas, se for uma carga diária/semanal cria-se um job no Analys Services com agendamento conforme necessidade;
Carga da ST_FATO
Será carrega apartir de uma View, nesta View irei trazer todas as informações para a Fato, cada linha será uma venda, terei informação do vendedor, do produto vendido, quantidade vendida, custo total, e o lucro total, tendo os ID’s e as Medidas eu levo para a tabela de ST_FATO
USE COMERCIO_OLTP GO
DROP VIEW RELATORIO_VENDAS GO
CREATE VIEW RELATORIO_VENDAS AS SELECT C.IDCLIENTE AS IDCLIENTE,
V.IDVENDEDOR AS IDVENDEDOR, P.IDPRODUTO AS IDPRODUTO,
FO.IDFORNECEDOR AS IDFORNECEDOR, N.IDNOTA AS IDNOTA,
IDFORMA AS IDFORMA,
I.QUANTIDADE AS QUANTIDADE,
(I.QUANTIDADE * P.CUSTO_MEDIO) AS CUSTO_TOTAL,
(I.TOTAL - (I.QUANTIDADE * P.CUSTO_MEDIO)) AS LUCRO_TOTAL, I.TOTAL AS TOTAL_ITEM,
N.DATA AS DATA FROM NOTA_FISCAL N
INNER JOIN ITEM_NOTA I ON ( N.IDNOTA = I.ID_NOTA_FISCAL ) INNER JOIN CLIENTE C ON ( C.IDCLIENTE = N.ID_CLIENTE ) INNER JOIN VENDEDOR V ON ( V.IDVENDEDOR = N.ID_VENDEDOR ) INNER JOIN PRODUTO P ON ( P.IDPRODUTO = ID_PRODUTO ) INNER JOIN FORMA_PAGAMENTO F ON ( F.IDFORMA = N.ID_FORMA ) INNER JOIN FORNECEDOR FO ON (FO.IDFORNECEDOR = P.ID_FORNECEDOR) GO
Na origem da carga no pacote de carga do Integration Service seleciono a View RELATORIO_VENDAS que acabei de criar acima
Os campos são automaticamente mapeados pois tem os mesmos nomes
É executado com sucesso
E a tabela com todos os dados
Fase 03 – Construção do Data Warehouse
Na entrevista com os gestores podemos já definir qual o modelo de Data Warehouse que iremos contruir, o gestor quer saber as analises sumarizadas, ele não quer saber quantas vendas tem um vendedor e sim o Total vendido(isto já é uma análise dos dados para o gestor), não importa se um cliente foi na loja e comprou 1 item com um vendedor e outro cliente foi na loja e comprou 10 itens, pode ocorrer que o primeiro cliente comprou um produto de 20 mil e o segundo cliente comprou 10 produtos de 10 reais por exemplo, no final 1 produto vendido pode ter uma melhor venda do que 10 produtos vendidos.
Modelagem Logica DW
Assim montando o modelo lógico do data warehouse temos que pensar que deve ser desenhado para transpor os limites de cada um dos sistemas transacionais.
Ele é construído para responder questões que não estão limitadas às transações ou aos sistemas individuais, apresentando, desta forma, uma visão integrada e completa dos negócios.
Uma das técnicas utilizadas para se obter um modelo para o data warehouse que identifique e represente as informações importantes para o modelo de negócios é a modelagem
dimensional ou multidimensional.
Quando bem definido, o modelo dimensional pode ser uma ajuda de valor incalculável para as áreas de negócio, apoiando e otimizando todo o processo de tomada de decisões.
O modelo dimensional representa:
Os indicadores importantes para uma área de negócios, que são chamados de fatos ou métricas;
Os parâmetros através dos quais estas métricas são analisadas pelos usuários, que são chamados de dimensões (as dimensões de negócios).
A Figura 1 abaixo apresenta um modelo dimensional.
As métricas ou fato definidas neste modelo é a tabela central e as dimensões estão representadas nas tabelas ao redor das métricas/fato.
As métricas são sumariadas (agregadas) ou detalhadas de acordo com o interesse da análise a ser feita sobre os dados.
Este modelo é fácil de ser entendido por uma pessoa da área de negócios, já que “as coisas que eu avalio” estão na parte central do diagrama e “as formas de se olhar para elas” estão nas tabelas em volta.
É um DW snowflake.
Modelagem Fisica do DW
CREATE DATABASE COMERCIO_DW GO
USE COMERCIO_DW GO
CREATE TABLE DIM_VENDEDOR(
IDSK INT PRIMARY KEY IDENTITY, IDVENDEDOR INT,
INICIO DATETIME,
FIM DATETIME, NOME VARCHAR(50), SEXO VARCHAR(20), IDGERENTE INT )
GO
CREATE TABLE DIM_NOTA(
IDSK INT PRIMARY KEY IDENTITY, IDNOTA INT
) GO
CREATE TABLE DIM_FORMA(
IDSK INT PRIMARY KEY IDENTITY, IDFORMA INT,
FORMA VARCHAR(30) )
GO
CREATE TABLE DIM_CLIENTE(
IDSK INT PRIMARY KEY IDENTITY, IDCLIENTE INT,
INICIO DATETIME, FIM DATETIME, NOME VARCHAR(100), SEXO VARCHAR(20), NASCIMENTO DATE, CIDADE VARCHAR(100), ESTADO VARCHAR(10), REGIAO VARCHAR(20) )
GO
CREATE TABLE CATEGORIA(
IDCATEGORIA INT PRIMARY KEY, NOME VARCHAR(50)
) GO
CREATE TABLE DIM_PRODUTO(
IDSK INT PRIMARY KEY IDENTITY, IDPRODUTO INT,
INICIO DATETIME, FIM DATETIME, NOME VARCHAR(50),
VALOR_UNITARIO NUMERIC(10,2) DEFAULT NULL, CUSTO_MEDIO NUMERIC(10,2) DEFAULT NULL, ID_CATEGORIA INT,
FOREIGN KEY(ID_CATEGORIA) REFERENCES CATEGORIA(IDCATEGORIA)
) GO
CREATE TABLE DIM_FORNECEDOR(
IDSK INT PRIMARY KEY IDENTITY, IDFORNECEDOR INT,
INICIO DATETIME, FIM DATETIME, NOME VARCHAR(30) )
GO
CREATE TABLE DIM_TEMPO(
IDSK INT PRIMARY KEY IDENTITY, DATA DATE,
DIA CHAR(2),
DIASEMANA VARCHAR(10), MES CHAR(2),
NOMEMES VARCHAR(10),
QUARTO TINYINT,
NOMEQUARTO VARCHAR(10), ANO CHAR(4),
ESTACAOANO VARCHAR(20), FIMSEMANA CHAR(3), DATACOMPLETA VARCHAR(10) )
GO
CREATE TABLE FATO(
IDNOTA INT REFERENCES DIM_NOTA(IDSK), IDCLIENTE INT REFERENCES DIM_CLIENTE(IDSK), IDVENDEDOR INT REFERENCES DIM_VENDEDOR(IDSK), IDFORMA INT REFERENCES DIM_FORMA(IDSK), IDPRODUTO INT REFERENCES DIM_PRODUTO(IDSK), IDFORNECEDOR INT REFERENCES DIM_FORNECEDOR(IDSK), IDTEMPO INT REFERENCES DIM_TEMPO(IDSK),
QUANTIDADE INT,
TOTAL_ITEM NUMERIC(10,2), CUSTO_TOTAL NUMERIC(10,2), LUCRO_TOTAL NUMERIC(10,2) )
GO
Obs: Como pode ser visto o DW está sendo criado com Surrogate Keys(IDSK) que são as chaves subistitutas e Slowly Changing Dimension (INICIO DATETIME,FIM DATETIME) que nos auxilia na manutenção de registros mantengo o historico de alteração dos mesmos .
Carga do DW
As tranformações realizadas nos dados estão todas na área de Stage, ou seja em momento algum a Base OLTP Será prejudicada em performance.
Foi realizada modelagem de dimensões ou sejam pelo que agente divide os fatos, pelo que agente quer ver o negocio, quero ver por cliente, quero ver por fornecedor, quero ver por categoria, por produto essas são dimensões e foi modelado a tabela Fato que está prontinha
na Stage para ser carregada para o DW com Qte, Lucro, Valor Produto, tudo que são números, tudo que agente pode dividir por dimensões são fato além dos ID’S das dimensões .
Mapa Geral do Projeto
Inicio da Carga
A primeira carga que vou iniciar é a da categoria, por ser um DW snowflake a categoria não vai carregar com Slowly Changing Dimension
A carga Inicio separando o carregamento do pacote categoria em Conteiners Um Conteiner para carga da Stage e um Conteiner para carga do DW
Para realizar a carga para o DW, para irei utilizar o carregamento com tecnica se interseção ou seja vou carregar e inserir na tabela categoria somente o que eu preciso, somente os dados que não existem no DW.
Para isso ao selecionar a tabela do stage faço a seguinte query.
Para me mostrar somente quem vem da stage e não esteja no DW.
SELECT * FROM COMERCIO_STAGE.dbo.ST_CATEGORIA WHERE IDCATEGORIA NOT IN
( SELECT IDCATEGORIA FROM COMERCIO_DW.dbo.CATEGORIA) O Data Flow(Fluxo de Dados fica da seguinte forma
Executando o pacote realiza a carga normalmente e com sucesso
A tabela categoria do DW foi carregada com sucesso.
Na carga do Fornecedor irei utilizar o SCD(Slowly Changing Dimension), para realizar esta carga tem um componente para isso. Como vou utilizar SCD não tem porque carregar pela diferença.
A pós programação do componente é criado um fluxo enorme.
O Pacote foi executado com sucesso.
A tabela DIM_FORNECEDOR foi preenchida com sucesso com todos os dados da Stage.
E A SCD Inicio preenchida com a data do dia da carga.
Assim o que ocorre se um registro destes fornecedores forem atualizados no ambiente OLTP é fechado a vigencia dele com data de FIM do dia e criado um novo registro com a data de Inicio preenchida com a data da alteração e sem a FIM que será o registro válido/atual
Todas as dimensões carregam da mesma forma, a não ser as que utiliza a tecnica de interseção.
Agora vamos para o carregamento da Fato no DW.
A carga da Fato irei realizar através de uma procedure , nesta procedure vou verificar a data máxima já carregada no DW fazendo um join com a dimensão tempo, a primeira vez que faço a carga a FATO está nula e também verifico os IDSK .
CREATE PROC CARGA_FATO AS
DECLARE @FINAL DATETIME
DECLARE @INICIAL DATETIME SELECT @FINAL = MAX(DATA) FROM COMERCIO_DW.DBO.DIM_TEMPO T SELECT @INICIAL = MAX(DATA)
FROM COMERCIO_DW.DBO.FATO FT
JOIN COMERCIO_DW.DBO.DIM_TEMPO T ON (FT.IDTEMPO=T.IDSK) IF @INICIAL IS NULL
BEGIN
SELECT @INICIAL = MIN(DATA) FROM COMERCIO_DW.DBO.DIM_TEMPO T END
INSERT INTO COMERCIO_DW.DBO.FATO(
IDNOTA , IDCLIENTE , IDVENDEDOR , IDFORMA , IDFORNECEDOR, IDPRODUTO, IDTEMPO, QUANTIDADE, TOTAL_ITEM, CUSTO_TOTAL, LUCRO_TOTAL ) SELECT
N.IDSK AS IDNOTA, C.IDSK AS IDCLIENTE, V.IDSK AS IDVENDEDOR, FO.IDSK AS IDFORMA, FN.IDSK AS IDFORNECEDOR,
P.IDSK AS IDPRODUTO, T.IDSK as IDTEMPO, F.QUANTIDADE, F.TOTAL_ITEM, F.CUSTO_TOTAL, F.LUCRO_TOTAL FROM
COMERCIO_STAGE.DBO.ST_FATO F INNER JOIN DBO.DIM_FORMA FO on (F.IDFORMA=FO.IDFORMA) INNER JOIN DBO.DIM_NOTA N on (F.IDNOTA=N.IDNOTA)
INNER JOIN DBO.DIM_FORNECEDOR FN on (F.IDFORNECEDOR=FN.IDFORNECEDOR
AND (FN.INICIO <= F.DATA AND (FN.FIM >= F.DATA) or (FN.FIM IS NULL)))
INNER JOIN DBO.DIM_CLIENTE C on (F.IDCLIENTE=C.IDCLIENTE
AND (C.INICIO <= F.DATA
AND (C.FIM >= F.DATA) or (C.FIM IS NULL))) INNER JOIN DBO.DIM_VENDEDOR V
on (F.IDVENDEDOR=V.IDVENDEDOR
AND (V.INICIO <= F.DATA
AND (V.FIM >= F.DATA) or (V.FIM IS NULL))) INNER JOIN DBO.DIM_PRODUTO P
ON (F.IDPRODUTO=P.IDPRODUTO AND (P.INICIO <= F.DATA
AND (P.FIM >= F.DATA) OR (P.FIM IS NULL))) INNER JOIN DBO.DIM_TEMPO T
ON (CONVERT(VARCHAR, T.DATA,102) = CONVERT(VARCHAR, F.DATA,102))
--WHERE F.DATA > @INICIAL AND F.DATA < @FINAL WHERE F.DATA BETWEEN @INICIAL AND @FINAL GO
Pacote executado com sucesso
Tabela Fato no DW carregada com sucesso e finalizamos o processo de ETL.
Fase 04 – Analise dos Dados
Irei fazer uma nálise inicial aqui com um gráficos trazendo a Região, o Ano e irei agregar a Quantidade, o Custo, o Lucro e o Total.
Farei um Dashboar no PowerBI com os dados vindos da tabela fato e suas dimensões.
Muitos outros análises podem ser realisados este é apenas um demonstrando o Custo, o Lucro por ano e por região para auxiliar os gestores na tomada de decisão.
O dashboard é totalmente interativo, a matriz é em Drill down como pode ser visto
