SuiteAnalytics Connect (a.k.a. "Connect Service") lets you select, and report on NetSuite data using database tools via ODBC, JDBC, or other programming standards for database connections. Oracle provides a comprehensive guide to this service, here: https://docs.oracle.com/cd/E60665_01/netsuitecs_gs/NSALC/NSALC.pdf
This article describes the steps to setting up a connection to NetSuite SuiteAnalytics using the JDBC driver provided by Oracle. The connection is made with the Database connector in a Boomi process, and it allows you retrieve NetSuite data using a SQL "SELECT" statement.
Setting up the connection
These are the necessary steps to set up this connection:
- Download the JDBC driver and create and deploy a Custom Library component with it.
- Add a Database shape to your process, and configure the connection correctly.
- Write your SQL SELECT statement to get data from NetSuite.
The information below will go through each of these.
Download and set up the JDBC driver
- You must download the JDBC driver from NetSuite. To do that, you must first log in to NetSuite and in the Settings portlet of your NetSuite homepage, click "Set Up SuiteAnalytics Connect". If the download link is not displayed, you must enable the Connect Service. See the SuiteAnalytics PDF file referenced above for information on this.
- Select the platform your Boomi atom is running on.
- Click the Download button next to the JDBC driver. (This article is not discussing ODBC or other drivers provided by Oracle, but their documentation covers these in detail.)
- Save the .zip file to your local drive.
- Extract the contents of the .zip file, and locate the NQjc.jar file. This file contains the driver.
- In the AtomSphere platform, follow the instructions here to create a Custom Library component using the NQjc.jar file.
- Deploy the Custom Library component to the atom where you will run the process that connects to NetSuite. You can deploy this Custom Library component to the Boomi Atom Cloud or Test Atom Cloud. It should work without any difficulties, as long as your NetSuite account is set to allow this kind of connection.
Database Shape and Connection String
In the Boomi process where you will use this connection, you will need to add a Database connector to your build canvas. The action will always be "Get" for this connector, since the only SQL statements supported are SELECT statements to retrieve data.
- Click on the + icon for the connection to create a new connection.
- Set the Driver Type to "Custom".
- In the User Name and Password fields, you will need to enter your credentials for NetSuite.
In the Class Name field, paste in this value: com.netsuite.jdbc.openaccess.OpenAccessDriver
- In the Connection URL field, you will need to use a connection string like this:
- Replace the example account ID 123456 with your NetSuite account ID.
- Replace the example role ID 3 with a valid role ID from your NetSuite account.
Further details on the connection string parameters and their meaning can be found in the PDF document from Oracle referenced earlier in this article (see pages 35-40).
- Test the connection.
Write your SQL Statement
Because this is a database connection, you need to retrieve the data from NetSuite with a SELECT statement. In the PDF document referenced earlier, there is a wealth of information about the SQL compliance, reserved words, and the Connect Schema including table names, and column joins.
A simple example of a SELECT statement with a WHERE clause that you might use is:
select * from TRANSACTIONS where transaction_id > 1000000 and transaction_id <= 2000000
Of course, you will want to tailor the statement to your own NetSuite data. To help you find the tables to query, the PDF referenced earlier also includes information about a tool called the "SuiteAnalytics Connect Browser" that can be used both , and offline. This tool gives you information about tables, columns, and key relationships that will help you construct queries for your NetSuite data.