Thursday Sep 25, 2014

ODI 12c - Migration from OWB to ODI - PLSQL Procedures Pt 2

In the first part of this blog post I showed you how PLSQL procedures could be invoked from ODI and help you benefit from supporting such functionality in the tool and increasing performance. Here in part 2, I'll show how the map can be changed in OWB and subsequently migrated using the migration utility.

Remember the original mapping design in OWB used a transformation operator to invoke the PLSQL procedure, the same mapping can be modified by replacing that transformation component with a construct operator to build the ref cursor and also a table function operator, see below for the original map design at the top and the modified one below with the ref cursor and table function;


The mapping can be configured to be further optimized by specifying extraction and loading hints in order to tune performance for your system;


You will have to enable the parallel DML capabilities for the OWB mapping also. With this design you can test/ensure it works and then use it in a migration. Why go through all this? You may want to try and optimize existing mappings in OWB, or when migration your map may be more complex and you do not wish to reconstruct it (perhaps the mapping is large and complex upstream from the PLSQL procedure). Doing this will save that work, you need to remove the PLSQL procedure plus target attribute mappings and insert the cursor and table function.

When the migration utility migrates this, it will migrate the entire logical design and also the hints you have specified. You will see the following mapping in ODI;

You will have to add the enable parallel DML code into the begin mapping command and then the code will be equivalent and performing as such. For details of the OWB to ODI migration utility see here, it's also worth checking various other useful resources such as the migration in action demo here and Stewart Bryson's 'Making the Move from Oracle Warehouse Builder to Oracle Data Integrator 12c' article here (useful tip on database link mechanics in ODI).

ODI 12c - Migrating from OWB to ODI - PLSQL Procedures

The OWB to ODI migration utility does a lot, but there are a few things it doesn't handle today. Here's one for our OWB customers moving to ODI who are scratching their heads on the apparent lack of support for PLSQL procedures in ODI... With a little creative work you can not only get those mappings into ODI but there is potential to dramatically improve performance (my test below improves performance by 400% and can be easily further tuned based on hardware).

This specific illustration will cover the support for PLSQL procedures (not functions) in OWB and what to do in ODI, OWB takes care of invoking PLSQL procedures mid-flow - it did this by supporting PLSQL row-based code for certain (not all) map designs out of the box - PLSQL procedure invocation was one case this was done. The PLSQL that OWB generated was pretty much as efficient as it could have been for PLSQL (used bulk collect and many other best practices you didn't have to worry about as a map designer) but it was limited in the logical map support (couldn't have a set-based operator such as join after a row based only operator such as a PLSQL transformation) - it was also PLSQL not SQL.

Here we see how with a simple pipelined, parallel enabled table function wrapper around your PLSQL procedure call, how you capture the same design in ODI 12c and/or get the mapping migrated from OWB. I think the primary hurdle customers have is what is the option going forward. To solve this, we will just leverage more of the Oracle database; table functions and parallelize the <insert your favorite word> out of it!

The mapping below calls a PLSQL procedure and OWB generated PLSQL row based code for this case, the target is getting loaded with data from the source table and the 2 output parameters of the PLSQL procedure;

When you try and migrate such a mapping using the OWB to ODI migration utility, you'll get a message indicating that the map cannot be migrated using the utility. Let's see what we can do! The vast majority of mappings are set-based, generally a very small subset are row based PLSQL mappings. Let's see how this is achieved in ODI 12c.

I did a test using the generated code from OWB - no tuning just the raw code for the above mapping - it took ">">">12 minutes 32 seconds to process about 32 million rows and invoke the PLSQL procedure and perform a direct path insert into the target. With my ODI 12c design using a very simple table function wrapper around the PLSQL procedure I can cut the time to 3 minutes 14 seconds!! Not only can I do this, but I can easily further optimize it to better leverage the Oracle database server by quickly changing the hints - I had a 4 processor machine, so that's about as much as I could squeeze out of it.

Here is my map design in ODI 12c;

The table function wrapper to call the PLSQL procedure is very simple, line 7 is where I call the PLSQL procedure, I use the object instance in the call and pipe the data when the call is made;

  1. create or replace function TF_CALL_PROC(input_values sys_refcursor) return TB_I_OBJ pipelined parallel_enable(PARTITION input_values BY ANY) AS
  2.   out_pipe i_obj := i_obj(null,null,null,null,null,null,null,null,null);
  3. BEGIN
  4.   LOOP
  5.     FETCH input_values INTO out_pipe.prod_id, out_pipe.cust_id,out_pipe.time_id,out_pipe.channel_id,out_pipe.promo_id,out_pipe.quantity_sold,out_pipe.amount_sold;
  6.     EXIT WHEN input_values%NOTFOUND;
  7.     MYPROC(out_pipe.prod_id,out_pipe.status,out_pipe.info);
  8.     PIPE ROW(out_pipe);
  9.   END LOOP;
  10.   CLOSE input_values;
  11.   RETURN;
  12. END;

This is a very simple table function (with enough metadata you could generate it), it uses table function pipelining and parallel capabilities - I will be able to parallelize all aspects the generated statement and really leverage the Oracle database. The above table function uses the types below, it has to project all of the data used downstream - whereas OWB computed this, you will have to do that.

  1. create or replace type I_OBJ as object (
  2.  prod_id number,
  3.  cust_id number,
  4.  time_id date,
  5.  channel_id number, 
  6.  promo_id number,
  7.  quantity_sold number(10,2),
  8.  amount_sold number(10,2),
  9.  status varchar2(10),
  10.  info number
  11.   );
  12. create or replace type TB_I_OBJ as table of I_OBJ; 

The physical design in ODI has the PARALLEL(4) hints on my source and target and I enable parallel DML using the begin mapping command within the physical design.

You can see in above image when using Oracle KMs there are options for hints on sources and targets, you can easily set these to take advantage of the hardware resources, tweak these to pump the performance throughput!

To summarize, you can see how we can leverage the database to really speed the process up (remember the 400%!), also we can still capture the design in ODI and on top of that unlike in OWB, this approach let's us carry on doing arbitrary data flow transformations after the table function component which is invoking our PLSQL procedure - so we could join, lookup etc. Let me know what you think of this, I'm a huge fan of table functions I think they afford a great extensibility capability.  

Wednesday Sep 17, 2014

Cloud Data Integration Sessions at Oracle OpenWorld

It’s almost time for Oracle OpenWorld and I would like to give you a sneak peek into some of the cloud data integration sessions we are hosting this year.

On Tuesday September 30th we will start with ‘Oracle GoldenGate and the Cloud [CON7774]’. Chai Pydimukkala and Pawan Kumar Kumar will be discussing how Oracle GoldenGate can run in the cloud and be used in the Oracle Cloud.

To learn more about it join us for this session:
Oracle GoldenGate and the Cloud - CON7774
09/30/14 (Tuesday) 12:00 PM - Moscone South - 302

Then on Thursday September 2nd I will have the pleasure to co-present ‘Oracle Data Integration: A Crucial Ingredient for Cloud Integration [CON7926]’ with Sumit Sarkar from Progress DataDirect. In this session we will be explaining how the Oracle Data Integration solutions help businesses integrate with cloud resources such as cloud databases and applications or cloud BI infrastructures. We will cover topics like initializing and synchronizing Oracle DBaaS or performing native E-LT transformations on a cloud data warehousing platform.

We are also very excited to be doing 2 live demonstrations in this session using Oracle Data Integrator and the Progress DataDirect JDBC drivers: extracting data from Salesforce.com as well as integrating and transforming data on Amazon Redshift.

If you are interested in cloud integration make sure you attend this session:
Oracle Data Integration: A Crucial Ingredient for Cloud Integration - CON7926

10/2/14 (Thursday) 9:30 AM - Moscone South - 270

Of course there are also plenty of other Oracle Data Integration sessions you should plan on attending at Oracle OpenWorld this year. For a complete list review our Focus On Data Integration website. See you there!

Tuesday Sep 09, 2014

ODI 12c - Models in Data Modeler and ODI

Ever wondered how to get your models from Oracle SQL Developer Data Modeler (SDDM) in to ODI data models? The most common practice is to generate and execute the physical DDL scripts from SDDM to the target database. Another technique is possible leveraging the ODI SDK and the SDDM SDK - that's what I will illustrate here. There is an example script posted here on the java.net site.

There is an end to end demo viewlet below to see the script in action, check it out;

https://blogs.oracle.com/dataintegration/resource/viewlets/odi_models_from_sddm_viewlet_swf.html

The viewlet shows the transformation script in action creating the ODI model from the Data Modeler design, here's a peek at the script in action;

In the viewlet you will see how I added the groovy scripting engine as an engine for SDDM - I can then leverage my groovy skills and the ODI SDK to build the script and provide useful capabilities.

This script is publicly available at link above, take, enhance and comment! Join the communities on LinkedIn for Oracle Data Integration and the OTN forum to learn and exhange with other members of the community.

Wednesday Aug 06, 2014

OWB to ODI 12c Migration in action

The OWB to ODI 12c migration utility provides an easy to use on-ramp to Oracle's strategic data integration tool. The utility was designed and built by the same development group that produced OWB and ODI.

Here's a screenshot from the recording below showing a project in OWB and what it looks like in ODI 12c;


There is a useful webcast that you can play and watch the migration utility in action. It takes an OWB implementation and uses the migration utility to move into ODI 12c.

http://oracleconferencing.webex.com/oracleconferencing/ldr.php?RCID=df8729e0c7628dde638847d9511f6b46

It's worth having a read of the following OTN article from Stewart Bryson which gives an overview of the capabilities and options OWB customers have moving forward.
http://www.oracle.com/technetwork/articles/datawarehouse/bryson-owb-to-odi-2130001.html

Check it out and see what you think!

Monday Jul 28, 2014

New Security Enhancements in ODI 12.1.3

Oracle Data Integrator now uses Advanced Encryption Standard (AES) as the standard encryption algorithm for encrypting ODI objects such as Knowledge Modules, procedures, scenarios or actions as well as any password.

You can configure the encryption algorithm and key length to meet your specific requirements. By default ODI uses AES-128 but you can also use cryptographic keys of 192 and 256 bits.

Passwords and other sensitive information included in repository exports are also now encrypted and secured by an export key. This export key must be provided when importing the exported content, if it is not provided all the sensitive information is removed from the imported object.

You can find more information about these security enhancements in the ODI 12.1.3 documentation: Advanced Encryption Standard


This is just one of the many new features added to ODI 12.1.3! You can find a complete list in the following document: Oracle Data Integrator 12c New Features Overview.

Monday Jul 21, 2014

ODI 12.1.3: New Model and Topology Objects Wizard

Oracle Data Integrator 12.1.3 introduces a new wizard to quickly create Models. This wizard will not only help you create your Models more easily, if needed it will also create the entire required infrastructure in the ODI Topology: Data Servers, Physical and Logical Schemas.

In this blog article we will go through an example together and add a new Model to access the HR sample schema of an Oracle database. You can follow through this example using the ODI Getting Started VirtualBox image which is available here: http://www.oracle.com/technetwork/middleware/data-integrator/odi-demo-2032565.html

The ‘New Model and Topology Objects’ wizard can be accessed from the Models menu as shown below:

The wizard opens up and displays default settings. From there we can customize our objects before they actually get created in the ODI repositories.

In this example we want to access tables stored in the HR schema of an Oracle database so we name the Model ORACLE_HR. Note that the Logical Schema as well as the Schema and Work Schema fields in the Physical Schema section automatically default to the Model name:

Next we will give a new name to our Data Server: LINUX_LOCAL_ORACLE since we are connecting to a local Oracle database running on a Linux host.

We then fill in the User, Password and URL fields to reflect the environment we are in. To access the HR schema we use the ODI Staging area user which is ODI_STAGING. This is a best practice and it also ensures that the Work Schema field automatically gets updated with the right value for the Staging Area.

Note that the wizard also allows us to link a new Model to an existing Data Server.

Finally we click on Test Connection to make sure the parameters are correct.


Then we update the Schema field using the drop-down list to point to the HR schema at the database level.

Our Model is now fully set up, we click on OK to have it created along with its related Topology objects. The Model ORACLE_HR opens up allowing us to reverse-engineer the tables using the Selective Reverse-Engineering tab:

We pick all the tables and click on the Reverse Engineer button to start this process and save the Model at the same time. A new Model called ORACLE_HR was created as shown below as well as the appropriate objects in the Topology:


Thursday Jul 17, 2014

ODI 12c and Eloqua using DataDirect Cloud JDBC Driver

Sumit Sarkar from Progress DataDirect just posted a great blog on connecting to Eloqua in ODI 12c using the DataDirect Cloud JDBC driver. You can find the article here: http://blogs.datadirect.com/2014/07/oracle-data-integrator-etl-connectivity-eloqua-jdbc-marketing-data.html


The steps described in this tutorial also apply to other datasources supported by the DataDirect Cloud JDBC driver.

Sunday Jul 13, 2014

New Big Data Features in ODI 12.1.3

Oracle Data Integrator (ODI) 12.1.3 extends its Hadoop capabilities through a number of exciting new cababilities. The new features include:

  • Loading of RDBMS data from and to Hadoop using Sqoop
  • Support for Apache HBase databases
  • Support for Hive append functionality
With these new additions ODI provides full connectivity to load, transform, and unload data in a Big Data environment.

The diagram below shows all ODI Hadoop knowledge modules with KMs added in ODI 12.1.3 in red. 

Sqoop support

Apache Sqoop is designed for efficiently transferring bulk amounts of data between Hadoop and relational databases such as Oracle, MySQL, Teradata, DB2, and others. Sqoop operates by creating multiple parallel map-reduce processes across a Hadoop cluster and connecting to an external database and transfering data from or to Hadoop storage in a partitioned fashion. Data can be stored in Hadoop using HDFS, Hive, or HBase. ODI adds two knowledge modules IKM SQL to Hive- HBase-File (SQOOP) and IKM File-Hive to SQL (SQOOP).

Loading from and to Sqoop in ODI is straightforward. Create a mapping with the database source and hadoop target (or vice versa) and apply any necessary transformation expressions.

In the physical design of the map, make sure to set the LKM of the target to LKM SQL Multi-Connect.GLOBAL and choose a Sqoop IKM, such as  IKM SQL to Hive- HBase-File (SQOOP). Change the MapReduce Output Directory IKM property MAPRED_OUTPUT_BASE_DIR to an appropriate HDFS dir. Review all other properties and tune as necessary. Using these simple steps you should be able to perform a quick Sqoop load. 

For more information please review the great ODI Sqoop article from Benjamin Perez-Goytia, or read the ODI 12.1.3 documentation about Sqoop.

HBase support

ODI adds support for HBase as a source and target. HBase metadata can be reverse-engineered using the RKM HBase knowledge module, and HBase can be used as source and target of a Hive transformation using LKM HBase to Hive and IKM Hive to HBase. Sqoop KMs also support HBase as a target for loads from a database. 

For more information please read the ODI 12.1.3 documentation about HBase.

Hive Append support

Prior to Hive 0.8 there had been no direct way to append data to an existing table. Prior Hive KMs emulated such logic by renaming the existing table and concatenating old and new data into a new table with the prior name. This emulated append operation caused major data movement, particularly when the target table has been large.

Starting with version 0.8 Hive has been enhanced to support appending. All ODI 12.1.3 Hive KMs have been updated to support the append capability by default but provide backward compatibility to the old behavior through the KM property HIVE_COMPATIBLE=0.7. 

Conclusion

ODI 12.1.3 provides an optimal and easy-to use way to perform data integration in a Big Data environment. ODI utilizes the processing power of the data storage and processing environment rather than relying on a proprietary transformation engine. This core "ELT" philosophy has its perfect match in a Hadoop environment, where ODI can provide unique value by providing a native and easy-to-use data integration envionment.

Wednesday Jul 02, 2014

Learn more about ODI and Apache Sqoop

The ODI A-Team just published a new article about moving data from relational databases into Hadoop using ODI and Apache Sqoop. Check out the blog post here: Importing Data from SQL databases into Hadoop with Sqoop and Oracle Data Integrator (ODI)

Wednesday Jun 04, 2014

ODI 12c - Loading Files into Oracle, community post from ToadWorld

There's a complete soup to nuts post from Deepak Vohra on the Oracle community pages of ToadWorld on loading a fixed length file into the Oracle database. This post is interesting from a few fronts; firstly this is the out of the box experience, no specialized KMs so just basic integration from getting the software installed to running a mapping. Also it demonstrates fixed length file integration including how to use the ODI UI to define the fields and pertinent properties.

 Check the blog post out below....

http://www.toadworld.com/platforms/oracle/w/wiki/10935.loading-text-file-data-into-oracle-database-12c-with-oracle-data-integrator-12c.aspx

Hopefully you also find this useful, many thanks to Deepak for sharing his experiences. You could take this example further and illustrate how to load into Oracle using the LKM File to Oracle via External table knowledge module which will perform much better and also leverage such things as using wildcards for loading many files into the 12c database.

Friday May 30, 2014

Looking for Cutting-Edge Data Integration: 2014 Excellence Awards

2014 Oracle Excellence Awards Data Integration

It is nomination time!!!

This year's Oracle Fusion Middleware Excellence Awards will honor customers and partners who are creatively using various products across Oracle Fusion Middleware. Think you have something unique and innovative with one or a few of our Oracle Data Integration products?

We would love to hear from you! Please submit today.

The deadline for the nomination is June 20, 2014.

What you win:

  • An Oracle Fusion Middleware Innovation trophy

  • One free pass to Oracle OpenWorld 2014

  • Priority consideration for placement in Profit magazine, Oracle Magazine, or other Oracle publications & press release

  • Oracle Fusion Middleware Innovation logo for inclusion on your own Website and/or press release

Let us reminisce a little…

For details on the 2013 Data Integration Winners:

Royal Bank of Scotland’s Market and International Banking and The Yalumba Wine Company, check out this blog post: 2013 Oracle Excellence Awards for Fusion Middleware Innovation… and the Winners for Data Integration are…

and for details on the 2012 Data Integration Winners:

Raymond James and Morrisons, check out this blog post: And the Winners of Fusion Middleware Innovation Awards in Data Integration are… 

Now to view the 2013 Winners (for all categories).

We hope to honor you!

Here's what you need to do: 

Click here to submit your nomination today.  And just a reminder: the deadline to submit a nomination is 5pm Pacific Time on June 20, 2014.

Thursday May 15, 2014

Oracle Data Integrator Webcast Archives

Have you missed some of our Oracle Data Integrator (ODI) Product Management Webcasts?

Don’t worry – we do record and post these webcasts for your viewing pleasure. Recent topics include Oracle Data Integrator (ODI) and Oracle GoldenGate Integration, BigData Lite, the Oracle Warehouse Builder (OWB) Migration Utility, the Management Pack for Oracle Data Integrator (ODI), along with other various themes focused on Oracle Data Integrator (ODI) 12c. We run these webcasts monthly, so please check back regularly.

You can find the Oracle Data Integrator (ODI) Webcast Archives here.

And for a bit more detail:

The webcasts are publicized on the ODI OTN Forum if you want to view them live.  You will find the announcement at the top of the page, with the title and details for the upcoming webcast.

Thank you – and happy listening!

Monday May 12, 2014

Check it out – BI Apps 11.1.1.8.1 is now available!

As of May 8, 2014, Oracle Business Intelligence (BI) Applications 11.1.1.8.1 is available on the Oracle Software Delivery Cloud (eDelivery), and on the Oracle BI Applications OTN page. This is the second major release on the 11g code line leveraging the power of Oracle Data Integrator (ODI), and certified with the latest version of Oracle BI Foundation 11.1.1.7. For more details on this release and what’s new – check it out!

Friday May 02, 2014

3 Key Practices For Using Big Data Effectively for Enhanced Customer Experience

As organizations focus on differentiating their offering via superior customer experience, they are looking into ways to leverage big data in this effort. Couple of weeks ago my colleague Pete Schutt and I hosted a webcast on this very topic: Turning Big Data into Real-Time Action for a Greater Customer Experience

In this webcast we talked about 3 key practices to make the most out of big data for improving customer experience, which are:

  1. Know your customer leveraging big data: Leverage all relevant data (internal and external; structured, semi-structured, and unstructured) to understand and predict customers needs & preferences accurately.
  2. Capture, analyze, act on data fast to create value: Achieve accurate insight and take the right action fast so your action can be still relevant to the customer’s situation.
  3. Empower employees & systems with insight & smarter decisions: In this step you ensure that the capability to act right and fast is not limited to a few in the organization, but everyone and every system that interacts and influences customers’ experience.


After explaining why these practices are critical to improving customer experience, we discussed Oracle’s complete big data analytics and management platform, as well as the specific products and architectural approaches to execute on these 3 key areas. We focused particularly on data integration for fast and timely data acquisition and business analytics for real-time insight and action, and how they fit together in a real-time analytics architecture.

You can watch this webcast now on demand via the link below:

Turning Big Data into Real-Time Action for a Greater Customer Experience

In this webcast we received many great questions and I have provided below a few of them along with the answers.

Is real-time action related to the Internet of Things?

Yes, more physical things will be connected to the internet, often wirelessly with RFID tags or other sensors and Java to record where they are and what they are doing (or not doing). The IoT will be more practical by automating the information process from capture to analysis to appropriate and immediate action.

What does Oracle have for real-time mobile analytics?

Oracle BI Mobile App Designer empowers business users to easily create interactive analytical applications on any device without writing a single line of code and to also take action and respond to events in the context of their day-today business activities

Can these real-time systems be managed by business users?

Yes, you need the agility for business owners to be able to respond, experiment, and adapt, in real-time as the environment or consumer behavior changes. The systems have to be intuitive enough for users with the business content and context who can easily visualize, understand, and change the patterns they're looking and the rules that are being enforced.

Can the real-time systems use other statistical models or algorithms?

Yes. Oracle Advanced Analytics offer an enterprise version of R and Oracle RTD can source and publish scores from other advanced analytical models such as R, SAS, or SPSS or others.

Where do we get more information about ODI for big data?

 You can start with Oracle Data Integrator Application Adapter for Hadoop. And also take a look at the  Oracle BigDataLite Virtual Machine, a pre-built environment to get you started on an environment reflecting the core software of Oracle's Big Data Appliance 2.4. BigDataLite is a VirtualBox VM that contains a fully configured Cloudera Hadoop distribution CDH 4.5, an Oracle DB 12c, Oracle's Big Data Connectors, Oracle Data Integrator 12.1.2, and other software. You can use this environment to see ODI 12c in action integrating big data with Oracle DB using ODI's declarative graphical design, efficient EL-T loads, and Knowledge Modules designed to optimize big data integration. 

For GoldenGate, can a target be something other than a database, e.g. queue?

Yes, GoldenGate can deliver database changes into JMS message queues and topics, as well as in flat file format. Oracle GoldenGate Application Adapters would need to be used for those use cases. For low-impact real-time data integration into Hadoop systems customers will need to use the Java Adapter within this GoldenGate Application Adapters license as well.

What other data warehouses can does Oracle support for real-time data integration?

Oracle's data integration offering is heterogeneous for both sources and targets. Both Oracle Data Integrator and Oracle GoldenGate work with non-Oracle data warehouses including Teradata, DB2, Netezza, Greenplum.

I invite you to watch this webcast on demand to hear the details of our solution discussion and the Q&A with the audience. For more information big data integration and analytics you can review Bridging Two Worlds. Big Data and Enterprise Data and Big Data @ Work Turning Customer Interactions into Opportunities.

·


About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
7
11
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  
       
Today