• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 12, 2020
  • Reading time:6 mins read
You are currently viewing Fix for BI reports generating stale/old data due to Cache options
Fix for BI reports generating stale/old data due to Cache options

In this article we will look into the settings which impact the cache behavior of BI Publisher reports.

Normally when we develop BI Reports we create Data Model, Template and Report and tag the Data Model to Report and voila we are done with the BI Report.

However there are some settings which we also need to look into to see if the BI Report gets impacted.

BI Publisher has a caching mechanism of its own. If the BI Reports are showing up old/stale data or the BI Report is not taking into consideration the new template that you have attached to Report, then there is some issue with the cache settings on BI Report.

We need to navigate to Reports and Analytics > BI Catalog and go to the desired report. Click Edit and then click properties on the right top to set the report properties.

cache1 - Fix for BI reports generating stale/old data due to Cache options

Here is the snapshot of the Cache settings for a Report. We will look into the individual options now.

Enable Data Caching – This checkbox enables the caching for the report. The data generated by the online submission of this report is stored in the cache. Subsequent requests to run this report with the same parameter selections display the report using the data that is stored in the cache. This setting enhances performance by using stored data to generate a report rather than regenerating the data from the source. This option is used along with the Caching Duration, User Level, Document Caching and User Can Refresh Report Data flags..

Caching Duration (minutes): If the Enable Data Caching is setup then the number of minutes will be used to maintain the cache till that time. Ex: 30, the cache will be maintained on the server for 30 minutes post which the report will fetch data again. If the user runs this report again within those 30 min, the data which was fetched previously would appear on the report even though there are changes to data in this 30 minutes. Once the time limit has expired, the next request for the same report generates a fresh data set.

User Level: This property stores a separate cache for each user. The report data shown to each user comes only from the private cache. When enabled, this property ensures that each user can only see data that they are authorized to view. However, user-level cache has less efficient performance. If the report data is not user sensitive, you can disable this property to enhance performance.

Document Caching: Enable this property to cache the report document. With document cache enabled, when a user views the report online, the document (data plus layout) is placed in the cache. When any other user (unless User Level is enabled) uses the online viewer to view the exact same report (same layout, same output type, same parameter selections) the document is retrieved from the cache. The document remains in the cache according to the caching duration specified. Note that scheduled reports do not use document cache. Even if you upload a new template with same name, it will not be considered until this Document Caching is removed.

User Can Refresh Report Data: When this property is enabled, the user can choose to refresh the data on demand. When the user clicks Refresh in the report viewer, BI Publisher generates a fresh data set for the report.

Real time examples:

If you face issues with same data being retrieved even if you changed the query, then turn off the “Enable Data Caching” option. If it is turned off, then the other flags will not have any importance or the caching will not work at all.

If you face issues with new template not getting reflected and you want caching to improve performance, then uncheck the Document Caching option so that the report uses old data but new template to provide the output.

If you face issues with data not getting changed on the report even if you modify the SQL Query, then the user cache should have been removed with caching ON, so you are getting output of the report run by someone else. You need to enable the “User Level” check box.

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.