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

Create a Planning Dimension Loader

Contains preview content for the upcoming 2018.1 release. 

The Planning Dimension Loader limits the number of updates to a maximum of 10000 dimension values. Please contact Support to have the Planning Dimension Loader enabled for your instance.

The Planning Dimension Loader lets you map metadata about dimension values from the staging area into Adaptive Planning. The primary benefit of the Planning Dimension Loader is that it allows you to import dimensions values in bulk, instead of creating them one at a time within Modeling > Model Management > Dimensions. Once you create a loader, you can run it manually or schedule it to run as part of an Integration task.

Planning Dimension Loader
 

To create a Planning Dimension Loader, follow this flow:

Enter Data Source
Settings
→︎
Configure Column
Mapping
→︎
Create Business
Rules
→︎
Preview the Loader
Output
→︎
Run or Schedule the
Loader

Before You Start

The Planning Dimension Loader requires staging data from an import of an existing Data Source

Enter Data Source Settings

The Data Source Settings let you choose what source table you want to use from Integration's staging data, select what Planning Dimension to load to, and whether or not new dimension values should be created automatically. 

  1. Navigate to Integration > Data Designer.
  2. Click Create New Loader In the Loaders area of the Component Library.
  3. Select Planning Dimension Loader as the loader type. Enter a name for the loader.
  4. Click Create.

Best Practice: Save frequently as you create and edit your loader by clicking Save in the Actions panel. You can discard any unsaved changes by clicking Close, then clicking No.

Enter the Planning Dimension Loader general properties information:

Planning Dimension Loader - Data Source Settings 

  • Source Table: Select the source table from the dropdown list. The source tables in the list are all the tables available in the staging area you have access to.
  • Planning Dimension: Choose a dimension from all of the dimensions for the source to load into. The dimensions listed come from Modeling > Model Management > Dimensions.
  • Create dimension value if not found in Planning: Check the box to automatically create new dimension values found in the source system, but not found in Modeling > Model Management > Dimensions. If this option is not checked, only updates to existing dimension values are performed and no new dimensions are created.
    • The dimension you load into must also have 'Data import automatically creates dimension values' enabled in Modeling > Model Management > Dimensions. If the dimension does not have this enabled, the loader will warn you:
      Planning Dimension Loader - Data import automatically creates dimension values warning
  • Log Level: Choose the type of details logged when running the loader.
    • Error: Log serious errors only.
    • Info: Log basic information, such as when the loader was updated.
    • Verbose: Provide 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.

Configure Column Mapping

Column Mapping lets you map columns from the data source to dimension values available in Modeling > Model Management > Dimensions.

If there are several columns to map, you can use the Show filter to select:

  • 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 columns. Enter a complete or partial name to search for and click the magnifying glass. Any staging columns matching the search appear in the results.

Map Columns

  1. Click Column Mapping.

Planning Dimension Loader - Column Mapping

The Column Mapping tab has these columns:

  • Status: Indicates if a Planning column is mapped. A checkmark Mapped Column indicates a mapped column. An exclamation point Unmapped Column indicates an unmapped column.
  • Planning Column: Displays all of the Planning columns available for mapping on a sheet. The list of Planning columns include a set of mandatory Planning columns, followed by all other Planning columns. Mandatory Planning columns are indicated by bold text with an asterisk and include a value column. Non-manda­tory columns include the Short Name and Description.
  • Source Id Column: Displays the columns within the staging table chosen from the Data Source Settings.
  • Source Display Name Column: Indicates the display names to simplify the mapping process.
  1. Select an unmapped column and click the dropdown. A list of columns you can map to appears in the dropdown list. As you map the columns, the status indicator changes from red IntegrationMappingUnmapped.png to green IntegrationMappingMapped.png.

See Load Dimension Values From a Spreadsheet Data Source for an example of how dimension values and their parents map to Planning.

Unmap Columns

To unmap columns, click Unmap and choose:

  • Unmap All: Unmaps all of the the columns.
  • Unmap Selected: Unmaps only the columns that are selected.

Unmapping a column changes its map status icon to red IntegrationMappingUnmapped.png.

Map Attributes Associated with List Dimensions

You can associate attributes with a list dimension by mapping columns from a data source import.

A list dimension is a dimension that is entirely non-hierarchical. It has no sub-dimensions and no hierarchy of dimension values, only a flat list of values.

Associating attributes with dimensions is only possible for list dimensions. You cannot associate attributes with hierarchical dimensions.

Within the Adaptive Suite you can visit Model Management > Dimensions and click a dimension to view its details to find out if it is a list dimension.

 Dimension Detail - List Dimension

Within Data Source Settings, select a list dimension as the Planning Dimension.

Planning Dimension Loader - Data Source Setting - List Dimension

Attributes become available as columns within Column Mapping because the dimension is a list dimension.

Because list dimensions are not hierarchical, parentId is not an option for the Source Id Column.

Leave the Source Display Name Column blank.

Planning Dimension Loader - Map Attributes Associated with List Dimensions

Create Business Rules

You can use Business Rules to create SQL expressions that limit what data in staging is available for loading. Only records that meet your filter criteria will load.

Planning Dimension Loader - Business Rules

To create an SQL filter:

  1. In the Business Rules tab, select SQL Filter. Click Edit.

Planning Dimension Loader - BusinessRules - SQL Editor

  1. Enter an SQL expression. You can click an item in the Available Columns list to bring that column into the SQL expression instead of typing it.

  2. Click Apply to check your SQL syntax. Errors in your syntax turn the border around the expression red. Hover your cursor over the SQL editor to see syntax error information.

Planning Dimension Loader - SQL Syntax Error

For detailed SQL syntax help, you can click 'online help' in the Notes sec­tion of the Edit SQL Filter. You can also visit the SQL Expression Reference.

  1. Correct any errors in your syntax and click Apply. Only staging rows that match the SQL expression will import when you run the loader.

Preview the Loader Output

You can preview the loader output before performing a full load to check that the mappings are the way you need them. You can also download its output as an XML file to manually verify the dimension values from your data source against your requirements before loading them.

  1. Click Preview loader output in the Actions panel

  2. (Optional) Enter Preview Loader Output settings. The information you can enter var­ies depending on the data source you chose for this Planning Dimension Loader. Changing the settings here imports new data into Staging tables and columns.

  3. Click Preview loader output.

The loader runs, applying all transformations and business rules as part of the run. A status popup displays the steps the loader executed. If there are dimension values to load, the loader creates an XML file and sends an email notification with the output of the loader as a zipped XML attachment.

An additional popup lets you download the preview loader output as a zipped XML file after the loader succeeds. The download popup may take a moment to appear depending on the number of dimension values and the size of the file.

Planning Dimension Loader - Preview Loader Output - XML Download

Preview Loader Output - XML of Metadata

Fix Validation Errors

When dimension values are loaded, the source data is validated for the following errors:

  • The source Id value for the dimension value is missing (empty) in the data.
  • There are duplicate Source Ids in the data. Source Ids must be unique.
  • A dimension value name is missing (empty) in the data.
  • There are duplicate dimension value names in the data.
  • A ParentId is the same as the Source Id in the data. In this case, the column is referring to itself as its parent.
  • When importing a sub tree, the root of the sub tree maps to an existing node in Planning. In this case, the parentID is not in the source data for a new node being introduced to Planning.
  • An existing node is moved under a new node and the Create New flag is disabled. In this case, an existing dimension value is moving into a new node when the loader has been explicitly told not to make new dimension values.
  • The data has a cyclic reference in its hierarchy. In this case, a ParentId is referring to its own child as its parent.

Run the Loader

After saving a Planning Dimension Loader with all the required configuration settings, the loader can be run manually or run as a scheduled integration task.

Wait about 15 - 30 minutes between each Planning Dimension Loader run so that the Adaptive Suite has time to synchronize with your import. Larger imports take longer to synchronize. Wait time depends on model size and other factors.

You can import directly from the source system or you can run the loader using staging values that were already imported. When the loader runs, select which of these behaviors you want. Choose Bypass data import to use the existing staging table for loading. Bypassing data import is not available when using spreadsheets as data sources.

As part of the run process, the loader checks that the data in the staging table has been mapped correctly. Multiple validations are applied during the load. Any errors that occur during the load indicate what needs to be resolved for a successful import.

Include the Planning Dimension Loader in an Integration Task

Any Integration task can contain one or more loaders. A task can also contain other integration tasks as well as Discovery metric loaders and scripted loaders.

Best Practice: Have separate Integration tasks for each loader.

If a task contains multiple loaders, then parameters from each loader are presented when the task is run. If there is a common/shared parameter used in the loader(s) within a task, then the task only prompts for the parameter once. You can choose to override parameter prompts. 

For scheduled runs of the task, the default values of the parameters stored when the loaders were created are used.

Troubleshooting Messages and Warnings

For an explanation of the messages logged during a load, see Planning Dimension Loader Log Messages.