Load Dimension Values From a Spreadsheet Data Source
This example illustrates loading a CRM Customer list from a spreadsheet data source into Adaptive Insights using the Planning Dimension Loader. It assumes a hierarchical dimension called Customer already exists:
The end goal is to add 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.
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.
Import the sheet contents for the data source to populate the staging area.
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.
Select Customer as the Planning Dimension.
Enable Create dimension value if not found in Planning.
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 |
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.
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 could run automatically.
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 becomes its parent.
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.
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:
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:
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 spreadsheet data source for the list dimension Product would have columns for each of its dimension attributes such as Group, Color, and Size:
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.
The attributes for Product become available as columns within Column Mapping because this dimension is a list dimension:
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:
