Jan 0, 2022

Connecting Oracle NetSuite to TM1

Thanks to TM1py, there is no limit to ways to connect IBM Planning Analytics (TM1) with any data source on cloud or on-premise. This article explains the logic to write a Python script to connect Oracle NetSuite with TM1. The only thing you will need is one Python script.

Executing the Python script

First, you will need to execute the Python script. This can be achieved with a TM1 process as it is explained in the following article

The Python script

The script that you will need to write can be split into four main sections:

  1. Authenticate and request the data from NetSuite

  2. Transform the data

  3. Create the data structure (cube, dimension…)

  4. Send data to TM1 with TM1py

Requesting NetSuite information

In the Python script, there are four ways to connect to NetSuite:

SOAP: SOAP is somewhat deprecated and inferior to the REST based approaches.

RESTlet: A simple and minimalistic REST API that offers custom REST endpoints to execute defined scripts within NetSuite.
The result of those defined scripts is returned to the caller (= python) in JSON. The scripts are written in a proprietary language called SuiteScript

REST: Effectively allows to execute CRUD (Create, Read, Update, Delete) and SuiteQL queries through a REST interface.

ODBC: ODBC into NetSuite cloud but requires additional modules and licensing.

We recommend the REST or RESTlet approach.

You can find details about the different approaches here netsuite.com:

Processing the response

For the REST and RESTlet option, the retrieved information from NetSuite will be in JSON format.

With Python the data can easily be transformed to fit TM1.

Sending data to TM1

Once the data is retrieved and transformed, with TM1py you can create or update dimensions and load data into a cube directly from Python. There is no need for CSV files or intermediary SQL tables.

Where to store the Python script

The machine that runs python requires access to the internet to reach the NetSuite Cloud API. Python does not need to run on the same machine as TM1.

Example 1: Retrieving the accounts information

Below you find a sample of how the NetSuite JSON responses for the accounts can be transformed to create a new dimension in TM1 with the TM1py function tm1.hierarchies.update and finally load the attributes’ values with tm1.cells.write function:

Example 2: Retrieving the trial balance

Below you find a sample of how the NetSuite JSON responses for the trial balance can be transformed and then loaded to TM1 using the TM1py function tm1.cells.write:

 

READ MORE:

Related content

Loading related content