Estratégia para montagem de Data Warehouse

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).

image

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

 1: CREATE DATABASE mercadinhoOLTP
 2: GO
 3: 
 4: USE mercadinhoOLTP
 5: GO
 6: 
 7: /************************/
 8: /* CRIAÇÃO DAS TABELAS  */
 9: /************************/
 10: 
 11: CREATE TABLE tbCliente
 12: ( ClienteID INT IDENTITY(1,1) PRIMARY KEY
 13: , ClienteNome VARCHAR(50)
 14: , ClienteTelefone VARCHAR(15)
 15: , ClienteDataCadastro DATE)
 16: GO
 17: 
 18: CREATE TABLE tbPagamento
 19: ( PagamentoID INT IDENTITY(1,1) PRIMARY KEY
 20: , ClienteID INT
 21: , PedidoID INT
 22: , PagamentoValor DECIMAL(8,2)
 23: , PagamentoData DATE)
 24: GO
 25: 
 26: CREATE TABLE tbPedido
 27: ( PedidoID INT IDENTITY(1,1) PRIMARY KEY
 28: , PedidoData DATE
 29: , ClienteID INT)
 30: GO
 31: 
 32: CREATE TABLE tbProduto
 33: ( ProdutoID INT IDENTITY(1,1) PRIMARY KEY
 34: , ProdutoNome VARCHAR(50)
 35: , ProdutoValor DECIMAL(5,2))
 36: GO
 37: 
 38: CREATE TABLE tbPedidoItem
 39: ( PedidoItemID INT IDENTITY(1,1) PRIMARY KEY
 40: , PedidoID INT
 41: , ProdutoID INT)
 42: GO
 43: 
 44: ALTER TABLE tbPagamento  WITH CHECK ADD  CONSTRAINT FK_tbPagamento_tbCliente
 45: FOREIGN KEY(ClienteID) REFERENCES tbCliente (ClienteID)
 46: GO
 47: 
 48: ALTER TABLE tbPagamento  WITH CHECK ADD  CONSTRAINT FK_tbPagamento_tbPedido
 49: FOREIGN KEY(PedidoID) REFERENCES tbPedido (PedidoID)
 50: GO
 51: 
 52: ALTER TABLE tbPedido  WITH CHECK ADD  CONSTRAINT FK_tbPedido_tbCliente
 53: FOREIGN KEY(ClienteID) REFERENCES tbCliente (ClienteID)
 54: GO
 55: 
 56: ALTER TABLE tbPedidoItem  WITH CHECK ADD  CONSTRAINT FK_tbPedidoItem_tbPedido
 57: FOREIGN KEY(PedidoID) REFERENCES tbPedido (PedidoID)
 58: GO
 59: 
 60: ALTER TABLE tbPedidoItem  WITH CHECK ADD  CONSTRAINT FK_tbPedidoItem_tbProduto
 61: FOREIGN KEY(ProdutoID) REFERENCES tbProduto (ProdutoID)
 62: GO
 63: 
 64: /**********************************/
 65: /*  INSERT DE DADOS ALEATÓRIOS    */
 66: /**********************************/
 67: 
 68: -- 10.000 Clientes
 69: INSERT INTO tbCliente (ClienteNome, ClienteTelefone, ClienteDataCadastro)
 70: VALUES (NEWID(), '(XX) 1234-5678'
 71:       , CONVERT(DATE,CONVERT(DATETIME, CONVERT(INT,(RAND()*356)+40710))))
 72: GO 10000
 73: 
 74: -- 100 Produtos
 75: INSERT INTO tbProduto(ProdutoNome, ProdutoValor)
 76: VALUES ( NEWID(), CONVERT(DECIMAL(5,2),(RAND()*100)+1,2)  )
 77: GO 100
 78: 
 79: -- 1.000 Pedidos
 80: WITH Clientes AS
 81: ( SELECT ClienteID
 82: , CONVERT(DATE, DATEADD(DAY,RAND()*100,
 83:         CONVERT(DATETIME,ClienteDataCadastro))) [ClienteDataCadastro]
 84: FROM tbCliente
 85: WHERE ClienteID = CONVERT(INT,(RAND()*10000)+1) )
 86: INSERT INTO tbPedido(ClienteID,PedidoData)
 87: SELECT ClienteID, ClienteDataCadastro FROM Clientes
 88: GO 1000
 89: 
 90: -- 2.500 Itens nos Pedidos
 91: INSERT INTO tbPedidoItem(PedidoID, ProdutoID)
 92: SELECT (SELECT CONVERT(INT,RAND()*MAX(PedidoID)-1) FROM tbPedido)
 93:       ,(SELECT CONVERT(INT,RAND()*MAX(ProdutoID)-1) FROM tbProduto)
 94: GO 2500
 95: 
 96: -- Itens de Pagamento
 97: INSERT INTO tbPagamento (ClienteID, PedidoID, PagamentoValor, PagamentoData)
 98: SELECT PE.ClienteID, PE.PedidoID, SUM(PR.ProdutoValor)
 99:  , CONVERT(DATE, DATEADD(DAY,RAND()*5
 100:  , CONVERT(DATETIME,PE.PedidoData))) FROM tbPedido PE
 101: INNER JOIN tbPedidoItem PI ON PE.PedidoID = PI.PedidoID
 102: INNER JOIN tbProduto PR ON PR.ProdutoID = PI.ProdutoID
 103: GROUP BY PE.ClienteID, PE.PedidoID
 104:  , CONVERT(DATE, DATEADD(DAY,RAND()*5, CONVERT(DATETIME,PE.PedidoData)))
 105: 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.

image

 1: CREATEDATABASE mercadinhoOLAP

 2: GO
 3: 
 4: USE mercadinhoOLAP
 5: GO
 6: 
 7: /************************/
 8: /* CRIAÇÃO DAS TABELAS  */
 9: /************************/
 10: 
 11: 
 12: CREATE TABLE DimCliente
 13: ( ClienteID INT PRIMARY KEY
 14: , ClienteNome VARCHAR(50)
 15: , ClienteDataCadastro DATE)
 16: GO
 17: 
 18: CREATE TABLE DimPagamento
 19: ( PagamentoID INT PRIMARY KEY
 20: , PedidoID INT
 21: , PagamentoValor DECIMAL(8,2))
 22: GO
 23: 
 24: CREATE TABLE DimPedido
 25: ( PedidoID INT PRIMARY KEY
 26: , PedidoData DATE
 27: , ClienteID INT)
 28: GO
 29: 
 30: CREATE TABLE DimProduto
 31: ( ProdutoID INT PRIMARY KEY
 32: , ProdutoNome VARCHAR(50))
 33: GO
 34: 
 35: CREATE TABLE DimPedidoItem
 36: ( PedidoItemID INT
 37: , PedidoID INT
 38: , ProdutoID INT)
 39: GO
 40: 
 41: CREATE TABLE FatFaturamento
 42: ( ClienteID INT
 43: , PagamentoID INT
 44: , PedidoID INT
 45: , ProdutoID INT
 46: , TotalFaturamento decimal(10,2))
 47: GO
 48: 
 49: ALTER TABLE FatFaturamento  WITH CHECK ADD  CONSTRAINT FK_FatFaturamento_DimCliente
 50: FOREIGN KEY(ClienteID) REFERENCES DimCliente (ClienteID)
 51: GO
 52: 
 53: ALTER TABLE FatFaturamento  WITH CHECK ADD  CONSTRAINT FK_FatFaturamento_DimPagamento
 54: FOREIGN KEY(PagamentoID) REFERENCES DimPagamento (PagamentoID)
 55: GO
 56: 
 57: ALTER TABLE FatFaturamento  WITH CHECK ADD  CONSTRAINT FK_FatFaturamento_DimPedido
 58: FOREIGN KEY(PedidoID) REFERENCES DimPedido (PedidoID)
 59: GO
 60: 
 61: ALTER TABLE FatFaturamento  WITH CHECK ADD  CONSTRAINT FK_FatFaturamento_DimProduto
 62: FOREIGN KEY(ProdutoID) REFERENCES DimProduto (ProdutoID)
 63: GO
 64: 
 65: /**********************************/
 66: /*    INSERT DADOS ALEATÓRIOS     */
 67: /**********************************/
 68: 
 69: INSERT INTO DimCliente (ClienteID, ClienteNome, ClienteDataCadastro)
 70: SELECT ClienteID, ClienteNome, ClienteDataCadastro FROM mercadinhoOLTP.dbo.tbCliente
 71: GO
 72: 
 73: INSERT INTO DimPagamento (PagamentoID, PedidoID, PagamentoValor)
 74: SELECT PagamentoID, PedidoID, PagamentoValor FROM mercadinhoOLTP.dbo.tbPagamento
 75: GO
 76: 
 77: INSERT INTO DimPedido (PedidoID, PedidoData, ClienteID)
 78: SELECT PedidoID, PedidoData, ClienteID FROM mercadinhoOLTP.dbo.tbPedido
 79: GO
 80: 
 81: INSERT INTO DimProduto (ProdutoID, ProdutoNome)
 82: SELECT ProdutoID, ProdutoNome FROM mercadinhoOLTP.dbo.tbProduto
 83: GO
 84: 
 85: INSERT INTO DimPedidoItem (PedidoItemID, PedidoID, ProdutoID)
 86: SELECT PedidoItemID, PedidoID, ProdutoID FROM mercadinhoOLTP.dbo.tbPedidoItem
 87: GO
 88: 
 89: INSERT INTO FatFaturamento (ClienteID, PagamentoID, PedidoID, ProdutoID, TotalFaturamento)
 90: SELECT CL.ClienteID, PG.PagamentoID, PE.PedidoID, PR.ProdutoID, PG.PagamentoValor
 91: FROM DimPedido PE LEFT JOIN  DimPagamento PG
 92:         ON PE.ClienteID = PG.PedidoID
 93:     LEFT JOIN DimCliente CL
 94:         ON PE.ClienteID = CL.ClienteID
 95:     LEFT JOIN DimPedidoItem PI
 96:         ON PE.PedidoID = PI.PedidoID
 97:     LEFT JOIN DimProduto PR
 98:         ON PI.ProdutoID = PR.ProdutoID
 99: GROUP BY CL.ClienteID, PG.PagamentoID, PE.PedidoID, PR.ProdutoID, PG.PagamentoValor
 100: GO
 101: 
 102: 
 103: 
 104: /**********************************/
 105: /*     CRIAÇÃO DOS ÍNDICES        */
 106: /**********************************/
 107: 
 108: CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CSI_Cliente] ON [dbo].[DimCliente]
 109: (
 110:     [CLienteID]
 111: )WITH (DROP_EXISTING = OFF) ON [PRIMARY]
 112: GO
 113: 
 114: CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CSI_Pagamento] ON [dbo].[DimPagamento]
 115: (
 116:     [PagamentoID]
 117: )WITH (DROP_EXISTING = OFF) ON [PRIMARY]
 118: GO
 119: 
 120: CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CSI_Pedido] ON [dbo].[DimPedido]
 121: (
 122:     [PedidoID]
 123: )WITH (DROP_EXISTING = OFF) ON [PRIMARY]
 124: GO
 125: 
 126: CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CSI_Produto] ON [dbo].[DimProduto]
 127: (
 128:     [ProdutoID]
 129: )WITH (DROP_EXISTING = OFF) ON [PRIMARY]
 130: GO
 131: 
 132: CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CSI_Faturamento] ON [dbo].[FatFaturamento]
 133: (
 134:     ClienteID, PagamentoID, PedidoID, ProdutoID
 135: )WITH (DROP_EXISTING = OFF) ON [PRIMARY]
 136: GO

 

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.

Compartilhe o post:
RSS
Follow by Email
Facebook
YOUTUBE
YOUTUBE
LinkedIn

Comentários

comments