Similar to OBIEE the Query Cache can be disabled on Report Level within BICS.
However, the approach is slightly different to OBIEE, since BICS doesn’t provide the Pre or Post-fix box on the Advanced Tab of the Report Builder (Answers).
The below example shows the configuration and Behavior of disabling the Query Cache in a Step-by-step approach:
When creating a (simple) Query, a Cache entry is created for the returned result set:
For any subsequent Report, the Cache is analyzed if it can be used to fulfill the subsequent Report. E.g. when adding a Filter for Channel “Catalog”, the above Result set could be used:
Giving the following Result:
When analyzing the Query log, the Cache hit is also shown in the log itself:
In order to bypass the Query Cache, the Query Cache needs to be disabled for the respective Report by adding the following command (setting of internal server variable) into the Report:
SET VARIABLE DISABLE_CACHE_HIT=1;
To do this, the SQL displayed on the Advanced Tab of the Report Builder (Answers) needs to be copied to the clipboard and clicking the new Analysis button.
The previously copied SQL needs to be pasted in the popup Windows with the Disable Cache Hit command as prefix and the 0 needs to be removed from the pasted SQL:
The Presentation Server Cache can also by bypassed by checking the check-box at the top of the screen. When navigating to the Results tab of Answers, the Report is run again and the Query log can be checked again:
The Query log also shows that now the Query is not retrieved from the Cache, but retrieved from the Database.
A Technote also exist for further information on Oracle Support.