4 min read

(For more resources on Oracle 11g, see here.)

The Data Model Editor’s interface deals with all the components and functionalities needed for the data model to achieve the structure you need. However, the main component is the Data Set. In order to create a data model structure in BIP, you can choose from a variety of data set types, such as:

  • SQL Query
  • MDX Query
  • Oracle BI Analysis
  • View Object
  • Web Service
  • LDAP Query
  • XML file
  • Microsoft Excel file
  • Oracle BI Discoverer
  • HTTP

Taking advantage of this variety requires multiple Data Sources of different types to be defined in the BIP. In this article, we will see:

  • How data sources are configured
  • How the data is retrieved from different data sets
  • How data set type characteristics and the links between elements influence the data model structure

Administration

Let’s first see, how you can verify or configure your data sources. You must choose the Administration link found in the upper-right corner of any of the BIP interface pages, as shown in the following screenshot:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

The connection to your database can be choosen from the following connection types:

  • Java Database Connectivity (JDBC)
  • Java Naming and Directory Interface (JNDI)
  • Lightweight Directory Access Protocol (LDAP)
  • Online Analytical Processing (OLAP)

Available Data Sources

To get to your data source, BIP offers two possibilities:

  1. YOu can use a connection. In order to use a connection, these are the available connection types:
    • JDBC
    • JNDI
    • LDAP
    • OLAP
  2. You can also use a file.

In the following sections, the Data Source types&mdashJDBC, JNDI, OLAP Connections, and File&mdashwill be explained in detail.

JDBC Connection

Let’s take the first example. To configure a Data Source to use JDBC, from the Administration page, choose JDBC Connection from the Data Sources types list, as shown in the following screenshot:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

You can see the requested parameters for configuring a JDBC connection in the following screenshot:

  • Data Source Name: Enter a name of your choice.
  • Driver Type: Choose a type from the list. The relating parameters are:
    • Database Driver Class: A driver, matching your database type.
    • Connection String: Information containing the computer name on which your database server is running, for example, port, database name, and so on.
  • Username: Enter a database username.
  • Password: Provide the database user’s password.

The Use System User option allows you to use the operating system’s credentials as your credentials. For example, in this case, your MS SQL Database Server uses Windows authentication as the only authentication method.

When you have a system administrator in-charge of these configurations, all you have to do is to find which are the available Data Sources and eventually you can check if the connection works. Click on the Test Connection button at the bottom of the page to test the connection:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

JNDI Connection

JNDI Connection pool is in fact another way to access your JDBC Data Sources. Using a connection pool increases efficiency by maintaining a cache of physical connections that can be reused, allowing multiple clients to share a small number of physical connections.

In order to configure a Data Source to use JNDI, from the Administration page, choose JNDI Connection from the Data Sources types list. The following screen will appear:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

As you can see in the preceding screenshot, on the Add Data Source page you must enter the following parameters:

  • Data Source Name: Enter a name of your choice
  • JNDI Name: This is the JNDI location for the pool set up in your application server, for example, jdbc/BIP10gSource

The users having roles included in Allowed Roles list only will be able to create reports using this Data Source.

OLAP Connection

Use the OLAP Connection to connect to OLAP databases. BI Publisher supports the following OLAP types:

  • Oracle Hyperion Essbase
  • Microsoft SQL Server 2000 Analysis Services
  • Microsoft SQL Server 2005 Analysis Services
  • SAP BW

In order to configure a connection to an OLAP database, from the Administration page, choose OLAP Connection from the Data Sources types list. The following screen will appear:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

On the Add Data Source page, the following parameters must be entered:

  • Data Source Name: Enter a name of your choice
  • OLAP Type: Choose a type from the list
  • Connection String: Depending on the supported OLAP databases, the connection string format is as follows:
    • Oracle Hyperion Essbase

      Format: [server name]

    • Microsoft SQL Server 2000 Analysis Services

      Format: Data Source=[server];Provider=msolap;Initial Catalog=[catalog]

    • Microsoft SQL Server 2005 Analysis Services

      Format: Data Source=[server];Provider=msolap.3;Initial Catalog=[catalog]

    • SAP BW

      Format: ASHOST=[server] SYSNR=[system number] CLIENT=[client] LANG=[language]

  • Username and Password: Used for OLAP database authentication

File

Another example of a data source type is File. In order to gain access to XML or Excel files, you need a File Data Source. In order to set up this kind of Data Source, only one step is required&mdashenter the path to the Directory in which your files reside. You can see in the following screenshot that demo files Data Source points to the default BIP files directory. The file needs to be accessible from the BI Server (not on your local machine):

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 


Subscribe to the weekly Packt Hub newsletter

* indicates required

LEAVE A REPLY

Please enter your comment!
Please enter your name here