✍ Kashif M ™
☎ +91 9994883085
✉ kashif7222@gmail.com


Saturday, 22 April 2017

Oracle BICS Architecture


Any users required guidance and discover the right questions by using the analytic and this type of analytic required reach and quality data and the very important is richness in the functionality to solve the hard questions.
Flexibility and functionality to view analytic is given by cloud Oracle BICS. Below is architecture of BICS.

Oracle BICS Architecture
Oracle BICS Architecture

The Business Intelligence Cloud Service and 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.


Requirements Before installing, you must have the Java 1.7 or later version of Java Developer Kit (JDK). On an on-going basis, any critical updates specific to this version of Java should always be applied.


Business Intelligence Cloud Service Data Sync supports the following databases:
Oracle (source and target)
Microsoft SQL Server (source and target)
DB2 (source and target)
Teradata (source and target)
MySQL (source only)
Oracle TimesTen (source and target)


To set up the software, copy the BICSDataSync.Zip file to an installation directory with no spaces in its name, and unzip the files.

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.

For example, on Windows:

Set JAVA_HOME=D: \Java

For example, on UNIX:


Data Load from Data Sync - BICS

Download mysql-connector-java-5.1.34.bin.jar and com.mysql.jdbc-5.1.5.jar

and place it data sync folder.

Source connection:

If connection fails, paste the data sync path in system properties

Go to view->system properties-> paste the path in Data file root directory,

And click save.

Click test connection

Here in MYSQL I have created a table named book

Then go to projects, in that click Relational Data and click Data from SQL

Give name for the target table and write a select query (e.g.: select * from book)

Then click ok

Go to target tables/Data sets, in that click table columns, you could see the target table columns.

Click run job

Go to jobs and click current jobs.

Click history in jobs

Job successfully completed

Go to object browser in BICS, you would see the target table book1 has been loaded successfully.

SQL Data Definition Language (DDL)

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables. 

The most important DDL statements in SQL are: 

CREATE TABLE - creates a new database table 

ALTER TABLE - alters (changes) a database table 

DROP TABLE - deletes a database table 

CREATE INDEX - creates an index (search key) 

DROP INDEX - deletes an index

SQL Data Manipulation Language (DML)

SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.

These query and update commands together form the Data Manipulation Language (DML) part of SQL:

 SELECT - extracts data from a database table 

 UPDATE - updates data in a database table

 DELETE - deletes data from a database table

 INSERT INTO - inserts new data into a database table

What is SQL?

SQL is a standard computer language for accessing and manipulating databases.
What is SQL?

• SQL stands for Structured Query Language 

• SQL allows you to access a database 

• SQL is an ANSI standard computer language 

• SQL can execute queries against a database 

• SQL can retrieve data from a database 

• SQL can insert new records in a database 

• SQL can delete records from a database 

• SQL can update records in a database 

• SQL is easy to learn

What is SQL?
What is SQL?

SQL is a Standard - BUT....

SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.
Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!


Run rcu to create repositories in the database to support the WLS OBI install
Source: /stage/oracle/OBI/OBI/RCU

You can either unzip rcu to be local or run it from the share. It is recommended to run local to ensure no issues.

The rcu will require the normal setup to allow an oracle installer session to run, ie, a working X server, the DISPLAY to be set and so on.
From the application server, e.g., d001m-uap04:
mkdir -p /u01/product/OBIDEV/rcu
cp /stage/oracle/BPM_OBI/OBI/RCU/V37394-01.zip /u01/product/OBIDEV/rcu
cd /u01/product/OBIDEV/rcu
unzip V37394-01.zip
rm V37394-01.zip
cd rcuHome/bin

Please Note: If the database you are installing into is not clean, you may need to drop the existing repository. If that is the case, just click on drop instead of create in the second screen, and plug in the same details and just follow the bouncing ball.

• When the GUI is presented Click Next

· Click Create

• Supply details for the repository database, production values below as an example.

o Host: p001m-udb02
o Port: 1666

Note – you will need to connect as sys / sysdba to create the repository.
• Click Next

· Click OK

· Create a new Prefix, OBIA

· Select Oracle Data Integrator and Oracle Business Intelligence

· Click Next

· Click OK

· Click “Use same passwords for all schemas”

· Provide Passw0rd

· Click Next

· Provide the following details,

Master Repository ID (001): 001

Supervisor Password: <password safe>

Confirm Supervisor Password: <password safe>

Work Repository Type: D

Work Repository ID(001): 001

Work Repository Password: <password safe>

Work Repository Password: <password safe>

Click ok

Click OK

Click create

Should start progressing

· Click Close