Oracle Business Intelligence Cloud
Service (BICS) with Data Sync
1. Business Intelligence
Cloud Service Data Sync Overview :
The Business Intelligence Cloud Service Data Sync supports the load of on-premises data residing in one or more relational or comma-separated value file sources into the schema provisioned on the Oracle Business Intelligence Cloud Service.
2. Installing the Business Intelligence Cloud Service Data Sync :
To install the Data Sync, you must meet the requirements and prerequisites, then unzip and run the application.
Download Data Sync from:
http://www.oracle.com/technetwork/middleware/bicloud/downloads/index.html
2.1 Prerequisites, Supported Databases, and JDBC Requirements :
Before installing, you must have the 64 bit
machine and Java 1.7 or later version of Java Developer Kit (JDK).
Install jdk to a directory with no spaces in its name.
Note: Java Developer Kit is required for Business Intelligence Cloud Service Data Sync.
The equivalent version of the Java Runtime Environment (JRE) is insufficient to support
the installation.
2.2 Setting Up the Software :
Install jdk to a directory with no spaces in its name.
Note: Java Developer Kit is required for Business Intelligence Cloud Service Data Sync.
The equivalent version of the Java Runtime Environment (JRE) is insufficient to support
the installation.
2.2 Setting Up the Software :
To set up the software, copy the BICSDataSync.Zip file to an installation directory with no spaces in its name, and unzip the files.
2.2.1 Setting the Java Home:
Depending on your operating system, edit either the config.bat or config.sh file, modifying the line that sets the JAVA_HOME. Replace the @JAVA_HOME with the directory where the JDK is installed.
2.2.2 Execute datasyncclient.bat or datasynccclient.sh :
Execute datasyncclient.bat or datasynccclient.sh depending on the operating system to launch the first time Data Sync Configuration Wizard and click next.
2.2.3 Configure the Repository and JAVA DB :
In Environment Configuration, select configure a new
environment and click next to configure the repository and Java DB for Data
Sync to store its metadata.
2.2.4 Specify Repository Name and click Next :
2.2.5 Choose Password for Datasync Login and click Next:
2.2.6 Configuration is complete, click finish to
close the configuration wizard :
2.2.7 BICS Datasync server gets started :
BICS
Datasync server gets started, To get started, please enter the password set for
the repository. Skip the create project option. Project can be created from
Datasync client.
Enter the same password given while
Installing Datasync in step 2.2.5
After
successful Login, following screen will appear.
3. Creating Project and
Connections :
3.1.1
Creating Projects :
a. To Create a new Project, select File
--> Project, In the project creation wizard, specify the New Project Name
and click OK.
Please
Note: A project is a list
of tables/files to upload in a single session. You may need multiple projects
to populate a BI Cloud database – either if loading data from multiple sources
or if you need to schedule different tables at different times
Give
the relevant Project Name.
3.2.2
Creating Connections :
a. Once Project is created, we need to setup
Source & Target connections, Source connections can be RDMBS or Files, BICS
Datasync supports connections to popular RDMBS like MYSQL, ORACLE, SQL SERVER,
DB2 etc.,
1. Click on Connections
2. Select the TARGET Source
3. Enter BICS Username
4. Enter BICS Password
5. URL should be same as the BICS URL
excluding /analytics tag at the end.
6. To Test the connection, click on Test
Connection button, a pop up window appears with the test result.
7. Click OK to Close the Test result window
8. Save the changes to the TARGET
connection, if the connection is successfully tested.
b. Following are the steps for setting up the
Source RDBMS connection (e.g. Oracle source).
1. In the Connection Tab, click on New to
create a new Source DB connection
2. Enter the Name of the connection,
Connection Type, Table owner, Username, Password, Service name, Host & Port
number of the Source.
3. Click on Test Connection to check the
status of the Test result
4. After reviewing the test results, Click
OK to close the test result window
5. Once the connection is tested
successfully, save the connection details.
3.2.3 Importing Metadata from Relational, File & Target
into Project.
a. Once Connections for source and target is succesfully
configured, then we can to create the tasks/ETL.
1. To setup a new
datasysnc task, Select the Project Tab
2. In the Relational Data
Tab, click on Data from Table to import the metadata of the source table(s)
3. In Import Table into
Datasync window, select the appropriate data sources.
4. Search/Select the list
of tables displayed and Import tables.
b. Review Target Metadata.
Note: By default BICS Datasync
adds additional 3 columns in the Target table namely:
1. DSYS_BATCH_ID: Tracks the batch that is trying
to upload the data. Each table load streams multiple batches (currently of
3,000 rows), with each batch assigned a unique number.
2. DSYS_INSTANCE_ID: Tracks the Data Sync
installation instance ID
|
|
3. DSYS_PROCESS_ID: Tracks the process ID
assigned to a certain run of the job.
4. Creating Jobs &
Loading On-Premise data to BICS Schema :
4.1.1 Creating
& Executing Jobs :
a. A job is an instance
of running a particular project and can be associated with a schedule to
regularly run it. Clicking on the Jobs button in the top menu bar opens the
Jobs window. To create a new executable job, click New & Enter a Job Name.
b. To Execute the Job
1. Click on Run Job button.
2. Go to the Current Jobs.
3. All Tasks would be queued.
c. You can
also view the Job History by selecting the History tab to review the status of
the load.
d. Finally we
can check the data that is been loaded in Oracle Cloud Schema by logging on to
BICS Myservices and browsing through the schema objects as shown below, notice
the extra columns created by BICS Datasync.
5. Conclusion
:
The Business Intelligence Cloud Service Data Sync supports
the load of on-premises data residing in one or more relational or
comma-separated value file sources into the schema provisioned on the Oracle
Business Intelligence Cloud Service.
Thanks for visiting..Keep Learning..:)
Thanks for visiting..Keep Learning..:)
