You can set up an external system and associate it with a mapping profile in a Planning Data Loader to allow drill through from Adaptive Insights sheet and report data into NetSuite. When a user clicks a drill through link, Adaptive Insights connects to the system and reveals the data's underlying transactions.
Associating a mapping profile with an external system is optional. Only set one up to enable drill through from Adaptive Insights to NetSuite.
Before You Begin
- Permissions required: Integration, Data Designer
- Verify you have access to NetSuite data sources in your instance
- Verify you created a NetSuite Credential for Token Based Authentication
- When you create a new data source specifically for drilling, make a note of the NetSuite columns you want to map to data in Adaptive Insights .
- Only the standard NetSuite dimensions (categories) of Account, Level, Customer, Item, and Department allow drilling
How You Get There
Go to nav menu Integration > Data Designer
- Create an external system and give it a name.
- Set up external system settings so that Adaptive Insights knows how to connect and what account and/or credentials to use.
- Set up external dimension mappings to identify how dimensions on the external system map into the dimensions in Adaptive Insights.
- Configure tuple SQL columns if multiple external columns map to one Adaptive Insights dimension
Best Practice: Configure an external system for NetSuite before configuring a planning loader for the data source. After you create the external system, you will need to return to the NetSuite data source to complete the drill-through set up.
WARNING: If you are importing data from NetSuite and would like to also configure drill-through into NetSuite based on the standard categories Account, Time Period, Subsidiary, Department, Class, Location, Item and Customer, then you will need to configure the NetSuite internal identifiers for each of these dimensions as part of your column and data mapping steps in the Planning Data Loader.
Create an External System:
- Expand External Systems in the Component Library panel on the right.
- Click Create New External System.
- In the Create New ... dialog, enter a name and click Create.
Set Up NetSuite External System Settings:
- Select the External Source System, in the External System Settings tab. Select NetSuite, enter the Account ID used in NetSuite.
- Select External Dimensions to view a list of the external system dimensions and drill-through parameter names.
Set Up NetSuite External Dimension Settings
- Click the External Dimension Mapping tab.
- Double click a Planning Dimension. In the dialog, select an External Dimension to map it to. Click Apply.
- A green status checkmark indicates the Planning Dimension successfully mapped to at least one external dimension. Click Save.
External Dimension Mappings
External Dimension mappings help when mapping two or more dimensions from an external system into a single planning dimension for drill-through.
- Select an External System listed in the Component Library to map additional external dimensions to a single Planning dimension.
- Select the External Dimension Mapping tab and double click a Planning dimension to launch the mappings editor.
- Select external dimensions and enter a delimiter in the mappings editor. A delimiter is required when multiple external dimensions get selected.
- Delimiters cannot exceed five characters in length and should not match any characters used in a dimension value (actual values in the tuple mapped columns described later). Click Apply.
- View the External Dimension(s) column to verify it indicates multiple external dimensions mapped to a single Planning dimension.
- Click Save in the Actions panel after you finish mapping the Planning Dimensions the External Dimensions.
Configure Tuple SQL Columns For NetSuite
If you map multiple columns from NetSuite into a single dimension in Planning, then you need to create a Tuple SQL column in the staging table that you wish to expose in the Planning loader.
If you do not require data from multiple NetSuite columns into the same Planning dimension, then you do not need to create a Tuple SQL column in the data source.
If you plan to reuse an existing NetSuite data source to enable drill-through and if you need to bring multiple NetSuite columns into a single Planning dimension, you must redo that mapping using Tuple SQL columns. Reconfigure your loaders to ensure that the integration task can be valid. Make these types of changes in a test environment and validate the changes there before rolling them out to a production system.
All of the following steps need to be completed to configure the Tuple SQL Column for drill-through. Perform them only if you require data import from multiple NetSuite columns into a single Planning dimension.
In order for Adaptive Insights to parse the different data items into the source NetSuite categories, you will need to specify which NetSuite source columns combine to form a single column before importing this data into Planning.
The order in which the different NetSuite columns combine, and the delimiter specified, should match how the many-to-one column mappings are specified in the NetSuite external system.
By indicating the constituent NetSuite columns and what their underlying NetSuite categories are, Adaptive Insights constructs drill-through URLs on the Planning sheet.
Before you create a Tuple SQL column, ensure that you have configured the many-to-one column mapping in the NetSuite external system.
- Select your NetSuite Data Source in the Component Library.
- Select the table you want to enable drill-through on in the Data Components panel on the left.
- The table comes into focus in the Staging Area. Expand the custom column folder in the Data Components.
- Drag and drop a Tuple SQL Column into the Staging Area. This column is available when at least one tuple mapping is defined in an external system.
- Add a name, select a previously configured External System, and select the Planning Dimension that corresponds to the External Dimension Mapping performed in the previous section.
- Select the Mappings tab.
- For each of the External Dimensions, map the corresponding table column.
- Select Apply
- Select Save in the Actions panel.