ODI Tutorial - Desenvolvendo Procedures

  • Published on
    05-Jul-2015

  • View
    1.334

  • Download
    3

Embed Size (px)

DESCRIPTION

Terceiro documento da Srie Tutorial Oracle Data Integrator. Neste tutorial demonstro como criar Procedures para criao de tabelas e carga de dados.

Transcript

<ul><li> 1. March 3 2012ODITutorialUso da ferramenta Oracle Data Integrator (ODI) paraa construo de processos ETL (Extract, Transformand Load). Nesta srie de tutoriais, utilizaremos o ODI Desenvolvimentopara integrar dados de diferentes origens (bancos dedados diferentes e arquivos texto) para uma base dede Proceduresdestino Oracle.</li></ul><p> 2. Desenvolvimento de ProceduresDepois de configurar a Topologia para o nosso projeto, comearemos agora adesenvolver as integraes e o primeiro passo nessa jornada montar o ambiente deorigem, as tabelas, as vises, as procedures, enfim todos os objetos necessrios paracriar a origem dos dados.Neste post vou disponibilizar a maneira clssica da criao do ambiente e tambmpropor a utilizao do mdulo Designer do ODI contrundo Procedures para exercer amesma funo.Mas sempre lembrando que devemos utilizar os objetos de Procedure com muitacautela, pois as procedures permitem a execuo de aes bem complexas, incluindocomandos SQL. Alm disso, elas permitem a utilizao das conexes source e target eainda suportam biding, isto significa que possvel mover dados de um lado para ooutro usando apenas as procedures.Os desenvolvedores que se sentem vontade com cdigo SQL ficam sriamentetentados a escrever cdigo para fazer transformaes e movimentao de dados aoinvs de desenvolver interfaces.Existem alguns problemas quanto isso: Procedures contm cdigo manual que precisa sofrer manuteno. Procedures no mantm referncias com os outros artefatos ODI como datastores, modelos, etc, fazendo com que a manuteno seja muito mais complexa comparado s interfaces. Procedures nunca devem ser utilizadas para mover ou transformar dados, essas operaes devem ser feitas utilizando as interfaces.Outra importante informao que devemos ter quando iniciamos um projeto dedesenvolvimento a organizao. Organize o seu projeto no inicio da construo, poisser muito mais fcil realizar o desenvolvimento e manuteno posterior.O gerenciamento e a organizao so pontos crticos quando estamos falando deintegrao de dados, o ODI oferece muitas ferramentas que ajudam a organizar odesenvolvimento e o ciclo de vida do projeto, tais como: perfis de segurana, pastas,marcadores, versionamento, importao e exportao, impresso de documentao,etc.Utilize todas as ferramentas disponveis para gerenciar o seu projeto. Defina aorganizao do projeto, a padronizao de nomes e tudo o que pode evitar o caosdepois que o projeto tiver iniciado. Em outros posts iremos detalhar cada uma dessasferramentas e features e lembre-se sempre ORGANIZE O SEU PROJETO NO INCIO.Neste post iremos explorar conforme mencionado o objeto Procedure, tambmpodemos encontrar na literatura e na verso em portugus do ODI a nomenclaturaProcedimento. 3. Procedure ou Procedimento como o nome indica, so objetos em que so escritosqualquer tipo de procedimento extra que se faa necessrio no processo de ETL.Podemos criar procedimentos que contenham vrios tipos de cdigos, de diferentestecnologias suportadas pelo ODI, como por exemplo, escrever um procedimento emPL/SQL, em Java, em Jython, etc.Modelagem do SistemaAntes de planejar a criao das PROCEDURES devemos planejar a criao do nossobanco de dados, lembrando que devemos sempre ORGANIZAR O PROJETO NO INCIO,para o nosso estudo contruiremos um SISTEMA DE CONTROLE DE VENDAS.O modelo proposto representa uma estrutura bsica de vendas de mercadorias, aestrutura de tabelas representa um modelo clssico de Data Warehouse na origem,neste modelo teremos as tabelas CLIENTES, PRODUTOS, GRUPO ITEM, ITENS DEESTOQUE, TIPO DE CLIENTES, VENDEDORES E FATURAMENTO. No destino estaremostrabalhando com o conceito de Data Mart criando uma estrutura de cubos e dimensespara capturar as informaes de faturamento para gerenciamento e anlise deperformance de vendas, neste modelo teremos as tabelas de Dimenso paraCLIENTES, PRODUTOS, TEMPO (MESES, ANOS, SEMANAS e ETC.), VENDEDORES e atabela FATO onde sero registrados os KPIs e/ou indicadores para montagem dasanlises. 4. Criando as tabelas Modelo ClssicoModelo Data Warehouse ( User/Schema DW.ORIGEM )-- Tabela TIPO_CLIENTE CREATE TABLE "DW_ORIGEM"."TIPO_CLIENTE"( "CD_TIPO_CLIENTE" NUMBER(*,0) NOT NULL ENABLE, "DS_TIPO_CLIENTE" VARCHAR2(30), PRIMARY KEY ("CD_TIPO_CLIENTE") ENABLE ) ;--Tabela CLIENTECREATE TABLE "DW_ORIGEM"."CLIENTE" ( "CD_CLIENTE" NUMBER(*,0) NOT NULL ENABLE,"NM_CLIENTE" CHAR(30),"CD_TIPO_CLIENTE" NUMBER(*,0),PRIMARY KEY ("CD_CLIENTE") ENABLE,FOREIGN KEY ("CD_TIPO_CLIENTE") REFERENCES "DW_ORIGEM"."TIPO_CLIENTE"("CD_TIPO_CLIENTE") ON DELETE SET NULL ENABLE );-- Tabela VENDEDOR CREATE TABLE "DW_ORIGEM"."VENDEDOR"( "CD_VENDEDOR" NUMBER(*,0) NOT NULL ENABLE, "NM_VENDEDOR" CHAR(30), "PERC_COM" NUMBER(5,2), PRIMARY KEY ("CD_VENDEDOR") ENABLE ) ;--Tabela FATURAMENTOCREATE TABLE "DW_ORIGEM"."FATURAMENTO" ( "NUM_NF" NUMBER(*,0) NOT NULL ENABLE,"CD_VENDEDOR" NUMBER(*,0) NOT NULL ENABLE,"CD_CLIENTE" NUMBER(*,0) NOT NULL ENABLE,"DT_VENDA" DATE NOT NULL ENABLE,"TOT_NF" NUMBER(9,2),"LOCAL_VENDA" VARCHAR2(30),PRIMARY KEY ("NUM_NF") ENABLE,FOREIGNKEY("CD_CLIENTE")REFERENCES"DW_ORIGEM"."CLIENTE"("CD_CLIENTE") ENABLE,FOREIGNKEY ("CD_VENDEDOR")REFERENCES "DW_ORIGEM"."VENDEDOR"("CD_VENDEDOR") ENABLE ) ; 5. -- Tabela GRUPO_ITEMCREATE TABLE "DW_ORIGEM"."GRUPO_ITEM" ( "CD_GRUPO_ITEM" NUMBER(*,0) NOT NULL ENABLE,"NM_GRUPO_ITEM" CHAR(30),PRIMARY KEY ("CD_GRUPO_ITEM") ENABLE ) ;-- Tabela ITEM_ESTOQUECREATE TABLE "DW_ORIGEM"."ITEM_ESTOQUE" ( "CD_GRUPO_ITEM" NUMBER(*,0) NOT NULL ENABLE, "CD_ITEM"NUMBER(*,0) NOT NULL ENABLE, "NM_ITEM" VARCHAR2(40) NOT NULL, "UNIDADE"VARCHAR2(5), "PRECO"NUMBER(9,2), "QTE_ESTOQUE" NUMBER(*,0), PRIMARY KEY ("CD_GRUPO_ITEM", "CD_ITEM") ENABLE, FOREIGN KEY("CD_GRUPO_ITEM")REFERENCES"DW_ORIGEM"."GRUPO_ITEM" ("CD_GRUPO_ITEM") ENABLE) ;-- Tabela ITEM_NF CREATE TABLE "DW_ORIGEM"."ITEM_NF"("NUM_NF"NUMBER(*,0) NOT NULL ENABLE, "CD_GRUPO_ITEM" NUMBER(*,0) NOT NULL ENABLE, "CD_ITEM" NUMBER(*,0) NOT NULL ENABLE, "QTE_VENDA" NUMBER(*,0), "VLR_UNITARIO" NUMBER(9,2), PRIMARY KEY ("NUM_NF", "CD_GRUPO_ITEM", "CD_ITEM") ENABLE, FOREIGNKEY ("CD_GRUPO_ITEM", "CD_ITEM") REFERENCES"DW_ORIGEM"."ITEM_ESTOQUE" ("CD_GRUPO_ITEM", "CD_ITEM") ENABLE ) ; 6. Modelo Data Mart ( User/Schema DW.DESTINO )-- Tabela DIM_CLIENTE CREATE TABLE "DW_DESTINO"."DIM_CLIENTE"( "CD_CLIENTE" NUMBER(*,0), "NM_CLIENTE" VARCHAR2(30), "CD_TIPO_CLIENTE" NUMBER(*,0), "DS_TIPO_CLIENTE" VARCHAR2(30), "ID_CLIENTE" NUMBER(*,0) NOT NULL ENABLE,CONSTRAINT "DIM_CLIENTE_PK" PRIMARY KEY ("ID_CLIENTE") ENABLE ) ;-- Tabela DIM_PRODUTOCREATE TABLE "DW_DESTINO"."DIM_PRODUTO" ( "ID_PRODUTO" NUMBER(*,0) NOT NULL ENABLE,"CD_ITEM" NUMBER(*,0),"CD_GRUPO_ITEM" NUMBER(*,0),"UNIDADE" VARCHAR2(5),"NM_ITEM" VARCHAR2(40),"NM_GRUPO_ITEM" VARCHAR2(30), CONSTRAINT "DIM_PRODUTO_PK" PRIMARY KEY ("ID_PRODUTO") ENABLE) ;-- Tabela DIM_TEMPO CREATE TABLE "DW_DESTINO"."DIM_TEMPO"( "ID_TEMPO" NUMBER NOT NULL ENABLE, "DATA_DIA" DATE, "DIA" NUMBER, "DIA_SEMANA" VARCHAR2(50), "MES" NUMBER, "MES_ANO" VARCHAR2(50), "ANO" NUMBER, "TURNO" VARCHAR2(50),CONSTRAINT "DIM_TEMPO_PK" PRIMARY KEY ("ID_TEMPO") ENABLE ) ;-- Tabela DIM_VENDEDOR CREATE TABLE "DW_DESTINO"."DIM_VENDEDOR"( "ID_VENDEDOR" NUMBER NOT NULL ENABLE, "CD_VENDEDOR" NUMBER, "NM_VENDEDOR" VARCHAR2(30), "PERC_COM" NUMBER(5,2),CONSTRAINT "DIM_VENDEDOR_PK" PRIMARY KEY ("ID_VENDEDOR") ENABLE) ; 7. -- Tabela FATO_VENDAS CREATE TABLE "DW_DESTINO"."FATO_VENDAS" ( "ID_PRODUTO" NUMBER NOT NULL ENABLE,"ID_CLIENTE" NUMBER NOT NULL ENABLE,"ID_VENDEDOR" NUMBER NOT NULL ENABLE,"ID_TEMPO" NUMBER NOT NULL ENABLE,"QTE_VENDA" NUMBER,"PRC_VENDA" NUMBER(9,2),FOREIGN KEY ("ID_PRODUTO") REFERENCES "DW_DESTINO"."DIM_PRODUTO"("ID_PRODUTO") ENABLE,FOREIGN KEY ("ID_CLIENTE") REFERENCES "DW_DESTINO"."DIM_CLIENTE"("ID_CLIENTE") ENABLE,FOREIGN KEY ("ID_VENDEDOR") REFERENCES "DW_DESTINO"."DIM_VENDEDOR"("ID_VENDEDOR") ENABLE,FOREIGNKEY("ID_TEMPO")REFERENCES "DW_DESTINO"."DIM_TEMPO"("ID_TEMPO") ENABLE ) ; 8. Inserindo Dados Modelo ClssicoModelo Data Warehouse ( User/Schema DW.ORIGEM )-- DADOS PARA TABELA TIPO_CLIENTEInsertinto TIPO_CLIENTE (CD_TIPO_CLIENTE,DS_TIPO_CLIENTE) values(5,COMERCIO);Insertinto TIPO_CLIENTE (CD_TIPO_CLIENTE,DS_TIPO_CLIENTE) values(6,ARQUITETURA);Insertinto TIPO_CLIENTE (CD_TIPO_CLIENTE,DS_TIPO_CLIENTE) values(7,INFORMATICA);Insertinto TIPO_CLIENTE (CD_TIPO_CLIENTE,DS_TIPO_CLIENTE) values(8,INDUSTRIA);Insertinto TIPO_CLIENTE (CD_TIPO_CLIENTE,DS_TIPO_CLIENTE) values(10,BANCO);-- DADOS PARA TABELA CLIENTEInsertintoCLIENTE (CD_CLIENTE,NM_CLIENTE,CD_TIPO_CLIENTE)values(1,Sanders Roch,7);Insert into CLIENTE (CD_CLIENTE,NM_CLIENTE,CD_TIPO_CLIENTE) values (2,PernalLivi,8);InsertintoCLIENTE (CD_CLIENTE,NM_CLIENTE,CD_TIPO_CLIENTE)values(3,Marenghi Nor,5);Insert into CLIENTE (CD_CLIENTE,NM_CLIENTE,CD_TIPO_CLIENTE) values (4,OBrienVan DerHor,6);Insert into CLIENTE (CD_CLIENTE,NM_CLIENTE,CD_TIPO_CLIENTE) values (5,HanesAlvis,10);InsertintoCLIENTE (CD_CLIENTE,NM_CLIENTE,CD_TIPO_CLIENTE)values(7,Rothman Chit,7);InsertintoCLIENTE (CD_CLIENTE,NM_CLIENTE,CD_TIPO_CLIENTE)values(9,Koonitz Scprect Tu,6);-- DADOS PARA TABELA VENDEDORInsert into VENDEDOR (CD_VENDEDOR,NM_VENDEDOR,PERC_COM) values (1,MariaELISA ANGE,9);Insert into VENDEDOR (CD_VENDEDOR,NM_VENDEDOR,PERC_COM) values (4,LUCasZardo dos reis,10);Insert into VENDEDOR(CD_VENDEDOR,NM_VENDEDOR,PERC_COM)values(5,CLAUDIOMIRO A NUNES,8);Insert into VENDEDOR (CD_VENDEDOR,NM_VENDEDOR,PERC_COM) values (15,SILVIAMaria,6); 9. Insert into VENDEDOR (CD_VENDEDOR,NM_VENDEDOR,PERC_COM) values (10,Mirna SRIAT,6);Insert into VENDEDOR (CD_VENDEDOR,NM_VENDEDOR,PERC_COM) values (11,MarA LBatista,4);-- DADOS PARA TABELA FATURAMENTOInsertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(441,1,5,to_date(29/12/04,DD/MM/RR),123.44,Iju);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(442,4,2,to_date(30/12/04,DD/MM/RR),58.14,Santa Rosa);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(12941,5,3,to_date(31/12/04,DD/MM/RR),28.49,Panambi);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(12942,5,3,to_date(01/01/05,DD/MM/RR),1921.72,Santa Rosa);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(541,1,4,to_date(02/01/05,DD/MM/RR),51.44,Iju);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(642,11,5,to_date(03/01/05,DD/MM/RR),23.14,Panambi);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(2941,10,7,to_date(04/01/05,DD/MM/RR),12.66,Iju);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(1292,5,9,to_date(05/01/05,DD/MM/RR),321.01,Panambi);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(361,4,1,to_date(06/01/05,DD/MM/RR),73.44,Santa Rosa);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(2,11,4,to_date(07/01/05,DD/MM/RR),3.14,Iju);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(191,15,1,to_date(08/01/05,DD/MM/RR),62.66,Santa Rosa);Insertinto FATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA) values(122,5,2,to_date(09/01/05,DD/MM/RR),31.64,Iju); 10. InsertintoFATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA)values(51,5,5,to_date(11/01/05,DD/MM/RR),183.99,Panambi);InsertintoFATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA)values(62,4,4,to_date(05/01/05,DD/MM/RR),29.44,Santa Rosa);InsertintoFATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA)values(371,11,4,to_date(10/01/05,DD/MM/RR),82.66,Santa Rosa);InsertintoFATURAMENTO(NUM_NF,CD_VENDEDOR,CD_CLIENTE,DT_VENDA,TOT_NF,LOCAL_VENDA)values(1092,10,5,to_date(09/01/05,DD/MM/RR),221.93,Iju);-- DADOS PARA TABELA GRUPO_ITEMInsert into GRUPO_ITEM(CD_GRUPO_ITEM, NM_GRUPO_ITEM) values (7,Eletronicos);Insert into GRUPO_ITEM (CD_GRUPO_ITEM, NM_GRUPO_ITEM) values (4, Livraria);Insert into GRUPO_ITEM(CD_GRUPO_ITEM, NM_GRUPO_ITEM) values (5,Informatica);Insert into GRUPO_ITEM (CD_GRUPO_ITEM, NM_GRUPO_ITEM) values (1, ArtigosEsportivos);-- DADOS PARA TABELA ITEM_ESTOQUEInsert into ITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (5, 1, Cartucho CANON BC-02, UND, 72.5, 20);Insert into ITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (5, 2, Cartucho EPSON 20093, UND, 65.1, 10);Insert into ITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (5, 3, Cartucho HP 51629A, UND, 45.28, 17);Insert into ITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (5, 36, Papel form. 80 1V br.c/3000 SP, CXA, 55.79,6);Insert into ITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (5, 31, Refil impr.EXTR.8508 51649A c1, CJT, 19.39,7);Insert into ITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (5, 47, Toner HP C 7115A p/laser 1200, UND, 245.61,3);Insert into ITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (5, 50, Mouse CLONE c/saida serial, UND, 5.33, 3);Insert into ITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (4, 1, Cola TENAZ 500grs, TBO, 2.5, 20); 11. Insert intoITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (4, 31, Caneta esfer.PARKER, PC, 5.8, 3);Insert intoITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (4, 33, Agenda, PC, 6.54, 8);Insert intoITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (4, 4, Album, PC, 12.78, 12);Insert intoITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (4, 41, Regua Metal, UND, 1.78, 81);Insert intoITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (7, 2, DVD, UND, 1222.5, 2);Insert intoITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (7, 4, Televisao 14 Pol., PC, 445.8, 3);Insert intoITEM_ESTOQUE (CD_GRUPO_ITEM, CD_ITEM, NM_ITEM, UNIDADE, PRECO,QTE_ESTOQUE) values (7, 8, PlayStation, PC, 512.78, 8);-- DADOS PARA TABELA ITEM_NFInsert into ITEM_NF (NUM_NF, CD_GRUPO_ITEM, CD_ITEM, QTE_VENDA, VLR_UNITARIO)values (2, 4, 1, 1, 3.14);Insert into ITEM_NF (NUM_NF, CD_GRUPO_ITEM, CD_ITEM, QTE_VENDA, VLR_UNITARIO)values (51, 5, 2, 1, 65.1);Insert into ITEM_NF (NUM_NF, CD_GRUPO_ITEM, CD_ITEM, QTE_VENDA, VLR_UNITARIO)values (51, 5, 3, 1, 48.28);Insert into ITEM_NF (NUM_NF, CD_GRUPO_ITEM, CD_ITEM, QTE_VENDA, VLR_UNITARIO)values (51, 5, 1, 1, 70.61);Insert into ITEM_NF (NUM_NF, CD_GRUPO_ITEM, CD_ITEM, QTE_VENDA, VLR_UNITARIO)values (12941, 5, 31, 1, 28.49);Insert into ITEM_NF (NUM_NF, CD_GRUPO_ITEM, CD_ITEM, QTE_VENDA, VLR_UNITARIO)values (12942, 7, 2, 1, 1921.72);Insert into ITEM_NF (NUM_NF, CD_GRUPO_ITEM, CD_ITEM, QTE_VENDA, VLR_UNITARIO)values (2941, 4, 33, 1, 12.66);Insert into I...</p>