OAC―Loading Data with Data Sync

Video 1.  Getting Started with Loading Data

Preparing business data in Oracle Analytics Cloud (OAC) for analysis, the first step is to upload your data:

• From Source Data
• You can load data from files (CSV and XLSX), various relational sources (tables, views, SQL statements), OTBI, JDBC data sources, and Oracle Service Cloud.
• To Target Data
• You can load to relational tables, data sets, Essbase cubes, or flat-files. 

In the upload process, the data is cleaned, transformed, cataloged and made available for data mining, online analytical processing, market research and decision support.

As a Data Loader, you can use Data Sync, REST API, or SQL Developer to upload data (watch video 1).  For illustration, we will only cover uploading data to the cloud using Data Sync in this article.

When to Use Data Sync

Use Data Sync when you want to:

• Load data
• From both Oracle and non-Oracle data sources, including CLOB andBLOB format.
• From a combination of data sources, such as CSV and Oracle relational data.
• Merge data 
• You can merge data from multiple sources.
• Transform data 
• You can transform your data while loading it, including formatting, joining, masking, and scrambling. 
• Schedule data loads
• You can replace, append, and update data in tables by scheduling data loads and using the Load Strategy option of this utility.
• Incremental data loads or rolling deletes.
• Insert-only or append strategies. 

Loading Data

With Data Sync you can quickly load data from a data source to OAC, Oracle Essbase, or flat-file format(e.g. CSV, ZIP, or XLSX).

Below are the tasks to be performed:

Task	Description	More Information
1. Download and install Data Sync	Download Data Sync from Oracle Technology Network and follow the installation steps.	http://www.oracle.com/technetwork/middleware/oac/downloads/index.html
2. Request permissions to load data	Work with your service administrator to ensure that you have permissions to load data.	Give Users Permissions toUpload Data with DataSync
3. Change default environment properties	Configure Data Sync for your local environment.	Set Default Options forData Sync
4. Create a new data loading project	Create a project, specify connections, set up the loading options, and load your data.	Quick Guide to Loading Data With Data Sync

Figure 2.  Project view with Data Flows, Script Processing, and Parameters sub-tabs

Data Loading Details

• User Permission
• To load data using Data Sync, you need a user account with appropriate privileges.  For instance, you need to assign users below application roles:
• BI Dataload Author - Enables Data Sync users to load data into a table. 
• DVContent Author - Enables Data Sync users to load data into a data set
• Default Options
• Data File Root Directory property
• To specify the default location for source files
• Proxy Host and Proxy Port 
• If your organization uses a proxy server to route calls to external websites,
• How much detail gets logged
• How long to keep log files
• New Data Loading Project (see Figure 2)
• When you create a data loading project, you specify which data columns to load and how you want to load them.
• In the Connections view, specify connection details:
•  See Specify Connection Details for Your Data Target
• Your data target might be Oracle Database Cloud Service, OracleAutonomous Data Warehouse, or Oracle Essbase.
•  See Specify Connection Details for a DataSource
• In the Project view, specify loading options
•  See Set Up Your DataLoad.
• At the Import Definition into <project> dialog box, choose a load type: 
• Discover object from catalog
• Type list of object names
• Enter object's properties manually
• Add target to an existing source
• In the Data Flows tab, 
• Click Edit to drill into source or target details, or update the data load strategy. 
• Click Column Mapping to add transformations or joins.
• In the Script Processing tab, run SQL commands before, during, or after a data load. 
• For example, you might want to create indexes or remove temporary tables. 
• In the Parameters tab, specify run-time values
• For example, Last Replication Date.

Transforming Data

You can use Data Sync to transform relational data if you‘re loading data into either Oracle Database Cloud Service or an on-premises database that is configured using the ‘Oracle (Thin)’ connection type.

These transformation types are supported:

• Uppercase - Convert lower-case characters to upper-case. 
• Lowercase - Convert upper-case characters to lower-case. 
• Trim - Remove part of a field. 
• Number - Convert a string to a number by stripping away non-numeric characters. 
• Mask (Custom, Simple, Null) - Hide parts of data fields. For example, you might want to hide credit-card numbers, or hide the first 12 digits of credit-card numbers. 
• Obfuscate (various) - Scramble data. For example, you might want to convert credit-card numbers to strings of alpha-numeric characters, or add a 30% variance to age data. 
• Obfuscate dates - Convert dates to a date range. For example, you might want to show approximate date of birth. 

More Transformation Options^[12]

You can use the Column Mapping dialog box or Mapping dialog box to configure your transformations.  For example, you can use Data Sync to transform your data 

1. With DefaultValues, Conversions,and Calculations
2. With New Target Columns
3. Using Joins
4. Using SurrogateKeys
• To improve performance

Figure 1.  Jobs view with Jobs, Schedules, and Runs sub-tabs.

Scheduling Data Load

After you have set up your data in Data Sync, you can use a job (see Jobs view in Figure 1):

• To load the data from the data source into the data target
• See Starting a Data Load
• To refresh data regularly by scheduling a job
• See Refreshing Data Regularly
• To monitor progress and respond to loading issues.
• See Monitoring Data Loads 

Scheduling Details

• Default Job
• Before you start, on the Jobs sub-tab, configure a job. 
• You can either use the default job that Data Sync created for you based on the current project name, or create your own job.
• Starting Data Load
• In the Jobs view, start the data load:
• Use the New Job dialog to specify a job name and loading details
• Use the Override With option to specify a data source and data target
• In the Configure Initial Extract dialog, specify whether you want to perform a full load or incremental load
• Scheduling Job
• In the Schedules sub-tab, click New to display the New Schedule dialog
• In the Job list, select the job
• In the Recurrence Pattern area to specify when and how regularly you want to load the data
• Monitoring Job
• In the Jobs view, use the Runs sub-tab to monitor the progress of the data load.

References

1. About Data Preparation (OAC) (OAC)
2. Preparing Data in Oracle Business Intelligence Cloud Service
3. Manage Database Connections for Data Models (OAC)
4. Use Data Modeler (OAC)
5. Create Your Initial Data Model From Relational Sources Using Data Modeler (Youtube)
6. OAC―Knowing Snapshot Basics (XML and More)
7. Data Sync Reference (OAC)
8. Book Review: "Oracle Warehouse Builder 11g R2: Getting Started 2011" (XML and More)
9. Getting Started with Loading Data (Youtube)
10. Oracle BICS / OAC - using Data Sync to upload data in DBCS (Youtube)
11. Get Started with Data Modeling in Oracle Analytics Cloud (Youtube)
12. Data Preparation, Cleansing, and Transformation with Oracle BI Cloud Services Data Sync (OAC Tutorial)
13. Learn Modern Data Visualization with Oracle Analytics
14. Click here for more A-Team Oracle Analytics (OAC) Blogs.