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

Load Dimension Values From a Spreadsheet Data Source

Contains preview content for the upcoming 2018.1 release.

This example illustrates loading a CRM Customer list from a spreadsheet data source into the Adaptive Suite using the Planning Dimension Loader.  It assumes a hierarchical dimension called Customer already exists: 

Existing Dimension - Customer

The end goal is to add a several dimension values directly under the hierarchical Customer dimension as a collection of CRM Customers, so that the tree looks like:

Customer
|__Customer 1
|__Customer 2
|__CRM Customers
   |__R.G. Barry Corporation
   |__Sierra Bancorp
   |__SkullCandy, Inc.
   ...
|__StoneMore Partners L.P.
|__ScanSource, Inc.
...   

Format the CRM Customer Spreadsheet

Spreadsheets used to import into the Planning Dimension Loader require specific formatting to indicate which dimension values are parents and which are children.

Three columns are required:

  • Id: The unique identifier for the dimension value in the source system.
  • ParentId: The Id for the parent of the dimension value in the source system if it has a parent.
  • Name: The name of the dimension in the source system that ideally should match the Planning dimension value if it exists in Planning for the first run of the loader.

The Id column uniquely identifies each dimension value. The ParentId column indicates the Id the dimension values point to as their parent. To load at the root, ParentID should be null.

CRM Customer Spreadsheet

Notice that 1 CRM Customers has an Id of 3. This Id of 3 is referenced in the 2 ParentId column to flag all of the dimension values that will become its children.

The dimension Customer does not need to be included in the spreadsheet because it will be selected in the Data Source Settings of the loader. 

Create a CRM Sheet Data Source for the Planning Dimension Loader

Using the spreadsheet shown above, create a spreadsheet data source called CRM 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 Dimension Loader Settings

After the spreadsheet data source is created and its data populates into the staging table, the loader can be configured.

Data Source Settings

The Source Table is the CRM Sheet data source's Sheet1. This example only uses the CRM Sheet data source, but you can see the other data sources in the list. Planning Dimension Loaders can access any staging data, regardless of the data source.

CRM Dimension Loader - Data Source Settings - Source Table

Select Customer as the Planning Dimension.

CRM Planning Dimension Loader - Data Source - Planning Dimension

Enable Create dimension value if not found in Planning.

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 should be:

Planning Column Source Id Column Source Display Name Column
Parent Id ParentId  
Customer Id Name

 

CRM Planning Dimension Loader - Column Mapping

This example does not need any business rules.

Save the loader by clicking Save in the Actions Panel.

Preview the Loader Output

Once the loader is saved, click Preview Loader Output in the Actions Panel.

CRM Planning Dimension Loader - Preview Loader Output Button

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.

CRM Planning Dimension Loader - Run Loader

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

CRM Planning Dimension Loader - Scheduled Integration Task

View the Loaded Dimension Values

The screenshot below illustrates the results of loading the Customer dimension values. The left side 1 shows the expanded tree within Modeling > Model Management > Dimensions. The right side 2 shows the spreadsheet formatted for import. If the ParentId column 3 is left blank, the planning dimension you choose in the Planning Dimension Loader Data Source Settings dropdown its parent.

Dimension Values Import XLSX Formatting

Customer, at the top of the Dimension Values list on the left, was selected as the Planning Dimension in the Data Source Settings dropdown within the loader.

Planning Dimension Customer

Reparent Dimension Values

To reparent existing dimension values with the loader, provide the Id of the new parent in the ParentId column of the dimension value you need to move. For example, using the result of the example above, if you wanted CRM Customers to become a child of Customer 1 so that the tree looks like:

Customer
|__Customer 1
   |__CRM Customers
      |__R.G. Barry Corporation
      |__Sierra Bancorp
      |__SkullCandy, Inc.
      ...
|__Customer 2
|__StoneMore Partners L.P.
|__ScanSource, Inc.
...   

The data source spreadsheet would look like:

CRM Plannding Dimension Loader - Reparent Dimension Values

Customer 1 has an 1 Id of 1. It will become the parent of CRM Customers as indicated in the 2 parentId column for CRM Customers.

The result of loading this spreadsheet would be:

CRM Planning Dimension Loader - Reparent Dimension Values Example

Notice that Customer 1 is the parent of CRM Customers as indicated by the parentId column in the spreadsheet.

Associate Attributes with List Dimensions

You can associate attributes with list dimensions by mapping them without indicating a parentId.

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.

A spreadsheet data source for the list dimension Product would have columns for each of its dimension attributes such as Group, Color, and Size:

Spreadsheet Data Source - List Dimension - Attributes

The columns for Group, Color, and Size contain the attributes for the product dimension.

Select a list dimension in the loader's Data Source Settings.

Dimension Loader - Select List Dimension

The attributes for Product become available as columns within Column Mapping because this dimension is a list dimension:

Dimension Planning Loader - Attribute - Column Mapping

These additional columns would not be available for column mapping if this were a hierarchical dimension.

The result of loading the spreadsheet would look like:

Planning Dimension Loader - Dimensions Loaded with Attributes

  • Was this article helpful?