SunGard Removes Batch Window Dependency for Its Enterprise Data Warehouse Using Real-Time Data Integration
Commentary by Susan Clark, Enterprise Data Warehouse Manager, SunGard Availability Services
As a pioneer in IT operations and information availability management, SunGard Availability Services delivers responsive, integrated, and reliable solutions, including disaster recovery services, managed IT and cloud services, information availability consulting services, and business continuity management software.
Challenges
In mid-2010, SunGard’s enterprise data warehouse (EDW) went into production to support its employees with a complete view into business operations. One of the key sources of information for the EDW is its Oracle E-Business Suite application, which updates the data warehouse on a nightly basis. The E-Business Suite production environment goes through nightly routine maintenance and application-related nightly batch processing that cannot be performed in parallel with the data warehouse loading process. Therefore, the nightly data warehouse refresh process could not finish in time to support SunGard’s global operations.
“We had both technical and business-oriented challenges in updating our enterprise data warehouse from our E-Business Suite application. Real-time data integration with Oracle GoldenGate enabled us to remove the batch window dependency as well as provide fresh data for our finance team to improve our decision making.”
—Susan Clark, Enterprise Data Warehouse Manager, SunGard Availability Services
In addition, the finance department needed more timely data for its business intelligence (BI) dashboards to help with the month-end close process. This meant that changes from E-Business Suite had to be captured multiple times during the day. The company considered creating a separate database to hold all of the changes that occurred throughout the day, but this would require purchasing additional hardware and software and incur related development costs. SunGard needed a cost-effective and reliable solution that would perform intraday updates and operate within the limited time window for data refresh without impacting E-Business Suite’s performance.
Solution
After a thorough evaluation, SunGard decided to implement a low-impact, real-time data integration solution with Oracle GoldenGate. Oracle GoldenGate captures changed data in the production system from database transaction logs throughout the day and categorizes them as “insert,” “update,” or “delete” for the EDW’s nightly refresh process. This frees up E-Business Suite from the nightly load process. In addition, the real-time data integration solution provides the data from the Oracle E-Business Suite financial tables throughout the day to meet the needs of the Oracle BI applications for finance. SunGard needed just three weeks to design, build, test, and migrate the real-time data integration solution to production.
Benefits
Using Oracle GoldenGate to capture all the intraday changes that take place on the selected tables allowed SunGard to remove the batch processing timeline–related constraints. The real-time data integration solution reduced the nightly extract process from the Oracle E-Business Suite application by nine hours. In addition, it improved information flow for the EDW significantly. It reduced the comparison time of full-data compares that would have been required for the 1,500 tables in the EDW staging area from eight hours to one hour, thus decreasing the total nightly refresh process for the EDW by seven hours.
In addition, by using Oracle GoldenGate, SunGard was able to avoid the hardware, software, and labor costs it would have incurred to store all changed data from the E-Business Suite application throughout the day. SunGard estimates an internal rate of return of 35 percent on this project.
The new solution also provides intraday updates for the Oracle BI application for finance, and displays the changes on Oracle BI dashboards as they occur. The data is immediately available for the finance team during the monthly close process, allowing them to effectively analyze and respond to specific situations on a real-time basis.
This article originally appeared in the issue of .