What is the difference between Oracle GoldenGate and Oracle ODI?

Gadsden Consulting
Gadsden Consulting used Ask the Experts™
on
What's the difference between Oracle GoldenGate and Oracle ODI? Which one is the ETL tool?

Why two tools? (Their product descriptions are fuzzy enough that it's hard for the layman to tell the difference).

if the two tools are appropriate, isn't that a big learning curve to learn two separate (albeit complementary) tools?

It seems to me GoldenGate is a "data replication tool", layman's view: it moves data from point A to point B.
- but it also says it does "transformations", so doesn't that translate to an E-T-L tool?

The ODI product home page doesn't say anything about transformation.

This is a good article but still my understanding on the differences is fuzzy ...

related article ...

===== background =====

I'm a data warehouse consumer and designer of a relational d.b. data warehouse. Never got into the multi-dimensional or star schema stuff.

So I'm not building the infrastructure for the data warehouse, but I might write the ETL specs. In other words, I know the data and know my reporting requirements, with a strong SQL knowledge and understanding of the business.

But I need to know some of the tools to best understand the environment and work with the builders of the data warehouse.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I believe your assessment is accurate.

Goldengate is replication (can be cross system) and ODI is ETL.  Sure, in the replication rules, you can perform some transformations but it really isn't the "E" and "L" in ETL in the higher meaning even though it sort of has to do that to get data from A to B.

A good description of ODI that does mention transformation:  This is 11g but I cannot locate a newer version:
https://docs.oracle.com/cd/E17904_01/integrate.1111/e12641/overview.htm#ODIGS111
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv,

thanks - just the straight scoop like I was looking for.

What about the learning curve? Is it two training paths (more or less learn TWO things)?  Sure, complementary, but THIS week: GoldenGate, NEXT WEEK: ODI. THIRD WEEK: get 'er done ...

===== UPDATE ======
Yes, two learning paths:
- GoldenGate
- ODI

so that's a lotta learning ...
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Cannot speak to the learning curve.  I've never used either product.

I was using the previous replication products to replicate Oracle to Oracle:  Streams Replication and before that Advanced Replication.  They took some getting used to and I had to spend some time in the documentation digging out what I needed.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

MI and Automation
Commented:
ODI was acquired by Oracle from Synopsys about 10-15 years ago. ODI is a true ETL tool (or as Oracle refer to it ELT as the transformations are post load. I would say ODI is not a competitor of Informatica (Power Exchange) or Microsoft's SSIS.

That is not to say that Golden gate cannot be used for such things... If you want to perform ETL via CDC, then you can use GG. But ODI is more user friendly, and, if you are a BI guy, just more familiar in its UI.

That said Informatica also has a CDC component, as does SSIS on SqlServer.

If you are in a position to be making architecture choices, you will obviously need to consider cost. But whatany people miss out on, is the RESOURCE COST. GG skills ar hard to come by. ODI not much more common. I ran the data management practice for CGI on Halifax Canada (circa 2010-2014)... And I'll tell you, recruiting INFORMATICA developers (mostly from India) was almost a full time job.
I suspect there are more SSIS Devs around.

One boon of ODI, is that it runs on the DB server, rather than its own separate server (as Informatica does)

These days, I no longer use GG or Informatica... Mostly SSIS... (As our warehouses are on SQL server now). I quite like it. It is ridiculously quick.
jtriftsMI and Automation

Commented:
Sorry ..typo... ODI is not a competitor OF GOLDENGATE... But is more of a competitor of Informatica and SSIS.
Mark GeerlingsDatabase Administrator

Commented:
I haven't actually used either ODI or Golden gate.  But we did use ODI here a few years ago until we learned that the similar Informatica tool works better and is cheaper (at least based on what I heard - I didn't actually see the quotes or invoices).  We now use Informatica for most of our ETL processes.
Gadsden ConsultingIT Specialist

Author

Commented:
Great discussion, thank you, just what I was looking for.

In our environment, the goal is "incremental loads" as opposed to the full database load (which is done weekly, and painfully) (it's not all that big of a database).

Here, GoldenGate is seen as the vehicle for incremental loads, and I assume ODI for the ETL.

My takeaway from this discussion:
- Informatica is the best tool
- GG and ODI overlap in functionality, and "tooling up" is a challenge, and then Admin Support is heavy, as is the need for developer skills

I'm good with the discussion, will keep open until tomorrow.

thanks!

jtrifts

- great info, but I'm still not sure if both tools are needed (here). Our need is to take transaction data (d.b. changes), load to a data warehouse staging area, then transform to the actual data warehouse. I'm sensing that one (or the other) tool could actually do the job, and that both tools are not necessary (in our environment, which is small data)

Mark Gettings
- thanks! good info too.
jtriftsMI and Automation

Commented:
If you want to perform both traditional ETL and CDC using informatica, you do need two tools  Informatica is acthallyy a company, not a tool though people use the term as if it was. Informatica power center for ETL... and Informatica power exchange for CDC.
jtriftsMI and Automation

Commented:
*actually
Mark GeerlingsDatabase Administrator

Commented:
To jtrifts:  
Did you notice the "update" feature that was added to this site some time (months maybe?) ago?  That allows us now to correct typos in a comment after posting a comment.  If you put your mouse over a comment, you should see a small box with three vertical dots in it near the right edge of the screen.  Clicking in that box brings up a list of actions you can take, including: "Edit this Comment".  (I've never tried editing a comment I didn't post.  I don't know if that is supported, hopefully not, since that could be abused.)
jtriftsMI and Automation

Commented:
To say you want to perform incremental loads only does not mean you must use both. You can - CDC is faster for identifying changed records. An analogy for oracle DBAs --- if you perform incremental backups using RMAN, you'd configure RMAN block change tracking.

With a power centre only implementation you'd use version or insert/update date fields to identify new/changed records. On a large table, these selects can take some time, even when these columns are indexed. CDC avoids the necessity as all the
new/changed records are marked/tracked by CDC processes.

So...the use case defines the need.

When we first implemented ETL, we started with power center only, and it was fine for smaller sets of data. But when we went headlong into warehousing and BI (plus a Cognos layer), we added power exchange to shorten our load windows.
jtriftsMI and Automation

Commented:
Thanks Mark. Have been using E-E since 1997 and still don't know much about the features!
Gadsden ConsultingIT Specialist

Author

Commented:
jtrifts,

>>So...the use case defines the need.
- Bingo

and great points on the process.
jtriftsMI and Automation

Commented:
Long story short... You could use ODI or Informatica Power Center for small to medium sized incremental load jobs.
There are other tools on the market too.
Mark GeerlingsDatabase Administrator

Commented:
That's OK.  I think I started on this site in 1998, and I don't know all the features either.
Gadsden ConsultingIT Specialist

Author

Commented:
first time with the new award system - I like it. All great discussion, thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial