Monday Oct 27, 2014

Updated Statement of Direction for Oracle Business Intelligence Analytics (OBIA)

Oracle's product strategy around the Oracle Business Intelligence Analytics (OBIA) has been published this October in the latest Statement of Direction.

Interesting points relative to the BI Applications around data integration:

  • Oracle’s strategic development for ELT for BI Applications will focus on the Oracle Data Integrator and related technologies. Since the fielding of the ODI compatible version of BI Applications in the 11g series, customers have realized substantial financial and operational benefits from reduced time to value and improved speed of operations. Oracle continues to evolve and develop ODI, and Oracle’s BI Applications will take advantage of the latest capabilities as they become available.

  • Oracle will continue to support the 7.9.6.x product series according to the Oracle Lifetime Support policy including certifications of databases, operating systems, and enabling 3rd  party technologies.  However, Oracle will no longer develop new content for this series, nor extend the 7.9.6.x support or any series based on an ETL architecture with Informatica.

You can find the related blog entry with additional details from the BI Team here.

Raymond James Financial Leverages Oracle Data Integration

Hot off the press! 

Raymond James Financial shares how it uses Oracle Data Integrator and Oracle GoldenGate in establishing an enterprise information platform that integrates data from multiple heterogeneous sources such as HP NonStop, Microsoft SQL Server, etc and provides a consolidated company view.  This solution provides quicker access to actionable, timely data that helps operational efficiency for its 6,000 financial advisers by enabling repeatable processes in data migration and loading.

Read the press release.

For more information on this solution, this blog may be of interest also.

Friday Oct 24, 2014

Automating ODI development tasks using the SDK

By Ayush Dash, Oracle Consulting Services

Oracle Data Integrator (ODI) 11g uses Interfaces to move data from a source to a target datastore. ODI Studio is a very convenient drag and drop UI to build, test and execute such interfaces. These interfaces can have different processing logic and complex transformations for disparate requirements, but there could be interfaces which behave in the exact same manner except the source and target are different.

Let’s say, I have these requirements defined, able to identify the different buckets of processing required and develop respective interfaces. That’s easily done! However, if I change the requirement such that I need 10 interfaces for each bucket, the requirement gets little complex and am I face increased level of effort. What about 100 such interfaces for each buckets? Much more effort required now! It’s the same repetitive set of tasks but it needs to be done for each interface for each bucket. The problem we face here is to somehow expedite and automate the entire sequence of steps for each bucket and reduce the redundant, manual development of ODI interfaces. As the number of interface grows, our problem (increase in effort) compounds.

Note, this is not limited to interfaces only, it can be extended to generate scenarios, packages etc.

Use Case:

In one of my ODI engagements, we had the below requirements with aggressive timelines.

  1. Incremental Loads from a Source Database to Incremental Database. (ODI interfaces)
  2. Data masking on Incremental Database (not an ODI task)
  3. Incremental loads from Incremental Database to Target Database. (ODI Interfaces)

This had to be done for Oracle and PeopleSoft (2 buckets) and a total of 2300 tables (So a total of 4600 interfaces. 2300 interfaces for step 1 and 2300 for step 3) and eventually respective scenarios.

ODI SDK Groovy Scripts:

ODI Studio provides a Groovy Editor; a java based scripting language as part of its install. Groovy can be leveraged to work with ODI SDK and build automation scripts. Below is the list of scripts;

  • CreateProject – Creates an ODI Project with a ProjectName and FolderName.
  • ImportKnowledgeModules – Imports the specified Knowledgemodules to the KM directories.
  • CreateModels – Creates the source and target Models for existing Datastores.
  • CreateModelsandDatastore – Creates Datastores and Models.
  • CreateInterfaceIterative – Iterates through all the Source Datastores and generates an interface for each with respective Target Datastores.
  • CreateInterfaceIterativeandSetKMOptions – Creates Interfaces and set a KM options iteratively.
  • CreateScenario – Create scenarios for all the interfaces.
  • ExecuteScenario – Executes all the scenarios under all the interfaces.
  • CountInterface – Counts the no. of interfaces, can be used al validation.

The scripts and guide have been uploaded to the Oracle Data Integration project on Java.net: https://java.net/projects/oracledi.

All the scripts can be downloaded from here: https://java.net/projects/oracledi/downloads/download/ODI/SDK%20Samples/ODI%2011g%20SDK%20Automation/Automation%20Scripts/Automation%20Scripts.zip

Please refer to the ODI SDK Automation Guide for detailed steps: https://java.net/projects/oracledi/downloads/download/ODI/SDK%20Samples/ODI%2011g%20SDK%20Automation/ODI%2011g%20SDK%20Automation%20Guide.doc

Friday Oct 10, 2014

Oracle Data Integrator Webcast Archives

Check out the recorded webcasts on Oracle Data Integrator! 

Each month the Product Management Team hosts a themed session for your viewing pleasure.  Recent topics include Oracle Data Integrator (ODI) and Big Data, 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 topics focused on Oracle Data Integrator (ODI) 12c.

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

Take a look at the individual sessions:

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!

Wednesday Oct 08, 2014

A Recap of the Data Integration Track's Final Day in OpenWorld 2014

Last week during OpenWorld, my colleague Madhu provided a summary of the first 3 days of the Data Integration track in his blog post: Data Integration At OOW14 -Recapping days 1,2 and 3Today I would like to mention a few key sessions we presented on Thursday.

We kicked off the last day of OpenWorld with the cloud topic. In the Oracle Data Integration: A Crucial Ingredient for Cloud Integration [CON7926] session Julien Testut from Data Integration product management team presented with Sumit Sarkar from Progress DataDirect. Julien provided an overview of the various data integration solutions for cloud deployments, including integration between databases and applications hosted on cloud,  as well as loading cloud BI infrastructures. Sumit followed Julien with a live demo using Oracle Data Integrator and the Progress DataDirect JDBC drivers for loading and transforming data on Amazon Redshift, and extracting data from Salesforce.com. All of us in the audience were amazed that the demo worked seamlessly using only the OpenWorld wi-fi network.

For Oracle GoldenGate we had 2 key sessions on Thursday:

Achieving Zero Downtime During Oracle Applications Upgrades and System Migrations was in the Fusion Middleware AppAdvantage track and featured Oracle GoldenGate’s zero downtime upgrades and migration solution with customer Symantec and partner Pythian. In this session, Doug Reid presented GoldenGate's 3 different deployment models for zero downtime application upgrades.  In his slides, Doug highlighted GoldenGate’s certified solution for Siebel CRM, and mentioned the support for zero downtime application upgrade for JD Edwards and Billing and Revenue Management (BRM) as well. Following Doug, Symantec’s Rohit Muttepawar, came to stage and talked about their database migration project for their critical licensing database. And Pythian’s  Gleb Otochkin and Luke Davies presented how using Oracle GoldenGate in a 6-node active-active replication environment helped Western Union in achieving application releases with zero downtime, database patches and upgrades with zero downtime, and a real-time reporting database with no impact to online users.

The other key GoldenGate session was Managing and Monitoring Oracle GoldenGate. Joe deBuzna in the GoldenGate product management team provided an overview of the new monitoring and management capabilities included in the Oracle GoldenGate Plug-in for Enterprise Manager, and Oracle GoldenGate Monitor. Both of these products are included in the Oracle Management Pack for Oracle GoldenGate license. With the new 12c release,  they can now control the starting, stopping and configuration of existing Oracle GoldenGate processes, and include many new metrics that strengthen how users can monitor their Oracle GoldenGate deployments. 

The Data Integration track at OpenWorld closed with " Insight into Action: Business Intelligence Applications and Oracle Data Integrator". Jayant Mahto from ODI Development team, and Gurcan Orhan from Wipro Technologies focused  on the Oracle BI Applications latest release that embeds Oracle Data Integrator for data loading and transformations, and provides the option to use Oracle GoldenGate for real-time data feeds into the reporting environment. The session highlighted how the new Oracle BI Apps release provides greater strategic insight quickly, efficiently, and with a low total cost of ownership, as well as the role Oracle Data Integrator as the data integration foundation. Jayant and Gurcan presented how Oracle Data Integrator enables users to increase IT efficiency and reduce costs by increasing data transparency, easing setup and maintenance, and improving real-time reporting.

In case you missed it, I'd like to remind you on the press announcement that went out on September 29th and gives a summary of the key developments in the Fusion Middleware product family and Oracle's data integration offering. As mentioned in the press announcement, we have now a new offering for metadata management. An overview of this product was delivered in the “Oracle Data Integration and Metadata Management for the Seamless Enterprise [CON7923] session We will post a dedicated blog on this topic later in the week. Stay tuned for more on that.

Wednesday Oct 01, 2014

Are you at Oracle OpenWorld?

The week isn’t over just yet!

Oracle Data Integration has nine pods located throughout Moscone South where you can meet the product management and development teams. You will find us in the Fusion Middleware, Database, Application and Big Data areas this year.

For additional details on the Oracle Data Integration Solutions track at OpenWorld, make sure to check us out!  There are still many more sessions to attend before the end of the conference!

Friday Sep 26, 2014

Oracle GoldenGate and Oracle Data Integrator on the Oracle BigDataLite 4.0 Virtual Machine

Oracle's big data team has just announced the Oracle BigDataLite Virtual Machine 4.0, a pre-built environment to get you started on an environment reflecting the core software of Oracle's Big Data Appliance 4.0. BigDataLite is a VirtualBox VM that contains a fully configured Cloudera Hadoop distribution CDH 5.1.2, Oracle DB 12.1.0.2.0 with Big Data SQL, Oracle's Big Data Connectors, Oracle Data Integrator 12.1.3, Oracle GoldenGate, and other software.

The demos for Oracle GoldenGate and Oracle Data Integrator show an end-to-end use case of the fictional Oracle MoviePlex on-line movie streaming company. It shows how to load data into a Data Reservoir in real-time, process and transform the data using the power of Hadoop, and utilize the data on an Oracle data warehouse, either by loading the data with Oracle Loader for Hadoop or by using Hive tables within Oracle SQL queries with Oracle Big Data SQL. 

Please follow the demo instructions to try out all these steps yourself. If you would like to build the ODI mappings from scratch, try our ODI and Big Data Hands-on Lab.  Enjoy! 

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.

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
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
     
Today