Universe performance issue with large derived tables and prompts (BO 4.2 SP8)

general discussion
1

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:

  1. Is there a best practice to optimize derived tables in IDT?
  2. Would converting derived tables to database views improve performance significantly?
  3. Has anyone seen performance degradation specifically when prompts are combined with complex joins?
  4. 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.

2

I don’t know of any best practices specifically for Derived Tables. The same rules that apply to normal SQL building would apply there though.
Converting the derived tables to database views could improve performance. The main factor would be assuming that your database servers have more processing power than your client machines.
I have not observed any specific performance degradation for prompts involving complex joins, but by there very nature they would not perform as well as prompts with simpler joins.
It would depend on how your universe is organized and what objects are selected for the report to determine if aggregate awareness would help. The primary advantage of aggregate awareness is to reduce the joins needed for the reporting so it is possible. Unfortunately, you would just have to experiment with them to see if they have any impact.

The fact that your universe is multi-source will also have a negative impact on performance. It might be worth investing the work into re-working it into one or more single source universes now as multi-source universes will no longer be available beginning with BI 2025.