Querying NetSuite Using SuiteAnalytics Database Connection in Boomi
Querying NetSuite data through Boomi’s native connector can be limiting, especially when dealing with multi-table joins and high-volume queries. SuiteAnalytics Connect offers a robust alternative, providing SQL-like access to NetSuite’s data model for seamless querying and analysis. In this guide, we’ll walk you through setting up SuiteAnalytics Connect with Boomi using JDBC drivers and custom libraries, unlocking better performance and flexibility for your data operations.
SuiteAnalytics Connect:
SuiteAnalytics Connect can be used to archive, analyze, and report on NetSuite Data. You can use any of the NetSuite-provided drivers like ODBC, JDBC, and ADO.NET to connect with the NetSuite.
Enabling the SuiteAnalytics Connect Service Feature :
- To enable the SuiteAnalytics Connect feature login into your NetSuite Account and navigate to
- Setup > Company > Enable Features
- Click the Analytics tab.
- Check the SuiteAnalytics Connect box.
Connect Driver Download Page:
This page will be available to all the roles with SuiteAnalysis Connect Permission. Now navigate to the Dashboard settings portlet and open the Set Up Analytics Connect link.
Here you can find different Drivers available for download. Select the Operating system in the Operating System dropdown list.
Download the JDBC Driver, install it in the local machine, and complete the installation process.
After the installation is finished, navigate to the folder where the Jar file is installed. Locate the NQjc.jar file, which is the JDBC driver, and deploy it to the atom used for testing the database connection. This step ensures that the necessary driver is available for Boomi to establish the connection with NetSuite.
Login into the Boomi account and navigate to the Manage Account Libraries page (Settings > Account Information and Setup > Account Libraries) add the “NQjc.jar” file
Creating a Custom Library:
- On Build Page click Create New and select Custom Library from the list.
- From the Custom Library Type drop-down list, select the type of library it provides 3 options i.e. General, Scripting, and Connector.
- Select the Connector and in the Connector dropdown list select the Database
- Include the “NQjc.jar” Jar file and click Save.
- Create a package component and deploy the Custom Library component to the environment where you will run the process that connects to NetSuite.
Database Connection
In the Boomi process, add the database connector to your build canvas. The action can only be “Get” as the only SQL statements supported are Select statements.
- Click the plus icon (+) for the connection to create a new connection.
- In the connection select the Driver type as Custom.
- Enter your NetSuite login information in the User Name and Password fields.
- Use “com.netsuite.jdbc.openaccess.OpenAccessDriver” in the Class Name column.
- The connection URL is in the format of “jdbc:ns://<service_host>:1708;ServerDataSource=<server_data_source>;Encrypted=1;CustomProperties=(AccountID=<account_ID>;RoleID=<role_ID>);”
- The Variables like <service_host>,<server_data_source>,<account_ID>, and <role_ID> can be obtained through the values available on the SuiteAnalytics Connect Driver Download page under Your Configuration.
- Let the service_host = account677.connect.api.netsuite.com, server_data_source = NetSuite2.com, account_ID = account667, and role_ID = 1983
The connection URL becomes:
“jdbc:ns://account677:1708;ServerDataSource=NetSuite2.com;Encrypted=1;CustomProperties=(AccountID=account667;RoleID=1983);”
To test the connection, click on the test connection button available on the top right corner.
After clicking on Test connection, select the atom that belongs to an environment where we deploy the Custom Library and click on Next.
Once the connection is established we can see the message “Test connection successfully” and then click on Save and close the connection. Now you can create an operation to query the data from NetSuite.
Database Operation
In the NetSuite Operation you can define how to access the database that is selected in the connection (NetSuite) It also provides the batching options.
In the operation, you can select a Database profile in which to write the SQL Statement to query specific data from the database. Create the fields according to the Query and click on Save and Close.
After configuring, save and close the operation. Click on OK on the connector to save changes made on the connector.
Now run the process with the local atom to Query the data from NetSuite.
Using SuiteAnalytics Connect in Boomi opens up powerful capabilities for querying NetSuite data efficiently, overcoming the limitations of native connectors. By leveraging JDBC drivers, custom libraries, and SQL-like access, businesses can streamline high-volume data extraction and analysis directly from NetSuite.
Need help setting up SuiteAnalytics Connect in Boomi? Let’s talk!
