In Part 2 of the product review for Toad for Data Analysts
(TDA), well look at several of the more advanced features of this application
(part one is covered here).
TDA offers more sophisticated users features such as data synchronization,
automation, and reporting.
As a DBA, how many times have you been asked to run a query
and provide the results in an Excel format? And, to extend this scenario a bit
farther, how often do you have to provide a flat file (typically in comma
separated value format) as a means of transferring data from your company to
somewhere else? The manual process often times includes you having to script
the select statement and concatenate columns and commas. This is also a fairly
common question seen on various DBA forums (How do I dump a table into a flat
file?). With TDA, this task just became trivial. The only work you have to do
is to create the query. From there, select the type of report on the Excel
report tool bar.
Using the Departments table in the HR sample schema, simply
open the table and then click on the type of output you want.
Another route to get to the report generation is to run the
query in a SQL Editor workspace (or use Query Builder), and once the results
are displayed, click Send to Report (bottom right corner of the window) and
an instance of Excel opens inside of TDA. From this point, you can send the
report via email to whomever. Click on Email (also at the bottom of the window)
and your default email tool (e.g., Outlook) opens with the file already
included as an attachment.
Oh wait, the requirement is to run this report on a regular basis.
Sounds like an excellent time to employ automation, and Quest certainly had
this in mind when designing the workflow to facilitate this requirement.
Aside from showing the user what the steps are, TDA also
shows the details needing attention. In the example above, the expansion of the
red exclamation mark reveals three items needing resolution. If you already use
Toad for Oracle, the SMTP server you specified in your Toad setup is more than
likely the same server you would use here. If you dont know who/what your SMTP
is, look at the header of an email in your inbox. An example from Toad
) is shown below.
Once the automation script has been designed in the Design
Surface window, you are ready to schedule it. The scheduling interface is easy
One tip on using the scheduling feature concerns the
username/password account to authenticate with your mail server (assuming
authentication is required). It would be handy to create a non-human domain
account for this purpose, just like you would in SQL Server (e.g., the Logon
As account used for starting a service, an authenticated user for mirroring,
and so on). Once scheduled, sit back and wait for the email.
TDA also allows you to use templates (think re-usable code),
add exception handling, and change logging levels within the automation design
So now that we can easily automate reports, pretty much
anything you can use reporting for is at your disposal. A simple means of
comparing data can not only be automated, but also reported upon. Open a table,
right-click it and select Data Compare from the context sensitive menu. Follow
the steps within the easy to use Data Compare Wizard, and just like that, you
have a means of detecting changes between tables. Again, it is not just tables
within the same database or schema, but between many database systems. Any
database system TDA can connect to is eligible for use as a source or target
when it comes to data comparison. Not only can you compare data, but you can
synchronize as well (with the exception of Excel).
Suppose a user suspects that a table is missing some data,
and the user knows he has a good source elsewhere. In pure SQL, it is likely to
be a trivial task to do a minus between the tables. For someone with lesser
knowledge of SQL syntax, the point and click interface offered by TDA has an
immediate return on enhancing the users productivity. No calls to an otherwise
busy DBA are needed.
Select a source, select a target, select what it is you want
to compare (i.e., table to table), and view the results. In the example below,
the comparison is between the same table, with the 2 version missing a row present in the source.
What are some of the possible differences? Without having to
think about the answer, TDA suggests additional, missing, different, identical
(i.e., no difference), and conflict. Knowing now that the source table has an
additional row when compared to the target/copy (keeping in mind the context of
which object you picked as the source; the opposite being that a row is
missing), what then? How about synchronizing the tables?
The Synchronization Wizard gives you a choice on which
direction to synchronize: target to source or source to target. You can run a
fix immediately, or choose to see what is going to take place via the
appropriate DML statement. Viewing of the script takes place in an editor
Considering only Oracle, what does this process sound like?
Materialized view should come to mind. Looked at another way, the data
synchronization process, especially if incorporated into a scheduled task, is a
poor mans way of implementing materialized views. Within Oracle, you would no
doubt simply use an MV for this purpose. But, given TDAs ability to connect
across many database systems, and having a requirement to refresh or keep data
in a different RDBMS in synch with <pick your system of choice>, this
feature of TDA can go a long way in simplifying an otherwise complex task for
More information about Toad for Data Analysts, including an
extensive help resource in the form of online demonstrations, can be found here at Quest
Softwares TDA page. Overall, Toad for Data Analysts is very impressive in its
ability to make users more productive. It is simple to use, has a clean
intuitive interface, and is loaded with many useful features.
See All Articles by Columnist Steve Callan