Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Creating a Planning Loader

Adaptive Integration for Planning uses a planning loader.

To create a planning loader, follow these steps:

  1. Access the Data Designer by going to Integration > Data Designer.

  2. In the Loaders folder in the Component Library on the right side of the screen, click Create New Loader.
    The Create New dialog box appears.

  3. Select Planning Loader as the loader type and enter a name for the loader.

  4. Click Create.
    The center area of the screen displays your new loader’s settings and other infor­mation, as shown below.

    !AI4P_default_planning_loader_fields.png
  5. Enter the planning loader’s general properties information:

  • Source Table: Select the source table from the drop-down list. The source tables in the list are all the tables available in the staging area for this user.
  • Import Type: Select an import type from the drop-down list. Your options are:
    • Actuals: Lets you load data into the Actuals version of Adaptive Planning.
    • Plan: Lets you load data into any version defined within Adaptive Planning. (This import type is not available for a Consolidation-only instance.)
  • Transactions: Lets you load data into the Transactions module in adaptive Plan­ning and Adaptive Consolidation. (This option is not available if you have not pur­chased the Transactions module for your instance).
  • Version Parameter: Lets the user running this loader to select a version into which to import. Select from the version parameters you have previously defined. (For information on how to create a version parameter, see Creating a Version Parameter.) The parameters visible in the drop-down list are deter­mined by the value you selected in Import Type.
  • Import Into Sheet: Specify the planning sheet to load the data into. Select Stan­dard to load the data into the standard accounts (General Ledger, Custom, or Assumption accounts), or click the radio button below this to select a model or cube sheet from the drop-down list.

Whenever you change the sheet selection in an existing loader, Adap­tive Integration for Planning displays a dialog box with a warning that this will affect the column and members mappings. If you click OK, Adaptive Integration for Planning removes all column and data mappings from the loader. You must then create new mappings for the loader, to correspond to the new sheet.

  • Make New Actuals Visible: This option is only available when Import Type is set to Actuals. If this box is checked, the Actuals version has its Completed Val­ues Through date set to the final month of data found in the import, making the imported data available for overlaying plan data in plan versions. This option is also available as a parameter, to allow the user running the integration task to override this setting. Depending on the period range being imported and the cur­rent date set on the actuals version, it is possible the import will move the "com­pleted" date backwards.Actuals. If this box is checked, the Actuals version has its Completed Val­ues Through date set to the final month of data found in the import, making the imported data available for overlaying plan data in plan versions. This option is also available as a parameter, to allow the user running the integration task to override this setting. Depending on the period range being imported and the cur­rent date set on the actuals version, it is possible the import will move the "com­pleted" date backwards.
  • Replace existing in data sheet: This option is only available when you select a modeled sheet from the drop-down list. If this box is checked, all existing data for the sheet in the selected version is erased and replaced with new data from the loader. If this box is not checked, the data being loaded is appended into the model sheet. (This option is also available as a parameter, which can be overrid­den at runtime.)
  • Delete Existing Transactions: This option is only available when Import Type is set to Transactions. If this box is checked, all existing transactions associated with the Period parameter are deleted.
  • Log level: Select a log level from the drop-down list to specify the detail for the logging for this loader. Your options are:
    • Error: Only logs serious errors.
    • Info: Logs all basic information, such as when the loader 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.)
  1. Enter the planning loader’s default period settings information:
  • Period Parameter: You can specify the range of time periods for which data needs to be loaded into Adaptive Planning. (For information on how to create a period parameter, see Creating a Period Parameter.)
  • Start Date: This display-only field shows the start period selected in the Period Parameter.
  • End Date: This display-only field shows the end period selected in the Period Parameter.

  • Time Stratum: This display-only field shows the time stratum, based on the selec­tion in the Import Type field. If the import type is Actuals or Plan, the time stratum is set to the lowest available time stratum of the sheet you are importing into. If the import type is Transactions, the time stratum is set to “Day.” 

  • Period Column: This option is not available when Import Type is set to Transac­tions. This drop-down list displays all columns of type Date or DateTime available in the source table selected in the DateTime available in the source table selected in the Source Table field. (If only one column meets these criteria, this field defaults to that column.) This column controls the mapping of time period data from the staging table to Adaptive Planning’s period. (You must align the data values in the staging values with the Adaptive Planning peri­ods.)

  • Posting Date: This option is only available when Import Type is set to Transac­tions. This is the staging column that contains the Date or DateTime values for the posting date (the date to be used for identifying and finding applicable trans­actions when drilling) for the transactions to be submitted to Adaptive Planning.DateTime values for the posting date (the date to be used for identifying and finding applicable trans­actions when drilling) for the transactions to be submitted to Adaptive Planning.

  1. Enter the erase actuals settings.

    When you define an actuals import into a standard sheet, you can erase GL accounts, custom accounts, and/or cell notes before the planning loader loads the data into Planning/Consolidation. If you are defining an actuals import into a cube sheet, you can also erase cube accounts.

    These options are available when you’re defining the planning loader and also when a task containing this planning loader runs. If there is more than one planning loader within a task, then each of the erase options are available for each included loader. The options are tagged with the name of the loader to help identify the source of the erase option.

You must have the 'Erase Actuals' permission enabled in Planning for these features to work.Actuals' permission enabled in Planning for these features to work.

  • GL Accounts: This options is only available when Import Type is set to Actuals. If this box is checked, all data in GL accounts is erased for the time period chosen in the loader prior to new data being loaded by the loader. (This option is also available as a parameter, which can be overridden at runtime.) Selecting this option only deletes data in GL accounts but leaves data unchanged in custom accounts and in cell notes (and in cube accounts if Actuals. If this box is checked, all data in GL accounts is erased for the time period chosen in the loader prior to new data being loaded by the loader. (This option is also available as a parameter, which can be overridden at runtime.) Selecting this option only deletes data in GL accounts but leaves data unchanged in custom accounts and in cell notes (and in cube accounts if Import into Sheet is set to Expense Cube).

  • Custom Accounts: This option is only available when Import Type is set to Actuals. If this box is checked, all data in custom accounts is erased for the time period chosen in the loader prior to new data being loaded by the loader. (This option is also available as a parameter, which can be overridden at runtime.) Selecting this option only deletes data in custom accounts but leaves data unchanged in GL accounts and in cell notes (and in cube accounts if Actuals. If this box is checked, all data in custom accounts is erased for the time period chosen in the loader prior to new data being loaded by the loader. (This option is also available as a parameter, which can be overridden at runtime.) Selecting this option only deletes data in custom accounts but leaves data unchanged in GL accounts and in cell notes (and in cube accounts if Import into Sheet is set to Expense Cube).

  • Cube Accounts: This option is only available when Import Type is set to Actuals and Actuals and Import into Sheet is set to Expense Cube. If this box is checked, all data in cube accounts is erased for the time period chosen in the loader prior to new data being loaded by the loader. (This option is also available as a parameter, which can be overridden at runtime.) Selecting this option only deletes cube accounts but leaves data unchanged in GL accounts, custom accounts, and cell notes.

  • Cell Notes: This option is only available when Import Type is set to Actuals. If this box is checked, all data in GL accounts is erased for the time period chosen in the loader prior to new data being loaded by the loader. (This option is also available as a parameter, which can be overridden at runtime.) Selecting this option only deletes cell notes but leaves data unchanged in GL accounts and custom accounts (and in cube accounts if Actuals. If this box is checked, all data in GL accounts is erased for the time period chosen in the loader prior to new data being loaded by the loader. (This option is also available as a parameter, which can be overridden at runtime.) Selecting this option only deletes cell notes but leaves data unchanged in GL accounts and custom accounts (and in cube accounts if Import into Sheet is set to Expense Cube).Creating a Version Parameter

You can use a version parameter to specify at runtime which version to load the data into. This gives you greater flexibility when setting up a planning loader, as the version parame­ter lets you select a version other than the default at runtime.

You only need to create a version parameter once for a loader. Users with access to the loader can select a value for the parameter by selecting an available version within the version tree they see when they run the loader. (The version tree you can select from depends on the type of the parameter.) Only versions that allow importation of data by the user running the loader are selectable at runtime.

Creating an Actuals Version Parameter

You can set up version parameters of either actuals or plan type. The different types spec­ify where you want to load the data.

To create an actuals version parameter, follow these steps:

  1. Click Edit parameters below the Version Parameters drop-down (shown earlier). The Parameters editor appears, as shown below.

    !AI4P_parameters_editor.png
  2. Select a folder from the left pane. If you want the parameter to be shared across users or loaders, select a folder from the Shared area. If you want the parameter to only be available to the current loader, select a folder from the Local area.

  3. Click Add. A context menu appears, as shown below.

    !AI4P_parameters_selecting_options.png
  4. From the context menu, click Actuals Version. The Actuals parameter dialog box appears, as shown below.Actuals parameter dialog box appears, as shown below.

    !AI4P_parameters_actuals_selection.png
  5. Enter the actuals parameters information:

  • Name: Enter the name for the parameter. The name entered here is the label the planning loader and associated tasks show at runtime.

  • ActualsActuals Version: Select the default value for the version from the drop-down list.

For Adaptive Consolidations, select which of the Actuals subversions is used as the default. For Adaptive Planning, there is only one Actuals version, which is the only available selection in this dialog.

  1. Click Apply to apply the settings.

  2. Click Close to return to the Parameter editor dialog box. The actuals version param­eter you have just created is now visible in Version Parameters drop-down list.

Creating a Plan Version Parameter

The process for setting up a plan version parameter is similar to that for creating an actu­als version parameter.

To create a plan version parameter, follow these steps:

  1. Click Edit parameters below the Version Parameters drop-down (shown earlier). The Parameters editor appears, as shown below.

    !AI4P_parameters_editor00118.png
  2. Select a folder from the left pane. If you want the parameter to be shared across users or loaders, select a folder from the Shared area. If you want the parameter to only be available to the current loader, select a folder from the Local area.

  3. Click Add. A context menu appears, as shown below.

    !AI4P_parameters_selecting_options00119.png
  4. From the context menu, click Plan Version. The Plan parameter dialog box appears, as shown below.

    !AI4P_parameters_defining_plan.png
  5. Enter the plan version parameter information:

  • Name: Enter the name for the parameter. The name entered here is the label the planning loader and associated tasks show at runtime.

  • Plan Version: Select the default value for the version from the drop-down list.

  1. Click Apply to apply the settings.

  2. Click Close to return to the Parameter editor dialog box. The plan version parame­ter you have just created is now visible in Version Parameters drop-down list.

Creating a Period Parameter

The period parameter specifies the range of time periods for which data will be loaded into Adaptive Planning. You can override the assigned time periods at runtime. You can select a single period, or set start and end periods that are either dynamic or fixed.

Dynamic Period

A dynamic period allows configuring an offset from the current time in Planning’s configured calendar.

plannnigLoaderDynamicPeriodOffsetEnd.png

Offset: A positive integer representing how many Time Strata to offset from the current date. 

Time Strata Selector: A dropdown list of the time strata configured in the Planning calendar (months, quarters, weeks, years, etc.).

Direction: The direction of the offset, either forwards or backwards from the current time.

Fixed Period

A fixed period period allows choosing any time values found in Planning’s configured calendar by letting you navigate its time hierarchy.

To create a period parameter, follow these steps:

  1. Click Edit parameters below the Period Parameters drop-down (shown earlier). The Parameters editor appears, as shown below.

    !AI4P_period_parameters_screen.png
  2. Select a folder from the left pane. If you want the parameter to be shared across users or loaders, select a folder from the Shared area. If you want the parameter to only be available to the current loader, select a folder from the Local area.

  3. Click Add. A context menu appears, as shown below.

    !AI4P_period_parameters_selecting_options.png
  4. From the context menu, click Period Range. The Period Range parameter dialog box appears, as shown below.

    !AI4P_period_parameters_defining.png
  5. Enter the period range parameter information:

  • Single Period: Check this box if you want to specify a single period for the date range. 
  • Start Period: 
    • Select Dynamic to choose a dynamic date for the start period, such as the previous quarter, month, year, or other stratum (if you are using a custom calendar) available from your Planning calendar.
      • Enter an offset integer and select forward or backward. Use 0 to select the current time stratum.
    • Select Fixed to specify any time period from your time structure in Planning.
      The resolved date for your selection appears beneath the dropdowns.
  • End Period: 
    • Select Dynamic to choose a dynamic date for the end period, such as the previous quarter, month, year or other stratum (if you are using a customer calendar) available from your Planning calendar.
      • Enter an offset integer and select forward or backward. Use 0 to select the current time stratum.
    • Select Fixed to specify any time period from your time structure in Planning.
      The resolved date for your selection appears beneath the dropdowns.
  1. Click Apply to apply the settings.
  2. Click Close to return to the Parameter editor dialog box. The period range parame­ter you have just created is now visible in the Period Parameter drop-down list.

Dynamic Period Example

Assume the current month is April for the screenshot below: 

The Start Period would resolve to March 1st, the first date of the prior month. The End Period would resolve to May 1st, the first date in the next month.

Fixed Period Example

The screenshot below shows a fixed Start Period of November 1, 2016 and a fixed End Period of March 31, 2017. Both of these were directly chosen from Planning's configured calendar.

Using Mapping Profiles with Planning Loaders

You may need to load data with similar account/dimension value information from sepa­rate GL/ERP/CRM systems, with the requirement that similarly named source-side mem­bers be part of more than one unique mapping. This results in similarly named source members being loaded into different sheets.

Users must define a linked profile before performing any column mapping.

Mappings created within a planning loader by default have all mappings in a default, global mapping set: all mappings created are within a single unified mapping set, with a behavior similar to the one in the current Planning product, where there can be no two dif­ferent maps with the same source member name.

You can save account/level/dimension value mappings for your planning loaders in a new mapping profile for each loader and assign it a name. When you create a loader, you can then attach either the default profile or the new mapping profile. This is useful if you are creating a large number of planning loaders: for example, if you are extracting information from multiple different systems such as NetSuite and Salesforce, or if you have multiple subsidiaries, you can use mapping profiles to create a large number of mappings easily. If there are multiple planning loaders in the same Integration instance, the loaders can be linked to either the default mapping profile or can be linked to a different mapping profile.

Most users do not need to use this feature. The default profile mapping is adequate. If you don’t think you need to use this, you probably don’t.

Understanding Mapping Profiles

Suppose you had the following members on the Source and Planning sides:

Source1:

  • Acct1

  • Acct2

Source2:

  • Acct1

  • Acct3

Planning:

  • Acct10

  • Acct11

  • Acct12

You can create the following mappings:

  • Loader 1: Profile 1: Acc1 <-> Acct10

  • Loader 2: Profile 2: Acc1 <-> Acct11

Using Mapping Profiles

You link the mapping profile to a planning loader as part of the loader creation process.

To use a mapping profile, follow these steps:

  1. After completing the Data Source Settings, click the Profile tab. The Profiles tab appears, as shown 

  1. Select the mapping profile you want to use.

You can create a new mapping profile by clicking New at the bottom of the screen. You then enter the name of the new mapping profile and click Save. You can also clone a pro­file from an existing mapping profile. Cloning copies all the mappings from an existing pro­file into a newly cloned profile. This is useful if you want to inherit mappings from an existing profile and then make changes to some of the mappings that should not affect the original profile.To clone a profile, click the Clone button and then specify the profile to clone from as well as the name of the new profile.

Some tips for using mapping profiles with planning loaders:

  • When a loader is created, it has the default profile linked to it unless you specify another profile in the Profile tab.

  • When you create a new profile, it is initially empty. Mappings from the default profile are not copied into it. If you need to duplicate mappings, you must use the Clone feature.

  • If you change the linked profile from a named profile to the default profile, only new mappings are brought in. In addition, Integration does not check for conflicts. How­ever, if the default profile does not have any staging values that exist in the staging columns mapped in this loader, then these staging values are brought into the default profile when you click on the Data Mapping tab as unmapped values.

External Systems

Associating a profile with an external system is optional, and should only be set up to enable NetSuite Drill-through in Adaptive Planning.

WARNING: Do not associate a profile with an external system if you do not plan on using Drill-through in Adaptive Planning.

Ensure that you have access to NetSuite Data Sources in your instance.

Drill-through from Planning sheets and reports back to NetSuite is enabled only on the standard dimensions of Account, Level, Customer, Item, Department.

If you are creating a new NetSuite data source and using it to enable drill through, then make a note of the NetSuite columns that will get mapped to Planning data.

Best Practice: Configure an external system for NetSuite before configuring a planning loader for the data source. After you have created such an external system, you will need to return to the NetSuite data source for completing the configuration of drill through.

If you are mapping multiple columns from NetSuite into a single dimension in Planning, then you will need to create a Tuple SQL column in the staging table that you wish to expose in the Planning loader.

If you do not have a requirement to bring data from multiple NetSuite columns into the same Planning dimension, then you do not have to create a Tuple SQL column in the data source.

If you will be reusing an existing NetSuite data source to enable drill through and if you are bringing multiple NetSuite columns into a single Planning dimension, please be aware that you will have to redo that mapping using Tuple SQL columns. In such a case, your loaders will have to be reconfigured to ensure that the integration task can be valid. It is strongly recommended that you make these types of changes in a test environment and validate the changes before rolling the changes onto a production system.

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, Sub­sidiary, 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.

To create an external system for Drill-through:

  1. Expand External Systems in the Component Library panel on the right.

  2. Click Create New External System.

  3. In the Create New ... dialog, enter a name and click Create.

To Configure the External System Settings:

  1. In the External System Settings tab, choose the External Source System for Net­Suite and enter the Account ID for that system. Enter the account ID used in Net­Suite. The only type of external system supported is NetSuite.

  2. Click the External Dimensions tab to view a list of the external system’s dimen­sions and drill-through parameter names.

To Configure the External Dimension Settings

  1. Click the External Dimension Mapping tab.

  2. Double click a Planning Dimension. In the dialog, select an External Dimension to map it to. Click Apply.

  3. A green status checkmark indicates the Planning Dimension is mapped to at least one external dimension. Click Save.

To Configure External Dimension Mappings

External Dimension mappings are useful for mapping two or more dimensions from an external system into a single planning dimension for drill-through.

  1. To map additional external dimensions to a single Planning dimension, select an External System listed in the Component Library.

  2. Click the External Dimension Mapping tab and double click a Planning dimension to bring up the mappings editor.

  3. In the mappings editor, select external dimensions and enter a delimiter. A delimiter is required if more than one external dimension is selected.
    Delimiters cannot exceed five characters in length and should not match any char­acters used in a dimension value (actual values in the tuple mapped columns described later). Click Apply.

  4. The External Dimension(s) column now indicates multiple external dimensions are mapped to a single Planning dimension. Click Save in the Actions panel on the left after mapping the Planning Dimensions the External Dimensions.

To Configure Tuple SQL Columns For NetSuite

All of the following steps need to be completed to configure the Tuple SQL Column for drill-through.

These steps are needed only if you have a requirement to import data from multiple Net­Suite columns into a single Planning dimension.

In order for Adaptive Integration to parse the different data items into the source NetSuite categories, you will need to specify which NetSuite source columns combine to form a sin­gle column before importing this data into Planning.

The order in which the different NetSuite columns combine, and the delimiter specified, should be identical to 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 catego­ries are, Integration will be able to construct drill through URLs on the Planning sheet.

WARNING: Before you create a Tuple SQL column, ensure that you have configured the many-to-one column mapping in the NetSuite external system.

1. Select your NetSuite Data Source in the Component Library.

2. Select the table you want to enable drill-through on in the Data Components panel on the left.

3. The table comes into focus in the Staging Area. Expand the custom column folder in the Data Components.

4. Drag and drop a Tuple SQL Column into the Staging Area.

5. 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.

6. Select the Mappings tab.

7. For each of the External Dimensions, map the corresponding table column.

8. Click Apply

8. Click Save in the Actions panel.

Configuring Column Mapping

After you have linked a profile in the settings the Profiles tab, you must then map the stag­ing columns to Adaptive Planning columns. You cannot perform column mapping until a profile is linked.

To map staging columns to Adaptive Planning columns, follow these steps:

The number of columns that have been mapped appears near the top of the screen.

The Column Mapping screen has four columns. You can complete the entire mapping pro­cess with just the Source Id Column. The only requirement is that this column must con­tain unique values from the data source. In cases where the information in the Source Id Column is not easily understandable or if you need further context for what the identifier represents, you can include an optional source display name column to make it easier to map source values. If you use the Source Display Name Column in the mapping, its val­ues are used in the member mapping UI; otherwise, the values from the Source Id Column are used.

  1. Click the Column Mapping heading. The Column Mapping screen appears, as shown below.

    !AI4P_column_main_screen.png
  • Status: Indicates if a particular Planning column is mapped.

  • Planning: Displays all the Planning columns available for mapping on a sheet. The list of Planning columns include a set of mandatory Planning columns, followed by all other Planning columns. Mandatory Planning columns are indicated by bold text with an asterisk and include a value column (labeled “Actuals Version Value” if the import type is Actuals or “Plan Version Value” if the import type is Plan), Accounts, Level, and any other mandatory column specified on a particular sheet. Non-manda­tory columns include any other Planning column on the sheet (typically all custom dimensions).Actuals or “Plan Version Value” if the import type is Plan), Accounts, Level, and any other mandatory column specified on a particular sheet. Non-manda­tory columns include any other Planning column on the sheet (typically all custom dimensions).

  • Source Id Column: Displays all columns within the staging table chosen in the data source settings (shown earlier).

  • Source Display Name Column: Displays optional display names to simplify the mapping process.

  1. Select an unmapped column and click the down arrow. A list of columns you can map to appears in the drop-down list (shown below).

    !AI4P_column_mapping_columns.png
  2. As you map the columns, the status indicator changes from red to green. The image below shows several of the planning columns mapped.

!AI4P_columns_mapped.png

If there are a lot of Planning columns to map, you can use the Show filter at the upper left of the screen. You can select any of the following options:

  • All: Shows all Planning columns.

  • Required: Shows all required Planning columns.

  • Unmapped: Shows all unmapped Planning columns.

  • Mapped: Shows all mapped Planning columns.

You can use the Search field to find staging columns. Enter a complete or partial name to search for and click the magnifying glass. Any staging columns matching the search crite­rion appear in the staging column.

Configuring Data Mapping

After you have configured the settings for the loader and mapped the staging columns to the Planning columns, you can map individual members within the staging columns to the corresponding Planning members.

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, Sub­sidiary, 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.

Mapping Account Members

You can map individual account members within the staging columns to corresponding Planning members.

To map account members in the staging columns to the corresponding Adaptive Planning columns, follow these steps:

  1. Click the Data Mapping heading. The Data Mapping overview screen appears, as shown below.

    !AI4P_data_main_screen.png

The Data Mapping overview screen shows you summary data for the mapped and unmapped members. You can see how many members are mapped within accounts, levels, and dimensions. (The numbers shown in the overview summary reflect the staging members, not the Planning members.)

2. Click the Account link category name to display detailed information about the map­pings of the members in the staging columns. The Account Mapping detail informa­tion appears, as shown below. Unmapped members appear in red and mapped members appear in green.!AI4P_data_mappings_account_detail.png

As with the column mappings, you can use the Display and the Search options to filter the display of members and search for specific members. (You can also click the numbers in the mapped and unmapped members for Accounts on the Overview screen, shown earlier to display only the mapped or unmapped members.)

When you are working with account members, there is always a single staging column mapped to Planning accounts, so the filter region on the left always shows a single filter column.

3. Click the Planning value column for a staging member value entry. A drop-down list of the hierarchical view of the Planning dimension (such as accounts, levels, or a custom dimension) appears, as shown below.!AI4P_data_mappings_accounts_with_dropdown.png

 
  1. Click a Planning member to map it to the staging member.

  2. Click Save to save the data mapping information.

Creating New Mappings

You can create a new mapping in anticipation of a new data import. This is helpful for scheduled runs of the data import when you know that a particular member value will appear in a future import.

To create a new account mapping, follow these steps:

  1. Click the gear icon at the top right corner of the mapping table for the dimension you are mapping.

  2. Click New Account Mapping.The New Account Mapping dialog box appears, as shown below.

    !AI4P_data_mappings_new_account_mapping.png
  3. Enter the new account mapping information:

  • Staging Column: Select a staging column from the drop-down list.
  • Staging Value: Enter an account code (or account name if you are mapping by account name) not currently present in the staging table.
  • Planning Value: Select a Planning member from the drop-down list.
  1. Click Apply.
  2. Click Save.
Automapping, Unmapping, and Deleting

You can automap members to Planning members. Adaptive Integration for Planning searches for Planning members that match the selected staging members (either by code or by name) and, if an exact match is found, the staging member(s) are mapped.

To automap accounts, follow these steps:

  1. Select the members to automap by clicking the checkbox at the beginning of each row.

  2. Click Automap.

  3. Click Automap Selected. (You can automap all unmapped members by selecting Automap All.) The Confirm Automap dialog box appears, shown below.

    !AI4P_data_mappings_automap.png
  4. Select Account code or Account name and click Automap.

You can unmap one or more mappings by clicking Unmap and then clicking Unmap Selected to remove the mappings between staging accounts and Planning accounts. You can also click Unmap All to break all mappings.

You can completely delete a mapping and the member information by clicking Delete and then clicking Delete Selected. This removes the member information completely. You can then start over with the mappings and importing data from the staging table. You can also click Delete All to delete all information.

Warning: You can not retrieve a mapping that has been deleted.

Tips for Importing Account Mapping Data

There are some conditions on what you are able to import and how.

  • Group accounts, parent accounts, and system accounts can not have data.
  • Metric accounts can not have data.
  • Modeled accounts can not have data imported; however,  modeled sheets can have rows (which may include some data) imported using modeled sheet-level loading.
  • You can not use the standard import to import data to GL or Custom accounts, which are entered in cube sheets. You must use a cube import to import values to them using the cube where these accounts appear.

Mapping Level Members

You can map individual level members within the staging columns to corresponding Plan­ning members.

To map level members in the staging columns to the corresponding Adaptive Plan­ning columns, follow these steps:

  1. Click the Data Mapping heading. The Data Mapping overview screen appears, as shown earlier.

  2. Click the Level Overview link category name to display detailed information about the mappings of the members in the staging columns. The Level Mapping detail information appears, as shown below. Unmapped members appear in red and mapped members appear in green.!AI4P_data_mappings_level_detail.png

     

As with the column mappings, you can use the Display and the Search options to filter the display of members and search for specific members. (You can also click the numbers in the mapped and unmapped members for Levels on the Overview screen, shown earlier, to display only the mapped or unmapped members.)

  1. Click the Planning value column for a staging member value entry. A drop-down list of the hierarchical view of the Planning dimension (such as accounts, levels, or a custom dimension) appears, as shown below.

    !AI4P_data_mappings_levels_with_dropdown.png
  2. Click a Planning member to map it to the staging member.

  3. Click Save to save the data mapping information.

Creating New Mappings

You can create a new mapping in anticipation of a new data import. This is helpful for scheduled runs of the data import when you know that a particular account value will appear in a future import.

To create a new level mapping, follow these steps:

  1. Click the gear icon at the top right corner of the mapping table for the dimension you are mapping.

  2. Click New Level Mapping.The New Level Mapping dialog box appears, as shown below.

    !AI4P_data_mappings_new_level_mapping.png
  3. Enter the new level mapping information: 

  • Staging Column: Select a staging column from the drop-down list.
  • Staging Value: Enter a source level not currently present in the staging table.
  • Planning Value: Select a Planning level from the drop-down list.
  1. Click Apply.
  2. Click Save.
Automapping, Unmapping, and Deleting

You can automap source levels to Planning levels. Adaptive Integration for Planning searches for Planning levels that match the selected staging levels and, if an exact match is found, the staging member(s) are mapped.

To automap levels, follow these steps:

Select the levels to automap by clicking the checkbox at the beginning of each row.

  1. Click Automap.

  2. Click Automap Selected. (You can automap all unmapped members by selecting Automap All.) The Confirm Automap dialog box appears, shown below.

    !AI4P_data_mappings_automap_levels.png

Custom level members are always automapped using the level member names, so there is no Automap dialog box when you are automapping level members.

  1. Click Yes.

You can unmap one or more mappings by clicking Unmap and then clicking Unmap Selected to remove the selected mappings. You can also click Unmap All to break all mappings.

You can completely delete a mapping and the member information by clicking Delete and then clicking Delete Selected. This removes the member information completely. You can then start over with the mappings and importing data from the staging table. You can also click Delete All to delete all information.

Warning: You can not retrieve a mapping that has been deleted.

Tips for Importing Level Mapping Data

There are some conditions on what you are able to import and how.

  • Users with "Import capabilities" permission can import data into all levels, regardless of user level ownership.
  • Linked levels are visible but you can not map or import data to them.
  • Cube and model sheets show the entire level hierarchy, but you can not select levels that the sheet is not assigned to. Every sheet has associated levels to which it is assigned. If a sheet is not available on a particular level, it can not hold any data on that level.

Mapping Dimension Members

You can map individual dimension members within the staging columns to corresponding Planning members.

To map dimension members in the staging columns to the corresponding Adaptive Planning columns, follow these steps:

  1. Click the Data Mapping heading. The Data Mapping overview screen appears, as shown earlier.
  2. Click the Dimension Overview link category name to display detailed information about mappings of the members in the staging columns. The Dimension Overview detail information appears, as shown below. Unmapped members appear in red and mapped members appear in green.!AI4P_data_mappings_dimension_detail.pngAs with the column mappings, you can use the Display and the Search options to filter the display of columns and search for specific columns. (You can also click the numbers in the mapped and unmapped columns for Dimensions on the Overview screen, shown earlier, to display only the mapped or unmapped members.)

3. Click the Planning value column for a staging member value entry. A drop-down list of the hierarchical view of the Planning dimension (such as accounts, levels, or a custom dimension) appears, as shown below.!AI4P_data_mappings_accounts_with_dropdown00122.png

  1. Click a Planning member to map it to the staging member.
  2. Click Save to save the data mapping information.

Creating New Mappings

You can create a new mapping in anticipation of a new data import. This is helpful for scheduled runs of the data import when you know that a particular account value will appear in a future import.

To create a new dimension member mapping, follow these steps:

  1. Click the gear icon at the top right corner of the mapping table for the dimension you are mapping.

  2. Click New Dimension Mapping.The New Dimension Mapping dialog box appears, as shown below.

    !AI4P_data_mappings_new_dimension_mapping.png
  3. Enter the new dimension mapping information:

  • Staging Column: Select a staging column from the drop-down list.
  • Staging Value: Enter an account code (or account name if you are mapping by account name) not currently present in the staging table.
  • Planning Value: Select a Planning member from the drop-down list.
  1. Click Apply.
  2. Click Save.
Uploading Dimension Member Mappings

You can upload dimension member mappings from a spreadsheet. This lets you create dimension member mappings offline and then do a bulk import.

To upload dimension member mappings, follow these steps:

  1. Click Dimension Mapping to display the Dimension Mapping screen.

  2. Click the gear icon.

  3. Select Import Dimension Mappings. The Import Mappings dialog box appears, as shown below.

    !AI4P_uploading_dimensions.png
  4. Click Browse to select the file to upload. , You can download a preformatted Excel spreadsheet to use as an upload by clicking Download Template. The template (in Excel format) provides instructions for filling the template out on the first spread­sheet tab and the columns to populate with data on the second tab. When you have created the file, you can use the Upload Account Mappings link to upload the map­pings.

Automapping, Unmapping, and Deleting

You can automap members to Planning members. Adaptive Integration for Planning searches for Planning members that match the selected staging members (either by code or by name) and, if an exact match is found, the staging member(s) are mapped.

To automap dimensions, follow these steps:

  1. Select the members to automap by clicking the checkbox at the beginning of each row.

  2. Click Automap.

  3. Click Automap Selected. (You can automap all unmapped members by selecting Automap All.) The Confirm Automap dialog box appears, as shown below.

    !AI4P_data_mappings_automap_levels00123.png
Custom dimension members are always automapped using the dimen­sion member names, so there is no Automap dialog box when you are automap­ping dimension members.
  1. Select Account code or Account name and click Automap.

You can unmap one or more mappings by clicking Unmap and then clicking Unmap Selected to remove the mappings between staging accounts and Planning accounts. You can also click Unmap All to break all mappings.

You can completely delete a mapping and the member information by clicking Delete and then clicking Delete Selected. This removes the member information completely. You can then start over with the mappings and importing data from the staging table. You can also click Delete All to delete all information.

Warning: You can not retrieve a mapping that has been deleted.

Tips for Importing Dimension Member Mapping

There are some conditions on what you are able to import and how.

  • You can map to any dimension for standard (GL, custom, and assumption) accounts.
  • Cube accounts can only have mappings to dimension values for dimensions and values that are present on the sheet.
  • You can create mappings for modeled sheet selectors.
  • The import mapping namespace for dimension values is shared between standard, cube, and modeled sheets. If you create a mapping for standard import for a dimen­sion’s dimension value, and the dimension value is also on a cube, the same map­ping is used for that dimension value.
  • If an Adaptive dimension is configured to autocreate dimension members on data import, then staging columns mapped to these Planning dimensions do not appear in the list of columns that can be mapped in the dimension member mapping screen, as there is no need to perform any mapping for such dimensions.

Uploading and Downloading Mappings

You can create a new mapping between the source (staging) and Planning for accounts, levels and dimension members.

To upload account mappings, follow these steps:

  1. Click the settings icon in the Accounts mapping UI.

  2. Click Upload Account Mappings on the popup menu.
    The Upload Mappings dialog box appears, as shown below.

3. Click Browse to specify an accounts mapping file to upload. If no mappings exist, Integration creates a mapping between a source (external) account and a planning account. If a mapping already exists, the uploaded entry overwrites the existing map­ping. (If the planning account referenced in the mappings file does not exist, an error is displayed.)

You can also download a template file by clicking Download template. The tem­plate (in Excel format) provides instructions for filling the template out on the first spreadsheet tab and the columns to populate with data on the second tab. When you have created the file, you can use the Upload Account Mappings link to upload the mappings.

You can repeat this procedure in the level mapping and dimension value mapping tabs, selecting the appropriate upload link, and specifying a file in the dialog box.

To download mappings, select the Download Mappings option.

Data Designers can also click the gear icon at the top right of the Data Mappings Accounts list in the Data Mappings tab and select Download Account Mappings.

!AI4P_data_mapping_options_button_choices.png

Three download options are available, as shown below:

  • All: Downloads the entire mappings file.

  • Mapped Only: Downloads only the mapped mappings.

  • Unmapped Only: Downloads only the unmapped mappings. This is useful for quickly resolving unmapped entries in an offline mode. Once the unmapped entries are resolved, this file can then be used for Upload Mappings without changing col­umn or worksheet names.!AI4P_download_mappings_options_from_staging.png

     

The download button on the Download Mappings dialog box is enabled when all the map­pings have been downloaded into a spreadsheet.

Creating Business Rules

The Business Rules tab lets you create business rules to specify how to import data. You can create three types of business rules: skipping accounts, changing signs, and SQL fil­ters.

Creating a Skip Accounts Rule

You can specify which accounts or ranges of accounts need to be skipped during a data load process. The loader matches source accounts with codes specified within the skip account rules, and then discards those records from being uploaded into Planning.

This rule is applied on the source accounts. The rule assumes that the staging rows contain account codes and that mappings are between staging account codes and planning account codes.

You can set up rules for skipping individual accounts using account codes that specify alphabets, numbers, and some special characters: period (.), underline (_), hyphen (-), and colon (:). Rules for account ranges can only use numeric characters. You can create any number of skip account business rules.

To create a skip account rule, follow these steps:

  1. On the Business Rules tab, select Skip Accounts.

  2. Click Add. The Add Skip account range dialog box appears, as shown below.

    !AI4P_skip_accounts.png
  3. Enter the account number or account number range.

  4. Click Save Rule to save the rule. Saved skip account rules appear on the Business Rules screen. (If you want to create several skip account rules at once, check Cre­ate Another. When you then click Save Rule, the skip account rule is saved and the Add Skip account range dialog box stays open.)

Creating a Change Signs Rule

You can specify source accounts to have their values change signs.

The process for creating a change signs rule is virtually identical to that for creating a skip account rule. Any account that you specify in a change signs rule will have the sign reversed when it is loaded into Planning.

Creating a SQL Filter

You can use this feature to create SQL statements that specify which rows in the staging table are available for loading into Planning. Records that meet the filter criteria are selected for loading into Planning.

To create a SQL filter, follow these steps:

  1. On the Business Rules tab, select SQL Filter.

  2. Click Edit. The Edit SQL Filter dialog box appears, as shown below.

!AI4P_SQL_filter.png

  1. Enter a SQL WHERE-clause expression in the field. This filters out from the load any staging rows that do not match the expression. Click Apply to automatically check the SQL syntax of the filter. (If there are any errors, the error is indicated in the expression.) For detailed SQL syntax help, you can click here in the Notes sec­tion. The expression you entered appears in the Business Rules screen.

  2. For additional information, click the help available here link in the Notes section of the Edit SQL Filter dialog box.

Tips and Techniques

This section contains some general tips and techniques for working with Adaptive Integra­tion for Planning.

Saving Changes

You can save the changes you’ve made to the Planning loader at any time by clicking Save (and it’s good practice to save your changes frequently). You can discard any unsaved changes by clicking Close, then clicking No on the dialog box to discard the changes.

Running the Loader

When you have saved a planning loader with all the required configuration settings, it is then available to be run manually or as a scheduled run of an integration task.

As with other loaders, you can import data from the source system or you can run the loader using staging values already imported. At runtime, the user can select which of these behaviors they would like by choosing the Bypass data import parameter to use the existing staging table for loading. (This parameter is not available for spreadsheet data sources.)

As part of the run process, the loader checks that the data in the staging table has been mapped correctly using the following rules:

  • Any row that contains an empty account/level value causes the load to abort, with no data written to Planning and the task shows as “failed.”
  • For any row that contains an empty dimension value, the loader passes all rows including those with a blank value for the dimension) to Planning, which will attempt to import that data using the "uncategorized" value for the blank dimension value.
  • When processing rows with missing mappings for account/level/dimension for stan­dard and cube sheets, Adaptive Integration for Planning skips the row and does not write the row to Planning. It also logs details of the issue. For modeled sheets, when a row with missing mappings is encountered, Adaptive Integration for Planning aborts the load with no data written to Planning and reports the task as “failed.”

Downloading the Output of a Planning Loader

You can download the output of a planning loader to a spreadsheet without loading data into Planning. This lets you manually check the data against your requirements prior to loading it.

To download the output of a planning loader, follow these steps:

  1. On the Actions menu, select Preview loader output.
    The Preview loader output dialog box appears.

  2. Enter the preview loader output information. (The information you need to enter var­ies depending on the planning loader you’re creating.)

  3. Click Preview loader output.

The loader runs, applying all transformations and business rules as part of the run. A status pop-up (shown below) displays the steps executed as part of the loader run. If there is data to be loaded, the loader creates a spreadsheet and sends an email notification with the output of the loader as a spreadsheet attachment.

Including the Planning Loader in an Integration Task

Any Integration task can contain one or more Planning loaders. A task can also contain other integration tasks as well as Discovery metric loaders and scripted loaders, though the best practice is to have separate tasks for each loader. (Integration tasks are described in Using Tasks and Schedules.)

If a task contains multiple loaders, then parameters from each loader are presented at run time. If there is a common/shared parameter used in the loader(s) within a task, then the task only prompts for the parameter once. (You can choose to override this at run time.) For scheduled runs of the task, the default values of the parameters (the settings stored when the loaders were created) are used for running the integration task.