Transforming Data Using Oracle Analytics Cloud Data Sync

Before You Begin

This 15-minute tutorial shows you how to transform data while you load it into a database using Oracle Analytics Cloud Data Sync. You transform data during loading if the data isn't in the format you want. For example, you might add a default value to empty fields, or covert mixed-case values to upper case for consistency.

Background

Using Oracle Analytics Cloud Data Sync, you can transform and load data into a database so that you can then analyze the data in Oracle Analytics Cloud.

What Do You Need?

• Oracle Analytics Cloud Data Sync version 2.5
• Java Development Kit (JDK) V1.8
• Access to an Oracle Database Cloud Service instance, including:
• user name
• password
• URL
• Java Database Connectivity (JDBC) driver
• Download sampledatadenorm.csv.

Create Your Data Loading Project

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

1. From the directory where you installed Oracle Analytics Cloud Data Sync, run datasync.bat or datasync.sh.
2. In Enter Password, enter your password, click Remember password, and then click Login.
3. In the Welcome dialog box, click Create a New Project, enter Data Transform Demo, and then click OK.

Create Your Target Connection

Specify the connection details for your target database.

1. Click Connections, and then click New.
2. In the New Connection dialog box, click Name, and enter OracleDB.
3. In the Type field, select Oracle (Thin), and then click OK.
4. Click the Edit tab.
5. In User, enter the user name of your database account.
6. In Password, enter the password of your database account.
7. In URL, click the edit icon , enter the URL for your Oracle Database Cloud Service instance and then click OK. For example, jdbc:oracle:thin:@HOST_NAME:1521/SERVICE_NAME.
8. In JDBC Driver, click the edit icon , and enter the JDBC driver address for your Oracle Database Cloud Service instance, and then click OK. For example, oracle.jdbc.driver.OracleDriver.)
9. Click Test Connection, and then click OK.
10. Click Save.

Configure Your Data Load

Choose which columns and rows to load, and what to name the target table.

1. Click Project, click Source Data, and then click Data From Object(s).
2. In the Select Source dialog box, click File source, and then click OK.
3. In the Select File dialog box, in the File Location field, click the edit icon .
4. In the Open dialog box, navigate to and select the sampledatadenorm.csv file, and then click Next.
5. In the Import Options dialog box, click Next.
6. In the Configure Target dialog box, in the Create new field, enter T_PRODUCTS, and then click Next.
7. In the New Source File: Map Columns dialog box, click Select None.
8. In the Load column, select the product-related columns:
   • CHANNEL_NAME
   • PROD_ITEM_KEY
   • PROD_BRAND
   • PROD_LOB
   • PROD_TYPE
   • PRODUCT
   • PROD__ITEM_DESC
9. In the PROD_ITEM_KEY row, click Update Rows on Match, and then click OK.
10. Click Source Data, and in the Data from Object(s) tab, click the Targets tab.
11. In the Load Strategy column, click the edit icon , and then click Replace data in table.
12. Click OK.

Configure Your Data Transformations

Apply these data transformations:

• Convert CHANNEL_NAME values (for example, 'Online' and 'online') to uppercase to make the channel names consistent.
• Insert the default value LOB_01 for the empty PROD_LOB field to make identification easier.
• Concatenate the PROD_TYPE and PRODUCT fields to make the product names easier to read.

1. In the Targets tab, click Map Columns to display the Map Columns: File source dialog box.
2. In the CHANNEL_NAME row, click the edit icon in the Target Expression column, enter UPPER("%%T_PRODUCTS"."CHANNEL_NAME" ), and then click OK.
3. In the PROD_LOB row, click the edit icon in the Target Expression column, enter LOB_01 in the Default field, and then click OK.
4. In the PROD_TYPE row, click the edit icon in the Target Expression column, enter "%%T_PRODUCTS"."PROD_TYPE" || "%%T_PRODUCTS"."PRODUCT", and then click OK.
5. Close the Map Columns dialog box.

Load and Transform Your Data

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

1. Click Jobs, and then click New.
2. In the New Job dialog box, 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 "Data Transform Demo - MyProducts," click OK.
6. In Run Job, Request successfully submitted, click OK.

Check Your Data Transformations

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

1. In 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 5248 and the Failed Rows value is 0.

Want to Learn More?

• Loading Data With Data Sync
• Setting Up Data Loads from CSV or XLSX Files Using Data Sync
• Loading Data from Tables Using a SQL Query