|
|
 |
Blogs
Toad and Database Commentaries |
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
|
|
|
|
|
|
Location: Blogs
Debbie Peabody's blog
|
|
| Debbie Peabody |
1/14/2010 6:50 PM |
One of the first principles you learn as a software developer is how to reuse code. In fact reusing efforts is actually essential to any job. I remember numerous times over the years trying to figure out how can I take fewer steps, deliver faster and better service and increase my value and income? Well, life hasn’t changed. So when they asked me to take on the automation window for Toad for Data Analysts I was truly excited. (Mainly because I wanted to use it myself)
What can I use Automation for?
When you start thinking about it most processes that are repeated can be automated through scripts. Scheduling them makes them truly automatic but even if run manually these scripts help increase your productivity. Some examples of what can be automated using Toad for Data Analysts:
1) Monthly and weekly reports generated and distributed.
2) Exception reporting – locating anomalies.
3) Export/importing data – setups for other tasks.
4) File or data backups (just a good idea to have your own personal copies).
Getting Started
The automation designer can be launched by pressing the Automation Grouping tool button or from the Tool menu. 
Automation activities are available from the toolbox on the left. There are three groups of activity types: Database, File and System activities. Click or drag to add to your script. All of the essential activities you perform in Toad for Data Analysts are available.
Edit an activity by double clicking on the activity. This brings up the activity editor. Or edit the activity values in the property tool window on the right. Customizing an activity usually takes only one or two input values.
When you first open the Automation window you see the Script settings activity. This contains the name of the script, exception handling, and logging information. It is essential to set these up in the beginning. Exception handling is done by email notification which is off by default. You need to change Email on Error to true and provide the proper email data. SMTP email is provided, so enter your To:, From:, Server and port values. Each step of your script is logged in a file located in the applications data directory. (Once you set the script setting values it will remember these values for future scripts. You won't need to set everytime. )
Putting it all together
One of the simplest (and most common) tasks to automate is exporting the query results to Excel and emailing the file to a co-worker. Here are the steps to do this.
1) Start up Toad for Data Analysts and connect to the database you want to get your data from.
2) Open up the Automation window and double click on the Script Settings. Change the ‘Email on Error’ to true and fill in your email To, From, Server and port values.
3) From the toolbox on the left click on the ‘Select to File’ activity.
4) You will see a yellow connection square with your ‘Select to file’ activity on top. Since you connected to the right database in step one you only need to insert the path to the sql file to execute and export options. To do this, double click on the ‘Select to File’ activity. Browse to your sql file and click OK. Use the export options window to select Excel, CSV or html settings for exporting the results to file.
5) Add a ‘Send Email’ activity and send the exported file as an attachment. The attachment editor of the send email activity keeps track of all files generated in a script and lists them on the right. Simply press the Add button to attach.
6) Test the script by pressing the ‘Test’ button on the Wizard bar at the bottom of the window. The automation window will save the script, compile and then execute. As it steps through the activities the logging data is displayed in the output window.
7) When you are satisfied with the script and want to schedule it, click on the wizard bar schedule button. (The wizard bar is the bar with the large buttons at the bottom of the window) The scheduling window uses your windows task manager to schedule. Fill out the task specifics. Don’t forget to enter your password.
But for now I wanted to just start with the basics. In future postings I will cover:
· Emailing to a list of users
· Using Dates
· Using variables for string replacement
· Using variables for SQL variables
· How to debug a script
· Looping through datasets
· If conditions
· Row Count variable
· Heterogeneous Queries
For now, those are the basics of what Automation consists of in Toad for Data Analysts. If you need to accomplish a specific task and need some help just post to the Toad for Data Analysts community site and someone on the team will respond. http://tda.inside.quest.com/index.jspa
Have fun!
Debbie
|
|
| Permalink |
Trackback |
Comments (2)
Add Comment
|
By edja on
3/15/2010 6:24 AM |
How can I use Toad for Data Analysis to automate a pivot report that has embedded data in it. When the pivot was created instead of pulling data from another excel sheet, it automatically pulls from a data source. I can't have the users refresh it when they open it since they don't have access to that data source. Is there a way to do it using Toad for Data Analysis?
Thanks,
Edja |
|
|
By Debbie Peabody on
3/15/2010 11:35 AM |
I would use the Toad Pivot Grid report and build an automation script. The Pivot Grid activity will execute the query in Toad, refresh the pivot and then you can export to pdf and email to end user. To access the Pivot Grid designer window, right click from any result set and use the 'Send To' menu. After configuring the fields and formatting save the file as tpg type and use in automation script.
Debbie |
|
|
|
|
|
|
|
|
|
|