Charger des niveaux à partir d’une source de données de feuille de calcul
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:
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.
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.
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 Insights.
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.
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. 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.
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:
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:
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
Pour créer de nouvelles valeurs d’attribut pendant la charge, assurez-vous que l’importation de niveau crée automatiquement des valeurs d’attribut pour l’attribut dans l’admin des attributs de niveau.
Une source de données de feuille de calcul pour le niveau aura une colonne pour chaque attribut de niveau. Dans cet exemple, il existe un attribut de niveau : Code régional. Les niveaux ventes obtiennent chaque valeur d’attribut de niveau Code régional :
Notez que Ventes -Nord a N20 comme valeur d’attribut qui n’existe pas déjà dans Adaptive Insights. En raison du paramètre pour le code régional dans l’admin d’attribut de niveau, N20 sera créé comme une nouvelle valeur d’attribut de niveau lorsque nous exécutons le chargeur de niveau.
L’attribut niveau Code régional est disponible sous forme de colonne dans Mappage de colonnes parce qu’il s’agit d’un attribut de niveau disponible dans Adaptive Insights :
Le chargement de la feuille de calcul avec le chargeur de niveau créera la valeur d’attribut N20 pour Code régional et associe immédiatement N20 avec le niveau Ventes -Nord :
