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:
Authenticate and request the data from NetSuite
Transform the data
Create the data structure (cube, dimension…)
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: