Dec 8, 2021 8:00:00 AM by Dan Hotka
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.
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.
How to get the most out of Toad Data Point
Learn how Toad Data Point can help you access and prepare data faster. Seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data source with near zero transition times.
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.
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.
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.
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.
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!
Written by Dan Hotka
Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.
Dan's most recent book is Toad for Oracle Unleashed