Oracle by Example brandingLoading Data Using Oracle Analytics Cloud Data Sync

section 0Before You Begin

This 15-minute tutorial shows you how to load data from a flat file data source into a database using Oracle Analytics Cloud Data Sync.

Background

Use Oracle Analytics Cloud Data Sync to upload and manage data. You can load data from CSV and XLSX files, relational sources (tables, views, and SQL statements), Oracle Transactional Business Intelligence (OTBI), JDBC data sources, and Oracle Service Cloud. You can load to relational tables or data sets.

After loading data with Oracle Analytics Cloud Data Sync, you can then analyze the data in Oracle Analytics Cloud.

What Do You Need?

section 1Create Your Data Loading Project

In Oracle Analytics Cloud Data Sync, create a new project to manage your data load.

  1. From the Oracle Analytics Cloud Data Sync installation directory, start Oracle Analytics Cloud Data Sync using datsync.bat or datasync.sh.
  2. In Enter Password, enter your password, click Remember password, and then click Login.
  3. In Welcome, click Create a New Project, enter My Demo Data Load, and then click OK.

section 2Create Your Target Connection

Specify the connection details for your target database. In this tutorial, an Oracle Database Cloud Service instance was used to perform the steps.

  1. Click Connections. In the Sources/Targets tab, click New.
  2. In New Connection, enter OracleDB in Name.
  3. From the Type field, select Oracle (Thin), and then click OK.
  4. In the Sources/Targets tab, select the OracleDB connection.
  5. In the Edit tab, enter the user name of your database account in User, and then enter your Password.
  6. In the URL field, click the check Edit icon. In the URL dialog box, enter the URL for your database instance, and then click OK.

    Your URL should look similar to the following: jdbc:oracle:thin:@HOST_NAME:1521/SERVICE_NAME.

  7. In the JDBC Driver field, click the check Edit icon. In the JDBC dialog box, enter the JDBC driver address for your database instance, and then click OK.

    Your URL should look similar to the following: oracle.jdbc.driver.OracleDriver.

  8. On the Edit tab, click Test Connection.
  9. In Connection Successful, click OK. In the Edit tab, click Save.

section 3Configure Your Data Load

In this section, you select the columns and rows to load to your database instance, and you name the target table.

  1. Click Project. On the Data Flows tab, click Data From Object(s). In Select Source, choose File source, and then click OK.
  2. In New Source File, on the Select File tab, click the check Edit icon in the File Location field.
  3. Navigate to and select the sampledatadenorm.csv file, and then click Next.
  4. In Import Options, click Next.
  5. In Configure Target, in the Create new field, enter T_PRODUCTS, and then click Next.
  6. In New Source File: Map Columns, click Select None.
  7. In the Load column, select the product-related columns:
    • PROD_ITEM_KEY
    • PROD_BRAND
    • PROD_LOB
    • PROD_TYPE
    • PRODUCT
    • PROD__ITEM_DESC
  8. In the PROD_ITEM_KEY row, click Update Rows on Match, and then click OK.

section 4Specify a Load Strategy

Choose how you'd like to keep your target data up-to-date.

  1. Click the Data Flows tab. In the row containing the T_Products target object, click the check Edit icon in the Load Strategy column, and then click Replace data in table. Click OK.

section 5Load Your Data

Create and run a data loading job to load your data.

  1. Click Jobs, and then click New.
  2. In New Job, enter MyProducts in Job name, and then click Next.
  3. In New Job, in the TARGET row, click Override With, and select OracleDB. Click Finish.
  4. In Configuring execution instance, click OK.
  5. Click Run Job. In Do you want to run the job "My Demo Data Load - MyProducts," click OK.
  6. In Run Job, Request successfully submitted, click OK.

section 6Verify the Data Load Success

Monitor the data loading job to check that the data load was successful.

  1. From the Jobs tab, click Runs.
  2. Review the Run Status column until you see the value Completed.
  3. Click Tasks, and make sure that the Successful Rows value is 11 and the Failed Rows value is 0.

more informationWant to Learn More?