Product Review: Toad for Data Analysts

October 28, 2009

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.

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.

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.

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.

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.

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.

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.

» See All Articles by Columnist Steve Callan