Fala galera, uma área que é extremamente importante quando estamos falando em BI (Business Intelligence), é a área do DW (Data Warehouse). Estudos apontam que a criação do DW, através do processo de ETL (Extraction, Transformation and Loading – Extração, Transformação e Carga), consome, em média, 70% do tempo total do projeto. Isso se dá ao fato de, em grande parte das vezes, precisarmos buscar as informações necessárias para responder as perguntas que a área de negócio faz, mas que nem sempre estão dentro do nosso ambiente relacional. É preciso, através dos processos de ETL, buscar estas informações em outras origens.
Uma questão que em aulas/palestras sempre me perguntam é: “COMO MONTAR O DW“? Sempre respondo para pensar no DW se adequando às necessidades da modelagem que vamos utilizar dentro do SSAS (SQL Server Analysis Services). Essa adequação é um exercício que deve ser feito juntamente com a área de negócios. Isso é um ponto importante. BI é pra área de negócios, a área de TI só falicita que a área de negócios consiga encontrar a informação mais rapido. Porém, é a área de negócios que dita a regra do BI… Pode parecer estranho, mas é isso que defendo! Ninguém melhor que a área de negócios para saber as perguntas que precisam das respostas… A nossa área será esse facilitador, entregando uma plataforma que irá responder estas perguntas!
Quando pensamos na arquitetura do DW, existem duas modelagens que são amplamente estudadas e utilizadas, Snowflake Schema e Star Schema… Elas se diferem pela modelagem da estrutura do ambiente e desempenho no processamento. Eu particularmente tento usar sempre a modelagem Star Schema, pelo desempenho ser melhor.
A grosso modo, no modelo Star Schema, as dimensões se relacionam diretamente com a tabela Fato, sem nenhuma sub-dimensão. A ligação é direta. Já no modelo Snowflake Schema, possuimos sub-dimensões ligadas às dimensões, que por sua vez estão ligadas à tabela fato. Isso bem resumidamente, lembrando que preciso montar um post específico sobre cada uma das modelagens!
Voltando à estratégia inicial do DW, nem tudo que está na tabela do OLTP precisa de fato estar dentro do DW. Por exemplo, na tabela de cadastro de clientes tem o campo telefone, mas a necessidade da área de negócios é responder quantos cadastros de solteiros foram realizados em Janeiro/2012. Os dados de telefone não são importantes para esta análise, já os dados de Estado Civil sim. Neste caso, precisamos enviar os dados de Estado Civil da tabela online (transacional) para a tabela analítica do DW e não precisamos enviar os dados de telefone.
Os dados existentes na tabela fato já dizem o que eles são por sí só. Os dados da tabela fato são, única e exclusivamente, fatos reais e verdadeiros, sobre a análise que precisa ser feita. A tabela fato deve receber somente os campos chaves das tabelas dimensões, criando o relacionamento entre elas. Bem no formato PK e FK que já estamos acostumados a encontrar no ambiente OLTP. Estes dados inseridos dentro da fato podem ser detalhados, fazendo uma busca na tabela dimensão correspondente àquela informação. Lembrando que os dados na tabela fato estão desnormalizados, sendo duplicados diversas vezes para responder todas as variações que geram um fato no cruzamento das dimensões.
A tabela Dimensão armazena os dados detalhados sobre aquele grupo específico. Seria algo do tipo, a tabela dimensão de cliente possui os dados relacionados à clientes. A dimensão de pagamentos, possui os dados referentes à pagamentos. A dimensão de pedidos, possui dados referentes à pedidos. Elas não se relacionam entre sí, a única ligação que me responde qual cliente comprou tal produto na data x, são as chaves que estão na tabela fato. Também são representações detalhadas das informações na qual os dados agrupados das Measures (medidas) serão cortados (quebrados / filtrados).
As medidas normalmente são baseadas nas informações contidas na tabela fato. Sendo os consolidadores – agregações – baseado em um campo que recebe uma operação. Estas agregações podem ser de SOMA, MÉDIA, MAXIMO, MÍNIMO, CONTAGEM, PRIMEIRO VALOR, ULTIMO VALOR…
Para exemplificar esses conceitos, vamos imaginar um cenário pequeno que representa um sistema de mercadinho (relembrando da época do colégio técnico em Processamento de Dados).
Este diagrama acima representa uma parte do sistema do mercadinho. E o script T-SQL abaixo gera o banco… Eu sei, eu sei… Faltam os índices e outros objetos. Mas não é esse o objetivo aqui. rss
CREATE DATABASE mercadinhoOLTP
GO
USE mercadinhoOLTP
GO
/************************/
/* CRIAÇÃO DAS TABELAS */
/************************/
CREATE TABLE tbCliente
( ClienteID INT IDENTITY(1,1) PRIMARY KEY
, ClienteNome VARCHAR(50)
, ClienteTelefone VARCHAR(15)
, ClienteDataCadastro DATE)
GO
CREATE TABLE tbPagamento
( PagamentoID INT IDENTITY(1,1) PRIMARY KEY
, ClienteID INT
, PedidoID INT
, PagamentoValor DECIMAL(8,2)
, PagamentoData DATE)
GO
CREATE TABLE tbPedido
( PedidoID INT IDENTITY(1,1) PRIMARY KEY
, PedidoData DATE
, ClienteID INT)
GO
CREATE TABLE tbProduto
( ProdutoID INT IDENTITY(1,1) PRIMARY KEY
, ProdutoNome VARCHAR(50)
, ProdutoValor DECIMAL(5,2))
GO
CREATE TABLE tbPedidoItem
( PedidoItemID INT IDENTITY(1,1) PRIMARY KEY
, PedidoID INT
, ProdutoID INT)
GO
ALTER TABLE tbPagamento WITH CHECK ADD CONSTRAINT FK_tbPagamento_tbCliente
FOREIGN KEY(ClienteID) REFERENCES tbCliente (ClienteID)
GO
ALTER TABLE tbPagamento WITH CHECK ADD CONSTRAINT FK_tbPagamento_tbPedido
FOREIGN KEY(PedidoID) REFERENCES tbPedido (PedidoID)
GO
ALTER TABLE tbPedido WITH CHECK ADD CONSTRAINT FK_tbPedido_tbCliente
FOREIGN KEY(ClienteID) REFERENCES tbCliente (ClienteID)
GO
ALTER TABLE tbPedidoItem WITH CHECK ADD CONSTRAINT FK_tbPedidoItem_tbPedido
FOREIGN KEY(PedidoID) REFERENCES tbPedido (PedidoID)
GO
ALTER TABLE tbPedidoItem WITH CHECK ADD CONSTRAINT FK_tbPedidoItem_tbProduto
FOREIGN KEY(ProdutoID) REFERENCES tbProduto (ProdutoID)
GO
/**********************************/
/* INSERT DE DADOS ALEATÓRIOS */
/**********************************/
-- 10.000 Clientes
INSERT INTO tbCliente (ClienteNome, ClienteTelefone, ClienteDataCadastro)
VALUES (NEWID(), '(XX) 1234-5678'
, CONVERT(DATE,CONVERT(DATETIME, CONVERT(INT,(RAND()*356)+40710))))
GO 10000
-- 100 Produtos
INSERT INTO tbProduto(ProdutoNome, ProdutoValor)
VALUES ( NEWID(), CONVERT(DECIMAL(5,2),(RAND()*100)+1,2) )
GO 100
-- 1.000 Pedidos
WITH Clientes AS
( SELECT ClienteID
, CONVERT(DATE, DATEADD(DAY,RAND()*100,
CONVERT(DATETIME,ClienteDataCadastro))) [ClienteDataCadastro]
FROM tbCliente
WHERE ClienteID = CONVERT(INT,(RAND()*10000)+1) )
INSERT INTO tbPedido(ClienteID,PedidoData)
SELECT ClienteID, ClienteDataCadastro FROM Clientes
GO 1000
-- 2.500 Itens nos Pedidos
INSERT INTO tbPedidoItem(PedidoID, ProdutoID)
SELECT (SELECT CONVERT(INT,RAND()*MAX(PedidoID)-1) FROM tbPedido)
,(SELECT CONVERT(INT,RAND()*MAX(ProdutoID)-1) FROM tbProduto)
GO 2500
-- Itens de Pagamento
INSERT INTO tbPagamento (ClienteID, PedidoID, PagamentoValor, PagamentoData)
SELECT PE.ClienteID, PE.PedidoID, SUM(PR.ProdutoValor)
, CONVERT(DATE, DATEADD(DAY,RAND()*5
, CONVERT(DATETIME,PE.PedidoData))) FROM tbPedido PE
INNER JOIN tbPedidoItem PI ON PE.PedidoID = PI.PedidoID
INNER JOIN tbProduto PR ON PR.ProdutoID = PI.ProdutoID
GROUP BY PE.ClienteID, PE.PedidoID
, CONVERT(DATE, DATEADD(DAY,RAND()*5, CONVERT(DATETIME,PE.PedidoData)))
ORDER BY ClienteID, PedidoID
Depois de criado o banco de dados de exemplo, para o padrão de ambientes transacionais. Vamos remodelar estas mesmas tabelas para o ambiente analítico, montando o Data Warehouse com uma idéia de tabela Fato e algumas tabelas Dimensões. Veja o diagrama e o códito T-SQL abaixo, para gerar esse ambiente de DW.
Com essa idéia, é possível comparar a modelagem dos ambientes preparados para OLTP (transacional) e OLAP (analítico). Lembrando que ambos estão rodando em cima do engine relacional do SQL Server. Somente depois de criar o projeto do SSAS e processar/publicar o cubo, que ele será criado de fato no ambiente Tabular/Multidimensional.
Para um próximo post, vou mostrar como criar, processar e publicar o cubo com base nestas informações do DW. Também escreverei sobre as diferenças da modelagem Star Schema e Snowflake Schema.
