Pentaho Business Analytics

This section provides you with information on how to connect Pentaho Business Analytics to your Exasol database.

Prerequisite

You need to have the following:

  • A running Exasol SaaS database. For more information, see Manage Databases and Clusters.
  • The IP address of the client or server where the tool is running must be listed in the allowed IP address list. For more information, see Network Security.

Get Drivers and Connection Details

Before you can proceed with connecting to your database from a tool, download the latest Exasol JDBC driver and get the connection details. Follow these steps to get your connection details: 

  1. Click Databases Databases in the web console to open the Databases page.
  2. Click More on the cluster you want to connect to and then click Connect via tools.
  3. In the Connect to cluster wizard, enter the IP address of the client or server which will connect to the database and click Add IP. Then click Next. If the IP address was already added, just click Next.
  4. If not already installed, download the latest JDBC driver for your operating system and install it.
  5. Click Next and proceed through the wizard until you see your connection details:

  6. Copy the HOST, PORT, USERNAME, and PASSWORD. You can generate a new Personal Access Token by clicking Generate. You will need this information to connect to your database.

Connect Pentaho to Exasol

Follow these steps to connect Pentaho to Exasol:

  1. Copy and paste the Exasol JDBC driver into the Pentaho server directory. The server directory is located at C:\Program Files (x86)\Pentaho\server\pentaho-server\tomcat\lib

    2. Once done, you need to restart the Pentaho server if it is already running.

  2. From the Pentaho User Console (PUC), click Manage Data Sources.

  3. On the Manage Data Source dialog box, you need to define a new connection. Click the gear icon for more menu options and then click New Connection.

  4. The Database Connection dialog box is displayed as show in the below image. Enter the data connection information:
    • Connection Name: Enter a name for the connection.
    • Database Type: Set the database type to Generic database.
    • Access: Set it to Native (JDBC).
    • Settings:
      • Custom Connection URL: Enter the connection URL to your database instance along with the port number.
      • Custom Driver Class Name: Enter the drive class name, which is com.exasol.jdbc.EXADriver.
      • User Name: Enter the user name to connect to the database. This step is optional.
      • Password: Enter the password to connect to the database. This step is optional.

  5. Click Test to test the connection. A success message is displayed if a connection is established.
  6. Click Ok to close the Database Connection dialog box and then click Close.

  7. To be able to use tables that you need from the Exasol instance, you may need to create a new data source. On the User Console Home, click Create New, then click Data Source.

  8. The Data Source Wizard is displayed. Enter a name for the data source. From the Source Type drop-down list select Database Tables(s). Click Next.
  9. In the Connections section, select the connection you created in earlier (in step 3). Click Next.

  10. Select the schema you want to use from the Schema drop-down list and select the tables by moving them to the Selected Tables section.

  11. Click Finish. You will now be able to create dashboards and reports from User Console Home.