ETL – Extração, Transformação e Carga de Dados

Uma Etapa crítica do Data Warehouse!

Fase extremamente critica de um Data Warehouse,  pois envolve a movimentação dos dados de origem nos sistemas transacionais e/ou sistemas legados, obedecendo as regras de negócio. A mesma se dá basicamente em três passos, extração (E), transformação (T)  e carga (L – Loader) dos dados, esses são os mais trabalhosos, complexos e também muito detalhados, embora tenhamos várias ferramentas (falaremos mais abaixo) que nos auxiliam na execução desse trabalho.

O primeiro passo a ser tomado no processo de ETL:  a definição das fontes de dados e fazer a extração deles.

  • As origens deles podem ser várias e também em diferentes formatos, onde poderemos encontrar desde os sistemas transacionais das empresas até planilhas, flat files (arquivos textos) , dados do Mainframe, etc.

 O segundo passo:  transformar e limpar esses dados. Mas, o que, afinal de contas, é isso?

  • É muito comum, na obtenção dos dados que, no mais das vezes, são antigos e desconhecidos, encontrarmos muito ‘lixo’ e inconsistências. Por exemplo. Quando um vendedor de linhas telefônicas for executar uma venda, ou inscrição, ele está preocupado em vender, e não na qualidade dos dados que está inserindo na base, então se por acaso o cliente não tiver o número do CPF a mão, ele cadastra um número qualquer, desde que o sistema aceite, um dos mais utilizados é o 999999999-99. Agora imagine um diretor de uma companhia telefônica consultar o seu Data Warehouse (DW) para ver quais são os seus maiores clientes, e aparecer em primeiro lugar o cliente que tem o CPF 999999999-99 ? Seria no mínimo estranho. Por isso, nessa fase do DW, fazemos a limpeza desses dados, para haver compatibilidade entre eles.
  • Além da limpeza, temos de fazer na maioria das vezes uma transformação, pois os dados provêm de vários sistemas, e por isso, geralmente uma mesma informação tem diferentes formatos, por exemplo: Em alguns sistemas a informação sobre o sexo do cliente pode estar armazenada no seguinte formato : “M” para Masculino e “F” para Feminino, porém em algum outro sistema está guardado como “H” para Masculino e “M” para Feminino, em outro ainda, podemos encontrar “1” para Masculino e “2” para Feminino,  e assim sucessivamente. Quando levamos esses dados para o DW, deve-se ter uma padronização deles, ou seja, quando o usuário for consultar o DW, ele não pode ver informações iguais em formatos diferentes. Assim sendo, quando fazemos o processo de ETL, transformamos esses dados e deixamos num formato uniforme sugerido pelo próprio usuário, como por exemplo “M” para Masculino e “F” para Feminino. No DW, teremos somente M e F, fato esse que facilitará a análise dos dados que serão recuperados pela ferramenta OLAP.

Além desses exemplos acima, nós podemos integrar todas fontes de dados num único banco. Com isso não existirão mais “ilhas” de dados, mas sim teremos informações ricas e totalmente integradas.

Como o volume de dados é muito grande, há muitos casos que não temos condições de processar as extrações e transformações na janela de tempo em que o DW não está sendo usado, então temos de fazer uso do que chamamos de staging área para conseguirmos executar os processos com sucesso. Segundo Kimball, a Staging Area é  parte do Data Warehouse responsável por receber a extração, transformação e carga (ETL) das informações dos sistemas transacionais legados, para posterior geração dos Data Marts de destino, com as características:

  • A Staging Area é considerada área fora do acesso dos usuários.
  • A Staging Area não deve suportar queries dos Usuários.
  • Ela pode ser composta por flat files (arquivos textos) ou tabelas de banco de dados na terceira forma normal (normalizadas).

A seguir são apresentados alguns dos fatores que devem ser analisados antes de começar a fase de extração dos dados:

  • A extração de dados do ambiente operacional para o ambiente de data warehouse demanda uma mudança na tecnologia. Os dados são transferidos de bancos de dados hierárquicos, tal como o adabas, ou de bases do grande porte, como o DB2, para uma nova estrutura de SGBD relacional para Data Warehouse, tal como o DB2 UDB, Oracle, Teradata e etc;
  • A seleção de dados do ambiente operacional pode ser muito complexa, pois muitas vezes é necessário selecionar vários campos de um sistema transacional para compor um único campo no data warehouse;
  • Outro fator que deve ser levado em conta é que dificilmente há o modelo de dados dos sistemas antigos, e se existem não estão documentados;
  • Os dados são reformatados. Por exemplo: um campo data do sistema operacional do tipo DD/MM/AAAA pode ser passado para o outro sistema do tipo ano e mês como AAAA/MM;
  • Quando há vários arquivos de entrada, a escolha das chaves deve ser feita antes que os arquivos sejam intercalados. Isso significa que se diferentes estruturas de chaves são usados nos diferentes arquivos de entrada, então deve-se optar por apenas uma dessas estruturas;
  • Os arquivos devem ser gerados obedecendo a mesma ordem das colunas estipuladas no ambiente de data warehouse;
  • Podem haver vários resultados. Dados podem ser produzidos em diferentes níveis de resumo pelo mesmo programa de geração das cargas;
  • Valores default devem ser fornecidos. As vezes pode existir um campo no data warehouse que não possui fonte de dados, então a solução é definir um valor padrão para estes campos.

O data warehouse espelha as informações históricas necessárias, enquanto o ambiente operacional focaliza as informações pontuais correntes.  A parte de carga dos dados também possui uma enorme complexidade, e os seguintes fatores devem ser levados em conta:

  • A parte de Integridade dos dados. No momento da carga é necessário checar os campos que são chaves estrangeiras com suas respectivas tabelas para certificar-se de que os dados existentes na tabela da chave estrangeira estão de acordo com a tabela da chave primária;
  • Se a tabela deve receber uma carga incremental ou a carga por cima dos dados. A carga incremental normalmente é feita para tabelas fatos e a carga por cima dos dados é feita em tabelas dimensões onde o analista terá que deletar os dados existentes e incluí-los novamente. Mas em alguns casos poderá acontecer que as tabelas de dimensões tem de manter o histórico, então o mesmo deverá ser mantido (slowly change dimension);

Apesar de existirem ferramentas de ETL como o DTS (Data Transformation Service), Data Stage, ETI, Business Objects Data Integration, Sunopsis – atual Oracle Data Integrator, Oracle Warehouse Builder e o Informática, ainda tem-se a necessidade de criar rotinas de carga para atender determinadas situações que poderão ocorrer. Pode ser em shell script, SQL puro ou em C, quando precisa de performance.

As ferramentas de ETL mais utilizadas no mercado são o Data Stage da IBM, o ETI da ETI Corporation, Sagent da Group 1 Software, Informática Power Center da Informática, DTS da Microsoft, Pentaho (Open Source), Talend (Open Source), Sunopsis e o Oracle Warehouse Builder da Oracle. Todos tem os seus diferenciais e cada um poderá ser utilizado dependendo do caso de cada empresa. Algumas ferramentas tem a curva de aprendizado mais suave, outras um pouco mais íngrime, mas em certos casos mesmo sendo uma ferramenta de difícil aprendizado exigindo maiores investimentos em pessoal, serão compensados com a performance e flexibilidade da mesma. Há outras ferramentas que tem custo zero de aquisição pois, vem embutida junto com um SGBD (Sistema Gerenciador de Banco de Dados), mas em contrapartida são bastante limitadas no tocante a extração de dados e exigem uma maior codificação dos processos de ETL, em relação as outras e por fim tem as open source que, dependendo do porte do projeto, podem atender satisfatoriamente.

O que vale dizer é que uma ferramenta de ETL tem grande valia, principalmente se os sistemas OLTP (transacionais) são muitos, pois elas são uma poderosa fonte de geração de metadados, e que contribuirão muito para a produtividade da sua equipe, porém deve-se tomar muito cuidado ao escolhe-la. Seja minucioso, teste o máximo de ferramentas que puder e veja qual é a mais adequada ao seu caso, pois elas exigem um alto investimento, tanto em capacitação, quanto na própria aquisição. Em alguns casos é interessante o auxílio de profissionais externos para a escolha. O fato verdadeiro é que os benefícios serão bastante vistosos e a produtividade aumentará consideravelmente.

Até a próxima!

28 Respostas to “ETL – Extração, Transformação e Carga de Dados”

  1. Marcelo Lemos Says:

    Fantástica sua linguagem para explicar o que é ETL. De forma simples e prática explicou o que eu já tinha lido em textos acadêmicos e não tinha chegado a uma conclusão tão clara qto a que tenho agora.
    Parabéns pelo blog.

  2. Os números de 2010 « Blog do Lito – Data Warehouse / Business Intelligence Says:

    […] ETL – Extração, Transformação e Carga de Dados janeiro, 2010 2 comentários 3 […]

  3. Marcos Says:

    Muito bom este post mesmo…..linguagem rica em exemplos e didática!!

    só um detalhe: a ferramenta da Informatica é Power Center, ao invés de ‘Conect’ como descrito….

    Abcos!
    Marcos

  4. Paulo Cesar F.Cardoso Says:

    Gostaria de obter apostilas ou manuais para estudar e enter o aplicativo acima, ferramentas de ETL como o DTS (Data Transformation Service), Data Stage, ETI, Business Objects Data Integration, Sunopsis – atual Oracle Data Integrator, Oracle Warehouse Builder e o Informática, Grato

  5. Rafael Says:

    Excelente post, muito explicativo, Paulo, com relação a apostilas de ferramentas, eu trabalho com ETI e tenho uma apostila deste, se deseja recebe-la, entre em contato por favor.

    Obrigado.

  6. Regis Says:

    Olá. Eu estou procurando algum material que fale sobre ferramentas para extrair dados de mainframes, e de que forma é feita a extração. Você dispoe de algum material neste sentido? Abraço. Régis

    • Marcelo Almeida Says:

      Olá Régis, a forma como é feita a extração de dados do mainframe depende muito da política de segurança de cada empresa, em alguns casos são criados programas COBOL que fazem a extração e geram arquivos, então, estes aquivos são transportados para a baixa plataforma onde as ferramentas de ETL fazem a leitura e carga destes arquivos. Já em outros lugares, as próprias ferramentas de ETL acessam e extraem os dados diretamente das bases do mainfraime. Um exemplo de ferramenta que faz o acesso direto é o DataStage, para o DataStage a leitura da base de dados do maiframe ocorre naturalmente, pois para ele (DataStage) é apenas mais uma base de dados DB2.

    • Carlos Alberto Lorenzi Lima Says:

      Regis, tudo bem?

      Desculpe a demora, mas regressei nesse fim de semana de uma viagem a trabalho…

      Por sorte, o Marcelo deu ótimas dicas pra você!!! Acrescento apenas, que o ODI da Oracle também faz a conexão com Mainframes e DB2. Mas a opção mais usada, é da geração de arquivos TXT, como comenta o Marcelo.

      Abs.

      Lito

  7. Paulo Says:

    Caro Lito,
    Qual o seu sentimento sobre o SSIS?

    • Carlos Alberto Lorenzi Lima Says:

      Paulo, meu velho,

      Confesso que não tenho prática nas ferramentas da Microsoft… Mas as informações que recebo a respeito são excelentes!!!

      Pra quem não sabe:
      O Designer SSIS é uma ferramenta gráfica que pode ser usada para criar e manter pacotes do Integration Services. O Designer SSIS está disponível no SSDT (SQL Server Data Tools) como parte de um projeto do Integration Services.

      Abração!!!!

      Lito

  8. Melissa Adimari Says:

    Lito, Gostei muito da sua matéria.
    Preciso de ajuda, o modulo designer está dando erro ao abrir, ele diz não encontrar o repositório e fecha a aplicação. Você saberia me dizer, elucidar alguma coisa sobre isso?

    Abs,

    Melissa

    • Carlos Alberto Lorenzi Lima Says:

      Melissa, tudo bem?

      Pode ser que, na conexão com o ODI o nome do repositório de work tenha sido perdido (tenha apagado) entra pra editar a conexão com o ODI e verifica se o nome do repositório de Work esta correto… Este é o primeiro passo….
      Abs.
      Lito

      • adimari Says:

        Na conexão o nome do repositório está correto, testei e ok, mas quando vai logar ele mostra a mensagem: O designer não pode se conectar com um repositório de execução.

      • Carlos Alberto Lorenzi Lima Says:

        Melissa,

        Lendo melhor a sua duvida, não que eu não tivesse lido antes, hehehe, mas você esta dizendo REPOSITÓRIO DE EXECUÇÃO… ou seja, você criou um repositório do tipo RUN TIME. Pois bem, o Design só se conecta com repositórios do tipo DESENVOLVIMENTO e semelhantes, não se conecta com repositórios do Tipo Run Time ou execução, pois supõe-se que esses repositórios estão em produção e em produção não se pode alterar nada!!!!
        Quaisquer alterações devem ser feitas em DESENVOLVIMENTO e depois exportadas para a PRODUÇÃO.
        Entendi certo?
        Abs.
        Lito

  9. adimari Says:

    Oi Lito, gostei da sua matéria, eu estava pesquisando com meu chefe ao lado, e ele disse que conhece você, o Martin.
    Preciso de ajuda no ODI, modulo designer, ele não abre, aparece mensagem que não encontrou o repositório e fecha.
    Não encontrei nada sobre.

    Att,

    Melissa

  10. adimari Says:

    Eis o problema,
    Eu peguei isso aqui já configurado, pelo antigo desenvolvedor, e qdo ele saiu começou a dar esse erro e antes ele acessava, pois fiquei uns dias ao lado dele antes de partir. Isso mesmo Quaisquer alterações são ser feitas em DESENVOLVIMENTO e depois exportadas para a PRODUÇÃO.

    • Carlos Alberto Lorenzi Lima Says:

      Então, Melissa, eu duvido que ele estivesse acessando o repositório de execução, pois nesse repositório não são criados os metadados para o Design… Talvez ele estivesse usando a mesma conexão para acessar o desenvolvimento, mudando o nome do repositório na conexão. Se, nessa mesma conexão você colocar o nome do repositório de desenvolvimento, TALVEZ funcione… pode ser que ambos estejam no mesmo endereço IP… faça um teste…
      Abs.
      Lito

  11. Vanessa Speranza Says:

    Sabe me dizer onde fazer o curso?

  12. Emerson Says:

    Muito bom artigo. Na empresa em que trabalho utiliza-se para fazer a ETL o aplicativo desse site é bem fácil de utilizar. http://www.fullcopyconvert.com.br

  13. Anderson Says:

    Cara você tem algum tutorial em português utilizando o Oracle Warehouse Builder? Fazendo Integração de Dados, utilizando ETL..?

  14. eduardolbarbosa Says:

    Republicou isso em Eduardo Lemes Barbosae comentado:
    Excelente Materia!!


Deixe uma resposta

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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s

%d blogueiros gostam disto: