Oracle offers several tools that enable or enhance the real-time data integration with an enterprise data warehouse. Those tools, when integrated and deployed correctly, offer a cross-systems
One of Oracle’s lead tools is Oracle GoldenGate 11g for Real-Time Data Warehousing. Oracle defines GoldenGate as a real-time data acquisition product that uses log-based, real-time change data capture (CDC) capabilities to provide continuous capture and delivery of the most recently changed data between OLTP systems and the data warehouse. It is a long-winded and complex description, but it exemplifies the primary capability of the GoldenGate application.
In short, GoldenGate offers transactional, real-time data capture, routing, transformations and delivery using the push approach. It works by detecting when a new database transaction is committed at the source system—when detected, that data is immediately captured through the database transaction logs and moved to the data warehouse, where it can drive enhanced, strategic and operational business intelligence (BI) capabilities.
Oracle GoldenGate is designed to perform basic, row-level transformations at the point of capture or at the time of delivery; it’s an ideal starting point for real-time data integration. Nevertheless, GoldenGate does have its limitations, and environments with heavy transformation requirements call for some additional tool integration.
In such environments, Oracle GoldenGate 11g can be integrated with Oracle Data Integrator Enterprise Edition (EE) 11g for better data warehousing. In this configuration, Oracle GoldenGate 11g delivers transactional data to a staging area in the data warehouse for in-database transformations with Oracle Data Integrator EE to support an extract transform and load (ETL) architecture. Oracle GoldenGate can deliver the data to an existing ETL server as well. In a nutshell, Oracle GoldenGate eliminates the need for batch windows and is extremely low-impact. It supports the movement of large data volumes, improves the ability to recover data in the event of a failure or outage and moves read-consistent data with referential integrity.
One of the most important capabilities of GoldenGate is the product’s inherent heterogeneity; Oracle GoldenGate supports log-based CDC for a wide range of databases—Oracle Database, SQL Server, IBM DB2 OS/390 and LUW, Sybase ASE, Enscribe, SQL/MP and SQL/MX, and Teradata running on Linux, Unix, Microsoft Windows, Oracle Solaris and Hewlett-Packard NonStop platforms. That cross-platform compatibility makes GoldenGate an ideal candidate for larger enterprises built through mergers and acquisitions, allowing existing systems to be leveraged and included in real-time data transformation projects.
That heterogeneity is further evidenced by GoldenGate’s ability to deliver information to data warehouses such as SQL Server, Teradata, Oracle Database, Netezza, Greenplum, HP Neoview and any warehouse running on a database compliant with Open Database Connectivity. What’s more, Oracle GoldenGate is certified to capture from or deliver to Oracle Exadata Storage Server. Oracle GoldenGate can also be deployed with Oracle GoldenGate Application Adapters to capture from or deliver to messaging systems.
Other products in the Oracle fold include Oracle Data Quality for Data Integrator 11g and Oracle Data Profiling 11g. Each of these adds a particular level of value to the real-time data integration puzzle. Oracle’s Data Quality product brings enhanced capabilities to the transformation process by enabling organizations to measure, improve and manage the quality of data from any domain. Ideally, the product can be used to inspect customer and product data and minimize exposure to risk, which in turn increases the value of the data by preventing “bad data” from becoming embedded in the transformation process. Other capabilities include data profiling, cleansing, and matching and monitoring, with a focus on ease of use that helps organizations deal with real-time data—to make sure data is valid and falls within norms.
Oracle’s Data Profiling 11g, a data investigation and quality-monitoring tool, allows business users to assess the quality of their data through metrics, discover or infer rules based on that data and monitor the evolution of data quality over time. When applied to real-time data transformation, Data Profiling 11g offers increasingly accurate data transformation capabilities by using lessons learned to cleanse data before it becomes part of a BI process.
One thing is certain, bringing real-time data integration into today’s data warehouses is no longer an impossibility—Oracle and its competitors offer a vast array of tools to ease the transition to real-time BI and analytics using the most current data possible, all without having to rely on cumbersome batch processing. Oracle offers a complete suite of products that make the task much easier, and the company’s additive technology allows real-time data collection and transformation to be done in phases, smoothing the process across heterogeneous data warehouses. It is an important consideration when it comes to data warehouse consolidation that goes along with mergers, acquisitions and corporate realignments.
ABOUT THE AUTHOR
Frank J. Ohlhorst is an award-winning technology journalist, professional speaker and IT business consultant with more than 25 years of experience. He served as a network administrator and applications programmer at the U.S. Department of Energy before forming his own computer consulting firm, which can be found at ohlhorst.net.
This was first published in August 2012