Try it out: Custom snippets tutorial

Work through this tutorial to understand the process of converting an existing process script to a reusable custom snippet.

About this task

This tutorial converts this example process script into a custom snippet.

# Iterate over members in a hierarchy
vDimension = 'null';
vHierarchy = 'null';
vSubset = 'temporary';
HierarchySubsetCreate(vDimension, vHierarchy, vSubset, 1);
HierarchySubsetIsAllSet(vDimension, vHierarchy, vSubset, 1);
index = 1;
vSubsetSize = HierarchySubsetGetSize(vDimension, vHierarchy, vSubset);
WHILE (index <= vSubsetSize);
   vElement = ElementName(vDimension, vHierarchy, index);
   IF ( ElementLevel(vDimension, vHierarchy, vElement) = 0);
       # Do something with vElement such as a write it to a file
       AsciiOutput('model_upload/snippet.txt', vElement);
   ENDIF;
   index = index + 1;
   vElement = ElementName(vDimension, vHierarchy, index);
END;

The snippet you'll create in this tutorial prompts for variable values and then inserts the resulting script into a process. The script iterates over all members in the specified hierarchy and performs an action on each member. In this case, the script uses the AsciiOutputFunction to write each 0-level (leaf) member to the snippet.txt file. You can insert the custom snippet that you create in this tutorial into any new or existing process on your database and run it against any existing hierarchy.

Procedure

Create a new JSON file named IterateOverMembersInHierarchy.json. The file extension must be .json, as this is the only file extension recognized for custom snippets.
Identify variables and auxiliary variables in the process script.

This is the most important factor when converting a script into a snippet. Variables must be initialized before the snippet is executed, while auxiliary variables are required at run time.

In the example script, you can identify three variables that must be initialized before the snippet is executed: vDimension, vHierarchy, and vSubset. The three auxiliary variables are index, vSubsetSize, and vElement. vSubsetSize and vElement store values needed during the runtime of the script.
To start building your custom snippet, define a snippetID, name, and description for the snippet in the IterateOverMembersInHierarchy.json file.
```
{
  "snippetId": "ITERATOR_OVER_MEMBERS_IN_A_HIERARCHY",
  "name": "Iterate over members in a hierarchy",
  "description": "This snippet iterates over members in a hierarchy and performs an action on each leaf member"
}
```
The name and description provide information when a user inserts the custom snippet into a process.

Add the three auxiliary variables identified in step 2: index, vSubsetSize, and vElement.

{
  "snippetId": "ITERATOR_OVER_MEMBERS_IN_A_HIERARCHY",
  "name": "Iterate over members in a hierarchy",
  "description": "This snippet iterates over members in a hierarchy and performs an action on each leaf member"

  "auxiliaryVariables": [
        "index",
        "vSubsetSize",
        "vElement"
    ],
}

Add the three variables identified in step 2: vDimension, vHierarchy, and vSubset.

For each variable, specify a variableName, description, and valueDefinition.

valueDefinition requires that you define both a type and a defaultVal.

vDimension is a dimension, so type is DIM. The defaultVal is null, which means that you select the dimension when you insert the snippet into a process.
vHierarchy is a hierarchy so type is DIM_HIER. The defaultVal is null, which means that you select the hierarchy when you insert the snippet into a process.
vSubset is a subset so type is STRING. The defaultVal is temporary, but this subset name can be edited when you insert the snippet into a process.

{
  "snippetId": "ITERATOR_OVER_MEMBERS_IN_A_HIERARCHY",
  "name": "Iterate over members in a hierarchy",
  "description": "This snippet iterates over members in a hierarchy and performs an action on each leaf member"

  "auxiliaryVariables": [
        "index",
        "vSubsetSize",
        "vElement"
    ],

   "variables": [
        {
            "variableName": "vDimension",
            "description": "Parent dimension of the hierarchy",
            "valueDefinition": {
                "type": "DIM",
                "defaultVal": null
            }
        },
        {
            "variableName": "vHierarchy",
            "description": "Hierarchy to iterate",
            "valueDefinition": {
                "parent": "vDimension",
                "type": "DIM_HIER",
                "defaultVal": null
            }
        },
        { 
            "variableName": "vSubset",
            "description": "Subset created to hold all members ",  
            "valueDefinition": {
                "type": "STRING",
                "defaultVal": "temporary"
            }
        }],
}

Create a script property in your .json file.

{
  "snippetId": "ITERATOR_OVER_MEMBERS_IN_A_HIERARCHY",
  "name": "Iterate over members in a hierarchy",
  "description": "This snippet iterates over members in a hierarchy and performs an action on each leaf member"

  "auxiliaryVariables": [
        "index",
        "vSubsetSize",
        "vElement"
    ],

   "variables": [
        {
            "variableName": "vDimension",
            "description": "Parent dimension of the hierarchy",
            "valueDefinition": {
                "type": "DIM",
                "defaultVal": null
            }
        },
        {
            "variableName": "vHierarchy",
            "description": "Hierarchy to iterate",
            "valueDefinition": {
                "parent": "vDimension",
                "type": "DIM_HIER",
                "defaultVal": null
            }
        },
        { 
            "variableName": "vSubset",
            "description": "Subset created to hold all members ",  
            "valueDefinition": {
                "type": "STRING",
                "defaultVal": "temporary"
            }
        }],

   "script": [ ]
}

Copy the entirety of the example process script and paste it into the script property in the snippet.
Reformat the code in the script property into an array of comma-separated strings, with each line an individual string.
For example, the first few lines of code in the script property should look like this
```
"# Iterate over members in a hierarchy",
"vDimension = 'null';",
"vHierarchy = 'null';",
"vSubset = 'temporary';",
```
When you insert the custom snippet into a process, each string is a new line in the process editor.
Edit the code in the script property by updating all variable names using the format vName:VariableName and all initialized values using the format vValue:VariableName..

For example, vDimension = 'null'; becomes vName:vDimension = 'vValue:vDimension';.

Update all auxiliary variables in the script property using the format aux:AuxiliaryVariableName.

For example, index = 1; becomes aux:index = 1;.

The contents of the IterateOverMembersInHierarchy.json file should now look like this:

{
  "snippetId": "ITERATOR_OVER_MEMBERS_IN_A_HIERARCHY",
  "name": "Iterate over members in a hierarchy",
  "description": "This snippet iterates over members in a hierarchy and performs an action on each leaf member",
  "auxiliaryVariables": [
        "vSubsetSize",
        "index",
        "vElement"
    ],
    "variables": [
        {
            "variableName": "vDimension",
            "description": "Parent dimension of the hierarchy",
            "valueDefinition": {
                "type": "DIM",
                "defaultVal": null
            }
        },
        {
            "variableName": "vHierarchy",
            "description": "Hierarchy to iterate",
            "valueDefinition": {
                "parent": "vDimension",
                "type": "DIM_HIER",
                "defaultVal": null
            }
        },
        { 
            "variableName": "vSubset",
            "description": "Subset created to hold all members ",  
            "valueDefinition": {
                "type": "STRING",
                "defaultVal": "temporary"
            }
        }],
    "script": [
            "# Iterate over members in a hierarchy",
            "vName:vDimension = 'vValue:vDimension';",
            "vName:vHierarchy = 'vValue:vHierarchy';",
            "vName:vSubset = 'vValue:vSubset';",
            "HierarchySubsetCreate(vName:vDimension, vName:vHierarchy, vName:vSubset, 1);",
            "HierarchySubsetIsAllSet(vName:vDimension, vName:vHierarchy, vName:vSubset, 1);",
            "aux:index = 1;",
            "aux:vSubsetSize = HierarchySubsetGetSize(vName:vDimension, vName:vHierarchy, vName:vSubset);",
            "WHILE (aux:index <= aux:vSubsetSize);",
            "   aux:vElement = ElementName(vName:vDimension, vName:vHierarchy, aux:index);",
            "   IF ( ElementLevel(vName:vDimension, vName:vHierarchy, aux:vElement) = 0);", 
            "       # Do something with vElement such as a write it to a file;",
            "       AsciiOutput('model_upload/snippet.txt', aux:vElement);",
            "   ENDIF;",
            "   aux:index = aux:index + 1;",
            "   aux:vElement = ElementName(vName:vDimension, vName:vHierarchy, aux:index);",
            "END;"
      ]
}

Create a new process on your database without a data source.
Click the Script tab on the process editor.
On the toolbar, click the snippet icon , then click Custom.
On the Snippet manager page, click Upload snippets.
Specify your IterateOverMembersInHierarchy.json file, then click Upload.
The custom snippet you just created and uploaded is now available for reuse at any time.
Click Close and then Cancel to dismiss the Snippet upload and Snippet manager pages, respectively.
On the Script tab of the process editor, position the cursor between the #Section prolog and #Section epilog.
Click the snippet icon , then click Custom.
Click IterateOverMembersInHierarchy.json file, then click Select.

The Insert snippet page opens so you can set variable values and preview the process code that will be inserted by the snippet.
Click Select a dimension and pick the dimension you want use.
Click Select a hierarchy and pick the hierarchy you want to iterate.

You can leave the vSubset value as temporary. The code creates a temporary subset that persists only for the duration of the process execution.
Click Insert.

Script based on the custom snippet is inserted into the process, using the variable values you specified on the Insert snippet page
Click Save to save the process.
Click Run.

Results

When you run the process, the process creates a temporary subset and iterates over each member. When the process encounters a 0-level member, it writes the member name to the snippet.txt file in the Planning Analytics Workspace file manager.

To review the file, right-click your database in the Databases tree on a modeling workbench, then click File manager. Select snippet.txt, then click Download. The file should contain all the 0-level members of the hierarchy you selected when you inserted the custom snippet into the process.