Why do I need to refresh Pentaho report data cache everytime there is a change in database

Question

I want to display Data not available if data does exist in the database, else simply display that data. I created a stored procedure in MySQL to achieve this and it's working fine when run in MySQL Workbench. The procedure takes has two input parameters and one output parameter to display the text "Data not available, if there is no data in the table.

When I call this procedure in Pentaho Report Designer (PRD), It shows data when there is data and data not available text when there is no data. But I have refresh report data cache every time truncate data from table or load data in the concerned table.

How can I get away with refreshing report cache manually every time there's change in database?

Answer 1

Try following to change cache timings in BA server.
1) Stop BA server.
2) navigate to \biserver-ee\tomcat\webapps\pentaho\WEB-INF\classes
3) Take back up of file ehcache.xml
4) Open File ehcache.xml
5) Scroll down to content.

<cache name="report-dataset-cache"
maxElementsInMemory="50"
eternal="false"
overflowToDisk="false"
timeToIdleSeconds="1"
timeToLiveSeconds="2"
diskPersistent="false"
diskExpiryThreadIntervalSeconds="1"
/>

6) Reduce the values of timeToIdleSeconds, timeToLiveSeconds, diskExpiryThreadIntervalSeconds (By default it would be around 300 seconds)
7) Save and Restart BA server.

To change in PRD:

Change according to the following image in PRD