BI Publisher 11g -Report - Part1 - Creating Data Source

Introduction: 
Oracle BI Publisher is a strategic enterprise reporting product from Oracle that provides the ability to create and manage highly formatted reports from a wide range of data sources. Oracle BI Publisher is available as part of the Oracle BI Enterprise Edition Suite and also as a stand alone reporting tool. Oracle BI Publisher report formats can be designed using Microsoft Word or Adobe Acrobat, the tools that are easy to use, and most users are already familiar with. Oracle BI Publisher also allows you to create reports from different types of data sources such as Oracle Database, Files, BI EE, Web services, and Discoverer.


In OBIEE 11g of Oracle BI Publisher has enhanced, easy- to-use user interface (UI), many enhanced features, and newly introduced salient features such as - Data Model Editor - a graphical user interface for building data models within the BI Publisher interface; and Layout Editor - a design tool that enables you to create report layouts within the BI Publisher interface.


Prerequisites:
Before starting to work with BI Publisher 11g, we should have,
1. Have executed the Repository Creation Utility ( RCU) scripts in Oracle Database 11g. This should be done before installing Oracle BI EE. RCU creates the BI Publisher scheduler repository.
2. Have access to Oracle BI Publisher 11g or Have installed oracle BI Publisher as a part of OBIEE 11g
3. Have access to an Oracle Database11g or 10g with the sample schema OE (It will come by default with the installation of Oracle) installed and unlocked.
 To unlock the accounts in oracle first login with sys or sysdba account  and follow the below commands:


SQL> alter user OE account unlock;
SQL> alter user OE identified by OE;
SQL> conn OE/OE@orcl

Note: BI Enterprise Edition (BI EE) installer comes with these out of the box RCU scripts, that create the necessary schemas and the repositories in the Database for various components of BI EE including BI Publisher. You need an Oracle 11g Database to run these RCU scripts. 

Creating DSN in BI Publisher 11g:
After the successful completion of installation of OBIEE 11g We will start to learn how to work with Publisher 11g.
1. Go to All Programs -> Oracle Business Intelligence -> Start BI Services
    (Leave this step if you have started and OBIEE 11g is running)


2. Open any latest browser like firefox or chrome and type the below link on address bar
      http://machinename:9704/xmlpserver
     e.g: http://bose-obi:9704/analytics
We will get the following login page:

3. Login to Publisher with Administration privilages
                 Username: weblogic
                 Password: Admin123
Once we entered the credentials in login page and clicked 'Sign In' the Home page will open.
We can see the default objects and other options comes with OBI Publisher 11g.

4. To create a datasource connection with BI Publisher click 'Administration' which is available near to 'Help' link in right upper corner.
We will get the following Administration screen:

5. Click 'JDBC Connection' from 'Data Sources' in the Administration Windows ( from above screen)

5. Click 'Add Data Source' with the above screen, it open 'Add Data Source' Window. Give the credentials of OE schema and click 'Test Connection'  

Data Source Name - OE

Driver Type - Select a driver type to suit your Database (for example, you can select Oracle 10g or Oracle 11g to suit your Database)

Database Driver Class - oracle.jdbc.driver.OracleDriver (Define a driver class to suit your Database)

Connection String - Provide the database connection details. For example hostname:port:sid.

User name - OE (Database user name)

Password - OE (Database user password)

It will give confirmation with the message 'Connection Established Successfully'. After this click 'Apply'

6. Once we clicked 'Apply' with the above screen We can verify the 'OE' schema is added in the DSN in JDBC Data Sources.

So with the above Step We successfully added a Oracle database connection which we are going to use to create the reports in BI Publisher 11g.

In the next post we will know how to create a Dataset using BI Publisher 11g with this OE Data Source.