Skip to main content
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday company.

Create a Planning Data Loader

Adaptive Integration for Planning uses a planning loader.

To create a planning loader:

  1. Go 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 Insights.
    • Plan: Lets you load data into any Planning or budget version defined in Adaptive Insights. (This import type is not available for a Consolidation-only instance.)
  • Transactions: Lets you load data into the Transactions module in Adaptive Insights. If there are multiple transaction tables in Adaptive Insights, you can select the appropriate transaction from the dropdown. (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. You can use Version Parameters in more than one loader.
  • 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.
  • 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. Keep this checked unless there is a specific reason to uncheck it. If you reimport data for a given period, and this is unchecked, you end up with duplicate transaction records.
  • Log level: Select a log level from the drop-down list to specify the detail for the logging for this loader. Logs automatically get erased after a week. If you need logs for debugging, download them.
    • 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 Insights. See the Creating a Period Parameter section that follows for more on how to create a period parameter. You can use Period Parameters in more than one loader.
  • 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 the period defined in Adaptive Insights. You must align the data values in the staging values with peri­ods in Adaptive Insights.

  • 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 Insights.

  1. Enter the erase actuals settings.

    When you define an actuals import, 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 must 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 data 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.

For consolidations, select which of the Actuals sub-versions is used as the default. For 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 Insights. 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. When a scheduled task runs, it uses these settings to decide what periods to import. Dynamic dates automatically adjust as months go by.

Dynamic Period

A dynamic period allows configuring an offset from the current time in configured planning 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.

Auto Mapping

You can select auto mapping options to automatically map new data elements detected at loader run time. Auto mapping can replace the manual steps you take in the Data Mapping tab for:

  • Accounts
  • Levels
  • Dimensions

Existing loaders and any new loaders you create do not automap by default.

If your Data Mapping doesn't include a selection for Source Display Name, you can automap:

  • Accounts by Account Name or Account Code
  • Levels by Source ID
  • Dimensions by Source ID

Auto Mapping - No Data Mapping Selections

If your Data Mapping does include a selection for Source Display Name Column, you can automap:

  • Accounts by Source ID to Account Name
  • Accounts by Source ID to Account Code
  • Accounts by Source Display Name to Account Name
  • Accounts by Source Display Name to Account Code
  • Levels by Source ID
  • Levels by Source Display Name
  • Dimensions by Source ID
  • Dimensions by Source Display Name

Auto Mapping - Data Mapping Selections

If the loader encounters data elements it cannot automap, the loader errors out.

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.

To use the Default profile you must explicitly link to it, even if it's the only profile available.

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 Workday, 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

You can set up an external system and associate it with a mapping profile to allow drill-through on Adaptive Insights sheet and report data. When a user clicks a drill-through link, Adaptive Insights connects to the external system and reveals the data's underlying transactions. 

Associating a profile with an external system is optional, and should only be set up to enable drillback to Workday or NetSuite drill-through in Adaptive Insights.

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

Basic Steps for Creating an External System
  • Create an external system and give it a name.
  • Set up external system settings so that Adaptive Insights knows where to connect and what account or credentials to use.  
  • Set up external dimension mappings to identify how dimensions on the external system map into the dimensions in Adaptive Insights.
  • Set up tuple SQL columns if multiple external columns map to one Adaptive Insights dimension
Set Up a Workday External System

Follow the steps described here.

Set Up a NetSuite External System

Follow the steps described here

Configuring Column Mapping

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

To map staging columns to Adaptive Planning columns:

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 the Source Id 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:

    !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 dimensions defined in the model. You don't need to map all dimensions in the Planning Columns. Map only the ones you want to import. Unmapped columns won't import anything when the loader runs. 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:

    !AI4P_column_mapping_columns.png
  2. As you map the columns, the status indicator changes from red to green. You do not need to map every dimension. Map only the ones you need to import. Unmapped columns will not import. Examples of 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.

When you see red exclamation marks for non-mandatory Planning columns in Column Mapping they do not indicate an error. Red exclamation marks simply mean you did not provide a mapping and nothing will import for those Planning columns when you run the loader.

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.

If you are importing data from NetSuite and want to configure drill-through into NetSuite based on the standard categories Account, Time Period, Sub­sidiary, Department, Class, Location, Item and Customer, you must configure the NetSuite internal identifiers for each of these dimensions as part of your column and data mapping steps.

Remember to either have all accounts, levels, and dimensions mapped, or to have explicit business rules to filter out records containing unmapped values. If a loader encounters an unmapped value, and there’s no filter to exclude that value from the final import, a mapping error is reported, and the final status of the loader will be “failed”. 

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:

  1. Click the Data Mapping heading. The Data Mapping overview screen appears:

    !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 display in red and mapped members display 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 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. 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 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:

  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.
  • 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.
  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:

  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 AllThe Confirm Automap dialog box displays:

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

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:

  1. Click the Data Mapping heading. The Data Mapping overview screen displays.

  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. 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 of the hierarchical view of the Planning dimension (such as accounts, levels, or a custom dimension) displays:

    !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:

  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.
  • Staging Value: Enter a source level not currently present in the staging table.
  • Planning Value: Select a Planning level from the drop-down.
  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 members are mapped.

To automap levels:

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:

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

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 Insights columns:

  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. Unmapped members display in red and mapped members display 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 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) displays:.!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:

  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 displays:

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

  • Staging Column: Select a staging column from the drop-down.
  • 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.
  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:

  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:

  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.

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:

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

  2. Click Upload Account Mappings on the popup menu.
    The Upload Mappings dialog box displays:

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

The following download options are available:

  • 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

For numeric formatted accounts you can specify which accounts or ranges of accounts need to be skipped during a data load process. These accounts can only contain numeric digits. 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 skip accounts that are in alpha-numeric format, use the SQL filter in Business Rules.

To create a skip account rule:

  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 filters:

  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.

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 loader to complete with status "failed." The loader will complete importing all of the data it can. If the loader is part of an integration task, the failed status causes the task to terminate and not run any additional loaders after the one with the "failed" status.
  • 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:

  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 override the value of the parameter 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.

  • Was this article helpful?