NetSuite SuiteAnalyticslink
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.
Featureslink
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 | ||
dbt Package | check | Get the package |
Setup guidelink
Follow our step-by-step NetSuite SuiteAnalytics setup guide to connect NetSuite SuiteAnalytics with your destination using Fivetran connectors.
Schema informationlink
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
Sync noteslink
We only support NetSuite.com as a data source. We don’t support NetSuite2.com.
Analytics templateslink
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 synclink
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 datalink
Fivetran uses one of the following strategies to update the tables that you included in your sync:
Incrementallink
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-importlink
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 Tableslink
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 Hashinglink
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 datalink
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.