NetSuite SuiteAnalytics
NetSuite provides SuiteAnalytics as a paid option, giving businesses the power of built-in real-time dashboards, reporting and analysis—integrated right into the ERP, CRM or ecommerce application that you use every day.
Features
| Feature Name | Supported | Notes |
|---|---|---|
| Capture Deletes | check | |
| Custom Data | check | |
| Data Blocking | check | Column level and table level |
| Column Hashing | check | |
| Re-sync | check | Table level. If you re-sync a parent table, its child tables will resync as well. |
| History | ||
| API Configurable | check | |
| Priority-first sync |
Setup guide
Follow our step-by-step NetSuite SuiteAnalytics setup guide to connect NetSuite SuiteAnalytics with your destination using Fivetran connectors.
Schema information
NetSuite SuiteAnalytics' schema follows Fivetran's standard API schema rules.
Fivetran currently supports all tables in version 2020.1 as well as any custom tables present in your NetSuite account. See NetSuite's Connect Browser documentation for information on all the available tables, and the domain diagrams illustrating how tables relate to one another.
To zoom, open ERD in new window
Analytics templates
Get Fivetran-created NetSuite SuiteAnalytics analytics templates on GitHub. Our custom-built analytics templates consist of a set of SQL queries, a Looker block, and pre-built Tableau workbooks. You can use these templates as-is or customize them to meet your needs.
You can use the SQL queries as-is if you use BigQuery or Snowflake as your destination. If you use a different destination, you need to tweak the syntax slightly to get the queries to run.
The Looker Block consists of a set of view files, a model file, and a set of dashboards. Read our blog posts to learn how to use our Looker template.
The Tableau workbook consists of the SQL queries and pre-built workbooks. Read our blog posts to learn how to use our Tableau template.
Initial sync
When you first connect a new account, Fivetran scans every table you selected and copies every row for which we have permission to view. We import each table in full before moving onto the next table. Depending on the number and size of tables to be imported, the initial sync can take a while.
Updating data
Fivetran uses one of the following strategies to update the tables that you included in your sync:
Incremental
Tables which have a date_last_modified, or date_last_modified_gmt, or last_modified_date column are updated incrementally.
System_notes and System_notes_custom
System_notes and System_notes_custom are audit tables that should be immutable (unchanged). New records are synced incrementally using the date_created column. Updates or deletes are not captured.
Re-import
Tables without one of the timestamp columns must be re-imported in full to update. To maintain the speed up overall updates, the frequency with which we update these re-import tables is based on how fast we can ingest them from NetSuite. This speed is entirely dependent on NetSuite performance.
| Table Import Duration | Import Frequency |
|---|---|
| 20 seconds or less | Every sync |
| Between 20 seconds and 60 minutes | Twice a day |
| More than 60 minutes | Once a week |
We mark the tables that take longer than 20 seconds to import as not recommended in the dashboard and include
a message on how frequently Fivetran syncs them. They are excluded from the sync by default because they degrade performance. However, you can choose to include them.
| Table Name |
|---|
| Account_subsidiary_map |
| Amortization_sched_lines |
| Amortization_schedules |
| Billing_class_rates |
| Billing_classes |
| Billing_rate_cards_prices |
| Billing_schedule |
| Billing_schedule_descriptions |
| Bill_of_materials |
| Bins |
| Bin_number |
| Bin_number_counts |
| Bom_revision_components |
| Bom_revisions |
| Budget |
| Budget_category |
| Caseescalationhistory |
| Case_stage_changes |
| Commissionschedule |
| Campaignevent |
| Campaignitem |
| Campaignresponse |
| Campaignresponsehistory |
| Commission_authorization_link |
| Commissionrate |
| Competitoroppmap |
| Components_per_routing_steps |
| Consolidated_exchange_rates |
| Countries |
| Coupon_codes |
| Crmgroup |
| Crmgroupmap |
| Customer_currencies |
| Customer_group_pricing |
| Customer_item_pricing |
| Customer_subsidiary_map |
| Employee_currency_map |
| Employee_types |
| Entitycategory |
| Entityeventmap |
| Entityterritorymap |
| Entity_role_map |
| Entity_status_history |
| Eventattendees |
| Expense_based_charge_rules |
| Expense_categories |
| Expense_categories_rates |
| Expense_categories_subs_map |
| Expense_detail |
| Fixed_fee_charge_rules |
| Group_test_cell |
| Inbound_shipments |
| Inventory_cost_template_items |
| Item_collection_item_map |
| Item_collections |
| Item_demand_plan_lines |
| Item_group |
| Item_location_map |
| Item_price_history |
| Item_prices |
| Item_quantity |
| Item_shipmethods |
| Item_site_categories |
| Item_subsidiary_map |
| Item_supply_plan_attributes |
| Item_supply_plan_lines |
| Item_supply_plan_source |
| Item_supply_plan_source_types |
| Item_vendor_map |
| Item_vendor_pricing |
| Job_resources |
| Job_resource_role |
| Job_types |
| Location_costing_groups |
| Location_costing_grp_locations |
| Memorized_trans |
| Messagerecipient |
| Mfg_cost_template_items |
| Mfg_routing_steps |
| Nexus |
| Payroll_item_types |
| Percent_complete_overrides |
| Planassignmap |
| Planschedulemap |
| Posting_account_activity |
| Posting_account_activity_pe |
| Price_book_line_intervals |
| Price_books |
| Pricing_groups |
| Price_plans |
| Price_tiers |
| Project_billing_budgets |
| Project_cost_budgets |
| Project_cost_categories |
| Project_expense_types |
| Project_revenue_rl_plans |
| Project_revenue_rls_charge_rls |
| Project_task_assignments |
| Project_task_billing_budgets |
| Project_task_cost_budgets |
| Project_task_dependencies |
| Project_templates |
| Project_time_approval_types |
| Quota1 |
| Resource_group_entity_map |
| Revaluation |
| Revenue_plan_versions |
| Revenue_plan_version_lines |
| Revrecschedules |
| Revrecschedulelines |
| Role_subsidiary_map |
| Salesforecast |
| Shipment_packages |
| Solutioncasemap |
| Solutiontopicmap |
| States |
| Standard_cost_components |
| Subscript_line_price_intervals |
| Subscription_co_lines |
| Subscription_plan_lines |
| Subscription_terms |
| Subsidiary_book_map |
| Subsidiary_class_map |
| Subsidiary_department_map |
| Subsidiary_location_map |
| Subsidiary_nexus_map |
| Supplier_categories |
| Supportcasehistory |
| Task_contacts |
| Time_based_charge_rules |
| Topic |
| Transaction_bin_numbers |
| Transaction_book_map |
| Transaction_cost_components |
| Transaction_inventory_numbers |
| Transaction_shipping_groups |
| Transaction_tax_detail |
| Transaction_tracking_numbers |
| Usages |
| Vendor_currencies |
| Vendor_subsidiary_map |
| Vendor_types |
| Work_calendars |
| Work_calendar_holidays |
Item_location_map
Item_location_map is an exception. If you enable it, it will update every sync. If the Item_location_map re-import is slow for your connection, we recommend you set up a second NetSuite connector for this single table to maintain the speed of updates to your other tables.
Excluded Tables
| Table Name | Other reason for exclusion |
|---|---|
| Account_activity | View on Posting_account_activity |
| Account_period_activity | View on Posting_account_activity |
| Account_period_activity_pe | View on Posting_account_activity_pe |
| Case_types | Duplicate of Casetype |
| Case_origins | Duplicate of Caseorigin |
| Deleted_records | System table that keeps track of all deleted records |
| Nlcompany | Has a single row with information about NetSuite account itself |
| Notes_system | Obsolete as of NetSuite 2017.1 |
| Notes_system_custom | Obsolete as of NetSuite 2017.1 |
Primary Key Hashing
Some primary key columns contain null, but contain at least one non-null primary key column; and most destinations don't support a null value for the primary key column.
So, Fivetran creates hash of primary key columns and stores them in the _fivetran_id system column,
where the _fivetran_id column is the part of primary key for the table alongside the non-null primary key column.
Actual primary key columns won't be created as primary keys of the table in the destination.
Deleting data
We do not rely on the Delete_records table to capture deletes for standard tables. Instead, we use a novel approach to incrementally capture deletes.
We logically divide the table into contiguous chunks, and import the chunks for which we detect change (using the checksums from the previous sync).
We use the chunk size in a way that it minimises the amount of data to be transferred from NetSuite.
1) The following tables have a _fivetran_deleted column in the destination.
When rows are deleted from these tables in the account, the corresponding rows in the destination are marked as deleted.
| Table Name |
|---|
| Accounting_books |
| Accounts |
| Address_book |
| Addresses |
| Bill_of_distributions |
| Billing_accounts |
| Billing_rate_cards |
| Billing_subscription_lines |
| Billing_subscriptions |
| Campaign_audiences |
| Campaign_categories |
| Campaign_families |
| Campaign_search_engine |
| Campaign_subscriptions |
| Campaign_verticals |
| Campaignchannel |
| Campaignoffer |
| Campaigns |
| Caseissue |
| Caseorigin |
| Casetype |
| Classes |
| Commissionplan |
| Company_status |
| Competitor |
| Contactrole |
| Contacts |
| Crmtemplate |
| Currencies |
| Currency_exchange_rate_types |
| Currency_exchange_rates |
| Currencyrates |
| Customer_types |
| Customers |
| Departments |
| Distribution_categories |
| Distribution_networks |
| Employee_time |
| Employees |
| Entity |
| Entity_status |
| Expense_accounts |
| Expense_amortization_rules |
| Expense_plans |
| Global_account_map |
| Global_inventory_relationships |
| Income_accounts |
| Inventory_cost_template |
| Item_account_map |
| Item_revisions |
| Items |
| Locations |
| Message |
| Mfg_cost_template |
| Nexus |
| Notes_user |
| Notetype |
| Originating_leads |
| Partner_sales_roles |
| Partners |
| Payment_methods |
| Payment_terms |
| Payroll_items |
| Planned_standard_costs |
| Price_types |
| Project_revenues_rules |
| Promotion_codes |
| Purchase_charge_rules |
| Resource_groups |
| Revenue_elements |
| Revenue_plans |
| Revenue_recognition_rules |
| Sales_reps |
| Sales_roles |
| Sales_territories |
| Service_items |
| Subscription_change_orders |
| Subscription_plans |
| Subsidiaries |
| Support_incidents |
| Support_territories |
| Tax_items |
| Territory |
| Transactions |
| Units_type |
| Unlocked_time_periods |
| Uom |
| Vendors |
| Winlossreason |
2) Deleted records are hard deleted from the destination for the following tables.
| Table Name |
|---|
| Accounting_periods |
| Activities |
| Billing_rate_cards_versions |
| Calls |
| Campaign_subscription_statuses |
| Charges |
| Companies |
| Companycontactmap |
| Contact_types |
| Customer_partner_sales_teams |
| Customer_sales_teams |
| Events1 |
| Expense_plan_lines |
| Fair_value_prices |
| Generic_resources |
| Gift_certificates |
| Inventory_items |
| Inventory_number |
| Item_billing_rates |
| Item_demand_plans |
| Item_fulfillments |
| Item_supply_plans |
| Mfg_routing |
| Opportunities |
| Opportunity_lines |
| Opportunitycontactmap |
| Other_names |
| Partner_types |
| Project_tasks |
| Resource_allocations |
| Revenue_plan_lines |
| Roles1 |
| Shipping_items |
| Solution |
| Subscription_line_revisions |
| Support_reps |
| Tasks |
| Timesheet |
| Trans_partner_sales_teams |
| Transaction_address |
| Transaction_line_book_map |
| Transaction_lines |
| Transaction_links |
| Transaction_sales_teams |
3) Custom tables with a single primary key also receive entries in the Deleted_records. Therefore, they also have
a _fivetran_deleted column in the destination.
4) Re-imported tables also have a _fivetran_deleted column in the destination. After each re-import, any row that no
longer exists in the source table is marked as deleted in the destination.