2Overview of Oracle Data Loader On Demand
Overview of Oracle Data Loader On Demand
This chapter provides an overview of Oracle Data Loader On Demand, and it describes how to install Oracle Data Loader On Demand. It includes the following topics:
Introduction to Oracle Data Loader On Demand
The Oracle Data Loader On Demand client provides a means of importing data into Oracle CRM On Demand from external data sources. It has the following supported operations:
Insert operation. This operation takes records from a file and adds them to Oracle CRM On Demand.
Update operation. This operation modifies existing records in Oracle CRM On Demand, using records from an external data source. The external records must provide a value that uniquely identifies a record in Oracle CRM On Demand, such as External System ID or Row ID. Because External System ID and Row ID are used to identify the target record, those fields cannot be modified using Oracle Data Loader On Demand.
Upsert operation. This operation is a combination of the Insert operation and the Update operation. Using records from an external data source, the Upsert operation first checks for the existence of the external record in the Oracle CRM On Demand database. The external records must provide a value that uniquely identifies a record in Oracle CRM On Demand, such as External System ID or Row ID. If the external record already exists in the Oracle CRM On Demand database, the Upsert operation then performs an Update operation on the record. If the external does not exist in the Oracle CRM On Demand database, the Upsert operation performs an Insert operation and inserts the external record into the Oracle CRM On Demand database.
Oracle Data Loader On Demand can be used for both small and large loads. However, larger loads (millions of records) require further sizing considerations. There are many factors to consider when inserting or modifying a large amount of data. Contact Customer Care if you plan to do large data loads or if the characteristics of your company's data load change dramatically. For example, contact Customer Care if you have been inserting one million records weekly and then suddenly need to increase to four million records.
Determining Row ID Support
To determine whether an object supports Row ID, complete the following procedure.
To determine whether an object supports Row ID
Navigate to Admin, then Import and Export Tools.
Select an object under Import Data.
If the option On Demand Row ID is listed as an import method, that indicates that the chosen object supports Row ID.
Getting Started with Oracle Data Loader On Demand
Oracle Data Loader On Demand is a command-line utility, and it runs separately from Oracle CRM On Demand. The following summary explains how you use Oracle Data Loader On Demand.
You prepare an external data file in a comma-separated value (CSV) file.
You create a map file and a properties file that contains parameters for Oracle Data Loader On Demand for processing the data file. Because properties files are sets of instructions, you can reuse them on multiple data files. You can create a library of properties files and map files for actions that you use routinely.
From a command-line prompt you run the Oracle Data Loader On Demand providing the names of the files to process on the command-line.
Software Requirements for Installing Oracle Data Loader On Demand
Oracle Data Loader On Demand is a Java-based client which can run on any operating system. However, only Microsoft Windows is currently officially supported. Oracle Data Loader On Demand has the following software requirements:
Java Runtime Environment (JRE) JRE 1.7.0_131 or later
A ZIP utility to extract the files
To check whether you have Java Runtime Environment installed, and have the correct version, perform the following procedure.
To check whether the appropriate Java Runtime Environment is installed
In Windows, open the Control Panel:
In Classic View, double-click the Java icon.
In Category View, select the Other Control Panel Options, and then click the Java icon.
Note: If the Java icon is not present, then you do not have Java Runtime Environment installed.In the Java Control Panel window, click the Java tab, and then click View.
In the Platform column, make sure that version 1.7.0_131 or later exists and that it is enabled.
To get the latest version of Java Runtime Environment, navigate to:
http://www.java.com
Installing Oracle Data Loader On Demand
To access the download page, you must have the Admin Import privilege. To run Oracle Data Loader On Demand, you must have the Enable Web Services Access privilege. To assign both privileges, you edit your role in step 4 of the Role Management wizard. For more information on the Role Management wizard, see Oracle CRM On Demand Online Help.
To download Oracle Data Loader On Demand
In Oracle CRM On Demand, navigate to Admin, then Import and Export Tools.
Click Oracle Data Loader On Demand.
When prompted by the browser, choose the option to save the file to your hard disk.
Extract the Oracle_DataLoader_OnDemand.zip file to a directory on your computer.
Note: The contents of the ZIP file include files within folders. Make sure to maintain the folder structure by using the extraction feature from the menu bar of your compression application. Do not drag the files from that application and drop them into a folder on your hard drive because the folder structure will not be maintained.Review the readme file for any updates that might not be included in this guide.
The readme file contains information about Oracle Data Loader On Demand and is included in the Oracle_DataLoader_OnDemand.zip file.
Which Method Should You Use to Import Records?
You can import records using the Import operation, Oracle Data Loader On Demand, or Web services. The following information can help you choose the method that best suits your needs:
Import Assistant. Use the Import Assistant if the following conditions apply:
You want to process records sequentially, where record 1 is processed before record 2, and so on.
You are a new user, or you want to use a user interface that guides you through the process.
You want to associate records using Predefined Fields in Oracle CRM On Demand, as opposed to associating records using External System ID.
You want detailed error messages, which is important if you are performing a small test before a large data load.
You do not have a map file and want to create a map file using the Mapping Wizard page of the Import Assistant.
For information on the Import Assistant, see Oracle CRM On Demand Online Help.
Oracle Data Loader On Demand. Use Oracle Data Loader On Demand if the following conditions apply:
You want to automate your data loads.
You can allow your data to be processed in any order.
You have files that are larger than the allowable limits in Oracle CRM On Demand Import. The limits for each record type can be found in Oracle CRM On Demand Online Help.
You want to schedule an Insert, Update, or Upsert operation.
Your data has no errors and does not require much validation.
Web services. Use Web services if the following conditions apply:
You require operations that are not supported by the Import operation or Oracle Data Loader On Demand.
You require multiple actions for each record. For example, you read a record, merge it with another record, associate it with a third record, and then delete a few of its child records.
You require operations performed directly on child records. For such operations, use the Web Services 2.0 API.
For information on Web services, see Oracle Web Services On Demand Guide. This guide is available from Oracle Technology Network and from within Oracle CRM On Demand (see Downloading Oracle Web Services On Demand Guide).
Downloading Oracle Web Services On Demand Guide
Navigate to Admin and then to Web Services Administration.
Click Download Documentation, and choose Oracle Web Services On Demand Guide.
Differences Between Using the Import Operation of Oracle CRM On Demand and Oracle Data Loader On Demand
The following table compares and contrasts the differences between using the Import operation of Oracle CRM On Demand and Oracle Data Loader On Demand.
Table Differences Between the Import Operation of Oracle CRM On Demand and Oracle Data Loader On Demand
| Import Operation of Oracle CRM On Demand | Oracle Data Loader On Demand |
|---|---|
Data can be processed sequentially or in any order (optional). |
Data is processed in any order. |
CSV data file submission cannot be interrupted. |
CSV data file submission can be halted and resumed. |
What Factors Affect the Processing Speed?
The queuing mechanism and the throughput rate affect the processing speed and consequently the time it takes to process records.
About the Enhanced Queuing Mechanism
The queuing mechanism allows the server to distribute the resources more efficiently for all users. Thus, a user with a smaller import or data load no longer has to wait for a large import or data load to finish until the small import is processed.
When Oracle Data Loader On Demand submits a request to Oracle CRM On Demand, the file is divided into smaller segments that are processed in parallel by all available tasks on the server. For example, when you submit a file with 4,000 accounts and there are four tasks available on the server, each task processes a data file of 1,000 records. Because of this separation, the server can process the records in any order. For example, records 1001 to 2000 can be processed before records 1 to 1000.
If there are more CSV data segments than available tasks, then the CSV data segments are queued. This is true for all users who submit data using Oracle Data Loader On Demand.
About Throughput Rates
The throughput rate is the number of records processed during a specific period, such as 1 second. You calculate the throughput rate by dividing the total amount of time it takes to process the data by the number of records processed.
What Factors Affect the Throughput Rates?
The following factors directly or indirectly affect the throughput rate of Oracle Data Loader On Demand:
Type of object (record type). When a record in Oracle CRM On Demand is processed, many records might have to be created or updated. Depending on the complexity of the record type, the number of underlying database records might differ substantially. As a result, the throughput rates differ. The difference is based on the record type that is selected.
Type of operation (Insert, Update, or Upsert). Throughput rates vary by operation. Update operations are usually faster than Insert operations, because Insert operations might have to write many records and update indexes. Upsert operations are a combination of Insert operations and Update operations and as a result you must include both factors in your estimate.
Type of fields (picklist, date, description). Some fields require more processing time for validation than others. Picklists, description fields, and dates might process more slowly than Boolean and text fields.
The number of fields mapped. The more fields you map, the slower the processing of the records. For best results, remove the unnecessary fields.
Data quality. The more errors in the data file, the more data validation must occur, and more log messages must be written to the log file.
Log Level. The higher the level of the log file, the more processing time is required, which slows the throughput rate.
Submission time. Data loads performed during peak hours when other users are also using the server’s resources are slower than loads submitted during off-peak hours.
Associations. When a record is associated with other records, additional processing and validation might be required.
Background processes. Many background processes are not visible and might affect the throughput rate. These background processes include: log file generation, email notification, and other processes.
Optimizing Load Size
When performing a large load, keep in mind that the entire environment might need to be sized appropriately for the load that you plan to perform. There are many factors to take into consideration to ensure optimal results.
To ensure optimal load size
Check the client configuration to ensure data is sent at an optimal speed.
Inspect the data to avoid errors.
You might want to load your data in a particular sequence because one record might depend on another record's existence. For example, if you want to import your users, and your data has a Reports To field, you might want to import users in hierarchical order so the executives are already in the database before their subordinates. These records cannot be imported randomly, but require sequential processing.
Check for other data quality issues, such as the required fields and pick list values are valid.
Make sure that the log level is tailored to the specific load.
There is no need for unnecessary logging.
Some recurring data loads might need to be suspended by Oracle CRM On Demand Hosting Operations personnel, and they might need to restrict the use of the environment.
Perform test runs and collect metrics for each test run. Scale or reconfigure the environment iteratively to achieve the required throughput rate.
Optimizing typically requires a few weeks because it is an iterative process of plan, test, analyze, adjust, and repeat.
Factors That Affect Performance When Performing a Large Load
The following information relates to performance and sizing concerns:
Throughput characteristics between different record types and scenarios vary widely, therefore it is not possible to predict the throughput rate for a given load.
Optimization is an iterative process which builds from a single session test to large scale concurrency testing.
It is your responsibility to act on your data. Oracle does not update, delete, or otherwise alter customer data.
Staging and production environments are not identical, therefore throughput can differ between these two environments.
Oracle Data Loader On Demand uses nonsequential processing. This provides for maximum throughput by using all available resources to import your data. For example, if your server has four tasks available and you submitted a 4000-record import, and if no one else was importing data, then Oracle Data Loader On Demand would use all four tasks to import the 4000 records (each task importing 1000 records). If this job was processed using a sequential process, then only one task would be used and the other three would be idle.
For better performance when importing, consider inactivating any workflows that can be postponed.