Implementing reporting with high datat volume and complexity requirements
During the planning processes users often must review large amounts of data in an esay to comprehend, condensed and clear format that helps merchandisers and buyers to compare figures across a large set of data columns and rows. Such needs are difficult to answer on the standard Oracle Retail Planning module UI regardless of whether a customer is using the nemw Fusion based UI or the traditional classic UI for planning.
For such purposes the use of a reporting engine is needed, with the ability to access data stored in the RPAS database. One solution for such a requirement is the Oracle Business Intelligence Enterprise Edition. OBIEE has the capability to report directly from the RPAS database via a special ODBC adapter provided by Oracle specifically for RPAS. Although this solution is ideal for reporting on small to moderate sets of data, in case of large data sets with complex formatting and aggregation levels and data structures, a more robust solution was needed due to problems experienced in performance of the RPAS ODBC driver when retrieving large amount of complex data from the RPAS database.
The problem
Unfortunatelly the RPAS ODBC driver, which is responsible for data retrieval from the multidimensional RPAS database, cannot provide the stability and the performance which is needed in the case of mass amount of complex data. Also as the driver shows a so called relational format of the multidimensional database structures – developers encounter decreased functionality in SQL queries that are used to access data from RPAS via the ODBC driver for RPAS.
In addition to the above limitation in the set of available SQL instructions via ODBC for RPAS, the usage of the the RPAS database for reporting purposes during daytime is also resource intensive, which is not recommended during the planning task flow when users are also accessing the RPAS database querying large set s of data and potentially also updating the same. Reporting from the same database that is used by users during daytime activities will slow its performance which in turn affects system response time to user queries. Unless absolutely real-time data is needed to be reported, the live RPAS production database should never be used for reporting during normal daytime operations. Reports should either be generated at night, or a seperate database, with a copy of the PRD data should be used for reporting.
If you are interested in our solution, check out the document below.