Loading Data with Oracle BI Cloud Service Data Sync

Loading Data with Oracle BI Cloud Service Data Sync :

  1. Login to the Data Sync.

Data sync password

  1. Create a new project or select an existing one.

create a new project or select an existing one

  1. Launching Data Sync and Setting up connections :

Click on Connections, Initially two connections are set up,

TARGET – The connection to the Oracle BI Cloud Service instance, which is used for       data population.

File Source – The connection to your locally stored data source CSV files.

Launging datasync and click connections

  1. On the toolbar, click new, then a new row is displayed under the Source/Targets tab.
  2. Provide connection details for Oracle relational data source on the Edit tab:

Name – Logical name for data source.

Connection Type – The database type.

Table Owner – Schema owner name.

User – Database User.

Password – Password for the database user.

Service Name – Service name of the Oracle Listener to connect to the database.

Host – Machine name or IP address of the machine where the database resides.

Port – Port number of the database listener.

est. connection

  1. Click Save, then Test Connection.

connection successful

  1. Now click OK.

 

Loading Data from a CSV File :

Registering the Data Source File

 

  1. Click Project to open the project view.

click project to open the project view

  1. Select the File Data tab.

select file data tab

  1. In the toolbar, click New. The Select File tab of a dialog box is displayed.

select file

  1. In the Select a File text field, click the Open File icon , navigate to the location where you saved the CSV file you want to upload, and select the file.

Open file

  1. Click Open. The file name and the default logical name is displayed in the Select File tab.

logical name

  1. Click Next. The Import Options tab is displayed.

Import options

7.   Optional :

  • Number of lines to skip Use this option if your file has lines with additional information, and the actual data starts from a specific line.
  • Select a delimiter – Select a delimiter for the file from the drop-down list. Options include Comma, Pipe, Semi-colon, Space, Tab, Tilde, or Custom.
  • Select timestamp format(java style)  Select a timestamp format from the available options in the drop-down menu.
  • Number of lines to be sampled By default, up to 10,000 rows are taken as a sample to identify the data types. You can specify that a higher or lower number of rows be sampled, or choose -1 to read the whole file.
  • First line contains headers Select this option if the file contains column names in a header. When the data file does not contain headers, then column names appear as COLUMN_1, COLUMN_2, and so on. equal to the number of attributes in a row.
  1. Accept the defaults and click Next. The Select Target Table tab is displayed.

select target table

  1. In the Create a table text box, type NEW_EXP_SAMPLE and click Next.

The New Source File: Map columns dialog box is displayed listing file columns and suggested table columns.

MAP COLUMNS

  1. Verify the column mapping and make necessary changes if needed. Click OK.

The Import File dialog box displays the Success message.

success

  1. Click OK.
  1. The New_Exp.csv file is registered as a data source file.

edit new

  1. Click the File Attributes tab to review the source file columns.

file attributes new

  1. Click the File Targets tab to review the target tables. The NEW_EXP_SAMPLE table is listed as a target.

File targets

 

Adding Targets to the Source File

  1. On the File Targets tab, click New.

on the file targets tab click new

  1. Click Next.

cant select anything on next

17.   Click Next.

again select target table

  1. Select the Create a table option and typeNEW_GEOGRAPHYin the text box. Select the Remove duplicates option.

new geo

  1. Click Next.

The New Source File: Map columns dialog box is displayed listing file columns and suggested table columns.

map columns review

  1. Click Select None.

Select Plant Location Country Name in the File Columns section and also select the Update Rows on Match option for the same.

update rows on match1

  1. Click OK. A dialog box with a message prompting you to create a unique index is displayed.

unique index

  1. Click OK. You are returned to the File Targets tab.

return to the file targets tab

  1. Click Refresh. The NEW_GEOGRAPHY target table is listed in the tab.

new geography listed

 

  1. Repeat steps 15 through 23 and add the ORDERS_NEW target table with the following columns :
  • # Of Orders
  • Ordered Amount

 

  1. The ORDERS_NEW table is listed as the target.

Orders new target table

 

Setting Load Strategies

  • Replace data in table – Delete any existing data and reload data always. Also applies to loads where a unique key is not available.
  • Append data to table – New data is added to the table without checking for any prior existence of data.
  • Update table (Add new records) – During incremental runs, the table is not truncated. Data is applied incrementally, and any missing indexes are created.
  • Update table (Update existing records) – During initial run, the table is truncated before the first load. Indexes are dropped prior to data load, and recreated after load.

 

  1. In the Project view, select the File Data tab, select the NEW_GEOGRAPHY target table, click the Load Strategy field, and click the Select button .

 load strategy new

  1. The Load Strategy dialog box is displayed.

strategy list

  1. Select the Update table option, the Add new records checkbox, the Update existing records checkbox, and click OK.

update table

  1. The Incremental Settings dialog box is displayed.

incr settings

  1. Click OK.
  1. The load strategy changed for the NEW_GEOGRAPHY target table.

updated strategy

  1. Repeat steps 26 through 30 for the ORDERS_NEW table.

The load strategy changed for the ORDERS_NEW target table.

ordersnew upd

 

Running Jobs

 33. Click the Jobs button to open the Job view for the project.

jobs

  1. Click the Run Job Button.

The Run Job confirmation dialog box is displayed.

run job dbox

  1. Click OK.

The Request successfully submitted message is displayed.

req succ sub

  1. Click OK to close the Run Job dialog box. You are returned to the Jobs view. Select the Current Jobs tab.

Notice that the job run status is Running that is also indicated by the process icon turned green green.

job run status

  1. You can click Refresh to monitor the Job Run. After the job is finished, select the History tab. The job run is listed with the Run Status Completed.
  2. Select the Tasks tab. The list of Complete tasks is displayed.

list of completed tasks

  1. Select the NEW_EXP_SAMPLE TASK and click Details.

another details tab

  1. Close the Task Details window, select another task ORDERS_NEW, and click Details.

details tab

  1. Here we could see the number of Successful Rows for all the tasks. Close the Details window.

 

Loading Data from a Database Table :

Registering the Database Table Source

 

  1. On the toolbar click project, and select the Relational Data tab.

click project then relational data

  1. Click Data From Table.

The Import Tables into New_OBICS dialog box is displayed. The previously configured Datatab_Demo connection is selected by default in the Data Source drop-down list.

import table

  1. Click Search Tables.

The Searching Tables dialog box displays a message that the search was successful.

searching tables

  1. Click OK.

The Import Tables into New_OBICS dialog box displays the list of available tables.

table list

  1. For the W_AP_AGING_SUPPLIER_A table select the Import option and click Import Tables.

import w_AP_AGING_Supplier_A

  1. The Importing Tables dialog box displays a message that the import was successful.

import table successful

  1. Click OK.

The W_AP_AGING_SUPPLIER_A table is listed as the source and target table.

table listed below

 

Running a Job

  1. On the toolbar, click the Run Job button runjob button. The Run Job confirmation dialog box is displayed.

runjob confirmation dialog box

2.   Click OK. The Request successfully submitted message is displayed.

req succf submitted

  1. In the toolbar, click Jobs and select the Current Jobs tab to view the job progress.

job running

  1. After the job is finished, select the History tab and then select the Tasks tab to review the job tasks. The completed tasks of the project have been listed.

completed tasks

Leave A Reply

Your email address will not be published. Required fields are marked *