This content is identical to Setting up a NetSuite Data Source within Setting up Data Sources
You can use Adaptive Integration to extract information from your NetSuite instance. Adaptive users can access data in a NetSuite instance through the NetSuite adapter for Adaptive Integration.
To set up a NetSuite data source, follow these steps:
Access the Data Designer by going to Integration > Data Designer.
Expand the Data Sources folder in the Component Library.
(The NetSuite data source is licensed separately. If you do not see this adapter within the list of data sources, contact Adaptive support for information.)
Click the NetSuite data source.
The center area of the screen displays your new data source’s settings and other information, as shown below.
Enter the data source’s information:
- Account Id: Enter the account ID for the NetSuite user account.
- Email Address: Enter the email address associated with the account ID.
- Use sandbox: Check this box to use the NetSuite sandbox. (The sandbox is an account you can purchase separately from NetSuite that copies the database to a different server.) You should check this box only if your implementation process requires you to test data in a sandbox as part of the data import process. Be sure to uncheck this box when you have to bring data from your production NetSuite instance.
- Role Id: Enter the role ID you want to access the Netsuite user account. (The role ID can be found on the NetSuite user/role configuration screens at Setup > Users/Roles > Manage Roles. The role ID must have the “Web Services” person activated. If you don't see a column called “Internal ID,” which is the role ID, you need to add it with the NetSuite Customize View option.)
- Account Time Zone: Select the appropriate time zone for the account from the drop-down list. (The timezone you select here must be the same one used within the NetSuite user interface for this user account. In NetSuite, go to Home > Set Preferences > Localization.)
- Backfill Batch Duration: Select the duration for the backfill batch. The default is one week (168 hours). This value is the number of hours of data extracted during a single update pass on a differentially-imported NetSuite table. This lets you set chunking for the scheduler to maintain a balance between large and small batch jobs. Lower this if you have very large amounts of NetSuite data. The smaller the value, the more backfill loops are required for a given calendar period.
The specific value you enter is highly dependent on a number of site-specific variables, such as the size of jobs, ratio small vs. large jobs, turnaround time, and system resources.
- Log Level: Select a log level from the drop-down list to specify the detail for the logging for this data source. Your options are:
- Error: Only logs serious errors.
- Info: Logs all basic information, such as when the data source was updated.
- Verbose: Provides very detailed information about all phases and actions. (This level is used primarily for debugging or auditing, as it may produce more log information than is practical for typical use.)
- SSO Token Active: This read-only box shows whether or not there is an active SSO token for the data source. You must have an active SSO token for any additional communications (such as testing connection, import structure, import data) to be successful. Saving a change to the Account Id, Email Address, Use Sandbox?, or Role Id fields causes the SSO token to be erased.
When you create a new data source, this box is not checked. You must create a new SSO token by clicking Generate SSO Token on the Actions menu. You enter the account ID, email, and role ID, then click Generate SSO Token. You enter the password for the account, then click OK to submit the info to NetSuite. NetSuite issues an SSO token. Adaptive Integration automatically checks SSO Token Active and removes Generate SSO Token from the Actions menu. The SSO token provides the login information to NetSuite so you don’t have to enter the NetSuite password whenever you import NetSuite data. (If there are any changes to the NetSuite user credentials, you must regenerate the SSO token by unchecking SSO Token Active.)
5. Click Save on the Actions menu.
You can test the connection information in the new agent by clicking Test connection in the Actions menu. The system tries to connect to the database using the information in the data source fields. If the system can connect to the database, click OK. If there are connection problems, contact Adaptive support.
When you can connect to the database, you’re ready to import the data structure. This shows you the tables and columns in the database.
To import the NetSuite structure, do the following:
Click Import structure in the Actions menu. The tables and columns appear in the Data Components menu.
Data is synchronized for a NetSuite user account as follows:
Adaptive Integration chooses the best synchronization method. It tries initially to get data incrementally. If the underlying data source doesn’t support this, Adaptive Integration does a full refresh on the data in the staging area.
Import Structure and Adaptive Posting Transaction Summary Table for NetSuite
Adaptive Integration provides a “getting started” table for NetSuite called the AdaptivePostingTransactionSummary Table.
The AdaptivePostingTransactionSummary and its associated tables show up by default during Import Structure. This table surfaces the most frequently requested data elements from a AdaptivePostingTransactionSummary and its associated tables show up by default during Import Structure. This table surfaces the most frequently requested data elements from a NetSuite source system. This Adaptive-created table automatically creates joins between the PostingTransactionSummary table and its associated tables. As a result, this exposes the business friendly names for the most frequently requested NetSuite categories: Account, Classification, Items, Department, Location, Subsidiary, and Customer.
For each table, the Name column is included. The Account Type column is automatically enabled from the Accounts tab. The category tables automatically import when Import Structure runs.
You might need to filter out the account types that are not needed using an SQL filter in the Planning Loader as described in Creating a SQL Filter.
You can also choose to remove one or more default dimensions available in the joined table. You only want to include the categories you need for your Adaptive Planning model. All other categories should be removed from the join, and the underlying column should be excluded from import from the PostingTransactionsSummary.
Adding Tables to a NetSuite Data Source
After you have imported the data’s structure, you are ready to specify the information you want to import. A standard set of NetSuite tables has already been enabled. If you want additional tables brought over, use the Tables to Import section of the screen to configure the imported NetSuite data.
To specify the tables to import from the user account, follow these steps:
Expand the entry for the data source in the Data Components menu, as shown below.
Each table appears as a separate item.
3. Drag and drop the tables you want to import to the Tables to Import section in the center of the screen, as shown below. As you drag each table to the Tables to Import section, a tab appears for the table and information appears in the Tables to Import section letting you see the type of information you’ve added. (Several sample tables appear below.) Each table’s tab also has a drop-down menu, which you can access by clicking the arrow next to its name.
If a table you are working with has a large number of columns, you can use the Manage Columns to do quick edits for which columns to import. The Manage Columns dialog box displays the columns in a table so you can then check or uncheck columns for importing.
To manage columns for importing, follow these steps:
Click the arrow on the tab to the right of the table name.
A floating menu with several options appears.
Click Manage Columns on the floating menu.
The Manage Columns dialog box appears.
3. Check the box next to a column to import it. When you check a column, the column also appears within the preview window after the popup contents are saved. Unchecking a column removes the column from the preview window.
You cannot change the import status on system-generated fields such as isDeleted. You can also change the import status on individual columns by selecting column properties and setting the property or by dragging and dropping the columns onto the designer.
Customizing the Tables
For each table, you can customize the way in which data is imported.
To customize the table settings, follow these steps:
Click the arrow on the tab to the right of the table name.
A floating menu with several options appears.
Click Table Settings on the floating menu.
The Table Settings dialog box appears, as shown below.
3. Enter the table settings:
- Data Import Mode: There is only one option in the drop-down list:
- Data source to automatically select the best synchronization approach. This is the default. The data source examines NetSuite and automatically uses the best way to synchronize data in the staging area with the information in the NetSuite. (Some tables are read in their entirety each time while others use differential updating.)
- Data Import Filter: Use this to enter a SQL expression that filters or formats information being imported. The SQL filter is applied before data is extracted from the database to reduce the amount of data that needs to be imported, giving you an additional level of control for which data is brought into the staging area. Click the field to display the Edit SQL Expression dialog box, shown earlier. (The filter you enter here is applied to the actual data in the database rather than the preview data in the staging area.) Not all data sources support the same level of SQL filtering capability.
NetSuite has a very limited, non-SQL filtering capability. Only very simple expressions are supported.
4. Click Apply to apply the settings.
5. Repeat steps 1-3 for each table.
6. Click Save to save the settings in the data source.
Setting Column Options
While using the Tables to Import section, you can see a preview of the data as it is in the data source or as it is in the Staging area after it has been imported. You can change the options for each column by mousing over its heading and then clicking the drop-down menu next to its name. These options are:
- Sort Ascending: Sort the entire table based on this column in ascending order.
- Sort Descending: Sort the entire table based on this column in descending order.
- Column Settings: This dialog is used to change the properties of the column within the staging table.
- Name: The name of the column, displayed in the header.
- Column Type: The type of data used in the column.
- Convert Column Type: Select the new type from this drop-down menu.
- Exclude column from import: Select this to remove this column from the data being imported.
- Delete custom column: Select this to delete the column. (Available for custom columns only.)
If a database table has keys defined, this information is brought into the staging table as well. You can review this information and edit as needed.
Creating Subquery Columns
Subquery columns let you filter NetSuite rows based on conditions with another related table. For example, you can filter NetSuite Opportunity records based on the existence of one or more Quote records. You can use subquery columns to pick the primary table/column and related table/column, check related tables for certain conditions, and insert appropriate values in the driving table based on the results of those checks.
For each table, you can customize the way in which data is imported.
To create a subquery column, follow these steps:
Expand the Custom Column folder in the data source in the Data Components menu.
Drag and drop the Subquery Column component to the Tables to Import section in the center of the screen. The Column Settings dialog box appears.
3. Enter the column settings:
- Name: Enter the name for the subquery column:
- Column Type: This display-only field identifies this as a boolean column.
- Related Table: Select a related table to use for comparison.
- Join Expression: Click this field to enter a join condition. The Edit SQL Join Expression dialog box appears. (More information on creating SQL joins appears in Adding a SQL Join Table.)
- Join Value Rule: Select one of the SQL join value options from the drop-down list: Exists, Does Not Exist, Sum, Count, Min, or Max.
- Click Apply to apply the settings.
- Click Save to save the settings in the data source.
Downloading Data from a Staging Table
You can download data from a single staging table.
To download data from a single staging table, follow these steps:
Click the down arrow in a table heading in the Tables to Import section.
Select Download Data.
At the prompt “This operation will send an email with a url to download the data,” click Send. Adaptive Integration sends an email containing a URL to the email account associated with the data designer.
Open the email and click the URL. The data is downloaded in .csv format.
Using the Advanced Filter
Below the Source drop-down menu, you can click Advanced Filter to reveal options for filtering the data in the Tables to Import preview area. The Advanced Filter provides tools to view and browse a subset of the data within a staging table or directly from the remote source (if you select NetSuite from the Source drop-down menu).
The preview area is specifically for previewing a small portion of the data in the staging area (or NetSuite). You can explore the data that is available from the Spreadsheet account and verify that you are getting the data you expect by examining the staging area. You can also use this to refine the data import filter you want to use. (Filters set in the preview function do not affect the actual data in the staging area.)
The options for the Advanced Filter are:
Distinct Rows: Check this box to hide duplicate rows.
Max Rows: Limit the number of rows displayed in the preview area by entering a number in the text box.
Columns: Check or uncheck columns in this drop-down menu to show or hide the columns displayed.
Removing a column from this list does not remove the column from the import list.
SQL Filter: Click the large text box to display the Edit SQL Expression dialog box (shown earlier), where you can enter a SQL filter to restrict the rows being previewed. Click Apply to automatically check the SQL syntax of the filter. (If there are any errors, the error is indicated in the expression.) The expression you entered appears in the Data Import Filter field. For detailed SQL syntax help, you can click here in the Notes section. NetSuite has a very limited, non-SQL filtering capability. Only very simple expressions are supported.
Click Remove Filter (next to the Source drop-down menu) to clear the settings in the preview window.
NetSuite designates certain columns as key columns. This information is brought over into the staging tables. This information should not be modified.
Importing Data from NetSuite
When you have set up and saved your NetSuite data source, you can import data.
To import data from NetSuite, do the following:
Click Import data in the Actions menu.
The selected data is filtered by any SQL expressions and loaded into the staging area. You can preview the data in the staging area.
Information remains in the staging area until you clear it.
To clear data from the staging area, do the following:
Click Clear staging area in the Actions menu.
The staging area for all the tables in the data source is cleared.
Importing NetSuite Saved Searches
Saved Searches can only be imported from NetSuite adapters. SSO Authentication for this Data Source must also be configured.
Importing saved searches requires configuring a NetSuite Saved Search in the Data Designer then importing its structure. Saved Searches within Adaptive Integration support NetSuite Custom Records, Custom Fields, and Custom Segments.
Configure a Saved Search in the Data Designer
Click Manage Saved Searches in the Actions menu.
In the Managed Saved Searches dialog, click the actions button below the navigation tree and select Add Saved Search, or right-click on the saved searches folder and select Add Saved Search. Give the Saved Search a Name.
Select the NetSuite Record Type.
Select the NetSuite Saved Search.
Import a Saved Search’s Structure
In the Managed Saved Searches dialog, click a Saved Search you previously created to select it.
Click the actions button below the navigation tree and select Import structure in the Actions Menu, or right-click on the saved search and select Import structure.
After the import Saved Search structure succeeds, close the Managed Saved Searches dialog.
The staging area will display a tab for each saved search structure you imported.
Be aware that all Saved Searches in the Data Components panel will import if you click Import structure in the Actions pane.
Best Practice: Make sure that the results of your Saved Search structure import does not contain columns with NetSuite Summary Results. This is a NetSuite web services API restriction.
The maximum number of NetSuite Saved Searches that can be imported is 1000.
Select a NetSuite Saved Search Data Import Mode
- In the staging area, click the arrow icon in the tab for a table of your NetSuite Saved Search.
- Select Table settings from the dropdown.
Two options are available:
- All records replaced each time a data import is run: This is the default. This replaces all data when the import is run.
- All records that fall within a period range transmitted: This allows you to select a period range using a parameter, then choose a Saved Search table's datetime column the parameter applies to. You can click Edit parameters within this dialog to create a period range parameter to use.
- Click Apply
- Click Save in the Actions panel
Import Data from a Saved Search
In the Actions panel click Import Data
The NetSuite Saved Search data imports to the Staging area.
Deleting NetSuite Saved Searches
Click Managed Saved Searches in the Actions pane.
Right-click on the saved search in the Managed Saved Searches and select Delete. The saved search is removed from the Manage Saved Searches dialog list, but its structure will remain in the Data Components pane and the Staging area until Import Structure is run again.
If a NetSuite saved search is deleted within NetSuite, a message will indicate it is no longer available when performing the import structure action. If a saved search that is already being used within Integration for data import is modified in NetSuite, then future data imports are not guaranteed to succeed.
Best Practice: Within NetSuite, create your saved searches specifically for use in Adaptive Integration and name them so that they are not modified or deleted by other NetSuite users.
Best Practice: Make sure that you have the appropriate permissions in NetSuite to view the contents returned by a Saved Search result. If you don't have permission to the content, your search will return zero records without an error message. This is a limitation in NetSuite indicated in their online help:
If you reference a saved search for which you do not have the permission, the exception is ignored and fields have no value (are not in the SOAP response at all).
Drill-through from Adaptive Planning Back to NetSuite
You can enable drill-through on data imported from NetSuite. Drill-through links will become available on Planning sheets and reports for actuals imported from NetSuite. In order to configure drill-through for NetSuite, you should ensure that your internal identifiers are used as part of column mapping in the Planning loader. You will need to configure a NetSuite external system and attach it to a profile that is used by the Planning loader.
If you are configuring multiple columns on the NetSuite side to map to a single Planning dimension, then those NetSuite columns need to be combined in the data source using a Tuple SQL column.
For more details, see Understanding Mapping Profiles.
High-level Steps for Enabling Drill Through from Planning to NetSuite
Before getting started, ensure the Enhanced bundle-based drill-through checkbox is checked within Planning's Integration > NetSuite Setup menu.
Configure a NetSuite External System.
If you have multiple NetSuite categories mapped into the same Planning Dimension, revisit the NetSuite Data Source to add a tuple SQL column for a many-to-one mapping of the columns.
After the above steps, configure a Planning Loader to import NetSuite actuals data.
- Link a Profile to the External System you configured in step 2.
- Attach this profile to the Planning Loader.