SSIS – Criando seu próprio SCD 2

Nesse post da série você irá preparar o ambiente de testes para que seja possível criarmos os fluxos de carga de dados da dimensão de Cliente de nosso Data Warehouse fictício da Light LTDA.


Criando o ambiente de testes OLTP e OLAP

Um OLTP (On-line Transaction Processing) é que um sistema de banco de dados que trabalha com os dados em tempo real.

Geralmente estes SGBDs estão ligados a uma aplicação, recebendo constantemente os dados inseridos, alterados e excluídos desse sistema pelo usuário.

Já um On-line Analytical Processing (OLAP), como o próprio nome já diz, é um ambiente responsável por guardar dados analíticos, um Data Warehouse.


Vamos iniciar a criação do nosso ambiente de testes. Você irá criar a tabela, que guarda os dados dos clientes no banco OLTP de uma grande empresa de vendas fictícia, chamada Light LTDA, usando o código abaixo.

CREATE DATABASE OLTP
GO

USE OLTP
GO 

CREATE TABLE CLIENTES ( 
    ID INT NOT NULL IDENTITY (1,1),
    NOME VARCHAR(25),
    CPF VARCHAR(14),
    EMAIL VARCHAR(25),
    DDD CHAR(2),
    TELEFONE CHAR(8),
    DATA_CADASTRO DATE)
GO

Depois de criada a base de dados e a tabela de clientes, é necessário inserir os seus registros. Você adicionará à essa tabela 500 mil dados de clientes no ano de 2013 usando o código abaixo.

DECLARE @n INT, @n1 INT, @n2 INT, @n3 INT, @n4 INT, 
        @n5 INT, @n6 INT, @n7 INT, @n8 INT, @n9 INT 
DECLARE @d1 INT, @d2 INT
DECLARE @nome VARCHAR(21), @email VARCHAR(20), @cpf VARCHAR(14) 
DECLARE @ddd CHAR(2), @telefone CHAR(8)
DECLARE @data DATE
DECLARE @contador INT

SET @contador = 0

WHILE @contador < 500000

BEGIN	
-- Gera nome do cliente aleatório.
SET @nome = SUBSTRING(CONVERT(VARCHAR(40), NEWID()),0,21);

-- Gera CPF do cliente aleatório.
SET @n = 9;
SET @n1 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n2 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n3 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n4 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n5 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n6 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n7 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n8 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n9 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @d1 = @n9*2+@n8*3+@n7*4+@n6*5+@n5*6+@n4*7+@n3*8+@n2*9+@n1*10;
SET @d1 = 11 - (@d1%11);

IF (@d1 >= 10) 
SET @d1 = 0
SET @d2 = @d1*2+@n9*3+@n8*4+@n7*5+@n6*6+@n5*7+@n4*8+@n3*9+@n2*10+@n1*11;
SET @d2 = 11 - ( @d2%11 );

IF (@d2 >= 10) 
SET @d2 = 0;
SET @cpf = CAST(@n1 AS VARCHAR(1)) + 
CAST(@n2 AS VARCHAR(1)) + 
CAST(@n3 AS VARCHAR(1)) + '.' + 
CAST(@n4 AS VARCHAR(1)) + 
CAST(@n5 AS VARCHAR(1)) + 
CAST(@n6 AS VARCHAR(1)) + '.' +
CAST(@n7 AS VARCHAR(1)) + 
CAST(@n8 AS VARCHAR(1)) + 
CAST(@n9 AS VARCHAR(1)) + '-' +
CAST(@d1 AS VARCHAR(1)) + 
45 CAST(@d2 AS VARCHAR(1))

-- Gera email do cliente aleatório.
SET @email = SUBSTRING(CONVERT(varchar(40), NEWID()),0,11) + 
'@email.com';

-- Gera data de cadastro do cliente aleatória.
SET @data = CONVERT(DATE, CONVERT(VARCHAR(15),'2013-' 
+CONVERT(VARCHAR(5),(CONVERT(INT,RAND()*12))+1) + '-' + 
CONVERT(VARCHAR(5),(CONVERT(INT,RAND()*27))+1) ))

-- Gera DDD do cliente aleatório.
SET @ddd = CAST(@n8 AS VARCHAR(1)) +
CAST(@n4 AS VARCHAR(1))

-- Gera telefone do cliente aleatório.
SET @telefone = CAST(@n6 AS VARCHAR(1)) +
CAST(@n9 AS VARCHAR(1)) +
CAST(@n2 AS VARCHAR(1)) + 
CAST(@n4 AS VARCHAR(1)) +
CAST(@n1 AS VARCHAR(1)) +
CAST(@n5 AS VARCHAR(1)) +
CAST(@n8 AS VARCHAR(1)) +
CAST(@n3 AS VARCHAR(1)) 

-- Popula a tabela Clientes.
INSERT INTO CLIENTES VALUES (
    @nome ,
    @cpf ,
    @email ,
    @ddd ,
    @telefone ,
    @data )

SET @contador = @contador + 1 

END

Por último, você precisará criar o banco OLAP e a dimensão de Clientes de um dos Data Marts da instituição Light LTDA. Isso será possível utilizando o script abaixo.

CREATE DATABASE OLAP
GO
 
USE OLAP
GO 

CREATE TABLE DM_CLIENTES ( 
     SK NOT NULL IDENTITY (1,1), 
     ID INT NOT NULL,
     NOME VARCHAR(25),
     CPF VARCHAR(14),
     EMAIL VARCHAR(25),
     DDD CHAR(2),
     TELEFONE CHAR(8),
     DATA_INICIO DATE
     DATA_FIM DATE )
GO

Obs – Para quem está iniciando os estudos em Business Intelligence, os Data Marts são pequenas partes de um Data Warehouse formados por tabelas denominadas Fatos e Dimensões. Basicamente, as Dimensões são tabelas que contém atributos relacionados a determinado assunto. Já as tabelas de Fato contém as chaves primários das dimensões (as Foreign Key) e medidas, métricas e valores.


No Próximo post da série você irá aprender a configurar o componente Slowly Changing Dimension na ferramenta de ETL Integration Services.

3 comentários sobre “SSIS – Criando seu próprio SCD 2

  1. Pingback: SSIS – Criando seu próprio SCD 1 | Alex Souza

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s