NetSuite Power BI Connection | Comprehensive Step-by-Step Overview

As a consultancy for Power BI, we have developed numerous NetSuite Power BI dashboards for clients. Each client we collaborated with utilized a different approach to connect NetSuite with Power BI: through a web query, ODBC, or third-party connectors. All these methods were pre-built integrations.

While you can establish a completely custom integration using the NetSuite Rest API, this method is highly technical and won't be covered here.

In this article, we will explore the various ways to connect NetSuite with Power BI, helping you determine the most suitable option for your organization. Below is a comparison table for the three methods we will discuss. We will outline the steps to implement each of these connection options throughout the article.

What is NetSuite?

NetSuite is a robust, cloud-based Enterprise Resource Planning (ERP) solution created by Oracle. It offers a range of applications that assist businesses in managing essential functions, including:

• Financial Management: Streamlining financial operations.

• Customer Relationship Management (CRM): Improving client interactions and sales.

• E-commerce: Linking online sales channels.

• Inventory Management: Effectively managing stock levels and procurement.

NetSuite is celebrated for its adaptability and scalability, making it an ideal choice for growing businesses seeking seamless operations.

What is Power BI?

Power BI is a business analytics tool from Microsoft that provides interactive visual representations and intelligence features with a user-friendly interface enabling users to create their reports and dashboards. Notable functionalities include:

• Data Visualization: Crafting interactive graphs and charts.

• Data Integration: Connecting to multiple data sources.

• Insights and Analysis: Utilizing advanced analytical tools for valuable data insights.

Power BI is recognized for its intuitive interface and powerful data processing features, essential for making data-driven decisions. For an in-depth understanding, consider looking at the comprehensive Power BI Guide for clear explanations.

Can you integrate NetSuite with Power BI for free?

Yes, you can connect NetSuite with Power BI at no cost using the Web Query Report method. This integration does not require costly third-party tools or extra paid features for setup. A significant advantage is that this approach consolidates all necessary data into a single report, while other methods require establishing relationships between tables to build Power BI dashboards.

Screenshots from a great article by Charles Webb will help visualize the step-by-step process for integrating NetSuite with Power BI.

Step 1: Create a Saved Search in NetSuite

• Log in to NetSuite: Begin by accessing your NetSuite account.

• Navigate to Saved Searches: Head to Reports > Saved Searches > All Saved Searches.

• Create New Search: Click on New to initiate a saved search based on your required data. Customize the search to incorporate necessary fields and filters.

Refer to the NetSuite documentation for guidance on creating saved searches.

Step 2: Enable Web Query for the Saved Search

• Edit Search Settings: After your saved search has been created, click the "Customize" button.

• More Options: In the search settings, select More Options.

Step 3: Copy the Web Query URL

Once you save the report and apply the changes, a new icon will appear. Clicking this icon will generate the .iqy file containing the URL needed to connect NetSuite with Power BI.

After downloading the .iqy file, right-click on it -> select Open with -> Edit with Notepad.

The following text will be visible in the file:

Replace [“Please enter your email address:”] with the email address you use to access NetSuite. The brackets can be omitted.

Next, remove the extra text so your final URL resembles the following format:

Remember to copy the generated URL. This URL will be crucial for fetching data into Power BI.

Step 4: Import Data into Power BI

• Open Power BI: Start Power BI Desktop.

• Get Data: Navigate to Home > Get Data > Web.

• Paste URL: Insert the copied Web Query URL from NetSuite into the URL field.

• Connect: Click OK to retrieve the data. Power BI will fetch the data from NetSuite and display it in tabular format.

Note: This integration requires a data gateway to enable automatic data refresh in the Power BI service.

Can you develop this integration using ODBC?

You can link NetSuite with Power BI using the ODBC connector, though this method tends to be more costly at around $499 per month. The ODBC connection is made possible through the SuiteAnalytics Connect add-on offered by Oracle.

This add-on provides real-time, out-of-the-box reporting accessible within NetSuite. You can then customize dashboards in SuiteAnalytics or export data to Power BI via ODBC.

We referenced a video from DataAnts illustrating the integration of NetSuite with Power BI using SuiteAnalytics, and we will summarize the steps here for convenience.

Step 1: Purchase SuiteAnalytics Connect

• Enable SuiteAnalytics Connect: Purchase and activate SuiteAnalytics Connect from NetSuite.

• Download ODBC Driver: Obtain and install the ODBC driver supplied by SuiteAnalytics Connect.

Step 2: Configure ODBC Data Source

• Set Up ODBC DSN: Utilize the ODBC Data Source Administrator to create a new Data Source Name (DSN) with your NetSuite credentials.

• Enter Credentials: Input the required credentials, including your NetSuite account ID, user details, and role ID.

Step 3: Connect to NetSuite in Power BI

• Open Power BI: Launch Power BI Desktop.

• Get Data: Navigate to Home > Get Data > ODBC.

• Select DSN: Choose the DSN you set up previously.

• Load Data: Preview and import the data into Power BI.

Can you integrate NetSuite with Power BI using third-party connectors?

You can also connect NetSuite with Power BI through third-party connectors, though these methods usually incur licensing or purchase costs. Some additional setup challenges may arise with these connectors. Let’s share a specific case study highlighting these challenges.

We once had to create a sales NetSuite Power BI dashboard analyzing the monthly performance of each sales representative. This client relied on a third-party connector to source the data.

The first obstacle was that the connector imported every available NetSuite table into Power BI, resulting in a hefty list of 200+ tables, complicating our effort to locate the necessary data. The limited documentation about relationships between these tables meant we spent considerable time seeking the required information.

The second challenge stemmed from some tables in the connector being empty, often due to access issues. Third-party connectors depend on the NetSuite Rest API to pull data into Power BI, requiring proper configuration of Rest API access to grant visibility to all resources.

Only a NetSuite admin can provide access to organizational reports for another user. Follow the guide from NetSuite to manage access to the reports necessary for Power BI.

Now, let’s briefly mention several third-party NetSuite Power BI integration options without endorsing any specific one.

1. CData Connect Cloud: Offers a straightforward interface for syncing NetSuite data with Power BI. It supports live connectivity and allows seamless updates for dynamic dashboards.

2. Celigo Integrator.io: Provides a solid integration platform featuring an easy-to-use interface and extensive customization capabilities.

3. Skyvia: A no-code solution designed for non-technical users, supporting two-way data sync between NetSuite and Power BI.

4. Jitterbit: Known for advanced data transformation features, this tool is suited for organizations with complex integration needs.

Although external companies typically provide comprehensive instructions for their connectors, you can follow these general steps to achieve integration:

1. Choose a Connector: Assess your organization's data needs and select the most suitable connector.

2. Authenticate Accounts: Set up the necessary authentication credentials for both NetSuite and Power BI.

3. Define Data Requirements: Decide which data fields or modules in NetSuite you wish to import into Power BI.

4. Configure Data Mapping: Map NetSuite data fields to Power BI’s data model for accurate reporting.

5. Load Data into Power BI: Arrange data syncs to keep dashboards updated with the latest information.

6. Build Dashboards: Utilize Power BI’s tools to craft insightful and actionable visualizations.

Now that you understand how to connect NetSuite with Power BI, you can start your integration process.