How to load data from NetSuite to Data Warehouse

This integration will allow you to connect with Netsuite and start collecting your data. It is a matter of minutes to send your business finances, operations, and customer relations data to feed your BI, your data warehouse, or your custom analytics stack.

How to connect

Login to your Blendo account and make sure that your data warehouse of choice is selected (at the navigation bar). Click on the “Add Pipeline” button to create a new Netsuite pipeline. You will be presented a list of sources to choose from. This should also be what you see if you have no pipelines created yet. Select the Netsuite icon to start creating your pipeline.

In order to create you Netsuite pipeline, the first step is to fill in all the information in order to authenticate the pipeline. You will need to provide Blendo with 5 IDs/Keys that can be found on your Netsuite account. Below you will find the steps in order to create these IDs/Keys.

The first ID that Blendo is asking you to fill in is your Account ID. In order to find it, you should click on Setup -> Integration -> Web Services Preferences and there you will find the ACCOUNT ID.

After that you should fill in the Consumer Key and the Consumer Secret. In order to create those, you have to add a new Integration on your Netsuite account. You should click on Setup -> Integration -> Manage Integrations -> New. As shown in the image below, you should fill in the Name of the integration (e.g. Blendo), the State should be Enabled and also the Token-Based Authentication should be selected. After you have filled in this information you should click on “Save”.

After you have clicked on “Save”, the Consumer Key and the Consumer Secret will appear on your screen and as the warning message indicates, for security reasons, this is the only time that the Consumer Key and the Consumer Secret values are displayed. Please make sure that you have copied both of them in order to use them during the configuration of your pipeline.

In the next step you should create the Token ID and the Token Secret. In order for these Tokens to be created, you should first create a Role for Blendo and en Employee that has this specific role.

In order to create a Role you should click on Setup -> Users/Roles -> Manage Roles -> New. In this page you should fill in the Name of the Role (e.g. Blendo) and the “Two Factor Authentication required” under the Authentication section should be “Not required”.

Under the Permissions section you should first select Setup, you should give

Full permissions for:

  • Web Services
  • User Access Tokens
  • Log In Using Access Tokens
  • Deleted Records

View permissions for:

  • custom fields
  • custom body fields
  • perform search (you can find this under Lists)

Moreover, for each resource of the pipeline that you want Blendo to sync, you should give to the Role the relevant permissions. The resources are divided among Transactions/Reports/Lists/Custom and each permission should have View level.

NOTE: Please make sure that you have added the permissions for each resource/saved search/custom record that you want Blendo to sync. In case that you see later that a table in Blendo fails to sync with a permissions related issues, this means that there is something missing in this step.

After saving the Role you should create the Employee. In order to do this, you should click on Lists -> Employees ->Employees -> New. There you should fill in the Name and the Email of the Employee. Under Access section, you should select “Give Access” and “Manually assign or change password” and fill in the password. Last, you should add the Role that you have created in the previous step. After you have completed it you should click on “Save”.

Afterwards you should create an Access Token. In order to do that you should click on Setup -> User/Roles -> Access Tokens -> New. There you should select the Application Name (as created in the Integration step), the User (as created in the Employee step) and the Role (as created in the Role step). The Token Name is created by Netsuite.

After you click on Save, the Token ID and the Token Secret will appear on your screen and as the warning message indicates, for security reasons, this is the only time that the Token ID and the Token Secret values are displayed. Please make sure that you have copied both of them in order to use them during the configuration of your pipeline.

So after you have found all the IDs/Keys that your pipeline needs in order to authenticate, you should go back to our Blendo account, fill in the relevant boxes and click on “Next”.

In the configuration step of your pipeline, you are asked to select the Historical Sync Start Date, meaning the date that your pipeline should start syncing data during the first (historical) sync. Moreover, in case you want your pipeline to sync also your Saved Searches, you should fill in the Blendo Restlet URL. As the message also indicates, you should download the relevant .js file and install it in your Netsuite account.

In order to find the Blendo Restlet URL, you should create a new Script in your Netsuite account. In order to create the Script, you should go back to your Netsuite account and click on Customization -> Scripting -> Scripts -> New. There you should click on the “+” icon in order to create a new Script. After clicking on the icon, a new window opens that asks you to select the file that you have just downloaded from Blendo. After you have attached it, you should click on Save and then on Create Script Record.

Afterwards you should fill in the Name of the Script and the Owner, that is the Employee that you have created before. Then click on Save.

Last, you need to deploy the Script. In order to do that you should click on Deployments, then fill in the Title and click on Add. After the Script is deployed you should click on Save.

After that, you should click on the Title of the Deployment and there you will be able to find the External URL, that is the URL that you need for Blendo. At this point you should go back to your Blendo account. After you have filled in the Blendo Restlet URL in the configuration step of the pipeline, you should click on Validate.

In the next step of the creation of the pipeline you are asked to add a table prefix if you want and also select your database schema that the tables will end up to. Keep in mind that in case you add a table prefix, it will be used only by the standard resources and not the custom resources and the saved searches, since you will be asked to create a specific name for these resources later. After selecting these, you should click on “Next”.

After that you can select the resources that you want the pipeline to sync. By default none of the resources are selected, so you should do it manually. You can also use the search bar to find the desired resource more easily.

There are two types of resources that you can add: Custom Records and Saved Searches. In order to do that you should click on “Create Resource” on the top right.

Under Select Resource you have two options: saved_search and custom_record. Firstly, let’s look at saved_search: You have to provide a name for the resource, select the record type that can be found on your Netsuite account and the exact saved search that you have created there. After you have filled in all the information you should click on “Save”.

Now, let’s look at custom_record: You have to provide a name for the resource and select the custom record that can be found on your Netsuite account. After you have filled in all the information you should click on “Save”.

After you have created all your resources and selected the ones that you want to sync from the list, you should click on “Create”. That’s it! Your pipeline is ready to receive data.

Configuration of the pipeline

When selecting the pipeline, you can see some information about it. On the top right you can see the pipeline ID, the data warehouse that the data is synced to and the schema that it is using.

Below you can find three tabs:

The first tab that you can see while selecting your pipeline is the Status tab. There you can see if the pipeline is syncing at the moment and for how long or if it has stopped. Additionally, you can manually stop the pipeline. Moreover, you can see the details of the current or previous sync, specifically the status of each of the tables that are syncing, and how many records have synced.

The second tab is the Resources tab. There you can edit the resource selection for your pipeline by selecting or deselecting any of the resources, or you can create a new resource as described above.

The third tab is the Settings tab. There you can see the the account information of your Netsuite pipeline and you can either pick another account or re-authenticate the pipeline.

Moreover you can edit the configuration of the Source, meaning the Historical Sync Start date. Keep in mind that if the pipeline has already synced some resources starting from a specific date, by changing this date afterwards the tables that have already synced won’t be affected. This date will be valid only for newly created resources that haven’t synced before. Moreover, you can edit the Restlet URL. Keep in mind that the Restlet URL associates with the account selected above, so in case you select another account you should also edit the Restlet URL.

Under that, you can see the Destination configuration and you can edit the table prefix and the database schema that your tables end up to.

Blendo collects the data as soon as they arrive and will sync them to your data warehouse according to your pipeline’s schedule. The default behaviour is to do this once per hour, but you can configure it to run as often as once per 15 minutes or once per day, by clicking on “Reschedule” button. Additionally, next to the “Reschedule” button you can find the “Pause” button that pauses the pipeline and keeps it paused until you manually resume it.

On the bottom of the page you can see the button “Remove”. By clicking it you delete your pipeline. This action is irreversible.

Keep in mind that any changes that may be done to the pipeline will be valid starting from the following sync and on

What NetSuite data do I have available?

Bellow are some of the tables with NetSuite data that we create in your target data warehouse.

Expected NetSuite Data Model & Schema Information

For a complete view of the data model of your expected data click here.

Questions? Contact us!

in Connecting SaaS Data Sources