ETL e integrao de dados Prof. Mrcio Fuckner

Mapa do Mdulo

SemanaAssuntoProfessorTemas Abordados1 Data WarehouseGilmarTeoria: Definio de DW, OLAP, OLTP, Data Mart, Data Mining, Arquitetura. 2Data WarehouseGilmarModelagem Multidimensional: Fatos, Dimenses e Medidas, Star Schema e Snowflake Schema; Implementaes OLAP: MOLAP, ROLAP e HOLAP; Tcnicas: Drill Down, Drill Up, Slice and Dice3BIMrcioConceitos e Infra-estrutura de um BI4BIMrcioPrtica: Modelagem Multidimensional e Design de um Datawarehouse5BIMrcioData Mining6BIMrcioPrtica: ETL e Integrao de Dados

ContedoIntegrao de DadosAmbiente Operacional BIDefinio de ETLAtividades ETL

Integrao de DadosProcessos que tem por objetivo reunir dados de diferentes origens em uma fonte de dados consolidada.

Uma atividade que requer conhecimento acerca de diferentes tecnologias de integrao e transformao de dados.

um passo essencial para viabilizar um ambiente operacional de Business Intelligence.

Ambiente Operacional do BIEXTRAO DE DADOS OPERACIONAISDATA WAREHOUSEBUSINESS INTELLIGENCEConverses e organizao de dadosExtrado de: http://www.slideshare.net/fabiolagrijo/aplicao-de-business-intelligence-em-laboratrios-clnicos-como-ferramenta-para-tomada-de-deciso

O que ETL?Extrao (Extraction)Obteno de dados a partir de diferentes origens. Um exemplo tpico a obteno e carga de todos os clientes adicionados ou alterados desde a ltima carga de dados.Transformao (Transformation)Alterao da estrutura original dos dados para se adequar a estrutura do data warehouse modelado.Um exemplo pode ser realizar a busca dos nomes de cidades e estados a partir de cdigos Carga (Load)Armazenar os dados no data warehouse

Atividades ETL (1 de 3)Extrao

Captura de dados alteradosNa maioria dos casos a atividade de extrao deve se limitar a poro de dados alterados ou includos desde a ultima extrao. Atividade conhecida na literatura como Change Data Capture.

Data StagingNem sempre ser possvel transformar imediatamente os dados extrados. Muitas vezes a extrao armazenada em uma rea temporria para futuramente passar pela transformao.A rea de Data Staging em um DW em geral implementado em bancos de dados relacionais e servem apenas como um buffer entre o sistema origem e o DW.

Atividades ETL (2 de 3)TransformaoValidao de DadosVerificao de integridade de dados e filtragem de dados invlidos.Limpeza de DadosCorreo de dados invlidos.Decodificao e AlteraoTransformar dados operacionais, chaves estrangeiras em dados humanamente legveis.AgregaoRealizar sumarizaes e clculos prvios.Gerenciamento de ChavesNovas linhas em tabelas de dimenso recebem chaves surrogadas (surrogate keys). Nesta fase ocorre a gerao e o gerenciamento dessas chaves

Atividades ETL (3 de 3)Carga

Carga de dados em tabelas de fatosEm geral as tabelas de fatos recebem novos dados e raramente sofrem atualizaes.

Carga de dados em tabelas de dimensesAs dimenses podem receber novos dados assim como receber atualizaes frequentes.

Processo de ExtraoAlgumas estratgias de captura de dados:

Recuperao de dados alterados a partir de eventos naturais nas bases de dados. Por exemplo, a data de emisso de uma nota fiscal, a data de alterao de um cliente ou a data de registro de inventrio de um produto.

Usando as chaves sequenciais dos sistemas de origem

Alimentando tabelas de transio a partir de triggers. Em alguns sistemas possvel instalarmos triggers.

Leitura da log dos bancos de dados.

Ferramentas de ETLAlgumas ferramentas usadas para captura de dados:

Uso de programas batch para processar dados, gerar arquivos e realizar a carga.

Uso de stored procedures.

Uso de ferramentas de replicao de dados.

Uso de ferramentas de ETL.

Combinao de diversas tcnicas.

Processo de Extrao Surrogate Keys uma boa prtica de design em DWConsiste na criao de uma chave tcnica, tipicamente implementada por um campo numrico (integer)Melhor desempenho se comparado com chaves complexas tpicas de bases de dados operacionaisPermite mudana de estratgias de carga sem comprometer o modelo de dados.

LimitaesNecessrio criar mecanismos de transformao de chave (de/para) durante o ETL. No chega a ser complexo uma vez que a grande maioria das ferramentas ETL implementam essas transformaes.

Processo de ExtraoEm geral o processo de extrao tem por objetivo alimentar tabelas de dimenses ou fatosExtrair fatos uma tarefa relativamente simples, uma vez que esse tipo de tabela apenas alimentada com novas informaes e raramente sofre atualizaesPor outro lado, as dimenses sofrem pequenas adies e atualizaes, raramente remoes.Ralph Kimball prope um pacote de estratgias para atualizao de dimensesEste pacote de estratgia chamam-se Slowly Changing Dimensions ou resumidamente SCD.

SCD 0Nesta estratgia no h atualizao. A dimenso possui um conjunto estvel de dados.Quando uma carga ocorre, os dados so prviamente truncados

SCD 1Nesta estratgia todo dado antigo substitudo por dados novosOs dados histricos no so armazenados

Atualizao na mesma tupla. Sem histrico

IDNomeCidade01Joaquim CruzFlorianpolis

IDNomeCidade01Joaquim CruzCuritiba

SCD 2Nesta estratgia, os dados histricos so mantidos.Colunas do tipo data e hora so usados para identificar a vignciaColunas que indicam qual o registro corrente tambm so usados em geral.

IdNomeCidadeInic VigFim VigAtual1Joaquim CruzFlorianpolis01/01/000110/05/201001Joaquim CruzCuritiba11/05/201012/05/201001Joaquim CruzSo Paulo13/05/201031/12/99991

SCD 3 um meio termo entre SCD 1 e SCD 2Consiste em criar uma coluna para armazenar o dado anterior.Atualizao na mesma tupla. Histrico anterior apenas.

IDNomeCidadeCidade_ant01Joaquim CruzFlorianpolisnull

IDNomeCidadeCidade_ant01Joaquim CruzCuritibaFlorianpolis

ResumoExistem outras variaes de SCD disponveis na literaturaAs estratgias no precisam ser necessriamente implementadas em isolamento, sendo comum implement-las em conjunto. Exemplo: No interessa ao usurio se um cliente alterou seu CPF, mas importante saber se ele mudou a categoria.Para saber mais, consulte o livro The Datawarehouse Tookit,

Exerccio Prtico - ObjetivosO objetivo deste exerccio prtico :

Apresentar o processo de migrao de um modelo de dados operacional para um modelo de dados multidimensional

Preparar as etapas de extrao, transformao e carga usando uma ferramenta de ETL

Criar uma consulta OLAP para o modelo gerado.

Exerccio Prtico Modelo de DadosModelo de dados base do exerccio

Exerccio - PreparaoUsando um cliente SQL, execute os scripts abaixo:criar_base_operacional.sqlpopular_base_operacional.sql

Estes scripts criaro as tabelas, ndices e sequncias do banco de dados operacional, assim como popularo o banco de dados com exemplos.

Exerccio Modelo Estrela Modelo de dados estrela gerado

Exerccio Prtico - PreparaoUsando um cliente SQL, execute o script abaixo:criar_base_estrela.sql

Este script criar as tabelas, ndices e sequncias do banco de dados que sero usados nas consultas BI.

Exerccio Prtico Ferramenta de ETLUsaremos a ferramenta de integrao de dados do Pentaho Data Integration chamada Spoon para realizar as transformaes.

Instrues de Instalao:Descompacte o arquivo pdi-ce-3.2.0-stable.zip em uma pasta.Execute o arquivo Spoon.bat.A tela de boas-vindas ser apresentada.Clique em No repository. No usaremos um repositrio central.

Exerccio - PlanejamentoUma vez que o modelo estrela foi criado, a prxima etapa preparar os scripts ETL que sero executados periodicamente para alimentar o modelo estrela.

Sero criados os seguintes processos ETL:

Extrao e carga da dimenso clienteExtrao e carga da dimenso produtoExtrao e carga da dimenso dataExtrao e carga do fato emprstimos

Exerccio - Dimenso clienteA estratgia utilizada para atualizar a dimenso cliente foi SCD tipo 1. Foi criada uma surrogate key para esta tabela com o nome id. A chave primria da tabela foi armazenada no atributo id_origFoi criado o atributo dt_atualiz para armazenar a data e hora da ltima atualizao

Exerccio - Dimenso produtoA dimenso produto foi desnormalizada para armazenar o tipo do filme, sendo que a origem do dado um join entre as tabelas dvds e tipos_dvds.A estratgia utilizada para atualizar a dimenso produto foi SCD tipo 1. Foi criada uma surrogate key para esta tabela com o nome id. A chave primria da tabela foi armazenada no atributo id_origFoi criado o atributo dt_atualiz para armazenar a data e hora da ltima atualizao

Exerccio - Dimenso dataA dimenso data comum em diversos modelos multidimensionaisA carga de dimenses do tipo data em geral realizada a partir de stored procedures que geram os calendrios periodicamente.Este tipo de tabela contem informaes desnormalizadas de datas para facilitar a consulta hierrquica permitindo por exemplo visualizar meses por extenso, dias da semana, semestres, bimestres, feriados, etc.Neste cenrio, as datas sero carregadas a partir de uma planilha do Excel.

Exerccio - Fato de emprstimosTabelas de fatos em geral sofrem apenas incluses e rarissimas atualizaes. Este caso especfico sofrer atualizaes uma vez que a carga pode recuperar emprstimos em andamento. Estes por sua vez, aps a sua devoluo devero ser atualizados no DW.A ateno especial fica por conta da necessidade de transformao da chave original dos dados de cliente, produto e data nas chaves surrogate

Exerccio - FinalizaoCrie uma consulta OLAP do modelo proposto usando o fato de emprstimos e as dimenses de data de emprstimo, data de devoluo, cliente e produto.

RefernciasPentaho Solutions, Ed. Wiley & SonsRoland Bouman e Jos Van DogenThe Datawarehouse ToolkitRalph Kimball