{"id":4613,"date":"2018-08-11T17:29:14","date_gmt":"2018-08-11T17:29:14","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4613"},"modified":"2018-10-03T18:30:11","modified_gmt":"2018-10-03T18:30:11","slug":"read-netsuite-data-power-bi","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/","title":{"rendered":"How to read NetSuite data in Power BI"},"content":{"rendered":"

Introduction<\/h2>\n

\"Power<\/a>In our previous blog we discussed how to\u00a0import REST \/ SOAP API data in Power BI<\/a>. Now we will use that knowledge to read NetSuite data in Power BI (NetSuite CRM – SuiteTalk)<\/strong>. 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<\/a>. 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<\/a>,\u00a0Qlik<\/a>, Informatica<\/a>, SSRS<\/a>) or programming language (e.g. C#<\/a>, JAVA<\/a>, Python<\/a>) or SQL Server (Linked Server)<\/a>.<\/p>\n

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.<\/p>\n

So let’s get started. The approach listed in this article can be used to call other SOAP based API such as Salesforce<\/strong><\/a>, Workday<\/strong><\/a>, SAP<\/strong>, Sage etc.<\/p>\n

Prerequisite<\/h2>\n

Before you get started make sure following requirements are met.<\/p>\n

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

    What is NetSuite CRM?<\/span><\/h2>\n

    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.<\/p>\n

    Preparing for NetSuite WebService Call in Power BI<\/span><\/h2>\n

    For NetSuite API call we have to make sure certain settings and obtain Application ID and AccountID. So let’s get started.<\/p>\n

    How to enable NetSuite Web Service Feature<\/h3>\r\nBefore you call any NetSuite SOAP API (i.e.\u00a0SuiteTask Web Service<\/strong>), 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).\r\n\r\nClick this link<\/a>\u00a0to see steps to enable Web Service Feature\r\n
    \r\n\r\n\"Enable<\/a>\r\n

    Enable NetSuite Web Service Feature (SuiteTalk API)<\/p>\r\n\r\n<\/div>\r\n

    Obtain NetSuite Account ID<\/span><\/h3>\r\nNetSuite Account ID is needed by Login API call. So perform following steps if you dont know account id.\r\n
      \r\n \t
    1. Login to your NetSuite Portal<\/li>\r\n \t
    2. Click the\u00a0Setup Tab<\/strong><\/li>\r\n \t
    3. In the drop-down click\u00a0Integration<\/strong>\u00a0>\u00a0\u00a0Web services preferences<\/strong><\/li>\r\n \t
    4. On that page you will find your Account ID (example account ID = TSTDRV1234567<\/strong>\u00a0)<\/li>\r\n<\/ol>\r\n
      \r\n\r\n\"Obtain<\/a>\r\n

      Obtain NetSuite Account ID (Needed for Web Service Login)<\/p>\r\n\r\n<\/div>\r\n

      Obtain NetSuite Application ID<\/span><\/h3>\r\nOnce you have account ID now we need to get Application ID. For that follow these steps.\r\n
        \r\n \t
      1. Login to your NetSuite Portal<\/li>\r\n \t
      2. Click the\u00a0Setup Tab<\/strong><\/li>\r\n \t
      3. In the drop-down click\u00a0Integration<\/strong>\u00a0>\u00a0\u00a0Manage Integrations<\/strong><\/li>\r\n \t
      4. On that page you will find your existing Applications along with Account ID \/ Status (example Application ID = AE2EACFD-1234-1222-1111-12345544555<\/strong>\u00a0)<\/li>\r\n \t
      5. If you don\u2019t have any application created yet then just click on New.<\/li>\r\n<\/ol>\r\n
        \r\n\r\n\"Create<\/a>\r\n

        Create new App \/ Get NetSuite Application ID<\/p>\r\n\r\n<\/div>\r\n

        <\/h3><\/div>\n

        Understanding how NetSuite SOAP API Call works<\/h2>\r\nIn 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.\r\n\r\nThere are two steps involved before you can call any SOAP API in NetSuite.\r\n
          \r\n \t
        1. Call Login method<\/a> and extract\u00a0JSESSIONID from response Cookie (This token expires after some time)<\/li>\r\n \t
        2. Call any other API (Pass obtained JSESSIONID token in Cookie header)<\/li>\r\n<\/ol>\r\n

          NetSuite Login Call<\/h3>\r\nRequest<\/strong>\r\n
          POST https:\/\/webservices.netsuite.com\/services\/NetSuitePort_2017_1\r\nContent-Type: text\/xml;charset=UTF-8\r\nSOAPAction: \"login\"\r\nAccept-Encoding: gzip, deflate\r\n\r\n<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n  <soap:Header>\r\n    <applicationInfo xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <applicationId>xxxxxxxxxxxxxx<\/applicationId>\r\n    <\/applicationInfo>\r\n  <\/soap:Header>\r\n  <soap:Body>\r\n    <login xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <passport>\r\n        <email xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">xxxxxxxxxxxxx<\/email>\r\n        <password xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">xxxxxxxxxxxx<\/password>\r\n        <account xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">TSTxxxxxxxxxxxxxxx<\/account>\r\n        <role internalId=\"3\" xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\" \/>\r\n      <\/passport>\r\n    <\/login>\r\n  <\/soap:Body>\r\n<\/soap:Envelope><\/pre>\r\nResponse<\/strong>\r\n\r\nNotice\u00a0JSESSIONID=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<\/a> option can extract Cookie and reuse it until its expired (1 hour by default).\r\n
          HTTP\/1.1 200 OK\r\nDate: Wed, 03 Oct 2018 16:50:41 GMT\r\nContent-Length: 1676\r\nContent-Type: text\/xml; charset=utf-8\r\nX-N-OperationId: 777274f6-49c6-4883-bcd1-1d70df90d78e\r\nStrict-Transport-Security: max-age=31536000\r\nNS_RTIMER_COMPOSITE: 13633xxxxxxxxx:70617xxxxxxxxxxxxx36F6D:80\r\nSet-Cookie: JSESSIONID=6xI61rdFt26pxxxxxxxxxxxxxxxqvJIMlu!-17981123445; path=\/; secure; HttpOnly\r\nSet-Cookie: NS_VER=2018.1.0; domain=webservices.netsuite.com; path=\/\r\nSet-Cookie: NS_ROUTING_VERSION=LAGGING; path=\/\r\nP3P: CP=\"CAO PSAa OUR BUS PUR\"\r\nVary: User-Agent\r\nKeep-Alive: timeout=10, max=990\r\nConnection: Keep-Alive\r\n\r\n<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<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\">\r\n  <soapenv:Header>\r\n    <platformMsgs:documentInfo xmlns:platformMsgs=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <platformMsgs:nsId>WEBSERVICES_xxxxxxxxxxxxxxxxx_9fc719<\/platformMsgs:nsId>\r\n    <\/platformMsgs:documentInfo>\r\n  <\/soapenv:Header>\r\n  <soapenv:Body>\r\n    <loginResponse xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <sessionResponse xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n        <ns1:status isSuccess=\"true\" xmlns:ns1=\"urn:core_2017_1.platform.webservices.netsuite.com\"\/>\r\n        <ns2:wsRoleList xmlns:ns2=\"urn:core_2017_1.platform.webservices.netsuite.com\">\r\n          <ns2:wsRole>\r\n            <ns2:role internalId=\"3\">\r\n              <ns2:name>Administrator<\/ns2:name>\r\n            <\/ns2:role>\r\n            <ns2:isDefault>false<\/ns2:isDefault>\r\n            <ns2:isInactive>false<\/ns2:isInactive>\r\n          <\/ns2:wsRole>\r\n          <ns2:wsRole>\r\n            <ns2:role internalId=\"15\">\r\n              <ns2:name>Employee Center<\/ns2:name>\r\n            <\/ns2:role>\r\n            <ns2:isDefault>false<\/ns2:isDefault>\r\n            <ns2:isInactive>false<\/ns2:isInactive>\r\n          <\/ns2:wsRole>\r\n      <\/sessionResponse>\r\n    <\/loginResponse>\r\n  <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\r\n \r\n
          NetSuite API Call ( getAll method )<\/h3>\r\nNow once you have JSESSIONID extracted you can pass it to call next API call as below. For example here is how to call getAll<\/strong><\/a> method to obtain currency list and exchange rates. If you are not sure how to craft correct Request XML then use tool like SoapUI<\/a>.\r\n\r\nYou can use NetSuite WSDL file\u00a0from here<\/a>\u00a0and import it in\u00a0SoapUI<\/a>.\r\n\r\n\"\"<\/a>\r\n\r\n \r\n\r\nRequest<\/strong>\r\n\r\nSee how Cookie is passed for authentication purpose. Again you can pass this manually or use HTTP connection with Dynamic Token<\/a> to pass it automatically along with each API call.\r\n
          POST https:\/\/webservices.netsuite.com\/services\/NetSuitePort_2017_1\r\nContent-Type: text\/xml;charset=UTF-8\r\nSOAPAction: \"getAll\"\r\nCookie: JSESSIONID=6xI61rxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxMlu!-1798141234\r\nAccept-Encoding: gzip, deflate\r\n\r\n<soapenv:Envelope \r\n   xmlns:soapenv=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\" \r\n   xmlns:urn=\"urn:messages_2018_1.platform.webservices.netsuite.com\" \r\n   xmlns:urn1=\"urn:core_2018_1.platform.webservices.netsuite.com\">\r\n   <soapenv:Body>\r\n      <urn:getAll>\r\n         <record recordType=\"currency\"\/> \r\n      <\/urn:getAll>\r\n   <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\r\nResponse<\/strong>\r\n
          HTTP\/1.1 200 OK\r\nDate: Wed, 03 Oct 2018 16:50:42 GMT\r\nContent-Length: 9648\r\nContent-Type: text\/xml; charset=utf-8\r\nX-N-OperationId: 1b2989e4-b930-4a72-b471-00a8e131dfd4\r\nNS_RTIMER_COMPOSITE: 21403xxxxxx:706172746E65xxxxxxxxxxxx6F6D:80\r\nStrict-Transport-Security: max-age=31536000\r\nP3P: CP=\"CAO PSAa OUR BUS PUR\"\r\nVary: User-Agent\r\n\r\n<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<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\">\r\n  <soapenv:Header>\r\n    <platformMsgs:documentInfo xmlns:platformMsgs=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <platformMsgs:nsId>WEBSERVICES_TSTDRV1439151_100320189871507121000128715_6ae8e8<\/platformMsgs:nsId>\r\n    <\/platformMsgs:documentInfo>\r\n  <\/soapenv:Header>\r\n  <soapenv:Body>\r\n    <getAllResponse xmlns=\"urn:messages_2018_1.platform.webservices.netsuite.com\">\r\n      <platformCore:getAllResult xmlns:platformCore=\"urn:core_2017_1.platform.webservices.netsuite.com\">\r\n        <platformCore:status isSuccess=\"true\" \/>\r\n        <platformCore:totalRecords>11<\/platformCore:totalRecords>\r\n        <platformCore:recordList>\r\n          <platformCore:record internalId=\"1\" xsi:type=\"listAcct:Currency\" xmlns:listAcct=\"urn:accounting_2017_1.lists.webservices.netsuite.com\">\r\n            <listAcct:name>USD<\/listAcct:name>\r\n            <listAcct:symbol>USD<\/listAcct:symbol>\r\n            <listAcct:isBaseCurrency>true<\/listAcct:isBaseCurrency>\r\n            <listAcct:isInactive>false<\/listAcct:isInactive>\r\n            <listAcct:overrideCurrencyFormat>false<\/listAcct:overrideCurrencyFormat>\r\n            <listAcct:displaySymbol>$<\/listAcct:displaySymbol>\r\n            <listAcct:symbolPlacement>_beforeNumber<\/listAcct:symbolPlacement>\r\n            <listAcct:locale>_unitedStatesEnglish<\/listAcct:locale>\r\n            <listAcct:formatSample>$1,234.56<\/listAcct:formatSample>\r\n            <listAcct:exchangeRate>1.0<\/listAcct:exchangeRate>\r\n            <listAcct:fxRateUpdateTimezone>_americaNewYork<\/listAcct:fxRateUpdateTimezone>\r\n            <listAcct:currencyPrecision>_two<\/listAcct:currencyPrecision>\r\n          <\/platformCore:record>\r\n          <platformCore:record internalId=\"2\" xsi:type=\"listAcct:Currency\" xmlns:listAcct=\"urn:accounting_2017_1.lists.webservices.netsuite.com\">\r\n            <listAcct:name>GBP<\/listAcct:name>\r\n            <listAcct:symbol>GBP<\/listAcct:symbol>\r\n            <listAcct:isBaseCurrency>true<\/listAcct:isBaseCurrency>\r\n            <listAcct:isInactive>false<\/listAcct:isInactive>\r\n            <listAcct:overrideCurrencyFormat>false<\/listAcct:overrideCurrencyFormat>\r\n            <listAcct:displaySymbol>\u00a3<\/listAcct:displaySymbol>\r\n            <listAcct:symbolPlacement>_beforeNumber<\/listAcct:symbolPlacement>\r\n            <listAcct:locale>_unitedKingdomEnglish<\/listAcct:locale>\r\n            <listAcct:formatSample>\u00a31,234.56<\/listAcct:formatSample>\r\n            <listAcct:exchangeRate>1.33165497<\/listAcct:exchangeRate>\r\n            <listAcct:fxRateUpdateTimezone>_americaNewYork<\/listAcct:fxRateUpdateTimezone>\r\n            <listAcct:currencyPrecision>_two<\/listAcct:currencyPrecision>\r\n          <\/platformCore:record>\r\n          <platformCore:record internalId=\"3\" xsi:type=\"listAcct:Currency\" xmlns:listAcct=\"urn:accounting_2017_1.lists.webservices.netsuite.com\">\r\n            <listAcct:name>CAD<\/listAcct:name>\r\n            <listAcct:symbol>CAD<\/listAcct:symbol>\r\n            <listAcct:isBaseCurrency>true<\/listAcct:isBaseCurrency>\r\n            <listAcct:isInactive>false<\/listAcct:isInactive>\r\n            <listAcct:overrideCurrencyFormat>false<\/listAcct:overrideCurrencyFormat>\r\n            <listAcct:displaySymbol>$<\/listAcct:displaySymbol>\r\n            <listAcct:symbolPlacement>_beforeNumber<\/listAcct:symbolPlacement>\r\n            <listAcct:locale>_canadaEnglish<\/listAcct:locale>\r\n            <listAcct:formatSample>$1,234.56<\/listAcct:formatSample>\r\n            <listAcct:exchangeRate>0.76457273<\/listAcct:exchangeRate>\r\n            <listAcct:fxRateUpdateTimezone>_americaNewYork<\/listAcct:fxRateUpdateTimezone>\r\n            <listAcct:currencyPrecision>_two<\/listAcct:currencyPrecision>\r\n          <\/platformCore:record>\r\n        <\/platformCore:recordList>\r\n      <\/platformCore:getAllResult>\r\n    <\/getAllResponse>\r\n  <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\r\n
          NetSuite Search API (Pagination Example)<\/h3>\r\nNow 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.\r\n\r\nRequest (Page-1)<\/strong>\r\n
          POST https:\/\/webservices.netsuite.com\/services\/NetSuitePort_2017_1\r\nContent-Type: text\/xml; charset=utf-8\r\nSOAPAction: \"search\"\r\nCookie: JSESSIONID=BKQ7xxxxxxxxxxxx4Ug!-995xxxxxx\r\nAccept-Encoding: gzip, deflate\r\n\r\n<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n  <soap:Header>\r\n    <applicationInfo xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <applicationId>xxxxxxxxxxxxxxx<\/applicationId>\r\n    <\/applicationInfo>\r\n    <searchPreferences xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <pageSize>50<\/pageSize>\r\n    <\/searchPreferences>\r\n  <\/soap:Header>\r\n  <soap:Body>\r\n    <search xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <searchRecord xmlns:q1=\"urn:accounting_2017_1.lists.webservices.netsuite.com\" xsi:type=\"q1:AccountSearch\">\r\n        <q1:basic>\r\n          <description operator=\"doesNotContain\" xmlns=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n            <searchValue xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">x<\/searchValue>\r\n          <\/description>\r\n          <name operator=\"contains\" xmlns=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n            <searchValue xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">a<\/searchValue>\r\n          <\/name>\r\n        <\/q1:basic>\r\n      <\/searchRecord>\r\n    <\/search>\r\n  <\/soap:Body>\r\n<\/soap:Envelope><\/pre>\r\n \r\n\r\nResponse (Page-1)<\/strong>\r\n\r\nIn 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.\r\n\r\nplatformCore:searchId<\/strong> = WEBSERVICES_ABCD_12345-xxxxxxxxx\r\nplatformCore:totalPages<\/strong> = 4\r\n
          HTTP\/1.1 200 OK\r\nDate: Wed, 03 Oct 2018 18:40:33 GMT\r\nContent-Length: 49266\r\nContent-Type: text\/xml; charset=utf-8\r\nX-N-OperationId: c1e79336-63b7-4eb7-989e-5c3d24c56891\r\nNS_RTIMER_COMPOSITE: 118xxxxxxxx:70617xxxxxxxxxxxxxxxxxxxxxD:80\r\nStrict-Transport-Security: max-age=31536000\r\nP3P: CP=\"CAO PSAa OUR BUS PUR\"\r\nVary: User-Agent\r\n\r\n<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n  <soapenv:Header>\r\n    <platformMsgs:documentInfo xmlns:platformMsgs=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <platformMsgs:nsId>WEBSERVICES_ABCD_12345-xxxxxxxxx<\/platformMsgs:nsId>\r\n    <\/platformMsgs:documentInfo>\r\n  <\/soapenv:Header>\r\n  <soapenv:Body>\r\n    <searchResponse xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <platformCore:searchResult xmlns:platformCore=\"urn:core_2017_1.platform.webservices.netsuite.com\">\r\n        <platformCore:status isSuccess=\"true\" \/>\r\n        <platformCore:totalRecords>193<\/platformCore:totalRecords>\r\n        <platformCore:pageSize>50<\/platformCore:pageSize>\r\n        <platformCore:totalPages>4<\/platformCore:totalPages>\r\n        <platformCore:pageIndex>1<\/platformCore:pageIndex>\r\n        <platformCore:searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx<\/platformCore:searchId>\r\n        <platformCore:recordList>\r\n          <platformCore:record internalId=\"1\" xsi:type=\"listAcct:Account\" xmlns:listAcct=\"urn:accounting_2017_1.lists.webservices.netsuite.com\">\r\n            <listAcct:acctType>_bank<\/listAcct:acctType>\r\n            <listAcct:acctNumber>1111<\/listAcct:acctNumber>\r\n            <listAcct:acctName>Checking<\/listAcct:acctName>\r\n            <listAcct:includeChildren>false<\/listAcct:includeChildren>\r\n            <listAcct:currency internalId=\"1\">\r\n              <platformCore:name>USD<\/platformCore:name>\r\n            <\/listAcct:currency>\r\n            <listAcct:generalRate>_current<\/listAcct:generalRate>\r\n            <listAcct:parent internalId=\"246\">\r\n              <platformCore:name>xxxxxxxxxxxxxxxxxxx<\/platformCore:name>\r\n            <\/listAcct:parent>\r\n            <listAcct:cashFlowRate>_average<\/listAcct:cashFlowRate>\r\n            <listAcct:description>Checking<\/listAcct:description>\r\n            <listAcct:isInactive>false<\/listAcct:isInactive>\r\n            <listAcct:inventory>false<\/listAcct:inventory>\r\n            <listAcct:eliminate>false<\/listAcct:eliminate>\r\n            <listAcct:revalue>true<\/listAcct:revalue>\r\n          <\/platformCore:record>\r\n          <platformCore:record internalId=\"2\" xsi:type=\"listAcct:Account\" xmlns:listAcct=\"urn:accounting_2017_1.lists.webservices.netsuite.com\">\r\n            <listAcct:acctType>_bank<\/listAcct:acctType>\r\n            <listAcct:acctNumber>22222<\/listAcct:acctNumber>\r\n            <listAcct:acctName>Savings<\/listAcct:acctName>\r\n            <listAcct:includeChildren>false<\/listAcct:includeChildren>\r\n            <listAcct:currency internalId=\"1\">\r\n              <platformCore:name>USD<\/platformCore:name>\r\n            <\/listAcct:currency>\r\n            <listAcct:generalRate>_current<\/listAcct:generalRate>\r\n            <listAcct:parent internalId=\"246\">\r\n              <platformCore:name>yyyyyyyyy<\/platformCore:name>\r\n            <\/listAcct:parent>\r\n            <listAcct:cashFlowRate>_average<\/listAcct:cashFlowRate>\r\n            <listAcct:description>Savings<\/listAcct:description>\r\n            <listAcct:isInactive>false<\/listAcct:isInactive>\r\n            <listAcct:inventory>false<\/listAcct:inventory>\r\n            <listAcct:eliminate>false<\/listAcct:eliminate>\r\n            <listAcct:revalue>true<\/listAcct:revalue>\r\n          <\/platformCore:record>\r\n          .........\r\n          .........\r\n          .........\r\n          .........\t\t  \r\n        <\/platformCore:recordList>\r\n      <\/platformCore:searchResult>\r\n    <\/searchResponse>\r\n  <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\r\n \r\n\r\nRequest (Page-2 and onwards)<\/strong>\r\n\r\nHere is the second page request. Notice few things here. In heraders we are now sending SOAPAction: \"searchMoreWithId\"<\/strong> rather than \"search\"<\/strong>.\u00a0 Also our SOAP Body is also different compared to first page request. Now we only sending pageIndex<\/strong> we want to fetch and searchId<\/strong>. We have also change <search><\/strong> tag with <searchMoreWithId><\/strong> under Body.\r\n
          POST https:\/\/webservices.netsuite.com\/services\/NetSuitePort_2017_1\r\nContent-Type: text\/xml; charset=utf-8\r\nSOAPAction: \"searchMoreWithId\"\r\nCookie: JSESSIONID=BKQ7xxxxxxxxxxxx4Ug!-995xxxxxx\r\nAccept-Encoding: gzip, deflate\r\n\r\n<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n  <soap:Header>\r\n    <applicationInfo xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <applicationId>xxxxxxxxxxxxxxxxxx<\/applicationId>\r\n    <\/applicationInfo>\r\n    <searchPreferences xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <pageSize>50<\/pageSize>\r\n    <\/searchPreferences>\r\n  <\/soap:Header>\r\n  <soap:Body>\r\n    <searchMoreWithId xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx<\/searchId>\r\n      <pageIndex>2<\/pageIndex>\r\n    <\/searchMoreWithId>\r\n  <\/soap:Body>\r\n<\/soap:Envelope><\/pre>\r\nResponse (Page-2 and onwards)<\/strong>\r\n
          HTTP\/1.1 200 OK\r\nDate: Wed, 03 Oct 2018 18:40:33 GMT\r\nContent-Length: 49266\r\nContent-Type: text\/xml; charset=utf-8\r\nX-N-OperationId: c1e79336-63b7-4eb7-989e-5c3d24c56891\r\nNS_RTIMER_COMPOSITE: 118xxxxxxxx:70617xxxxxxxxxxxxxxxxxxxxxD:80\r\nStrict-Transport-Security: max-age=31536000\r\nP3P: CP=\"CAO PSAa OUR BUS PUR\"\r\nVary: User-Agent\r\n\r\n<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n  <soapenv:Header>\r\n    <platformMsgs:documentInfo xmlns:platformMsgs=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <platformMsgs:nsId>WEBSERVICES_ABCD_12345-xxxxxxxxx<\/platformMsgs:nsId>\r\n    <\/platformMsgs:documentInfo>\r\n  <\/soapenv:Header>\r\n  <soapenv:Body>\r\n    <searchMoreWithIdResponse xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <platformCore:searchResult xmlns:platformCore=\"urn:core_2017_1.platform.webservices.netsuite.com\">\r\n        <platformCore:status isSuccess=\"true\" \/>\r\n        <platformCore:totalRecords>193<\/platformCore:totalRecords>\r\n        <platformCore:pageSize>50<\/platformCore:pageSize>\r\n        <platformCore:totalPages>4<\/platformCore:totalPages>\r\n        <platformCore:pageIndex>2<\/platformCore:pageIndex>\r\n        <platformCore:searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx<\/platformCore:searchId>\r\n        <platformCore:recordList>\r\n          <platformCore:record internalId=\"1\" xsi:type=\"listAcct:Account\" xmlns:listAcct=\"urn:accounting_2017_1.lists.webservices.netsuite.com\">\r\n            <listAcct:acctType>_bank<\/listAcct:acctType>\r\n            <listAcct:acctNumber>8000<\/listAcct:acctNumber>\r\n            <listAcct:acctName>Checking<\/listAcct:acctName>\r\n            <listAcct:includeChildren>false<\/listAcct:includeChildren>\r\n            <listAcct:currency internalId=\"1\">\r\n              <platformCore:name>USD<\/platformCore:name>\r\n            <\/listAcct:currency>\r\n            <listAcct:generalRate>_current<\/listAcct:generalRate>\r\n            <listAcct:parent internalId=\"246\">\r\n              <platformCore:name>xxxxxxxxxxxxxxxxxxx<\/platformCore:name>\r\n            <\/listAcct:parent>\r\n            <listAcct:cashFlowRate>_average<\/listAcct:cashFlowRate>\r\n            <listAcct:description>Checking<\/listAcct:description>\r\n            <listAcct:isInactive>false<\/listAcct:isInactive>\r\n            <listAcct:inventory>false<\/listAcct:inventory>\r\n            <listAcct:eliminate>false<\/listAcct:eliminate>\r\n            <listAcct:revalue>true<\/listAcct:revalue>\r\n          <\/platformCore:record>\r\n          <platformCore:record internalId=\"2\" xsi:type=\"listAcct:Account\" xmlns:listAcct=\"urn:accounting_2017_1.lists.webservices.netsuite.com\">\r\n            <listAcct:acctType>_bank<\/listAcct:acctType>\r\n            <listAcct:acctNumber>8001<\/listAcct:acctNumber>\r\n            <listAcct:acctName>Savings<\/listAcct:acctName>\r\n            <listAcct:includeChildren>false<\/listAcct:includeChildren>\r\n            <listAcct:currency internalId=\"1\">\r\n              <platformCore:name>USD<\/platformCore:name>\r\n            <\/listAcct:currency>\r\n            <listAcct:generalRate>_current<\/listAcct:generalRate>\r\n            <listAcct:parent internalId=\"246\">\r\n              <platformCore:name>yyyyyyyyy<\/platformCore:name>\r\n            <\/listAcct:parent>\r\n            <listAcct:cashFlowRate>_average<\/listAcct:cashFlowRate>\r\n            <listAcct:description>Savings<\/listAcct:description>\r\n            <listAcct:isInactive>false<\/listAcct:isInactive>\r\n            <listAcct:inventory>false<\/listAcct:inventory>\r\n            <listAcct:eliminate>false<\/listAcct:eliminate>\r\n            <listAcct:revalue>true<\/listAcct:revalue>\r\n          <\/platformCore:record>\r\n          .........\r\n          .........\r\n          .........\r\n          .........\t\t  \r\n        <\/platformCore:recordList>\r\n      <\/platformCore:searchResult>\r\n    <\/searchMoreWithIdResponse>\r\n  <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\r\n \r\n\r\n <\/div>\n
          Getting Started<\/span><\/h2>\n
          Now let\u2019s check step by step instructions on\u00a0how to load NetSuite data in Power BI<\/strong>. In this exercise we will focus on\u00a0calling search API<\/a>\u00a0but you can call virtually any API using the same technique.<\/p>\n
          For detailed help on NetSuite SOAP API and other useful information check\u00a0this documentation<\/a>. To browse schema you can use\u00a0this link<\/a>. 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\u00a0AccountSearch<\/strong>\u00a0as below example. This name can be found\u00a0from here(click search tab)<\/a>\u00a0You can change it to something else. For now dont worry too much until we get to that point.<\/p>\n
          \n
          \n
          \n
          <\/div>\n<\/div>\n
          \n
          <<\/span>searchRecord <\/span>xmlns<\/span>:<\/span>q1<\/span>=<\/span>\"urn:accounting_2017_1.lists.webservices.netsuite.com\"<\/span> xsi<\/span>:<\/span>type<\/span>=<\/span>\"q1:AccountSearch\"<\/span>><\/span><\/code><\/div>\n<\/div>\n<\/div>\n
          <\/div>\n<\/div>\n
          Creating NetSuite ODBC Connection (Using ZappySys XML \/ SOAP Driver)<\/h3>\n
          Once you have all necessary information handy we can create ODBC DSN for NetSuite<\/strong> which can be used by any ODBC compliant app such as Power BI<\/strong>. Perform the following steps to set NetSuite Connection which we will use to query data (e.g. Accounts, Budget, Campaign etc).<\/p>\n
           <\/p>\n
            \n
          1. Open start menu and search for “ODBC 64”. Launch ODBC Data sources for 64 bit\"Open<\/a><\/li>\n
          2. Click Add<\/strong> and select ZappySys XML Driver<\/strong>\n
          3. Once driver UI is visible change default DSN name (e.g. change to ZS – NetSuite<\/strong>)<\/li>\n
          4. Click on the Load Connection String<\/strong> 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.
            \nReplace\u00a0$$$YOUR-APPLICATION-ID$$$<\/strong>\u00a0 (2 times) with your own Application ID
            \nReplace\u00a0$$$YOUR-ACCOUNT-ID$$$<\/strong>\u00a0with your own Account ID
            \nReplace\u00a0$$$YOUR-USER-ID$$$<\/strong>\u00a0 with your own Login ID (e.g myuser@mycompany.com)
            \nReplace\u00a0$$$YOUR-PASSWORD$$$<\/strong>\u00a0 with your own Password
            \nReplace\u00a0<pageSize>50<\/pageSize><\/strong>\u00a0 with your own value. We suggest not to go more than 200 rows per page for better performance.
            \nReplace\u00a0c:\\temp\\netsuite.cache.db<\/strong> 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.<\/p>\n
            DRIVER={ZappySys XML Driver};ElementsToTreatAsArray='platformCore:record';DataPath='https:\/\/webservices.netsuite.com\/services\/NetSuitePort_2017_1';DataConnectionType=HTTP;AuthScheme='JSESSIONID=';TokenUrl='https:\/\/webservices.netsuite.com\/services\/NetSuitePort_2017_1';TokenRequestData='<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n  <soap:Header>\r\n    <applicationInfo xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <applicationId>$$$YOUR-APPLICATION-ID$$$<\/applicationId>\r\n    <\/applicationInfo>\r\n  <\/soap:Header>\r\n  <soap:Body>\r\n    <login xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <passport>\r\n        <email xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">[$userid$]<\/email>\r\n        <password xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">[$password$]<\/password>\r\n        <account xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">$$$YOUR-ACCOUNT-ID$$$<\/account>\r\n        <role internalId=\"3\" xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\" \/>\r\n      <\/passport>\r\n    <\/login>\r\n  <\/soap:Body>\r\n<\/soap:Envelope>';TokenRequestMethod='POST';TokenRequestContentType=TextXmlUtf8;TokenResponseValueExtractMode=Cookie;TokenRequestHeaders='SOAPAction:\"login\"';TokenResponseCookie='JSESSIONID';TokenAuthHeader='Cookie';UserName='$$$YOUR-USER-ID$$$';CredentialType=TokenDynamic;Password='$$$YOUR-PASSWORD$$$';Filter='$.soapenv:Envelope.soapenv:Body.search[$tag$]Response.platformCore:searchResult.platformCore:recordList.platformCore:record[*]';RequestData='<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n  <soap:Header>\r\n    <applicationInfo xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <applicationId>$$$YOUR-APPLICATION-ID$$$<\/applicationId>\r\n    <\/applicationInfo>\r\n    <searchPreferences xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <pageSize>50<\/pageSize>\r\n    <\/searchPreferences>\r\n  <\/soap:Header>\r\n  <soap:Body>\r\n[$tag$]\r\n  <\/soap:Body>\r\n<\/soap:Envelope>';RequestMethod='POST';NextUrlAttributeOrExpr='$.soapenv:Envelope.soapenv:Body.search[$tag$]Response.platformCore:searchResult.platformCore:searchId';FirstPageBodyPart='    <search xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <searchRecord xmlns:q1=\"urn:accounting_2017_1.lists.webservices.netsuite.com\" xsi:type=\"q1:AccountSearch\">\r\n        <q1:basic>\r\n\t\t<!--\r\n          <description operator=\"doesNotContain\" xmlns=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n            <searchValue xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">x<\/searchValue>\r\n          <\/description>\r\n          <name operator=\"contains\" xmlns=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n            <searchValue xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">a<\/searchValue>\r\n          <\/name>\r\n\t\t --> \r\n        <\/q1:basic>\r\n      <\/searchRecord>\r\n    <\/search>';NextPageBodyPart='    <searchMoreWithId xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <searchId>[$pagetoken$]<\/searchId>\r\n      <pageIndex>[$pagenumber$]<\/pageIndex>\r\n    <\/searchMoreWithId>';PagingMaxPagesExpr='$.soapenv:Envelope.soapenv:Body.searchResponse.platformCore:searchResult.platformCore:totalPages';HasDifferentNextPageInfo=True;EnablePageTokenForBody=True;PagePlaceholders='header=|MoreWithId;filter=|MoreWithId';RequestHeaders='Content-Type: text\/xml; charset=utf-8 || SOAPAction: \"search[$tag$]\"';CachingMode=All;CacheStorage=File;CacheEntryTtl=120;CacheFileLocation='c:\\temp\\netsuite.cache.db'<\/pre>\n
             <\/p>\n
            \"ZappySys<\/a>
            ZappySys ODBC Driver Load Connection String – NetSuite Connection Example<\/p><\/div><\/li>\n
          5. After configuration your screen may look like below. You can also click on Advanced View<\/strong>\u00a0(Radio button) to see some Advanced properties (e.g. Caching Properties such as CachingMode<\/strong>, CacheFileLocation<\/strong>\u00a0and Advanced Pagination Properties such as PagingMaxPagesExpr<\/strong>,\u00a0HasDifferentNextPageInfo<\/strong>, NextPageBodyPart<\/strong>, FirstPageBodyPart,\u00a0EnablePageTokenForBody, PagePlaceholders<\/strong> )
            \n
            Notice [$tag$]<\/strong> 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<\/strong> and NextPageBodyPart<\/strong> properties found on Advanced View under Pagination. [$tag$]<\/strong> used in Filter, Header or any other place gets its replacements from PagePlaceHolders<\/strong> 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.<\/div><\/div>\n
          6. Here is what it looks like on Advanced Tab.\n
          7. Now go to preview tab and click Preview Data<\/strong> button. You will see upto 100 sample records. Using below query it will use all options from UI (No override in query).SELECT * from $<\/strong>\n
          8. Now let’s change our default query a bit\u00a0 to get list of Expense Category (i.e. ExpenseCategorySearch<\/a>). As you see we change FirstRequestBody a little bit (change\u00a0q1:ExpenseCategorySearch<\/strong>). We also removed some unwanted columns from preview by setting IncludeParentColumns=’false’<\/strong>\n
            SELECT * FROM $\r\nWITH(\r\n\tFirstPageBodyPart='    <search xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <searchRecord xmlns:q1=\"urn:accounting_2017_1.lists.webservices.netsuite.com\" xsi:type=\"q1:ExpenseCategorySearch\">\r\n        <q1:basic>\r\n          <description operator=\"doesNotContain\" xmlns=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n            <searchValue xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">Some-String-Goes-Here<\/searchValue>\r\n          <\/description>\r\n          <name operator=\"contains\" xmlns=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n            <searchValue xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">Some-String-Goes-Here<\/searchValue>\r\n          <\/name>\r\n        <\/q1:basic>\r\n      <\/searchRecord>\r\n    <\/search>',\r\n    IncludeParentColumns='False'\r\n)<\/pre>\n
            We have commented SearchQuery portion to make it simple but you can learn more about how to write search query from here<\/p>\n
            Here is one more example of query which combines Basic Search<\/a> and Advanced Search<\/a><\/p>\n
            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 ….)<\/div><\/div>\n
            SELECT * FROM $\r\nWITH(\r\n\tFirstPageBodyPart='\r\n    <search xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <searchRecord xsi:type=\"ns4:ContactSearch\" xmlns:ns4=\"urn:relationships_2017_1.lists.webservices.netsuite.com\">\r\n        <ns4:basic xsi:type=\"ns5:ContactSearchBasic\" xmlns:ns5=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n          <ns5:email operator=\"contains\" xsi:type=\"ns6:SearchStringField\" xmlns:ns6=\"urn:core_2017_1.platform.webservices.netsuite.com\">\r\n            <ns6:searchValue xsi:type=\"xsd:string\">.com<\/ns6:searchValue>\r\n          <\/ns5:email>\r\n        <\/ns4:basic>\r\n        <ns4:customerJoin xsi:type=\"ns7:CustomerSearchBasic\" xmlns:ns7=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n          <ns7:entityId operator=\"contains\" xsi:type=\"ns8:SearchStringField\" xmlns:ns8=\"urn:core_2017_1.platform.webservices.netsuite.com\">\r\n            <ns8:searchValue xsi:type=\"xsd:string\">C<\/ns8:searchValue>\r\n          <\/ns7:entityId>\r\n        <\/ns4:customerJoin>\r\n      <\/searchRecord>\r\n    <\/search>',\r\n    IncludeParentColumns='False'\r\n)<\/pre>\n
             <\/p>\n
            \"NetSuite<\/a>
            NetSuite Search API Example (Mixing BasicSearch \/ AdvancedSearch with JOIN)<\/p><\/div><\/li>\n
          9. Copy your Final Query you like to use to import NetSuite data in Power BI<\/li>\n
          10. Click OK to save DSN<\/li>\n<\/ol>\n
             <\/p>\n
            Import NetSuite data in Power BI<\/h3>\n
            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.<\/p>\n
              \n
            1. Open Power BI Desktop<\/li>\n
            2. Click on Get Data<\/strong> > More Data…<\/strong> >\u00a0Other<\/strong> > ODBC<\/strong>\n
            3. Select your ODBC DSN Source<\/strong> from the dropdown (Assuming you have created a ODBC Data Source using ZappySys XML Driver \u2013 See the previous Section)<\/li>\n
            4. Expand Advanced Options<\/strong> to enter custom Query.<\/li>\n
            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.\n
              SELECT * FROM $\r\nWITH(\r\n\tFirstPageBodyPart='    <search xmlns=\"urn:messages_2017_1.platform.webservices.netsuite.com\">\r\n      <searchRecord xmlns:q1=\"urn:accounting_2017_1.lists.webservices.netsuite.com\" xsi:type=\"q1:AccountSearch\">\r\n        <q1:basic>\r\n        <!--\r\n          <description operator=\"doesNotContain\" xmlns=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n            <searchValue xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">x<\/searchValue>\r\n          <\/description>\r\n          <name operator=\"contains\" xmlns=\"urn:common_2017_1.platform.webservices.netsuite.com\">\r\n            <searchValue xmlns=\"urn:core_2017_1.platform.webservices.netsuite.com\">a<\/searchValue>\r\n          <\/name>\r\n          -->\r\n        <\/q1:basic>\r\n      <\/searchRecord>\r\n    <\/search>',\r\n    IncludeParentColumns='False'\r\n)<\/pre>\n
               <\/p>\n
              \"Enter<\/a>
              Import NetSuite SOAP Web Service Data in Power BI (ZappySys XML Driver)<\/p><\/div><\/li>\n
            6. Once you see data preview click OK to import.<\/li>\n
            7. After data import is done you can edit your dataset (e.g. remove unwanted columns)<\/li>\n
            8. You can also edit Source query after data is imported (See below)\n
            9. That’s it. You can now create your dashboards using NetSuite data. See below example.\n
              \"Import<\/a>
              Import NetSuite data in Power BI dashboard<\/p><\/div><\/li>\n<\/ol>\n
               <\/p>\n
              How to debug Web Requests using Fiddler<\/h2>\n
              There will be a time you like to see RAW HTTP Requests generated by driver for NetSuite SOAP API.\u00a0 If you like to do that check this article<\/a>.<\/p>\n
              How to call other NetSuite SOAP API<\/h2>\n
              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<\/a> 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\u00a0from here<\/a> copy link listed on that page and use it to import in SoapUI.<\/p>\n
              Conclusion<\/h2>\n
              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<\/a> and explore many other API integration scenarios not discussed in this article.<\/p>\n","protected":false},"excerpt":{"rendered":"
              Introduction In our previous blog we discussed how to\u00a0import 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 […]<\/p>\n","protected":false},"author":1,"featured_media":3951,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[335,278],"tags":[366,298,67,113,7],"yoast_head":"\r\nHow to read NetSuite data in Power BI | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to read NetSuite data in Power BI. Import SuiteTalk Web Service data using ZappySys XML Driver (Call SOAP Web Service). Call Search API and Load.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to read NetSuite data in Power BI | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to read NetSuite data in Power BI. Import SuiteTalk Web Service data using ZappySys XML Driver (Call SOAP Web Service). Call Search API and Load.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2018-08-11T17:29:14+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2018-10-03T18:30:11+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"310\" \/>\r\n\t<meta property=\"og:image:height\" content=\"310\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/\",\"url\":\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/\",\"name\":\"How to read NetSuite data in Power BI | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png\",\"datePublished\":\"2018-08-11T17:29:14+00:00\",\"dateModified\":\"2018-10-03T18:30:11+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to read NetSuite data in Power BI. Import SuiteTalk Web Service data using ZappySys XML Driver (Call SOAP Web Service). Call Search API and Load.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png\",\"width\":310,\"height\":310,\"caption\":\"Power BI Integration\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to read NetSuite data in Power BI\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to read NetSuite data in Power BI | ZappySys Blog","description":"Learn how to read NetSuite data in Power BI. Import SuiteTalk Web Service data using ZappySys XML Driver (Call SOAP Web Service). Call Search API and Load.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/","og_locale":"en_US","og_type":"article","og_title":"How to read NetSuite data in Power BI | ZappySys Blog","og_description":"Learn how to read NetSuite data in Power BI. Import SuiteTalk Web Service data using ZappySys XML Driver (Call SOAP Web Service). Call Search API and Load.","og_url":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2018-08-11T17:29:14+00:00","article_modified_time":"2018-10-03T18:30:11+00:00","og_image":[{"width":310,"height":310,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/","url":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/","name":"How to read NetSuite data in Power BI | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png","datePublished":"2018-08-11T17:29:14+00:00","dateModified":"2018-10-03T18:30:11+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to read NetSuite data in Power BI. Import SuiteTalk Web Service data using ZappySys XML Driver (Call SOAP Web Service). Call Search API and Load.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png","width":310,"height":310,"caption":"Power BI Integration"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/read-netsuite-data-power-bi\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to read NetSuite data in Power BI"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/4613"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=4613"}],"version-history":[{"count":16,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/4613\/revisions"}],"predecessor-version":[{"id":5105,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/4613\/revisions\/5105"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3951"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=4613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=4613"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=4613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}