15 Nov 2011 Capture, route, transform and send data with Oracle GoldenGate
GoldenGate is a tool that Oracle acquired in 2009 that allows us to capture, route, transform and send data between heterogeneous systems in real time with a very low impact on the source systems.
So basically Oracle GoldenGate (OGG from now on) moves data between point A and point B, but it does it in a brilliant way. Let’s take a look in detail to its main features:
Very low impact on data sources – This means that OGG does not disturb the source systems when fetching new data. In order to achieve that it doesn’t really access the database layer but it’s constantly looking for change in the redo files (or equivalent technology) to know when a transaction has been done.
Heterogeneous Systems – Thanks to the ODBC technology and the VAM (Vendor Access Modules) OGG is able to interconnect a nice amount of database vendors between each other, although only the ones that offers referential integrity can be chosen as source systems.
Real time – Maybe you’ve heard that OGG can transfer data with a sub second latency. That is true if your integration scenario meets some conditions and the physical distance between source and destination is not huge. Anyway, in the worst circumstances we are talking about real low latency times.
GoldenGate Scenarios
Due to its purpose, OGG can be used in a variety of scenarios, many of them related to DBA activities like zero-downtime migrations, query offloading and continuous availability. However, there is also room here for solving some BI challenges. The main two scenarios related to the BI world are the following:
Operational Reporting
Sometimes you may need operational reports to help you in your decision making process. To be operative, this kind of reports need to access real time data, but usually the access to the OLTP systems means that performance for other users may decrease when such reports are being generated.
In this scenario OGG may help us replicating information in real time to another destination (maybe an obsolete piece of commodity software) so the operational reports can fetch data from that new source instead of the OLTP system.
Real Time BI
So OGG is all about moving data, but not necessarily between databases. We can leverage the power of OGG along our ETL tool to create a real time BI scenario.
OGG is able to generate micro batch of real time data and feed them to our ETL tool (like ODI, or Informatica). That data is being captured without impacting the data source, so we can actually decrease the interval of time between our ETL batches. Our limitation is the running time of our ETL process.
In this way, we can have our regular data warehouse with “old” data (say 24 hours or more) and another clone data warehouse with the same structure being populated in real time, with fresh data up to 24 hours. Our analytic tool usually will be able to discern if the data asked should be fetched from the old or the new repository in a seamlessly way. Data older than 24 hours in our real time repository will be discarded as the same data will be captured by our daily ETL load.
Conclusions
Oracle GoldenGate is a tool to be taken into account when thinking about Real Time BI. The tool is powerful and robust and easy to deploy in most scenarios. In top of that you can acquire the Oracle Management Pack for GoldenGate in order to have a neat graphical interface to assist you in the development process and after that in the monitoring process.
It’s true that many of the traditional ETL vendors have developed a real time version of their tools, but that option is usually much more expensive than using OGG along with your usual ETL tool. However, be aware of the integration effort that this second approach implies.
Read more information here.
