OBIEE Dashboard Prompts Return No Results or Stale Data Even Though Data Actually Exists Within Data Warehouse

(Doc ID 2381495.1)

Last updated on APRIL 04, 2018

Applies to:

Primavera Analytics Cloud Service - Version 17.11.0.0 and later
Primavera Analytics - Version 17.11.0.0 and later
Information in this document applies to any platform.

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 prepends 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:

 

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.

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms