Wednesday Feb 12, 2014

ODI 12c - Mapping SDK Auto Mapping

The ODI 12c release has the new flow based mapping designer, this comes with new concepts to make the mapping design as efficient as possible as well as the runtime execution of such! The 12c release also has a new SDK for mapping, the 11g SDK is still available for backwards compatibility, but if if you want to properly leverage the 12c release the new mapping designer and SDK is the way forward. I posted a bunch of SDK examples (here and here) which demonstrated different mapping designs - the examples were in groovy and the column/attribute level mapping expressions were all done explicitly, I did not illustrate any auto mapping capabilities. So... I thought I should do it here. In doing so I'll show some other APIs within the mapping area that are very useful.

The 12c release introduced mapping components and categorized such components so that we can minimize the column level mapping expressions. If you compare ODI 12c with OWB, ODI 12c has a lot less inter component and cross component information, OWB capture a lot of information in a very explicit manner (it was very verbose, concise, but verbose).

One of the useful capabilities in the UI is to perform auto mapping, the function createExpressions below will use all available in-scope attributes that are upstream from the target component and match with attributes in the component you are targeting. The match can be done by equality, ends or starts and ignore case or exact match. Quite a simple piece of code and you can see the use of the function getUpstreamLeafAttributes for components or even getUpstreamInScopeAttributes for connector points. Some components have multiple input connector points with different graphs, for example set component, other ones are simple.

  1. enum MatchTypes {EQUALS,SRCENDSWITH, TGTENDSWITH, SRCSTARTSWITH, TGTSTARTSWITH}
  2. enum MatchCaseTypes {MATCH,IGNORECASE}

  3. def createExpressions(component, conPoint, matchType, matchCaseType) { 
  4.   atts = null
  5.   if (conPoint != null)   atts = conPoint.getUpstreamInScopeAttributes()
  6.   else atts = component.getUpstreamLeafAttributes(component)
  7.   tatts = component.getAttributes()
  8.   for (MapAttribute tgt_attr : tatts) {
  9.     attr_str = tgt_attr.getName()
  10.     if (matchCaseType == MatchCaseTypes.IGNORECASE) {
  11.       attr_str = attr_str.toLowerCase()
  12.     }
  13.     sourceCol = null;
  14.     for (MapAttribute src_attr : atts) {
  15.       src_attr_str = src_attr.getName()
  16.       if (matchCaseType == MatchCaseTypes.IGNORECASE) {
  17.        src_attr_str = src_attr_str.toLowerCase()
  18.       }
  19.       if ( (matchType == MatchTypes.SRCENDSWITH && src_attr_str.endsWith( attr_str )) ||
  20.            (matchType == MatchTypes.SRCSTARTSWITH && src_attr_str.startsWith( attr_str )) ||
  21.            (matchType == MatchTypes.TGTSTARTSWITH && attr_str.startsWith( src_attr_str )) ||
  22.            (matchType == MatchTypes.TGTENDSWITH && attr_str.endsWith( src_attr_str )) ||
  23.            (matchType == MatchTypes.EQUALS && attr_str.equals( src_attr_str )) ) {
  24.        sourceCol = src_attr
  25.        break
  26.       }
  27.     }
  28.     if (sourceCol != null && conPoint != null)  tgt_attr.setExpression( conPoint, sourceCol, null )      
  29.     else if (sourceCol != null)  tgt_attr.setExpression( sourceCol )      
  30.   }
  31. }

You can then call this function on a datastore to auto map all attribute expressions in the component as follows;

  • createExpressions(tgtempDatastoreComponent, null,MatchTypes.EQUALS,MatchCaseTypes.MATCH);

To illustrate the set component, you can code the population of each connector point as follows;

  • createExpressions(setComponent, inConnectorPoint1,MatchTypes.EQUALS,MatchCaseTypes.MATCH);
  • createExpressions(setComponent, inConnectorPoint2,MatchTypes.EQUALS,MatchCaseTypes.IGNORECASE);

this will auto map the attributes in a set component for each connector point with different rules (just for illustration purposes). You can see below the result of calling these 2 functions on the set component for each connector point. All upstream in-scope attributes are considered.

These APIs to get scoping attributes make it simple to build customized accelerators for building expressions when auto mapping. Its a little different than in 11g, have a look at the examples I posted above and the snippets above, there's a lot you can do and its easy to utilize.

Wednesday Feb 05, 2014

Introducing Oracle GoldenGate 12c: Extreme Performance Simplified

Oracle GoldenGate 12c was released last fall with a long list of new features that simplify configuration and increase flexibility, while delivering easy-to-use, advanced solutions with multi-fold performance gain.  We have been discussing these new features in various blog posts including:

· Advanced Replication for The Masses – Oracle GoldenGate 12c for the Oracle Database.

· GoldenGate 12c - What is Coordinated Delivery?

· GoldenGate 12c - Coordinated Delivery Example

· Oracle GoldenGate 12c - Announcing Support for Microsoft and IBM

And you can find new white papers about the 12c release in the Oracle GoldenGate resource kit.

Following the executive video webcast launching Oracle Data Integrator 12c and Oracle GoldenGate 12c in November 2013, we have set up another webcast for Oracle GoldenGate 12c where our product management team discusses the key new features in more depth and takes live questions from the audience.

If you would like to learn more about GoldenGate 12c I invite you to join us on Feb 12th in a webcast with product experts. You can register for this free event via the link below.

Live Webcast: Introducing Oracle GoldenGate 12c: Extreme Performance Simplified

February 12, 2014 -  10am PT/ 1pm ET

If you have missed our executive launch webcast I highly recommend that you watch it on demand via the link below. It talks about the tighter integration between Oracle Data Integrator 12c and Oracle GoldenGate and features customer and partner speakers from SolarWorld, BT, and Rittman Mead Consulting.

On-Demand Video Webcast: Introducing 12c for Oracle Data Integration

Thursday Jan 30, 2014

Webcast - Oracle Data Integrator 12c and Oracle Warehouse Builder

If you missed the recent Oracle Data Integrator 12c and Oracle Warehouse builder live webcast do not worry. You can still catch up on the events and connect with us with your feedback here. Here we discuss customer examples,ODI12c new features, Big Data compatibility, Oracle Warehouse Builder Migration Utility and Support and live Q and A among other topics.  

Register to listen. 

ODI12c and OWB Webcast.

Here are some questions from the audiences that were answered following the webcast.

Question

Response

Is the management pack free to ODI customers?

While the Management Pack for ODI works to consolidate and manage your ODI infrastructure it is a separately licensed product.  

- You can learn more about the Management Pack for ODI in this Data Sheet

Also visit the Oracle Management Pack for ODI homepage for more details.

Where can we download ODI 12c?

The ODI 12c can be downloaded in various ways including Virtual Machines and complete downloads from the Oracle Technology Network.  

This page also gives the latest on trainings, patches and other happenings around Oracle Data Integrator so keep this page bookmarked to follow the latest on ODI.

Thank you.

Happy to help, please let me know if you need any other assistance. Thank you for your participation.

Could you expand on how the time reduction is effected through parallelism?

As you might have suspected the time reductions depends on various factors including database tunings, network capabilities and code efficiencies.

ODI’s parallelism offers parallel threading and intelligent identification of parallelizable components.

This blog on the in-session parallelism functionality explains the workings and logic behind the process.

What has Oracle done to ensure any applications written today using existing KM's will work going forward for any ODI upgrades?

While we do our best to ensure consistency across the versions with Knowledge Modules - any Knowledge Modules you have changed would require additional testing to make sure everything works as it should.

Customization allows your Knowledge Modules to work most efficiently but also means changes from the out of the box KMs provided. Unfortunately there is no simple/easy answer outside of further testing.

Could you tell more about integrations to Workday? (real-time)

Workday integration is currently provided through some of our partners , mostly with customized Knowledge Modules.  For help in contacting such a partner, please reach out to your Oracle Data Integration Sales Manager.

Does Oracle Business Intelligence Application 11.1.1.7.1 offer a choice to use ODI or a third party DAC?

Oracle BI Apps version 11.1.1.7.1 which was released in May of 2013 was built on Oracle Data Integrator and fully functions only on ODI.

Is ODI 12c Generally Available?

Yes, ODI 12c has been generally available since October of 2013. For more information on the product please go to the ODI homepage.


You can also download the product here

Can you provide high level differences between Oracle Data Integrator and Golden Gate ?

At a high level, ODI is bulk data movement and transformation - known often as ETL/ELT. 

OGG is a high-volume, low latency replication/synchronization product.  For more information - please view the various datasheets, whitepaper on the two products that can both be found here.

What versions of E-Business Suite does ODI 12c require?

The certification matrix gives an overview of the compatible technologies and standards for ODI. 

You can also find more information in the E business documentation

Do you support connection, extraction and upload from QuickBase and into Quickbase in ODI 12c ?

For Intuit QuickBase there is an ODBC driver which might work in conjunction with the generic SQL KMs shipped with ODI. 

More information can be found here: http://qunect.com/. 

Please note that QuickBase is not certified with ODI. For a full set of certified products refer to the certification matrix.

Is it correct that there is no Oracle Warehouse Builder in 12c ?

That is correct. From a packaging perspective, OWB is not shipped with Oracle DB 12c, however, previous OWB releases can connect to Oracle DB 12c.  

The Statement of Direction around ODI and OWB explains the reasons and direction forward for both the products.

Great Webinar - would love to see more of these

Great! The feedback has been great for all our audiences and we shall definitely find ways to bring ODI to you. Meanwhile we invite you to follow our blogs and facebook channels.

Wednesday Jan 29, 2014

ODI 12.1.2 Demo on the Oracle BigDataLite Virtual Machine

Oracle's big data team has just announced 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. 

The sample data contained in BigDataLite represents the fictional Oracle MoviePlex on-line movie streaming company. The ODI sample performs the following two steps:

  • Pre-process application logs within Hadoop: All user activity on the MoviePlex web site is gathered on HDFS in Avro format. ODI is reading these logs through Hive and processes activities by aggregating, filtering, joining and unioning the records in an ODI flow-based mapping. All processing is performed inside Hive map-reduce jobs controlled by ODI, and the resulting data is stored in a staging table within Hive.
  • Loading user activity data from Hadoop into Oracle: The previously pre-processed data is loaded from Hadoop into an Oracle 12c database, where this data can be used as basis for Business Intelligence reports. ODI is using the Oracle Loader for Hadoop (OLH) connector, which executes distributed Map-Reduce processes to load data in parallel from Hadoop into Oracle. ODI is transparently configuring and invoking this connector through the Hive-to-Oracle Knowledge Module.

Both steps are orchestrated and executed through an ODI Package workflow. 

Demo Instructions

Please follow these steps to execute the ODI demo in BigDataLite:

  1. Download and install BigDataLite. Please follow the instructions in the Deployment Guide at the download page
  2. Start the VM and log in as user oracle, password welcome1.
  3. Start the Oracle Database 12c by double-clicking the icon on the desktop.


  4. Start ODI 12.1.2 by clicking the icon on the toolbar.


  5. Press Connect To Repository... on the ODI Studio window. 


  6. Press OK in the ODI Login dialog.


  7. Switch to the Designer tab, open the Projects accordion and expand the projects tree to Movie > First Folder > Mappings. Double-click on the mapping Transform Hive Avro to Hive Staging.


  8. Review the mapping that transforms source Avro data by aggregating, joining, and unioning data within Hive. You can also review the mapping Load Hive Staging to Oracle the same way. 

    (Click image for full size)

  9. In the Projects accordion expand the projects tree to Movie > First Folder > Packages. Double-click on the package Process Movie Data.


  10. The Package workflow for Process Movie Data opens. You can review the package.


  11. Press the Run icon on the toolbar. Press OK for the Run and Information: Session started dialogs. 




  12. You can follow the progress of the load by switching to the Operator tab and expanding All Executions and the upmost Process Movie Data entry. You can refresh the display by pressing the refresh button or setting Auto-Refresh. 


  13. Depending on the environment, the load can take 5-15 minutes. When the load is complete, the execution will show all green checkboxes. You can traverse the operator log and double-click entries to explore statistics and executed commands. 

This demo shows only some of the ODI big data capabilities. You can find more information about ODI's big data capabilities at:


Wednesday Jan 22, 2014

Deep Dive Into Oracle Data Integrator Changed Data Capture Leveraging Oracle GoldenGate

Check out this blog post below from Christophe – first the details related to Oracle Data Integrator’s (ODI) Journalizing Knowledge Modules (JKMs) as well as a deeper dive into the particulars around the seamless out-of-the-box integration between Oracle Data Integrator (ODI) and Oracle GoldenGate.

http://www.ateam-oracle.com/understanding-the-odi-jkms-and-how-they-work-with-oracle-goldengate/

Happy reading!

Thursday Jan 16, 2014

A preview of Oracle Data Integrator 12c and Oracle Warehouse Builder Webcast - Jan 23rd

It has been a busy close to 2013 with Oracle Data Integration announcing a lot of significant releases in its product line. Oracle Data Integrator 12c (ODI12c) was released along with Oracle Golden Gate. Then came the release of the Management pack for Oracle Data Integrator in December. This year the availability of the migration utility to migrate Oracle Warehouse Builder objects to Oracle Data Integrator was announced. Join us as we discuss in detail all these products, their workings and features on the 23rd of January. Below is a quick preview of the contents of the webcast.

With its declarative design and flow based design for the user, boosted performance for the administrators and enterprise architects and increased integration with enterprise software ODI12c continues to deliver on its performance and productivity based themes.

Hadoop support is enhanced through Knowledge Modules for Hadoop standards, connectivity support to load data into HDFS, generate HiveQl and more. With its extended heterogeneous connectivity ODI12c ensures full connectivity for all enterprise data. This release also sees tighter integration points with Oracle GoldenGate for real time data requirements.

With ODI becoming the strategic ELT technology for Oracle, the OWB migration utility was made available late 2013 to provide a phased migration approach for our OWB customers to ODI. Tighter integration between ODI and OWB also allows run time integration (managing of OWB repository from ODI topology, execution and monitoring of OWB jobs from  ODI) between the two technologies.  We discuss the objects supported, gotchas, supported versions and support policies for the migration in the webcast.

The announcement of the availability of Management pack for Oracle Data Integrator now allows for consolidation of monitoring and managing of all your ODI infrastructure.

We invite you to join us as we discuss these developments in ODI and to get your questions answered by an expert panel.


Thursday Jan 09, 2014

ODI - High performance data movement using Datapump

Great blog post below by Ben from the A-Team on improved Datapump support in ODI. As well as a great write up the KM is posted on the https://java.net/projects/oracledi site too.

http://www.ateam-oracle.com/using-oracle-data-pump-in-oracle-data-integrator-odi/

Feedback appreciated on this.

Tuesday Jan 07, 2014

Oracle GoldenGate 12c - Announcing Support for Microsoft and IBM

Oracle GoldenGate 12c further advances Oracle's leadership in real-time, heterogenous data replication technology with simplified configuration, extreme performance, and improved high availability solutions.  Continuing our release process Oracle GoldenGate 12c, we are pleased to announce the support for Microsoft SQL Server 2008/2012, IBM DB2 LUW, and IBM DB2Remote.  This release includes optimizations for each platform, capture, and intelligent, coordinated delivery capabilities.   The product delivers seamless transition to private cloud and real-time data replication between databases on public cloud and on premises environments to meet businesses current and future-needs.    Keep tuned for additional announcements as more platforms are released. 

Media is available for download on the Oracle Technology Network (OTN), and the Oracle Software Delivery Cloud (OSDC).


Monday Jan 06, 2014

Low Downtime E-Business Suite Migrations using Oracle GoldenGate

Who likes to work work weekends and holidays to migrate a production database to new hardware or to upgrade to a new version?      In many cases working the weekend is unavoidable and often these types of activities have to be done while minimizing business activities that generating revenue.   But let’s face it, current migration methods haven’t changed a whole lot in twenty years;  take the system down Friday night, migrate the data to the new system in bulk or upgrade the database in place,   bring the system back up, unit test and hope Monday morning that everything works.    The techniques have changed and things have definitely improved on the efficiency side of the equation, but downtime for the business still exists.   As IT professionals and DBAs all know, months of planning and preparation go into making these types of event successful but things can go wrong.    “The best laid schemes of Mice and Men oft go awry, And leave us nothing but grief and pain, For Promised Joy - Robert Burns 1785."    For years customers have been asking for a better way to migrate or upgrade the underlying Oracle database for E-Business Suite with a low amount of downtime.    

Oracle ACS (Advanced Customer Services) developed a services based solution to  deliver low downtime, low risk migrations and upgrades using Oracle GoldenGate as the enabling technology. Oracle GoldenGate has a long track record of successful migrations and upgrades with little or no downtime. Combining this technology with the services provided by Oracle ACS and customers now have an option significantly reduce their downtime when migrating or upgrading their Oracle databases. The Oracle ACS Migration Service uses functions integrated in the Oracle Database for the initial load of the target system and Oracle GoldenGate for ongoing data synchronization (see image below). The process is suitable for all Oracle E-Business Suite products on an Oracle Database of any size. The end result is a database migration with low amount of downtime for business users. 

One common question that comes up regarding this specific migration service is “Oracle GoldenGate provides zero downtime migrations for other applications, why not E-Business Suite?”   The reason why the migration service is a low downtime solution and not zero is that E-Business Suite uses Oracle Advanced Queues, which under meta data tables use the ANYDATA data type. This particular data type is not currently supported by Oracle GoldenGate and as such has to be moved in bulk. These tables typically have a very small amount of data and prior to cutting over to the new target environment, these tables are moved from the source to target as a complete unit.   A small amount of time is needed to perform this action. 

For additional details about the Migration Services available for Oracle E-Business Suite, please refer to this Oracle Migration Service for Oracle E-Business Suite data sheet. You can hear about this new solution and Oracle GoldenGate 12c on Feb 12th 10am PT in our upcoming webcast: Introducing Oracle GoldenGate 12c: Extreme Performance Simplified.


Welcome Oracle Management Pack for Oracle Data Integrator! Let’s maximize the value of your Oracle Data Integrator investments!

To help you make the most of Oracle Data Integrator, and to deliver a superior ownership experience in an effort to minimize systems management costs, Oracle recently released Oracle Management Pack for Oracle Data Integrator. This new product leverages Oracle Enterprise Manager Cloud Control's advanced management capabilities to provide an integrated and top-down solution for your Oracle Data Integrator environments. Management Pack for Oracle Data Integrator supports both 11g (11.1.1.7.0 and higher) and 12c versions of Oracle Data Integrator.

Management Pack for Oracle Data Integrator provides a consolidated view of your entire Oracle Data Integrator infrastructure. This enables users to monitor and manage all their components centrally from Oracle Enterprise Manager Cloud Control.

Performance Monitoring and Management

Management Pack for Oracle Data Integrator streamlines the monitoring of the health, performance, and availability of each and all components of an Oracle Data Integrator environment – this includes Master and Work Repositories, Standalone and JEE agents, as well as source and target Data Servers.


Figure 1 – Management Pack for Oracle Data Integrator - Dashboard

Through an easy-to-use graphical interface, administrators and operators are able to quickly asses the status of their Oracle Data Integrator environments. The Dashboard page provides a summary of the health of each Oracle Data Integrator components, and clearly highlights potential issues with links for drill-down such that users can easily further investigate and assess more details.


Figure 2 – Management Pack for Oracle Data Integrator– Repositories Page

The Repositories page clearly offers key database statistics and performance metrics, where users can easily track the growth of the repository tablespace and purge the Oracle Data Integrator logs when needed – one of Oracle Data Integrator’s best practices!


Figure 3 – Management Pack for Oracle Data Integrator– Database Execution Details Page

The Load plan Executions/Sessions page allows developers and operators to view the entire Oracle Data Integrator Sessions activities and review detailed execution statistics; such as the overall duration of processes, or the number of inserts, updates, deletes performed. Users can click to access database details on Oracle, or to view a relevant entire SQL statement generated by Oracle Data Integrator.


Figure 4 - Management Pack for Oracle Data Integrator– Agents Details Page

The Agents Page provides the list of Oracle Data Integrator Agents and the ability to drill down into each one. Recently triggered alerts or warnings can also be viewed. Overall metrics for agent performance, with quick links to session details are provided.

Configuration Management

To ensure that the configurations of all critical Oracle Data Integrator components in production environments are consistent with staging or test environments, Management Pack for Oracle Data Integrator provides Configuration Snapshots to save working configurations into the Management Repository or into an external XML file. These snapshots can then be compared with the active configuration in staging or test environments, or against any historical snapshot. Configuration Comparison is the next logical step which thus allows you to ensure the consistency of configurations in your application environment. Going one step further, Configuration History keeps track of all configuration changes, which can help in diagnosing performance problems that may be related to configuration changes along the way.

Service Level Management

Management Pack for Oracle Data Integrator allows for a proactive method to monitor Oracle Data Integrator environments at all levels. Many out of the box metrics are automatically collected for each Oracle Data Integrator component (repositories, agents, sources, targets…) which then provides users the ability to set up alerts based on critical or warning thresholds. Notifications can be provided to users in various ways through emails or Simple Network Management Protocol (SNMP) options.

Historical Analysis and Reporting

While real-time metrics certainly come in handy, there is also something to be said for historical collection of data! Management Pack for Oracle Data Integrator stores collected metrics and configurations data in a central repository –allowing users to thus analyze the metrics in various incremental views that include; last 24 hours, last 7 days, last 31 days. In addition, customizable service and system dashboard functionality allows users to create reports on various services and systems for service level availability, usage, performance, and business indicators – all in an effort to facilitate strategic trend analysis and reporting.

In a nutshell, Oracle Management Pack for Oracle Data Integrator benefits include: reducing the costs associated with monitoring the overall Oracle Data Integrator infrastructure and providing visibility into complex Oracle Data Integrator deployments across the enterprise, minimizing troubleshooting and performance tuning efforts as well as improving the overall ability to efficiently monitor Service Level Agreement compliance using powerful alerting capabilities.

Oracle Management Pack for Oracle Data Integrator is a new Management Pack, licensed separately. From an installation perspective it is packaged in the Fusion Middleware plug-ins for Enterprise Manager (version 12.1.0.4 and higher) and comes with the base Enterprise Manager 12c download. There is no separate installation file to download.

Try it out! Getting Started with Oracle Fusion Middleware Management Plug-in

Friday Jan 03, 2014

ODI 12c - Components and LKMs/IKMs

Here I'd like to illustrate some nice capabilities of ODI 12c's knowledge module framework in combination with the new component based mapper. Some of this was prompted from recent questions from Holger Friedrich (on migration from OWB..'where is delete'), Stewart Bryson and Mark Rittman (on components and KMs here) and a general nagging thought I had that people were generally unaware of the capabilities. There's a lot more we can illustrate and talk about in this area, so this is a little taster...

Customized loading, integration and error management can be defined using knowledge modules. There is a text based substitution library that makes building such knowledge modules very easy - especially with the abundance of examples - plus the substitution reference documentation is a good reference guide (intro here, reference here). This can be used in harmony with ODI 12c components. In 12c we have modularized a lot and introduced components (components are logical and describe the WHAT of the transformation, component KMs describe the HOW, just like regular KMs), also we have formalized some parts of the framework but it is still as open as ever.

In supporting the odiRef substitution library we have chosen a similar approach to how the sub-select worked in 11g. If you want to build your own integration you can use the odiRef methods to get the target shape, details, connection etc. The source for the target, just like in 11g is provided via the odiRef methods also and may be rolled into the odiRef.getFrom method.

To illustrate, if you want to perform an integration to do a delete operation, then the following snippet is sufficient - in 11g or 12c (the code in red is boiler plate). In ODI 12c you can now have an arbitrary mapping graph leading up to the target;

  1. DELETE FROM <%=odiRef.getTable("L","TARG_NAME","A")%> T
  2. WHERE (<%=odiRef.getTargetColList("", "[COL_NAME]", ", ", "\n", "UK")%> )
  3. IN
  4. (
  5. select * from (
  6.  <%for (int i=odiRef.getDataSetMin(); i <= odiRef.getDataSetMax(); i++){%>
  7.  <%=odiRef.getDataSet(i, "Operator")%>
  8.    SELECT  <%=snpRef.getColList(i,"", "\t[COL_NAME]", ",\n", "", "UK")%>
  9.    FROM <%=odiRef.getFrom(i)%>
  10.    WHERE (1=1) <%=snpRef.getJoin(i)%> <%=snpRef.getFilter(i)%> <%=snpRef.getGrpBy(i)%> <%=snpRef.getHaving(i)%>
  11.  <%}%>
  12. ) S
  13. )

For example in the mapping below, I am using the subquery filter component to identify some rows that I then want to DELETE from the CONTACT_LIST target. The integration type property on target datastores was introduced in 12c and supports a set number of integrations to help filter the IKMs, if you select None, you can pick any IKM applicable for the technology.

If you look at the physical design for the above mapping you can now pick the IKM which performs the delete, the subquery filter also has a component KM which produces code, this code is rolled into the odiRef methods in the template. This then lets arbitrary map designs to be created and still build customized KMs for loading, integrating and error management. The odiRef substitution methods are simple text based APIs for producing text based on simple primitives.

This then produces the following SQL statement to perform the DELETE DML, our IKM has no knowledge of how to construct subquery filter, pivot or any other complex transformation - the code has been modularized. The code produced is below;
  1. DELETE FROM OE.CONTACT_LIST T WHERE (CUSTOMER_ID)
  2. IN (
  3. SELECT   CUSTOMER_ID
  4. FROM OE.ADDRESSES ADDRESSES
  5. WHERE (1=1)   and ( ( ADDRESSES.CUSTOMER_ID ,  ADDRESSES.PHONE_NUMBER )
  6.                = SOME  (  
  7. SELECT 
  8.   CUSTOMERS.CUSTOMER_ID  CUSTOMER_ID ,   CUSTOMERS.CUST_FIRST_NAME  PHONE_NUMBER  
  9. FROM
  10.  OE.CUSTOMERS CUSTOMERS  
  11.  WHERE  CUSTOMERS.MARITAL_STATUS = 'married' AND CUSTOMERS.CREDIT_LIMIT > 1000  AND  ADDRESSES.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID 
  12.     )
  13.    )  
  14. )

Another useful illustration of things working in harmony is related to the improved code generation for Oracle connectivity - the basics of database links have been greatly improved (no more requirements for view on source, but still supported). The framework change in order to support this will have more use cases down the line too. Now you can use the 12c database link LKMs to pull data from tables over a database link and leverage custom IKMs on the target. One of the 11g issues was related to the requirements the 11g Oracle to Oracle database link LKM placed on users, this has been greatly improved, you can now take advantage of this, plus use existing IKMs, build new ones etc.

The mapping above uses new 12c LKMs to access remote Oracle datastores and a customized IKM to illustrate the mix and match capabilities. 

Hopefully this gives you some background and insight into the ODI 12c mapping and knowledge module capabilities that you weren't aware of. Looking forward to any questions and additional ideas, insights that you have.

Saturday Dec 28, 2013