Este repositório contém um projeto utilizando o SQL Server, e o Visual Studio 2015 (Business intelligence, Reporting Services, Integration Services, Analysis Services). Esse é um projeto de estudo. Os scripts construidos foram feitos no Microsoft SQL Server Management Studio. As modelagens foram feitas no StarUML. E os processos de ETL foram feitos no Visual Studio 2015.
A loja musical necessita armazenar o seus funcionários, metodos de pagamento, o cadastro de clientes, produtos, fornecedores, marcas, categorias, subcategorias, os protudos das notas fiscais, e as notas fiscais. Também querem guardar seus dados de vendas, custos e lucros. Não importando saber quem vendeu ou quantas vendas cada funcionário fez. Eles desejam saber em que época do ano tem mais vendas e também em quais meses. Gostariam de ter uma análise de vendas por categoria, subcategoria e marcas.
O objetivo deste projeto é demonstrar a criação de um banco de dados relacional de uma loja musical. Fazendo a criação do Ambiente OLTP, Staging Area, Datawarehouse, Ambiente OLAP, CUBO(Analysis Services), Reporting Services para atender todas as necessidades da loja.
Nesta primeira etapa, será criado o database, e a estrutura das tabelas com base na modelagem.
CREATE DATABASE LOJA_INSTRUMENTOS_OLTP
GO
UTILIZANDO O DATABASE:
USE LOJA_INSTRUMENTOS_OLTP
GO
CRIAÇÃO DA TABELA PARA O ARMAZENAMENTO DOS PRODUTOS:
CREATE TABLE PRODUTOS(
IDPRODUTO INT IDENTITY,
PRODUTO VARCHAR(70) NOT NULL,
VALOR NUMERIC(10,2) NOT NULL,
CUSTO_MEDIO NUMERIC(10,2) NOT NULL,
ID_CATEGORIA INT NOT NULL,
ID_MARCA INT NOT NULL,
ID_FORNECEDOR INT NOT NULL,
ID_SUBCATEGORIAS INT NOT NULL,
CONSTRAINT PK_PRODUTO PRIMARY KEY(IDPRODUTO)
)
GO
CRIAÇÃO DA TABELA MARCAS:
CREATE TABLE MARCAS(
IDMARCA INT IDENTITY,
MARCA VARCHAR(25) NOT NULL,
CONSTRAINT PK_MARCA PRIMARY KEY(IDMARCA)
)
GO
CRIAÇÃO DA TABELA CATEGORIAS:
CREATE TABLE CATEGORIAS(
IDCATEGORIA INT IDENTITY,
CATEGORIA VARCHAR(25) NOT NULL,
CONSTRAINT PK_CATEGORIA PRIMARY KEY(IDCATEGORIA)
)
GO
CRIAÇÃO DA TABELA SUBCATEGORIAS:
CREATE TABLE SUB_CATEGORIAS(
IDSUB INT IDENTITY,
SUB_CATEGORIA VARCHAR(30) NOT NULL,
ID_CATEGORIA INT NOT NULL,
CONSTRAINT PK_SUBCTG PRIMARY KEY(IDSUB)
)
GO
CRIAÇÃO DA TABELA FORNECEDORES:
CREATE TABLE FORNECEDORES(
IDFORNECEDOR INT IDENTITY,
FORNECEDOR VARCHAR(25) NOT NULL,
CONSTRAINT PK_FORNECEDOR PRIMARY KEY(IDFORNECEDOR)
)
GO
CRIAÇÃO DA TABELA CLIENTES:
CREATE TABLE CLIENTES(
IDCLIENTE INT IDENTITY,
NOME VARCHAR(20) NOT NULL,
SOBRENOME VARCHAR(35) NOT NULL,
SEXO CHAR(1) CONSTRAINT CK_SEXO CHECK (SEXO IN ('M', 'F')) NOT NULL,
NASCIMENTO DATE NOT NULL,
EMAIL VARCHAR(50) NOT NULL,
RUA VARCHAR(30) NOT NULL,
CIDADE VARCHAR(20) NOT NULL,
UF CHAR(2) NOT NULL,
CONSTRAINT PK_CLIENTE PRIMARY KEY(IDCLIENTE)
)
GO
CRIAÇÃO DA TABELA FUNCIONARIOS:
CREATE TABLE FUNCIONARIOS(
IDFUNCIONARIO INT IDENTITY,
FUNCIONARIO VARCHAR(45) NOT NULL,
SEXO CHAR(1) CONSTRAINT CK_SEXO_FUN CHECK (SEXO IN ('M', 'F')) NOT NULL,
NASCIMENTO DATE NOT NULL,
EMAIL VARCHAR(40) NOT NULL,
RUA VARCHAR(30) NOT NULL,
CIDADE VARCHAR(20) NOT NULL,
UF CHAR(2) NOT NULL,
CARGO VARCHAR(30) NOT NULL,
SALARIO NUMERIC(10,2) NOT NULL,
CONSTRAINT PK_FUNCIONARIO PRIMARY KEY(IDFUNCIONARIO)
)
GO
CRIAÇÃO DA TABELA METODO DE PAGAMENTO:
CREATE TABLE METODO_PAGAMENTO(
IDMETODO INT IDENTITY,
FORMA_DE_PAGAMENTO VARCHAR(35) NOT NULL,
CONSTRAINT PK_METODO PRIMARY KEY(IDMETODO)
)
GO
CRIAÇÃO DA TABELA NOTA FISCAL:
CREATE TABLE NOTA_FISCAL(
IDNOTAFISCAL INT IDENTITY,
DATA DATE DEFAULT GETDATE() NOT NULL,
TOTAL DECIMAL(10,2),
ID_FORMA INT NOT NULL,
ID_CLIENTE INT NOT NULL,
ID_FUNCIONARIO INT NOT NULL,
CONSTRAINT PK_NOTA PRIMARY KEY(IDNOTAFISCAL)
)
GO
CRIAÇÃO DA TABELA ITENS DA NOTA FISCAL:
CREATE TABLE ITEM_NOTA(
IDITEMNOTA INT IDENTITY,
QUANTIDADE INT,
VALOR NUMERIC(10,2),
ID_PRODUTO INT NOT NULL,
ID_NOTA_FISCAL INT NOT NULL,
CONSTRAINT PK_ITEM PRIMARY KEY(IDITEMNOTA)
)
GO
Nesta etapa iremos criar as Foreign Key(FK) para relacionar as tabelas. Elas foram criadas fora do script de criação de tabelas para termos um dicionário de dados.
CRIANDO OS RELACIONAMENTOS ENTRE TABELAS, FOREIGNS KEYS REFERENTES AS TABELAS:
PRODUTOS E CATEGORIAS:
ALTER TABLE PRODUTOS ADD CONSTRAINT FK_PROD_CATG
FOREIGN KEY(ID_CATEGORIA) REFERENCES CATEGORIAS(IDCATEGORIA)
GO
PRODUTOS E MARCAS:
ALTER TABLE PRODUTOS ADD CONSTRAINT FK_PROD_MARCA
FOREIGN KEY(ID_MARCA) REFERENCES MARCAS(IDMARCA)
GO
PRODUTOS E FORNECEDORES:
ALTER TABLE PRODUTOS ADD CONSTRAINT FK_PROD_FORNCEDORES
FOREIGN KEY(ID_FORNECEDOR) REFERENCES FORNECEDORES(IDFORNECEDOR)
GO
NOTA FISCAL E FORMA DE PAGAMENTO:
ALTER TABLE NOTA_FISCAL ADD CONSTRAINT FK_NOTA_FORMA
FOREIGN KEY(ID_FORMA) REFERENCES METODO_PAGAMENTO(IDMETODO)
GO
NOTA FISCAL E CLIENTES:
ALTER TABLE NOTA_FISCAL ADD CONSTRAINT FK_NOTAS_CLIENTE
FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTES(IDCLIENTE)
GO
ITENS DE NOTA E PRODUTOS:
ALTER TABLE ITEM_NOTA ADD CONSTRAINT FK_ITEM_PROD
FOREIGN KEY(ID_PRODUTO) REFERENCES PRODUTOS(IDPRODUTO)
GO
ITENS DE NOTA E NOTA FISCAL:
ALTER TABLE ITEM_NOTA ADD CONSTRAINT FK_ITEM_NOTAFISCAL
FOREIGN KEY(ID_NOTA_FISCAL) REFERENCES NOTA_FISCAL(IDNOTAFISCAL)
GO
PRODUTOS E SUBCATEGORIAS:
ALTER TABLE PRODUTOS ADD CONSTRAINT FK_PRODUTO_SUBCATE
FOREIGN KEY(ID_SUBCATEGORIAS) REFERENCES SUB_CATEGORIAS(IDSUB)
GO
SUBCATEGORIAS E CATEGORIAS:
ALTER TABLE SUB_CATEGORIAS ADD CONSTRAINT FK_SUBCATEG_CATEGO
FOREIGN KEY(ID_CATEGORIA) REFERENCES CATEGORIAS(IDCATEGORIA)
GO
NOTA FISCAL E FUNCIONARIO:
ALTER TABLE NOTA_FISCAL ADD CONSTRAINT FK_NOTA_FUNC
FOREIGN KEY(ID_FUNCIONARIO) REFERENCES FUNCIONARIOS(IDFUNCIONARIO)
GO
Nesta etapa iremos adicionar os dados de todas as tabelas. OBS: Os dados completos de todas as tabelas, estão na pasta scripts.
ADICIONANDO DADOS NA TABELA CATEGORIAS:
INSERT INTO CATEGORIAS VALUES('Acessórios')
INSERT INTO CATEGORIAS VALUES('Igreja')
INSERT INTO CATEGORIAS VALUES('Sopro')
INSERT INTO CATEGORIAS VALUES('Teclas')
INSERT INTO CATEGORIAS VALUES('Cordas')
INSERT INTO CATEGORIAS VALUES('Livros')
INSERT INTO CATEGORIAS VALUES('Outlet')
INSERT INTO CATEGORIAS VALUES('Percussão')
INSERT INTO CATEGORIAS VALUES('Áudio')
GO
ADICIONANDO DADOS NA SUBCATEGORIAS:
-- Audio = 9
INSERT INTO SUB_CATEGORIAS VALUES('Afinador/Metronomo', 9)
INSERT INTO SUB_CATEGORIAS VALUES('Cabos e Adptadores', 9)
-- Percurssão = 8
INSERT INTO SUB_CATEGORIAS VALUES('Pandeiros', 8)
INSERT INTO SUB_CATEGORIAS VALUES('Baqueta', 8)
-- Outlet = 7
INSERT INTO SUB_CATEGORIAS VALUES('Estojo', 7)
INSERT INTO SUB_CATEGORIAS VALUES('Bíblias', 7)
-- Livros = 6
INSERT INTO SUB_CATEGORIAS VALUES('Métodos Cordas', 6)
INSERT INTO SUB_CATEGORIAS VALUES('Métodos Diversos', 6)
-- Cordas = 5
INSERT INTO SUB_CATEGORIAS VALUES('Cavaquinhos', 5)
INSERT INTO SUB_CATEGORIAS VALUES('Viola de Arco', 5)
-- Teclas = 4
INSERT INTO SUB_CATEGORIAS VALUES('Escaleta', 4)
INSERT INTO SUB_CATEGORIAS VALUES('Pianos', 4)
-- Sopro = 3
INSERT INTO SUB_CATEGORIAS VALUES('Gaita', 3)
INSERT INTO SUB_CATEGORIAS VALUES('Bombardinos', 3)
INSERT INTO SUB_CATEGORIAS VALUES('Clarinetes', 3)
-- Igreja = 2
INSERT INTO SUB_CATEGORIAS VALUES('Acessórios', 2)
INSERT INTO SUB_CATEGORIAS VALUES('Caixa de Coleta', 2)
-- Acessorios = 1
INSERT INTO SUB_CATEGORIAS VALUES('Abraçadeiras', 1)
INSERT INTO SUB_CATEGORIAS VALUES('Arcos', 1)
GO
ADICIONANDO MARCAS:
INSERT INTO MARCAS VALUES('CSR')
INSERT INTO MARCAS VALUES('Dolphin')
INSERT INTO MARCAS VALUES('Free Sax')
INSERT INTO MARCAS VALUES('Saty')
INSERT INTO MARCAS VALUES('JBL')
INSERT INTO MARCAS VALUES('Sony')
INSERT INTO MARCAS VALUES('Philips')
INSERT INTO MARCAS VALUES('Paganini')
GO
ADICIONANDO METODO DE PAGAMENTOS:
INSERT INTO METODO_PAGAMENTO VALUES('Tranferência - Vista')
INSERT INTO METODO_PAGAMENTO VALUES('Depósito - Vista')
INSERT INTO METODO_PAGAMENTO VALUES('Boleto - Vista')
INSERT INTO METODO_PAGAMENTO VALUES('PicPay - Vista')
INSERT INTO METODO_PAGAMENTO VALUES('Mercado Pago - Vista')
INSERT INTO METODO_PAGAMENTO VALUES('Cartão Master 2 vezes')
INSERT INTO METODO_PAGAMENTO VALUES('Cartão Visa 2 vezes')
INSERT INTO METODO_PAGAMENTO VALUES('Cartão Visa 3 vezes')
INSERT INTO METODO_PAGAMENTO VALUES('Cartão American 5 vezes')
INSERT INTO METODO_PAGAMENTO VALUES('Cartão Visa 2 vezes')
INSERT INTO METODO_PAGAMENTO VALUES('Pay Pall - 5 vezes')
INSERT INTO METODO_PAGAMENTO VALUES('Pag Seguro Web - Vista')
INSERT INTO METODO_PAGAMENTO VALUES('Cheque - Vista')
INSERT INTO METODO_PAGAMENTO VALUES('Pic Pay - Vista')
INSERT INTO METODO_PAGAMENTO VALUES('Mercado Pago - Vista')
GO
ADICIONANDO FORNECEDORES:
INSERT INTO FORNECEDORES VALUES('Alibaba')
INSERT INTO FORNECEDORES VALUES('Oderço')
INSERT INTO FORNECEDORES VALUES('ZadSom')
INSERT INTO FORNECEDORES VALUES('Guimarães Comercial')
INSERT INTO FORNECEDORES VALUES('Izzo Instrumentos')
INSERT INTO FORNECEDORES VALUES('Hayamax')
INSERT INTO FORNECEDORES VALUES('Musitech')
INSERT INTO FORNECEDORES VALUES('Kyodday Comércio')
GO
ADICIONANDO PRODUTOS:
INSERT INTO PRODUTOS VALUES('Queixeira Guarnieri Violino 3/4 4/4 em Ébano Hill', 113.68,80.50,1,24,3,53)
INSERT INTO PRODUTOS VALUES('Queixeira Violino 4/4 em Ébano', 120.00, 85.00, 1, 23, 2, 53)
INSERT INTO PRODUTOS VALUES('Queixeira Violino 4/4 - Madeira', 105.00, 75.00, 1, 8, 1, 53)
INSERT INTO PRODUTOS VALUES('Suporte Estante Dobrável Compacta para Partitura',339.00,280.00,1,30,7,52)
INSERT INTO PRODUTOS VALUES('Estante para Piano Digital YAMAHA',869.00,680.00,1,10,3,52)
INSERT INTO PRODUTOS VALUES('Polidor Instrumentos Niquelados',90.00,40.00,1,10,3,51)
INSERT INTO PRODUTOS VALUES('Surdina para Trombone',230.00,175.00,1,1,2,50)
INSERT INTO PRODUTOS VALUES('Surdina Tour te Round',12.87,4.13,1,43,4,50)
INSERT INTO PRODUTOS VALUES('Surdina de Metal Violino',112.20,68.70,1,8,1,50)
INSERT INTO PRODUTOS VALUES('Prendedor de Partitura Clave de Sol',18.00,7.00,1,8,8,49)
INSERT INTO PRODUTOS VALUES('Palheta para violão Fina',13.20,5.20,1,8,8,48)
INSERT INTO PRODUTOS VALUES('Palhetapara Sax Alto',35.00,20.00,1,45,2,48)
INSERT INTO PRODUTOS VALUES('Plaheta para Sax ALto',23.00,9.00,1,26,1,48)
INSERT INTO PRODUTOS VALUES('Bocal para trompa prateado nºW15 - VFH',78.00,32.00,1,17,5,47)
INSERT INTO PRODUTOS VALUES('Bocal para Bombardino 6 1/2 AL',80.00,46.00,1,2,4,47)
INSERT INTO PRODUTOS VALUES('Breu para Violino/Viola - Preto',15.00,6.00,1,8,3,46)
INSERT INTO PRODUTOS VALUES('Breu para Violino e Viola Black',40.00,27.00,1,24,7,46)
INSERT INTO PRODUTOS VALUES('Capa Preta para Violão ou Violão 12 Cordas',452.00,388.00,1,21,6,45)
INSERT INTO PRODUTOS VALUES('Capa Capota Preta para Sax Alto',252.96,198.04,1,21,6,45)
CRIANDO E ADICIONANDO DADOS DE FORMA ALEATORIA NAS NOTAS FISCAIS, COMO SE FOSSEM COMPRAS FEITAS:
/*
ADICIONANDO DADOS NAS NOTAS FISCAIS
1- CLIENTE ALEATORIO;
2- FUNCIONARIO ALEATORIO;
3- FORMA DE PAGAMENTO/METODO DE PAGAMENTO ALEATORIA
4- ANO/MES/DIA ALEATORIO
OBS: RODAREI CADA ANO 5000 VEZES (2019,2020,2021)
PARA CRIAR 15.000
*/
DECLARE
@ID_CLIENTE INT, @ID_FUNCIONARIO INT, @ID_FORMA INT,
@DATA DATE
BEGIN
SET @ID_CLIENTE =
(SELECT TOP 1 IDCLIENTE FROM CLIENTES ORDER BY NEWID())
SET @ID_FUNCIONARIO =
(SELECT TOP 1 IDFUNCIONARIO FROM FUNCIONARIOS ORDER BY NEWID())
SET @ID_FORMA =
(SELECT TOP 1 IDMETODO FROM METODO_PAGAMENTO ORDER BY NEWID())
/* CRIANDO UMA DATA ALEATORIA*/
/* CADA VEZ QUE RODAR O COMANDO, ALTERE O 2019 POR 2020 E DEPOIS PARA 2021 */
SET @DATA = (SELECT
CONVERT(DATE, CONVERT(VARCHAR(15),'2019-' +
CONVERT(VARCHAR(5),(CONVERT(INT,RAND()*12)) + 1) + '-' +
CONVERT(VARCHAR(5),(CONVERT(INT,RAND()*27)) + 1))))
INSERT INTO NOTA_FISCAL(ID_CLIENTE,ID_FUNCIONARIO,ID_FORMA, DATA)
VALUES (@ID_CLIENTE,@ID_FUNCIONARIO,@ID_FORMA,@DATA)
END
GO 5000 --VAI EXECUTAR 5000 VEZES
ADICIONANDO ITENS NA NOTA FISCAL:
-- OBS: Execute esse comando de acordo com a nota fiscal
-- SELECT COUNT(*) FROM NOTA_FISCAL
DECLARE
@ID_PRODUTO INT,
@ID_NOTA_FISCAL INT,
@QUANTIDADE INT,
@VALOR NUMERIC(10,2),
@VALOR_TOTAL NUMERIC(10,2)
BEGIN
SET @ID_PRODUTO =
(SELECT TOP 1 IDPRODUTO FROM PRODUTOS ORDER BY NEWID())
SET @ID_NOTA_FISCAL =
(SELECT TOP 1 IDNOTAFISCAL FROM NOTA_FISCAL ORDER BY NEWID())
SET @QUANTIDADE =
(SELECT ROUND(RAND() * 1 + 1, 0))
SET @VALOR =
(SELECT VALOR FROM PRODUTOS WHERE IDPRODUTO = @ID_PRODUTO)
SET @VALOR_TOTAL = @QUANTIDADE * @VALOR
INSERT INTO ITEM_NOTA(ID_PRODUTO,ID_NOTA_FISCAL,QUANTIDADE,VALOR)
VALUES (@ID_PRODUTO,@ID_NOTA_FISCAL,@QUANTIDADE,@VALOR_TOTAL)
END
GO 15000
VERIFICANDO AS NOTAS QUE NÃO FORAM PREENCHIDAS:
SELECT IDNOTAFISCAL FROM NOTA_FISCAL
WHERE IDNOTAFISCAL NOT IN(SELECT ID_NOTA_FISCAL FROM ITEM_NOTA)
GO
PREENCHENDO AS NOTAS FISCAIS SEM ITENS:
/* AS NOTAS SERÃO PREENCHIDAS APENAS COM 1 PRODUTO ALEATORIO */
DECLARE
C_NOTAFISCAL CURSOR FOR
SELECT IDNOTAFISCAL FROM NOTA_FISCAL
WHERE IDNOTAFISCAL NOT IN(SELECT ID_NOTA_FISCAL FROM ITEM_NOTA)
DECLARE
@IDNOTAFISCAL INT,
@ID_PRODUTO INT,
@VALOR DECIMAL(10,2)
OPEN C_NOTAFISCAL
FETCH NEXT FROM C_NOTAFISCAL
INTO @IDNOTAFISCAL
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ID_PRODUTO =
(SELECT TOP 1 IDPRODUTO FROM PRODUTOS ORDER BY NEWID())
SET @VALOR =
(SELECT VALOR FROM PRODUTOS WHERE IDPRODUTO = @ID_PRODUTO)
INSERT INTO ITEM_NOTA(ID_PRODUTO, ID_NOTA_FISCAL, QUANTIDADE, VALOR)
VALUES(@ID_PRODUTO, @IDNOTAFISCAL,1, @VALOR)
FETCH NEXT FROM C_NOTAFISCAL
INTO @IDNOTAFISCAL
END
CLOSE C_NOTAFISCAL
DEALLOCATE C_NOTAFISCAL
RELATORIO/VIEW DO TOTAL POR NOTA FISCAL:
CREATE VIEW V_TOTAL_NOTAFISCAL AS
SELECT ID_NOTA_FISCAL, SUM(VALOR) AS SOMA
FROM ITEM_NOTA
GROUP BY ID_NOTA_FISCAL
TOTAL DA SOMA NA CARGA NOTA FISCAL: OBS: SOMA = TOTAL GASTO NA NOTA
CREATE VIEW V_CARGA_NOTAFISCAL AS
SELECT N.IDNOTAFISCAL, N.TOTAL AS TOTAL_NOTA, T.SOMA
FROM NOTA_FISCAL N
INNER JOIN V_TOTAL_NOTAFISCAL T
ON IDNOTAFISCAL = ID_NOTA_FISCAL
GO
SOMA = TOTAL DA NOTA
UPDATE V_CARGA_NOTAFISCAL SET TOTAL_NOTA = SOMA
GO
Nesta etapa iremos realizar os processos de ETL de acordo com as necessidades da loja musical. Houve uma mudança nos requisitos e eles desejam que o nome e sobrenome do cliente, estejam na mesma coluna como nome completo.
CREATE DATABASE LOJA_STAGE
GO
UTILIZANDO O DATABASE:
USE LOJA_STAGE
GO
CRIAÇÃO DA TABELA STAGE CLIENTES:
CREATE TABLE ST_CLIENTES(
IDCLIENTE INT DEFAULT NULL,
NOME_COMPLETO VARCHAR(80) DEFAULT NULL,
SEXO VARCHAR(6) DEFAULT NULL,
NASCIMENTO DATE DEFAULT NULL,
CIDADE VARCHAR(20) DEFAULT NULL,
UF VARCHAR(6) DEFAULT NULL,
EMAIL VARCHAR(100) DEFAULT NULL
)
GO
CRIAÇÃO DA TABELA STAGE FUNCIONARIOS:
CREATE TABLE ST_FUNCIONARIOS(
IDFUNCIONARIO INT DEFAULT NULL,
FUNCIONARIO VARCHAR(50) DEFAULT NULL,
SEXO VARCHAR(6) DEFAULT NULL,
CARGO VARCHAR(40) DEFAULT NULL
)
GO
CRIAÇÃO DA TABELA STAGE CATEGORIAS:
CREATE TABLE ST_CATEGORIAS(
IDCATEGORIA INT DEFAULT NULL,
CATEGORIA VARCHAR(40) DEFAULT NULL,
)
GO
CRIAÇÃO DA TABELA STAGE SUBCATEGORIAS:
CREATE TABLE ST_SUBCATEGORIAS(
IDSUB INT DEFAULT NULL,
SUB_CATEGORIA VARCHAR(35) DEFAULT NULL
)
GO
CRIAÇÃO DA TABELA STAGE FORNECEDORES:
CREATE TABLE ST_FORNECEDORES(
IDFORNECEDOR INT DEFAULT NULL,
FORNECEDOR VARCHAR(30) DEFAULT NULL,
)
GO
CRIAÇÃO DA TABELA STAGE MARCAS:
CREATE TABLE ST_MARCAS(
IDMARCA INT DEFAULT NULL,
MARCA VARCHAR(30) DEFAULT NULL
)
GO
CRIAÇÃO DA TABELA STAGE PRODUTOS:
CREATE TABLE ST_PRODUTOS(
IDPRODUTO INT DEFAULT NULL,
PRODUTO VARCHAR(100) DEFAULT NULL,
VALOR_UNIT NUMERIC(10,2) DEFAULT NULL,
CUSTO_MEDIO NUMERIC(10,2) DEFAULT NULL
)
GO
CRIAÇÃO DA TABELA STAGE NOTAS:
CREATE TABLE ST_NOTAS(
IDNOTA INT DEFAULT NULL
)
GO
CRIAÇÃO DA TABELA STAGE METODOS DE PAGAMENTO:
CREATE TABLE ST_METODOS(
IDMETODO INT DEFAULT NULL,
FORMA_PAGAMENTO VARCHAR(45) DEFAULT NULL
)
GO
OBS: FATO -> SÃO AS MEDIDAS DO NEGOCIO -- TOTAL -- QUANTIDADE -- LUCRO -- CUSTO -- DATA
CREATE TABLE ST_FATO(
ID_CLIENTE INT DEFAULT NULL,
ID_FUNCIONARIO INT DEFAULT NULL,
ID_FORNECEDOR INT DEFAULT NULL,
ID_PRODUTO INT DEFAULT NULL,
ID_CATEGORIA INT DEFAULT NULL,
ID_SUB INT DEFAULT NULL,
ID_MARCA INT DEFAULT NULL,
ID_NOTA INT DEFAULT NULL,
ID_METODO INT DEFAULT NULL,
DATA DATE DEFAULT NULL,
QUANTIDADE INT DEFAULT NULL,
TOTAL_ITEM NUMERIC(10,2) DEFAULT NULL,
CUSTO_TOTAL NUMERIC(10,2) DEFAULT NULL,
LUCRO_TOTAL NUMERIC(10,2) DEFAULT NULL
)
GO
CRIADO NO BANCO OLTP OBS: SERÁ USADO COMO A CARGA DA TABELA FATO.
USE LOJA_INSTRUMENTOS_OLTP
GO
CREATE VIEW RELATORIO_VENDAS_FATO AS
SELECT C.IDCLIENTE,
F.IDFUNCIONARIO,
P.IDPRODUTO,
CT.IDCATEGORIA,
S.IDSUB,
MA.IDMARCA,
FO.IDFORNECEDOR,
N.IDNOTAFISCAL,
M.IDMETODO,
I.QUANTIDADE,
(I.QUANTIDADE * P.CUSTO_MEDIO) AS CUSTO_TOTAL,
(I.VALOR - (I.QUANTIDADE * P.CUSTO_MEDIO)) AS LUCRO_TOTAL,
I.VALOR AS VALOR_VENDA_TOTAL,
N.DATA AS DATA
FROM NOTA_FISCAL N
INNER JOIN ITEM_NOTA I
ON N.IDNOTAFISCAL = I.ID_NOTA_FISCAL
INNER JOIN CLIENTES C
ON C.IDCLIENTE = N.ID_CLIENTE
INNER JOIN FUNCIONARIOS F
ON F.IDFUNCIONARIO = N.ID_FUNCIONARIO
INNER JOIN PRODUTOS P
ON P.IDPRODUTO = ID_PRODUTO
INNER JOIN METODO_PAGAMENTO M
ON M.IDMETODO = N.ID_FORMA
INNER JOIN FORNECEDORES FO
ON FO.IDFORNECEDOR = P.ID_FORNECEDOR
INNER JOIN CATEGORIAS CT
ON CT.IDCATEGORIA = P.ID_CATEGORIA
INNER JOIN SUB_CATEGORIAS S
ON S.IDSUB = P.ID_SUBCATEGORIAS
INNER JOIN MARCAS MA
ON MA.IDMARCA = P.ID_MARCA
GO
Nesta etapa será criado o projeto no visual studio 2015, e realizar as extrações, transformações e carregamento dos dados.
- NOVA...
- NAME SERVER: DIGITE UM "." OU SEU "NOME DO SERVIDOR";
- ESCOLHA AS CONEXÕES COM O OLTP E STAGE.
- TROQUE O NOME DO PACOTE EX: CARGA CLIENTE.
- SEQUENCE CONTAINER: TODOS OS PROCESSOS SERÃO FEITOS DENTRO DE UM CONTAINER;
- EXECUTE SQL TASK: SERÁ EXECUTADO UM TRUNCATE TABLE "NOME DA TABELA STAGE".
- INSTRUÇÃO SQL: DEFINA A CONEXÃO COM O STAGE E ESPECIFIQUE A CONSULTA A SER EXECUTADA.
- JOGUE O "EXECUTE SQL TASK" E O "DATA FLOW TASK" DENTRO DO CONTAINER.
- LIGUE O "EXECUTE SQL TASK" COM O "DATA FLOW TASK".
- OLE DB SOURCE: ORIGEM DOS DADOS.
- GERENCIADOR DE CONEXÕES: SELECIONE O DATABASE DA ORIGEM DOS DADOS;
- TABELA OU VIEW: SELECIONE A TABELA DA CARGA;
OBS: O MODO DE ACESSO PODE SER ALTERADO PARA "COMANDO DO SQL" E ENTÃO FEITO UM SELECT COM TODAS AS COLUNAS DA TABELA.
CRIE UMA COLUNA DERIVADA E FAÇA A LIGAÇÃO ENTRE "OLE DB SOURCE" E "DERIVED COLUMN".
.png?raw=true)
- NOME DA COLUNA DERIVADA: SERÁ O NOME DA NOVA COLUNA;
- EXPRESSÃO: AÇÃO A SER EXECUTADA, JUNTAR O NOME COM SOBRENOME.
CRIE O OLE DB DESTINATION.
- OLE DB DESTINATION: DESTINO DOS DADOS;
- LIGUE "COLUNA DERIVADA" COM "OLE DB DESTINATION".
- GERENCIADOR DE CONEXÕES: SELECIONE A DATABASE USADA COMO DESTINO DOS DADOS;
- TABELA OU EXIBIÇÃO/VIEW: SELECIONE A TABELA DESTINO;
- OK.
O processo de criação das cargas será basicamente o mesmo, excluindo a coluna derivada feita na carga clientes.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_funcionarios;
- DATA FLOW TASK: Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da tabela funcionarios no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_funcionarios no banco STAGE.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_categorias;
- DATA FLOW TASK: Leva a Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da tabela categorias no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_categorias banco STAGE.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_subcategorias;
- DATA FLOW TASK: Leva a Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da tabela sub_categorias no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_subcategorias banco STAGE.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_fornecedores;
- DATA FLOW TASK: Leva a Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da tabela Fornecedores no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_fornecedores banco STAGE.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_marcas;
- DATA FLOW TASK: Leva a Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da tabela Marcas no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_marcas banco STAGE.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_metodos;
- DATA FLOW TASK: Leva a Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da tabela Metodo_pagamento no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_metodos banco STAGE.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_notas;
- DATA FLOW TASK: Leva a Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da tabela Nota_Fiscal no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_notas banco STAGE.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_produtos;
- DATA FLOW TASK: Leva a Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da tabela Produtos no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_produtoss banco STAGE.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será feito o truncate table na tabela st_fato;
- DATA FLOW TASK: Leva a Area do fluxo de dados;
- OLE DB SOURCE: Origem dos dados, vindo da view RELATORIO_VENDAS_FATO criada anteriormente no banco OLTP;
- OLE DB DESTINATION: Destino dos dados, indo para a tabela st_FATO banco STAGE.
Após a criação de todas as cargas, execute uma por uma.
Nesta etapa iremos transformar as tabelas em dimensões. Criaremos o inicio e o fim do "registro", e usaremos a IDSK(Surrogate key) como chave primaria.
CREATE DATABASE LOJA_DW
GO
USE LOJA_DW
GO
CRIAÇÃO DA DIMENSÃO FUNCIONARIOS:
CREATE TABLE DIM_FUNCIONARIOS(
IDSK INT IDENTITY,
IDFUNCIONARIO INT,
FUNCIONARIO VARCHAR(50),
SEXO VARCHAR(6),
CARGO VARCHAR(40),
INICIO DATETIME,
FIM DATETIME,
CONSTRAINT SK_FUNCIONARIO PRIMARY KEY(IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO PRODUTOS:
CREATE TABLE DIM_PRODUTOS(
IDSK INT IDENTITY,
IDPRODUTO INT,
PRODUTO VARCHAR(100),
VALOR_UNIT NUMERIC(10,2),
CUSTO_MEDIO NUMERIC(10,2),
INICIO DATETIME,
FIM DATETIME,
CONSTRAINT SK_PRODUTOS PRIMARY KEY(IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO CATEGORIAS:
CREATE TABLE DIM_CATEGORIAS(
IDSK INT IDENTITY,
IDCATEGORIA INT,
CATEGORIA VARCHAR(40),
CONSTRAINT SK_CATEGORIAS PRIMARY KEY (IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO FORNECEDORES:
CREATE TABLE DIM_FORNECEDORES(
IDSK INT IDENTITY,
IDFORNECEDOR INT,
FORNECEDOR VARCHAR(30),
INICIO DATETIME,
FIM DATETIME,
CONSTRAINT SK_FORNECEDORES PRIMARY KEY(IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO METODOS:
CREATE TABLE DIM_METODOS(
IDSK INT IDENTITY,
IDMETODO INT,
FORMA_PAGAMENTO VARCHAR(45),
CONSTRAINT SK_METODOS PRIMARY KEY(IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO NOTAS:
CREATE TABLE DIM_NOTAS(
IDSK INT IDENTITY,
IDNOTA INT,
CONSTRAINT SK_NOTAS PRIMARY KEY(IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO CLIENTES:
CREATE TABLE DIM_CLIENTES(
IDSK INT IDENTITY,
IDCLIENTE INT,
NOME_COMPLETO VARCHAR(80),
SEXO VARCHAR(6),
NASCIMENTO DATE,
CIDADE VARCHAR(20),
UF VARCHAR(6),
EMAIL VARCHAR(100),
INICIO DATETIME,
FIM DATETIME,
CONSTRAINT SK_CLIENTES PRIMARY KEY (IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO TEMPO:
CREATE TABLE DIM_TEMPO(
IDSK INT IDENTITY,
DATA DATE,
DIA CHAR(2),
DIA_SEMANA VARCHAR(10),
MES CHAR(2),
NOME_MES VARCHAR(20),
QUARTO TINYINT,
NOME_QUARTO VARCHAR(20),
ANO CHAR(4),
ESTACAO_ANO VARCHAR(20),
FIM_SEMANA CHAR(1),
DATA_COMPLETA VARCHAR(10),
CONSTRAINT SK_TEMPO PRIMARY KEY (IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO MARCAS:
CREATE TABLE DIM_MARCAS(
IDSK INT IDENTITY,
IDMARCA INT,
MARCA VARCHAR(30),
CONSTRAINT SK_MARCAS PRIMARY KEY (IDSK)
)
GO
CRIAÇÃO DA DIMENSÃO SUBCATEGORIAS:
CREATE TABLE DIM_SUBCATEGORIAS(
IDSK INT IDENTITY,
IDSUB INT,
SUB_CATEGORIA VARCHAR(35),
CONSTRAINT SK_SUB PRIMARY KEY (IDSK)
)
GO
CREATE TABLE FATO(
ID_NOTA INT,
ID_CLIENTE INT,
ID_MARCA INT,
ID_FUNCIONARIO INT,
ID_METODO INT,
ID_PRODUTO INT,
ID_FORNECEDOR INT,
ID_CATEGORIA INT,
ID_SUB INT,
ID_TEMPO INT,
QUANTIDADE INT,
TOTAL_ITEM DECIMAL (10,2),
CUSTO_TOTAL DECIMAL (10,2),
LUCRO_TOTAL DECIMAL (10,2)
)
GO
Nesta etapa iremos adicionar as FKs na tabela FATO dentro do DW.
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_NOTAS
FOREIGN KEY (ID_NOTA) REFERENCES DIM_NOTAS(IDSK)
GO
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_CLIENTES
FOREIGN KEY (ID_CLIENTE) REFERENCES DIM_CLIENTES(IDSK)
GO
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_MARCAS
FOREIGN KEY (ID_MARCA) REFERENCES DIM_MARCAS(IDSK)
GO
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_FUNCIONARIOS
FOREIGN KEY (ID_FUNCIONARIO) REFERENCES DIM_FUNCIONARIOS(IDSK)
GO
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_METODO
FOREIGN KEY (ID_METODO) REFERENCES DIM_METODOS(IDSK)
GO
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_PROD
FOREIGN KEY (ID_PRODUTO) REFERENCES DIM_PRODUTOS(IDSK)
GO
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_FORNECEDOR
FOREIGN KEY (ID_FORNECEDOR) REFERENCES DIM_FORNECEDORES(IDSK)
GO
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_CATEGORIAS
FOREIGN KEY (ID_CATEGORIA) REFERENCES DIM_CATEGORIAS(IDSK)
GO
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_SUB
FOREIGN KEY (ID_SUB) REFERENCES DIM_SUBCATEGORIAS(IDSK)
go
ALTER TABLE FATO ADD CONSTRAINT FK_FATO_TEMPO
FOREIGN KEY (ID_TEMPO) REFERENCES DIM_TEMPO(IDSK)
GO
Nesta etapa iremos realizar o preenchimento da dimensão tempo.
--------------------------
------DIMENSÃO TEMPO------
--------------------------
--EXIBINDO A DATA ATUAL (PADRÃO BRASIL) DATA, MES, ANO
PRINT CONVERT(VARCHAR,GETDATE(),113)
-- INICIO DAS DATAS EM 2500, PARA DAR A POSSIBILIDADE
-- DE ADICIONAR DATAS ANTERIORES
DBCC CHECKIDENT (DIM_TEMPO, RESEED, 2500)
--INSERÇÃO DE DADOS NA DIMENSÃO TEMPO
DECLARE @DATAINICIO DATETIME, @DATAFIM DATETIME, @DATA DATETIME
PRINT GETDATE()
SELECT @DATAINICIO = '1/1/1980',
@DATAFIM = '1/1/2100'
SELECT @DATA = @DATAINICIO
WHILE @DATA < @DATAFIM
BEGIN
INSERT INTO DIM_TEMPO
(
DATA,
DIA,
DIA_SEMANA,
MES,
NOME_MES,
QUARTO,
NOME_QUARTO,
ANO
)
SELECT @DATA AS DATA, DATEPART(DAY,@DATA) AS DIA,
CASE DATEPART(DW, @DATA)
WHEN 1 THEN 'Domingo'
WHEN 2 THEN 'Segunda'
WHEN 3 THEN 'Terça'
WHEN 4 THEN 'Quarta'
WHEN 5 THEN 'Quinta'
WHEN 6 THEN 'Sexta'
WHEN 7 THEN 'Sábado'
END AS DIA_SEMANA,
DATEPART(MONTH,@DATA) AS MES,
CASE DATENAME(MONTH,@DATA)
/* Essa parte só funciona caso seu sql server esteja em inglês e você queira traduzir o nome dos meses para português */
WHEN 'January' THEN 'Janeiro'
WHEN 'February' THEN 'Fevereiro'
WHEN 'March' THEN 'Março'
WHEN 'April' THEN 'Abril'
WHEN 'May' THEN 'Maio'
WHEN 'June' THEN 'Junho'
WHEN 'July' THEN 'Julho'
WHEN 'August' THEN 'Agosto'
WHEN 'September' THEN 'Setembro'
WHEN 'October' THEN 'Outubro'
WHEN 'November' THEN 'Novembro'
WHEN 'December' THEN 'Dezembro'
END AS NOME_MES,
DATEPART(qq,@DATA) QUARTO,
CASE DATEPART(qq,@DATA)
WHEN 1 THEN 'Primeiro'
WHEN 2 THEN 'Segundo'
WHEN 3 THEN 'Terceiro'
WHEN 4 THEN 'Quarto'
END AS NOME_QUARTO
, DATEPART(YEAR,@DATA) ANO
SELECT @DATA = DATEADD(dd,1,@DATA)
END
UPDATE DIM_TEMPO
SET DIA = '0' + DIA
WHERE LEN(DIA) = 1
UPDATE DIM_TEMPO
SET MES = '0' + MES
WHERE LEN(MES) = 1
UPDATE DIM_TEMPO
SET DATA_COMPLETA = ANO + MES + DIA /* Padrão internacional*/
GO
select * from DIM_TEMPO
----------------------------------------------
----------FINS DE SEMANA E ESTAÇÕES-----------
----------------------------------------------
DECLARE C_TEMPO CURSOR FOR
SELECT IDSK, DATA_COMPLETA, DIA_SEMANA, ANO FROM DIM_TEMPO
DECLARE
@ID INT,
@DATA varchar(10),
@DIASEMANA VARCHAR(20),
@ANO CHAR(4),
@FIMSEMANA CHAR(3),
@ESTACAO VARCHAR(15)
OPEN C_TEMPO
FETCH NEXT FROM C_TEMPO
INTO @ID, @DATA, @DIASEMANA, @ANO
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DIASEMANA in ('Domingo','Sábado')
SET @FIMSEMANA = 'S'
ELSE
SET @FIMSEMANA = 'N'
--ATUALIZANDO ESTACOES
IF @DATA BETWEEN CONVERT(CHAR(4),@ano)+'0923'
AND CONVERT(CHAR(4),@ANO)+'1220'
SET @ESTACAO = 'Primavera'
ELSE IF @DATA BETWEEN CONVERT(CHAR(4),@ano)+'0321'
AND CONVERT(CHAR(4),@ANO)+'0620'
SET @ESTACAO = 'Outono'
ELSE IF @DATA BETWEEN CONVERT(CHAR(4),@ano)+'0621'
AND CONVERT(CHAR(4),@ANO)+'0922'
SET @ESTACAO = 'Inverno'
ELSE -- @data between 21/12 e 20/03
SET @ESTACAO = 'Verão'
--ATUALIZANDO FINS DE SEMANA
UPDATE DIM_TEMPO SET FIM_SEMANA = @FIMSEMANA
WHERE IDSK = @ID
--ATUALIZANDO
UPDATE DIM_TEMPO SET ESTACAO_ANO = @ESTACAO
WHERE IDSK = @ID
FETCH NEXT FROM C_TEMPO
INTO @ID, @DATA, @DIASEMANA, @ANO
END
CLOSE C_TEMPO
DEALLOCATE C_TEMPO
GO
Nesta etapa iremos realizar as extrações, transformações e carregamento dos dados do Staging Area para o DW.
CLIQUE EM "NOVA..."
- NAME SERVER: DIGITE UM "." OU SEU "NOME DO SERVIDOR";
- ADICIONE A CONEXÃO COM O DW.
Será criado um "countainer" com o "data flow task" praticamente igual o processo do stage.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_CLIENTES no STAGE;
- GERENCIADOR DE CONEXÕES OLE DB: Será colocado o local que vem os dados;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: Um select com as colunas e a tabela do STAGE.
Criação de uma Dimensão de Alteração Lenta. Onde o O ID será transformado em chave de negocio. E será definido o tipo das colunas entre: Historico, fixo, de alteração
- LIGAÇÃO: Ligação entre "OLE DB SOURCE" com "SLOWLY CHAGING DIMENSION";
- GERENCIADOR DE CONEXÕES: Banco de dados do DW;
- TABELA OU EXIBIÇÃO: Tabela destino dos Dados;
- KEY TYPE: Troca o tipo da chave.
- COLUNAS DE DIMENSÃO: As colunas usadas na dimensão;
- ALTERAR TIPO: Escolhe o tipo entre Historico, fixo, de alteração.
System::ContainerStarTime | System::CreationDate | System::StarTime
- START DATE COLUMN: Coluna de INICIO do registro;
- END DATE COLUMN: Coluna de FIM do registro;
- VARIABLE TO SET DATE VALUES: Variavel que define o valor de DATA. Será usado System::StarTime (Data que o pacote rodou).
- AVANÇAR >
- CONCLUIR
- OLE DB DESTINATION: Destino dos dados, indo para a dimensão DIM_CLIENTES no banco do DW.
Foi utilizado o mesmo processo em todas as cargas.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_FUNCIONARIOS no STAGE;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: Um select com as colunas desejadas de relevancia para o negócio, e a tabela do STAGE;
- DIMENSÃO DE ALTERAÇÃO LENTA: Transformação do ID para chave de negócio, definição do tipo de colunas, definição da data(INICIO,FIM).
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_PRODUTOS no STAGE;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: Um select com as colunas desejadas de relevancia para o negócio, e a tabela do STAGE;
- DIMENSÃO DE ALTERAÇÃO LENTA: Transformação do ID para chave de negócio, definição do tipo de colunas, definição da data(INICIO,FIM).
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_FORNECEDORES no STAGE;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: Um select com as colunas desejadas de relevancia para o negócio, e a tabela do STAGE;
- DIMENSÃO DE ALTERAÇÃO LENTA: Transformação do ID para chave de negócio, definição do tipo de colunas, definição da data(INICIO,FIM).
As cargas que possuem INICIO, FIM devem ser feitas com a "DIMENSÃO DE ALTERAÇÃO LENTA", já as que não possuem, devem usar um SQL de interseção.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_CATEGORIAS no STAGE;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: SELECT IDCATEGORIA, CATEGORIA FROM ST_CATEGORIAS WHERE IDCATEGORIA NOT IN (SELECT IDCATEGORIA FROM LOJA_DW.DBO.DIM_CATEGORIAS)
- OLE DB DESTINATION: Destino dos dados, indo para a tabela DIM_cATEGORIAS no DW.
Mesmo padrão da carga CATEGORIAS
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_MARCAS no STAGE;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: SELECT IDMARCA, MARCA FROM ST_MARCAS WHERE IDMARCA NOT IN (SELECT IDMARCA FROM LOJA_DW.DBO.DIM_MARCAS)
- OLE DB DESTINATION: Destino dos dados, indo para a tabela DIM_MARCAS no DW.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_METODOS no STAGE;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: SELECT IDMETODO, FORMA_PAGAMENTO FROM ST_METODOS WHERE IDMETODO NOT IN (SELECT IDMETODO FROM LOJA_DW.DBO.DIM_METODOS)
- OLE DB DESTINATION: Destino dos dados, indo para a tabela DIM_METODOS no DW.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_NOTAS no STAGE;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: SELECT [IDNOTA] FROM [LOJA_STAGE].[dbo].[ST_NOTAS] WHERE IDNOTA NOT IN (SELECT IDNOTA FROM LOJA_DW.DBO.DIM_NOTAS)
- OLE DB DESTINATION: Destino dos dados, indo para a tabela DIM_NOTAS no DW.
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- DATA FLOW TASK: Area do fluxo de dados;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
- OLE DB SOURCE: Origem dos dados, vindo da tabela ST_SUBCATEGORIAS no STAGE;
- MODO DE ACESSO AOS DADOS: Comando SQL;
- TEXTO DO COMANDO SQL: SELECT [IDSUB], [SUB_CATEGORIA] FROM [LOJA_STAGE].[dbo].[ST_SUBCATEGORIAS] WHERE IDSUB NOT IN (SELECT IDSUB FROM LOJA_DW.DBO.DIM_SUBCATEGORIAS)
- OLE DB DESTINATION: Destino dos dados, indo para a tabela DIM_SUBCATEGORIAS no DW.
Iremos utilizar uma Procedure para a carga FATO.
USE LOJA_DW
GO
-----------------------
----CRIAÇÃO DA PROC----
-----------------------
CREATE PROC CARGA_FATODW
AS
DECLARE @FINAL DATETIME
DECLARE @INICIAL DATETIME
SELECT @FINAL = MAX(DATA)
FROM DIM_TEMPO T
SELECT @INICIAL = MAX(DATA)
FROM FATO FT
JOIN DIM_TEMPO T
ON (FT.ID_TEMPO = T.IDSK)
IF @INICIAL IS NULL
BEGIN
SELECT @INICIAL = MIN(DATA)
FROM DIM_TEMPO T
END
INSERT INTO LOJA_DW.DBO.FATO(
ID_NOTA,
ID_CLIENTE,
ID_FUNCIONARIO,
ID_METODO,
ID_FORNECEDOR,
ID_MARCA,
ID_CATEGORIA,
ID_SUB,
ID_PRODUTO,
ID_TEMPO,
QUANTIDADE,
TOTAL_ITEM,
CUSTO_TOTAL,
LUCRO_TOTAL
)
SELECT
N.IDSK AS IDNOTA,
C.IDSK AS IDCLIENTE,
FU.IDSK AS IDFUNCIONARIO,
M.IDSK AS IDMETODO,
FN.IDSK AS IDFORNECEDOR,
MA.IDMARCA AS IDMARCA,
CA.IDCATEGORIA AS IDCATEGORIA,
S.IDSUB AS IDSUB,
P.IDSK AS IDPRODUTO,
T.IDSK AS IDTEMPO,
F.QUANTIDADE,
F.TOTAL_ITEM,
F.CUSTO_TOTAL,
F.LUCRO_TOTAL
FROM
LOJA_STAGE.DBO.ST_FATO F
INNER JOIN DIM_METODOS M
ON (F.ID_METODO = M.IDMETODO)
INNER JOIN DIM_NOTAS N
ON (F.ID_NOTA = N.IDNOTA)
INNER JOIN DIM_CATEGORIAS CA
ON (F.ID_CATEGORIA = CA.IDCATEGORIA)
INNER JOIN DIM_MARCAS MA
ON (F.ID_MARCA = MA.IDMARCA)
INNER JOIN DIM_SUBCATEGORIAS S
ON (F.ID_SUB = S.IDSUB)
INNER JOIN DIM_FORNECEDORES FN
ON (F.ID_FORNECEDOR = FN.IDFORNECEDOR
AND (FN.INICIO <= F.DATA
AND (FN.FIM >= F.DATA) OR (FN.FIM IS NULL)))
INNER JOIN DIM_CLIENTES C
ON (F.ID_CLIENTE = C.IDCLIENTE
AND (C.INICIO <= F.DATA
AND (C.FIM >= F.DATA) OR (C.FIM IS NULL)))
INNER JOIN DIM_FUNCIONARIOS FU
ON (F.ID_FUNCIONARIO = FU.IDFUNCIONARIO
AND (FU.INICIO <= F.DATA
AND (FU.FIM >= F.DATA) OR (FU.FIM IS NULL)))
INNER JOIN DIM_PRODUTOS P
ON (F.ID_PRODUTO = P.IDPRODUTO
AND (P.INICIO <= F.DATA
AND (P.FIM >= F.DATA) OR (P.FIM IS NULL)))
INNER JOIN DIM_TEMPO T
ON (CONVERT(VARCHAR, T.DATA,102) = CONVERT(VARCHAR,
F.DATA,102))
WHERE F.DATA > @INICIAL AND F.DATA < @FINAL
- SEQUENCE CONTAINER: Será a divisão das cargas (STAGE E DW);
- EXECUTE SQL TASK: Será usado para executar a PROC criada;
- LIGAÇÃO: Ligue o container da carga STAGE com a carga DW.
Dentro do "EXECUTE SQL TASK"
SQLStatement: Digite o comando para executar a procedure criada "EXEC CARGA_FATODW"
Nesta etapa iremos realizar a criação do CUBO no analysis services, a criação de um DataSource e também um DataSource View.
Primeiro, iremos adicionar um novo projeto.
- Arquivo -> Adicionar -> Novo Projeto
- Business Intelligence -> Analysis Services -> Projeto Multidimensional...
- FONTE DE DADOS -> NOVA FONTE DE DADOS...
- Criar uma fonte de dados... -> SEU BANCO DW -> Avançar.
- Usar um nome de usuário e senha...
- Seu nome de Usuário;
- Senha.
Escolha um nome para sua fonte de dados.
Praticamente o mesmo processo para a criação do DataSource.
- EXIBIÇÕES DE FONTE DE DADOS -> NOVA EXIBIÇÃO...
Selecionando o DATASOURCE criado anteriormente...
- AVANÇAR.
- Selecione todas as Dimensões (DIM_CLIENTE, ETC);
- Clique na seta ">";
- Avançar.
- NOME: Nome que desejar.
Nesta etapa será criada todas as dimensões dentro do Visual Studio 2015, que será utilizada na Inteligência de negócio (Business Intelligence). Com a Dimensão, será permitido a visualização dos dados e informações por diversos aspectos e perspectivas. o Processo de Criação das Dimensões é sempre o mesmo, apenas mudando os atributos, e a coluna que ira representar a dimensão.
- Dimensões -> Nova Dimensão...
- Usar uma tabela Existente -> Avançar...
- Exibição da fonte de dados: Será utilizado o DataSource View criado;
- Tabela principal: Escolha da tabela DIM_CLIENTES no banco DW;
- Coluna de nome: Coluna que ira representar a Dimensão.
.png?raw=true)
- Seleção dos Atributos(colunas) que será usado para as analises
- Obs: A coluna "NOME_COMPLETO" mudou de nome para "IDSK". O nome "IDSK" pode ser trocado para "NOME_COMPLETO" para melhor análise.
- Nome: Nome da Dimensão;
- Concluir.
- Será usado alguns atributos da dimensão para criar hierarquias desejas;
- Pode ser criado apenas arrastando.
Realização do Processamento da Dimensão criada.
- Dim CLIENTES -> Processar...
- Executar...
- Exibição da fonte de dados: Será utilizado o DataSource View criado;
- Tabela principal: Escolha da tabela DIM_FUNCIONARIOS no banco DW;
- Coluna de nome: Coluna que ira representar a Dimensão.
- Seleção dos Atributos(colunas) que será usado para as analises;
- Obs: A coluna "FUNCIONARIO" mudou de nome para "IDSK".
- Nome: Nome da Dimensão;
- Concluir.
- Troca de nome do atributo "IDSK" para "NOME_FUNCIONARIO". Após esses processos, foi realizado o processamento igual a DIM CLIENTES.
- Exibição da fonte de dados: Será utilizado o DataSource View criado;
- Tabela principal: Escolha da tabela DIM_PRODUTOS no banco DW;
- Coluna de nome: Coluna que ira representar a Dimensão.
- Seleção dos Atributos(colunas) que será usado para as analises;
- Obs: A coluna "PRODUTOS" mudou de nome para "IDSK".
- Nome: Nome da Dimensão;
- Concluir.
- Troca de nome do atributo "IDSK" para "PRODUTO". Após esses processos, foi realizado o processamento.
- Exibição da fonte de dados: Será utilizado o DataSource View criado;
- Tabela principal: Escolha da tabela DIM TEMPO no banco DW;
- Coluna de nome: Coluna que ira representar a Dimensão.
- Seleção dos Atributos(colunas) que será usado para as analises;
- Obs: A coluna "DATA" mudou de nome para "IDSK".
- Nome: Nome da Dimensão;
- Concluir.
- Troca de nome do atributo "IDSK" para "PRODUTO". Após esses processos, foi realizado o processamento.
Após a criação e processamento de todas as dimensões, então foi feito o CUBO de vendas.
- Cubos -> Novo Cubo...
- Usar tabelas existentes;
- Avançar.
.png?raw=true)
- FATO: Tabela que foi criada para ser usada como grupo de medidas de vendas.
- Selecionamento das medidas para ser utilizada no cubo.
- Selecionamento das dimensões utilizadas no cubo.
- Nome do cubo: nome que será utilizado no cubo;
- Concluir.
- Estrutura final do Cubo.
Nesta etapa será criado o Reporting Services dentro do VS2015, também será feito um Shared DataSource e um DataSet. Foi criado um arquivo dentro da pasta do projeto, e dentro dele será guardado as informações.