What is the difference between Oracle GoldenGate and Oracle ODI?

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.
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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:
Gadsden ConsultingIT SpecialistAuthor Commented:

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

so that's a lotta learning ...
slightwv (䄆 Netminder) 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.
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

jtriftsMI and AutomationCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jtriftsMI and AutomationCommented:
Sorry ..typo... ODI is not a competitor OF GOLDENGATE... But is more of a competitor of Informatica and SSIS.
Mark GeerlingsDatabase AdministratorCommented:
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 SpecialistAuthor 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.



- 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 AutomationCommented:
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 AutomationCommented:
Mark GeerlingsDatabase AdministratorCommented:
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 AutomationCommented:
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 AutomationCommented:
Thanks Mark. Have been using E-E since 1997 and still don't know much about the features!
Gadsden ConsultingIT SpecialistAuthor Commented:

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

and great points on the process.
jtriftsMI and AutomationCommented:
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 AdministratorCommented:
That's OK.  I think I started on this site in 1998, and I don't know all the features either.
Gadsden ConsultingIT SpecialistAuthor Commented:
first time with the new award system - I like it. All great discussion, thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.