Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Load Levels From a Spreadsheet Data Source

Walk-through of importing levels and level attribute values in bulk from a spreadsheet data source using the Planning Level Loader

This example illustrates loading a list of levels from a spreadsheet data source into Adaptive Insights using the Planning Level Loader.  It assumes a level called Total Company already exists as the top level of the organization: 

Existing Level - Engineering

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

1 ParentId 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 2 Sales - South, the rows for Sales - South East and Sales - South West reference Sales - South in the 3 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.

CRM Planning Dimension - CRM Sheet Data Source

Import the sheet contents for the data source to populate the staging area.

CRM Sheet - Spreadsheet Data Source

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.

CRM Dimension Loader - Data Source Settings - Source Table

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

CRM Planning Dimension Loader - Create Dimension Value option

Column Mapping

Because of the data source spreadsheet's format and columns, the column mappings for this loader must be:

Planning Column Source Id Column Source Display Name Column
Parent Id ParentId  
Level id Level

Remember, the Source Id Column references a unique id for each level. The Source Display Name Column references the name of the level.

CRM Planning Dimension Loader - Column Mapping

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.

Preview Loader Output

Review the Preview Loader Output Status to verify that no errors occur.

 CRM Planning Dimension Loader - Preview Loader Output

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.

Run the Loader

The loader is also ready to be included in a scheduled Integration Task that can run automatically.

CRM Planning Dimension Loader - Scheduled Integration Task

View the Loaded Levels

This screenshot illustrates the results of loading the levels. The left side 1 shows the expanded tree within Modeling > Model Management > Levels. The right side 2 shows the spreadsheet formatted for import. If the ParentId column 3 is left blank, the top level of the hierarchy, Total Company, becomes the parent.

Loaded Levels Example

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

United States is the parent of Sales - South 1. United States will also become the parent of 2 Sales - South East and 3 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 Insights can be associated with levels.

Watch the video: 1m 42s

Create Level Attribute Values During Level Import
 

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.

Level Attribute Admin - Automatically create attribute values during import

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:

Spreadsheet Data Source - List Dimension - Attributes

Notice that Sales - North has N20 as an attribute value that does not already exist in Adaptive Insights. 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 Insights:

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:

Planning Dimension Loader - Dimensions Loaded with Attributes

  • Was this article helpful?