NetSuite

Knowi enables analytics, visualization, and reporting automation from NetSuite along with 30+ other structured and unstructured data sources. The connector runs SuiteQL queries against NetSuite's REST API using OAuth 2.0 — query transaction lines, general ledger, customers, vendors, and items directly, with no CSV exports or saved searches required.

Overview

1. Create an Integration Record in NetSuite with OAuth 2.0 enabled (one-time setup by a NetSuite administrator).
2. Connect from Knowi with your Account ID, Client ID, and Client Secret, then authenticate via the OAuth popup.
3. Query NetSuite using prebuilt SuiteQL collections or write your own custom SuiteQL.
4. Join NetSuite data with Salesforce, MongoDB, PostgreSQL, REST APIs, and 30+ other sources without ETL.
5. Visualize and automate your reporting instantly.

NetSuite Setup (Prerequisites)

A NetSuite administrator needs to complete the following one-time setup before connecting from Knowi.

Step 1: Create an Integration Record

1. In NetSuite, go to Setup > Integration > Manage Integrations > New.
2. Enter a name for the integration (e.g., "Knowi Analytics").
3. Set State to Enabled.

Step 2: Enable OAuth 2.0

On the same Integration Record, under the Authentication tab:

1. Check Authorization Code Grant under the OAuth 2.0 section.
2. Check the REST Web Services scope.

3. Set the Redirect URI to:

   https://www.knowi.com/service/oauth/redirect
   

   For single-tenant or on-premise Knowi deployments, use your Knowi host instead: https://<your-knowi-host>/service/oauth/redirect

4. Uncheck TBA: Authorization Flow and User Credentials unless you need them for other integrations.
5. Click Save.

Step 3: Copy the Client Credentials

After saving, NetSuite displays the Client ID and Client Secret at the bottom of the confirmation page.

Important: The Client Secret is shown only once. Copy both values now — if you lose the secret, you must reset the credentials on the Integration Record, which invalidates any existing connections.

Step 4: Find Your Account ID

1. Go to Setup > Company > Company Information.
2. Copy the Account ID field (e.g., 1234567).
3. Sandbox accounts: use the lowercase, hyphenated format — e.g., 1234567-sb1. The Account ID is part of the API URL, so the format matters: 1234567_SB1 will not resolve; 1234567-sb1 will.

Required Role Permissions

The NetSuite user who authenticates from Knowi must have a role with:

• REST Web Services permission (Setup > Users/Roles > Manage Roles > [role] > Permissions > Setup)
• Log in using OAuth 2.0 Access Tokens permission
• Record-level permissions for the data being queried (Transactions, Customers, Vendors, Items, etc.)

Also confirm that SuiteTalk (Web Services) and OAuth 2.0 features are enabled under Setup > Company > Enable Features > SuiteCloud.

Connecting in Knowi

1. Log in to Knowi and select Queries from the left sidebar.
2. Click on New Datasource + button and select NetSuite from the list of datasources.

3. Configure the following connection details:

   a. Datasource Name: Enter a name for your datasource (e.g., "NetSuite Production")
   b. NetSuite Account ID: Your Account ID from Setup > Company > Company Information (use the 1234567-sb1 format for sandboxes)
   c. Client ID: The Client ID from your Integration Record
   d. Client Secret: The Client Secret from your Integration Record

4. Click the Authenticate button. A NetSuite login popup opens — log in with a user whose role has the REST Web Services and Log in using OAuth 2.0 Access Tokens permissions, and allow the requested access.
5. After the popup confirms authorization, click Save and start querying.

Querying NetSuite

Knowi ships prebuilt collections for common NetSuite objects. Each collection sends a SuiteQL query to NetSuite's REST API as a POST body:

{"q": "SELECT id, entityid, companyname FROM customer"}

Every collection's SuiteQL is fully editable — treat them as starting points.

Available Collections

Custom SuiteQL Example

SELECT
    t.tranid,
    t.trandate,
    t.type,
    tl.item,
    tl.quantity,
    tl.netamount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'SalesOrd'
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')

SuiteQL supports joins, subqueries, aggregations, and Oracle-style functions. For the schema, see NetSuite's Records Browser (Analytics Browser) and the SuiteQL reference.

Pagination & Token Lifetimes

Pagination: NetSuite returns SuiteQL results in pages of up to 1000 rows, with hasMore and a links[rel=next] URL on each page. Knowi follows all pages automatically — you get the result set as a single dataset, with no manual paging, offset loops, or CSV stitching.

The 100,000-row cap: NetSuite limits a single SuiteQL query to 100,000 rows — this is a NetSuite server-side limit, not a Knowi one. Knowi auto-pages up to that ceiling, but you can't exceed it in one query. If your table is larger, window the query so each run stays under the cap and let Knowi accumulate the results.

Incremental loads (recommended for ongoing feeds). Add a date filter keyed to the last run, set the query's Data Strategy to append/merge (not replace), and schedule it. Each run pulls only new or changed rows:

SELECT ... FROM transactionline tl JOIN transaction t ON tl.transaction = t.id
WHERE tl.lineLastModifiedDate >= {$c9_last_run:yyyy-MM-dd HH:mm:ss}
ORDER BY tl.lineLastModifiedDate

Knowi substitutes {$c9_last_run:...} with the previous execution time, so every scheduled run stays well under 100k and the dataset grows over time. Use any Knowi date token — {$c9_today-1d:yyyy-MM-dd}, etc. — that suits your window.

Keyset backfill (one-time load larger than 100k). Order by a monotonic key and page through it using the last value of each batch as the cursor, repeating until a batch returns fewer than 100,000 rows:

SELECT ... FROM transactionline tl
WHERE tl.id > :last_id_from_previous_batch
ORDER BY tl.id

Avoid deep OFFSET-based paging for very large sets — NetSuite degrades on high offsets; a keyset cursor on an indexed column (id, lineLastModifiedDate) stays fast.

Tokens: NetSuite access tokens are valid for 60 minutes; refresh tokens default to 7 days for confidential clients (configurable on the Integration Record up to 30 days). Knowi refreshes tokens automatically in the background. If the refresh token itself expires (e.g., the datasource wasn't used within the validity window), re-authenticate from the datasource screen by clicking Authenticate again — queries and dashboards are unaffected.

Cloud9QL Transformations

Apply Cloud9QL to post-process SuiteQL results. Example — summarize transaction lines by type:

select transactionType, sum(lineAmountTxnCurrency) as total
group by transactionType

Other common patterns:

• Pivot GL activity by posting period
• Rolling averages on daily revenue
• Date bucketing: select week(trandate) as week, sum(netamount) as revenue group by week

Cross-Source Joins with NetSuite

Knowi can join NetSuite data with any other connected datasource — something SuiteAnalytics workbooks cannot do. Common patterns:

• NetSuite + Salesforce: Join NetSuite invoices with Salesforce opportunities to reconcile bookings vs. billings
• NetSuite + MongoDB: Enrich NetSuite item sales with MongoDB product catalog data
• NetSuite + PostgreSQL: Combine NetSuite financials with operational data

Example: join NetSuite invoices to Salesforce opportunities on account name, then compare closed-won amounts with invoiced amounts per account.

To set up a cross-source join, see Joining Across Multiple Databases.

Scheduling & Direct Queries

• Scheduled (non-direct) execution is recommended for NetSuite: results are stored in Knowi's ElasticStore, dashboards load instantly, and NetSuite REST API governance limits are respected. Use an incremental WHERE filter (e.g., on lineLastModifiedDate) with an upsert data strategy for large transaction datasets.
• Direct execution runs the SuiteQL on NetSuite each time a widget loads. Suitable for small, fast queries; avoid for large paged extracts. See Defining Data Execution Strategy.

Troubleshooting