by Jerod Johnson | August 11, 2016

Replicating NetSuite Data for Use in Tableau

As a NetSuite user, you may want to use data visualization tools beyond those offered directly by NetSuite, or you may want to create visualizations that combine your NetSuite data with data from another source. CData Software can help with both situations. With the Sync Application for NetSuite, users are freed from the burden of manually (and repeatedly) exporting their data or writing the integration to export the data and place it in a data store that is readable by most of the prevalent BI tools.

In this article, we will review the features and benefits of the Sync Application and provide a short tutorial for connecting to your NetSuite data from within the popular BI tool Tableau.

Note: While our ODBC Drivers give you a live connection to your data in Tableau, there are situations where replication is a better option (needing a limited subset of the data available, combining data from several sources into a single database, the Web Service for retrieving the data is notoriously slow, etc.).

CData Sync Apps

The CData Sync Apps provide users with easy-to-use solutions for synchronizing data from over 60 on-premises and cloud-based data sources, like QuickBooks, NetSuite, and Salesforce.com, to a variety of on-premises and cloud-based databases, including traditional databases like SQL Server, MySQL, and SQLite and emerging databases like Amazon Redshift, Google Cloud SQL, and PostgreSQL. The data replication that you perform can be used to facilitate data migration, operational reporting, data analytics and decision support in BI tools, data archival for disaster recovery, and much more.

Data Replication in Three Steps

1. Configure the connection to the data source.

Connecting to the data source will require knowledge of the server address, along with the appropriate credentials or, in many cases, authenticating your account directly with the data source via your Web browser (using OAuth). For more information on connecting to NetSuite from our Sync Application, refer to the Online Help.

2. Configure the connection to the replication database.

Connecting to the replication database will require knowing the address of the replication server and the appropriate credentials for write access to the on-premises or cloud-based database. For more information on connecting to the replication database, refer to the Online Help.

3. Select the tables to be replicated and a replication interval and click Run.

Our application works with SQL queries, so each entity, folder, bucket, etc. is treated as a table, replicating the columns and rows to the replication database. For more information on selecting tables and a replication interval, refer to the Online Help.

Incremental Updates

After performing the complete initial replication, the Sync Application will perform incremental updates to the replication database. The Sync Application requests only the changes to the data since the last replication, meaning that there are fewer API calls or queries made against the original data source. This results in fast updates that help you stay below any API or data limits.

Flexible Configuration and Scheduling

You have the freedom to configure the Sync Application to replicate exactly the data you need, even to perform operations on the data before it is replicated. For example, using a query like the following, you can change the name of the replication table and concatenate the fields FirstName and LastName:

REPLICATE REP_Account 
  SELECT  
    DateModified, 
    CONCAT(FirstName," ",LastName) AS FullName 
  FROM Account;

In addition to the full control over what data is replicated, you also have access to flexible replication scheduling, from replicating as infrequently as once daily to as regularly as every 15 minutes.

Secure, Monitored Backup and Archiving

Since the Sync Application is stand-alone, installed software, not SaaS, your data flows directly from the source to the replication database, with no online hosting or transactional fees. You can closely monitor changes being made to the replicated database with the built-in transaction logging, giving you up-to-date knowledge on the state of your data and helping you troubleshoot any replication issues.

NetSuite Data in Tableau

NetSuite offers a robust CRM, ERP, and eCommerce platform for businesses large and small, including some internal reporting and visualization features. The reality of business is that you may want to aggregate your NetSuite data with other data to craft combined visualizations in a third-party application. With the Sync Application, your data can be replicated to an on-premises or cloud-based database, which BI/Reporting tools like Tableau natively support.

Replicating NetSuite Data

To replicate your NetSuite data, you need to configure your connection to NetSuite, your connection to the replication database, and the tables to replicate (see above). It is worth noting that due to the speed limitations of the NetSuite Web Services, your initial replication can take a significant amount of time. Once the initial replication is complete, set a replication interval for all subsequent replication requests. Since the Sync Application performs incremental updates, future replication requests should take significantly less time.

In this article, we replicate our NetSuite data to an on-premises MySQL database, since connections to MySQL databases are natively supported in Tableau.

Connecting to NetSuite Data in Tableau

To connect to your replicated data, simply open Tableau, connect to the replicated database,

and select tables of data you wish to work with.

After selecting the data you wish to work with, you can leverage the power of data visualization in Tableau to create new worksheets, dashboards, and stories to quickly and easily analyze your data and make data-driven decisions.

Since the Sync Application is making scheduled, periodic updates, you are making decisions on data that is as fresh as your most recent update (as often as every 15 minutes) and you are saved from the hassle of creating a new data dump each time your NetSuite data changes or is updated.

More Information & Next Steps

If you are interested in connecting to your NetSuite data (or data from any of our other supported data sources) in your favorite BI/reporting tool, you can download a free, 30-day trial of our Sync Application today! For more information on our Sync Application and to see what other data sources we support, refer to our Sync Application page.