NetSuite ODBC Integration

The NetSuite ODBC connector provides direct access to your NetSuite data using the Open Database Connectivity (ODBC) standard. It supports high-volume data integration and enables you to securely and efficiently retrieve data into Planful.

You can set up multiple ODBC connection profiles, use them in Data Load Rules (DLRs), and schedule automated data loads through Cloud Scheduler. This setup helps ensure smooth data integration while giving you control over how and when data is loaded.

Supported Load Types

You can load the following Load Types from NetSuite ODBC to Planful via DLRs:

• Segment Hierarchies

• GL Data

• Transactions V2

• External Source Model

Navigation: Maintenance > Administration > Configuration Tasks > Data Integration Configuration > Cloud Services > NetSuite ODBC

Note:

The NetSuite ODBC integration feature is available upon request. Contact your Planful Account Manager for more information.

Process Overview

Follow these steps to complete the NetSuite ODBC integration:

• Prerequisites

• Create a NetSuite ODBC profile in Planful

• Create a NetSuite ODBC Data Load Rule (DLR)

• Schedule the DLR using Cloud Scheduler

Prerequisites

Before creating a NetSuite ODBC profile in Planful, ensure the following details and configurations are available:

How to Create a NetSuite ODBC Profile

To create a new NetSuite ODBC profile, do the following:

1. Navigate to Maintenance > Administration > Configuration Tasks > Data Integration Configuration > Cloud Services.

2. Click NetSuite ODBC.

3. Click New Profile.

4. Enter the following details:

   1. Profile Name – A unique name for your NetSuite ODBC profile

   2. Account ID - Your NetSuite Account ID

   3. Client ID - OAuth 2.0 Client ID used in NetSuite

   4. Certificate - The ID of the uploaded certificate in NetSuite.

   5. Private Key - Generated on your local system, and the same should be uploaded to the NetSuite certificate, which helps to sign the JWT for OAuth 2.0 authentication.

5. Click Create.

   Note:

   • You can create and manage multiple NetSuite ODBC profiles.

   • Once saved, the profile appears under Existing Profiles.

Once the NetSuite ODBC profile is successfully created, you can proceed to define a NetSuite ODBC Data Load Rule (DLR) to load your data into Planful.

How to Create a NetSuite ODBC DLR to Load Data

Users with DLR edit access can create NetSuite ODBC DLRs to load the following data types:

• Segment Hierarchies

• Data

  • GL Data

  • Transactions V2

• External Source Model.

To create a NetSuite ODBC DLR, do the following:

1. Navigate to Maintenance > Data Integration > Data Load Rules  

2. Go to New Data Load Rule.

3. Enter Name and Description.

4. Select Load Type as NetSuiteODBC.

5. Select the desired NetSuite ODBC profile

6. Select the Load Item (e.g., Data, Segment Hierarchies)

7. Select the desired Load Sub Item and click Next.

8. In the Query section, enter an SQL Query based on your data load needs.

   Sample Query:

   SELECT tranid, entity, amount, status
   FROM transaction
   WHERE type = 'Invoice' AND tranDate >= '2024-01-01'

9. Complete the Define Overall Rule Settings step as required.

   Note:

   The following options are selected by default in the Define Overall Rule Settings for GL Data.

   • Parameters: Frequency and Time mapping are selected by default.

     • Frequency: Month

     • Time mapping: Down rows

   • Defaults: The Include in the data file field for Fiscal Year and Fiscal Month is set to Yes by default.

10. Complete Manipulate Input File and Define Data Mappings.
    To learn more about the field explanations, click here.

11. Click Save.

Once your NetSuite ODBC Data Load Rule (DLR) setup is complete, you can schedule the process using Cloud Scheduler to automate data loads.

How to Schedule a NetSuite ODBC DLR Using Cloud Scheduler to Load Data

Use Cloud Scheduler to automate the execution of your NetSuite ODBC Data Load Rule (DLR) on a recurring or ad hoc schedule.

1. Click Maintenance > Administration > Cloud Scheduler.

2. Click Process Flow.

3. Click Add to create a scheduled process to load NetSuite data automatically using the Data Load Rule created.

4. In the Code field, enter a unique process name.

5. Click Tasks.

6. Click the Add Task icon.

   1. For Task Type, select Data Load Rule.

   2. For Task Name, select the NetSuiteODBC Data Load Rule created earlier.

   3. If data must be loaded before this data load, select dependencies.

7. Click Save.

8. Click Run Now to start the process immediately or schedule the time and date on the Scheduler tab, and then click Save & Schedule.
   To learn more about Process Flow, click here.

Best Practices

• Chunking/Incremental Loads: Pull by accounting period/date ranges and incremental keys (e.g., date_last_modified) instead of SELECT * across years.

• Optimize Queries: Specially optimize joins, select only needed columns for the retrieval. Aggregate by period for trial balance/IS/BS data.

• Follow standard Data Integrations Data Load Rule setup and data volume guardrail recommendations for efficient processing.