Incrementally Reload Your Datasets

Performing full dataset reloads to populate cache can be both time-consuming and inefficient, especially when only a few rows in your source system have been updated or added. Oracle Analytics’ incremental reload feature for cached datasets addresses this challenge by introducing a performance-optimized solution that reduces reload times and enhances efficiency.

Overview of Reload Options

Oracle Analytics offers three options for reloading cached data, which can be configured for supported data sources on the Edit Definition screen:

1. Replace Existing Data – Executes a full dataset reload.

2. Load New and Updated Data – Functions like an upsert; this reload type adds new records that don’t currently exist in the cache and updates existing records that have been modified since the last reload.

3. Add New Data to Existing Data – Functions like an insert; this adds new records that don’t currently exist in the cache.

Oracle Analytics provides incremental reload options for several sources, including Oracle Database, Oracle Applications, DB2, MySQL, among others.

Configuring Cache Reload Types

As stated, the cache can be configured on the Edit Definition screen at the table level for tables that pull from supported data sources. Once the table is set to Automatic Caching, there will be three Cache Reload Types that appear in a dropdown menu.

1. Replace Existing Data: When the table is set to Replace Existing Data, the entire cache is repopulated upon reload. No additional fields are required for configuration.

2. Load New and Updated Data: If the table is set to Load New and Updated Data, there are a few additional fields that must be configured.

• User Key Column(s): This required field is used to identify new records. Users can select multiple columns to identify unique records if necessary.
• New Data Indicator Column(s): This required field determines if data has been added or updated in the source system. The selected column(s) must be either date or timestamp.
• Incremental Offset Time: This optional field identifies how much OAC adjusts the reload time to account for timezone discrepancies in the data source system.        

3. Add New Data to Existing Data: Similar to Load New and Updated Data, when a table is set to Add New Data to Existing Data, the same fields need to be configured.

Executing a Reload

To reload a dataset, open the action menu and select ‘Reload Data.’ This will open the Inspect panel to a tab called ‘Reload Data.’ If your dataset table is configured with an incremental caching option, you will see two radio buttons: Run Reload, Run Full Reload, and a button: Run Now.

• Run Reload: When this option is selected and you click the Run Now button, the selected tables will reload based on their cache configuration.
• Run Full Reload: When this option is selected and you click the Run Now button, the selected tables will perform a full reload.

Example: Add New Data To Existing

Scenario: Oracle Analytics operates in UTC, but your source system records activities with timestamps in Pacific Time (8 hour difference).

Cache Configuration:

• New Data Indicator: transaction_timestamp
• User Key: transaction_id
• Inc Offset Time: 8 Hours                     

1. First Full Reload

• OAC Time: 6/11 04:00 (UTC) 
• Source Time: 6/10 20:00 (PT)

This is what’s currently in the OAC Cache after the full reload:

The following rows are added to the source system database:

2. First Incremental Load

• OAC Time: 6/11 06:00 AM (UTC)
• Source Time: 6/10 22:00 PM (PT)

With offset: Where transaction_timestamp > last reload time – offset time

• transaction_timestamp > 04:00 (UTC) – 8 Hours
• transaction_timestamp > 20:00 (PT) 6/10

New OAC cache after first incremental reload:

Call to Action

I encourage you to learn how to optimize your dataset reloads through the incremental reload feature. You can find more information about incremental reload in this YouTube video and this resource.