Replicate NetSuite to RDBMS to Facilitate Operational Reporting 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 CData Sync, 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 CData Sync and provide a short tutorial for connecting to your replicated 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

CData Sync includes connectivity to a wide range of popular on-premises and SaaS applications targeting CRM, ERP, Marketing Automation, Accounting, Collaboration, and more! The connected sources can be replicated 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 a 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). To connect with Netsuite, navigate to the Connections tab, click Add Connection, and select NetSuite from the Source section. You will need to set User and Password properties to valid NetSuite user credentials. In addition, the AccountId must be set to the Id of a company account that can be used by the specified User. The RoleId can be optionally specified to authenticate the user with limited permissions.

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. To connect to a replication destination, navigate to the Connections tab, click Add Connection, and select a database from the Destination section. Set the relevant connection properties for the replication database.

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. To create a new replication job, navigate to the Jobs tab and click Add Job. Select the configured NetSuite connection for the Source and the replication database connection for the Destination. You can choose to replicate entire tables by clicking Add Tables.

Alternatively, you can create custom queries (click Add Custom Query) to replicate exactly the data you need and even 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 either case, you can initialize the replication by clicking the Run button in the Tables section of the newly configured job.

Flexible Scheduling

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 monthly to as regularly as every 15 minutes.

Incremental Updates

After performing the complete initial replication CData Sync will perform incremental updates to the replication database. CData Sync 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.

Secure, Monitored Backup and Archiving

Since CData Sync 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 CData Sync, 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 CData Sync 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 CData Sync 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 CData Sync today! For more information on CData Sync and to see what other data sources we support, refer to the CData Sync page.