After our collaboration on the ODI book Getting Started with Oracle Data Integrator 11g: A Hands-on Tutorial, we thought that there might be a need for a more advanced book on the same subject. So when Packt Publishing approached us to see if it would be possible to write a Cookbook on ODI, we knew that we had the medium for this new adventure. Our objective with this book was to avoid any repetition from the first book and to offer recipes that go beyond what is readily available in the ODI documentation and on the Web. Hopefully, you will agree that we have achieved our goal.
Chapter 1, Installation, Deployment, and Configuration, answers several deployment questions that we have been asked by ODI users over time. A hands-on approach to these topics allows the reader to learn the answers that we regularly give to our counterparts.
Chapter 2, Defining the Oracle Data Integrator Security, expands the descriptions available in the ODI documentation with "how-to" examples by providing recipes to implement several different aspects of ODI security.
Chapter 3, Advanced Topology, delves into Topology beyond the definition of servers and schemas. These recipes will explore specific elements of Topology that are not typically explored by the average ODI user: definition of new technologies and datatypes, use of actions, and controlling case sensitivity.
Chapter 4, Using Variables, expands the use of variables by implementing them in ways not often documented within the product, such as using variables in resource names and how to best use variables as parameters.
Chapter 5, Knowledge Module Internals, will shed new light on what is possible with KMs, explaining how substitution methods are processed by ODI and even experimenting with Java code inside the KMs.
Chapter 6, Inside Knowledge Modules – SCD and CDC, offers an opportunity to expand on the concepts covered in the previous chapter by focusing on KMs that may not be well known or well understood. We will also experiment with a few alterations of these KMs to provide an even more hands-on experience.
Chapter 7, Advanced Coding Techniques, begins by looking into code generation that can be performed directly from the models using diagrams. This chapter then explores specific advanced topics such as temporary interfaces, embedded SQL queries, pivots, and partition exchange loading.
Chapter 8, Package Loop and File Processing, was written because we noticed that too many users limit their workflows by not knowing how to use them for more mature integration techniques. The recipes in this chapter demonstrate some of the more sophisticated integration techniques, including a focus on flat files processing that will help a developer greater take advantage of ODI orchestration.
Chapter 9, XML and Web Services, discusses two areas of growing importance within ETL processing: using XML files as input and using web services. Recipes will start with the basics of the definition of an XML file and go all the way to invoking web services with callbacks.
Chapter 10, Advanced Coding Techniques Using the SDK, is rich in sample code that automates tasks usually done with the graphical interface, including installation, development, and execution monitoring.
Chapter 11, More with ODI, is a catch-all chapter that addresses questions we've often had to answer, including misconceptions on how the product should be used, and little known secrets that we didn't want to leave aside.
Since Oracle is one of the leading database platforms, it has been used throughout this book as the primary database technology. By using the DDL installation scripts available through the publisher (see the following section, Downloading the example code), all of the required source and target tables can be created and populated in two of the schemas referenced throughout the book. Please note that should you choose to follow along using an alternative technology and/or schema, you will most likely need to adjust the syntax and options accordingly. The following scripts are available:
CREATE_USER.sql: (creates the two cookbook demo user accounts)CREATE_SRC.sql: (creates the source tables)CREATE_TRG.sql: (creates the target tables)LOAD_SRC_SCHEMA.sql: (populates the source tables)LOAD_TRG_SCHEMA.sql: (populates the target tables)
Some of the recipes within this book will also be referencing flat files, so it will be necessary to establish a folder/directory within the reader's environment and to copy the available (*.txt) files into this location. For example, create a directory location called C:\Temp and copy the SRC_AGE_GROUP.txt and SRC_SALES_PERSON.txt files into that folder.
Once all the schemas have been created and the database schemas have been loaded, the reader should start up ODI and perform the following preliminary tasks:
On the Topology tab and within the File technology, create a physical data server, physical schema, and logical schema for the
DEMO_FILEschema. Note that the physical location should beC:\TEMP.On the Topology tab and within the Oracle technology, create a physical data server, physical schema, and logical schema for the
DEMO_SRCschema.On the Topology tab and within the Oracle technology, create a physical data server, physical schema, and logical schema for the
DEMO_TRGschema.
On the Designer tab, create a data model
DEMO_FILElogical schema.Import the
SRC_AGE_GROUPdata store using the available XML file.Import the
SRC_SALES_PERSONdata store using the available XML file.On the Designer tab, create a data model and reverse engineer the
DEMO_SRClogical schema.On the Designer tab, create a data model and reverse engineer the
DEMO_TRGlogical schema.
This book has been designed to present the reader with solutions to specific problems that sometimes challenge even the most accomplished ODI developer. As such, it is not meant to teach the fundamentals of the ODI product, but rather to extend the reader's understanding of Oracle Data Integrator's capabilities. In order to expedite the presentation of the material within this book, there is an assumption that the reader already has a basic understanding of the ODI product and has ready access to a database. For those readers looking for an introduction to ODI, we recommend the book Getting Started with Oracle Data Integrator 11g: A Hands-on Tutorial by Packt publishing.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: "A default jps-config.xml file named odi-jps-config-jse.xml is delivered with the installation of ODI Studio and located within the ODI_HOME\oracledi\client\odi\bin directory."
A block of code is set as follows:
<serviceProvider type="IDENTITY_STORE" name="idstore.ldap.provider" class="oracle.security.jps.internal.idstore.ldap.LdapIdentityStoreProvider">
<description>LDAP-based IdentityStore Provider</description>
</serviceProvider>Any command-line input or output is written as follows:
Map: oracle.odi.credmap Key:ODI_AGENT User_Name:CN=Admin Password:weblogic1
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: " Select File then New from the menu ".
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.