Extracting data from NetSuite for Data Analytics teams

In the fast-paced business landscape, data is the key to making informed decisions and staying ahead of the competition. Enterprises worldwide are turning to powerful ERP (Enterprise Resource Planning) solutions like Oracle NetSuite to streamline their operations, optimize workflows, and gain deeper insights into their business processes. One of the critical aspects of harnessing the full potential of NetSuite lies in efficient data extraction. In this blog post, we will delve into Oracle NetSuite, shed light on its robust API - SuiteTalk, and explore how data pipelines enable seamless data extraction for businesses.

Understanding Oracle NetSuite

Oracle NetSuite is a cloud-based ERP system designed to integrate essential business processes, such as finance, customer relationship management (CRM), inventory management, and more, into a unified platform. As a comprehensive and scalable solution, NetSuite empowers businesses of all sizes to manage their operations efficiently, enhance visibility, and foster growth. It serves as a single source of truth, ensuring that data is consistent across the organization and reducing data silos that hinder productivity.

Introducing SuiteTalk

To unlock the true potential of Oracle NetSuite, developers and businesses can leverage SuiteTalk, a powerful and versatile web services integration platform. SuiteTalk provides a comprehensive set of SOAP (Simple Object Access Protocol) and REST (Representational State Transfer) web services APIs (Application Programming Interfaces) that facilitate seamless communication with NetSuite's data and functionalities.

The following list provides an overview of the main features of REST web services for NetSuite (with references to NetSuite documentation):

• Metadata: the REST API consists of a dynamic schema that is described by the metadata catalog, which serves as the API schema defining the contract, similarly to WSDL and XSD files in SOAP web services.

• CRUD operations: REST web services enables you to perform CRUD (create, read, update, and delete) operations on custom record instances, besides performing business processing on custom records.

• Record Collection Filtering: REST web services uses the N/query module to perform record collection filtering - before using this feature, see N/query Module for information about how this module works.

• Record Actions: with the record action APIs, REST web services can be used to trigger the same business logic that is triggered by the click of a UI button. Record actions can increase productivity by automating regular tasks that previously had to be done manually in the UI.

• Record Transformation: record can be transformed from one type into an another, using data from an existing records. For example, an invoice record can be created from an existing sales order record, using data from the sales order.

• Asynchronous request execution: besides synchronous execution, requests can also be executed asynchronously in REST web services. The asynchronous execution of requests is useful for long-running requests. With asynchronous requests, a request is sent to REST web services, where it is placed in a processing queue and handled asynchronously with other requests, where the client application does not wait for a response. After a job is submitted, a job ID is returned in the REST web services response, which can be checked for the status and result by referencing the job ID. Asynchronous processing can be useful if you expect the connection to NetSuite to be slow or unstable. Asynchronous REST jobs are handled within REST Async Processors, according to the number of SuiteCloud Plus licenses.

• Workbook Dataset Execution: in SuiteAnalytics Workbook, company data can be analyzed using two distinct objects: a dataset and a workbook. Datasets are the basis for all workbooks and workbook components in an account. In a dataset, record type fields and criteria filters can be combined to create a query. The results of this query act as the source data for workbooks.

• Workbook Dataset List: list of all existing SuiteAnalytics Workbook datasets through REST web services is possible to be achieved. Datasets must be created in the SuiteAnalytics Workbook UI and cannot be created through REST web services.

• SuiteQL Query Execution: SuiteQL is a query language based on the SQL database query language and it provides advanced dynamic query capabilities that can be used to access NetSuite records. SuiteQL queries can be executed through REST web services by sending a POST request to the suiteql resource, and specifying the query in the request body after the body parameter q.

In order to connect NetSuite through these services, authentication and integrations need to be set in place - an integration will create consumer keys and secrets, whilst authentication will grant token ID and secrets to each user.

Creating an integration

Inside NetSuite, the following breadcrumb needs to be followed: Setup > Integration > Manage Integrations > New.

Once that gets created, an organization name needs to be set, state needs to be enabled and authentication needs to be set as Token-based authentication. Make sure to get a copy of the consumer key and secret provided.

Enabling REST Web Services

After that is done, make sure to enable REST Web Services to be consumed, which can be done through the following steps:

• In the top navigation bar, go to Setup > Company > Enable Features

• Select the SuiteCloud subtab

• Check the Client SuiteScript and Server SuiteScript options inside the SuiteScript section

• Check for REST Web Services inside SuiteTalk (Web Services) and check Token-based authentication inside the Managed Authentication section

• Make sure to scroll to the bottom of the page and save the changes

Creating roles and assigning to users

API calls to the REST Web Service demand appropriate permissions, which are granted to roles and then bound to an user. Follow these steps to create a new role:

• From the top navigation bar go to Setup > Users/Roles > Manage Roles > New and set up the name to a role

• Check Web Services Only Role inside Authentication

• Go to Permissions > Transactions and then add Find Transaction, Invoice and Sales Order - make sure each has the level Full

• Go to Permissions > Reports and then add SuiteAnalytics Workbook

• Go to Permissions > Lists and then add any/all of the following (make sure to understand which ones you'll need for): Accounts, Contacts, Currency, Customers, Employees, Phonecalls, Projects, Subsidiaries - make sure each has the level Full

• Go to Permissions > Setup and then add Access Token Management, User Access Tokens and REST Web Services - make sure each has the level Full

• Scroll to the bottom of the page and click Save

This recent created role needs to be assigned to an user - make sure a service user is created, so that if any employee gets removed from the company, this integration NetSuite user doesn't go away with it. Follow these steps to assign the role to the user:

• Go to Setup > Users/Roles > Manage Users and select/create the user to receive the recently created role

• From the user’s profile page go to Access > Roles

• Add the role created above

• Scroll to the bottom of the page and click Save

Creating Tokens

Integration with NetSuite REST API is done through a Token Based Authentication using OAuth 1.0 - NetSuite also allows integration with OAuth 2.0, which goes away from this post scope.

Instead of going through the token request, authorization callback and the URL to get an access token, NetSuite allows us to create an OAuth1 session with a HMAC_SHA256 signature, which grants the permission to execute REST API requests successfully.

In order to do that, tokens ID and secret need to be created - they will be used together with the consumer key and secret. Execute the following steps to get the token ID and secret:

• Go to Setup > Users/Roles > Access Tokens > New

• Insert the Application Name (previously created integration), User and Role

• Give the token a name and click Save

How Data Pipelines Enhance Data Extraction from NetSuite using SuiteTalk

Data pipelines are instrumental in enabling businesses to extract, transform, and load (ETL) data from various sources, including Oracle NetSuite. Leveraging SuiteTalk in combination with data pipelines streamlines the data extraction process and ensures data accuracy, consistency, and timeliness. Here's how data pipelines help enhance data extraction from NetSuite:

• Automation and Scheduling: Data pipelines allow you to set up automated data extraction processes, ensuring that data is continuously fetched from Oracle NetSuite at regular intervals. By scheduling data extraction jobs, businesses can eliminate the need for manual intervention, saving time and reducing the risk of errors.

• Data Transformation: Data pipelines provide the ability to transform raw data into a standardized format that aligns with the organization's data structure. This transformation ensures that the data extracted from NetSuite is consistent and ready for analysis, reporting, or integration with other systems.

• Data Enrichment: With data pipelines, businesses can enrich the extracted data by combining it with data from other sources. This enriched dataset offers deeper insights and a more comprehensive understanding of business performance, customer behavior, and market trends.

• Performance Optimization: Data pipelines enable the extraction of data in parallel, optimizing performance and reducing the extraction time. This is particularly beneficial for enterprises dealing with large volumes of data, as it ensures that data is fetched efficiently without causing delays or performance bottlenecks.

• Error Handling and Monitoring: Data pipelines come equipped with error handling mechanisms, ensuring that any data extraction issues are addressed promptly. Additionally, monitoring capabilities provide real-time visibility into data extraction processes, allowing businesses to identify and resolve potential bottlenecks.

Tips on how to start a data extraction process from NetSuite

Knowing the benefits aforementioned above, here are some recommendations on how to set up your own data extraction pipelines on NetSuite:

• Make sure to create OAuth sessions and extract/update data using the REST Web Services (account ID needs to be fetched from Setup > Company > Company Information): with the credentials created before (i.e. consumer key, consumer secret, token id, token secret), establish an OAuth session connection similar to the one below with Python:

from requests_oauthlib import OAuth1Session
from oauthlib import oauth1

session = OAuth1Session(
  client_secret=<CHANGE_HERE_TOKEN_SECRET>,
  client_key=<CHANGE_HERE_TOKEN_KEY>,
  resource_owner_key=<CHANGE_HERE_CONSUMER_KEY>,
  resource_owner_secret=<CHANGE_HERE_CONSUMER_SECRET>,
  realm=<ACCOUNT_ID>,
  signature_method=oauth1.SIGNATURE_HMAC_SHA256,
)

• This session grants a client to run signed HTTP requests (the lib requests_oauthlib). An example is to execute queries using the SuiteQL Query feature, which allows to run any type of SuiteQL Query as desired. An example python code to query data from the Contacts table can be seen below:

import requests, json

def parse_suiteql_response(self, response):
  response_json = json.loads(response.text)

  items = response_json["items"]
  offset = response_json["offset"]
  count = response_json["count"]
  total = response_json["totalResults"]

  if response_json["hasMore"]:
    next_link = next(
      link for link in response_json["links"] if link["rel"] == "next"
    )["href"]
  else:
    next_link = None

  return items, offset, count, total, next_link

url = f"https://<ACCOUNT_ID.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql"
body = json.dumps({"q": "select * from contacts"})
headers = {"Prefer": "transient", "Content-Type": "application/json"}

while True:
  response = session.post(url=url, data=body, headers=headers)

  try:
    response.raise_for_status()
  except requests.exceptions.HTTPError as e:
    raise Exception(
      f"SuiteQL request failed. {e}. Response Body: {response.text}"
    )

  items, offset, count, total, next_link = parse_suiteql_response(response)

  data = data + items

  if next_link:
    url = next_link
  else:
    break

return data

• For tables that have a good amount of rows inside (i.e. more than 1 million), it's recommended to execute extractions in chunks of 10_000 rows - this avoids NetSuite facing quota limits issues on data extraction

• In the same perspective, data extraction from dense tables (i.e. not only voluminous but also deeply complex with multiple columns) can be speed it up with concurrent extractions in multiple batches. This demands knowing the min/max range from an unique and incremental column to make sure chunks are created and executed properly. Concurrent executions are done with a parallel library/code, which in python is handled by the library joblib. An example of a parallel extraction with chunks for a transaction line table can be seen below:

# run_netsuite_query works as a generic function that executes the HTTP signed request as shown beforehand

from joblib import Parallel, delayed
import numpy as np
from tqdm import tqdm

table_id   = "transaction"
table      = "transactionline"
chunk_size = 10_000

# Get total of existent records for transaction
minmax = run_netsuite_query(
  f"""select min({table_id}) as min_id, max({table_id}) as max_id from {table}""",
  table,
)
min_id = int(minmax[0]["min_id"])
max_id = int(minmax[0]["max_id"])

chunks = max_id // chunk_size

betweens = [
  f" where transaction_lines.{table_id} between {i.min()} and {i.max()}"
  for i in np.array_split(range(min_id, max_id + 1), chunks)
]
betweens = list(enumerate(betweens))

# Execute extractions in parallel
Parallel(n_jobs=-1, prefer="threads")(
  delayed(run_netsuite_query)(
    between,
    table
  )
  for between in tqdm(betweens)
)

• In case NetSuite throws errors on data extraction limits, always make sure a try/except clause captures that process with some time sleep in the code. These suggestions might cover most part of quota limits and restrictions on data extraction from the SuiteQL Queries feature.

Conclusion

Oracle NetSuite serves as a vital asset for businesses seeking to streamline their operations and gain valuable insights into their performance. To harness the full potential of NetSuite, organizations can integrate data pipelines with SuiteTalk, enabling seamless data extraction, transformation, and loading. With automation, standardization, and performance optimization, businesses can unlock the power of their data, make informed decisions, and drive growth in today's competitive landscape.