How to use Quest® Toad® Data Point to leverage data automation

What is data automation

Data automation is the process whereby a series of repetitive steps used to upload, transform or otherwise handle data can be scripted and reused. The resulting automation script contains one or more manual tasks with the intent of eliminating human error and streamlining the associated business analysis process. A scheduled automation is an automation script that is executed at pre-set times without human intervention.

Quest^® Toad^® Data Point has a complete data automation and scheduled automation capability for any set of tasks currently performed manually by a data analyst. The data automation feature comes with Toad Data Point, and the scheduler is part of Toad itself. Toad Intelligence Central is a separately licensed software tool that allows for unattended automation in a group environment.

Within Toad Data Point, the automation menu is accessed from the ribbon bar using the Automation button or by selecting Tools –> Automation from the menu bar. Notice just under the Automation menu item is a tutorial on using automation.

Toad Data Point Automation menu

Any task performed within Toad Data Point can be automated.

Toad Data Point Additional Automation Tasks

There are many types of system activities that can be included in automation, such as setting variables, looping conditions, IF-THEN-ELSE flow control, and sending the results via email – just as you would using Toad Data Point manually. Automating these activities eliminates the possibility of human error and takes what may be a complex series of steps and turns them into a simple mouse click.

A working example of data automation

The easiest way to do data automation is from a data grid in Toad Data Point. Enter and execute your query from either the Editor or the Query Builder. This example uses the Editor window.

Use the Editor window to access Toad Data Point Automation

After running a SQL query, click the Automate button, found on the lower right under the data grid. This will launch the Automation Wizard.

Begin using the Toad Data Point Automation Wizard

This Automation Wizard will set up the logins and steps necessary to run this SQL. The returned data is now available to the remainder of the automation steps as you will see in the next section.

Select the general type of automation you would like the wizard to set up and the outcome will be a working automation script that you can then modify to change the result of the data automation.

Changing the database connection in the Toad Data Point Automation Wizard

I selected the option Export Data and Email. The next panel of the automation wizard lets us change the database connection. It has already imported our SQL statement from the Editor, but we can make changes to the SQL if desired.

Choosing the data export destination in the Toad Data Point Automation Wizard

This panel shows what we are going to do with our data. In this example, you can choose to save the data in an Excel, CSV or HTML file. Click the three dots in file name to select a folder for your data automation output. You can select a time stamp to be included in the suffix. You can also choose to overwrite an existing file with the new data output.

Input email settings in the Toad Data Point Automation Wizard

This wizard will take you to your email settings where it appears very much like an email template.

Add attachments to your email in the Toad Data Point Automation Wizard

This step allows you to add the attachment. In this example, the attachment is the spreadsheet from the prior step of the wizard.

Save and schedule your automation

This is the final step of the wizard. This step illustrates now how you can run this script via a mouse click or set up a scheduled automation.

See the data automation workflow

As we designed it, this data automation script selects data from a data store, passes it to an Excel spreadsheet and emails it to a pre-determined list of recipients.

This is the automation part of Toad Data Point. Be sure to save your automation script by pressing the save button and giving it a name. Now, you can simply run this data automation script to prepare this spreadsheet and send it to the recipients without worrying about inconsistencies or errors.

I would recommend starting data automation or a scheduled automation using this wizard and doing these simple steps. This gives you a working automation script to enhance and develop for your specific needs.

A complex example of data automation using variables

A common use case for automation is to send a report to a list of people at a regular interval, and customize the email with data that is specific to the recipient, such as their name.

Toad Data Point Automation Variable via SQL

This example populates a variable called DAY with the day of the week. The Variable Type is SQL because the contents will be populated from the SQL statement supplied. This SQL will format the current date on the host computer system and extract the day of the week.

Toad Data Point Automation Variable In Use

This example shows the DAY variable (surrounded by '#' characters) being used as part of the Excel spreadsheet file name.

Toad Data Point Automation Data Set Variable Setup

This variable is for the columns of data in the SQL statement. Each column can now be accessed via a #EMPNO_VAR.<column name># such as #EMPNO_VAR.First_Name# that can be a formal greeting in the email text. The client number (in this case EMPNO) can be included as part of the report file name as #EMPNO_VAR.Empno# and even passed as a variable to the report.

This automation task is a loop, in that the tasks inside the circle will be run once, in order, for each row returned from the SQL. The steps in this example set the variable for the report, then run the report.

Toad Data Point Automation Data Set Variable for Report

Toad Data Point Automation Data Set Variable Setup inside the Report

This example shows how to code the variable inside the report, in the report's SQL.

Toad Data Point Automation In Action

Notice a report was generated for each row in the data set.

An email step within this loop after the report executes could easily email the report to an address that was also within the data set. The email body can also include variables to customize it a bit for the addressee, etc.

Create a scheduled automation

Now that the automation script has been saved and has a file name, simply click the “Schedule Automation on Toad Intelligence Central” or the “Schedule Automation” button.

Toad Intelligence Central is a separate, but related repository for Toad Data Point data, spreadsheets, workbooks and other items that need to be shared with the group or across the company. This kind of schedule automation is great for scripts that prepare reports (such as the above example), or data for daily use, that can run unattended whether the user is on the computer or not.

The Schedule Automation allows for this script to run on the business user's computer, when that computer is turned on, whether they are present or not.

Toad Data Point Scheduled Automation

Fill out this wizard, particularly the tab of “Run Times,” to automatically run this script on a user’s workstation. This data automation script will then run regularly in the Toad Scheduler. Use the menu item “Job Manager” to monitor or make any adjustments to runtime, to note if there were any errors or to make sure the script ran successfully.

Closing

The rich automation features of Toad Data Point make it easy to run and send scheduled reports. Instead of pulling data from endless exports, analysts can simply build reports and run scripts that offer the updated data needed to make actionable business decisions.

Related links:

• Blog:Feeding your database: Techniques for building automated Excel imports
• Extend your Toad Data Point free trial from 30 days to 90 days
• Video: Toad Data Point Import Wizard
• Video: Toad Data Point Automation Series: Blog #6 – Using the Export Wizard
• Video: Automate FTP upload and download in Toad Data Point
• Video: Toad Data Point Cross-Platform Compare and Synch
• New to Toad Data Point? Get started fast with these Toad Data Point resources:
  • Watch this Data Preparation Made Easy webinar series. 
  • Blog: Guide to using Toad Data Point Workbook

Search Toad World for more articles on Toad Data Point Automations. www.Toadworld.com

Dan Hotka has several course offerings that use Toad and Toad Data Point and covers Data Automation and Scheduled Automation.

Help your peers and share this blog

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!