How to read NetSuite data in Power BI

Posted on by
Contents hide
1 Introduction
2 Prerequisite
3 What is NetSuite CRM?
4 Preparing for NetSuite WebService Call in Power BI
4.1 How to enable NetSuite Web Service Feature
4.2 Obtain NetSuite Account ID
4.3 Obtain NetSuite Application ID
5 Understanding how NetSuite SOAP API Call works
5.1 NetSuite Login Call
5.2 NetSuite API Call ( getAll method )
5.3 NetSuite Search API (Pagination Example)
6 Getting Started
6.1 Creating NetSuite ODBC Connection (Using ZappySys XML / SOAP Driver)
6.2 Import NetSuite data in Power BI
7 How to debug Web Requests using Fiddler
8 How to call other NetSuite SOAP API
9 Conclusion
9.1 More from ZappySys Blog

Introduction

Power BI IntegrationIn our previous blog we discussed how to import REST / SOAP API data in Power BI. Now we will use that knowledge to read NetSuite data in Power BI (NetSuite CRM – SuiteTalk). We will use ZappySys XML Driver to call Search function in NetSuite SOAP API. If you are not familiar with SOAP API concept then we highly recommend you read previous blog post. In this post we will use Power BI as an example App to consume NetSuite data but you can use steps listed in this article to load data in any other ODBC compliant ETL / reporting app (e.g. Excel, Qlik, Informatica, SSRS) or programming language (e.g. C#, JAVA, Python) or SQL Server (Linked Server).

We will look at each step in depth, how to enable SOAP Api access in NetSuite, How to register Application, how to configure NetSuite ODBC DSN, and finally how to load NetSuite data into Power BI dashboard.

So let’s get started. The approach listed in this article can be used to call other SOAP based API such as Salesforce, Workday, SAP, Sage etc.

Prerequisite

Before you get started make sure following requirements are met.

  1. NetSuite CRM Login (UserID and Password)
  2. Enable Web Service Feature in NetSuite
  3. Obtain NetSuite Account ID
  4. Obtain Application ID if you have created an App. Else Register new Netsuite Application and get Application ID
  5. Download ZappySys ODBC PowerPack (for SOAP / XML Driver)
  6. Download Power BI Desktop (FREE) if you have not already done it.

What is NetSuite CRM?

NetSuite is a CRM / ERP product. It gives you scalable cloud CRM / ERP solution targeted at high-growing, mid-sized businesses and large enterprises. It automates front- and back-office processes including: financial management, revenue management, fixed assets, order management, billing, and inventory management.

Preparing for NetSuite WebService Call in Power BI

For NetSuite API call we have to make sure certain settings and obtain Application ID and AccountID. So let’s get started.

How to enable NetSuite Web Service Feature

Before you call any NetSuite SOAP API (i.e. SuiteTask Web Service), First thing to get started with NetSuite SOAP API (i.e. ) is to enable Web Service Feature. Once you enable Web Service, you can programmatically access NetSuite data in your App (e.g. Power BI, Tableau, Informatica). Click this link to see steps to enable Web Service Feature
Enable NetSuite Web Service Feature (SuiteTalk API)

Enable NetSuite Web Service Feature (SuiteTalk API)

Obtain NetSuite Account ID

NetSuite Account ID is needed by Login API call. So perform following steps if you dont know account id.
  1. Login to your NetSuite Portal
  2. Click the Setup Tab
  3. In the drop-down click Integration >  Web services preferences
  4. On that page you will find your Account ID (example account ID = TSTDRV1234567 )
Obtain NetSuite Account ID (Needed for Web Service Login)

Obtain NetSuite Account ID (Needed for Web Service Login)

Obtain NetSuite Application ID

Once you have account ID now we need to get Application ID. For that follow these steps.
  1. Login to your NetSuite Portal
  2. Click the Setup Tab
  3. In the drop-down click Integration >  Manage Integrations
  4. On that page you will find your existing Applications along with Account ID / Status (example Application ID = AE2EACFD-1234-1222-1111-12345544555 )
  5. If you don’t have any application created yet then just click on New.
Create new App / Get NetSuite Application ID

Create new App / Get NetSuite Application ID

Understanding how NetSuite SOAP API Call works

In this section we will briefly understand how NetSuite SOAP API call works. When you use ZappySys Driver / Component it will take care many details for you but still you have to configure correct XML Body. There are two steps involved before you can call any SOAP API in NetSuite.
  1. Call Login method and extract JSESSIONID from response Cookie (This token expires after some time)
  2. Call any other API (Pass obtained JSESSIONID token in Cookie header)

NetSuite Login Call

Request 
POST https://webservices.netsuite.com/services/NetSuitePort_2017_1
Content-Type: text/xml;charset=UTF-8
SOAPAction: "login"
Accept-Encoding: gzip, deflate

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <applicationInfo xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <applicationId>xxxxxxxxxxxxxx</applicationId>
    </applicationInfo>
  </soap:Header>
  <soap:Body>
    <login xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <passport>
        <email xmlns="urn:core_2017_1.platform.webservices.netsuite.com">xxxxxxxxxxxxx</email>
        <password xmlns="urn:core_2017_1.platform.webservices.netsuite.com">xxxxxxxxxxxx</password>
        <account xmlns="urn:core_2017_1.platform.webservices.netsuite.com">TSTxxxxxxxxxxxxxxx</account>
        <role internalId="3" xmlns="urn:core_2017_1.platform.webservices.netsuite.com" />
      </passport>
    </login>
  </soap:Body>
</soap:Envelope>
Response Notice JSESSIONID=6xI6xxxxxxxxx in the login call response below. This Session ID can be used to call next call. ZappySys HTTP Connection Manager configured with Dynamic Token option can extract Cookie and reuse it until its expired (1 hour by default). 
HTTP/1.1 200 OK
Date: Wed, 03 Oct 2018 16:50:41 GMT
Content-Length: 1676
Content-Type: text/xml; charset=utf-8
X-N-OperationId: 777274f6-49c6-4883-bcd1-1d70df90d78e
Strict-Transport-Security: max-age=31536000
NS_RTIMER_COMPOSITE: 13633xxxxxxxxx:70617xxxxxxxxxxxxx36F6D:80
Set-Cookie: JSESSIONID=6xI61rdFt26pxxxxxxxxxxxxxxxqvJIMlu!-17981123445; path=/; secure; HttpOnly
Set-Cookie: NS_VER=2018.1.0; domain=webservices.netsuite.com; path=/
Set-Cookie: NS_ROUTING_VERSION=LAGGING; path=/
P3P: CP="CAO PSAa OUR BUS PUR"
Vary: User-Agent
Keep-Alive: timeout=10, max=990
Connection: Keep-Alive

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
    <platformMsgs:documentInfo xmlns:platformMsgs="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformMsgs:nsId>WEBSERVICES_xxxxxxxxxxxxxxxxx_9fc719</platformMsgs:nsId>
    </platformMsgs:documentInfo>
  </soapenv:Header>
  <soapenv:Body>
    <loginResponse xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <sessionResponse xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
        <ns1:status isSuccess="true" xmlns:ns1="urn:core_2017_1.platform.webservices.netsuite.com"/>
        <ns2:wsRoleList xmlns:ns2="urn:core_2017_1.platform.webservices.netsuite.com">
          <ns2:wsRole>
            <ns2:role internalId="3">
              <ns2:name>Administrator</ns2:name>
            </ns2:role>
            <ns2:isDefault>false</ns2:isDefault>
            <ns2:isInactive>false</ns2:isInactive>
          </ns2:wsRole>
          <ns2:wsRole>
            <ns2:role internalId="15">
              <ns2:name>Employee Center</ns2:name>
            </ns2:role>
            <ns2:isDefault>false</ns2:isDefault>
            <ns2:isInactive>false</ns2:isInactive>
          </ns2:wsRole>
      </sessionResponse>
    </loginResponse>
  </soapenv:Body>
</soapenv:Envelope>
 

NetSuite API Call ( getAll method )

Now once you have JSESSIONID extracted you can pass it to call next API call as below. For example here is how to call getAll method to obtain currency list and exchange rates. If you are not sure how to craft correct Request XML then use tool like SoapUI. You can use NetSuite WSDL file from here and import it in SoapUI.   Request See how Cookie is passed for authentication purpose. Again you can pass this manually or use HTTP connection with Dynamic Token to pass it automatically along with each API call. 
POST https://webservices.netsuite.com/services/NetSuitePort_2017_1
Content-Type: text/xml;charset=UTF-8
SOAPAction: "getAll"
Cookie: JSESSIONID=6xI61rxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxMlu!-1798141234
Accept-Encoding: gzip, deflate

<soapenv:Envelope 
   xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:urn="urn:messages_2018_1.platform.webservices.netsuite.com" 
   xmlns:urn1="urn:core_2018_1.platform.webservices.netsuite.com">
   <soapenv:Body>
      <urn:getAll>
         <record recordType="currency"/> 
      </urn:getAll>
   </soapenv:Body>
</soapenv:Envelope>
Response 
HTTP/1.1 200 OK
Date: Wed, 03 Oct 2018 16:50:42 GMT
Content-Length: 9648
Content-Type: text/xml; charset=utf-8
X-N-OperationId: 1b2989e4-b930-4a72-b471-00a8e131dfd4
NS_RTIMER_COMPOSITE: 21403xxxxxx:706172746E65xxxxxxxxxxxx6F6D:80
Strict-Transport-Security: max-age=31536000
P3P: CP="CAO PSAa OUR BUS PUR"
Vary: User-Agent

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
    <platformMsgs:documentInfo xmlns:platformMsgs="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformMsgs:nsId>WEBSERVICES_TSTDRV1439151_100320189871507121000128715_6ae8e8</platformMsgs:nsId>
    </platformMsgs:documentInfo>
  </soapenv:Header>
  <soapenv:Body>
    <getAllResponse xmlns="urn:messages_2018_1.platform.webservices.netsuite.com">
      <platformCore:getAllResult xmlns:platformCore="urn:core_2017_1.platform.webservices.netsuite.com">
        <platformCore:status isSuccess="true" />
        <platformCore:totalRecords>11</platformCore:totalRecords>
        <platformCore:recordList>
          <platformCore:record internalId="1" xsi:type="listAcct:Currency" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:name>USD</listAcct:name>
            <listAcct:symbol>USD</listAcct:symbol>
            <listAcct:isBaseCurrency>true</listAcct:isBaseCurrency>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:overrideCurrencyFormat>false</listAcct:overrideCurrencyFormat>
            <listAcct:displaySymbol>$</listAcct:displaySymbol>
            <listAcct:symbolPlacement>_beforeNumber</listAcct:symbolPlacement>
            <listAcct:locale>_unitedStatesEnglish</listAcct:locale>
            <listAcct:formatSample>$1,234.56</listAcct:formatSample>
            <listAcct:exchangeRate>1.0</listAcct:exchangeRate>
            <listAcct:fxRateUpdateTimezone>_americaNewYork</listAcct:fxRateUpdateTimezone>
            <listAcct:currencyPrecision>_two</listAcct:currencyPrecision>
          </platformCore:record>
          <platformCore:record internalId="2" xsi:type="listAcct:Currency" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:name>GBP</listAcct:name>
            <listAcct:symbol>GBP</listAcct:symbol>
            <listAcct:isBaseCurrency>true</listAcct:isBaseCurrency>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:overrideCurrencyFormat>false</listAcct:overrideCurrencyFormat>
            <listAcct:displaySymbol>£</listAcct:displaySymbol>
            <listAcct:symbolPlacement>_beforeNumber</listAcct:symbolPlacement>
            <listAcct:locale>_unitedKingdomEnglish</listAcct:locale>
            <listAcct:formatSample>£1,234.56</listAcct:formatSample>
            <listAcct:exchangeRate>1.33165497</listAcct:exchangeRate>
            <listAcct:fxRateUpdateTimezone>_americaNewYork</listAcct:fxRateUpdateTimezone>
            <listAcct:currencyPrecision>_two</listAcct:currencyPrecision>
          </platformCore:record>
          <platformCore:record internalId="3" xsi:type="listAcct:Currency" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:name>CAD</listAcct:name>
            <listAcct:symbol>CAD</listAcct:symbol>
            <listAcct:isBaseCurrency>true</listAcct:isBaseCurrency>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:overrideCurrencyFormat>false</listAcct:overrideCurrencyFormat>
            <listAcct:displaySymbol>$</listAcct:displaySymbol>
            <listAcct:symbolPlacement>_beforeNumber</listAcct:symbolPlacement>
            <listAcct:locale>_canadaEnglish</listAcct:locale>
            <listAcct:formatSample>$1,234.56</listAcct:formatSample>
            <listAcct:exchangeRate>0.76457273</listAcct:exchangeRate>
            <listAcct:fxRateUpdateTimezone>_americaNewYork</listAcct:fxRateUpdateTimezone>
            <listAcct:currencyPrecision>_two</listAcct:currencyPrecision>
          </platformCore:record>
        </platformCore:recordList>
      </platformCore:getAllResult>
    </getAllResponse>
  </soapenv:Body>
</soapenv:Envelope>

NetSuite Search API (Pagination Example)

Now let's look at more complex API calls where Pagination is needed. For example when you call search API to pull data from specific table (e.g. Account) you need to loop through multiple responses until all records are consumed. Request (Page-1) 
POST https://webservices.netsuite.com/services/NetSuitePort_2017_1
Content-Type: text/xml; charset=utf-8
SOAPAction: "search"
Cookie: JSESSIONID=BKQ7xxxxxxxxxxxx4Ug!-995xxxxxx
Accept-Encoding: gzip, deflate

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <applicationInfo xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <applicationId>xxxxxxxxxxxxxxx</applicationId>
    </applicationInfo>
    <searchPreferences xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <pageSize>50</pageSize>
    </searchPreferences>
  </soap:Header>
  <soap:Body>
    <search xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <searchRecord xmlns:q1="urn:accounting_2017_1.lists.webservices.netsuite.com" xsi:type="q1:AccountSearch">
        <q1:basic>
          <description operator="doesNotContain" xmlns="urn:common_2017_1.platform.webservices.netsuite.com">
            <searchValue xmlns="urn:core_2017_1.platform.webservices.netsuite.com">x</searchValue>
          </description>
          <name operator="contains" xmlns="urn:common_2017_1.platform.webservices.netsuite.com">
            <searchValue xmlns="urn:core_2017_1.platform.webservices.netsuite.com">a</searchValue>
          </name>
        </q1:basic>
      </searchRecord>
    </search>
  </soap:Body>
</soap:Envelope>
  Response (Page-1) In below response notice 2 things. SearchId is like a cursor id which you have to pass for each page you request after first page. And you can stop looping after 4th page based on totalPages count. platformCore:searchId = WEBSERVICES_ABCD_12345-xxxxxxxxx platformCore:totalPages = 4 
HTTP/1.1 200 OK
Date: Wed, 03 Oct 2018 18:40:33 GMT
Content-Length: 49266
Content-Type: text/xml; charset=utf-8
X-N-OperationId: c1e79336-63b7-4eb7-989e-5c3d24c56891
NS_RTIMER_COMPOSITE: 118xxxxxxxx:70617xxxxxxxxxxxxxxxxxxxxxD:80
Strict-Transport-Security: max-age=31536000
P3P: CP="CAO PSAa OUR BUS PUR"
Vary: User-Agent

<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
    <platformMsgs:documentInfo xmlns:platformMsgs="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformMsgs:nsId>WEBSERVICES_ABCD_12345-xxxxxxxxx</platformMsgs:nsId>
    </platformMsgs:documentInfo>
  </soapenv:Header>
  <soapenv:Body>
    <searchResponse xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformCore:searchResult xmlns:platformCore="urn:core_2017_1.platform.webservices.netsuite.com">
        <platformCore:status isSuccess="true" />
        <platformCore:totalRecords>193</platformCore:totalRecords>
        <platformCore:pageSize>50</platformCore:pageSize>
        <platformCore:totalPages>4</platformCore:totalPages>
        <platformCore:pageIndex>1</platformCore:pageIndex>
        <platformCore:searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx</platformCore:searchId>
        <platformCore:recordList>
          <platformCore:record internalId="1" xsi:type="listAcct:Account" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:acctType>_bank</listAcct:acctType>
            <listAcct:acctNumber>1111</listAcct:acctNumber>
            <listAcct:acctName>Checking</listAcct:acctName>
            <listAcct:includeChildren>false</listAcct:includeChildren>
            <listAcct:currency internalId="1">
              <platformCore:name>USD</platformCore:name>
            </listAcct:currency>
            <listAcct:generalRate>_current</listAcct:generalRate>
            <listAcct:parent internalId="246">
              <platformCore:name>xxxxxxxxxxxxxxxxxxx</platformCore:name>
            </listAcct:parent>
            <listAcct:cashFlowRate>_average</listAcct:cashFlowRate>
            <listAcct:description>Checking</listAcct:description>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:inventory>false</listAcct:inventory>
            <listAcct:eliminate>false</listAcct:eliminate>
            <listAcct:revalue>true</listAcct:revalue>
          </platformCore:record>
          <platformCore:record internalId="2" xsi:type="listAcct:Account" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:acctType>_bank</listAcct:acctType>
            <listAcct:acctNumber>22222</listAcct:acctNumber>
            <listAcct:acctName>Savings</listAcct:acctName>
            <listAcct:includeChildren>false</listAcct:includeChildren>
            <listAcct:currency internalId="1">
              <platformCore:name>USD</platformCore:name>
            </listAcct:currency>
            <listAcct:generalRate>_current</listAcct:generalRate>
            <listAcct:parent internalId="246">
              <platformCore:name>yyyyyyyyy</platformCore:name>
            </listAcct:parent>
            <listAcct:cashFlowRate>_average</listAcct:cashFlowRate>
            <listAcct:description>Savings</listAcct:description>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:inventory>false</listAcct:inventory>
            <listAcct:eliminate>false</listAcct:eliminate>
            <listAcct:revalue>true</listAcct:revalue>
          </platformCore:record>
          .........
          .........
          .........
          .........		  
        </platformCore:recordList>
      </platformCore:searchResult>
    </searchResponse>
  </soapenv:Body>
</soapenv:Envelope>
  Request (Page-2 and onwards) Here is the second page request. Notice few things here. In heraders we are now sending SOAPAction: "searchMoreWithId" rather than "search".  Also our SOAP Body is also different compared to first page request. Now we only sending pageIndex we want to fetch and searchId. We have also change <search> tag with <searchMoreWithId> under Body. 
POST https://webservices.netsuite.com/services/NetSuitePort_2017_1
Content-Type: text/xml; charset=utf-8
SOAPAction: "searchMoreWithId"
Cookie: JSESSIONID=BKQ7xxxxxxxxxxxx4Ug!-995xxxxxx
Accept-Encoding: gzip, deflate

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <applicationInfo xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <applicationId>xxxxxxxxxxxxxxxxxx</applicationId>
    </applicationInfo>
    <searchPreferences xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <pageSize>50</pageSize>
    </searchPreferences>
  </soap:Header>
  <soap:Body>
    <searchMoreWithId xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx</searchId>
      <pageIndex>2</pageIndex>
    </searchMoreWithId>
  </soap:Body>
</soap:Envelope>
Response (Page-2 and onwards) 
HTTP/1.1 200 OK
Date: Wed, 03 Oct 2018 18:40:33 GMT
Content-Length: 49266
Content-Type: text/xml; charset=utf-8
X-N-OperationId: c1e79336-63b7-4eb7-989e-5c3d24c56891
NS_RTIMER_COMPOSITE: 118xxxxxxxx:70617xxxxxxxxxxxxxxxxxxxxxD:80
Strict-Transport-Security: max-age=31536000
P3P: CP="CAO PSAa OUR BUS PUR"
Vary: User-Agent

<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
    <platformMsgs:documentInfo xmlns:platformMsgs="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformMsgs:nsId>WEBSERVICES_ABCD_12345-xxxxxxxxx</platformMsgs:nsId>
    </platformMsgs:documentInfo>
  </soapenv:Header>
  <soapenv:Body>
    <searchMoreWithIdResponse xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformCore:searchResult xmlns:platformCore="urn:core_2017_1.platform.webservices.netsuite.com">
        <platformCore:status isSuccess="true" />
        <platformCore:totalRecords>193</platformCore:totalRecords>
        <platformCore:pageSize>50</platformCore:pageSize>
        <platformCore:totalPages>4</platformCore:totalPages>
        <platformCore:pageIndex>2</platformCore:pageIndex>
        <platformCore:searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx</platformCore:searchId>
        <platformCore:recordList>
          <platformCore:record internalId="1" xsi:type="listAcct:Account" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:acctType>_bank</listAcct:acctType>
            <listAcct:acctNumber>8000</listAcct:acctNumber>
            <listAcct:acctName>Checking</listAcct:acctName>
            <listAcct:includeChildren>false</listAcct:includeChildren>
            <listAcct:currency internalId="1">
              <platformCore:name>USD</platformCore:name>
            </listAcct:currency>
            <listAcct:generalRate>_current</listAcct:generalRate>
            <listAcct:parent internalId="246">
              <platformCore:name>xxxxxxxxxxxxxxxxxxx</platformCore:name>
            </listAcct:parent>
            <listAcct:cashFlowRate>_average</listAcct:cashFlowRate>
            <listAcct:description>Checking</listAcct:description>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:inventory>false</listAcct:inventory>
            <listAcct:eliminate>false</listAcct:eliminate>
            <listAcct:revalue>true</listAcct:revalue>
          </platformCore:record>
          <platformCore:record internalId="2" xsi:type="listAcct:Account" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:acctType>_bank</listAcct:acctType>
            <listAcct:acctNumber>8001</listAcct:acctNumber>
            <listAcct:acctName>Savings</listAcct:acctName>
            <listAcct:includeChildren>false</listAcct:includeChildren>
            <listAcct:currency internalId="1">
              <platformCore:name>USD</platformCore:name>
            </listAcct:currency>
            <listAcct:generalRate>_current</listAcct:generalRate>
            <listAcct:parent internalId="246">
              <platformCore:name>yyyyyyyyy</platformCore:name>
            </listAcct:parent>
            <listAcct:cashFlowRate>_average</listAcct:cashFlowRate>
            <listAcct:description>Savings</listAcct:description>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:inventory>false</listAcct:inventory>
            <listAcct:eliminate>false</listAcct:eliminate>
            <listAcct:revalue>true</listAcct:revalue>
          </platformCore:record>
          .........
          .........
          .........
          .........		  
        </platformCore:recordList>
      </platformCore:searchResult>
    </searchMoreWithIdResponse>
  </soapenv:Body>
</soapenv:Envelope>
   

Getting Started

Now let’s check step by step instructions on how to load NetSuite data in Power BI. In this exercise we will focus on calling search API but you can call virtually any API using the same technique.

For detailed help on NetSuite SOAP API and other useful information check this documentation. To browse schema you can use this link. From schema browser we will use Search Type name later on in this article. For example later on in this article to search accounts we will use AccountSearch as below example. This name can be found from here(click search tab) You can change it to something else. For now dont worry too much until we get to that point.

Creating NetSuite ODBC Connection (Using ZappySys XML / SOAP Driver)

Once you have all necessary information handy we can create ODBC DSN for NetSuite which can be used by any ODBC compliant app such as Power BI. Perform the following steps to set NetSuite Connection which we will use to query data (e.g. Accounts, Budget, Campaign etc).

 

  1. Open start menu and search for “ODBC 64”. Launch ODBC Data sources for 64 bitOpen ODBC Data Sources from Startup menu
  2. Click Add and select ZappySys XML Driver
    ZappySys ODBC Driver for XML / SOAP API

    ZappySys ODBC Driver for XML / SOAP API

  3. Once driver UI is visible change default DSN name (e.g. change to ZS – NetSuite)
  4. Click on the Load Connection String button and enter the following string after replacing below placeholders to configure the NetSuite connection. If you are unsure how to get Account ID and Application ID then check previous sections. For now dont worry about various options in the connection string. We will cover them later.
    Replace $$$YOUR-APPLICATION-ID$$$  (2 times) with your own Application ID
    Replace $$$YOUR-ACCOUNT-ID$$$ with your own Account ID
    Replace $$$YOUR-USER-ID$$$  with your own Login ID (e.g myuser@mycompany.com)
    Replace $$$YOUR-PASSWORD$$$  with your own Password
    Replace <pageSize>50</pageSize>  with your own value. We suggest not to go more than 200 rows per page for better performance.
    Replace c:\temp\netsuite.cache.db with your own path for Cache File. Caching must be ON in order to Import data in Power BI correct way. We also use CacheTtl=120 seconds (Cache Expires in 120 seconds) but adjust it longer if your data import takes long time.
     
    ZappySys ODBC Driver Load Connection String - NetSuite Connection Example

    ZappySys ODBC Driver Load Connection String – NetSuite Connection Example

  5. After configuration your screen may look like below. You can also click on Advanced View (Radio button) to see some Advanced properties (e.g. Caching Properties such as CachingMode, CacheFileLocation and Advanced Pagination Properties such as PagingMaxPagesExpr, HasDifferentNextPageInfo, NextPageBodyPart, FirstPageBodyPart, EnablePageTokenForBody, PagePlaceholders )
    Notice [$tag$] in Body, Header and Filter. This gets replaced at runtime based on some settings we did for pagination (see next 2 screenshots). NetSuite API has different Header, Body and Filter need for First page vs Next Pages so it needs special configuration. [$tag$] in Body gets replacement based on FirstPageBodyPart and NextPageBodyPart properties found on Advanced View under Pagination. [$tag$] used in Filter, Header or any other place gets its replacements from PagePlaceHolders Property found on Advanced View (see filter=firstPartString|nextPartString;header=firstPartString|nextPartString). Use pipe (vertical bar to separate first part placeholder and next part placeholder) In our case we use blank for first part.
    NetSuite ODBC Connection Settings (ZappySys XML Driver)

    NetSuite ODBC Connection Settings (ZappySys XML Driver)

  6. Here is what it looks like on Advanced Tab.
    NetSuite Connection Settings - ODBC XML Driver Advanced View (Pagination, Caching, Logging and other properties not found in Simple Mode)

    ODBC XML Driver – Ádvanced View (Pagination, Caching, Logging and other properties not found in Simple Mode)

  7. Now go to preview tab and click Preview Data button. You will see upto 100 sample records. Using below query it will use all options from UI (No override in query).SELECT * from $
    Preview NetSuite data using ODBC Driver

    Preview NetSuite data (Query Accounts) using ODBC Driver

  8. Now let’s change our default query a bit  to get list of Expense Category (i.e. ExpenseCategorySearch). As you see we change FirstRequestBody a little bit (change q1:ExpenseCategorySearch). We also removed some unwanted columns from preview by setting IncludeParentColumns=’false’
    We have commented SearchQuery portion to make it simple but you can learn more about how to write search query from here

    Here is one more example of query which combines Basic Search and Advanced Search

    Pay close attention to namespace prefix in SOAP code you copy from Help center. It may be using different prefix than our original connection string (e.g. we use q1:xxxxx at some places but online sample may use ns1, ns2, ns3 ….)
     
    NetSuite Search API Example (Mixing BasicSearch / AdvancedSearch with JOIN)

    NetSuite Search API Example (Mixing BasicSearch / AdvancedSearch with JOIN)

  9. Copy your Final Query you like to use to import NetSuite data in Power BI
  10. Click OK to save DSN

 

Import NetSuite data in Power BI

Now lets perform the final step to import our NetSuite Query to Power BI. Assume that we want to import Contacts in Power BI to report them by demographics or some other criteria.

  1. Open Power BI Desktop
  2. Click on Get Data > More Data… > Other > ODBC
    Import SOAP API in Power BI (Using ZappySys XML ODBC Driver)

    Import SOAP API in Power BI (Using ZappySys XML ODBC Driver)

  3. Select your ODBC DSN Source from the dropdown (Assuming you have created a ODBC Data Source using ZappySys XML Driver – See the previous Section)
  4. Expand Advanced Options to enter custom Query.
  5. Enter your SOAP API call SQL query here. For example purpose, we will use below query to get all account using NetSuite Search Web service.
     
    Enter custom SQL to load SOAP Web Service Data in Power BI (ZappySys XML Driver)

    Import NetSuite SOAP Web Service Data in Power BI (ZappySys XML Driver)

  6. Once you see data preview click OK to import.
  7. After data import is done you can edit your dataset (e.g. remove unwanted columns)
  8. You can also edit Source query after data is imported (See below)
    Edit Power BI Data Source SQL after SOAP Web Service Import

    Edit Power BI Data Source SQL after REST / SOAP Web Service Import

  9. That’s it. You can now create your dashboards using NetSuite data. See below example.
    Import NetSuite data in Power BI dashboard

    Import NetSuite data in Power BI dashboard

 

How to debug Web Requests using Fiddler

There will be a time you like to see RAW HTTP Requests generated by driver for NetSuite SOAP API.  If you like to do that check this article.

How to call other NetSuite SOAP API

So far we have seen only search API to query data. But there are many other SOAP APIs which can be consumed using approach listed in this article. Check this article to learn more how to call any SOAP API using ZappySys XML Driver. It shows techniques to use tool called SoapUI. You can use NetSuite WSDL file from here copy link listed on that page and use it to import in SoapUI.

Conclusion

Consuming data from NetSuite API or any XML / SOAP based API in your Reporting / ETL apps can be tricky. But we have seen how to solve this challenging task using ZappySys XML Driver in few clicks. Download ODBC PowerPack and explore many other API integration scenarios not discussed in this article.

More from ZappySys Blog

Posted in Reporting - Microsoft Power BI, XML File / SOAP API Driver and tagged , , , , .