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

Create a Planning Data Loader

Adaptive Integration for Planning uses the Planning Data Loader to load data into Adaptive Insights.

Before You Begin

The Planning Data Loader requires staging data from an import of an existing Data Source. Make a note of what data source you want to use.

Basic Steps

  1. Enter data source settings.
  2. Select a mapping profile.
  3. Configure column mapping.
  4. Configure data mapping.
  5. Create business rules.
  6. Run or schedule the loader.

How You Get There

Compass.png Go to nav menu navicon.gif > Integration > Data Designer

Enter Data Source Settings

The Data Source Settings let you choose the source table, select the period settings, select how actuals erase, and select how to auto map accounts, levels, and dimensions.

Best Practice: Save frequently as you create and edit your loader. Select Save in the Actions panel. To discard any unsaved changes select Close, then  No.

!AI4P_default_planning_loader_fields.png 

  1. Select Create New Loader in the Loaders section of the Component Library.

  2. Select Planning Data Loader as the loader type. Enter a name for the loader.

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

  4. Enter the Planning Data Loader 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.

Select a Mapping Profile

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 Adaptive Insights, 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

Configure Column Mapping

After you linked a profile in the Profiles tab, you must map the stag­ing columns to columns in Adaptive Insights.

You cannot perform column mapping until you link a profile.

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.

Configure Data Mapping

After you configure the settings for the loader and map staging columns to planning columns, map the accounts, levels, and dimension members within those columns to their corresponding planning column members in Adaptive Insights. The data mapping overview summarizes the mapped, unmapped, and invalid staging members, showing numbers for each. Select a number to navigate into the data mapping page for that category and status of mapping.

Planning Data Loader - Data Mapping Overview

Once you navigate into the account mapping, level mapping, or dimension mapping page within the data mapping tab, you can see the mapping status for every member in that category.

Planning Data Loader - Data Mapping - Level Mapping

Map all accounts, levels, and dimensions, or create explicit business rules to filter out records containing unmapped values. If a loader encounters an unmapped value during final import, the loader status errors out as Failed. 

 Requirements for Data Mapping and Drilling Into NetSuite

If you import data from NetSuite and want to configure drill-through into NetSuite based on NetSuite's standard categories of 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.

Data Mapping Status

Each member in an account, level, or dimension data mapping category gets one of these statuses:

  • Mapped Mapped: Staging members that map to a planning member in Adaptive Insights.
  • Not Mapped Unmapped: Staging members that are not mapped to a planning member in Adaptive Insights.
  • Invalid Mapping Invalid: Staging members that were mapped at some point to a planning member, but that planning member was deleted in Adaptive Insights.

Map Account, Level, and Dimension Staging Column Members

You can map individual account, level, or dimension members within the staging columns to their corresponding planning members in Adaptive Insights.

  1. Select Data Mapping.
  2. Select a linked number in the Data Mapping Overview page or select the category link for Account Mapping, Level Mapping, or Dimension Mapping.
  3. Select a mapping status in the Display drop-down. Display shows all mappings by default.
  4. (Optional) Enter a term in Search to filter the results for your display selection from the previous step.
  5. Select a Planning Value for a Staging Value by browsing or searching the hierarchical view of Adaptive Insights.
  6. Select Apply.
  7. Select Save.

Create a New Data Mapping

You can create a new mapping in anticipation of a new data import containing new staging members. New mappings help with scheduled loader runs when you know that a particular member value will appear in a future import. Completing a new mapping prevents the loader from failing due to an unmapped or invalid value.

  1. Navigate to the Account mapping, Level Mapping, or Dimension Mapping page within Data Mapping.
  2. Select the gear icon datamappingssettingsicon.png for mapping settings.
  3. Select New Account, Level, or Dimension Mapping.
  4. Enter a Staging Value exactly as you expect it to appear in your future import. For a new account mapping, also enter the staging display value for the account.
  5. Select a Planning Value to map to the Staging Value you selected in the previous step.
  6. Select Apply.
  7. Select Save.

Upload Data Mappings

You can download a mappings template to upload mappings for account data mappings, level data mappings, or dimension data mappings.

  1. Navigate to the Account mapping, Level Mapping, or Dimension Mapping page within Data Mapping.
  2. Select the gear icon datamappingssettingsicon.png for mapping settings.
  3. Select Upload Mappings.
  4. Select Download Template in the Upload Mappings dialog.
  5. Open the downloaded template Excel file and follow the instructions in the first sheet tab to populate the second sheet tab in the sheet with your mappings. Save the template.
  6. Select Browse and locate your edited Excel template file on your local computer.
  7. Select Import.
  8. Select Apply.
  9. Select Save.

Download Data Mappings for Bulk Edits

You can download mappings to bulk edit and upload them. Use a downloaded mappings file to fill in a downloaded template file. For account data mappings, level data mappings, or dimension data mappings you can download:

  • All mappings
  • Mapped Only
  • Unmapped Only
  • Invalid Only
  1. Navigate to the Account mapping, Level Mapping, or Dimension Mapping page within Data Mapping.
  2. Select the gear icon datamappingssettingsicon.png for mapping settings.
  3. Select Download Mappings.
  4. Select a download filter and wait for the system to prepare your download and enable the download button.
  5. Select Download.
  6. Select the gear icon again and select Import Mappings.
  7. Select Download Template so that you now have two downloaded files: a downloaded mappings file and a downloaded template file.

Edit Downloaded Mappings and Paste into Excel Template File

  1. Open the downloaded mappings Excel file from and make all of your edits.
  2. Copy all of the content from this file.
  3. Paste the content into sheet two of the downloaded template excel file.
  4. Save your populated Excel template file.

Upload the Populated Excel Template File

  1. Select the gear icon datamappingssettingsicon.png and select Upload Mappings in the Planning Data Loader data mappings page for accounts, levels, or dimensions.
  2. Select Browse and select your edited Excel template file.
  3. Select Import.
  4. Select Apply
  5. Select Save.

Automap, Unmap, and Delete Data Mappings

Automap Data Mappings

You can automap staging members to planning members. The system searches for planning members that match staging members by name. For accounts, the system matches by account code or account name. Automap will attempt to map both unmapped or invalid mappings. When an exact match happens, the staging members map automatically.

  1. Select the members to automap by selecting the checkboxes on member rows.
  2. Select Automap.
  3. Select Automap Selected.

Automap all unmapped members by selecting Automap All.

For accounts, you can select to automap by source ID column, or by source display name column. For both you can automap by Account Code or Account Name.

Custom dimension members always automap using dimen­sion member names. You will not see an Automap dialog when you automap dimension members.

Unmap Data Mappings

Unmap one or more mappings by selecting Unmap then selecting Unmap Selected. Unmapping disconnects the mapping between staging and planning members. You can disconnect all of the mappings at once by selecting Unmap All.

Delete Data Mappings

You can delete a mapping and its member information by selecting Delete and then Delete Selected. Deleting removes the member information completely, so that you can start over with another staging table import. Select Delete All to delete all of the members and their information.

You cannot retrieve any information from a deleted mapping.

Restrictions on Importing Member Data Mappings

Accounts

  • Group accounts, parent (roll up) accounts, system accounts, and metric accounts can not map.
  • You can import to a modeled sheet, but you can not import to modeled accounts because they are formula-driven and read-only.
  • You can not use the standard import to import data to GL (general ledger) or Custom accounts entered in cube sheets. You must use a cube sheet import for cube sheets where Custom accounts show up.

Levels

  • Users with the Import Capabilities permission can import data into all levels, regardless of level ownership/access.
  • Linked levels can’t map and can’t import data.
  • Cube and modeled sheets show the full level hierarchy but only let you select levels assigned to those sheets. If a sheet isn’t available on a level, that sheet can not hold any data for that level.

Dimensions

  • You can map to any dimension for standard accounts (GL, custom, and assumption accounts).
  • Cube accounts can only map to dimension values and dimensions on cube sheets.
  • You can create mappings for modeled sheet selectors.
  • The import mapping namespace for dimension values shares across standard, cube, and modeled sheets.
    • If you create a mapping for standard import for a dimen­sion’s dimension value, and the dimension value also resides on a cube sheet, the standard import mapping applies to that dimension value.
  • If a dimension is set to automatically create dimension members on data import, then staging columns mapped to that dimension don’t need to be mapped. These dimensions don’t show up in the dimension mapping page because they will autocreate at import.

Create Business Rules

Business rules let you specify how to import data. You can create three types:

  • Skip accounts rule
  • Change signs rule
  • SQL fil­ters

Create 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 uploading to Adaptive Insights.

This rule applies to 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 alphabetic, numeric, and some special characters:

  • period (.)
  • underline (_)
  • hyphen (-)
  • colon (:) 

Rules for account ranges can only use numeric characters. You can create as many skip account business rules as you want. To skip accounts that are in alpha-numeric format, use the SQL filter in Business Rules.

  1. Select Skip Accounts in Business Rules.

  2. Select Add to open the account range dialog box.

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

  4. Select Save Rule. Saved skip account rules appear on the Business Rules screen.

To create several skip account rules at once, select Cre­ate Another. When you then click Save Rule, the skip account rule saves and the Add Skip account range dialog box stays open.)

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

Create an 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.

Run or Schedule the Loader

When you save a planning data loader with all of the required settings, you can run manually or schedule it to run as an Integration Task.

As with other loaders, you can import data from the source system or you can run the loader using staging values you already imported. At runtime, you can select which of these behaviors you want by choosing the Bypass data import parameter to use the existing staging table for loading.

Bypass data import is not available for spreadsheet data sources.

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

  • Any row that contains an empty account/level value causes the loader to complete with a Failed status. 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

With a data source set up, you can create an Integration Task that runs this loader on a schedule automatically.

Any Integration Task can have one or more loaders. A task can contain other tasks, like a Planning Data Loader, a Planning Account Loader, or a Scripted Loader.

Best Practice: Have separate Integration tasks for each loader.

If a task contains multiple loaders, parameters from each loader display in a prompt when the task runs. If there is a common or shared parameter in the loaders within a task, the task prompts for the parameter only once. You can choose to override parameter prompts. 

For scheduled runs of the task, default values of the parameters are used.

 

  • Was this article helpful?