Data integration for Real Time BI using GoldenGate

David Yahalom
CTO Naya Technologies www.naya-tech.co.il davidy@naya-tech.co.il

 ;DATA Platform , Oracle- .MS , .

427X
. . 051 " : Oracle Gold Partner, Microsoft Silver Learning Partner

Business challanges Oracle GoldenGate

Oracle GoldenGate software enables real-time, continuous movement of transactional data across operational and analytical business systems.

Real-Time Access to Real-Time Information Real-Time Access

Mission-Critical Systems

Real-Time Information

Availability: the degree to which information can be instantly accessed.

Integration: the process of combining data from different sources to provide a unified view.

Real time data integration

Database and applications, Mixed sources, distributed systems, legacy, OLTP, OLAP

Real-time information

Mission Critical Applications & Data, Business Intelligence, Reporting for Customers, Partners & Employees

Real-time Access

GoldenGate benefits
Oracle GoldenGate provides low-impact capture, routing, transformation, and delivery of database transactions across heterogeneous environments in real time

Key Capabilities:

Additional Differentiators:
Performance
Log-based capture moves thousands of transactions per second with low impact

Real Time
Moves with sub-second latency

Heterogeneous
Moves changed data across different databases and platforms

Extensibility & Flexibility

Meets variety of customer needs and data environments with open, modular architecture
Resilient against interruptions and failures

Transactional
Maintains transaction integrity

Reliability

Solutions overview

TRANSACTIONAL DATA INTEGRATION

Key solutions

Two BI examples:

Realtime operational BI. Realtime data integration BI.

Solution Query Offloading

Cost Reduction Improve Resource Utilization

Find a solution that can transfer our data in real-time, keeping up with data volumes and meet future business needs. In actuality, GoldenGate was the only solution that handled all three requirements with ease. J Seelan, DBA Manager, 1-800-Flowers.com

Query Offloading
Improve Resource Utilization
OLTP Transactions Read Only Users

Benefit:
Offloads Queries - reduces load on transactional systems. Leverage existing investment in physical standby database. Replicate parts of the database as required. Improve performance: indexes, MViews... Reduce/Eliminate query load on transactional system

GG

Oracle Primary Database

Oracle Physical Standby Database

Non-Oracle or Legacy Platform

Consumer case study

COMPANY OVERVIEW A world leader in the travel marketplace, Sabre Holdings merchandises and retails travel products and provides distribution and technology solutions for the travel industry

GoldenGate PROVIDES Supports 1.6TB of data movement per day to read-only servers Source Oracle 10g | Target MySQL Current plans to migrate to Oracle 11g on both source and target.

CHALLENGES / OPPORTUNITIES Optimize OLTP system performance offload all query activity Reduce TCO via platform changes and segment lookers from bookers Handle growing data volumes and support heterogeneous systems over lifecycle of Air Travel Shopping Engine (ATSE) Maintain data integrity across all systems

RESULTS 80% TCO Reduction Millions $$ saved

Bookers vs Lookers

Solution Real-time Data Integration

Operational Reporting Real-time Business Intelligence

Accessing the data in real time using GoldenGate we can immediately see if were profitable and if our business processes are working. - Sam Peterson, SVP Technology, Overstock.com

Real Time Data Integration

Benefit:
Report on live data in transactional systems. No additional load on source system offload reporting to a database instance that is synchronized in real time. Enables tactical reports quickly for decision making and business transparency. Removes impact from source systems. Avoids the political challenges of running reports on transactional systems.

OGG

Transactional Applications

Operational Data Store

Real Time Data Integration

Real-time Integration + Bulk Data Transformation Production

Benefit:
ODI

OGG
Enterprise Data Warehouse Oracle Database, Oracle Exadata Heterogeneous Sources

Populate an operational data store for operational BI with real-time data. Run ELT from the ODS to load data warehouse with transformed data. Eliminates load on source systems. Reduces latencies and batch windows.

Source 1 EMP DEPT

Oracle GoldenGate Real time extracts from transactional systems

Non-invasive on sources

Oracle Data Integrator EE High Performance E-LT on target data warehouse

Periodic mini-batches (15 min) Transform in the Database Never go back to sources

Continuous streaming load into ODS Schema of target

Latency in seconds

ETL

On-Disk Logs

Oracle GoldenGate

Source 2 EMP DEPT

DIM FACT

DIM

Oracle GoldenGate
On-Disk Logs

EMP

DEPT

DIM

DIM

ODS Schema

DW Schema

22

Another case study

COMPANY OVERVIEW DIRECTV is a $17 billion provider of satellite-based television services. DIRECTVs 7,500 employees operate the companys broadcast centers, monitor satellites, and deliver service to about 17 million U.S. and over 5 million Latin American customers. GoldenGate PROVIDES Real-time data integration from Siebel CRM on Oracle to central Teradata Warehouse 1,500 service agents log 600,000 customer calls p/day GoldenGate moves 150-200 million records per day with 1.5 second latency.

CHALLENGES / OPPORTUNITIES Maintain high quality customer service in competitive market reduce churn! Centralize customer information for a single view to support sales, marketing, support & field service Significantly reduce data latency in central data warehouse for all queries & reports edict for < 15 minutes!

RESULTS Significantly reduced churn by 25% All business units have access to real-time business data.

One more case study

COMPANY OVERVIEW Overstock.com is an online closeout retailer offering high-quality, brand-name merchandise, including bed-and-bath goods, home dcor, kitchenware, watches, jewelry, electronics and computers, sporting goods, apparel and designer accessories among other products at discount prices GoldenGate PROVIDES Support for high availability & disaster tolerance across Oracle production sites Zero-downtime system migration and upgrade Enable active data warehouse roll-out in 90 days with real-time data feeds Complements Oracle Data Integrator for data transforms on target

CHALLENGES / OPPORTUNITIES Better understand customer purchasing behavior Provide timely information to support marketing, merchandising and operational decisions Improve shopping database reliability and uptime

RESULTS Customer analysis now done in minutes, rather than days Dashboard reports using real-time data for P&L & customer profitability Google Adwords adjusted based on realtime data reports Ranked #4 in customer service by National Retail Federation

How GoldenGate works?

Capture: Committed changes are captured (and can be filtered) as they occur by reading the transaction logs.
Trail files: Stages and queues data for routing. Route: Data is compressed, encrypted for routing to targets. Delivery: Applies data with transaction integrity, transforming the data as required.

Capture

Source Trail

Network (TCP/IP)

Target Trail

Delivery

Source Database(s)

Target Database(s)