Analytics - Analytics Data Sync Types for PowerBI
When syncing data from NetSuite to Microsoft's platform for use in PowerBI there are several different strategies which can be chosen depending on the needs. These different strategies mainly concern which location data will be sent to which PowerBI is able to connect to and pull data from.
It is important to note that a combination of strategies can be used in different data sets or even across related tables within a single dataset. Larger tables might be better suited for a scheduled data "push" rather than direct web-links.
Direct Web Link Connections
HTTPS web links can be generated using SuperSync Analytics to allow PowerBI to directly pull data from NetSuite when datasets are being refreshed.
This strategy is the fastest and easiest to setup however PowerBI can time-out on web data requests which run longer than a few minutes so this strategy generally works best for syncing record types with under twenty thousand results. This makes this strategy a great fit for lookup tables such as lists of locations, subsidiaries, employees or customers whereas transaction tables exports may require another strategy.
Direct connections via web links can also be a great fit when query or search results are being pre-processed into a smaller result sets in NetSuite using grouping columns reducing the total number of rows being sent to PowerBI overall.
Pros:
- Fastest to setup
- No intermediate files so users only need to work in PowerBI
- Does not use up scheduled script queues on NetSuite
Cons:
- Limited to smaller result sets
- Counts against NetSuite API concurrency
Sharepoint (CSV File)
SuperSync Analytics can be used to push data from NetSuite to Sharepoint in a CSV format. The data result from the NetSuite search or query is converted into a CSV file and uploaded to a location on Microsoft Sharepoint. PowerBI can then retrieve the file contents from the location on Microsoft Sharepoint.
Data source based on saved searches have an upper limit of a million records using this method due to NetSuite limitations on paginated data results but due to the fact that the entire file must be deleted and replaced each time a more realistic limit is a couple hundred thousand rows of data.
Pros:
- Fairly easy to setup
- Intermediate files stored in Sharepoint which is fairly easy for users
- Does not impact NetSuite API concurrency
Cons:
- Whole file must be re-written each time the data is pushed
- Whole file must be written as a single upload, capping the max file size to several megabytes
- Runs as a scheduled script (Map/Reduce Script Type) on NetSuite and takes up a slot in the script queue
Azure Data Blob (CSV File)
Data can also be pushed as a CSV file to Microsoft Azure Data storage accounts. This is similar to pushing data as a CSV file to Sharepoint but there is a little more flexibility around how the data is written and appended to files allowing for no theoretical limit to file sizes being uploaded and also allowing for the combination of multiple sets of search results pushing into the same file to bypass the NetSuite limitation on a million rows of search results.
Pros:
- File size not limited
- Does not impact NetSuite API concurrency
Cons:
- May require admin setups or access to Microsoft Azure platform
- All data must be re-written each time the data is pushed
- Runs as a scheduled script (Map/Reduce Script Type) on NetSuite and takes up a slot in the script queue
Azure Data Tables
The most flexible option is to push data to an Azure Data Store Table on the Microsoft platform. This method is a little more complex to setup and manage but allows for the greatest flexibility in terms of inserting data and managing large data sets.
When Azure Storage Tables are used, a row level key such as a NetSuite internal ID can be selected allowing for update or append operations. This can be especially useful for very large datasets containing historical data as instead of syncing the entire dataset on a regular basis, the search or query can filter for rows which have changed and these can be inserted or updated to the table by matching to to the row level key value.
Pros:
- File size not limited
- Ability to update existing rows thus push only recently changed records
- Does not impact NetSuite API concurrency
Cons:
- May require admin setups or access to Microsoft Azure platform
- Runs as a scheduled script (Map/Reduce Script Type) on NetSuite and takes up a slot in the script queue