ODI Series - Treinamento

ODIORACLE DATA INTEGRATOR

Caio Lima – [email protected]

Agenda

Oracle Oracle Data Integrator Estudo de Caso Inicializando ODI Limpeza dos Dados Transformação dos Dados Interface de Integração dos Dados Automatização Conclusão Referências

Oracle

Edições dos DBs Oracle SE1 – Standard Edition One SE – Standard Edition EE – Enterprise Edition PE – Personal Edition XE – Express Edition

Obs: Apenas a edição EE possui os atributos necessários para a utilização plena de Data Warehouse.

O que esperar de um ETL

Extrair dados de diversas fontes e plataformas Alta performance e processamento distribuído Alta escalabilidade Compactação de dados e criptografia Manipular todos os tipos de dados Repositório de metadados Scheduling / Cargas Event-Driven Interface gráfica de desenvolvimento Arquitetura aberta Geração / Reengenharia de códigos

Oracle Data Integrator

Cria, implanta e gerencia Data Warehouses complexos;

Automatizar a migração e a movimentação dos dados em lotes;

Assegura que as informações sejam oportunas, precisas e consistentes entre sistemas complexos;

Plataforma de integração de dados entre tecnologias diferentes ou iguais;


Transforma dados, permitindo escrever regras de negócios de fácil desenvolvimento e implantação;

Possui repositório centralizado dos modelos de dados a serem lidos, transformados e gravados;

O ODI é desenvolvido em linguagem Java, utiliza arquitetura E-LT (Extract, Load and Transform) e foi adquirido em 2006 da empresa francesa SUNOPSIS.

Reduz o tempo de implementação devido a suas ferramentas de alta produtividade para desenho;


No desenvolvimento declarativo o desenvolvedor simplesmente descreve as fontes, os destinos e o processo de transformação, focando seus esforços apenas no “O QUE FAZER” e não no “COMO FAZER”;

O ODI-EE decompõe a solicitação em todos os passos necessários, que podem ser facilmente auditados na ferramenta de monitoramento do processamento.


Produtos ETL da suite Oracle Data Integrator (ODI) Oracle Data Integration Enterprise Edition Oracle Data Quality and Profiling Oracle Changed Data Capture (CDC)


Flexibilidade SGBD, XML, File, Excel e muitos outros Tratamento para módulos de aplicações

comerciais existentes

Alta Performance E-LT – Extract, Load and Transform Changed Data Capture


Controle de restrições e consistência Geração de regras de integridade

Permite a verificação do fluxo de dados processados pelas interfaces Isolamento de erros e/ou reciclagem

Identifica falta de entrada de dados


Interoperabilidade Suporta todos os padrões Java e SOA Hot-Plugabble (Knowledge Modules)

Produtividade Declarative Design Knowledge Modules

Arquitetura ETL vs. E-LT

Arquitetura Convencional ETL ETL: Realiza as transformações em um servidor central Problemas de Escalabilidade Alto custo

Arquitetura E-LT E-LT: Realiza as transformações nos RDBMS existentes (Origem ou

Destino) Recursos nativos Eficiência e Escalabilidade

Benefícios Desempenho Escalabilidade Produtividade na Administração Baixo Custo

Arquitetura ETL vs. E-LT

Extraction Extrai e executa parte da

transformação utilizando a engine do DB source. Em seguida, carrega na memória do servidor de extração.

Load Carrega os dados já

transformados do DW. Transformation

Para cada tupla carregada, a engine do ETL finaliza o processo de transformação.

Módulos Gráficos Operacionais

Repository

Designer Navigator:Reverse EngineeringDevelop ProjectsRelease Scenario

Operator Navigator:Operate ProductionMonitor sessions

Operator Navigator:Define the infrastructure of the IS.

Security Navigator:Manage user privileges.

Repositórios

Master Repository Estrutura de dados contendo informações

sobre a topologia dos recursos de TI da empresa, segurança, gerenciamento de versões de projetos e modelos de dados.

Work Repository Estrutura de dados contendo informações

sobre os modelos de dados, projetos e cenários.

Para cada master, deve ter pelo menos um work. Não existe work sem master.

Repositórios – Visão Geral

Cria e arquiva versões de modelos, projetos e cenários. Importa versões

de modelos, projetos e cenários para teste.

Importa versões testadas dos cenários para produção.

Work Repository(Desenvolvimento)

Work Repository(Teste e Qualidade)

Execution Repository(Produção)

Master Repository

Diferenciais

Declarative Design e Knowledge Modules

Ambiente virtual de desenvolvimento Declarative Design

“O que” será extraído e transformado.

Knowledge Modules (Templates) “Como” essas operações deverão ser realizadas

face às várias plataformas e bancos de dados envolvidos no processo.

Knowledge Modules

Os KMs são considerados os “corações” do processo de E-LT no ODI. São código templates que contém a sequência dos comandos necessários para realizar as operações de E-LT do ODI.

Knowledge Modules

RKM – Reverse Knowledge Module (Engenharia Reversa): é o responsável por fazer uma reserva “customizada” dos armazenamentos de dados no ODI. Por exemplo: se existir uma situação em que se necessite fazer algum tipo de procedimento extra ao reverter um modelo de dados, podemos utilizar RKMs específicos e não o padrão para esta tarefa. O ODI faz reservas de tabelas automaticamente, mas podemos customizar estas reservas com um RKM;

JKM – Journalizing Knowledge Module (Documentação): é o responsável por fazer a jornalização de dados quando se trabalha com este tipo de conceito. Pode ser usado, por exemplo, para se fazer replicação de bancos de dados;

Knowledge Modules

LKM – Load Knowledge Module (Carga): é o responsável por carregar os dados das tabelas de origens no nosso processo de ETL quando estas tabelas se encontram em servidores de dados (Data Servers) diferentes;

CKM – Check Knowledge Module (Verificação): é o responsável por realizar as validações dos dados no processo de ETL. No ODI podemos criar check constraints próprias contendo alguma regra de negócio (por exemplo, valor não pode ser negativo) ou podemos validar FKs de banco antes de inserir os dados na tabela de destino, ou ainda, durante o próprio processo de ETL, podemos verificar dados NOT NULL, etc. O CKM é o responsável por executar todas as verificações;

Knowledge Modules

IKM - Integration Knowledge Module (Integração): é o responsável pela integração dos dados efetivamente no banco de destino. Ele resolve as regras do ETL descritas nas interfaces e insere os dados finais na tabela de destino;

SKM - Service Knowledge Modules (Serviço): é utilizado para publicar dados utilizando Web Services. Pode ser utilizado para gerar e manipular dados via Web Services para arquiteturas SOA (Service Oriented Architecture – Arquitetura Orientada a Serviços);

Agents

Em tempo de execução os Agents orquestram a execução dos cenários;

A execução pode ser iniciada a partir das interfaces de usuário, através do Scheduler interno, ou via console operator;

São componentes de software Java, que permitem execuções remotas, divisão de processamento e etc;

Agents

Quando uma execução é concluída o Agent atualiza os logs de execução e as mensagens de erro para relatórios e estatísticas de execução;

Os logs de execução podem ser vistos a partir da interface do operador.

Aplicabilidade

Para movimentação e transformação de dados em qualquer plataforma, seja para processamento em lote (batch), tempo-real, síncrono ou assíncrono;

Com conectores pré-construídos para os principais bancos de dados, e arquitetura orientada a serviços (SOA), o ODI-EE é uma solução extensível atendendo as necessidades atuais e futuras de integração;

Aplicabilidade

Real-time Data Warehousing, suportando três tipos de integração: Baseada em Dados, para processamento

de grandes volumes em lote; Baseada em eventos de dados, através

de avançados recursos de CDC (change data capture) e pooling;

Baseada em serviços, provendo serviços de dados para a Suite SOA da Oracle.

Aplicabilidade

Service-Oriented Architecture (SOA), através de chamadas a serviços externos ou provendo serviços que podem ser integrados à solução SOA, agregando suporte a processamento de grandes volumes, com alta performance ao ambiente SOA;

Master Data Management (MDM), provendo um robusto ambiente de incronização para hubs de dados desenvolvidos internamente, trabalhando conjuntamente em soluções MDM empacotadas ou em soluções híbridas de MDM com processos SOA e BPEL;

Aplicabilidade

Migração de sistemas, provendo processos eficientes de carga de dados históricas, incluindo transformações complexas, de sistemas existentes para novos sistemas, podendo ser utilizado para manter os sistemas sincronizados enquanto co-existirem.

Quando usar o ODI ?

Volume Integração assincrona em decorrência de Data

Warehousing(mini-batch) Integração em Batch com Alto Volume

Transformation Transformações em Database (E-LT)

Integration Topology Data Warehouse Loading (E-LT) JMS to DB/App with bulk transformation (mini-batch) DB/App to DB/App (batch or mini-batch with CDC)

ODI – Estudo de Caso

Administração de Vendas


Aplicação de Vendas – Modelo de Dados


Parâmetros (arquivo texto CSV)


Administração de Vendas – Modelo DW


Limpeza dos Dados Verificar a consistência dos dados das

fontes Restrições de integridade Regras de negócio

Permitir analisar os erros identificados Corrigir erros caso necessário


Limpeza dos Dados Restrições do estudo de caso

Idade dos clientes deve ser maior que 21 anos

Restrição de chave estrangeira:

FK


Limpeza dos Dados Criar as restrições na tabela

SRC_CUSTOMER: Condição: Age > 21 Referência: FK_SRC_CUSTOMER_SRC_CITY

Executar as restrições Verificar o relatório de erros


Transformação dos Dados Quais transformações queremos:

Obter TRG_CUSTOMER.AGE_RANGE a partir de SRC_AGE_GROUP.AGE_RANGE e SRC_CUSTOMER.AGE

idade do cliente(SRC_CUSTOMER – HSQL)

X

faixa de idade(SRC_SALES_PERS – TXT)

faixa de idade do cliente(TRG_CUSTOMER – HSQL)



Obter TRG_CUSTOMER.SALES_PERS de SRC_SALES_PERS.FIRST_NAME e SRC_SALES_PERS.LAST_NAME a partir de SRC_CUSTOMER.SALES_PERS_ID

primeiro e último nome do representante de vendas(SRC_SALES_PERS – TXT)

X

id do representante de vendas

(SRC_CUSTOMER – HSQL)

nome do representante(TRG_CUSTOMER – HSQL)



Transformar o valor numérico (0, 1, 2) presente em SRC_CUSTOMER.DEAR em uma das strings padrão (Mr, Mrs ou Ms) e salvar em TRG_CUSTOMER.DEAR

0, 1, 2(SRC_CUSTOMER – HSQL)

X

0 – Mr1 – Mrs2 - Ms

pronome de tratamento(TRG_CUSTOMER – HSQL)



Obter TRG_CUSTOMER.CUST_NAME a partir de SRC_CUSTOMER.FIRST_NAME e SRC_CUSTOMER.LAST_NAME

primeiro e último nome do cliente

(SRC_CUSTOMER – HSQL)

nome do cliente(TRG_CUSTOMER – HSQL)


Transformação dos Dados






Transformação dos Dados Definindo os JOINS entre as fontes de

dados


Transformação dos Dados Definindo os JOINS entre as fontes de

dados


Transformação dos Dados Definindo as regras de transformação

Definir mapeamentos para os atributos em “Target datastore” que não foram mapeados automaticamente.

CUST_ID: clicar em CUST_ID e arrastar SRC_CUSTOMER.CUSTID para o campo de texto da aba “implementação”.

DEAR: compor a seguinte expressão: CASEWHEN (SRC_CUSTOMER.DEAR = 0, ‘MR’, CASEWHEN (SRC_CUSTOMER.DEAR = 1, ‘MRS’, ‘MS’))



CUST_NAME: Compor a seguinte expressão: SRC_CUSTOMER.FIRST_NAME || ' ‘ || UCASE(SRC_CUSTOMER.LAST_NAME)

SALES_PERS:Compor a seguinte expressão: SRC_SALES_PERSON.FIRST_NAME || ' ' || UCASE(SRC_SALES_PERSON.LAST_NAME)



CRE_DATE: Como queremos apenas que o mapeamento funcione apenas no “Insert”, desmarcar “Update”. Depois, compor a seguinte expressão: CURDATE()

UPD_DATE: Como queremos apenas que o mapeamento funcione apenas no “Update”, desmarcar “Insert”. Depois, compor a seguinte expressão: CURDATE()


Transformação dos Dados Neste ponto, deveremos ter esta tabela

de mapeamento.


Transformação dos Dados Escolhendo estratégias de carregamento dos

dados. Na aba “Flow” são visualizados os passos para realizar a

transformação dos dados.



dados. Clique na janela “Target + Staging Area” e escolha “IKM

SQL Incremental Update”.



dados. Vá para a aba “Control”


Transformação dos Dados Agora estamos prontos para executar nossa

interface. Clique em “Execute”, salve a interface. Abra o módulo “Operator”. Dê um clique duplo

em “Pop.TRG_CUSTOMER”, vá para a aba “Execution” e verifique os números.


Transformação dos Dados Verificando o resultado

Verifique a tabela resultante. Na perspectiva “Models”, expanda “Sales Administrator – HSQL”, clique com o botão direito em TRG_CUSTOMER e em “Data”.


Transformação dos Dados Verificando erros

Vimos que ocorreram 9 erros durante a execução da integração de dados. Para visualizá-los, clique com o botão direito em “TRG_CUSTOMER” > “Control” > “Errors”


Transformação dos Dados Corrigindo erros: alterar os valores diretamente

na fonte de dados

Por exemplo: o cliente com Id 203 está registrado com uma cidade não registrada (208), quando na verdade a cidade correta é a com Id 107. Basta alterar este valor e clicar em “Execute” na janela “Interface”.


Interface para a tabela TRG_SALES

Definir junção entre: SRC_ORDERS SRC_ORDER_LINES



Regras de transformação de dados: Somente pedidos com status = ‘CLO’ (Closed) Definir filtro no atributo status:

SRC_ORDERS.STATUS = ‘CLO’



Regras de transformação de dados: FIRST_ORD_ID: menor ORDER_ID

MIN(SRC_ORDERS.ORDER_ID) FIRST_ORD_DATE: menor data de pedido

MIN(SRC_ORDERS.ORDER_DATE) LAST_ORD_ID: maior ORDER_ID

MAX(SRC_ORDERS.ORDER_ID) LAST_ORD_DATE: maior data de pedido

MAX(SRC_ORDERS.ORDER_DATE)



Regras de transformação de dados: QTY: total da quantidade de produtos vendidos

SUM(SRC_ORDER_LINES.QTY) AMOUNT: total de venda dos produtos

SUM(SRC_ORDER_LINES.AMOUNT) PROD_AVG_PRICE: preço médio de venda dos

produtos AVG(SRC_ORDER_LINES.AMOUNT)



Regras de transformação de dados:



Configurar Knowledge Module (KM) para exportar e carregar dados: wrapper Aba “Flow”

Data Loading Strategy (LKM): LKM SQL to SQL



Configurar KM para integração dos dados Aba “Flow”

Data Integration Strategy (IKM): IKM SQL Incremental Update



Configurar KM para verificação dos dados Aba “Control”

Data Control Strategy: CKM HSQL Restrições

PK_TRG_SALES FK_SALES_CUST (chave estrangeira SRC_CUSTOMER) FK_SALES_PROD (chave estrangeira SRC_PRODUCT)



Executar a interface – analisar o resultado e erros


Automatização da Carga no DW Definir o fluxo correto para a carga dos dados no

DW Usar interfaces previamente definidas Atenção para a ordem de carga:

Dependências de chaves estrangeiras


Automatização da Carga no DW

1

2

3

4

5

67


Automatização da Carga no DW Criar package “Load Sales Administration”:

Procedimento “Delete Targets” “Pop.TRG_COUNTRY” interface “Pop.TRG_REGION” interface “Pop.TRG_CITY” interface “Pop.TRG_PROD_FAMILY” interface “Pop.TRG_PRODUCT” interface “Pop.TRG_CUSTOMER” interface “Pop.TRG_SALES” interface





Executar a carga por meio de comando do Sistema Operacional Criar scenario


Automatização da Carga no DW Executar scenario do Sistema Operacional

Abrir prompt e ir para a pasta do Oracle Data Integrator, diretório bin

Digitar o comando: startscen LOAD_SALES_ADMINISTRATION 001 GLOBAL “-v=2”

Conclusão

Processo ETL no Oracle Data Integrator Limpeza dos Dados

Constraints Extração e Transformação dos Dados

Interfaces Automatização da carga dos Dados

Packages e scenarios

Referências

[Oracle, 2008]: Oracle Data Integrator: Getting Started with an ETL Project.

[Oracle,2009]: Oracle Data Integrator User’s Guide, 10g Release 3 (10.1.3).

Blog Integration Data Cube http://idcube.blogspot.com.br/

http://idcube.blogspot.com.br/



Glossário e Termos Técnicos

Termos Técnicos

Descrição

Action Action são modelos para Data Definition Language (DDL) comandos.

Agent Componentes de software Java, que permitem trabalhos ODI para ser executado em uma máquina remota.

CommonFormatDesigner

Common Format Designer (CFM) é usado para rapidamente desenvolver um modelo de dados a partir da interface de usuário do Designer.

Connection Connection ODI é como se conecta a um servidor de dados. Exige (na maioria dos casos) um nome de usuário (login) e uma senha. A ligação pode ser gerida através de um diretório LDAP. A única conexão permite o acesso a vários esquemas armazenados no servidor de dados mesmo.

*Context Um contexto é um conjunto de recursos que permitam o funcionamento ou a simulação de uma ou maisaplicações de processamento de dados


Termos Técnicos

Descrição

*Data Server Um servidor de dados é um recurso de processamento de dados que armazena e reproduz dados na forma de tabelas. Pode ser um banco de dados, uma MOM, um conector ou um servidor de arquivos.

Data Type Categoria ou a natureza dos dados. As tecnologias de cada um deles um tipo que define a sua natureza.

*Datastore Um armazenamento de dados é uma estrutura que permite que dados sejam armazenados. Pode ser uma tabela, um arquivo, uma fila de mensagens ou qualquer outra estrutura de dados acessíveis por middleware compatível com ODI (JDBC / ODBC, JMS ou JNDI).

Diagram Um diagrama é uma visão gráfica de um subconjunto de armazenamentos de dados contidos em um submodelo(ou modelo de dados).

Driver Componente de software fornecido como um conjunto de classes Java, permitindo o acesso a dados externos.


Termos Técnicos

Descrição

Flow ODI Entity permitindo um armazenamento de dados para carga de fonte diversos Datastore

Folder Uma pasta é um grupo de pacotes, interfaces e procedimentos específicos. Pastas e sub-pastas permitir que esses objetos sejam agrupados e organizados de acordo com critérios específicos. Sub-pastas podem ser criadas para um número ilimitado de níveis.

Graphical Synonym

Um sinônimo é uma representação gráfica de um armazenamento de dados. Sinônimos gráficos são utilizados para tornar o diagrama mais legível. Em um diagrama, um armazenamento de dados pode aparecer várias vezes como um sinônimo gráfica.

Integrity Constraints

Regra que define os limites de validade da informação. As restrições de integridade são anexados ao datastores. Existem vários tipos de restrições: Referências, chaves primárias, chaves alternativas, o ODI Controls.


Termos Técnicos

Descrição

Interface Uma interface consiste de um conjunto de regras que definem o carregamento de um armazenamento de dados ou de uma estrutura temporária alvo de um ou mais armazenamentos de dados de origem. O módulo Designer permite que as interfaces sejam definidas e testadas.

Interface IN Essas interfaces de integração geradas são usadas para carregar datastores do modelo montado a partir de outros armazenamentos de dados / colunas. Eles estão no processo de integração de dados a partir da fusão datastores original no datastores composto.

Interface OUT Essas interfaces de integração são utilizados para extrair dados de armazenamentos de dados do modelo. Eles são gerados usando as interfaces (incluindo as interfaces IN) já armazenamento de dados de carga do modelo. Eles revertem o processo de integração para propagar os dados do armazenamento de dadoscomposta para o datastores original.


Termos Técnicos

Descrição

*JDBC JDBC (Java Database Connectivity) um padrão API Java que dá acesso aos dados contidos em um RDBMS. Ela exige a instalação de um driver JDBC, se este não é fornecido com o ODI.

JMS Java Message Service uma API Java padrão criado pela Sun Microsystems para prover acesso à MOM.

JVM Java Virtual Machine

LDAP "Lightweight Directory Access Protocol" é um protocolo de acesso de um directório de recursos corporativos. Estes recursos são organizados em hierarquias e representar os funcionários, departamentos, máquinas, bancos de dados locais. O acesso a um diretório LDAP é assegurada por nomes de usuário e senhas.

*Meta-data Conjunto de dados que descrevem outros dados. É uma descrição da estrutura das tabelas e colunas em um banco de dados contidos em um servidor de dados.


Termos Técnicos

Descrição

Middleware Componente de software que permite a comunicação entre dois programas no modo cliente / servidor. Esse tipo de software é geralmente baseado em um padrão (JDBC, ODBC, JMS), ligado ao tipo do servidor de tecnologia.

Model Data Physical Model.

Module Software fornecido pelo ODI para conectar os repositórios e fornecendo um conjunto de características úteis para um grupo de pessoas. Exemplo: Designer, agente, Security Manager.

*MOM Message Oriented MiddleWare: Ferramentas de eventos que permitam o transporte de informações em um formato estruturado (texto, XML, objetos) ou não à distância entre sistemas heterogêneos. Conjunto de informações gerenciadas por uma MOM são filas e tópicos.


Termos Técnicos

Descrição

ODBC ODBC (Open Database Connectivity) é uma API que dá acesso aos dados contidos em um RDBMS. Ela exige a instalação de um driver ODBC, específico para o RDBMS acessado e o sistema operacional do cliente.

Package Um pacote é um conjunto de postos de trabalho seqüenciado, também chamado de etapas, projetado para ser executado em uma ordem pré-definida.

Physical Schema The physical schema é uma decomposição do servidor de dados, permitindo que os armazenamentos de dados (tabelas, imagens, etc) para serem classificados.

*Pooling Ação que consiste em interrogar repetidamente um aplicativo para recuperar os novos eventos.

Project Um projeto é um grupo de objetos desenvolvidos usando o ODI.


Termos Técnicos

Descrição

*Queue Conjunto de informações gerenciadas por uma MOM permitindo a publicação de um tipo de evento por uma aplicação e consumo deste mesmo evento. A fila é utilizada para comunicação ponto a ponto assíncrono entre as aplicações.

RDBMS DataBase Management System. É um servidor de dados como Oracle, Sybase, DB2, etc...

Reference Ligação funcional entre dois datastores.

Repository Repositório contém todas as informações necessárias para o desenvolvimento de interfaces e operacional. O repositório é armazenado em um banco de dados acessível por vários usuários simultaneamente.

Reverse A engenharia reversa consiste em recuperar os meta-dados de um servidor de repositório de dados para armazená-las no repositório do ODI.


Termos Técnicos

Descrição

Sequence Uma sequence é uma variável que aumenta cada vez que ela é usada.

Session Uma sessão é uma execução (de um cenário, uma interface, um pacote ou um procedimento, etc ..) realizados por um agente de execução ODI. Uma sessão é composta de etapas, que são constituídos por tarefas.

Solution A solução é um conjunto abrangente e consistente de versões de objetos interdependentes ODI. Ela pode ser verificada, em um determinado momento e em uma versão que pode ser restaurada em data posterior. As soluções são salvos em ODI Master Repository. A solução reúne um grupo de versões chamados elementos da solução.

Sub-Model Um sub-modelo é um grupo de datastores funcionalmente homogêneas dentro de um modelo.


Termos Técnicos

Descrição

Technology Na terminologia do ODI, isso é algum tipo de tecnologia acessível por JDBC, ODBC, JMS, JNDI, JCA, ou qualquer outro sistema operacional.

Topic Conjunto de informações gerenciadas por uma MOM permitindo a comunicação através do método "publicar e assinar". Um aplicativo que deseja consumir um tipo de evento devem inscrever-se neste tipo de evento. Cada evento é consumido por todos os inscritos para o tipo de evento.

URL "Uniform Resource Locator". Sintaxe de elemento que permite localizar um recurso acessível pelo TCP / IP. Cada URL começa com o nome do protocolo utilizado, o mais comum é "http".

Version A versão é uma cópia de backup de um objeto de ODI. É verificado em um determinado momento e podem ser restauradas depois. As versões são salvas no ODI Master Repository.

Virtual Machine Ambiente que permita a compilação e execução de programas Java. Todos os componentes do ODI requer uma JVM para executar.