Indian Journal of Science and Technology

Abstract

Objectives: To develop optimization techniques that can apply to any Transactional Business Intelligence (TBI) Applications development process leading to significant improvements in the performance and scalability of TBI queries. Methods/ Statistical analysis: Benchmark experiments were conducted using Oracle RDBMS 11gR2 using representative SQL queries from Oracle’s Fusion TBI Applications. All four measures of SQL performance viz. SQL Response Time (RT), InputOutput (IO) Buffer Gets, Hard-Parse-Time and Shared Memory utilization were recorded with and without our proposed optimizations. The four performance measures were then compared to record actual improvements. Findings: Oracle TBI Applications are the result of a convergence of many technologies like the Application Development Framework (ADF), Web Logic Server (WLS) and Oracle Business Intelligence Enterprise Edition (OBI EE). TBI queries are generated at runtime by the OBI EE and ADF layers based on the transactional application schema, the ADF model and the logical, business and physical model layers of OBI EE metadata and optimizing this end to end process was the focus of our research. The benchmark experiments established very promising results. We recorded repeatable, significant gains in not across all measures of SQL performance. Our findings and recommendations can optimize both query and application performance. Adoption of our proposed solutions by OBI EE and TBI Fusion applications has provided significant performance and scalability improvements in enterprise OTBI applications. Even though our findings and recommendations were derived during a multi-year ADF-OBIEE application development environment, we are confident that these lessons would provide good guidance for developers embarking on architecting any new TBI applications. Application/Improvements: Our proposed recommendations can improve performance of all TBI applications that use an abstracted model and metadata of the transactional data model with query-generation engines to generate TBI SQL queries. 
Keywords: OTBI, Oracle, Performance Optimization, Query Generation, SQL Performance, Transactional Business Intelligence Queries