www.it-ebooks - dbmanagement.info

1 data integrator 11g CookbookOver 60 field-tested recipes for successful data integration projects with oracle data IntegratorChristophe DupupetPeter C. Boyd-BowmanDenis GrayJulien TestutBIRMINGHAM - data integrator 11g CookbookCopyright 2013 Packt PublishingAll rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals.

2 However, Packt Publishing cannot guarantee the accuracy of this published: May 2013 Production Reference: 2060613 Published by Packt Publishing Place35 Livery StreetBirmingham B3 2PB, Image by Karl Moore DupupetPeter C. Boyd-BowmanDenis GrayJulien TestutReviewersUli BethkeHans ForbrichKevin GlennyMaciej KoconRay McCormackAcquisition EditorsEdward GordonErol StaveleyLead Technical EditorNeeshma RamakrishnanTechnical EditorsSaijul ShahSayali MirajkarHardik B. SoniProject CoordinatorArshad SopariwalaProofreaderPaul HindleIndexerMonica AjmeraProduction CoordinatorAparna BhagatCover WorkAparna 1998, when Sunopsis first opened its door to begin development of the product that would become ODI, the technology landscape was very different from today. There were very few data warehouses over 10 terabytes, and both the underlying hardware and software were struggling to keep up with the load and query demands placed upon them. As a result, the warehouses only included the most critical transactional data from the few key systems.

3 The prevailing ETL approach of the day was to hand-build highly optimized flows, which executed in dedicated hardware, and then spoon-feed the results into the data warehouse. In this context, the founding principles of ODI were extraordinarily bold: leverage the data warehouse itself as the transformation engine and enable developers to work at a much higher level of abstraction, counting on the tool to generate an optimized execution with hindsight 15 years later, these principles seem prescient. data warehouses have grown to be petabytes in size, and the hardware that houses them is often the most powerful in the data center 100s of processor cores, terabytes of RAM, and 10s of terabytes of Flash. After many years of concerted effort, and with thousands of mappings to show for it, many enterprises have added far more transaction systems into their warehouses, and they are now looking to bring in data sets that have hitherto been dark (for example, server logs, social media feeds), and may be best preprocessed on open source distributed frameworks such as Hadoop.

4 The world has finally caught up to on your decision to take a different approach to bulk movement and transformation of data within your business. For developers experienced on traditional ETL tools, you will discover yourself doing far less mundane work once you grasp a few of the key ODI concepts such as topologies, knowledge modules, and set-based transformation. This book will be an excellent companion for you on this journey. Written by four experts on the product (with decades of experience among them), including key product managers who are continuing to drive ODI's evolution, this book complements the product documentation with a variety of practical recipes. In addition to all of the common tasks required in populating a data warehouse (for example, change data capture, slowly changing dimensions), readers will also find valuable information on using ODI within a web service environment, and how to use its powerful APIs to programmatically author ODI its acquisition, ODI has become the key bulk data technology within oracle products and within our cloud offerings.

5 I hope you find it as impactful in your business as it has been in AdelbergVice President, Development for data IntegrationOracle the AuthorsChristophe Dupupet is a Director in the Fusion Middleware Architects Team, where he leads the expertise on ODI. The team works closely with strategic customers that implement ODI, and helps define best practices on the product in terms of architecture, implementation, and to oracle , Christophe was part of the team that started the operations for Sunopsis in the US, where he lead the technical team (presales, support, and training). Sunopsis was acquired by oracle in holds an Operations Research degree from EISTI in France, a Masters Degree in Operations Research from Florida Tech, and a certificate in Management from Harvard is a co-author of the book Getting Started with oracle data integrator 11g: A Hands-on would be no book if we did not have a fantastic product and customers to trust us with this product.

6 I want to particularly thank all the individuals that have helped this product become what it is today: the architects and software engineers that work and have worked on ODI for their vision and production; our support engineers that help our customers every day and help shape best practices with a forever growing knowledge base ( is truly a goldmine if you are looking for information on how to use ODI); our sales engineers and product managers that help customers and partners in their selection of our C. Boyd-Bowman is a Technical Director and Consultant with the oracle Corporation. He has over 30 years of software engineering and database management experience, including 12 years of focused interest in data warehousing and business intelligence. Capitalizing on his extensive background in oracle database technologies dating back to 1985, he has spent recent years specializing in data migration. After many successful project implementations using oracle Warehouse Builder, and shortly after oracle 's acquisition of the Sunopsis Corporation, he switched his area of focus over to oracle 's flagship ETL product: oracle data integrator .

7 Peter holds a BS degree in Industrial Management and Computer Science from Purdue University and currently resides in North Gray is a Director of Product Management for data Integration at oracle . Denis has over 15 years of experience in the data -integration field. For the past seven years, Denis has been an integral part of oracle Development Organization as a Product Manager within Fusion Middleware, delivering data integration solutions. Prior to this, Denis was a data integration consult for Hyperion Solutions ( oracle ). Here, Denis worked at many of the largest Fortune 100 companies, building data warehouses and implementing business intelligence solutions. Denis has a Bachelor's Degree in Computer Science from the University of Missouri and currently resides in St. Louis, MO. Denis also co-authored the Packt book Getting Started with oracle data integrator 11g: A Hands-on would like to thank my beautiful wife Tracy and my sons, Tad and Charlie, for their support, understanding, and above all their love and faith.

8 There were many nights where I was missing in action; however, I never heard a complaint. Also a special thanks to my co-authors for their help and guidance throughout this Testut is a Product Manager in the oracle data Integration group focusing on oracle data integrator . Julien has an extensive background in data Integration and data Quality solutions and is a co-author of Getting Started with oracle data integrator 11g: A Hands-on Tutorial. Prior to joining oracle , he was an Applications Engineer at Sunopsis, which was then acquired by oracle . Julien holds a Masters degree in Software my daughter Olivia, who was born while I was working on this book and who has been filling my life with joy since then. Thanks to my wife Emilie for her help and patience throughout this book's writing process. I couldn't have done it without you. I would also like to take this opportunity to express my gratitude to my co-authors. It's always a pleasure to collaborate with you!

9 Finally, I would like to thank my family and friends for their support as well as my colleagues who help make oracle data integrator a better product with every release. Thank you! the ReviewersUli Bethke has been working with ODI since 2007. After some initial difficulties familiarizing himself with the tool, he quickly realized the huge potential of ODI. A couple of great innovations went into ODI and it is light years ahead of any of its competitors. It is one of those tools that really make a difference. Uli has been working in data integration and data warehousing for more than 13 years. Verticals include Finance, Retail, Pharma, and is a managing partner and owner of a small but growing BI consultancy in Dublin, Ireland. He offers expert services on ODI including training. He also consults on data warehousing, data integration, and enterprise architecture. If you want to avail of any of his services, you can contact him through his blog or directly via has also been a reviewer of the book Getting Started with oracle data integrator 11g: A Hands-on Tutorial, published by last time I reviewed a Packt book, my baby boy Ruairi had just been born.

10 Recently, we have welcomed Una as another member to the family. Una, Ruairi, and Helen, you really make my Forbrich has been working with, consulting on, and teaching oracle products for 30 years. He is experienced in data integration using oracle Warehouse Builder and oracle data integrator , as well as with the SOA and OSB technologies. Hans has been enthusiastic about ODI since the Sunopsis acquisition by oracle , and regularly teaches the oracle University ODI courses in North America through his partner, ExitCertified has been an oracle ACE since 2005 and an oracle ACE Director since 2008. For more information about the oracle ACE program, see is a frequent reviewer of Packt Publishing's oracle books, spanning the range of OracleVM and oracle Scheduler through oracle SOA 'd like to thank my wife Susanne for her patience while I hide behind my computer screen, editing or writing. Also, thanks to my colleagues, especially Dan Morgan and Aman Sharma, for their assistance over the years, and the challenging questions that keep me motivated in our ever expanding field of Information Glenny has international oracle Technical Architecture experience integrating large scale real-time systems.