One might be tempted to think that with such a young tool like Toad for Data Analysis (TDA), that it would be a bit early to start trumpeting its advanced features and ‘hidden’ productivity boosters. In this case, one would be very wrong! Although Toad for Data Analysis is only up to release 1.1, it is built on top of Toad for SQL Server and Toad for DB2’s award winning platform. The development team has also included a couple of powerful new widgets just for TDA.
A Few of My Favorite Things…in No Particular Order
Searchable Code Snippets
TDA comes with references to ALL of the database provided functions you can use when building your own SQL queries – regardless of database provider. To access this list, go to View – Code Snippets. Three things to try:
- Do a keyword search. It will then filter the list and only show you the entries of interest.
- Try the online reference lookups to see the vendor supplied documentation on that particular function call.
- Double-Mouse-Click the entry to have the code appended to your Editor for immediate use.
You can dock the Code Snippets panel to your place of choice and un-pin it so it remains out of your way when you don’t need it!
Dynamic Date Filtering for your Queries
I want to create a report that queries the database for employees for a certain list of departments and were also hired in the last month. I want to schedule this report to run every 3rd Wednesday. Believe it or not, I can do all of this with TDA with a few point-and-clicks.
Step 1: Open the Query Builder
Step 2: Add the tables that contain the data you need.
Step 3: For the ‘last month’ filter, you COULD write a for SYSDATE – 30, or you can use TDA’s supplied Date Filters. Just click on the ellipsis button on your DATE column, and you’ll see this:
TDA will take your selection here and put a custom TDA hint into your query:
SELECT ...
WHERE ("EMP".HIREDATE = '' /*Current week*/ )
AND ("DEPT".DEPTNO ...
When TDA reads this hint, it will dynamically replace that text with the appropriate SQL call.
View Tables and their Related Data by Writing ZERO SQL
The Master-Detail browser is probably one of the most well received features when I demonstrate our technology to the Analyst audience. I can simply add a few related tables to my modeling area, and Toad will automatically find the relationships and offer to display the data as it relates to each of the tables.
Let’s look at a small example with Oracle’s supplied HR schema.
Step 1: Pull up the proper schema in the Object Palette.
Step 2: Send the tables to the Master Detail Browser
It’s as simple as Multi-selecting your tables, and Mouse-Right-Clicking.
Step 3: View the relationships.
Step 4: If it looks OK, press the ‘Play’ button and start browsing!
I select a region, and then see all countries for that region. I can then select an address, and then browse the departments for just that address!
Do You Have Your Own Tips?
Please contribute to our
TDA Community or post them as replies to this blog so that others can learn from your experience!