E-BIP/OAS: After Updating the Table Values in the Database, Oracle BI Publisher Reports Show Old Data even though 'Caching' Properties are not Enabled. (Doc ID 2979468.1)

Last updated on FEBRUARY 13, 2024

Applies to:

Symptoms

When attempting to apply some changes in the database tables (e.g. change a name in the database table), the new data are not reflected in BI Publisher report.
It is working only when cache usage is disabled in the Data Model SQL used by the report via “SET VARIABLE DISABLE_CACHE_HIT=1” or if a Cache refresh is enforced along with the SQL using “SET VARIABLE OBIS_REFRESH_CACHE=1” the correct (updated) data can be seen.

The issue can be reproduced at will with the following steps:
1. Login to xmlpserver
2. Go to Catalog
3. Create a new DataModel
E.g.
select "Customers".C1 Customer Name" as "C1 Customer Name"
from "A - Sample Sales"."Customers" "Customers"
Note: BI Publisher Dataset uses Data Source as 'Oracle BI EE' and fetching data from Subject Areas.
Catalog Type: Oracle BI EE Catalog.
3. Create a new BI Publisher Report based on the new DataModel created.
4. Go to the database and update one of the existing names in the “Customers” table
E.g. Update "Customer name" from Name1 to Name2
5. Run the report again, by using the following tests:

   5.1. Test1: Report shows old/stale data.
      5.1.1. Go to Report Properties.
      5.1.2. Select second tab: Caching.
      5.1.3. Enable all the four check boxes:
         - Enable Data Caching
         - User Level
         - Document Caching
         - User Can Refresh Report Data

Result: Old value is shown (e.g. Name1) after changing the "customer name" in the table.

   5.2. Test2: Disable cache usage in the Data Model SQL:
      5.2.1. Open Data Model
      5.2.2. Select Data set name
      5.2.3. Edit the Data Set
      5.2.4. Disable cache usage in the Data Model SQL, by adding the following entry 'SET VARIABLE DISABLE_CACHE_HIT =1':

E.g.
SET VARIABLE DISABLE_CACHE_HIT =1;
select "Customers".C1 Customer Name" as "C1 Customer Name"
from "A - Sample Sales"."Customers" "Customers"

Result: New data is shown correctly (e.g. Name2).

   5.3. Test 3: Report shows old data.
1. Go to Report Properties.
2. Select second tab: Caching.
3. Disable all the four check boxes:
 - Enable Data Caching
 - User Level
 - Document Caching
 - User Can Refresh Report Data

Result: Report shows old data (e.g. Name1).

   5.4. Test 4: Report shows old data.
Catalog Type: Oracle BI EE Catalog
      5.4.1. Go to Administration > Catalog
      5.4.2. Set Cache Expiration (minutes) to zero ("0")

Result: Report shows old data (e.g. Name1).

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.