跳至主要內容
文件
文件
Adaptive Planning
Example: Load Levels From Spreadsheet Data Sources

Example: Load Levels From Spreadsheet Data Sources

This example illustrates loading a list of levels from a spreadsheet data source into
Adaptive Planning
 using the Planning Level Loader. It assumes a level called
Total Company
 already exists as the top level of the organization:
The end goal is to add two new levels
Sales - South East
 and
Sales - South West
 under the existing level
Sales - South
. After loading, the level structure you see in the Levels admin will end up looking like:

Total Company
|__Company A (100% owned)
   |__Operations
      |__United States
         |__Sales - North
         |__Sales - South
            |__Sales - South East
            |__Sales - South West
         |__Sales - East
         |__Sales - West
         |__Services - East
         |__Services - West
         |__Support
      |__Canada
      |__United Kingdom
...

Format the Levels Spreadsheet

Spreadsheets used to import into the Planning Level Loader require specific columns to indicate which levels are parents and which are children.
These columns are required:
  • id:
     The unique identifier for the level in the source system.
  • Level:
     The name for the level in the source system.
  • ParentId:
     The unique identifier for the parent level in the source system.
The id column uniquely identifies each level. The ParentId column indicates the id of a level's parent. To load to the highest level in a hierarchy, ParentId must be empty. ParentId must also be empty If you want to create a child level underneath the top level.
CRM Customer Spreadsheet
Option
Description
1
ParentId column
2
Sales - South
3
Sales - South East and Sales - South West
If you have duplicate names for levels, you need an additional column in the data source XSLX file for Code. Code uniquely identifies levels when duplicate level names exist.
ParentId value is blank for
Company A (100% owned)
. This means
Company A (100%)
 is the child of the highest level in the organization,
Total Company
. To load new levels beneath id columns of
Sales - South
, the rows for
Sales - South East
 and
Sales - South West
 reference
Sales - South
 in the ParentId column. These two new levels will get
Sales - South
 as their parent.
Always include the full level tree of parents above the levels you intend to load. The loader needs to know how to distinguish levels with the same name that sit under different parents. An organization can have a West for Sales and a West for Marketing. Loading the full tree of parents is how to handle duplicate names in different levels.
Boolean columns, like in workflow, elimination level, and elimination trading partner require either a 1 for true, or a 0 for false. Other characters should not be used in boolean columns.

Create a Data Source for the Planning Level Loader

Create a spreadsheet data source and name it, such as Levels Sheet.
Import the sheet contents for the data source to populate the staging area.

Configure Planning Level Loader Settings

Configure the loader after the spreadsheet data source is created and its data populates into the staging table.
Data Source Settings
The Source Table is the Levels Sheet XLSX data source worksheet name,
Sheet1
. If there were more than one sheet in this XLSX file, additional names would show up under Levels Sheet. This example only uses the Levels Sheet data source, but you can see the other data sources in the list. Planning level loaders can access any staging data, regardless of the data source.
Enable
Create level if not found in Planning
. This setting makes new levels if the loader can't find levels with the same name in
Adaptive Planning
.
Column Mapping
Because of the data source spreadsheet's format and columns, the column mappings for this loader must be:
Planning Column
Source Column
Parent Id
ParentId
Level Id
id
Level Name
Level
Remember, the Source Column references a unique id for each level. The Source Column Name row references the name of the level.
This example does not need any business rules.
Click
Save
 in the Actions Panel.

Preview the Loader Output

Once the loader is saved, click
Preview Loader Output
 in the Actions Panel.
Review the Preview Loader Output Status to verify that no errors occur.

Run the Loader

With a successful preview of the loader output, this loader is ready to load. Click
Run manually
 in the Actions Panel to run it.
The loader is also ready to be included in a scheduled integration task that can run automatically.

View the Loaded Levels

This screenshot illustrates the results of loading the levels.
Loaded Levels Example
Option
Description
1
Expanded tree within
Modeling
Model Management
Levels
2
Spreadsheet formatted for import
3
ParentId column.
If the ParentId column is left blank, the top level of the hierarchy, Total Company, becomes the parent.

Reparent Levels

To reparent existing levels with the loader, provide the ID of the new parent in the ParentId column of the level you need to move. Example: Taking the results from above, if you wanted
Sales - South East
 and
Sales - South West
 to become direct children of
United States
 so that the tree looks like: 

Total Company
|__Company A (100% owned)
   |__Operations
      |__United States
         |__Sales - North
         |__Sales - South
         |__Sales - South East
         |__Sales - South West
         |__Sales - East
         |__Sales - West
         |__Services - East
         |__Services - West
         |__Support
     |__Canada
     |__United Kingdom
...
The data source spreadsheet would look like:
CRM Plannding Dimension Loader - Reparent Dimension Values
Option
Description
1
Sales- S
2
Sales- SE
3
Sales-SW
United States
 is the parent of
Sales - South
. United States will also become the parent of
Sales - South East
 and
Sales - South West
 as indicated in the ParentId column. The other siblings get listed below those levels to make sure they load in the order we want.
The result of loading this spreadsheet would be:
CRM Planning Dimension Loader - Reparent Dimension Values Example
Notice that
United States
 is now the parent of
Sales - South East
 and
Sales - South West
, as indicated by the ParentId column in the spreadsheet.

Associate Attributes with Levels

You can associate existing attributes with levels by indicating mappings for them. The Column Mappings tab within the loader indicates any dimensions and attributes as columns after Actuals Start and Actuals End. Only attributes that already exist in
Adaptive Planning
 can be associated with levels.
Watch the video:
 1m 42s

To create new attribute values during load, make sure that Level import automatically creates attribute values is enabled for the attribute in the Level Attributes Admin.
A spreadsheet data source for the level would have a column for each level attribute. In this example, there is one level attribute, Region Code. The sales levels each get one Region Code level attribute value:
Notice that
Sales - North
 has
N20
 as an attribute value that does not already exist in
Adaptive Planning
. Because of the setting for Region Code in the level attribute admin, N20 will get created as a new level attribute value when we run the level loader.
The level attribute Region Code is available as a column within Column Mapping because it is a level attribute available in
Adaptive Planning
:
Dimension Planning Loader - Attribute - Column Mapping
The result of loading the spreadsheet with the level loader would create the attribute value
N20
 for
Region Code
 and immediately associate
N20
 with the level
Sales - North
.