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

RangerMSP (CommitCRM) Server File Share for the CommitCRM application folder Configured and working Pentaho Data Integration 9.x server Advantage JDBC database driver 11

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
   Access	Native (JDBC)
   Custom connection URL	jdbc:extendedsystems:advantage://<servername>:6262;catalog=//<servername>/CommitCRM/Db
   Custom driver class name	com.extendedsystems.jdbc.advantage.ADSDriver *note this is case sensitive!
   Username
   Password

   
   
   
   
   
   
   
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.