1. Visualizing Data in Oracle Analytics Server
  2. Visualize Data
  3. Manage Datasets
  4. Reload a Dataset's Data

Reload a Dataset's Data

Keep your analytics content up-to-date by reloading dataset data when it's been updated.

Tutorial icon LiveLabs Sprint

Topics:

About Reloading a Dataset's Data

You can reload data in a dataset to keep it up-to-date.

Reloading data ensures that workbooks and visualizations contain current data. The most current data is displayed in workbooks and visualizations after the dataset reload is complete and you refresh the workbooks. See Refresh a Workbook's Data.

How you reload a dataset depends on how the data is sourced.

Dataset Source Type How You Reload Data
Data from databases that support incremental loading

You can reload dataset data incrementally if the dataset uses a data source that supports this. Incremental loading only loads new or updated data. See Reload a Dataset's Data Incrementally.

Data from external connections When you reload data for a dataset with an external connection, the dataset's SQL statement is rerun and the current data for the tables with the Data Access field set to Automatic Caching is loaded into cache.

If your dataset contains one or more tables with the Data Access field set to Automatic Caching, then you can use the Data page to reload the tables in the dataset. You can also set up a schedule to reload your dataset's tables as often as once per hour. See Schedule a Dataset Reload.
Data from Excel, CSV, or TXT File When you reload a Microsoft Excel file (XLSX or XLS), the newer spreadsheet file must contain a sheet with the same name as the original file. And the sheet must contain the same columns that are in the dataset. The data reload fails if the file you load is missing columns.

When you reload a CSV or TXT file, it must contain the same columns that are in the dataset. A reload will fail if the file you load is missing columns.

To reload a dataset that uses one file as its source, use the Data.

Data from Oracle Fusion Cloud Applications Suite

You can reload data and metadata for Fusion Applications Suite data sources. If the Fusion Applications Suite data source uses logical SQL, reloading data reruns the dataset's SQL statement.

Reload Data from the Workbook Editor

When you're editing a workbook, you can reload data to update the workbook with the most up-to-date data.

  1. On the Home page, locate the workbook and click Open.
  2. Click the Visualize tab.
  3. On the Data panel, right-click the connection name or dataset name and select Reload Data.
    Description of reload-my-workbook-data.png follows
    Description of the illustration reload-my-workbook-data.png

Reload an Individual Table in a Dataset

Use the Dataset editor to reload data for an individual dataset table that uses a connection. Reloading queries the table's data source and loads the current data into cache.

If you want to reload a table that uses a file as its source, then see Reload a Dataset's Files.
The Reload option is available for any dataset table with the Data Access field set to Automatic Caching.
Typically you reload all of the dataset's tables at the same time so that the data is consistent across all tables. But sometimes it makes sense to reload only one of the dataset's tables. For example, where you know that the data has changed in a fact table but not in any of the dimension tables.
Reloading doesn't update the table's Profile data preview information. To see the most current data in the data preview, re-profile the table after you reload the table.
  1. On the Home page, click Data and then click Data.
  2. Click the Datasets tab.
  3. Hover over the dataset that you want to reload, click Actions, and then click Reload Data.
  4. Select the table, then click Run Now.
    Description of set-incremental-load-2.png follows
    Description of the illustration set-incremental-load-2.png

Reload Tables in a Dataset

Use the Datasets tab to reload data for the dataset's tables that use connections and have the Data Access field set to Automatic Caching. Reloading queries the tables' data sources and loads the current data into cache.

Note:

You can also programmatically reload data for an existing dataset that's based on a connection using the REST API. See Reload data for a dataset in the REST API for Oracle Analytics Cloud.

Reloading data ensures that workbooks and visualizations contain current data. The most current data is displayed in workbooks and visualizations after the dataset reload is complete and you refresh the workbooks.

If you want to reload a dataset that contains more than one table and uses files as its source, or if the dataset contains a combination of tables created from connections and files, then see Reload a Dataset's Files.
The Reload Data option is available for any dataset when the Data Access field is set to Automatic Caching for one or more table, and when the data source connection includes credentials for one or more table .
Dataset tables with the Data Access field set to Live aren't included in the reload. See Specify Whether a Dataset Table Is Cached or Live
The user who created or owns the dataset or a user with Read-Write privileges on the dataset and Read-Only privileges on the data source connection can reload the dataset's tables.
You can create and use schedules to run a dataset reload. See Schedule a Dataset Reload.
  1. On the Home page, click Navigator and then click Data.
  2. Hover over the dataset that you want to reload, click Actions, and then click Reload Data
    Description of data_set_editor_reload_data_set_option_oac.png follows
    Description of the illustration data_set_editor_reload_data_set_option_oac.png

    .
  3. Select the tables that you'd like to reload then click Run Now.
    Description of inspect-dialog-reload-data-tab.png follows
    Description of the illustration inspect-dialog-reload-data-tab.png

Reload a Dataset's Files

Reload data in a dataset based on a XLSX, XLS, CSV, or TXT file to make sure that you have the most up-to-date workbook content.

Before you start, make sure that the file you upload contains the same columns as the existing dataset.

Reloading a dataset's files ensures that workbooks and visualizations contain current data. The most current data is displayed in workbooks and visualizations after the file reload is complete and you refresh the workbooks.

Reload a dataset's files from the Home page
  1. On the Home page, click Navigator and then click Data.
  2. Click the Datasets tab.
  3. Hover over the dataset that you want to reload, click Actions, and then click Reload Data.
  4. In the Upload File dialog, drag and drop the file onto the dialog or click Select File to browse for the file.
    Description of data_set_editor_reload_file.png follows
    Description of the illustration data_set_editor_reload_file.png

  5. Click OK.
If you're working in the dataset editor, you can reload data by right-clicking the dataset in the Join Diagram and then selecting Upload File.
Description of reload-file-based-dataset.png follows
Description of the illustration reload-file-based-dataset.png

Reload a Dataset's Data Incrementally

Keep your analytics content up-to-date by reloading datasets efficiently when new data is available in the source database.

Topics:

Overview to Loading Datasets Incrementally

You can keep your workbooks up-to-date by implementing incremental dataset loads.

Reloading data incrementally refreshes data and can minimize processing time.

Where Do I Configure Incremental Loading?

In Oracle Analytics you configure incremental loading for datasets using the Cache Reload Type in the data access options.
Description of set-incremental-load-1.png follows
Description of the illustration set-incremental-load-1.png

What Incremental Load Types Are Supported?

In Oracle Analytics you can:
  • Load new records and updates to existing records, known as upsert, using the Load New and Updated Data option.
  • Load new records only, known as insert, using the Add New Data to Existing Data option.

If incremental loading isn't appropriate for you data, then you might choose to reload all data by selecting the Replace Existing Data option. For example, if a significant proportion of your data changes regularly, then a full load might be more efficient.

Database Types that Support Incremental Loading for Datasets

  • Oracle Database
  • Oracle Applications
  • Oracle Autonomous Data Warehouse (ADW)
  • Oracle Autonomous Transaction Processing (ATP)
  • Oracle Cloud Infrastructure Object Storage
  • Oracle Talent Management Cloud/Oracle Talent Acquisition Cloud (Taleo)
  • DB2
  • Informix
  • MySQL
  • SQL Server
  • Sybase ASE and Sybase IQ

Working With Data in Different Time Zones

If there's a time difference between the timestamps in your data and the default Oracle Analytics timezone (Universal Time Coordinated or UTC), you can specify an offset that takes into account the time difference so that your data loads correctly. For example, if your source system records in Pacific Standard Time (PST), which is eight hours behind UTC, set the incremental offset time to 8 hours. See Configure a Dataset to Load Incrementally.

Configure a Dataset to Load Incrementally

Keep your datasets up-to-date by configuring them to load incrementally. For example, if a source system has new records, load just the new records to your dataset to minimize system traffic and reduce processing time.

After completing this configuring, you can reload the dataset incrementally.
Before you start, make sure that you have a dataset based on a database type that supports incremental loading. See Data Sources that Support Incremental Reload for Datasets.
  1. On the Home page, click Data from the Navigator, and hover over the dataset you'd like to refresh.
  2. Click Actions Actions option to list things you can do, then click Open.
  3. In the Join Diagram, double-click the table you want to refresh.
  4. Click Edit Definition.
  5. Make sure that the data access options are displayed at the right-hand side.
    If the data access options aren't displayed, hover over the center of the right edge of the window to locate the Expand option, then click Expand.
    Use the fields on the data access panel to configure the refresh.
  6. In Data Access, select Automatic Caching.
  7. In Cache Reload Type, specify the type of incremental refresh.
    • Select Load New and Updated Data (also known as upsert) to load new data when a new record is available or an existing record is updated. For example, you might want to load new transactions as well as changes to existing transactions.
    • Select Add New Data to Existing Data (also known as insert) to load new data when a new record is available. For example, you might want to load new transactions only.
  8. In User Key Column(s), specify the user key columns that identify unique records. Click the field to display available columns and select one or more columns.
  9. In New Data Indicator Column(s), specify the column or columns used to detect new or updated data. For example, you might specify a unique transaction ID column to identify new transactions in the data source.
    If you don't specify a new data indicator column, Oracle Analytics uses the file-modified timestamp to determine when new data is available.
    If you're loading data data from files in Oracle Cloud Infrastructure Object Storage, the New Data Indicator Column field isn't displayed because Oracle Analytics uses the file-modified timestamp.
  10. In Incremental Offset Time, change the default (None) if there's a time difference between the timestamps in your data and the default Oracle Analytics timezone (Universal Time Coordinated or UTC). For example, if your source system records in Pacific Time Zone (PST), which is eight hours behind UTC, set the incremental offset time to 8 hours.
  11. Click OK.

Load Dataset Data Incrementally

Reload dataset data incrementally to keep workbooks up-to-date. You can load data once or regularly.

Before you start, configure your dataset to load incrementally. See Configure a Dataset to Load Incrementally.
  1. On the Home page, click Data from the Navigator, and hover over the dataset you'd like to refresh to display the Actions Actions option to list things you can do option.
    Description of select-actions-menu-dataset.png follows
    Description of the illustration select-actions-menu-dataset.png

  2. To load data once, click Reload Data.
    Click Run Reload, then select the tables to reload, then click Run Now.
    Description of set-incremental-load-2.png follows
    Description of the illustration set-incremental-load-2.png

  3. To load data regularly, click New Schedule.
    Use the Schedule dialog to specify when you'd like to start the refresh and how regularly to refresh the data, then click OK.
    Description of set-incremental-load-3.png follows
    Description of the illustration set-incremental-load-3.png

Use the Schedules tab on the Inspect dialog to review updates. See View a Dataset's Reload History and View and Download Log Files for a Dataset Reload Job.
If you drill into the task-level details for an incremental load, you'll see that the Refresh Mode is set to "Incremental".
Description of task-level-log-details-job.png follows
Description of the illustration task-level-log-details-job.png

View a Dataset's Reload History

Historical information is generated for dataset reloads at the job level and at the table level.

Use a dataset's reload history to find out information such as how the reload was executed (manually or by schedule), job status, start time, end time, and the amount of data reloaded in bytes. You can also drill into information about the job's reloaded tables. For example, the table's source name, reload status, and number of rows.
Oracle Analytics generates historical information when you reload datasets that use connections.
Oracle Analytics doesn't generate historical information and log files when you reload datasets that:
  • Use only files.
  • Use a connection and a file. Oracle Analytics generates historical information only for a dataset's tables that use a connection.
  • Use an Oracle EPM Cloud or Oracle Essbase connection.
  1. On the Home page, click Navigator and then click Data.
  2. On the Datasets tab, hover over a dataset, then click Actions, and then click Inspect.
  3. Click the History tab to see data load jobs that have completed on that dataset.
    Description of load-history.png follows
    Description of the illustration load-history.png

  4. Click a job in the list to display the General tab, which provides an overview of the job.
  5. Optional: Click the Status tab display tasks that were executed as part of the job.
    Description of data_set_editor_reload_history_status.png follows
    Description of the illustration data_set_editor_reload_history_status.png

    Click a task in the list to display log information for the task. If the load was incremental, you'll see the Refresh Mode set to "Incremental".
    Description of task-level-log-details-job.png follows
    Description of the illustration task-level-log-details-job.png

View and Download Log Files for a Dataset Reload Job

You can review log files for dataset reloads to help you troubleshoot dataset reload issues.

For example, if you have an issue reloading data, the logs can help you determine if it's a problem with your configuration, such as an invalid or outdated password or an issue with too much data. In such cases, the log files contain time stamps for key operations and information on the number of records processed. You can review logs at the job level and at the table level.
Oracle Analytics generates log files when you reload datasets that use connections. Log files aren't generated if a dataset uses one or more files, or uses an Oracle EPM Cloud or Oracle Essbase connection.
  1. On the Home Page, click Navigator and then click Data.
  2. On the Datasets tab, hover over a dataset, click Actions, and then click Inspect.
  3. Click the History tab, then click the job that you want to look at.
    Description of load-history.png follows
    Description of the illustration load-history.png

  4. In the General tab, click Job details menu Ellipsis in the top right-hand corner and select View Log to view the log, or select Download Logs to save the logs to a local file area.
  5. If the job reloaded more than one table and you want to view log information for a specific table, then click Status, then the table's reload task name, then click Job Task Menu Ellipsis in the top right-hand corner, then click View Log.
    Description of data_set_editor_view_reload_log.png follows
    Description of the illustration data_set_editor_view_reload_log.png