Hi all,
I’m running into a performance issue in SAP BO 4.2 SP8 and wanted to get some feedback from others who may have faced something similar.
Environment:
- SAP BO 4.2 SP8
- Oracle 19c backend
- WebI reports
- Universe built in IDT (multi-source)
Scenario:
We have a universe that includes a few derived tables (based on complex joins across 6–8 tables). The derived tables are necessary due to business logic that cannot easily be modeled using standard joins.
The problem appears when:
• Users apply multiple prompts
• A date range filter is combined with 2–3 dimension filters
• Report includes 4–5 aggregated measures.
Query execution time increases dramatically (from ~10 seconds to 2–3 minutes).
Database side:
- Tables are indexed properly
- Execution plan shows heavy temp space usage
- Derived table SQL seems to be materialized repeatedly
Questions:
- Is there a best practice to optimize derived tables in IDT?
- Would converting derived tables to database views improve performance significantly?
- Has anyone seen performance degradation specifically when prompts are combined with complex joins?
- Would using aggregate awareness help in this case?
We are trying to avoid rebuilding the entire universe if possible.
Any suggestions or architecture recommendations would be appreciated.
Thanks in advance.