Bulk Load Template Sheet Layout

This topic describes a typical generated sheet when using the Export Bulk Load Template menu option.

Colors and styles are applied to the exported sheet:

• dark colors style for header rows
• yellow text for base group titles
• mandatory fields have red title text headers
• optional fields text headers are in white

Although an attribute that has nested attributes may be optional, if this attribute has mandatory nested attributes, then the containing attribute becomes mandatory. If a field is mandatory, it is shown on the sheet regardless of any Field Display Policy instruction to hide it.

The Field Display Policy that applies to a menu item from which a Bulk Load Template Export is carried out, is applied to the exported sheet as follows:

• Titles of attributes
• Sequence of the attributes
• Group names
• Hidden fields, with the exception of mandatory fields.

Note

• Macros can be included in the loader to either be loaded as text or evaluated as part of the load. See documentation in this guide around evaluate_macros header for more details on macro behavior in the loaders.
• A single sheet of a file can be used to manage multiple templates by adding additional header rows and data under them. A workbook file can include multiple sheets with single or multiple templates on each.

Refer to the example sheet snippet. A bulk load sheet contains the following information:

Sheet name (tab on spreadsheet workbook)

Any name can be provided on the tab or sheet If the name is prefixed with a # on the tab, the sheet is ignored during loading.

Row 1 - Resource and instructions

The exported bulk loader template will have the resource as target entity (model) as well as the hierarchy shown on the top row of the sheet. Verify the entity in the first row of an exported sheet. The reference data in the first row is of the format shown below, with variable values indicated in {}:

entity: {entity name}; \
hierarchy: {hierarchy}; \
parallel: {True | False}; \
parallel_transaction_limit: {n}; \
template: {config_template}; \
meta_prefix: {c}; \
evaluate_macros: {True | False}

• entity: {entity name}: the name of the model, in the format {modeltype}/{model name}, for example data/User

• {hierarchy}: the hierarchy, in the format {level1}.{level2}.{level3}, where {level1} is the first system level. Verify the hierarchy at which the bulk load should take place.

• parallel: True or False. By default, the value is False and rows are processed sequentially. If multiple templates are entered on a single sheet, they should all only have a single value: True or False.

  Sheet rows can be processed in parallel. The sheet should then not contain multiple, sequence dependent models. If there are a large number of rows for complex models on the sheet, the duration of a bulk load transaction is significantly reduced by parallel processing.

  By default, 14 rows are processed in parallel, since bulk loads are low priority transactions that are limited to 50% of the maximum allowed parent transactions, which is by default set to be 30 per unified node. The default value supposes that one slot is used by the parent bulk load transaction itself.

  The maximum allowed parent transaction limit can be modified from the Command Line Interface (CLI) using the command: voss workers <number>.

• parallel_transaction_limit: the maximum number of rows that can be processed in parallel by the bulk load at any given time. The minimum value that can be set is 1 and the maximum is 100.

• template: The Configuration Template {config_template} that is associated with the user’s menu item for the {entity_name} from which the export was carried out. The exported sheet will show a row of values from the Configuration Template.

  When a sheet is created to bulk load, the Configuration Template should be available on the target system and it will only apply to rows on the sheet that has add specified in the # Action column.

  Note: this header item is not used when Configuration Templates are loaded.

• meta_prefix: By default, the value is $. The # character cannot be used, as it is used for comments. The character is prefixed to the # Base group of columns in Row 2.

  The purpose of the prefix is to distinguish a special set of base columns from the entity attributes on bulk load sheets.

  Note that the bulk load sheets will fail to load if the # character is is used as prefix. An error message will be shown in the transaction log.

• evaluate_macros: By default, the value is False. When set to True, named macros can be added as values to be evaluated before the sheet is loaded. Otherwise, the value is a string.

  The format of the macro is {{ fn.bulkload_evaluate macro.NamedMacro }}, where NamedMacro is the name of an existing data/Macro instance. The function prefix fn.bulkload_evaluate is required in the value for the macro to be evaluated.

  • For examples, see Bulk Load Sheet Macro Evaluation.

  Note: fn.bulkload_evaluate is not available via the Macro Evaluator. For testing purpose using the Macro Evaluator, please use the fn.evaluate function prefix.

Base columns (grouped by # Base in Row 3)

The list below describes the column values with the default value of meta_prefix, in other words, column names by default prefixed by $.

The purpose of the columns is to provide more detailed instructions or overriding data for a row.

• Comments: Any row that contains a # character in column A is considered a comment row and will be ignored. Empty rows are also ignored. Column A - the first column - is also a # Comment column, so that any value entered in it is considered a comment. If all rows on a tab are commented, but the tab name itself is not commented, the tab sheet load will fail.

• $hierarchy: A hierarchy column with the name # Hierarchy Node is also available so that individual rows of a sheet can be loaded to a specified hierarchy. If a hierarchy is specified in this column for the row, it takes precedence over the hierarchy in the first row. The format for the hierarchy in the row is the same as for the first row: the full hierarchy, with levels separated by dots.

• $action: Any row that contains an action in the # Action column : add, delete, modify, execute or a custom action name, will have the action carried out. The action values in the column are case insensitive.

  If no action is entered, the add action is carried out. The list below shows the functionality for the values entered in the row. Also refer to the Search Fields entry below.

  • add or empty - the data in the attribute columns is added. Any values in the # Search Fields column are ignored.
  • delete - the row matching the unique criteria in the # Search Fields column is deleted.
  • modify - the row matching the unique criteria in the # Search Fields column is updated with values in the attribute columns. Refer to the Search Fields entry below.
  • execute - if the action is available for the model, the row matching the unique criteria in the # Search Fields column is executed, using any values entered in attribute columns.
  • custom action name - if the custom action is defined for the model, it is carried out for the row matching the unique criteria in the # Search Fields column.

• $search_fields: The column applies to rows where the action is not add and consists of a colon-separated list of attribute names and values, for example, fullname:'John Smith',username:jsmith.

  Note however that the pkid field takes precedence over search fields criteria.

  • delete - the search fields and corresponding attribute values uniquely identify the model instance to delete.
  • modify - the search fields and corresponding attribute values uniquely identify the model instance to modify, with the values to modify in the attribute columns.
  • execute - the search fields and corresponding attribute values uniquely identify the model instance to execute.

  Where the sheet is for a Relation model, only the left model attributes in the Relation can be in the Search Fields column. This is the standard search behavior for Relations.

• $device: The column is used when a sheet includes attribute columns that belong to a device model. This column then contains the comma-separated list of business keys of the device model, as well as its hierarchy. These values narrow the search for the device to which the data in the sheet applies. Examples of such sheets would contain device models or relations that have device model attributes in the left hand association of the relation.

  The format of the values in this column is:

  <business_key1>,<business_key2>,...,<business_keyn>,<device_hierarchy>.

  For example, if a CM instance in a model data/CallManager has host and port as business keys, the value would for example be: 10.120.2.175,8443,sys.Varidion.InGen.Tokyo.

• $template (Configuration Template): If a row that contains a Configuration Template name that applies to the model, this template is applied to the row when it is loaded. Upon bulk loading, values in this column will override any value for template in the sheet header.

• $ndl (Network Device List): The column is used when a sheet includes attribute columns that belong to a device model. This column then contains the name of the Network Device List that includes the required device in the list of devices. The NDL can be filled in as either the business key friendly name or in the NDL business key format, for example [“322-CL1-NDL”, “hcs.MTLAB.Ops.IBM”]. The friendly name (“322-CL1-NDL”) will then be used during the bulk load.

  If the Device column is also filled in, then the value in the Network Device List column overrides it.

• $pkid (Unique Identifier): On modify, delete, execute, and custom action operations, this pkid is used to identify the resource represented in the row data. The pkid field takes precedence over the search fields criteria when locating a resource. The pkid is unique to the resource on the particular database and cannot be relied upon when attempting to manipulate an identical resource on a different database.

Row 2 - Column names

• base column names (prefixed by the meta_prefix character and listed above)
• attribute names. Entity attribute names show as column header data in the spreadsheet.

Columns can be in any order in a row. Nested object attribute names follow a dot notation.

Array objects will be sorted, so that attributes with names such as filter_fields.<number>.xx will be in sequence: filter_fields.0.xx, filter_fields.2.xx, and so on - before further ordering (represented by .xx here) is applied.

• If a column header starts with a #, the column will not be loaded.
• If a column header is blank, this indicates the end of the sheet header. Subsequent columns will not be loaded.

Row 3 - Group or description

The row provides a description of a column or columns (as for example # Base for the sheet base columns), or else the group name of attributes that are grouped on the GUI as tabs on the detail or input GUI form.

A group is specified in the row by merging the group name across all the columns of the group. For attributes that are required and are not grouped in the GUI (or may be hidden in the GUI), the group name: Not Grouped Fields is given on the sheet.

“Default” values of attributes in this group need to be removed from an exported sheet before the sheet is used to bulk load rows.

Row 4 - Title

Title of:

• the reference for base column names (hierarchy, action and so on)
• the column attribute as on the GUI. This title may be modified by a Field Display Policy.

Data rows

The exported template contains no data.

Important

As a part of bulk loader sheet design, attention should be paid to the API payload posted to the system. The data entered in the loader sheet columns should correspond with the API payload.

GUI drop-down lists may contain user-friendly titles, while the actual value sent to the API may differ.