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.

Wednesday Jul 23, 2014

ODI 12c - Sessionizing Data with MATCH_RECOGNIZE and SQL Override

The Oracle 12c database introduced a very useful new pattern matching capability, useful for many different use cases. The example I will use in this blog post within ODI is sessionizing data - some log data with sample URLs, IP addresses initiating the clicks and their timestamps. To incorporate this in ODI, I will use one of the lesser known capabilities that many users often want to use - it is the ability to override the generated SQL. I blogged about how this could be done in ODI 11g here and here - in this post I will show how this can be done in 12c.

There are some great posts and presentations from Keith Laker and many other partners and users on the MATCH_RECOGNIZE capability, the post on Sessionization with 12c pattern matching is super fast is a great read, check it out! I will base the SQL used on that post. 

My source data has the following information;

  1. create table clicks (IP varchar2(16), Click_Timestamp timestamp, URL varchar2(200));
  2. insert into clicks values ('','03-JUL-2014 11:33:15','');
  3. insert into clicks values ('','03-JUL-2014 11:33:21','');
  4. insert into clicks values ('','03-JUL-2014 11:59:08','');
  5. insert into clicks values ('','04-JUL-2014 09:02:19','');
  6. insert into clicks values ('','04-JUL-2014 09:02:26','');
  7. insert into clicks values ('','04-JUL-2014 09:48:27','');
  8. insert into clicks values ('','04-JUL-2014 10:01:05','');
  9. commit;

The type of query we need to generate a session ID using MATCH_RECOGNIZE is shown below. As in Keith's blog, we defined a session as a sequence of one or more events with the same partition key (IP address) where the gap between the timestamps is less than 10 seconds - in this demo, the gap is completely arbitrary and could be set to any number as required. Here we detect the various sessions and assign a surrogate session id to each session within each partition (IP). 

  1. SELECT ip, session_id start_time, no_of_events, duration
  3.  (PARTITION BY ip ORDER BY click_TimeStamp 
  4.   MEASURES match_number() session_id, 
  5.            count(*) as no_of_events,
  6.            first(click_timestamp) start_time, 
  7.            last(click_timestamp) - first(click_timestamp) duration 
  9.   PATTERN (b s*) 
  10.   DEFINE 
  11.        s as ((s.click_TimeStamp - prev(s.click_TimeStamp)) <= TO_DSINTERVAL('0 0:00:10.0')) 
  12.  ); 

The Oracle syntax (...FROM clicks MATCH_RECOGNIZE....) for this operation is a little different from other SQL constructs, it isn't a join, it isn't a table function, it might be a custom component - but until that's public need another way. You could create a view and use that in ODI. Another option is to use the SQL override, custom template capability. This let's you define any arbitrary code for a source component in a mapping. Source datastores in mappings have a physical option named CUSTOM_TEMPLATE. You can define code here such as the SQL to produce the sessionized data above. The code can use ODI's substitution reference methods to make the code more generic (parameterized schemas etc.). For example the physical map design below defines a mapping with a source datastore defining the attributes projected from my query. I defined the datastore in an ODI model - it isn't actually in a database, it's just used in the mapping.

As it's description states 'Custom SQL statement as an inline view to override default code generation.' is what this is for. 


Note this option is under the extract options on the source datastore's physical node, the value for CUSTOM_TEMPLATE can be edited and arbitrary code, in this case SQL, defined. Double clicking the value field in the KM options table brings up an editor where you can see the entire code block I have defined. Note below, I have hard-wired the clicks table in the SQL;

To make this more generic and flexible, I can use the odiRef methods to get the name of the clicks table generated based on the topology defined in ODI. My clicks table is in a model in ODI and the use of odiRef.getObjectName below let's me get the name correctly generated depending on the generation context - perhaps in one context it is in schema DEVX and in another it is in DEVY, using the SDK shields us from such changes.

You will note also that there is another KM option named SELECT_HINT, this is used by the default 12c code generation capabilities but if you override the code using this custom code option described above you can still incorporate the hints supplied in the mapping. So if you define a value for the KM option SELECT_HINT you can have it included in your custom code by using this getOption method - pretty flexible! 

Hope this is a useful insight into some of the capabilities provided in ODI 12c. I used the latest and greatest 12.1.3 release in this post, that's what you will need to use this functionality.

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:

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:

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

Tuesday Jul 15, 2014

Oracle GoldenGate 12c Capture for SQL Server

In Oracle GoldenGate 12c we have expanded the heterogeneity of the product by adding support for the latest versions of the major databases. 

In Oracle GoldenGate for SQL Server, which is available for download on My Oracle Support, we offer support for capturing from and delivering to SQL Server 2012. Oracle GoldenGate 12c also supports SQL Server 2008 and SQL Server 2008 R2 Capture and Delivery.

Two other highly anticipated new features for Oracle GoldenGate 12c for SQL Server, are the ability for Capture to read from SQL Server compressed transaction log backups and the ability to restrict the need for Capture to read from transaction log backups, required when customers use 3rd party transaction log backup utilities.

Some of the key capabilities of Oracle GoldenGate for SQL Server are as follows:

  • Oracle GoldenGate now supports Capture from SQL Server 2012 beginning with GoldenGate
  • Oracle GoldenGate for SQL Server can be configured to Capture from tables without Primary Keys.
  • Oracle GoldenGate 12c for SQL Server supports SQL Server native backup compression and includes the optional configuration to never have to read from transaction log backups, which enables any log backup utility to be used.
  • Capture process can work in conjunction with SQL Server Transactional Replication, and Change Data Capture.
  • Oracle GoldenGate for SQL Server can provide real-time data to heterogeneous target databases, and receive real-time data from heterogeneous databases.

A complete list of supported features and details on how to implement those features are available in the Oracle GoldenGate 12c Documentation Library, available at the following address:

I also recommend reading our white paper Using Oracle GoldenGate 12c with SQL Server”  that provides more details on how GoldenGate supports SQL Server databases.

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. 


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 09, 2014

ODI 11g - HDFS Files to Oracle, community post from ToadWorld

There is a new tutorial on using ODI to move HDFS files into an Oracle database using OLH-OSCH from Deepak Vohra on the ToadWorld blog. This article covers all the setup required in great detail and will be very helpful if you're planning on integrating with HDFS files.

Tuesday Jul 08, 2014

Oracle GoldenGate Veridata Repair is here!

By Joe deBuzna, Director, Oracle Data Integration Product Management

Fix your out of sync data with a single click using the new Oracle GoldenGate Veridata 12.3 heterogeneous data repair feature!

I am so happy to finally be typing that sentence because I get asked about the Veridata Repair feature all the time. So YES, it’s really here! Call me biased, but this is one of the most highly sought after, killer features in the Oracle Data Integration product line, if not all of Oracle Fusion Middleware. And while it’s part of the Oracle GoldenGate product suite, it can compare and fix data between any two databases regardless of how you’re moving the data around: Oracle GoldenGate, Oracle Data Integrator, Oracle Streams, Logical Data Guard, Oracle Advanced Replication, SQL scripts, custom batch jobs, SQL*Loader – it does not matter! As long as Veridata has the right database connection permissions and supports the platform, you’re good to go.

And why does data go out of sync? Let me count the main ones, so we can keep this post short and sweet. The big ones we see include bad source data (e.g. out of bounds characters from wrong NLS settings and bad dates) got inserted or updated, no logging source operations caused data gaps in logical replication products, and bulk data loads/copies into target systems were timed incorrectly (improperly “instantiated” for replication). And here’s the one we don’t like to talk about: we work in extremely complicated IT environments and sometimes people just make mistakes. Sometimes we inherit those mistakes. And it’s ok to have mistakes. But far better if you can quickly identify them and it is absolutely crucial to fix them as soon as possible to avoid domino effects of inconsistent data.

So don’t just hope your data is in sync, prove it and have confidence knowing it. Have confidence when you’re about to pull the switch on your cloud migration or failover; have confidence looking your CEO in the eye telling him that his “single source of truth” still is; have confidence that complex, multi-master conflict detection and resolution setup you designed, or inherited, is actually keeping everything in sync.

You can learn more about the repair feature via a few new resources we have for you below. Take a look and see how you can pick and chose only which tables and specific rows you want to repair; what we did to make this feature fast and secure; how we can suppress target triggers; and more:

You can also watch a half hour video of me talking about and giving a demo of both Oracle GoldenGate Veridata and Monitor 12.1.3 here:

Oracle University Learning Stream video for Oracle GoldenGate Monitor and Oracle GoldenGate Veridata.

For Oracle GoldenGate Veridata 12.1.3 we now include in a single download Oracle GoldenGate Veridata Server, Veridata Java agents, Oracle GoldenGate Monitor Server, and Oracle GoldenGate Monitor agents. This single download can be found on and on our Oracle Technology Network page.

And since we’re now based on Oracle Weblogic Server 12.1.3, you’ll need to also download the Oracle Application Development Runtime Infrastructure.

To see which platforms are supported, you can click on the Certifications tab in My Oracle Support, or download the spreadsheet “System Requirements and Supported Platforms for Oracle Fusion Middleware 12c (12.1.3)”.

Finally, please feel free to leave comments and feedback below. For those willing to share, I’m always looking for interesting stories on how you’ve seen data going out of sync and how you ended up fixing it.

Monday Jul 07, 2014

Using ODI 12c for Data Integration with Oracle Hyperion Planning

Posted for Ayush Ganeriwal

Though the current Oracle Data Integrator 12c (ODI 12c) release does not include Oracle Hyperion Planning Knowledge Modules, the Outline Load Utility that comes with Oracle Hyperion Planning can be used for data integration, loading and extracting both data and metadata from Oracle Hyperion Planning. This article discusses the use of Oracle Data Integrator and the Outline Load Utility to achieve end to end data integration with Oracle Hyperion Planning.

 For data or metadata load use cases ODI is used to first prepare the required flat file and then kick off Outline Load Utility process to push data into Oracle Hyperion Planning. For extraction use cases ODI first runs the Outline Load Utility to extract data or metadata into a flat file which then transformed the flat file through ODI mapping.

For details on the Outline Load Utility please refer to Planning Documentation in the Oracle Enterprise Performance Management documentation library.

Loading Data into Oracle Hyperion Planning

In the below ODI package the source data from DB is first transformed into a comma separated value (CSV) file in the db_to_file mapping and then Outline Load Utility is run through an OS Command to push the data into Oracle Hyperion Planning.

In above example the following flat file is generated by the mapping step.

After the Outline Load Utility execution the data can be viewed through Oracle Hyperion Planning User Interface.

The OS command used above and its parameter descriptions are as follows

OutlineLoad.cmd -f:c:\password.txt /A:VisPlan1 /U:demoadmin /M /I:c:\data\TrialBalanceData.csv /TR /L:c:/outlineLoad.log /X:c:/outlineLoad.exc

- -f = Location of encrypted password file

- /A: = Application name

- /U: = User Name

- /M = Generate header records.

- /I: = Input File

- /L: = Log file

- /X: = Exception file

- /TR = Driver specified in data file. 

Loading Outline Members into Oracle Hyperion Planning

To load outline members, a parent child flat file is generated through the ODI mapping in the first step and then the Outline Load Utility is kicked off to create the outline members.

Each of the outline members is specified on a separate line in the CSV file in the parent child format. There can be additional attributes included with each member as a comma separate values but if none specified then default values of the attributes will be used. In this example only the parent child hierarchy is specified forcing the default values to be used for member attributes.

The above flat file is loaded into Oracle Hyperion Planning outline as follows.

The command and parameters used to load outline members are as follows.

OutlineLoad.cmd -f:c:\password.txt /A:VisPlan1 /U:demoadmin /M /I:c:\data\ODIAccounts.txt /D:Account /L:c:/outlineLoad.log /X:c:/outlineLoad.exc

- /D:= Dimension to be loaded

- All other parameters are same as described earlier in the article

Extracting Data from Oracle Hyperion Planning

Oracle Hyperion Planning uses Oracle Essbase for storing its data so there could be multiple approaches for extracting date out of Oracle Hyperion Planning.

1. Using Outline Load Utility

2. By executing MAXL or Calc Script

3. Using the ODI Knowledge Modules for Oracle Hyperion Essbase

In this article we will use the first approach only. The below package is created with OS Command as the first step to invoke Outline Load Utility to extract data in a flat file. The flat file is then transformed and loaded into database in mapping TransformPlanningData.

The below flat file shows the data extracted by the Outline Load Utility

The command used for data extraction and its parameter description is as follows

OutlineLoad.cmd -f:c:\password.txt /A:VisPlan1 /U:demoadmin /ED:c:/data/VisPlan1out /CP:c:/data/VisPlan1Dump.Properties /L:c:/outlineLoad-out.log /X:c:/outlineLoad-out.exc

- /ED:= Output file name where data is exported

- /CP:= Specifies a file that contains command line arguments that, in conjunction with command line arguments, constitutes the options for execution. The properties file has the extra parameter /EDD: that specifies the format of the exported Planning Driver Member formatted data files as follows

- The remaining parameters are the same as already described earlier in the article

Extracting an Outline Members out of Oracle Hyperion Planning

Similar to the Data Extraction process, the Outline Load Utility is invoked in the first step of the package to extract the member metadata in the flat file which is then transformed and loaded into a database table through the mapping.

The flat file generated contains following data

The Outline Load Utility command and parameters used here are as follows

OutlineLoad.cmd -f:c:\password.txt /A:VisPlan1 /U:demoadmin /E:c:/data/VisPlan1Account.csv /D:Account /L:c:/outlineLoad-out.log /X:c:/outlineLoad-out.exc

- /E:= Exports the dimension specified with the /D switch to the specified output file

- Rest all parameters are same as decribed earlier in the article


There are no Knowledge Modules available for Oracle Hyperion Planning in ODI 12c, but the same result can be easily achieved using the combination of Outline Load Utility and ODI 12c.

Thursday Jul 03, 2014

ODI 11g - Hive to Oracle with OLH-OSCH, community post from ToadWorld

There is a new blog post on using ODI to move data from Hive to Oracle using OLH-OSCH from Deepak Vohra on the ToadWorld blog. It covers everything from install to all the configuration of paths and configurations files. So if you are going down this route it's worth checking it out, he goes into great detail into everything that needs done and setup.

Big thanks to Deepak for sharing his experiences and providing the blog to get folk up and running. 

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)

Tuesday Jul 01, 2014

New Release for Oracle GoldenGate Monitor is here!

 By Tom Chu, Oracle GoldenGate Product Management

Oracle GoldenGate Management Pack provides extensive enterprise-wide monitoring and management of your Oracle GoldenGate solutions The license include:

  • Oracle Enterprise Manager Plug-in. Provides secure viewing, management, and alerting capabilities for Oracle GoldenGate while leveraging Oracle Enterprise Manager framework 

  • Oracle GoldenGate Monitor. A stand-alone product that provides secure viewing, management, and alerting capabilities for Oracle GoldenGate with end-to-end topology solution displays and customizable topology views

  • Oracle GoldenGate Director. A stand-alone product for basic configuration, management, monitoring, and alerting for legacy Oracle GoldenGate deployments

Oracle GoldenGate Monitor version 12.1.3 was released on June 26th, 2014 and delivers the following new features:

  • Control Oracle GoldenGate processes Capture (Extract) and Delivery (Replicat) by:

    • Initiating "start", "stop", and  "kill" commands
    • Configuration management, and ability to edit configuration files
  • Access report and discard files to understand processing and diagnose problems.

  • Integration with Oracle Web Logic Server 12.1.3 to provide secure single sign on (SSO).

  • New metrics including “Seconds Since Last GoldenGate Checkpoint”, and a class of “delta” metrics that track the change between samples for existing “total count” metrics.

  • Support for monitoring Oracle GoldenGate instances running on IBM DB2 for z/OS

For more information, please refer to Oracle Management Pack for Oracle GoldenGate data sheet and to download please visit Oracle GoldenGate Downloads.


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


« July 2014 »