Pentaho Business Analytics

This article explains how to connect Pentaho Business Analytics to an Exasol database.

Exasol takes no responsibility for any changes in functionality or terms of use for the third-party software described in this section. For more information, refer to the respective software vendor’s website.

Prerequisites

  • A running Exasol SaaS database. For more information, see Database Management.
  • The IP address of the host where the tool is running must be in the allowed IP address list. For more information, see Network Security.
  • Exasol JDBC driver is installed

Get JDBC Driver and Connection Details

To connect to your Exasol SaaS database from a tool you need a valid connection string. You must also allow network access to the database from the tool, and make sure that the latest Exasol JDBC driver is installed.

  1. On the Databases page in the web console, click on Connect via tools on the cluster that you want to connect to.

    connect via tools

  2. In the wizard, enter the IP address of the client or server that will connect to the database and click Add IP, then click Next. This will add the IP address to the list of IP addresses that are allowed network access to the cluster. The allow list is found on the Security page in the web console. For more information, see Network Security.

    If the IP address is already present in the allow list, just click Next.

    enter ip address

  3. If the latest JDBC driver for your operating system is not already installed, select the driver type and operating system and click on Download to download the latest driver to your local system. For more information about how to install the driver, see JDBC Driver.

    download driver

  4. Click on Next to view your connection details:

    connection details

  5. Copy the Connection string, Port, and User name values from the Connect details dialog. You will need to enter this information in your client to connect to your database.

  6. To generate a new Personal Access Token (PAT) to use for authentication in the client, click on the Password row, then click on Copy to copy the PAT to your clipboard. For more information, see Personal Access Token.

    generated pat

    The generated PAT is only shown once and cannot be retrieved after you have closed the wizard.

  7. Click on Done to close the wizard.

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.