Reload a Dataset's Data

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

LiveLabs Sprint

Topics:

• About Reloading a Dataset's Data

• Reload Data from the Workbook Editor

• Reload an Individual Table in a Dataset

• Reload Tables in a Dataset

• Reload a Dataset's Files

• Reload a Dataset's Data Incrementally

• View a Dataset's Reload History

• View and Download Log Files for a Dataset Reload Job

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), you must ensure that the newer spreadsheet file contains 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, make sure that it contains 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 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 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 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 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 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 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
• Configure a Dataset to Load Incrementally
• Load Dataset Data Incrementally

Overview to Loading Datasets Incrementally

Keep your workbooks up-to-date in the most efficient manner by loading dataset data incrementally. Review this overview before you start.

Reloading data incrementally refreshes data efficiently and minimizes processing time.

What Incremental Load Types Are Supported?

In Oracle Analytics you can:

• Load new records and updates to existing records (known as upsert).
• Load new records only (known as insert).

Alternatively, if incremental loading isn't suitable, you can perform a full data load. For example, if a significant proportion of your data changes regularly, a full data load might be more efficient.

Description of the illustration set-incremental-load-1.png

Database Types that Support Incremental Loading for Datasets

• Oracle Database
• Oracle Applications
• Oracle Autonomous Data Warehouse (ADW)
• Oracle Autonomous Transaction Processing (ATP)
• 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 Time Zone (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.

Before you start, make sure that you have a dataset based on a database type that supports incremental loading. See Databases 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 the Actions menu, 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.
   
   
   Description of the illustration data-access-panel.png
   
   
   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.
   

   
   Description of the illustration expand-data-access-panel-option.png

   
   
6. Use the fields on the data access panel to configure the refresh.
   
   
   Description of the illustration set-incremental-load-1.png
   
   
   • 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 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.

     Alternatively, if you think incremental reload isn't appropriate for your dataset, select Replace Existing Data to reload all records (also known as a full load). For example, if a significant percentage of your data has changed, it might be quicker to do a full reload rather than an incremental load.

   • In Key Fields, specify the user key columns that identify unique records.
   • In New Data Indicator Column(s), specify the column or columns used to detect new or updated data. For example, you might select a unique transaction ID column to identify new transactions in the data source.
   • 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.
7. Repeat steps 3 to 6 for each table in the dataset that you want to reload.
8. Click OK.

You can now reload the dataset, either once or regularly. See Load Dataset Data Incrementally.

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 menu.
   
   
   Description of the illustration select-actions-menu-dataset.png
   
   
2. To load data once, click Actions, then click Reload Data.
   Click Run Reload, then select the tables to reload, then click Run Now.
   

   
   Description of the illustration set-incremental-load-2.png

   
   
3. To load data regularly, click the Actions menu, then 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 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 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 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 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 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 the illustration load-history.png
   
   
4. In the General tab, click Job details menu 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 in the top right-hand corner, then click View Log.
   
   
   Description of the illustration data_set_editor_view_reload_log.png