The extract-transform-load (ETL) process was performance tuned to run 3x per day during the monthly and quarterly financial close process to optimally support the finance department during this critical and time-sensitive period. In addition, extract-transform-load (ETL) code was written to export the general ledger tables to flat files which were used as a source for Essbase cubes in "what-if analysis".
This project was unique with Oracle E-Business Suite integrated with both Oracle Financial Analytics and Essbase. Oracle Financial Analytics was used for Generally Accepted Accounting Principles (GAAP) reporting where the rollup hierarchies were maintained in the ERP system and limited "what-if analysis" was required.
Essbase was used for statutory reporting. Statutory hierarchies were different from the GAAP hierarchies and were not stored in the ERP system. Essbase was an excellent place to maintain these statutory hierarchies and do "what-if analysis" to iteratively ensure that the correct information was provided to state insurance regulators. The statutory reports were delivered using Web Analysis, Hyperion Financial Reporting and SmartView (Excel Add-in for Essbase).
Because of the success of the application customizations and utilization of Essbase for what-if analysis, the approach was been adopted by Oracle's Product Development Team for later releases of the Oracle BI Applications.
The project was also unique for its data security requirements. A custom data security model was built in the data warehouse which mimicked the data security rules in Oracle E-Business Suite.
The final result was an on-budget and on-time simultaneous deployment of Oracle E-Business Suite and Oracle Financial Analytics.