We’re often asked to describe how we pull NetSuite data into Power BI so ZoneReporting can do its work, so in this article we’ll outline the process. While this one is geared toward IT professionals or those with technical backgrounds, it may be of interest to anyone who’s curious about how NetSuite data can be imported into Power BI for reporting and analysis.
Before we dive in, it’s important to know why we handle data access differently than other reporting and analysis solutions. If you’ve ever tried to access NetSuite data through the back end or attempted to set up an open database connectivity (ODBC) connection, you likely know. We were frustrated by the complexity and coding requirements involved, along with the inability to access all the tables necessary to achieve full replication and reconciliation. That’s why we invested so heavily in our own approach that combines two key elements, a data connector and data model, that are:
- Based on the natural language of saved search
- Optimized for performance and
- Far more usable than other data connectors
Together, our data connector and data model work to extract data from NetSuite, transform it into a usable format and load it into the data warehouse – a process that takes as little as 3–4 weeks. Let’s take a closer look at each of these components.
The data connector
Our connector, which we affectionately call “Tactical Connect”, works in the background to extract saved search CSV files from NetSuite for delivery into Microsoft Azure. This extraction process can be automated throughout the day to provide users near real-time data without gaps, silos or blind spots.
“Essentially, our data connector and data model are disassembling the NetSuite house brick-by-brick and reassembling it in Power BI.” – Jim Doyle, Solution Consultant at Zone
In addition, the data we extract is comprehensive, containing 100% of the transaction lines, supporting files, and core formulas needed to construct income statements, balance sheets, and other rich and useful reports. This not only allows users to reconcile NetSuite data down to the penny in Power BI, but it also gives them the ability to access the underlying information and even drill into transactions to investigate issues and pursue deep insights.
Because we are replicating the data line-for-line in Power BI, users are not subject to NetSuite’s single join limitation. They can include as many data sets as they need to construct meaningful reports. And since it’s based on saved searches rather than ODBC queries, the connector is extremely easy to configure and use, even for those with no coding experience.
The data model
Our data model governs how the NetSuite data is organized within Power BI. It first points to the CSV source files that contain the raw NetSuite data provided by our connector. It then reformats the data for consistency, eliminating excess characters and mapping it to the NetSuite tables and fields we’ve replicated within Power BI. The result is a living instance of the NetSuite data in a format that’s easy to understand for users and consistent with NetSuite’s data structure.
Once the data is transformed and loaded, our model defines key relationships between tables and fields. The model also contains measures, which define calculations and complex business logic to provide high value business metrics such as volume & revenue analysis, item profitability, deferred revenue, trended income statements, top ten customer reports, 12-month rolling averages and a wide variety of other important indicators.
Measures are an important capability of our data model, producing critical insights into business health and performance without requiring users to know DAX code or flip through multiple pages of reports and perform manual correlations. Here’s a closer look at two core measures and how they’re constructed.
Together, our data connector and data model provide the engine that brings high quality NetSuite data into Power BI – continuously, consistently and comprehensively. And when we combine NetSuite data with information from other sources such as CRM, inventory, project management and other systems, it gives ZoneReporting the ability to produce business insights that just aren’t available anywhere else.
If you’d like to learn more about how we connect NetSuite and other data to Power BI, or how ZoneReporting can provide your team with deep business reporting and analytics, schedule a personalized demo today.