8 Integrating Other Clients with Oracle Business Intelligence

You can connect to the Oracle BI Server with a wide variety of ODBC-compliant query and reporting tools, as well as other clients such as remote Administration Tool clients. To connect with a remote client, you configure an ODBC DSN for the Oracle BI Server on the remote client computer, and then use that DSN to connect to a repository from the query tool.

Oracle BI Presentation Services clients also connect to the Oracle BI Server using an ODBC DSN. A default ODBC DSN for Presentation Services is created and configured for you during Oracle Business Intelligence installation.

Administration Tool clients on the same host as a Presentation Services instance can also use the default DSN to connect to the Oracle BI Server. Or, you can create a separate DSN for the Administration Tool to use.

The connection parameters for the Cluster Controller in the default DSN are centrally managed by Fusion Middleware Control. Do not update these parameters. If you attempt to manually update the centrally managed parameters, the values will be overwritten the next time the system is started.

In addition, do not change the name of the centrally managed default DSN. The default DSN has a name similar to "coreapplication_OHid_number," where id_number is a number specific to an installed Oracle home.

This chapter explains how you can integrate with the Oracle BI Server as a data source using ODBC on both Windows and UNIX platforms.

This chapter includes the following topics:

• "About Integrating with the Oracle BI Server as a Data Source"

• "ODBC Conformance Level"

• "Configuring an ODBC DSN for the Oracle BI Server on Windows"

• "Configuring an ODBC DSN for the Oracle BI Server on Linux or UNIX"

8.1 About Integrating with the Oracle BI Server as a Data Source

Open Database Connectivity (ODBC) is an industry standard interface for connecting to databases. A Data Source Name (DSN) is used to store the information about connecting to a given database as a given database user over ODBC.

You use the Oracle BI Server ODBC driver to configure a DSN to connect to a set of clustered Oracle BI Servers through the Cluster Controllers, or to an individual Oracle BI Server. The DSN you configure can be used with any ODBC-compliant query and reporting tool.

The Presentation layer lets you configure the presentation of a business model to be consistent with the rules and conventions of your tools to take advantage of the analytical engine and data abstraction of the Oracle BI Server. This makes it much easier to include columns involving complex aggregation and calculation rules in queries and reports. Also, if your organization is currently using query and reporting tools, using the Oracle BI Server as a data source makes these tools more valuable and simplifies the work entailed when using them.

Note: In a non-English environment, you can use a direct ODBC connection from a remote client to the Oracle BI Server. However, the translation and localization option that is provided with Oracle BI Presentation Services is not available. Instead, the translation and localization included in the Oracle BI Server metadata (the Oracle BI repository) is available. For example, if Presentation layer metadata objects are defined in French, they are displayed in French through a third-party application.

8.1.1 About Routing Requests to the Physical Layer

Oracle BI Server clients can create a dedicated physical connection to the Oracle BI Server over which all SELECT queries are treated as SELECT_PHYSICAL queries. SELECT_PHYSICAL queries directly query objects in the Physical layer of the metadata repository, bypassing the Presentation layer and the Business Model and Mapping layer. To enable this direct connection for your ODBC client, select Route Requests To Physical Layer in the DSN configuration.

Note that you cannot have a single ODBC connection that sends queries to both the Presentation layer, and the Physical layer. Instead, you can create one regular connection that sends requests to the Presentation layer, and one connection that sends requests to the Physical layer.

See "Syntax and Usage Notes for SELECT_PHYSICAL" in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information about SELECT_PHYSICAL queries.

8.1.2 About Integrating with the Oracle BI Server Using JDBC

In addition to using ODBC, you can also integrate with the Oracle BI Server using JDBC. For full information, see the README.TXT file contained in the bijdbc.jar file. You can find the bijdbc.jar file in the following directory:

ORACLE_HOME/bifoundation/jdbc

8.2 ODBC Conformance Level

The Oracle BI Server supports the following ODBC calls from client applications:

• SQLAllocConnect

• SQLAllocEnv

• SQLAllocStmt

• SQLBindCol

• SQLCancel

• SQLColumns

• SQLConnect

• SQLDescribeCol

• SQLDisconnect

• SQLDriverConnect

• SQLError

• SQLExecDirect

• SQLExecute

• SQLExtendedFetch

• SQLFetch

• SQLFreeConnect

• SQLFreeEnv

• SQLFreeStmt

• SQLGetConnectOption

• SQLGetCursorName

• SQLGetData

• SQLGetFunctions

• SQLGetInfo

• SQLGetStmtOption

• SQLGetTypeInfo

• SQLColAttributes

• SQLNumResultCols

• SQLPrepare

• SQLRowCount

• SQLSetConnectOption

• SQLSetStmtOption

• SQL Tables

Oracle Business Intelligence ODBC supports full scrollable cursors with static, dynamic, forward only, and key set driven cursors. Oracle Business Intelligence ODBC also supports asynchronous and synchronous processing and cancellation.

8.3 Configuring an ODBC DSN for the Oracle BI Server on Windows

This section explains how to create an ODBC DSN for the Oracle BI Server on Windows to enable remote client access. You use the Oracle BI Server DSN Configuration Wizard to set up an ODBC DSN that you can use to connect to a repository through the Oracle BI Server.

The Oracle BI Server ODBC driver is a 32-bit application. Use the 32-bit version of ODBC Data Source Administrator located at C:\Windows\SysWOW64\odbcad32.exe.

The 64-bit version of the ODBC Data Source Administrator (located at C:\Windows\system32\odbcad32.exe) does not show the Oracle BI ODBC driver.

To create a DSN for the Oracle BI Server on Windows:

1. Open the Windows Control Panel by selecting Start > Settings > Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).

2. In the ODBC Data Source Administrator dialog, click the System DSN tab, and then click Add.

3. In the Create New Data Source dialog, select the driver Oracle BI Server 11g_OHid_number (where id_number is a number specific to an installed Oracle home), and then click Finish. The first page of the Oracle BI DSN Configuration wizard is displayed.

4. Type a name for the data source in the Name field.

   Note: Do not change the name of the centrally managed default DSN for Presentation Services and the Administration Tool.

5. Optionally, enter a description in the Description field.

6. To connect to a single Oracle BI Server that is not part of a cluster, in the Server field, select the computer on which the Oracle BI Server is running.

   If the server name does not appear in the list, then type the computer name in the Server field.

   Note: This field is not used for the default DSN created for Presentation Services, because the Oracle Business Intelligence system is clustered by default.

7. To connect to a set of clustered Oracle BI Servers through the Cluster Controllers, do the following:

   1. Select Clustered DSN.

   2. Enter information for the primary and secondary Cluster Controllers, as follows:

      • In the Primary Controller field, enter the name of the computer that is specified as the primary Cluster Controller. Then, enter the port number for the Primary Controller in the appropriate Port field.

      • If a secondary Cluster Controller has been set up, then type the name of the host where the Secondary Controller is running in the Secondary Controller field. Then, enter the port number for the Secondary Controller in the appropriate Port field.

      You can find information about the Primary and Secondary Controller hosts and ports on the Availability tab of the Capacity Management page in Fusion Middleware Control.

   3. To test the connection to the Cluster Controllers, click Test Cluster Connect.

      If the test is not successful, then correct any errors identified in the message and test the connection again.

   Note: Do not change the Primary Controller, Secondary Controller, and Port parameters of the centrally managed default DSN for Presentation Services.

8. Select Route Requests To Physical Layer to create a dedicated physical connection to the Oracle BI Server for clients using this DSN. All SELECT queries over this connection will be treated as SELECT_PHYSICAL queries.

   SELECT_PHYSICAL queries directly query objects in the Physical layer of the metadata repository, bypassing the Presentation layer and the Business Model and Mapping layer.

9. Select Use Forward Only Cursor to change the ODBC cursor from its default scrollable mode to forward only.

10. To configure this DSN to communicate over SSL, select Use SSL.

    To configure advanced settings for SSL, see "Configuring the ODBC DSN for Advanced SSL Settings" for additional instructions.

11. Click Next. The second page of the Oracle BI DSN Configuration wizard is displayed.

12. Optionally, for Login ID and Password, enter a user name and corresponding password for the Oracle BI Server.

13. To save the user name in the Windows registry for this client, select Save login ID. If you select this option, you will not have to enter your user name each time you connect. You will still have to enter a password.

14. Enter a port number for the Oracle BI Server in the Port field. This option is enabled when Clustered DSN has not been selected on the first page of the wizard.

    You can find information about the Oracle BI Server port on the Availability tab of the Capacity Management page in Fusion Middleware Control.

15. If you want to connect to a repository other than the default repository, then select Change the default repository to, and then type the logical name of the repository to which you want to connect.

    You can only use this setting if you have configured the Oracle BI Server to host multiple repositories. Note that hosting multiple repositories on a single Oracle BI Server is not recommended for production deployments.

    
    

    Note: For Presentation Services clients, each Presentation Services instance can only access a single repository. To configure multiple Presentation Services instances to access multiple repositories, follow these steps: Install Presentation Services on one computer for each repository instance. For example, if you have three repositories running on your Oracle BI Server, install three Presentation Services instances on three separate computers. Adjust the setting Change the default repository to in the DSN for each Presentation Services instance. As a Web client end user, you can choose which repository to access from your client browser by substituting the appropriate Web server computer name or IP address in the URL for the Presentation Services instance that points to the repository you want to work with: http://host_name_or_IP_address/analytics/saw.dll?

    
    

16. Optionally, select Connect to obtain default settings for the additional configuration options.

    If you select this option, the wizard will attempt to connect to the server to obtain default values for the configuration settings on the next screen. If you do not select this option, then you can still configure the DSN by manually entering the information in the next screen.

    If you select this option, you must provide values for Login ID and Password.

17. Click Next. The third page of the Oracle BI DSN Configuration wizard is displayed.

18. To change the name of the default subject area, select Change the default subject area to and then enter the name of the subject area. If you selected the Connect option in the previous screen, you can choose a name from the list.

    Do not select this option for the DSN used by Oracle BI Presentation Services.

    If you do not select this option, the default subject area is the one defined in the internal call query metadata. You can use the DATABASE SQL function to determine the default subject area; see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information.

19. To change the default error message language, select Change the default error message language to and then select the language you want to use for error messages. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about choosing the error message language.

20. If needed, select Use Regional Settings when outputting dates and times. When this option is enabled, data in Date, Time, or DateTime format is displayed using Windows regional settings, rather than the default format for the Oracle BI Server. To see the Windows regional settings, open the Control Panel and double-click Regional and Language Options.

21. If the repository has been set up for database-specific login IDs and passwords, you can enter the user names and passwords for the underlying databases. The database-specific credentials allow privileged users to connect to the underlying databases at the level of authority granted to those users in the databases. To enter user names and passwords for the underlying databases, follow these steps:

    1. Select the database for which you want to enter a user name and password and click Edit.

    2. Enter the login ID and password corresponding to the database you selected and click OK.

    3. Repeat these steps for other databases if necessary.

22. Click Finish to save the DSN configuration.

8.3.1 Configuring the ODBC DSN for Advanced SSL Settings

Follow the steps in this section to configure additional advanced settings for SSL.

To configure the Oracle BI Server DSN for advanced SSL settings:

1. On the first page of the Oracle BI DSN Configuration wizard, after selecting Use SSL, click the Configure SSL button. The Secure Socket Layer Configuration dialog appears.

2. For Certificate File, enter the path and file name of the Client Certificate file, or click Select to browse for the file. For example:

   ORACLE_HOME\ssl\client-cert.pem
   

3. For Certificate Private Key File, enter the path and file name of the Client Private Key file, or click Select to browse for the file. For example:

   ORACLE_HOME\ssl\client-key.pem
   

4. For File Containing Passphrase, enter the path and file name of the passphrase file for the Client Key, or click Select to browse for the file. For example:

   ORACLE_HOME\ssl\clientpwd.txt
   

5. Select Verify Peer.

6. If you are using the hashed version of the CA certificate, enter the directory where the hashed file is located in the CA Certificate Directory field. For example:

   ORACLE_HOME\ssl
   

7. If you are using the CA certificate, provide the path and file name of the CA Certificate file in the CA Certificate File field. For example:

   ORACLE_HOME\ssl\cacert.pem
   

8. For Cipher List, enter the list of ciphers to be used. For example:

   EXP-DES-56-SHA
   

9. For Certificate Verification Depth, specify 1.

10. For Trusted Peer Distinguished Names, enter DNs of servers that will allowed to connect. For example:

    C=US/ST=CA/L=Redwood Shores/O=Oracle/ OU=BI/CN=servercertificate
    

11. Click OK.

12. Complete the additional steps to configure the DSN, starting from Step 11 of the procedure in the previous section.

After you complete these steps, copy the client certificate, client private key, and passphrase files (for example, client-cert.pem, client-key.pem, and clientpwd.txt) to the directory specified in the parameters. If you have set the CA Certificate File parameter, you must also copy the CA certificate file (for example cacert.pem) to the directory specified. If you have set the CA Certificate Directory parameter, copy the hash version of the CA certificate to the directory specified.

8.4 Configuring an ODBC DSN for the Oracle BI Server on Linux or UNIX

This section explains how to create an ODBC DSN for the Oracle BI Server on Linux or UNIX to enable remote client access.

On Linux and UNIX systems, the file odbc.ini contains the standard or clustered Oracle BI ODBC connection details that are used by Oracle BI Presentation Services and nqcmd processes.

Do not change the DSN logical name, or the Primary and Secondary Cluster Controller and Port parameters for the centrally managed default DSN.

To create an ODBC DSN for the Oracle BI Server on Linux or UNIX:

1. Log on as a separate telnet session.

2. Open the odbc.ini file for editing. You can find this file at:

   ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
   

3. In the section [ODBC Data Sources], add the new data source name you want to create and define it as an Oracle BI Server data source. For example:

   [ODBC Data Sources]
   my_new_dsn = Oracle BI Server
   

4. Add a section for the new DSN and provide parameters as follows:

   [my_new_dsn]
   Driver = ORACLE_HOME/bifoundation/server/bin/call_interface.library_suffix
   ServerMachine = local
   Port = bi_server_port_number
   ForwardOnlyCursor = No
   SelectPhysical = No
   Regional = Yes
   

   Note the following:

   • For call_interface, enter nqsodbc for ODBC35 (the default), or nqsodbc20 for ODBC20.

   • For library_suffix, use the library suffix appropriate for your operating system. For example, use .so for Linux, Solaris, or AIX, or use .sl for HP-UX.

   • For Port, enter the Oracle BI Server port number. Do not provide this option if you want this data source to connect to clustered Oracle BI Servers.

     You can find information about the Oracle BI Server port number on the Availability tab of the Capacity Management page in Fusion Middleware Control.

   • Set ForwardOnlyCursor to Yes to change the ODBC cursor from its default scrollable mode to forward only.

   • Set SelectPhysical to Yes to enable integrating clients to create a dedicated physical connection to the Oracle BI Server over which all SELECT queries are treated as SELECT_PHYSICAL queries.

   • Set Regional to Yes to display data in Date, Time, or DateTime format using the regional settings for your operating system, rather than the default format for the Oracle BI Server.

5. If you want this data source to connect to clustered Oracle BI Servers, supply the following additional parameters. Note that the primary CCS and secondary CCS should not be on the same computer.

   IsClusteredDSN=Yes
   PrimaryCCS=primary_cluster_controller_name
   PrimaryCCSPort=primary_cluster_controller_port_number
   SecondaryCCS=secondary_cluster_controller_name
   SecondaryCCSPort=secondary_cluster_controller_port_number
   

   Note the following:

   • If you want this data source to connect to a single Oracle BI Server, set IsClusteredDSN to No.

   • You can find information about the Primary and Secondary Controller hosts and ports on the Availability tab of the Capacity Management page in Fusion Middleware Control.

6. To configure SSL for this data source, supply the following additional parameters as appropriate for your SSL deployment:

   SSL = Yes
   SSLCertificateFile = path_to_client_certificate_file
   SSLPrivateKeyFile = path_to_client_private_key_file
   SSLPassphraseFile = path_to_passphrase_file_for_client_key
   SSLCipherList = list_of_cipers
   SSLVerifyPeer = Yes
   SSLCACertificateDir = path_to_hashed_version_of_ca_certificate_file
   SSLCACertificateFile = path_to_ca_certificate_file
   SSLTrustedPeerDNs = list_of_server_DNs_allowed_to_connect
   SSLCertVerificationDepth = 1
   

7. Save and close the file.

8. If you are updating the DSN used by Presentation Services, you must restart Presentation Services.