跳至主要内容
文档
文档
Adaptive Planning
上次更新时间 ：2025-05-09
Create Planning Data Loaders

Create Planning Data Loaders

Integration uses the Planning Data Loader to load data into Adaptive Planning.
Some links in this article go to the Workday Community. If you don't have a Community account, request one.

Prerequisites

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.

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.

Navigation

Compass.png From the nav menu, go to
Integration
Design Integrations

Enter Data Source Settings

The Data Source Settings enable you to select:
  • The source table.
  • The period settings.
  • How actuals erase.
  • How to auto map accounts, levels, and dimensions.
Best Practice: Save frequently as you create and update your loader. Select
Save
 in the Actions panel. Select
Close
, then
No
 to discard unsaved changes.
  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 page displays your new loader settings and other information.
  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.
      • Actuals
        : Enables you to load data into the Actuals version of Adaptive Planning.
      • Plan
        : Enables you to load data into any Planning or budget version defined in Adaptive Planning. (This import type isn’t available for Consolidation-only instances.)
    • Transactions
      : Enables you to load data into the Transactions module in Adaptive Planning. If multiple transaction tables exist, you can select the appropriate transaction from the drop-down. This option requires the Transactions module for your instance.
    • Import Into Sheet
      : Specify the planning sheet to load the data into, using one of these options:
      • Select
        Standard
         to load the data into standard accounts (General Ledger, Custom, or Assumption accounts).
      • Click the radio button to select a model or cube sheet from the drop-down. Modeled sheet validation rules that trigger critical errors don't import data and generate log errors indicating invalid rows.
      Changing the sheet selection in an existing loader displays a warning that doing so affects the column and members mappings. Clicking
      OK
       removes all column and data mappings from the loader. You must create new mappings for the loader to correspond to the new sheet.
    • Version Parameter
      : Enables the user running this loader to select a version to import to. Select from the version parameters you previously defined. The parameters visible in the list vary based on Import Type. You can use Version Parameters in more than 1 loader.
    • Import Options
      : Enables you to select how data updates during modeled sheet import:
      • Append New Data
        : Adds new rows without changing existing rows in modeled sheets.
      • Replace existing data in sheet
        : Replace all of the data in the sheet with the import, without updating or matching rows in modeled sheets.
      • Update existing rows
        : When a key exists and an existing row matches for a modeled sheet, update the data. If a row can't match to the import, rows don't update or append. The only required columns are Import Key and at least 1 column being updated. Sheets with
        Allow splits
         selected do not support updates. Values in the Import Key column of your imported file must be unique.
      • Update existing rows and add new rows
        : When a key exists and a row matches for a modeled sheet, update the data. If a row can't match to the import, make a new row. Only required columns are Import Key, Level, and any text selectors, even when not adding new rows. Sheets with
        Allow splits
         selected do not support updates. Values in the Import Key column of your imported file must be unique.
    • Import Key Column
      : Indicates the modeled sheet dimension or level column as the key for matching rows when importing. Values in the Import Key column of your imported file must be unique.
    • Import Secondary Column
      : Indicates the modeled sheet dimension or level column as the secondary key for rows that did not match on the Import Key Column. This option supports Workforce Planning and becomes available when:
      • The instance is configured for Workday HCM or Financials.
      • The
        Import Key Column
         uses
        Publish ID
        .
      • Selecting one of these Import Options:
        • Update existing rows only.
        • Update existing rows and add new rows.
    • Make New Actuals Visible
      : Only available when selecting
      Actuals
       for
      Import Type
      . When checked, the Actuals version has its
      Completed Values Through
       date set to the final month of data found in the import, making the imported data automatically available for overlaying plan data in plan versions. Enabling this and importing alters Actuals Overlay. You can also access
      Make New Actuals Visible
       as a parameter, to allow the user running the integration task to override this setting. Depending on the period range being imported, and the current date set on the actuals version, the import might move the
      completed
       date backwards.
    • Delete Existing Transactions
      : Only available when selecting
      Transactions
       for
      Import Type
      . When checked, all existing transactions associated with the Period parameter are deleted. Keep this checked unless a specific reason comes up to uncheck it. If you reimport data for a given period while unchecked, you end up with duplicate transaction records.
    • Log level
      : Select a log level to specify the detail for the logging for this loader. Logs automatically erase 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.)
    • GL Accounts
      : Only available when
      Import Type
       is set to
      Actuals
      , unless you configure Erase Data settings in
      Administration
      General Setup
      . If
      GL Accounts
       is selected, all data in general ledger accounts erases for the time period chosen in the loader prior to new data loading. You can also access
      GL Accounts
       as a parameter you can override at runtime. Selecting
      GL Accounts
       only deletes data in general ledger accounts but leaves data unchanged in custom accounts and in cell notes.
    • Custom Accounts
      : Only available when
      Import Type
       is set to
      Actuals
      , unless you configure Erase Data settings in
      Administration
      General Setup
      . If
      Custom Accounts
       is selected, all data in custom accounts erases for the time period chosen in the loader, prior to new data loading. You can also access
      Custom Accounts
       as a paremeter you can override at runtime. Selecting
      Custome Accounts
       only deletes data in custom accounts but leaves data unchanged in general ledger accounts and in cell notes.
    • Cube Accounts
      : Only available when
      Import Type
       is set to
      Actuals
      , unless you configure Erase Data settings in
      Administration
      General Setup
      . If
      Cube Accounts
       is selected, all data in cube accounts erases for the time period chosen in the loader, prior to new data loading. You can also access
      Cube Accounts
       as a parameter you can override at runtime. Selecting
      Cube Accounts
       only deletes cube accounts but leaves data unchanged in general ledger accounts, custom accounts, and cell notes.
    • Cell Notes
      : Only available when
      Import Type
       is set to
      Actuals
      , unless you configure Erase Data settings in
      Administration
      General Setup
      . If
      Cell Notes
       is selected, all data in general ledger accounts erases for the time period selected in the loader, prior to new data loading. You can also access
      Cell Notes
       as a parameter you can override at runtime. Selecting
      Cell Notes
       only deletes cell notes but leaves data unchanged in general ledger accounts and custom accounts.
    • Replace Mode Import Settings
      Enable Replace Mode
      : (Optional) Replaces data instead of erasing it. Removes the
      Erase Settings
       tab and disables the
      Erase manually
       option. You must select this setting to erase translated currency data. Replace mode for modeled sheets, cube sheets, and standard sheets excludes empty values at import and indicates skipped empty rows in the logs. Replace mode displays IDs instead of names in the preview loader output.
      You can only use
      Enable Replace Mode
       for imports to modeled sheets, cube sheets, and standard sheets. Any existing cell within the import scope that isn't matched in the new data (payload) will be deleted. Existing cells
      are
      overwritten by matching payload data, regardless of whether the existing cell held a value or a formula.
      Replace Mode updates your planning system data faster and cleaner, especially when intersections or data have changed. Instead of erasing all data and then loading new data, Replace Mode intelligently compares what you're importing with what's already there. Replace Mode:
      • Adds any new data.
      • Removes data that isn't in your import.
      Data that hasn't changed stays untouched. This prevents:
      • Unnecessary audit trail entries.
      • The performance cost of deleting and then recreating unchanged information.
      For most import situations, especially with a lot of your data already in the system, we highly recommend that you use a single Replace Mode loader step for a faster, streamlined process.
      • Accounts Parameter
        : Select the accounts to replace using the accounts parameter. For cube sheets, you can include all cube accounts in a sheet, or create a new account parameter to replace specific accounts. For standard sheets, you can create a new account parameter to replace specific general ledger, custom, or assumptions accounts.
      • Levels Parameter
        : Select the levels to replace using a Levels Parameter. You can include all levels, or create a new level parameter to replace specific levels. For standard data imports, you can select
        All GL Accounts
         and
        All Custom Accounts
        .
      • Cell Notes
        : Erases cell notes during replace.
  5. Enter the planning loader default period settings:
    • Period Parameter
      : You can specify the range of time periods for loading into Adaptive Planning. 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 1 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 strata based on the selection in
      Import Type
      . For import type Actuals or Plan, the time strata sets to the lowest available time strata of the sheet you import to. For import type Transactions, the time strata won't set to Day.
    • Period Column
      : Available for
      Import Type
      :
      Transactions
      . This drop-down displays all columns of type Date or DateTime available in the source table selected in the Source Table field. When only 1 column meets these criteria, this field defaults to that column.
      Period Column
       controls the mapping of time period data from the staging table, to the period defined in Adaptive Planning. You must align the data values in the staging values with periods in Adaptive Planning.
    • Posting Date
      : Available when
      Import Type
      :
      Transactions
      . Indicates the staging column that contains the Date or DateTime values for the posting date. The posting date helps with identifying and finding applicable transactions when drilling through transactions data submitted to Adaptive Planning.
  6. Create a Version Parameter.
    You must use a version parameter to specify at runtime which version to load the data into. A version parameter gives you greater flexibility when setting up a planning data loader, by enabling you to 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. The version tree you can select from varies depending on the type of the parameter. The versions displayed at runtime restrict based on the user running the loader.

Creating an Actuals Version Parameter

You can set up version parameters of either actuals or plan type. The different types specify where you want to load the data.
  1. Click
    Edit parameters
     below the
    Version
     Parameters drop-down.
  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
    .
  4. From the context menu, click
    Actuals Version
    .
  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.
    • Actuals 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 1 Actuals version, which is the only available selection in this dialog.
  6. Click
    Apply
     to apply the settings.
  7. Click
    Close
    to return to the Parameter editor dialog box. The actuals version parameter 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 actuals version parameter.
  1. Click
    Edit parameters
     below the Version Parameters drop-down.
  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
    .
  4. From the context menu, click
    Plan Version
    .
  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.
  6. Click
    Apply
     to apply the settings.
  7. Click
    Close
     to return to the Parameter editor dialog box. The plan version parameter you have just created is now visible in Version Parameters drop-down list.

Creating a Period Parameter

The period parameter specifies the range of time periods for which data will be loaded into Adaptive Planning. You can override the assigned time periods at runtime. You can select a single period, or set start and end periods that are either dynamic or fixed. 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:
  • Offset
    : A positive integer representing how many Time Strata to offset from the current date.
  • Time Strata Selector
    : A drop-down 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.
  1. Click
    Edit parameters
     below the Period Parameters drop-down.
  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
    .
  4. From the context menu, click
    Period Range
    .
  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 strata (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 strata.
      • 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 strata (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 strata.
      • Select Fixed to specify any time period from your time structure in Planning. The resolved date for your selection appears beneath the dropdowns.
  6. Click
    Apply
     to apply the settings.
  7. Click
    Close
     to return to the Parameter editor dialog box.
    The period range parameter you created becomes visible in the Period Parameter drop-down list.

Dynamic Period Example

Assume the current month is April.
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

For a fixed Start Period of November 1, 2016 and a fixed End Period of March 31, 2017, select the start and end time periods from the Adaptive Planning 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 Column, you can automap:
  • Accounts by Account Name or Account Code
  • Levels by Source ID to Code
  • Levels by Source ID to Name
  • Dimensions by Source ID to Code
  • Dimensions by Source ID to Name
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
  • Levels by Source ID to Name
  • Levels by Source ID to Code
  • Levels by Source Display Name to Name
  • Levels by Source Display Name to Code
  • Dimensions by Source ID to Name
  • Dimensions by Source ID to Code
  • Dimensions by Source Display Name to Name
  • Dimensions by Source Display Name to Code
If the loader encounters data elements it can't automap, the loader errors out.

Erase Data Settings

The
Erase Settings
 tab only displays after selecting
Enable Integration Loaders - Erase Data
 in
Administration
General Setup
.
You must have
Import Capabilities
Erase Actuals
 permission enabled in Adaptive Planning to erase actuals data. Erasing actuals is a superuser permission that enables erasing actuals or plan data across Adaptive Planning, including in locked levels.
Erase Actuals
 overrides Access Rules and level ownership restrictions.
You can erase plan or actuals data independent of the data being loaded. You can also choose to erase data for specific time periods, versions, levels or accounts. You can't erase translated currency data. Instead, select the Enable Replace Mode check box and replace the data.
Erase Data Details
  • Erase Data only erases data from non-virtual plan or actuals versions.
  • Erase Data can erase data from calculated accounts with a data entry override for versions that are set up for data entry.
  • Selecting a parent level deletes all data in its child levels.
  • Selecting a rollup account deletes all data in its child accounts.
  • Formulas will not be deleted.
  1. Click the
    Erase Settings
     tab.
  2. Select the
    Enable Data Erase
     check box to erase data.
  3. Select
    Edit Parameters
     in the
    Actions
     pane and create Version and Period parameters.
    You can also to choose to use the
    Version
     or
    Period
     parameters specified in the
    Data Source Settings
     tab.
  4. Select the accounts you want to erase data from.
    You can include all GL accounts, all custom accounts, all cube accounts in a sheet, or create a new account parameter to erase specific accounts. You can also select a combination of all the available account options.
  5. Specify levels for erasing data.
    You can select all levels or or create a new level parameter for specific levels.
  6. Run the loader manually using
    Erase Manually
     in the
    Actions
     pane or schedule the loader to run as a part of a task.
    If more than 1 planning loader exists within a task, then each of the erase options become available for each included loader. The options tag with the name of the loader to help you identify the source of the erase option.
With
Erase Settings
, you can erase data from cube assumption accounts. If the request includes an account code used by both an assumption and an account, the loader returns an error and won't erase anything.

Select a Mapping Profile

You may need to load data with similar account/dimension value information from separate GL/ERP/CRM systems. This could require similarly named source-side members as 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 Planning, where there can be no two different 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 easily create a large number of mappings. If there are multiple planning loaders in the same Integration instance, the loaders can link to either the default mapping profile, or can link to a different mapping profile.
Most users do not need to use something other than the default mapping profile. 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.
  1. After completing the
    Data Source Settings
    , select the
    Profile
     tab.
  2. Select the mapping profile you want to use.
You can create a new mapping profile by clicking
New
 at the bottom of the page. You then enter the name of the new mapping profile and click Save. You can also clone a profile from an existing mapping profile.
Cloning copies all the mappings from an exisn’tting profile into a newly cloned profile. This helps 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.
Mapping Profile Tips
  • 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
    Clone
    .
  • If you change the linked profile from a named profile to the default profile, only new mappings come in. Integration does not check for conflicts. However, if the default profile doesn't have any staging values that exist in the staging columns mapped in this loader, then these staging values come into the default profile as unmapped values when you click the
    Data Mapping
     tab.

External Systems

You can double click a profile in the Profiles tab to associate that profile with an external system to allow drill-through on Adaptive Planning sheet and report data.
When a user clicks a drill-through link, Adaptive Planning connects to the external system and reveals the underlying transactions for the data.
Associating a profile with an external system is optional. Only set up an external system to enable drillback to Workday or NetSuite drill-through in Adaptive Planning.
Do not associate a profile with an external system if you do not plan on using Drill-through in Adaptive Planning.

Basic Steps for Creating an External System

  • Create an external system and give it a name.
  • Set up external system settings so that Adaptive Planning 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 Planning.
  • Set up tuple SQL columns if multiple external columns map to one Adaptive Planning 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 link a profile in the Profiles tab, you must map the staging columns to columns in Adaptive Planning.
You cannot perform column mapping until you link a profile.
To map staging columns to Adaptive Planning columns:
The Column Mapping page has four columns. You can complete the entire mapping process with just the Source Id Column. The only requirement is that the Source Id column must contain unique values from the data source. In cases where the information in the Source Id Column is 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 values are used in the member mapping UI; otherwise, the values from the Source Id Column are used.
  1. Select the
    Column Mapping
     tab.
    • Status
      : Indicates if a particular Planning column is mapped.
    • Key
      : Indicates if the Planning Column participates in an Import key for a modeled sheet by displaying a key icon.
    • Planning Column
      : Displays all the Planning columns available for mapping on a sheet. The list of Planning columns includes a set of mandatory Planning columns, followed by all other dimensions defined in the model. Map only the dimensions in the Planning columns 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. The account will display on sheets and reports by its name when creating an account mapping in a loader. The exception is in Matrix reports where you have the option to display an account code next to the account name. Non-mandatory 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.
    • Source Id Column
      : Displays all columns within the staging table chosen in the data source settings.
    • Source Display Name Column
      : Displays optional display names to simplify the mapping process.
  2. Select an unmapped column and click the down arrow. A list of columns you can map to appears in the drop-down.
  3. 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.
If there are a lot of Planning columns to map, you can use the Show filter at the upper left of the page. 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 and click the magnifying glass. Any staging columns matching the search 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 Planning. 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.
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.
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 to NetSuite based on NetSuite's standard categories of
Account, Time Period, Subsidiary, 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 Planning.
  • Not Mapped Unmapped: Staging members that are not mapped to a planning member in Adaptive Planning.
  • Invalid Mapping Invalid: Staging members that were mapped at some point to a planning member, but that planning member was deleted in Adaptive Planning.

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 Planning.
You can import and map calculated accounts with data entry override for a plan version.
  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 Planning.
  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
    Upload Mappings
    .
  7. Select
    Download Template
     so that you now have two downloaded files: a downloaded mappings file and a downloaded template file.

Update Downloaded Mappings and Paste into Excel Template File

  1. Open the downloaded mappings Excel file and make all your changes.
  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 updated 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 dimension 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 dimension'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 filters

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 Planning.
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
    Add Skip
    Account Code Range
    dialog box.
  3. Enter the account number or account number range.
  4. Select
    Save Rule
    . Saved skip account rules appear on the Business Rules page.
To create several skip account rules at once, select
Create Another
. When you then click
Save Rule
, the skip account rule saves and the
Add Skip Account Code 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
    .
  3. 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 the link in the Notes section. The expression you entered appears in the Business Rules page.
For additional information, click the
online help
 link in the
Notes
 section of the
Edit SQL Filter
 dialog box.

Tips and Techniques

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 isn’t 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 these 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 standard and cube sheets, integration 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, integration aborts the load with no data written to Planning and reports the task as failed.
If your load fails when reparenting levels and you receive the warning
Level
levelname
 cannot be moved with Proceed with Warnings set to 0
, select
Proceed with Warnings
 and run the loader again.

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. Select
    Preview loader output
     in the
    Actions
     pane.
  2. Enter the preview loader output information.
    The information you need to enter varies 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 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 select to override parameter prompts.
For scheduled runs of the task, default values of the parameters are used.