Skip to main content
Hitachi VantaraPentaho Documentation
Pentaho Documentation

Define OCI Connections for the BA Server

Add Drivers

The BA Server needs the appropriate driver to connect to the database that stores your data. Your database administrator, Chief Intelligence Officer, or IT manager should be able to provide the appropriate driver. If not, you can download drivers from your database vendor's website. The Supported Technologies section contains a list of drivers.

Once you have the correct driver, copy it to this directory for the BA Server: /pentaho/server/biserver-ee/tomcat/lib/.

There should be only one driver for your database in this directory. Ensure that there are no other versions of the same vendor's driver in this directory. If there are, back up the old driver files and remove them to avoid version conflicts. This is a concern when you are adding a driver for the same database type as your Pentaho BA repository. If you have any concerns about how to proceed, contact Pentaho support.

Driver for Microsoft SQL Server

If you are using a Microsoft SQL Server (MSSQL), you might need to use an alternative, non-vendor-supported driver called JTDS. Contact Pentaho support to ensure that you are adding the correct driver.

For Microsoft Windows, most JDBC drivers support Type 2 integrated authentication through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to all machines and directories to which you copied the JDBC driver. You can find this file in this location.

<installation directory>\sqljdbc_<version>\<language>\auth\
If running: Use the sqljdbc_auth.dll file here:
32-bit Java Virtual Machine (JVM) even if the operating system is version x64 x86 folder
64-bit JVM on a x64 processor x64 folder
64-bit JVM on an Itanium processor IA64 folder

Specify OCI Connections Information

  1. Start the web application and BA Servers, log into the User Console, then click on File > Manage > Data Source. The Data Sources dialog box appears.
  2. Click the plus icon (+) on the right and select JDBC. The Database Connection dialog box appears with General highlighted in the left navigation pane.

    File:/ssbaPdiDialogConnectOci.png

  3. In the Connection Name field, enter a name that uniquely describes this connection. The name can have spaces, but it cannot have special characters, such as #, $, %.
  4. In the Database Type list, select Oracle.
  5. In the Access list, select OCI.
  6. Enter Settings as directed by the Oracle OCI documentation.
    1. In the SID field, enter the Oracle system ID that uniquely identifies the database on the system.
    2. In the Tablespace for Data field, enter the name of the tablespace where the data is stored.
    3. In the Tablespace for Indicies field, enter the name of the tablespace where the indices are stored.
    4. Enter the User Name and Password required to access the database.
  7. Click Test. A success message appears if the connection is established.
  8. To save the connection, click OK twice. This connection name appears in the list of available data sources in the Data Sources dialog box. If you want to use Advanced, Options, or Pooling, refer to the Oracle OCI documentation to understand how to specify these settings.

Edit Existing Connections

Once a connection has been established, you can open the Database Connection dialog box to further refine and change aspects of the connection.
  1. In the User Console, click on File > Manage > Data Source. The Data Sources dialog box appears.
  2. Highlight the name of the data source you want to edit and click Edit (the pencil icon on the right). The Database Connection dialog box appears with General highlighted in the left navigation pane.

    To further refine your connection, or to enable and disable connection options, select items in the left navigation pane.

Specify Advanced SQL Preferences

Setting SQL preferences enables you to standardize how Pentaho software generates SQL and uses a default schema. Preferences for database table and column names are honored, regardless of how they are entered.

  1. Open the Database Connection dialog box.
  2. Click Advanced on the left. The SQL preferences appear.

     

    File:/ssbaPdiDialogConnectAdvanced.png
  3. Check the appropriate boxes and enter the SQL statements as described in this table.
    Feature Description
    Quote all in database Enables case-sensitive table names

    For example MySQL is case-sensitive on Linux, but not case-sensitive on Microsoft Windows. If you quote the identifiers, the databases uses a case-sensitive table name.

    Force all to lower-case Enables the system to change the case of all database to lower-case
    Force all to upper-case Enables the system to change the case of all identifiers to upper-case
    SQL statements ... Enter the SQL statement used to initialize this connection.
  4. Click Test. A success message appears if the connection is established.
  5. To save the options, click OK twice.

Quoting

Pentaho uses a database-specific quoting system that allows you to use any name or character that complies with the supported databases' naming conventions.

Pentaho User Console contains a list of reserved words for most of the supported databases. To ensure that quoting behaves correctly, Pentaho maintains a separation between the schema, or user/owner of a table, and the table name itself. Doing otherwise makes it impossible to correctly quote table or field names that contain one or more periods (.).

To avoid quoting-related errors, a rule stops the Pentaho software from performing quoting activity when there is a start or end quote in the table or schema name. This allows you to specify the quoting mechanism yourself.

Set Options

The driver for your specific database may have options for handling different aspects of its operation. To prepare for setting these options, refer to the driver documentation and look for the names of parameters and values you can set. Then, add them to the Database Connections dialog box.

  1. Open the Database Connection dialog box.
  2. Click Options on the left. The Parameter table appears.

     

    File:/ssbaPdiDialogConnectOptions.png
  3. In the next available row in the Parameters table, enter a valid parameter name and its corresponding value. For JDBC database-specific configuration help, click Help. A new browser window opens and displays additional information about configuring the JDBC connection for the database type that is currently selected in the General pane.
  4. Click Test. A success message appears if the connection is established.
  5. To save the options, click OK twice.

Define Connection Pooling

Instead of having a connection open for each individual report or set of reports in a dashboard, you can set up a connection pool and define options that are available for your driver. For example, you might start by specifying a pool of ten or fifteen connections, and as you run reports, the unused connections drop off.

Pooling helps control database access, especially if you have dashboards that contain many reports and require a large number of connections. Pooling can also be implemented when your database licensing restricts the number of active concurrent connections.

  1. Open the Database Connection dialog box.
  2. Click Pooling on the left. Options appear for your JDBC driver. Enter the appropriate options.

     

    File:/ssbaPdiDialogConnssectPooling.png
     

    This table shows an example of pooling options that might be available in a typical JDBC driver. Check your driver documentation for details.

    Feature Description
    Enable Connection Pooling Enables connection pooling.
    Pool Size Sets the Initial size of the connection pool; and the Maximum number of connections in the connection pool.
    Parameters: Click on any parameter to get a short description of that parameter.

    The most commonly used parameter is validationQuery. The parameter is slightly different depending on the RDBMS that is being connected to.

    • For Oracle and PostgreSQL, use "Select 1 from dual"
    • For MS SQL Server and MySQL, use "Select 1"
  3. Click Test. A success message appears if the connection is established.
  4. To save the options, click OK twice.

Delete Connections

If you no longer need a connection, you can open the Database Connection dialog box and delete the connection.
CAUTION:
Deleting a connection affects all reports, charts, dashboards, and other content that are associated with the connection.
  1. In the User Console click on File > Manage > Data Source. The Data Sources dialog box appears.
  2. Highlight the name of the data source you want to delete and click Remove (the x icon in the red box on the right). The data source no longer appears in the list of data sources.