Set Up Data Sources

This topic describes how to set up data sources for Publisher.

About Private Data Source Connections

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

When you create a private data source connection, the private data source connection is available only to you in the data model editor data source menus. For example, if you create a private data source connection called "my datasource." and when you create a data set, the Data Source selection menu is as shown in the figure below.

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 Administration page.

Private data source connections are distinguished by an Allowed User value on the Data Source Administration page as shown in the figure below. 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 Grant Access to Data Sources Using the Security Region.

Grant 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.

About Proxy Authentication

Publisher supports proxy authentication for connections to various data sources

Supported data sources include:

  • Oracle 10g database

  • Oracle 11g database

  • Oracle BI Server

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

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

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.

Choose 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.

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.

About Pre Process Functions and Post Process Functions

You can define PL/SQL functions for 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 the table below.

Set Up a JDBC Connection to the Data Source

You can set 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.
  2. Click Add Data Source.
  3. Enter a display name for the data source in the Data Source Name field. This name is displayed in the Data Source selection list in the Data Model Editor.
  4. Select the driver type.
  5. You can update the Database Driver Class  field if required.
  6. Enter the database connection string.

    Example connection strings:

    • Oracle database

      To connect to an Oracle database (non-RAC), use the following format for the connect string:

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

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

    • Oracle RAC database

      To connect to an Oracle RAC database, use the following format for the connect string:

      jdbc:oracle:thin:@//<host>[:<port>]/<service_name>

      For example: jdbc:oracle:thin:@//myhost.example.com:1521/my_service

    • Microsoft SQL Server

      To connect to a Microsoft SQL Server, use the following format for the connect string:

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

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

  7. Enter the user name and password required to access the data source.
  8. (Optional) Enter a PL/SQL function to execute when a connection is created (Pre Process) or closed (Post Process).
  9. (Optional) Select a client certificate for secured connection.
    The client certificates uploaded in Upload Center are listed for selection.
  10. To enable Proxy Authentication, select Use Proxy Authentication.
  11. Click Test Connection.
  12. (Optional) Enable a backup database for this connection:
    1. Select Use Backup Data Source.
    2. Enter the connection string for the backup database.
    3. Enter the user name and password for this database.
    4. Click Test Connection.
  13. Define security for this data source connection. Move the required roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.

    If you have defined a backup data source, the security settings are passed to the backup data source.

Create a Secure JDBC Connection to Oracle Autonomous Data Warehouse

You can upload a JDBC client certificate and set up an SSL based JDBC connection to a database on the cloud.

To create a secure JDBC connection to Oracle Autonomous Data Warehouse:
  1. Upload the JDBC client certificate.
    1. From the Administration page, click Upload Center.
    2. Browse and select the Oracle wallet file, cWallet.sso.
    3. Select JDBC Client Certificate from the File Type list.
    4. Click Upload.
  2. From the Administration page, click JDBC Connection.
  3. Click Add Data Source.
  4. Specify the following details for the connection:
    • Data Source Name: DBaaSConnection
    • Driver Type: Oracle 12c
    • Database Driver Class: oracle.jdbc.OracleDriver
  5. Enter the JDBC connection string.
    Use TCPS strings. For example, jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=server_name)(PORT=port))(CONNECT_DATA=(SERVICE_NAME=serviceName)))
  6. From the Client Certificate list, select the wallet file, cwallet.sso uploaded earlier.
  7. Click Test Connection.
  8. Click Apply.

Set Up a Database Connection Using a JNDI Connection Pool

You can connect to the JDBC data source using 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).

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

  1. From the Administration page, click JNDI Connection.
  2. Click Add Data Source.
  3. Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.
  4. Enter the JNDI name for the pool. For example, jdbc/BIPSource.
  5. Enter the following fields for the new connection:
  6. Select Use Proxy Authentication to enable Proxy Authentication.
  7. Click Test Connection. You see a confirmation message if the connection is established.
  8. Define security for this data source connection. Move the required roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.

Set Up a Connection to an OLAP Data Source

You can set up connections to several types of OLAP databases.

To set up a connection to an OLAP data source:
  1. From the Administration page, click OLAP Connection.
  2. Click Add Data Source.
  3. Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.
  4. Select the OLAP type.
  5. Enter the connection string for the OLAP database.
  6. Enter the user name and password for the OLAP database.
  7. Click Test Connection.
  8. Define security for this data source connection. Move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.

Set Up a Connection to a Web Service

You can use a web service as a data source.

If you want to use an SSL certificate for the connection, upload the SSL certificate in Upload Center before you define the connection to the data source.
To add a web service as a data source:
  1. From the Administration page, click Web Service Connection.
  2. Click Add Data Source.
  3. Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.
  4. Select the server protocol.
  5. Enter the server name and the server port.
  6. Enter the URL for the web service connection.
  7. (Optional) Enter the session timeout in minutes.
  8. Select the security header from WS-Security.
    • 2002 — Enables the "WS-Security" Username Token with the 2002 namespace: http://docs.oasis-open.org/wss/2002/01/oasis-200201-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
  9. (Optional) Enter the user name and password for the web service data source.
  10. (Optional) From the SSL Certificate list, select the SSL certificate you want to use for the connection.
  11. If you're using a proxy-enabled server, select Use System Proxy.
  12. Click Test Connection.
  13. Define security for this data source connection. Move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.
  14. Click Apply.

Set Up a Connection to an HTTP Data Source

HTTP data sources enable your data model designers to build data models from XML, JSON, and CSV data over the web by retrieving data through the HTTP GET method.

If you want to use an SSL certificate for the connection, upload the SSL certificate in Upload Center before you define the connection to the data source.
To add an HTTP data source:
  1. From the Administration page, click HTTP Connection.
  2. Click Add Data Source.
  3. Enter a display name for the data source. This name is displayed in the Data Source selection list in the Data Model Editor.
  4. Select the server protocol.
  5. Enter the server name and the server port.
  6. Enter the URL context for the HTTP data source connection in the Realm field.
    For example, xmlpserver/services/rest/v1/reports
  7. Enter the user name and password required to access the data source on the database.
  8. (Optional) From the SSL Certificate list, select the SSL certificate you want to use for the data source.
  9. If you're using a proxy-enabled server, select Use System Proxy.
  10. Define security for this data source connection. Move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.

Set Up a Connection to a Content Server

Content server data source enables you to retrieve a text attachment content stored in Oracle WebCenter Content (earlier known as UCM) server and display it in the report of the corresponding document.

To set up a connection to a content server data source:
  1. From the Administration page, select the Content Server link.
  2. Click Add Data Source.
  3. Enter the name in the Data Source Name field.
  4. Enter the URL in the URI field.
  5. Enter the user name and password in the Username and Password fields, respectively.
  6. Click Test Connection.
  7. Define security for this data source connection. Move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.
  8. Click Apply.

View or Update a Data Source

You can view or update a data source from the Administration page.

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 editable. 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.