Sunday, October 24, 2021

Configure RangerMSP database connection in PDI

Configure RangerMSP (CommitCRM) database connection in PDI

Once your Pentaho server is installed and working, you will want to establish a connection to your RangerMSP database.

This tutorial will show you how to enable connectivity between Pentaho and Neo4j:

What you need to before you begin

  1. Create a Windows File share for the CommitCRM folder.  Grant permissions (file and share) to the computer account of your Pentaho (PDI) server.

  2. Download the Advantage JDBC database driver 11.  You will need to install the driver, then browse to the installation folder, find and copy the jar file into the /lib folder of your Pentaho server installation.

  3. Launch Spoon
    Create a new transformation, and select the "Execute SQL Script" Step and drag it onto the transformation.

  4. View the properties dialog
    by double clicking on it, or right-click → Edit
    Then on the Connection property, click New

  5. Use the following Connection Settings
    note, there is no username/password. rather, access to the DB is controlled via file/share permissions on your RangerMSP server.  Be sure your Pentaho Computeraccount$ has been given file/share permission on the share

    Connection Name
     <A unique descriptive name>
    Connection Type
     Generic database
    Native (JDBC)
    Custom connection URL
    Custom driver class name
    com.extendedsystems.jdbc.advantage.ADSDriver *note this is case sensitive!


  6. Click "Test" and verify connectivity to your neo4j database

    If successful you should see the following message:

  7. "SHARE" this database connection
    with other transformations/jobs, otherwise you would have to repeat these steps for EACH Transformation/Job.

    Switch from the Design to the View Tab

  8. Expand Database connections
    Right-Click on your new database connection and select Share

You will know the connection is shared if it is displayed in "BOLD".

Now this connection is available to all jobs/transformations for this installation of Spoon/PDI.

No comments:

Post a Comment

Have a comment? Would love to hear it!