A short intro from Gil:
Today we have a special guest on DataChant. Meet Charles Webb (LinkedIn here). Charles helps organizations compete and operate more effectively through process optimization, technology modernization, strategic advisory, and analytics. Follow Charles in his blog, Webb Insights.
NetSuite is a suite of cloud services that helps customers manage financials, operations and customer relations – providing integrated ERP, CRM and eCommerce functionality in one tightly knit solution. The ability to connect NetSuite to Power BI is a common, highly requested idea on the Power BI ideas website, collecting literally hundreds of votes.
As such, there is very little information available regarding how to connect NetSuite to Power BI. While you can easily export and visualize static reports or saved search results, there are two more elegant alternative solutions that connect NetSuite to Power BI by automating data refresh.
Option One: Purchase the SuiteAnalytics Connect (ODBC) functionality from NetSuite
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: email@example.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:
Finally, remember to configure the data to be refreshed using the data gateway, and voila. Your data will be updated on the cadence you choose:
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.
Do you know if are size limitations? I’ve tried using this method for a transaction register which can be a large query and have encountered some errors.
Another option! We have a full blown connector that connects NetSuite to PowerBI. Our SuiteApp is the most popular and powerful way to connect! No ODBC required, all through REST. http://www.tacticalconnect.com
Hi Charles, I’m having the same question as Chris. Does the report Web Query option have any limitations? We are having a “The Web.Page function requires Active Scripting to be enabled in Internet Explorer options. See https://go.microsoft.com/fwlink/?LinkId=506565 for details on how to enable Active Scripting.” error with some report when they seem to be large. Any thoughts?
Hi @Charles, how would those modifications in the editor vary for the iqy file from a SharePoint List? I’ll play around… although I get an access to resource is forbidden error when attempting to connect so far.