Automate the processing of your Excel-based calculations with Template Automation, and eliminate the need to manually refresh and save your templates.

 

Why use this feature?

Vena's Template Automation feature is designed to automatically refresh and save data on a template with the push of a single button. This eliminates the need to physically open and refresh it. If your spreadsheets have several page options, opening your spreadsheets, refreshing the data and saving it for each page individually is time-consuming - especially if you have dozens of spreadsheets that need to be updated every month.

With Vena’s Template Automation, you can schedule Template Automation to run as part of your process, completely automating the procedure for routine tasks. If you have many modeling spreadsheets on which you need to periodically refresh data and save the output, Template Automation will save you a lot of time.

In this article, you will learn how to set up and use Template Automation in your processes to automatically refresh and save data on your templates.

 

Before you begin

To follow the instructions in this article for setting up Template Automation tasks, you will need at least Manager access. To validate Template Automation setups, as well as to check on the status of running Template Automation tasks, you will also need Modeler access.

In addition, you will need to have the appropriate Application Permissions for the processes/models on which you want to use Template Automation. If Data Permissions are set up in your Vena environment, you will also need the appropriate permissions for the data involved.

 

Table of contents

• How to
  • Adding Template Automation to a process
  • Configuring Template Automations
  • Validating Template Automations
  • Running Template Automations
  • Copying, modifying & deleting Template Automations
  • Managing Template Automation status
• Reference Guide
  • Template Automation Wizard: Users & General Options
  • Template Automation Wizard: File Options
  • Template Automation Wizard: Schedule Options
  • Template Automation Wizard: View Summary & Run
• Notes & Known Limitations

 

How to

Template Automation is deployed in your Vena environment as part of a process, using a special type of task block called a Processing Block. The Processing Block contains one or more Template Automations, which represent individual processing jobs. Each one corresponds to a single task form and holds the configuration that specifies how that form will be processed when the automation is run.

 

Adding Template Automation to a process

Template Automation is built into a special task block—the Processing Block. To use this feature, you must first add this type of block to your process.

You can connect a Processing Block to a process in any of the following ways:

• Sequential: Connected as part of a normal process flow, with one incoming arrow and one outgoing arrow leading to the next block or the end.
• In Parallel: Multiple Processing Blocks in parallel to one another, connected to single or multiple nodes on either end.
• Single/Terminal: Connected with one incoming arrow but no outgoing arrows (i.e., standalone task, similar to Reporting Blocks).

Processing Blocks can be included as you build out a new process, or you can add them to any existing process (provided the process is paused or has not yet started).

 

To enable Template Automation on a process:

1. Log in to Vena and select the Manager tab. 
2. Select the process on which you want to use Template Automation. This opens the Process Designer for the selected process.
   
   
3. Right-click anywhere within the Process Designer to open the Add a New Block menu. 
4. Select Processing to add a Processing Block.
   
5. Enter a name for your new Processing Task.
6. Select Create once you have named your new Processing Task.
   
   
7. The new Processing Task will now appear in the workflow. Connect the Processing Block to your process by dragging connectors to/from its connection nodes. 
8. Double-click the Processing Block to edit it. The Processing Details pop-up will appear which contains the following sections:
   
   

   A	Title	Choose a name for your Processing Task.
   B	Instructions	Add any instructions that may be helpful to your users.
   C	Supporting Documents	Add any documents for reference/documentation.
   D	Processing Forms	Add task forms to which you want to apply Template Automation processing.
   E	Template Automation	Add Template Automation steps to be run when the Processing Task Block runs. See below for instructions.

9. Complete the Instructions (optional), Supporting Documents (optional) and Processing Forms sections. To attach Processing Forms, select the Attach Forms button and select from either Global Files or Process Files.
   
   
10. Select the X in the left-hand corner to close the Processing Block once you are finished.
11. Your Processing Block is now incorporated into your process, and you are ready to set up Template Automations within it.

 

Configuring Template Automations

Once a Processing Block is in place in your process, you can add Template Automations to it. These specify how each of the task forms will be processed when the Template Automation is run. Each Template Automation that you set up is specific to a single task form, and you can create multiple Template Automations within a single Processing Block (i.e., one per task form).

Template Automations are created and configured using the Template Automation Wizard, which walks you through each of the four steps for setting up a Template Automation.

 

To open the Template Automation Wizard to set up a Template Automation:

1. Double-click on a Processing Block to open the Processing Details menu.
2. Select the Template Automations section to view any existing Template Automation jobs (by default, none will be shown).
3. Select the +Create a Template Automation button to open the Template Automation Wizard.
   
   
4. The Template Automation Wizard walks you through each of the four steps for creating a Template Automation: Settings, File Options, Schedule and Summary. Select Next: after completing each step to proceed to the next step.
   • You can also select any of the steps in the navigation bar at the top to go directly to that step.
     
     

     Step	Name	Activity
     Step 1	Users & General Options	Specify a name and a description for the Template Automation task.
     Step 2	File Options	Choose the task form and page options to be automated.
     Step 3	Schedule	Configure whether to run the Template Automation as part of your process (or only manually, if not).
     Step 4	Summary	Review your settings, automatically check for issues, and manually run the selected Template Automation.

     
     
     For detailed instructions on how to configure each step, see the Reference Guide.

      

5. When you finish setting up a Template Automation, validate the setup using the Validate for Automation button. See the Validating Template Automations section for detailed instructions on how to use this tool.
   
   

   Template Selection

   The template that you select for template automation must have a data model attached to it.

   
   
6. Select the X in the upper left-hand corner of the drawer to close the wizard at any time.
7. Repeat steps 3-6 for each Template Automation you want to create within this Processing Block.

 

Validating Template Automations

You should always validate your setups using the Validate for Automation function whenever you create or modify a Template Automation. This tool can be found on the Summary page of the Template Automation Wizard for any Template Automation.

 

To validate a Template Automation setup:

1. Double-click on the Template Automation you want to validate to open the Template Automation Wizard (see Configuring Template Automations). 
2. Select the Step 4: Summary step of the wizard (View Summary & Run).
3. Select the Validate for Automation button.
   
   
4. Select the X in the upper left-hand corner of the Template Automation Wizard to close the window.
5. Select the Modeler tab. 
6. Select the History tab to see the current ETL jobs table.
7. In this table, locate the job for the validation. It will have a name in this format: Template validation job for file: [yourfilename.xlsx].
8. If the job is still running, wait for it to complete before proceeding.
   
   Once the job has been completed, select View Details to open the Job Information dialog. Here, you can check if your Template Automation was validated successfully or not:
   
   • Validation Success: The ETL job status will show Completed and the Job Information details will not list any mismatches.
     
     
   • Validation Failure: The ETL job status will show Error, and the Job Information details will list one or more mismatches as well as the location (sheet, row and column) of the mismatch.
     
     
9. If the validation is successful, you can proceed with running this Template Automation.

   If the validation fails, use the information provided in the Job Information details to determine and resolve the cause of the problem in the relevant file, then run the validation again by repeating steps 1-8.

 

Running Template Automations

If you enable Scheduling on a Template Automation, it will begin running automatically whenever the Processing Block is reached in your process. But if you want to run any Template Automation on demand, you need to trigger it manually.

 

To manually run Template Automations:

1. In the Process Designer, double-click on a Processing Block to open the Processing Details menu.
2. Select the Template Automation tab to view your existing Template Automation jobs.
3. Select the checkbox(es) next to the Template Automation(s) you want to run.
4. Select the Run button.
   

Copying, editing & deleting Template Automations

You can also copy, modify or delete Template Automations at any time.

 

To copy a Template Automation

1. In the Process Designer, double-click on a Processing Block to open the Processing Details menu.
2. Select the Template Automation tab to view your existing Template Automation jobs.
3. Select the (Copy) icon to create a copy of the Template Automation.

 

To edit a Template Automation

1. In the Process Designer, double-click on a Processing Block to open the Processing Details menu.
2. Select the Template Automation tab to view your existing Template Automation jobs.
3. Double-click on the Template Automation that you would like to modify; this opens the Template Automation Wizard. 
4. Select the appropriate step in the flow and edit as needed.
   
   

To delete a Template Automation

1. In the Process Designer, double-click on a Processing Block to open the Processing Details menu.
2. Select the Template Automation tab to view your existing Template Automation jobs.
3. Select the   (Trash) icon to delete the Template Automation.

 

Managing Template Automation status

When you run one or more Template Automations (either manually or on a schedule), each one will be performed as an ETL job. You can track the status of your Template Automation jobs in the ETL jobs queue.

 

To view your Template Automation ETL jobs:

1. Navigate to the Modeler tab.
2. Select the Data Modeler tab. 
3. Select the desired Data Model from the table.
4. Select the ETL tab to see a list of current ETL jobs including Template Automation jobs.
5. Use the Status column to review the status for each job. You can also view detailed information about each job's status by clicking on the Info icon. This opens the Job Information pop-up where you can see the steps that were executed as part of the job, as well as the log.
   • If a Template Automation job fails to complete, an Error icon will be shown in the Status column. Select the icon to view details about the error. 
   • In the Job Error pop-up that appears, select More to view technical details about the error. You can provide this information to the Vena Support Team for troubleshooting.

Reference guide

Template Automation Wizard: Users & General Options

A. Template Automation Name: Type in a name for this Template Automation job, which will be shown in the list of Template Automations in the Processing Block.

B. Description: Enter an optional description for this Template Automation job.

Template Automation Wizard: File Options

1. File Options: Choose the task form (template) and section to be processed with this Template Automation. 
   • Select a form: Use the first drop-down to choose the form.
     • Only task forms that you have attached to the Processing Block (under the Processing Forms section) will appear here.
     • Only one task form can be assigned per Template Automation. Create additional Template Automations within the same Processing Block for each form you want to process.
   •  Section: Use the second drop-down to select the Section of the task form that contains the page options you want to process.
     • If there is only one section, it will be selected automatically.
2. Page option selection: Specify which page options should be processed with this Template Automation. In this area, you'll find the dimensions associated with the task form you selected. Type in the search bar to search for a specific dimension, or use the drop-down list to manually select your dimensions.
   • If you wanted to add more than one dimension to a particular tab, you must select all the dimensions before selecting the +Add Selected button.
   • You must choose at least one page option for each available dimension.
   • Page options that have been added to the selection will be listed in the box at the bottom of the Template Automation wizard, and the number of current selections is displayed at the top-right of each box.
   • To remove a selection, select the garbage can icon next to a page option.
3. + Add Selected button: This adds all the combinations created by your page option selections to the Page Option Choices table, which marks them for processing as part of this Template Automation.
   • When you select the + Add Selected button, a pop-up will appear indicating how many page options were added, as well as if any were not added if they already existed in the table.
4. Page Option Choices table: Lists all the page options you have marked for processing.
   • You can rename the page options in the table by clicking on any of the underlined titles in the Name column. 
   • Filter the table of page options by using the Filter field in the top.
5. Trash buttons: Remove a page option by selecting the trash button next to the item you want to remove.
   
6. → Next: Schedule button: Select this when you want to move to the next step in the Template Automation flow.

• •  

Template Automation Wizard: Schedule Options

A scheduled template automation will automatically send when the Processing Task is reached. This can either be active or inactive by checking the checkbox next to Send automatically when Processing Task is reached.

• If selected: The Template Automation runs whenever the Processing Block it is attached to is reached in your process.
• If not selected: This disables the Template Automation from automatic running (default setting). Use this option if you only want to run the Template Automation on demand (manually).

 

Template Automation Wizard: View Summary & Run

A. Summary: Displays a summary of the settings, including the description, schedule, page options selected, etc. 

B. Validate for Automation: This allows you to dry run the Template Automation and check for any errors. See the section Validating Template Automations for detailed instructions.

C. Run Template Now: This runs this specific Template Automation immediately, even if Scheduling is set to ON. This button is used to run just this Template Automation, i.e., for the task form you specified on the File Options step.

 

Notes & known limitations

• Template Automation does not support templates with the Hybrid template property, but can be used with the Central and Standalone template properties.
• Template Automation does not support templates that use any of the following, which will cause validation to fail:
  
  • VBA macros (including VenaEvents and COM functions)
  • Array formulas
  • Multiple date formats
  • LOOKUP formulas (VLOOKUP is compatible)
• Aside from the Process Designer, Template Automations can also be set to run as part of an ETL template.
• Template Automation does not work if you need to pull data from multiple models into a single template.

 

 

Questions? Comments? Reach out to us directly at support@venasolutions.com.
(Please include the link to the article for reference.)