My problem:
- Working on Oracle Business Intelligence 11.1.1.7.1
- A report is generated dynamically from a stored procedure source, getting parameters from request variables set in presentation layer.
- The table object defined in Physical Layer and which uses a stored procedure as a source has the "Cacheable" option unchecked
- The analysis that uses the results from the stored procedure has the option "Bypass Oracle BI Presentation Services Cache" unchecked
- The results are shown in a table view which displays 20 rows at a time.
- Under this circumstances when the user uses the table view controls to move to the previous/next 20 records the report is re-executed, making navigation between table pages slow and impacting performance.
- When exporting to EXCEL or CSV the report is re-executed as well.
- Under "Administration"->"Manage Sessions", the report will show as many runs as i clicks i do on the controls.
I Would Like to:
- Report results to be saved when the report is executed, so when the user navigates in the report the UI performs fast and users can always information up to date.
- During a session, execution time for first run of a report with specific parameters is not relevant, subsequent executions within the same session with same parameters to be already cached.
Hope somebody can advice on a good caching strategy for this kind of situations
Thanks