Aplicando ETL em uma base de CRM

Atividade

Criar uma base de dados para o CRM;
Aplicar ETL na base;
Criar o modelo dimensional (DW).

Criar uma base de dados para o CRM

Para iniciar, vamos estabelecer a estrutura inicial da base de dados com a qual estaremos trabalhando. Para adicionar um toque de criatividade, optaremos por uma abordagem inicial totalmente não padronizada. Vamos deixar de lado as regras tradicionais de normalização de dados e as melhores práticas e criar nossa tabela inicial da seguinte forma:

Clique aqui para copiar o SQL da estrutura, ou clique aqui para os dados iniciais.

Aplicar ETL na base

Aplicar ETL (Extract, Transform, Load) envolve a extração de dados de diversas fontes, sua transformação para atender aos requisitos de análise e sua carga em um destino adequado. É um processo essencial para integrar e preparar dados para análise, garantindo que estejam limpos, consistentes e prontos para uso.

Extração (Extract)

Identificar as tabelas relevantes para o processo de ETL. Neste caso, as tabelas envolvidas são:

CustomerData (Tabela de Clientes)
ProdInfos (Tabela de Produtos)
CatTabela (Tabela de Categorias de Produto)
EmployeePessoas (Tabela de Funcionários)
OrdensTrans (Tabela de Pedidos)
PedidoItens (Tabela de Itens do Pedido)
InteracLogs (Tabela de Interações)

Transformação (Transform)

Aplicar as transformações necessárias nos dados para adequá-los ao modelo da camada ODS. Algumas transformações possíveis são:

Reestruturação e o remapeamento dos dados

Criação de novas tabelas, divisão de tabelas existentes, junção de múltiplas tabelas, normalização de dados.

CustomerData → Clientes

Antes	Atributos	Depois	Atributos
idCliente	INT PRIMARY KEY	ClienteID	INT IDENTITY(1,1) PRIMARY KEY
nomeComp	VARCHAR(100) NOT NULL	Nome	VARCHAR(50) NOT NULL
nomeComp	VARCHAR(100) NOT NULL	Sobrenome	VARCHAR(50) NOT NULL
email	VARCHAR(100) NOT NULL	Email	VARCHAR(100) NOT NULL
telefone	VARCHAR(20) NULL	Telefone	VARCHAR(20) NULL
endRua	VARCHAR(200) NULL	Endereco	VARCHAR(200) NULL
endCid	VARCHAR(50) NULL	Cidade	VARCHAR(50) NULL
endEst	CHAR(2) NULL	Estado	CHAR(2) NULL
endCEP	CHAR(8) NULL	CEP	CHAR(8) NULL

ProdInfos → Produtos

Antes	Atributos	Depois	Atributos
prodID	INT PRIMARY KEY	ProdutoID	INT IDENTITY(1,1) PRIMARY KEY
prodNome	VARCHAR(100) NOT NULL	Nome	VARCHAR(100) NOT NULL
prodDesc	VARCHAR(MAX) NULL	Descricao	VARCHAR(MAX) NULL
prodPreco	DECIMAL(10,2) NOT NULL	Preco	DECIMAL(10,2) NOT NULL
categoriaID	INT NOT NULL	-	-

CatTabela → Categorias

Antes	Atributos	Depois	Atributos
catID	INT PRIMARY KEY	CategoriaID	INT IDENTITY(1,1) PRIMARY KEY
catNome	VARCHAR(50) NOT NULL	Nome	VARCHAR(50) NOT NULL

EmployeePessoas → Funcionarios

Antes	Atributos	Depois	Atributos
funcID	INT PRIMARY KEY	FuncionarioID	INT IDENTITY(1,1) PRIMARY KEY
firstName	VARCHAR(50) NOT NULL	Nome	VARCHAR(50) NOT NULL
lastName	VARCHAR(50) NOT NULL	Sobrenome	VARCHAR(50) NOT NULL
email	VARCHAR(100) NOT NULL	Email	VARCHAR(100) NOT NULL
tel	VARCHAR(20) NULL	Telefone	VARCHAR(20) NULL
cargo	VARCHAR(50) NOT NULL	Cargo	VARCHAR(50) NOT NULL

OrdensTrans → Pedidos

Antes	Atributos	Depois	Atributos
ordensID	INT PRIMARY KEY	PedidoID	INT IDENTITY(1,1) PRIMARY KEY
clienteID	INT NOT NULL	ClienteID	INT FOREIGN KEY REFERENCES Clientes(ClienteID)
dataOrdens	DATETIME NOT NULL	DataPedido	DATETIME NOT NULL
statusPedido	VARCHAR(20) NOT NULL	StatusPedido	VARCHAR(20) NOT NULL

PedidoItens → ItensPedido

Antes	Atributos	Depois	Atributos
itemID	INT PRIMARY KEY	ItemPedidoID	INT IDENTITY(1,1) PRIMARY KEY
pedidoID	INT NOT NULL	PedidoID	INT FOREIGN KEY REFERENCES Pedidos(PedidoID)
prodID	INT NOT NULL	ProdutoID	INT FOREIGN KEY REFERENCES Produtos(ProdutoID)
qtd	INT NOT NULL	Quantidade	INT NOT NULL
precoUnit	DECIMAL(10,2) NOT NULL	PrecoUnitario	DECIMAL(10,2) NOT NULL

InteracLogs → Interacoes

Antes	Atributos	Depois	Atributos
logID	INT PRIMARY KEY	InteracaoID	INT IDENTITY(1,1) PRIMARY KEY
clienteID	INT NOT NULL	ClienteID	INT FOREIGN KEY REFERENCES Clientes(ClienteID)
funcID	INT NOT NULL	FuncionarioID	INT FOREIGN KEY REFERENCES Funcionarios(FuncionarioID)
dataInter	DATETIME NOT NULL	Data	DATETIME NOT NULL
tipo	VARCHAR(20) NOT NULL	Tipo	VARCHAR(20) NOT NULL
anot	VARCHAR(MAX) NULL	Anotacoes	VARCHAR(MAX) NULL

→ ProdutoCategoria (nova)

Essa tabela foi projetada para permitir que um produto seja categorizado em uma ou várias categorias. Ela utilizará o campo categoriaID da tabela ProdInfos para manter a consistência com os dados existentes e terá uma chave primária composta.

Antes	Atributos	Depois	Atributos
-	-	ProdutoID	INT NOT NULL FOREIGN KEY REFERENCES Produtos(ProdutoID)
-	-	CategoriaID	INT NOT NULL FOREIGN KEY REFERENCES Categorias(CategoriaID)

Carga (Load)

Carregar os dados transformados na camada ODS, seguindo o modelo de dados projetado para essa camada.

Criando o banco de dados ODS

Vamos criar nosso banco de dados ODS:

CREATE DATABASE ODS_CRM;

Criando as tabelas no ODS

Tabela de Clientes

CREATE TABLE ODS_Clientes (
    ClienteID INT IDENTITY(1,1) PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL,
    Sobrenome VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    Telefone VARCHAR(20) NULL,
    Endereco VARCHAR(200) NULL,
    Cidade VARCHAR(50) NULL,
    Estado CHAR(2) NULL,
    CEP CHAR(8) NULL
);

Tabela de Pedidos

CREATE TABLE ODS_Pedidos (
    PedidoID INT IDENTITY(1,1) PRIMARY KEY,
    ClienteID INT NOT NULL FOREIGN KEY REFERENCES Clientes(ClienteID),
    DataPedido DATETIME NOT NULL,
    StatusPedido VARCHAR(20) NOT NULL
);

Tabela de Itens do Pedido

CREATE TABLE ODS_ItensPedido (
    ItemPedidoID INT IDENTITY(1,1) PRIMARY KEY,
    PedidoID INT NOT NULL FOREIGN KEY REFERENCES Pedidos(PedidoID),
    ProdutoID INT NOT NULL FOREIGN KEY REFERENCES Produtos(ProdutoID),
    Quantidade INT NOT NULL,
    PrecoUnitario DECIMAL(10,2) NOT NULL
);

Tabela de Produtos

CREATE TABLE ODS_Produtos (
    ProdutoID INT IDENTITY(1,1) PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    Descricao VARCHAR(MAX) NULL,
    Preco DECIMAL(10,2) NOT NULL
);

Tabela de Categorias

CREATE TABLE ODS_Categorias (
    CategoriaID INT IDENTITY(1,1) PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL
);

Tabela de Categorias do Produto

CREATE TABLE ODS_ProdutoCategoria (
    ProdutoID INT NOT NULL FOREIGN KEY REFERENCES Produtos(ProdutoID),
    CategoriaID INT NOT NULL FOREIGN KEY REFERENCES Categorias(CategoriaID),
    PRIMARY KEY (ProdutoID, CategoriaID)
);

Tabela de Funcionários

CREATE TABLE ODS_Funcionarios (
    FuncionarioID INT IDENTITY(1,1) PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL,
    Sobrenome VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    Telefone VARCHAR(20) NULL,
    Cargo VARCHAR(50) NOT NULL
);

Tabela de Interações

CREATE TABLE ODS_Interacoes (
    InteracaoID INT IDENTITY(1,1) PRIMARY KEY,
    ClienteID INT NOT NULL FOREIGN KEY REFERENCES Clientes(ClienteID),
    FuncionarioID INT NOT NULL FOREIGN KEY REFERENCES Funcionarios(FuncionarioID),
    Data DATETIME NOT NULL,
    Tipo VARCHAR(20) NOT NULL,
    Anotacoes VARCHAR(MAX) NULL
);

Importando os dados para o ODS

Tabela de Clientes

SET IDENTITY_INSERT dbo.ODS_Clientes ON;
INSERT INTO ODS_Clientes (Nome, Sobrenome, Email, Telefone, Endereco, Cidade, Estado, CEP)
SELECT DISTINCT
    PARSENAME(REPLACE(nomeComp, ' ', '.'), 2) AS primeiro_nome,
    PARSENAME(REPLACE(nomeComp, ' ', '.'), 1) AS ultimo_nome,
    email,
    telefone,
    endRua,
    endCid,
    endEst,
    endCEP
FROM CustomerData;
SET IDENTITY_INSERT dbo.ODS_Clientes OFF;

Tabela de Pedidos

SET IDENTITY_INSERT dbo.ODS_Pedidos ON;
INSERT INTO ODS_Pedidos(PedidoID, ClienteID, DataPedido, StatusPedido)
SELECT DISTINCT
    ordensID,
    clienteID,
    dataOrdens,
    statusPedido
FROM OrdensTrans;
SET IDENTITY_INSERT dbo.ODS_Pedidos OFF;

Tabela de Itens do Pedido

SET IDENTITY_INSERT dbo.ODS_ItensPedido ON;
INSERT INTO ODS_ItensPedido(ItemPedidoID ,PedidoID, ProdutoID, Quantidade, PrecoUnitario)
SELECT DISTINCT
    itemID,
    pedidoID,
    prodID,
    qtd,
    precoUnit
FROM PedidoItens;
SET IDENTITY_INSERT dbo.ODS_ItensPedido OFF;

Tabela de Produtos

SET IDENTITY_INSERT dbo.ODS_Produtos ON;
INSERT INTO ODS_Produtos(ProdutoID, Nome, Descricao, Preco)
SELECT DISTINCT
    prodID,
    prodNome,
    prodDesc,
    prodPreco
FROM ProdInfos;
SET IDENTITY_INSERT dbo.ODS_Produtos OFF;

Tabela de Categorias

SET IDENTITY_INSERT dbo.ODS_Categorias ON;
INSERT INTO ODS_Categorias(CategoriaID, Nome)
SELECT DISTINCT
    catID,
    catNome
FROM CatTabela;
SET IDENTITY_INSERT dbo.ODS_Categorias OFF;

Tabela de Categorias do Produto

INSERT INTO ODS_ProdutoCategoria(ProdutoID, CategoriaID)
SELECT DISTINCT
    prodID,
    categoriaID
FROM ProdInfos;

Tabela de Funcionários

SET IDENTITY_INSERT dbo.ODS_Funcionarios ON;
INSERT INTO ODS_Funcionarios(FuncionarioID, Nome, Sobrenome, Email, Telefone, Cargo)
SELECT DISTINCT
    funcID,
    firstName,
    lastName,
    email,
    tel,
    cargo
FROM EmployeePessoas;
SET IDENTITY_INSERT dbo.ODS_Funcionarios OFF;

Tabela de Interações

SET IDENTITY_INSERT dbo.ODS_Interacoes ON;
INSERT INTO ODS_Interacoes(InteracaoID, ClienteID, FuncionarioID, Data, Tipo, Anotacoes)
SELECT DISTINCT
    logID,
    clienteID,
    funcID,
    dataInter,
    tipo,
    anot
FROM InteracLogs;
SET IDENTITY_INSERT dbo.ODS_Interacoes OFF;

Ao final, teremos a seguinte estrutura:

Clique aqui para copiar o SQL da estrutura, ou clique aqui para o SQL de importação.

Criar o modelo dimensional (DW)

Criar um modelo dimensional (Data Warehouse) implica na concepção de uma estrutura de dados otimizada para análise de negócios. Esse modelo é composto por tabelas de fatos que representam métricas de negócios e tabelas de dimensão que fornecem contexto para essas métricas. O objetivo é facilitar consultas analíticas eficientes e oferecer uma visão consistente e integrada dos dados da organização.

Identificando as tabelas de dimensão e de fato.

Antes	Depois	Tipo
ODS_Clientes	DimensaoCliente	Dimensão
ODS_Pedidos e ODS_ItensPedido	FatoPedido	Fato
ODS_Produtos	DimensaoProduto	Dimensão
ODS_Categorias	DimensaoCategoria	Dimensão
ODS_ProdutoCategoria	-	-
ODS_Funcionarios	DimensaoFuncionario	Dimensão
ODS_Interacoes	DimensaoInteracao	Dimensão
-	DimensaoTempo	Dimensão

Criando a nova estrutura

Dimensão de Cliente

CREATE TABLE DimensaoCliente (
    ClienteID INT PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL,
    Sobrenome VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    Telefone VARCHAR(20) NULL,
    Endereco VARCHAR(200) NULL,
    Cidade VARCHAR(50) NULL,
    Estado CHAR(2) NULL,
    CEP CHAR(8) NULL
);

Dimensão de Produto

CREATE TABLE DimensaoProduto (
    ProdutoID INT PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    Descricao VARCHAR(MAX) NULL,
    CategoriaID INT FOREIGN KEY REFERENCES DimensaoCategoria(CategoriaID)
);

Dimensão de Categoria

CREATE TABLE DimensaoCategoria (
    CategoriaID INT PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL
);

Dimensão de Funcionário

CREATE TABLE DimensaoFuncionario (
    FuncionarioID INT PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL,
    Sobrenome VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    Telefone VARCHAR(20) NULL,
    Cargo VARCHAR(50) NOT NULL
);

Dimensão de Tempo (Datas)

CREATE TABLE DimensaoTempo (
    DataID INT IDENTITY(1,1) PRIMARY KEY,
    Data DATETIME,
    Ano INT NOT NULL,
    Mes INT NOT NULL,
    Dia INT NOT NULL,
    DiaSemana INT NOT NULL,
    DiaAno INT NOT NULL,
    CONSTRAINT Unique_Date UNIQUE(Data)
);

Dimensão de Interação

CREATE TABLE DimensaoInteracao (
    InteracaoID INT PRIMARY KEY,
    Tipo VARCHAR(20) NOT NULL
);

Fato de Pedido

CREATE TABLE FatoPedido (
    PedidoID INT IDENTITY(1,1) PRIMARY KEY,
    ClienteID INT FOREIGN KEY REFERENCES DimensaoCliente(ClienteID),
    FuncionarioID INT FOREIGN KEY REFERENCES DimensaoFuncionario(FuncionarioID),
    DataID INT FOREIGN KEY REFERENCES DimensaoTempo(DataID),
    InteracaoID INT FOREIGN KEY REFERENCES DimensaoInteracao(InteracaoID),
    ProdutoID INT FOREIGN KEY REFERENCES DimensaoProduto(ProdutoID),
    Quantidade INT NOT NULL,
    PrecoUnitario DECIMAL(10,2) NOT NULL
);

Importando os dados

Dimensão de Cliente

INSERT INTO DimensaoCliente(ClienteID, Nome, Sobrenome, Email, Telefone, Endereco, Cidade, Estado, CEP)
SELECT DISTINCT
    ClienteID,
    Nome,
    Sobrenome,
    Email,
    Telefone,
    Endereco,
    Cidade,
    Estado,
    CEP
FROM ODS_Clientes;

Dimensão de Categoria

INSERT INTO DimensaoCategoria(CategoriaID, Nome)
SELECT DISTINCT
    CategoriaID,
    Nome
FROM ODS_Categorias;

Dimensão de Produto

INSERT INTO DimensaoProduto(ProdutoID, Nome, Descricao, CategoriaID)
SELECT DISTINCT
    OP.ProdutoID,
    OP.Nome,
    OP.Descricao,
    OPC.CategoriaID
FROM ODS_Produtos OP JOIN ODS_ProdutoCategoria OPC ON OP.ProdutoID = OPC.ProdutoID;

Dimensão de Funcionário

INSERT INTO DimensaoFuncionario(FuncionarioID, Nome, Sobrenome, Email, Telefone, Cargo)
SELECT DISTINCT
    FuncionarioID,
    Nome,
    Sobrenome,
    Email,
    Telefone,
    Cargo
FROM ODS_Funcionarios;

Dimensão de Interação

INSERT INTO DimensaoInteracao(InteracaoID, Tipo)
SELECT DISTINCT
    InteracaoID,
    Tipo
FROM ODS_Interacoes;

Dimensão de Tempo

INSERT INTO DimensaoTempo(Data, Ano, Mes, Dia, DiaSemana, DiaAno)
SELECT DISTINCT
    CONVERT(DATE, DataPedido),
    YEAR(DataPedido),
    MONTH(DataPedido),
    DAY(DataPedido),
    DATEPART(WEEKDAY, DataPedido),
    DATEPART(DAYOFYEAR, DataPedido)
FROM ODS_Pedidos;

Fato de Pedido

INSERT INTO FatoPedido(ClienteID, FuncionarioID, DataID, InteracaoID, ProdutoID, Quantidade, PrecoUnitario)
SELECT DISTINCT
    OC.ClienteID,
    OI.FuncionarioID,
    DT.DataID,
    OI.InteracaoID,
    OIP.ProdutoID,
    OIP.Quantidade,
    OIP.PrecoUnitario
FROM
    ODS_Pedidos OP
JOIN ODS_Clientes OC ON OP.ClienteID = OC.ClienteID
JOIN DimensaoTempo DT ON OP.DataPedido = DT.Data
JOIN ODS_Interacoes OI ON OP.ClienteID = OI.ClienteID
JOIN ODS_ItensPedido OIP ON OP.PedidoID = OIP.PedidoID;

Resultado final

Clique aqui para copiar o SQL da estrutura, ou clique aqui para o SQL de importação.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

Aplicando ETL em uma base de CRM

Atividade

Criar uma base de dados para o CRM

Aplicar ETL na base

Extração (Extract)

Transformação (Transform)

Reestruturação e o remapeamento dos dados

CustomerData → Clientes

ProdInfos → Produtos

CatTabela → Categorias

EmployeePessoas → Funcionarios

OrdensTrans → Pedidos

PedidoItens → ItensPedido

InteracLogs → Interacoes

→ ProdutoCategoria (nova)

Carga (Load)

Criando o banco de dados ODS

Criando as tabelas no ODS

Importando os dados para o ODS

Ao final, teremos a seguinte estrutura:

Criar o modelo dimensional (DW)

Identificando as tabelas de dimensão e de fato.

Criando a nova estrutura

Importando os dados

Resultado final

Files

README.md

Latest commit

History

README.md

File metadata and controls

Aplicando ETL em uma base de CRM

Atividade

Criar uma base de dados para o CRM

Aplicar ETL na base

Extração (Extract)

Transformação (Transform)

Reestruturação e o remapeamento dos dados

CustomerData → Clientes

ProdInfos → Produtos

CatTabela → Categorias

EmployeePessoas → Funcionarios

OrdensTrans → Pedidos

PedidoItens → ItensPedido

InteracLogs → Interacoes

→ ProdutoCategoria (nova)

Carga (Load)

Criando o banco de dados ODS

Criando as tabelas no ODS

Importando os dados para o ODS

Ao final, teremos a seguinte estrutura:

Criar o modelo dimensional (DW)

Identificando as tabelas de dimensão e de fato.

Criando a nova estrutura

Importando os dados

Resultado final