Passer au contenu principal

logoCorrectSize.png

Workday Adaptive Planning Knowledge Center

Charger des niveaux à partir d’une source de données de feuille de calcul

Présentation de l’importation de valeurs de niveau et d’attribut de niveau en bloc à partir d’une source de données de feuille de calcul à l’aide du chargeur de niveau de planification

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
 

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.

Level Attribute Admin - Automatically create attribute values during import

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 :

Spreadsheet Data Source - List Dimension - Attributes

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 :

Dimension Planning Loader - Attribute - Column Mapping

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 :

Planning Dimension Loader - Dimensions Loaded with Attributes

  • Cet article vous a été utile ?