Performance Optimisation for Heavy BI Reports

Why BI reports are slow

Report performance issues fall into four categories:

1. SQL query performance: the data model SQL is slow
2. Large result sets: too many rows being processed
3. Template rendering: complex RTF/XSL processing
4. Delivery bottlenecks: email, FTP, or UCM delivery taking time

Address them in this order — SQL is almost always the primary cause.

Diagnosing BIP report SQL performance

Enable query tracing for the BIP service user in Oracle DB, then run the report:

ALTER SESSION SET sql_trace = TRUE;

Analyse the resulting TKPROF output to find:

• Full table scans on large tables
• Missing indexes
• Poor cardinality estimates

Or test directly in SQL Developer: copy the data model SQL, substitute sample parameter values, and check the execution plan.

Data model SQL optimisation

Add pagination to limit initial result set: most users don’t need all 50,000 rows — add a default date range filter. Change WHERE 1=1 to WHERE creation_date >= :P_FROM_DATE.

Avoid functions on indexed columns in WHERE: WHERE TO_CHAR(creation_date, 'YYYY') = '2026' can’t use an index on creation_date. Use: WHERE creation_date >= DATE '2026-01-01' AND creation_date < DATE '2027-01-01'.

Replace correlated subqueries with JOINs: correlated subqueries in SELECT clauses execute once per row. Convert to LEFT JOINs.

Use WITH (Common Table Expressions) for complex queries: improves readability and often improves Oracle’s optimisation.

Template rendering performance

Complex RTF templates with many nested for-each loops and conditional sections can be slow to render on large datasets. Optimise:

• Move aggregations to the data model SQL rather than computing in template
• Reduce conditional nesting depth
• For very large reports, consider generating Excel format (faster than PDF rendering)

OTBI analysis performance

Slow OTBI analyses are usually caused by:

• Missing filters on large subject areas (always add date range filters)
• Too many columns in a single analysis (Oracle optimises each column’s SQL separately)
• Aggregate calculations that can’t be pushed to the database

Use “Explain the SQL” in OTBI (right-click analysis → Explain the SQL) to see the generated query and identify optimisation opportunities.

Scheduling vs interactive

Move heavy, complex reports to scheduled jobs that run overnight. Interactive reports should return in under 10 seconds — if they can’t, redesign as scheduled or add mandatory filters that restrict the result set.