Connect NetSuite to Power BI

You’ll want to follow the documentation from NetSuite, download the driver and then configure your ODBC connection according to your system information and credentials.

Next, you’ll need to connect NetSuite to Power BI via the ODBC connector, write Oracle SQL statements against the driver, retrieve and model the data. If this sounds a bit daunting, it is – you should leverage an expert who can help you understand and create an adequate semantic model according to your data and visualization requirements. Often, this is a technically skilled resource with expertise in NetSuite who will help you write and fine tune queries to pull the data you need and model it appropriately (transactions, lookup tables and so on) to optimize your data for detailed analysis, filtering and drill down capabilities.

Remember – flat tables with blanket select statements are NOT best practice. Instead, choose specific columns, leverage lookup tables and perform as much ETL work as you can closest to the source.

Following this work you’ll of course need to visualize your data. I recommend following best practices outlined in Storytelling with Data.

Automating the refresh of your data requires two things, a Power BI Pro subscription and the On-Premise Data Gateway.  Luckily, ODBC is one of the supported data sources for scheduled refresh. When you’re ready to publish, you’ll want to configure your on-premises data gateway to refresh data using your ODBC connection. Finally, setup the desired refresh cadence and voila – your reports are ready for showtime.

Option Two: Leverage NetSuite’s Web Query report functionality

If following the previous method sounds difficult, again, it can be. However as the title above for option two may suggest, there is an easier way to get report data from NetSuite, visualize it and share it with teams. Note however, that this method can be only used for reports. This is a great option of you want to blend report data with information outside of NetSuite, utilize advanced analytics or present data to people without access to NetSuite. In the tutorial below, I’ll walk you through how to connect to Power BI desktop and show you a potential end result.

First, you’ll want to enable the web query functionality on a given report. You’ll do this as shown below:

Once the Customize screen loads, navigate to the More Options button as depicted below:

After you’ve navigated to the More Options tab, check the box allowing Web Query access:

Now that you’ve checked the box, rename and make sure to save the report:

When you click the save button, NetSuite will render the new report. If you scroll down to the bottom of the page, you’ll notice a new icon. Click on this icon, illustrated below:

Selecting this icon should generate a .iqy file:

Clicking on this link directly will open up Excel where we will be prompted for credentials (email). However, what we want to do is prepare this source so that we can effectively connect NetSuite to Power BI. To do this, we will need to save the file in a location so that we can open it with text editor. Right click or select the carrot to do so:

Once the file location opens, right click the .iqy file to utilize the “Open With” command, and use a qualified text editor

Below I’ve colored text you’ll want to delete in red, and replace in bold:

WEB1 https://system.na1.netsuite.com/app/reporting/webquery.nl?compid=XXXXXXXXXXXXXX&entity=XXXXXX&email= [“emailaddress”,”Please enter your email address:”] &role=X&cr=XXX&hash=XXX-XXX-ETC= Selection=EntirePage Formatting=All PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False

To summarize what we are doing here, the file exported by NetSuite contains a URL. You’re replacing the text,

[“emailaddress”,”Please enter your email address:”]

with just your email address in the format: name@netsuitelogindomain.com – no brackets necessary.

To test to see if this is successful, copy and paste the text in a browser. It should return a table rendered in HTML format. This is exactly what Power BI was made to ingest. Save the URL in a handy place for use later in this tutorial.

Now, we are ready to connect NetSuite to Power BI and view this report. To do this, if you have not already done so, download Power BI Desktop.

Next, open Power BI Desktop and click on the “Get Data” icon:

Type in “Web” and select the appropriate Web source to import data from a web page:

When prompted, enter the URL saved earlier into the dialog box, then select “OK”:

You’ll be presented with the data navigator where you should select “Table 0”:

Selecting this table should render a data preview to the right of your selection:

Ensure that the data rendered matches the data you want to import into Power BI Desktop. If so, congratulations – you have managed to connect NetSuite to Power BI.

Now, click the Edit button to make a few quick transformations to the data. The Query Editor in Power BI Desktop, shown below, allows you perform several data preparation tasks without code.

These include, but are not limited to:

– Removing the = signs in front of decimal or integer (currency or quantity fields)

– Mashup data with other internal metrics or key performance indicators

– Combine data from additional sources (Salesforce, Google Analytics, Excel, Marketo)

As you prepare and cleanse your data, consider augmenting your data with industry benchmarks and advanced analytics. Strong use-cases and solid execution in these two regards can bring immense value and new insights to your investments in NetSuite and Power BI. Once you’ve completed your data prep and modelling tasks, it’s time to visualize your data and publish your data:

To summarize, two effective ways to connect NetSuite to Power BI are:

1. Utilize the ODBC functionality within NetSuite

2. Utilize the Web Query functionality within NetSuite

Both will utilize the On-Premise Data Gateway.

Connecting NetSuite to Power BI is a great starting place for data-driven storytelling and analysis. Further your analysis journey by coupling visualizations with Power BI features such as Export to PowerPoint, Email Subscriptions and Alerts to create additional value for both technology assets.