Working on Oracle Business Intelligence 22.214.171.124.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