OBIEE Dashboard Prompts Return No Results or Stale Data Even Though Data Actually Exists Within Data Warehouse (Doc ID 2381495.1)

Last updated on SEPTEMBER 13, 2023

Applies to:

Symptoms

ACTUAL BEHAVIOR
Dashboard column prompt values on existing dashboards (built to return data from basic subject area presentation SQL) are not refreshed after OBI Server (OBIS) cache is re-seeded from a dashboard page using the dashboard "Page Options > Refresh". Additionally, even when the dashboard prompt SQL is re-seeded through an alternate means (creation and refresh of an analysis which uses identical SQL as a dashboard prompt), the dashboard prompt will still yield stale data because it additionally pulls display from presentation server cache.

Therefore, the inability for re-seeding dashboard prompt OBIS cache and Presentation Server cache from within the UI of OBIEE as a non-administrator results in stale data to be presented within a dashboard prompt and no efficient method to re-seed the server cache to properly return updated values from the prompt.


ADDITIONAL INFORMATION
OBIEE 12c and later release versions is designed to return data from query cache (OBIS cache) seeded after first execution of presentation SQL. Starting with OBIEE 12c, select refresh from a dashboard (Page Options > Refresh) will prepend a "OBIS_REFRESH_CACHE=1" variable to the dashboard analysis presentation SQL to reseed OBIS cache and store/present updated data.

Reference:
Oracle Business Intelligence Enterprise Edition (OBIEE) BI Server Cache And Presentation Server Cache Default Settings With Primavera Analytics (Doc ID 2349232.1)

With regard to the refresh variable:

• The "OBIS_REFRESH_CACHE=1" variable is being properly appended for associated analysis presentation SQL on a dashboard (after selecting dashboard refresh), resulting in the analysis to display up-to-date data.
• The "OBIS_REFRESH_CACHE=1" variable is not being appended to dashboard column prompt (after selecting dashboard refresh), resulting in stale values returned from server cache.
• Additionally, there is no method for properly updating Presentation Server cache to properly display results even if server cache was updated, still resulting in stale data to return from a dashboard prompt.  Stale values could be missing data or no data (depending on when the analysis was first loaded to when data existed in the Data Warehouse).

EXPECTED BEHAVIOR
Dashboard prompts must have a method to return updated data from the target database it reads from (refreshing both OBIS and Presentation Server Cache).  For example, in OBIEE 12c, selecting the "Page Options > Refresh" link from a dashboard page should prepend the "OBIS_REFRESH_CACHE=1" variable to a dashboard prompt to re-seed OBIS cache *in addition* to whatever is required to clear presentation cache for the associated dashboard prompts.

STEPS TO REPRODUCE ISSUE
The issue can be reproduced at will with the following steps (using Primavera Activity Subject Area and Project Name column as example):

1. Create a dashboard column prompt using a single column (project name):
   
   1. Login to OBIEE
   2. Click New > Dashboard Prompt
   3. Select a configured subject area (Primavera - Activity)
   4. Click New dropdown > Column Prompt (within Definition grouping)
   5. In select column window:
      
      1. Expand Project > General - (Project)
      2. Select Project Name
      3.   Click OK
   6.   Leave all defaults in new prompt windows than select OK
   7.   Click Save to save the dashboard prompt
2. Create an analysis using same subject area/column as dashboard prompt.  Note: This will be utilized to test out whether the re-seed of OBIS cache from an alternate analysis is required to refresh data within dashboard prompt
   
   1. Click New > Analysis
   2. On Criteria tab:
      
      1. Select the same subject area as the dashboard prompt (Primavera - Activity)
      2. Add the same column as dashboard prompt (Project Name)
   3. Click Save to save the analysis
3. Seed OBIS cache using dashboard prompt:
   
   1. Login to OBIEE
   2. Browse to the catalog where the three files above exist (one dashboard prompt, two analysis)
   3. Select Edit on the dashboard prompt
   4. Select the Project name dropdown to load the project list
   5. Logout of OBIEE
4. Create new Project name data:
   
   1. Login to P6 EPPM
   2. Create a new project with one activity
   3. Publish the project and execute 'Publish Security'
   4. Execute Data Warehouse ETL (to move P6 EPPM data into Data Warehouse which OBIEE is configured to read from)
5. Reload dashboard prompt list from step 3:
   
   1. Login to OBIEE (if logged out)
   2. Browse to the catalog where the two files above exist (one dashboard prompt, one analysis)
   3. Select Edit on the dashboard prompt
   4. Select the Project name dropdown to load the project list
   5. Note that the new project is **not** displayed within the list (confirming that old, stale data is being returned)

BUSINESS IMPACT
Dashboard column prompts are used to refine existing dashboard and analysis filters.
Not properly updating a dashboard prompt display after updates were made to seeded data severely prohibits effective use of dashboard prompts to filter analysis data.

Cause

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