Skip Headers
Oracle® Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher
11g Release 1 (11.1.1)

Part Number E22255-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

9 Setting Up Data Sources

This chapter describes how to set up data sources for BI Publisher including JDBC and JNDI connections, LDAP server connections, OLAP data sources, and file data sources.

It covers the following topics:

9.1 Overview of Setting Up Data Sources

BI Publisher supports a variety of data sources. The data can come from a database, a HTTP XML feed, a Web Service, an Oracle BI Analysis, an OLAP cube, a LDAP server, or a previously generated XML file or Microsoft Excel file.

This section describes how to set up connections to the data sources that are described in the following sections:

9.1.1 About Private Data Source Connections

Private connections for OLAP, JDBC, Web Service, and HTTP data sources are supported in BI Publisher and can be created by users with data model creation privileges.

When a user creates a private data source connection, it displays only for that user in the data model editor data source menus. For example, a user creates a private data source connection called "my datasource." When the user creates a data set, the private data source connection displays in the Data Source selection menu as shown in Figure 9-1.

Figure 9-1 Selecting a Private Data Source Connection

Description of Figure 9-1 follows
Description of "Figure 9-1 Selecting a Private Data Source Connection"

Administrators have access to the private data source connections created by users. All private data source connections are displayed to Administrators when they view the list of OLAP, JDBC, Web Service, and HTTP data sources from the BI Publisher Administration page.

Private data source connections are distinguished by an Allowed User value on the Data Source Administration page as shown in Figure 9-2. Administrators can extend access to other users to a private data source connection by assigning additional user roles to it.

For more information on assigning roles to data sources, see Section 9.1.2, "Granting Access to Data Sources Using the Security Region.".

Figure 9-2 Private Data Source Connection Allowed User

Allowed user for the private data source connection

For more information about creating private data source connections, see "Managing Private Data Sources" in the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

9.1.2 Granting Access to Data Sources Using the Security Region

When you set up data sources, you can also define security for the data source by selecting which user roles can access the data source.

You must grant access to users for the following:

  • A report consumer must have access to the data source to view reports that retrieve data from the data source

  • A report designer must have access to the data source to create or edit a data model against the data source

By default, a role with administrator privileges can access all data sources.

The configuration page for the data source includes a Security region that lists all the available roles. You can grant roles access from this page, or you can also assign the data sources to roles from the roles and permissions page.

See Section 3.8, "Configuring Users, Roles, and Data Access" for information.

If this data source must be used in guest reports, then you must also enable guest access here. For more information about guest access see Section 4.2, "Enabling a Guest User."

Figure 9-3 shows the Security region of the data source configuration page.

Figure 9-3 Security Region

Description of Figure 9-3 follows
Description of "Figure 9-3 Security Region"

9.1.3 About Proxy Authentication

BI Publisher supports proxy authentication for connections to the following data sources:

  • Oracle 10g database

  • Oracle 11g database

  • Oracle BI Server

For direct data source connections through JDBC and connections through a JNDI connection pool, BI Publisher enables you to select "Use Proxy Authentication". When you select Use Proxy Authentication, BI Publisher passes the user name of the individual user (as logged into BI Publisher) to the data source and thus preserves the client identity and privileges when the BI Publisher server connects to the data source.

Note:

Enabling this feature requires additional setup on the database. The database must have Virtual Private Database (VPD) enabled for row-level security.

For more information on Proxy Authentication in Oracle databases, see Oracle Database Security Guide 10g or Oracle Database Security Guide 11g.

For connections to the Oracle BI Server, Proxy Authentication is required. In this case, proxy authentication is handled by the Oracle BI Server, therefore the underlying database can be any database that is supported by the Oracle BI Server.

9.1.4 Choosing JDBC or JNDI Connection Type

In general, a JNDI connection pool is recommended because it provides the most efficient use of your resources. For example, if a report contains chained parameters, then each time the report is executed, the parameters initiate to open a database session every time.

9.1.5 About Backup Databases

When you configure a JDBC connection to a database, you can also configure a backup database. A backup database can be used in two ways:

  • As a true backup when the connection to the primary database is unavailable

  • As the reporting database for the primary. To improve performance you can configure your report data models to execute against the backup database only.

To use the backup database in either of these ways, you must also configure the report data model to use it.

See the section "Setting Data Model Properties" in the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher for information on configuring a report data model to use the backup data source.

9.1.6 About Pre Process Functions and Post Process Functions

You can define PL/SQL functions for BI Publisher to execute when a connection to a JDBC data source is created (preprocess function) or closed (postprocess function). The function must return a boolean value. This feature is supported for Oracle databases only.

These two fields enable the administrator to set a user's context attributes before a connection is made to a database and then to dismiss the attributes after the connection is broken by the extraction engine.

The system variable :xdo_user_name can be used as a bind variable to pass the login username to the PL/SQL function calls. Setting the login user context in this way enables you to secure data at the data source level (rather than at the SQL query level).

For example, assume you have defined the following sample function:

FUNCTION set_per_process_username (username_in IN VARCHAR2)
   RETURN BOOLEAN IS
     BEGIN
     SETUSERCONTEXT(username_in);
     return TRUE;
   END set_per_process_username

To call this function every time a connection is made to the database, enter the following in the Pre Process Function field: set_per_process_username(:xdo_user_name)

Another sample usage might be to insert a row to the LOGTAB table every time a user connects or disconnects:

CREATE OR REPLACE FUNCTION BIP_LOG (user_name_in IN VARCHAR2, smode IN VARCHAR2) 
RETURN BOOLEAN AS
   BEGIN
   INSERT INTO LOGTAB VALUES(user_name_in, sysdate,smode);
   RETURN true;
   END BIP_LOG;

In the Pre Process Function field enter: BIP_LOG(:xdo_user_name)

As a new connection is made to the database, it is logged in the LOGTAB table. The SMODE value specifies the activity as an entry or an exit. Calling this function as a Post Process Function as well returns results such as those shown in Figure 9-4.

9.2 Setting Up a JDBC Connection to the Data Source

The following list shows prerequisites for setting up a JDBC connection to a data source:

To set up a JDBC connection to a data source:

  1. From the Administration page click JDBC Connection to display the list of existing JDBC connections.

    Private JDBC connections are also supported. For more information, see "Managing Private Data Sources" in the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

  2. Click Add Data Source.

  3. Enter the following fields for the new connection:

    • Data Source Name — Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.

    • Driver Type — Select the database type from the list. When you select a driver type, BI Publisher automatically displays the appropriate Database Driver Class and provides the appropriate Connection String format for your selected database.

    • Database Driver Class — This is automatically entered based on your selection for Driver Type. You can update this field if desired.

      For example: oracle.jdbc.OracleDriver or

      hyperion.jdbc.sqlserver.SQLServerDriver

    • Connection String — Enter the database connection string.

      When you select the driver type, this field automatically displays the appropriate connection string format for your database type.

      For an Oracle database the connect string must have the following format:

      jdbc:oracle:thin:@[host]:[port]:[sid]

      For example: jdbc:oracle:thin:@myhost.us.example.com:1521:prod

      For a Microsoft SQL Server, the connect string must have the following format:

      jdbc:hyperion:sqlserver://[hostname]:[port];DatabaseName=[Databasename]

      For example:

      jdbc:hyperion:sqlserver://myhost.us.example.com:7777;DatabaseName=mydatabase

    • Use System User — This is reserved for connections to the Oracle BI Server.

      See Section B.4, "Setting Up a JDBC Connection to the Oracle BI Server."

    • User Name — Enter the user name required to access the data source on the database.

    • Password — Enter the password associated with the user name for access to the data source on the database.

    • Pre Process Function and Post Process Function — (Optional) Enter a PL/SQL function to execute when a connection is created (Pre Process) or closed (Post Process). For more information see Section 9.1.6, "About Pre Process Functions and Post Process Functions."

    • Use Proxy Authentication — Select this box to enable Proxy Authentication. See Section 9.1.3, "About Proxy Authentication" for more information.

  4. Click Test Connection. A confirmation is displayed.

    Figure 9-5 shows the general settings of the JDBC connection page.

    Figure 9-5 JDBC Connection Page

    Description of Figure 9-5 follows
    Description of "Figure 9-5 JDBC Connection Page"

  5. (Optional) Enable a backup database for this connection by entering the following:

    • Use Backup Data Source — Select this box.

    • Connection String — Enter the connection string for the backup database.

    • Username / Password — Enter the username and password for this database.

    • Click Test Connection. A confirmation is displayed.

      Figure 9-6 shows the Backup Data Source region of the page.

      Figure 9-6 Backup Data Source Region

      Description of Figure 9-6 follows
      Description of "Figure 9-6 Backup Data Source Region"

  6. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list can create or view reports from this data source.

    The settings defined here are passed down to the backup data source, if one is defined.

9.3 Setting Up a Database Connection Using a JNDI Connection Pool

BI Publisher supports connecting to the JDBC data source through a connection pool. Using a connection pool increases efficiency by maintaining a cache of physical connections that can be reused. When a client closes a connection, the connection gets placed back into the pool so that another client can use it. A connection pool improves performance and scalability by allowing multiple clients to share a small number of physical connections. You set up the connection pool in your application server and access it through Java Naming and Directory Interface (JNDI).

After you set up the connection pool in your application server, enter the required fields in this page so that BI Publisher can use the pool to establish connections. For information on setting up a connection pool in WebLogic Server, see the chapter "Configuring JDBC Data Sources" in Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.

To set up a database connection using a JNDI connection pool:

  1. From the Administration page click JNDI Connection to display the list of existing JNDI connections.

  2. Click Add Data Source.

  3. Enter the following fields for the new connection:

    • Data Source Name — Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.

    • JNDI Name — Enter the JNDI location for the pool. For example, jdbc/BIP11gSource.

    • Use Proxy Authentication — Select this box to enable Proxy Authentication. See Section 9.1.3, "About Proxy Authentication" for more information.

  4. Click Test Connection. A confirmation message is displayed.

  5. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list can create or view reports from this the data source.

9.4 Setting Up a Connection to an LDAP Server Data Source

To set up a connection to an LDAP data source:

  1. From the Administration page select LDAP Connection to display the list of existing LDAP connections.

  2. Click Add Data Source.

  3. Enter the following fields for the new connection:

    • Enter the Data Source Name — This is the display name that is displayed in the Data Source selection list in the Data Model Editor.

    • Enter the LDAP Connection URL for the LDAP server in the format: ldap://hostname:port.

    • Enter the Username (for example: cn=admin,cn=users,dc=us,dc=company,dc=com).

    • Password — Enter the password if required.

    • Enter the JNDI Context Factor Class (for example: com.sun.jndi.ldap.LdapCtxFactory).

  4. Click Test Connection.

  5. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list can create data models from this the data source or view reports that run against this data source.

9.5 Setting Up a Connection to an OLAP Data Source

BI Publisher supports connecting to several types of OLAP databases. Note that to connect to Microsoft SQL Server Analysis Services, BI Publisher must be installed on a supported Windows operating system. For the complete list of supported OLAP databases, see "System Requirements and Certification."

To set up a connection to an OLAP data source:

  1. From the Administration page click OLAP Connection to display the list of existing OLAP connections.

    Private OLAP connections are also supported. For more information, see "Managing Private Data Sources" in the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

  2. Click Add Data Source.

  3. Enter the following fields for the new connection:

    • Data Source Name — Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.

      OLAP Type — Select from the list of supported OLAP databases. When you select the type, the OLAP Connection String field is updated with the appropriate connection string format for your selection.

    • OLAP Connection String — Enter the connection string for the OLAP database. Following are examples for each of the supported OLAP types:

      • Oracle's Hyperion Essbase

        Format: [server]

        Example: myServer.us.example.com

      • Microsoft SQL Server 2000 Analysis Services

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

        Example: Data Source=myServer;Provider=msolap;Initial Catalog=VideoStore

      • Microsoft SQL Server 2005 Analysis Services

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

        Example: Data Source=myServer;Provider=msolap.3;Initial Catalog=VideoStore

      • SAP BW

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

        Example: ASHOST=172.16.57.44 SYSNR=01 CLIENT=800 LANG=EN

    • Username and Password for the OLAP database.

  4. Click Test Connection. A confirmation message is displayed.

  5. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list can create or view reports from this the data source.

9.6 Setting Up a Connection to a File Data Source

BI Publisher enables you to use existing XML or Microsoft Excel files created from other sources as input to your BI Publisher reports. To use a file as a data source, it must reside in a directory that BI Publisher can connect to. Set up the connection details to the file data source directory using this page.

To set up a connection to a file data source:

  1. From the Administration page click File to display the list of existing file sources.

  2. Click Add Data Source.

  3. Enter the following fields for the new data source:

    • Data Source Name — Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.

    • Path — Enter the full path to the top-level directory on your server. Users can access files in this directory and any subdirectories.

  4. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list can create or view reports from this data source.

9.7 Setting Up a Connection to a Web Service

Note:

<Setting up a connection to a Web service through the Administration page applies to Oracle BI Publisher 11.1.1.7.10 and later versions, and might not be available in earlier versions. For more information about Oracle BI Publisher 11.1.1.7.10, see "New Features for 11.1.1.7.10."

BI Publisher supports Web service data sources that return valid XML data.

Important:

Additional configuration may be required to access external Web services depending on your system's security. If the WSDL URL is outside the company firewall, see "Configuring Proxy Settings" in Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher for more information.

BI Publisher supports Web services that return both simple and complex data types. You must make the distinction between simple and complex when you define the Web service connection. For more information about each Web service connection type, see Section 9.7.1, "Adding a Simple Web Service" and Section 9.7.2, "Adding a Complex Web Service."

If the Web service is protected by Secure Sockets Layer (SSL), see "Configuring BI Publisher for Secure Socket Layer (SSL) Communication" in Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher for more information.

Private Web Service connections are also supported. For more information, see "Managing Private Data Sources" in Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

Only Basic and Digest authentication is supported for Web service data sources.

Only document/literal Web services are supported.

9.7.1 Adding a Simple Web Service

To add a Web service as a data source:

  1. From the Administration page, click Web Service Connection to display the list of existing Web service connections.

  2. On the Web Services tab, click Add Data Source to display the Add Data Source page as shown in figure Figure 9-7.

    Figure 9-7 Creating a Simple Web Service Data Source

    Description of Figure 9-7 follows
    Description of "Figure 9-7 Creating a Simple Web Service Data Source"

  3. Enter the following fields for the new connection:

    • Data Source Name — Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.

    • Server Protocol — Select the server protocol.

    • Server — Enter the server name.

    • Port — Enter the server port.

    • URL Suffix — Enter the URL suffix for the web service connection.

      For example, stockquote.asmx?WSDL

    • (Optional) Session Timeout (Minutes) — Enter the timeout in minutes. If the BI Publisher server cannot establish a connection to the Web service, the connection attempt will time out after the specified time out period has elapsed.

    • Complex Type — Deselect the check box to designate the connection as a simple Web service.

  4. Define security for this data source by using the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list can create or view reports from this data source.

  5. Click Apply to save the data source connection.

9.7.2 Adding a Complex Web Service

To add a complex Web service as a data source:

  1. From the Administration page, click Web Service Connection to display the list of existing Web service connections.

  2. Click Add Data Source to display the Add Data Source page as shown in figure Figure 9-8.

    Figure 9-8 Creating a Complex Web Service Data Source

    Description of Figure 9-8 follows
    Description of "Figure 9-8 Creating a Complex Web Service Data Source"

  3. Enter the following fields for the new connection:

    • Data Source Name — Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.

    • Server Protocol — Select the server protocol.

    • Server — Enter the server name.

    • Port — Enter the server port.

    • URL Suffix — Enter the URL for the Web service connection.

    • (Optional) Session Timeout (Minutes) — Enter the timeout in minutes. If the BI Publisher server cannot establish a connection to the web service, the connection attempt times out after the specified time out period has elapsed.

    • Complex Type — Select the check box to designate the connection as a complex Web service.

    • WS-Security — Select the security header.

      • 2002 — Enables the "WS-Security" Username Token with the 2002 namespace:

        http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd

      • 2004 — Enables the "WS-Security" Username Token with the 2004 namespace:

        http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText

    • Authentication Type — BI Publisher supports HTTP and SOAP authentication types. SOAP is the default. When HTTP is selected, the user name and password information are passed through HTTP headers. When soap is selected, the user name and password information are passed through XML SOAP envelope headers.

    • Username — Enter the user name for the web service, if required.

    • Password — Enter the password for the web service, if required.

    • WSDL protected by HTTP basic auth — select if access to the WSDL is protected. When the WSDL is protected by user name and password, BI Publisher executes an HTTP call with the username and password to access the WSDL URL. The WSDL can then be downloaded and parsed by BI Publisher.

  4. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list can create or view reports from this data source.

    The settings defined here are passed down to the backup data source, if one is defined.

  5. Click Apply.

9.8 Setting Up a Connection to a HTTP XML Feed

Note:

<Setting up a connection to a HTTP XML feed through the Administration page applies to Oracle BI Publisher 11.1.1.7.10 and later versions, and might not be available in earlier versions. For more information about Oracle BI Publisher 11.1.1.7.10, see "New Features for 11.1.1.7.10."

HTTP (XML Feed) data sources enable your data model designers to build data models from RSS and XML feeds over the Web by retrieving data through the HTTP GET method.

Private HTTP XML connections are also supported. For more information, see "Managing Private Data Sources" in Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

To add a HTTP XML as a data source:

  1. From the Administration page, click HTTP Connection to display the list of existing HTTP connections.

  2. Click Add Data Source to display the Add Data Source page as shown in Figure 9-9.

    Figure 9-9 HTTP Connection Page

    Description of Figure 9-9 follows
    Description of "Figure 9-9 HTTP Connection Page"

  3. Enter the following fields for the new connection:

    • Data Source Name — Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.

    • Server Protocol — Select the server protocol.

    • Server — Enter the server name.

    • Port — Enter the server port.

    • Realm — Enter the URL for the web service connection.

      For example:

      xmlpserver/services/v2/SecurityService?wsdl

    • Username — Enter the user name required to access the data source on the database.

    • Password — Enter the password associated with the user name for access to the data source on the database.

  4. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list can create or view reports from this data source.

    The settings defined here are passed down to the backup data source, if one is defined.

9.9 Viewing or Updating a Data Source

To view or update a data source:

  1. From the Administration page, select the Data Source type to update.

  2. Select the name of the connection to view or update. All fields are updatable. See the appropriate section for setting up the data source type for information on the required fields.

  3. Select Apply to apply any changes or Cancel to exit the update page.