In OBIEE if you change any data in Data base while creating Dashboards/Reports, some times it won’t show you the latest data which is in database because of OBIEE internal Server Cache. To avoid this problem while creating the Report Click on Advanced tab and follow the steps given below.
1) Check the box Bypass Oracle BI Presentation Services Cache
2) In the Prefix field insert the given below statement and click on Apply SQL button
SET VARIABLE DISABLE_CACHE_HIT=1;
Then click “Apply SQL” button.
-> This method will fetch the data directly from the database. So now you can see the latest data in your reports.
Note: In some cases this method also won’t work…then you can go for another option clearing the presentation Cache instead of executing queries directly from Database which can be done by using two methods
(1) Clearing Presentation Cache from OBI Server
(2) Clearing Presentation Cache from Command prompt
-> Click on Administration link which is located at the top right section of the page.
-> Click on Issue SQL link which is located at the bottom left of the page under Maintenance and Troubleshooting section
-> Enter the following given SQL Statement in the SQL Statement section given and click on Issue SQL button
Command: call SAPurgeAllCache();
After you will be able to see the Successful message.