Data Integration and Management

As some of you may know there are a couple of ways to view the ODI sessions information in Operator. You can sort the ODI sessions by date, by user, by agent but most users don't know that you can also sort the sessions by keywords or tags.

Keywords enable users to assign tags to specific ODI scenarios, the keywords could be anything from 'Data Warehouse' to a server name like 'Server_XYZ'. Using keywords makes it easier to identify specific executions in Operator. You can have more than one keyword assigned to a single ODI scenario.

Operator

Prior to using keywords, users have to create session folders in Operator.
Open up Operator and right-click on the Keywords node in the Session List or Hierarchical Sessions list.
Then select Insert Session Folder.

A new window will pop up, enter a Folder Name and specify the keywords assigned to this folder.

One folder can be using multiple keywords as shown above. All the keywords must be matched when you specify multiple keywords.

When a session is executed ODI analyzes the keywords specified in the scenario execution and will try to match them with the keywords assigned at the Session Folder level. Then the sessions will appear in the appropriate session folders.

You can view some examples of how ODI matches the session folder keywords in the ODI Documentation.

Designer

ODI users can specify keywords when launching scenarios using an Operating System command: startscen or using the OdiStartScen tool in the package toolbox as shown below.
In this example I am using the OdiStartScen tool with the following keywords: DWH, Retail Source and Oracle Target.

Refer to the ODI Documentation for additional information regarding the startscen syntax or the OdiStartScen tool.

In our example, we used DWH, Oracle Target and Retail as our session keywords so we will see our session appearing under the Oracle session folder but not under the DWH session folder since we didn't specify all the keywords required by the DWH session folder.

George Verveniotis is a Senior Business Intelligence and Data Warehouse Consultant for Oracle in Greece.

He was recently responsible for the design and implementation of a very large data-integration project for a major telecommunication provider. The main purpose of the project was to develop with ODI the necessary synchronization and integration between Oracle Peoplesoft and Oracle e-Business Suite.

One key challenge for any data integration project is to properly estimate how much time will be required for a successful implementation.

Combining his extensive experience with academic research, George has condensed his recommendations in this article, which will detail the best practices you will need to be as successful as he is.

I must admit that he mentions shortcomings and pitfalls that I have personally witnessed in too many integration projects.

Thanks George for sharing this with us!

-Christophe Dupupet

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Data Store and Logical Architecture are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.

In a previous post I have described how to create in Topology Manager a connection to an Oracle Business Intelligence (OBI) Server. Now we will see how we can use Oracle BI Server as a data source in an ODI process.

Creating a Model for Oracle BI Server

The first step is to create a model in Designer.
Click on the Insert Model button to create a new model in the Models view.

Give it a name, I used OBIEE_EXAMPLE for this post.

Set the Technology to Oracle BI Server and the Logical Schema to the one you created in Topology previously, I used BIEE_LSCHEMA.

Now click on the Reverse tab and set the Context to your default context, I used Development in this post.

Click on the Selective Reverse tab then click on the Selective Reverse checkbox and finally click on the Objects to Reverse checkbox, a list of tables should appear. Pick all the tables you are interested in and then click on the Reverse button to start the reverse engineering process.

Verifying your Oracle BI Server Datastores

You should now see a list of datastores under your OBIEE_EXAMPLE model.

Right-click on one of them and select View Data to display the content of the BI Server artifact and make sure everything is set up correctly.

Prior to using those datastores in your interfaces make sure that the columns datatypes are set correctly. If a datatype is missing, open up the datastore, go to the Columns tab and select the corresponding datatype in the drop-down list.

You can now use those data stores in any ODI interface.

The LKM SQL to Oracle can be used to load data from OBI Server to an Oracle database, for other databases use the appropriate Loading Knowledge Module for that technology.

If you have issues joining columns with spaces, make sure that Object case-sensitive is enabled for the Oracle BI Server technology in Topology Manager.

Fragmented data silos got you feeling a bit dull? Checkout our upcoming webcast...

I'll be in a discussion with Forrester's Rob Karel, about new trends in real-time data integration, including how analytical architectural best practices aligned with solutions in business intelligence can foster better customer relationships, reduce risk, and improve profitability.

What we're going to cover:

• The benefits of using real-time data integration as a foundation for pervasive business intelligence.
• How analytical architecture approaches are compared, including how consolidation, data federation, data services, and master data management solutions can provide a complete view of enterprise data.
• How today's leading companies are making smarter decisions by delivering clean, accurate, and timely information to their employees.
• Best practices for using Oracle Data Integration products - including Oracle GoldenGate and Oracle Data Integrator Enterprise Edition - to deliver high-performance and reliable data across heterogeneous systems.

Register now for this free Webcast.

This post assumes that you have some level of familiarity with ODI. The concepts of Packages, Interfaces, Procedures and Scenarios are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information.

ODI: Parallel Processing

A common question in ODI is how to run processes in parallel. When you look at a typical ODI package, all steps are described in a serial fashion and will be executed in sequence.

However, this same package can parallelize and synchronize processes if needed.

PARALLEL PROCESSES

The first piece of the puzzle if you want to parallelize your executions is that a package can invoke other packages once they have been compiled into scenarios (the process of generation of scenarios is described later in this post). You can then have a master package that will orchestrate other scenarios. There is no limit as to how many levels of nesting you will have, as long as your processes are making sense: Your master package invokes a seconday package which, in turn invokes another package...

When you invoke these scenarios, you have two possible execution modes: synchronous and asynchronous.

A synchronous execution will serialize the scenario execution with other steps in the package: ODI executes the scenario, and only after its execution is completed, runs the next step.

An asynchronous execution will only invoke the scenario but will immediately execute the next step in the calling package: the scenario will then run in parallel with the next step. You can use this option to start multiple scenarios concurrently: they will all run in parallel, independently of one another.

SYNCHRONIZING PROCESSES

Once we have started multiple processes in parallel, a common requirement is to synchronize these processes: some steps may run in parallel, but at times we will need all separate threads to be completed before we proceed with a final series of steps. ODI provides a tool for this: OdiWaitForChildSession.

An interesting feature is that as you start your different processes in parallel, they can each be assigned a keyword (this is just one of the parameters you can set when you start a scenario). When you synchronize the processes, you can select which processes will be synchronized based on a selection of keywords.

ADDING SCENARIOS TO YOUR PACKAGE FOR PARALLEL PROCESSING

To add a scenario to your package, simply drag and drop the generated scenario in the package, and edit the execution parameters as needed. In particular, remember to set the execution mode to Asynchronous.

You can generate a scenario from a package, from an interface, or from a procedure. The last two will be more atomic (one interface or one procedure only per execution unit). The typical way to generate a scenario is to right-click on one of these objects and to select Generate Scenario.

The generation of scenarios can also be automated with ODI processes that would invoke the ODI tool OdiGenerateAllScen. The parameters of this tool will let you define which scenarios are being generated automatically.

In all cases, scenarios can be found in the object tree, under the object they were generated from - or in the Operator interface, in the Scenarios tab.

While you are developing your different objects, keep in mind that you can Regenerate existing scenarios. This is faster than deleting existing ones only to re-create them with the same version number. To re-generate a scenario, simply right-click on the existing version and select Regenerate ... .

From an execution perspective, you can specify that the scenario you will execute is version -1 (negative one) to ensure that the latest version number is always the one executed. This is a lot easier than editing the parameters with each new release.

DISPLAYING PARALLEL PROCESSING

You will notice that as of 10.1.3.4, ODI does not graphically differentiate between serialized and parallelized executions: all are represented in a serial manner. One way to make parallel executions more visible is stack up the objects vertically, versus the more natural horizontal execution for serialized objects. (If we have electricians reading this, the layout will be very familiar to them, but this is only a coincidence...)

OTHER OBJECTS THAN SCENARIOS

Scenarios are not the only objects that will allow for parallel (or Asynchronous) execution. If you look at the ODI tool OdiOSCommand, you will notice a Synchronous option that will allow you to define if the external component you are executing will run in parallel with the current process, or if it will be serialized in your process. The same is true for the Data Quality tool OdiDataQuality.

EXECUTION LOGS

As you will start running more processes in parallel, be ready to see more processes being executed concurrently in the Operator interface. If you are only interested in seing the master processes though, the Hierarchy tab will allow you to limit your view to parent processes. Children processes will be listed under the entry Childres Sessions under each session.

Likewise, when you access the logs from the web front end, you can view the Parent processes only.

Enjoy!

Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

If you listened to our webcast today: Introducing Oracle GoldenGate, Real-time Data Integration and Continuous Availability with Hasan Rizvi and Juan Loaiza, you heard us mention about some of the key performance, heterogeneity and reliability benefits.

Now you can download the Oracle GoldenGate software here on our website and check it out for yourself. We've included some of the common platforms and Oracle sources and targets to get you started, but as we pointed out in our I-seminar we have support for many other sources and target environments:

• Oracle GoldenGate


• Oracle GoldenGate Veridata


• Management Pack for Oracle GoldenGate

Click here for more information about Oracle GoldenGate.

This post assumes that you have some level of familiarity with ODI. The concepts of Knowledge Module are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information..

At the core, ODI knowledge modules are templates of code that will be leveraged for data integration tasks: they pre-define data integration steps that are required to extract, load, stage - if needed - and integrate data.

Several types of Knowledge Modules are available, and are grouped in families for Loading operations (LKMs), Integration operations (IKMs), Data Control operations (CKMs), and more.

For a more detailed description of what a knowledge module is, simply picture the multiple steps required to load data from a flat file into a database. You can connect to the file using JDBC, or leverage the database native loading utility (sqlldr for Oracle, bcp for SQL Server or Sybase, load for db2, etc.). External tables are another alternative for databases that support this feature.
As you use one or the other technique, you may first want to stage the data before loading your actual target table; in other cases, staging will only slow down your data load.

As far as the integration in the target system is concerned, again multiple strategies are available: simple inserts, inserts and updates, upserts, slowly changing dimension... these techniques may be as simple as one step, or be a complex series of commands that must be issued to your database for proper execution.

The Knowledge Modules will basically list these steps so that a developer who needs to repeat the same integration pattern only has to select the appropriate templates, versus re-developing the same logic over and over again.

The immediate benefits of this approach are well known and well documented:
- All developers use the same approach, and code development is consistent across the company, hence guarantying the quality of the code
- Productivity is greatly improved, as proven path are re-used versus being re-developed
- Code improvement and modification can be centralized and has a much broader impact: optimization and regulatory changes are done once and inherited by all processes
- Maintenance is greatly simplified

To fully appreciate all the benefits of using knowledge Modules, there is a lot more that needs to be exposed and understood about the technology. This post is a modest attempt at addressing this need.

GENERATION OF CODE AND TRANSFORMATIONS

Most tools today will offer the ability to generate SQL code (or some other type of code, such as scripts) on your source or target system. As most products come with a transformation engine, they will also generate proprietary code for this engine where data is staged (I'll skip the debate here as to whether a transformation engine is a staging area or not - the point being that code can be generated on either source, "middle-tier" or target).

However, real life requirements are rarely either/or. Often times, it makes sense to leverage all systems to optimize the processing: spread out the load for the transformations, reduce the amount of data to be transferred over the network, process the data where it is versus moving the data around solely for the purpose of transformations.

To achieve this, Data Integration tools must be able to distribute the transformation logic across the different systems.

Only ODI will effectively generate code and transformations on all systems. This feature is only possible thanks to the KM technology.

Beyond the ability to generate code, you have to make sure that the generated code is the best possible code for the selected technology. Too often, tools first generate code that is then translated for the appropriate database. With the KMs technology, no translation is required: the generated code was initially conceived explicitly for a given technology, hence taking advantage of all the specifics of this technology.

And since the KMs are technology specific, there is no limit to what can be leveraged on the databases, including user defined functions or stored procedures.

CODE ADAPTABILITY

Whenever a tool generates code, the most common complaint is that there is very little (if any) control over the generated result. What if a simple modification of the code could provide dramatic performance improvements? Basic examples would include index management, statistics generation, joins management, and a lot more.

The KM technology is open and expansible so that developers have complete control over the code generation process. Beyond the ability to optimize the code, they can extend their solution to define and enforce in house best practices, and comply with corporate, industry or regulatory requirements. KMs Modifications are done directly from the developers graphical interface.

One point that can easily be adapted is whether data have to be materialized throughout the integration process. Some out-of-the-box KMs will explicitly land data in a physical file or tables. Others will avoid I/Os by leveraging pipes instead of files, views and synonyms instead of tables. Again, developers can adapt the behavior to their actual requirements.

EXPANDING THE TOOL TO NEW TECHNOLOGIES

How much time does it take to adapt your code to a new release of your database? How much time does it take to add a new technology altogether? In both cases, KMs will provide a quick and easy answer.

Let us start with the case of a new version of the database. While our engineering teams will release new KMs as quickly as possible to take advantage of the latest releases of any new database, you do not have to wait for them. A new release typically means new parameters for your DDL and DML, as well as new functions for your existing transformations. Adapt the existing KMs with the features you need, and in minutes your code is ready to leverage the latest and greatest of your database.

Likewise, if you ever need to define a new technology that would not be listed by ODI (in spite of the already extensive list we provide), simply define the behavior of this technology in the Topology interface, and design technology specific KMs to take advantage of the specific features of this database. I can guaranty you that 80% of the code you need (at least!) is already available in an existing KM... Thus dramatically reducing the amount of effort required to generate code for your own technology.

ARE KM MODIFICATIONS REQUIRED?

I am a strong advocate of the customization of KMs: I like to get the best I can out of what I am given. But often times, good enough is more than enough. I will always remember trying to optimize performance for a customer: we did not know initially what our processing window would be - other than "give us your best possible performance". The first out-of-the-box KM we tried processed the required 30,000,000 records in 20 minutes. Due to IT limitations, we could only leverage lesser systems for faster KMs... but still reduced performance to 6 minutes for the same volume of data. We started modifying KMs to get even better results, when the customer admitted that we actually had 3 hour for the process to complete... At this point, spending time in KM modifications was clearly not needed anymore.

KMs are meant to give the best possible performance out of the box. But every environment is unique, and assuming that we can have the best possible code for you before knowing your own specific challenges would be an illusion - hence the ability to push the product and the code to the limit

Another common question is: do you have to leverage both source and target systems as part of your transformations? Clearly, the answer is no. But in most cases, it is crucial to have the flexibility to leverage all systems, versus being cornered in using only one of them. Over time, you will want to reduce the volume of data transferred over the network; you will want to distribute some of your processing... all more reasons to leverage all available engines in your environment.

Do not hesitate and share with us how you extend your KMs!

Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

Through its recent acquisition of GoldenGate Software, Oracle has accelerated its product strategy to deliver a comprehensive data integration platform and increase uptime for customers' mission-critical systems. But how can your business profit from Oracle GoldenGate products - and how do they complement your existing Oracle investments?

Join us for this live Webcast hosted by Hasan Rizvi, Senior Vice President for Oracle Fusion Middleware Product Development and Juan Loaiza, Senior Vice President Systems Technology, to get the answers!

Register now for this event

You'll learn how you can apply Oracle GoldenGate's real-time data integration and heterogeneous data replication capabilities to:

• Improve business insight through real-time integrations to business intelligence and data warehousing
• Maximize data availability through multi-master database configurations
• Synchronize and distribute data using bi-directional replication
• Upgrade databases, hardware and applications with zero downtime
• Replicate data between Oracle and non-Oracle systems

To find out more about Oracle and GoldenGate goto: http://www.oracle.com/goldengate

I hope everyone enjoyed the blockbuster keynote with Arnold and Larry - my favorite quote of the night was from Gov. Shwarzeneggar in speaking how much he loved the tech industry:

"I love tech. I love all kinds of tech... and not just high tech but bio tech, green tech, clean tech. The tech industry has made me what I am today... I wouldn't have made my start in acting career without help from body building technology and of course food supplement technology!"

Ellison delivered a compelling keynote as well - especially interesting was his 10 million dollar challenge for anyone that could double the speed of Exadata.

So what about data integration? Well yesterday we had an action-packed day. Thanks to Rittman Mead for their Oracle Un-conference on Oracle Warehouse Builder, and special thanks to Paul Longhurst at Overstock.com for delivering an innovative best practice presentation on how to make Data Warehouses more active, more real-time and more efficient.

Today there's a session which I'm delivering with Haidong Song on Information Management: Combining the power of ODI and MDM together. I invite you to come to our session at

9AM in Moscone South, Room 304

For all those geeks like me who idolize both the innovators and the terminators in the world, today is your day. Day 3 promises to keynote both Larry Ellison and Gov. Schwarzenegger. And if you're like me - and I know you are - you've memorized all the one-liners from Commando and stapled them into your pocket protector, or I should say you've archived them on your iPhone and created ring tones out of them.

What else to do at OpenWorld today?

Don't forget to spend time at the Oracle GoldenGate and the Oracle Data Integration booths in Moscone West . And check out Overstock.com session which promises to be a good one on the how Overstock.com used both Oracle GoldenGate and Oracle Data Integration. Moscone West L3 in Room 3014

What else is up today? Well I'm definitely looking forward today to meet up with some friends and colleagues and discuss and share what we've been up to this year.

Don't forget to follow me on twitter: "Dainsworld" and check out my own #oow09 one-liners