Toad for Data Analysts (TDA) is yet another product in a
long line of highly useful, well-designed, productivity enhancing applications
developed by Quest Software. The branding behind the purpose of this tool gets
right to the heart of what TDA can help you accomplish with data: Query,
Understand, and Report.
If you are a database administrator, don’t let the name of
the tool (just because it has “data analyst in it”) dissuade you from taking a
serious look at what it can do for you. If you are already managing (including
querying and reporting) more than one RDBMS source, chances are you are using
more than one RDBMS specific tool to do so. One of the main features of TDA is
that you can now do so with this one application. But wait, how does data
analyst come into play?
The intended audience for this tool is not a specific job
title or position. The term “analyst” really covers a lot of roles. As a DBA,
how many times have you been called upon to provide a report to management or
whomever, or to interpret a result set for a business analyst? Unless you are
deluged with database administration tasks all day long, you probably spend
some of your time being an analyst. So, “analyst” really covers a lot of roles.
TDA is meant to be as flexible as possible and target a reasonable middle
ground with respect to its capabilities when compared to RDBMS-specific tools.
As an example, Toad for Oracle is the de facto standard for
GUI-driven administration of Oracle databases. Both products share a common
functionality related to querying, understanding and reporting. You wouldn’t
(or couldn’t, depending on the task) use TDA to manage tablespaces, and at the
same time, DBA module features in Toad for Oracle have hardly any use in terms
of analyzing data. But for those middle ground or occasional users who are
familiar with database systems, TDA will definitely satisfy their needs.
One item worth noting is that Toad for Data Analysts is
included in later versions of Toad for Oracle. No extra add-on fees, no extra
modules to install, and it is ready for use as soon as you install Toad for
Oracle.
Click for larger image
Figure 1 – Features of Toad for Data Analysts
Figure 2 – Different roles supported by one product
Specific features of interest in Toad for Data Analysts
TDA provides an intuitive interface, which is quite useful
for infrequent users. Upon startup, a workflow is presented (which can be
turned off for more experienced users) and TDA goes out of its way to make the
query-understand-report process a no-brainer.
Figure 3 – Workflow for Query-Understand-Report
In the “Understand” interface, users get a fairly robust
entity relationship diagrammer (ER Diagrammer). In less than a minute, you can
generate an ERD of a schema (or whatever selection of tables/views you have in
mind). Using the HR sample schema, highlight/select all tables in Object
Explorer, drag them onto the drawing canvas, and voila, you have an ERD.
Figure 4 – Entity Relationship Diagram
Think about what the ER Diagrammer can do for the occasional
user or the business analyst who needs to see a visual representation of tables
(including their columns). When compared to the cost of Visio or other
ERD-generating tools, Toad for Data Analysts is a bargain when you consider
price alone. For the “let’s empower the user” approach, TDA is also hard to
beat in this area.
Continuing on with the use of the ERD tool, the next step is
to take selected tables and manually tie them together (aside from how they may
already be related via referential integrity) via the Query Builder feature.
Again, it is a simple matter to select the tables of interest and drag them
onto a canvas/work area, select columns which will appear in the SELECT
statement, and run the query. Using the countries, locations and regions tables
to generate a report on location IDs and their respective region and country
names as an example, a report can be generated in seconds. A sample of the
output is shown below.
Figure 5 – Partial Query Results
Clicking on the Query tab at the bottom of the work area
(the tab may look disabled, but it isn’t) results in the query being displayed.
Take note of the ANSI SQL syntax as opposed to Oracle’s SQL syntax.
Figure 6 – Generated Query Statement
Viewing the Explain Plan (using that tab at the top of the
Query Builder page) for a query results in a graphical representation of the
plan. A fairly sophisticated user will probably have knowledge of indexes and
how they may help (or hurt) the performance of a query. With the graphical
output, users can see exactly what took place at each step along the way. The
relatively simple query above used two different types of index scans, along
with nested loops and hash joins.
Figure 7 – Explain Plan from Query Builder
In Figure 7, the cursor was left over Step 4 (the View). The
base objects are tables, so why is a view involved? Based on the explanation in
the popup, the view represents the output of the hash join in step 3. If you
are familiar with how SQL Server shows an explain plan in Management Studio,
you’ll feel right at home with TDA’s output.
Being able to access multiple database systems is one of
TDA’s best features. Quest provides you with an out of the box Access database,
which is simply a file-based source in the installation tree where TDA was
installed. It is a trivial matter to have TDA read or detect which Oracle
databases you can connect to, or more accurately, what you have in a
tnsnames.ora file. SQL Server connections are made in a manner similar to how
you would make a new connection to a database engine in Management Studio.
TDA will also read Excel xls files, given that you take an
additional step within Excel. For the data of interest in a worksheet, select
the range and save it as a named region. Then, back in TDA, make a new
connection to Excel, browse for the file (have to close Excel first), and
import the region. Once that is done, you can query the “table” in Query
Builder and view the output.
In Closing
Toad for Data Analysts is loaded with features. If you are
looking for a “one stop, get all of your database connections and reporting
needs” type of tool, this one product will definitely meet most, if not all, of
your requirements. Finally, you get the same great support and access to a knowledge
base like you do with other Quest Software products. In the follow up article
for this product review, I’ll cover other features such as reporting,
automation, and data synchronization.
