Metadata loaders synchronize hierarchical information from source systems into the hierarchies of Adaptive Insights. Your hierarchies should already exist in your source system before you attempt to synchronize to Adaptive Insights.
The Planning Level Loader lets you map metadata about organizational hierarchies from the staging area into Adaptive Insights.Use the planning level loader to import levels in bulk, instead of creating them one at a time in the levels admin. Once you create a loader, you can run it manually or schedule it to run as part of an Integration task.
Watch the video: 1m 36s
- Enter data source settings
- Configure column mapping
- Create business rules
- Preview the loader output
- Run or schedule the loader
Before You Begin
The Planning Level Loader requires staging data from an import of an existing Data Source. Make a note of what data source you want to use.
Required Permissions: Integration Operator, Data Designer
How You Get There
Go to nav menu > Integration > Data Designer
Enter Data Source Settings
The Data Source Settings let you choose the source table you want to use from Integration's staging data, and indicate whether to create new levels automatically.
Best Practice: Save frequently as you create and edit your loader. Click Save in the Actions panel. To discard any unsaved changes click Close, then click No.
- Click Create New Loader In the Loaders section of the Component Library.
- Select Planning Level Loader as the loader type. Enter a name for the loader.
- Click Create.
- Enter the Planning Level Loader general properties information:
- Source Table: Select the source table from the dropdown list. The source tables in the list are of all the tables available in the staging area you have access to.
- Create level if not found in Planning: Check to automatically create new levels found in the source system, but not found in Modeling > Model Management > Levels. Uncheck update only existing levels in your model.
- Propagate applicable level updates to children: Check to update child levels if the change involves a cascading property. If this is unchecked only the level explicitly updated will change.
- Delete workflow items when disabling workflow: Check to delete items within workflow if you disable workflows. If this is unchecked, workflow items will not get deleted when workflow is disabled.
- Delete inaccessible actuals when updating level availability: If the start or end date for actuals on a level changes to make actuals inaccessible, delete the actuals data that can't be accessed.
- Publish pending admin publishing changes: If Admin Publishing has pending workflow changes, check to publish the changes.
- Log Level: Choose the type of details to capture when running the loader.
- Off: No errors logged.
- Error: Log serious errors only.
- Info: Log basic information, such as when the loader was updated.
- Verbose: Detailed information about all phases and actions. Used primarily for debugging or auditing.
Configure Column Mapping
Column Mapping lets you map columns from the data source to levels available in Modeling > Model Management > Levels. You can unmap columns to ignore the staging data you don't want to load. You can also unmap columns one at a time following loader runs to troubleshoot which columns generate errors in the logs.
If there are several columns to map, use the Show filter to select:
- All: Shows all Planning columns.
- Required: Shows all required Planning columns, like Parent ID and Level.
- Unmapped: Shows all unmapped Planning columns.
- Mapped: Shows all mapped Planning columns.
Use Category to select:
- Level Details: Shows columns that relate to level details, including Short Name, Currency, and In Workflow. In Workflow must be a boolean value of 1 for true or 0 for false.
- Elimination Settings: Shows columns that relate to elimination settings, including elimination level, and elimination trading partner. These boolean values must be either 1 for true or 0 for false.
- Version Settings: Shows columns that relate to version settings, including actuals start, and actuals end. These values should be date formats like 01/01/2010.
- Level Attributes: Shows columns that relate to level attributes.
- Level Dimensions: Shows columns that relate to level dimensions.
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.
The Column Mapping tab has these columns:
- Status: A checkmark indicates a mapped column. An exclamation point 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-mandatory columns include the Short Name and Currency.
- 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.
- Click Column Mapping.
- Select an unmapped column from the drop-down. Columns available for mapping display in the list. As you map the columns, the status indicator changes from an exclamation point to a check mark .
Map Parents of Levels
The Parent Id column indicates the id of a level's parent. To load to the highest level in a hierarchy, the Parent Id value in source data must be empty. Parent Id must also be empty If you want to create a child level underneath the top level.
See Load Levels From a Spreadsheet Data Source for an example of how levels and their parents map to planning.
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 the status icon to an en exclamation point .
Best Practice: Along with unmapping to troubleshoot errors and ignoring columns you can use unmap to help with change management. When new columns get introduced in a data source, you can unmap the older columns to map the new ones.
Create Business Rules
You can use Business Rules to create SQL expressions that limit the staging data that is available for loading. Only records that meet your filter criteria will load.
The Business Rules tab contains a text area for entering SQL:
To create an SQL filter:
In the Business Rules tab, select SQL Filter. Click Edit.
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.
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.
For detailed SQL syntax help, click 'online help' in the Notes section of the Edit SQL Filter. See the SQL Expression Reference for more.
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 to check the mappings and fix validation errors before running the loader.
When the preview loader output or manual load runs, the source data is validated for errors. Examples of the validations include checks if:
- A sub level is created without indicating its parent level.
- A level attribute is assigned an attribute value it can't find.
- The source ID value for the level is missing (empty) in the data.
- There are duplicate Source IDs or level names in the data. Source IDs must be unique.
- A ParentId is the same as the Source ID in the data. If so, the column is referring to itself as its parent.
- When importing sub levels, the root of the sub level maps to an existing node in Adaptive Insights. The parentID is not in the source data for a new node introduced to Adaptive Insights.
- An existing node moved under a new node and the Create New flag is disabled. An existing level is moving into a new node when the loader was set to not make new levels.
- The data has a cyclic reference in its hierarchy. A ParentId is referring to its own child as its parent.
- A level is set as an elimination trading partner and one of its child levels is set as an elimination level. A level set as an elimination trading partner can't have a child that is an elimination level.
You can also download the preview output as an XML file to manually verify the levels from your data source against your requirements before loading them.
Click Preview loader output in the Actions panel.
(Optional) Enter Preview Loader Output settings. The information you can enter varies depending on the data source you chose for this Planning Level Loader. Changing the settings here imports new data into Staging tables and columns.
- Click Preview loader output.
The loader runs, including all transformations and business rules. A status popup displays the steps as the loader executes. If levels are available to load, the loader creates an XML file and sends an email notification with the output as a zipped XML attachment. If no levels load, the zip will contain a message indicating none loaded.
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 levels and the size of the file.
This is an example of an downloaded unzipped output XML file:
Run the Loader
After saving a Planning Level Loader with the required settings, run the loader manually or run it as a scheduled integration task.
Wait about 15 - 30 minutes between each Planning Level Loader run so that Adaptive Insights has time to synchronize with your import. Larger imports take longer to synchronize. Wait time depends on model size and other factors.
To run the loader manually, click Run Manually in the Actions panel.
You can load levels 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 Level Loader in an Integration Task
Any Integration task can have one or more loaders. A task can contain other integration tasks, like Discovery metric loaders and scripted loaders.
Best Practice: Have separate Integration tasks for each loader.
If a task contains multiple loaders, parameters from each loader display in a prompt when the task runs. If there is a common or shared parameter in the loaders within a task, the task prompts for the parameter only once. You can choose to override parameter prompts.
For scheduled runs of the task, default values of the parameters for the loaders get used.
Troubleshooting Messages and Warnings
See Planning Level Loader Log Messages for descriptions of the messages logged during a load.