View the Warehouse Refresh Statistics

Using an additional mechanism, you can view the details of data refresh in the warehouse and the tables that got changed.

The following three tables contain information that you can use for data refresh details. To view these tables, you can use tools such as SQL Developer and connect to the Autonomous Data Warehouse associated with your Fusion Data Intelligence instance as OAX_USER. These tables will be populated for your instance after the feature is available. The tables retain values for about 6 months and then archived.

This capability is available in a phased rollout.

Table	What does it contain?	When is it updated?
FDI_REFRESH_SUMMARY	Summary of each data refresh including scheduled time, completion time and published record count. One row for each refresh.	Updated when the refresh goes In-progress and when the refresh completes (includes post-processing).
FDI_REFRESH_STAGES	Contains completion time for refresh stages. One row for each stage.	Updated at completion of each stage for Priority, Primary, Secondary, as applicable.
FDI_REFRESH_FACTORS	Factors affecting each refresh. One row for each factor, as applicable.	Updated if there are any factors that affected the refresh. Supported factors are source is in maintenance and customer High service ADW sessions.

Table Schema Details

Following are the schema details of these tables:

FDI_REFRESH_SUMMARY

Column	Data Type	Values
REFRESH_ID	VARCHAR2	Unique ID to join with other tables
SOURCE_TYPE_CODE	VARCHAR2	All supported sources. Same codes as the current mechanism
REFRESH_TYPE	VARCHAR2	Activation, Incremental, On Demand Refresh, App Upgrade, Full Reload Source, Data Maintenance, Prioritized Refresh
NUM_STAGES	NUMBER	1 to 3
STATUS	VARCHAR2	IN PROGRESS, COMPLETED
SCHEDULED_TIME	TIMESTAMP	-
COMPLETION_TIME	TIMESTAMP	-
PUBLISHED_REC_COUNT	NUMBER	-

FDI_REFRESH_STAGES

Column	Data Type	Values
REFRESH_ID	VARCHAR2	Unique ID to join with other tables
STAGE_NAME	VARCHAR2	PRIO_DATA_PUB_ORD_0, PRIO_DATA_PUB_ORD_1, DATA_PUBLISHED, POST_PROCESSING
COMPLETION_TIME	TIMESTAMP	-

FDI_REFRESH_FACTORS

Column	Data Type	Values
REFRESH_ID	VARCHAR2	Unique ID to join with other tables
FACTOR_CODE	VARCHAR2	SOURCE_MAINTENANCE, CUSTOMER_HIGH_ADW_SERVICE
FACTOR_DETAILS	VARCHAR2	Additional details for the factor such as minutes

Note:

When FACTOR_CODE is SOURCE_MAINTENANCE, the FACTOR_DETAILS indicate the number of minutes the source was under maintenance during this request execution and data couldn't be extracted. When FACTOR_CODE is CUSTOMER_HIGH_ADW_SERVICE, the FACTOR_DETAILS indicate the number of HIGH service sessions from users observed during the request execution.

Here are some sample queries to use these tables:

• View Refresh Summary for the Last 1 Day using this query:

   SELECT * FROM FDI_REFRESH_SUMMARY WHERE COMPLETION_TIME > SYSDATE - 1;

• View Stage Details for a Specific Refresh using this query:

  SELECT * FROM FDI_REFRESH_STAGES WHERE REFRESH_ID = <REFRESH_ID>;

• View Impacted Refresh Factors by Refresh ID using this query:

  SELECT * FROM FDI_REFRESH_FACTORS WHERE REFRESH_ID = <REFRESH_ID>; 

View Changed Tables

To view the tables that got changed, you can use the FDI_REFRESHED_DATASETS table. It shows the warehouse tables refreshed and the time-stamp at which they are refreshed.

Here are some sample queries:

• View all the datasets that are refreshed using this query:

  SELECT table_name, to_char(last_refresh_time,'DD-MON-YYY HH24:MI:SS') as last_refresh_datetime FROM fdi_refreshed_datasets;

• View all the datasets refreshed during last 2 days using this query:

  SELECT * FROM fdi_refreshed_datasets WHERE last_refresh_time > sysdate - 2; 

• View all the datasets refreshed during last 1 hour using this query:

  SELECT * FROM fdi_refreshed_datasets where last_refresh_time > sysdate-INTERVAL '1' HOUR;