Daniel Norwood's Blog

Calling all Visual Studio users! (and those who want an Apple iPad!)

Daniel Norwood — Fri, 19 Feb 2010 22:54:00 GMT

I'm proud to announce that "ProjectFuze", the code name for Quest Software’s database schema provider (DSP) for Oracle in Visual Studio 2010, is now part of our Toad® solutions! Beginning with Toad for Oracle over 10 years ago, the Toad family of products is designed to help you do your job better, regardless of which platform you work on today - or tomorrow!

Things Everyone Should Know about Toad for Data Analysts

Daniel Norwood — Mon, 21 Sep 2009 19:37:00 GMT

Written by Jeff Smith, Quest Solutions Architect

Toad is a brand at Quest. We have several tools that have been built explicitly for developers and administrators in the Oracle, DB2, SQL Server, and MySQL universe. After gaining the following of about 2 million users over the past decade we realized that more than a third of our users did not fall into the ‘developer’ or ‘DBA’ category. Toad had been adopted by a class of user that we had not planned for!

After realizing the number of business analysts, technical analysts, support analysts, and insert-your-job-title-here we had using our products, we decided to build a Toad just for them. This resulted in the development of Toad for Data Analysts which debuted in the summer of 2007. This tool is built from the ground up for someone who primarily needs a tool to get at the data in a database. Simply put, Toad for Data Analysts is a query tool for any/every database platform.

The PDF document you can download below will go over the compelling features and use-cases. This should give you an idea of how you might be able to benefit from the tool. Please feel free to share this document with anyone in your organization who might also be able to benefit from Toad!

PDF document, 1.3 MB

Reverse Engineer a SQL Query

Daniel Norwood — Tue, 25 Aug 2009 03:30:00 GMT

Have you considered switching to Toad for Data Analysts but have too much invested in your old query builder? Have you ever looked at a query with a complex join clause and wished that you could see it mapped out visually? You're in luck - Toad for Data Analysts can help! Toad for Data Analysts can take your SQL statement and reverse engineer it into a visual Query Builder. In this blog post, I’ll show you how to do it quickly and easily!

Configure your Toad!

Daniel Norwood — Wed, 29 Jul 2009 19:21:00 GMT

We all come from different backgrounds... some of us are old Toadies, and some of us are used to different tools. Did you know that Toad for Data Analysts can be configured to act and look much like Toad for Oracle and many other tools? Even down to the keyboard shortcuts? I'll show you how.

What's Next: Toad for Data Analysts v2.5 New Features (part I)

Daniel Norwood — Tue, 30 Jun 2009 23:10:00 GMT

Toad for Data Analysts v2.5 is just around the corner. In anticipation of the release, I want to highlight a few of the enhancements that we've made to the product. I'll be posting a new feature every few weeks, so stay tuned!

Update your Excel reports... automatically!

Daniel Norwood — Fri, 29 May 2009 22:02:00 GMT

If there's one thing we hear people asking about it's "How can I use Toad for Data Analysts to automatically update my Excel report?" This blog entry is dedicated to just that. I'll show you how you can get your Excel reports to update from the database in just a few steps - and automate the process for next time!

Creating multiple XLS files through Automation

Daniel Norwood — Fri, 27 Mar 2009 23:30:00 GMT

If you’re like many of the analysts I speak with on a regular basis, then you use Microsoft Excel. It’s a staple of the Information Age, right!? It seems like you can do everything but wash your car with Excel. But what if you want to get that data out of your database and into a series of worksheets… on a regular basis…? Unless you have lots of free time, this is probably something that you don’t look forward to. Let’s take a look at how Toad for Data Analysts can make your Excel-life even easier!

Getting to know the Query Builder in Toad for Data Analysis

Daniel Norwood — Wed, 05 Nov 2008 22:04:00 GMT

Query Builder is the heart and soul of Toad for Data Analysis. We've spent lots of time and effort working to make it very useful and intuitive. Recently, in version 2.0 of Toad for Data Analysis, we introduced a few new features including reverse engineering SQL to a Query Builder model, creation of DDL scripts and better connection awareness. The linked doc is a very nice write up that Shawn Pickett (Senior Developer) and Alan Bala (Development Team Lead) have put together.

Now part of Toad for Oracle: Toad for Data Analysis!

Daniel Norwood — Fri, 19 Sep 2008 18:46:00 GMT

Toad for Data Analysis now comes included with Toad for Oracle!

Writing Better Queries Starts With Understanding

Daniel Norwood — Mon, 23 Jun 2008 13:52:00 GMT

For those of you who may not come from a super-technical background, the term “ER Diagrammer” may sound strange or unfamiliar. Well, for the record, the term “ER Diagrammer” stands for “Entity Relationship Diagrammer” – a fancy name for a tool that displays the relationships between objects (like tables) in the database. You may ask, “Ok, but why is such a technical tool in a product like Toad for Data Analysis that’s supposed to make things easier?” Such a tool is actually indispensible for anyone that wants to retrieve accurate data in an efficient manner. In this article, I’ll briefly describe the benefits of Toad for Data Analysis’ ER Diagrammer and cover its use. (And don’t worry… its super simple to use!)

First, why should I use the ER Diagrammer?

Well, the very first reason is that the ER Diagrammer – let’s call it ERD for now – is going to show you a sort of “map” of your database or schema. If you just made a connection to the database and wanted to write a query, how would you know if there was more data that you need in some other table(s)? Obviously, the whole point of a relational database is that you can store like records together in their own tables and then define a relationship between that table and some other table. If I have a church database with member records, I may want to put all of the adult members together in a table and then create a separate – though related – table for the records concerning the members’ children. In each table I need to store different kinds of information: most adults won’t be in grade school (I hope) and the children won’t have email addresses or work telephone numbers… If I want to count the number of people in the church, I probably need to include both the MEMBER and CHILDREN tables in my query. The ERD visually maps out the relationship so I can see that these two tables are related.

Another reason to use the ERD is that, in Toad for Data Analysis, you can use it as the basis for your query. If you use the ERD to gather all of the tables you want to query from, then when you send the tables to the Query Builder or the SQL Editor, the tables and the necessary JOINs are already pre-populated – all you have to do now is select the columns you want to get data from. Even if you’re a pro at writing SQL, you can still save time and prevent headaches associated with getting all of your JOINs set up properly by using the ERD.

Perhaps one of the less obvious benefits of the ERD is the fact that you can use it to document schemas or databases. Imagine that church database from the previous example: if the relationship between the CHILDREN and MEMBER tables didn’t actually exist in the database, you can use the ERD in Toad for Data Analysis to manually define the relationship and then save the diagram as a .ter file. Now you have the schema or database documented for future reference or use without actually making changes on the server – very important if you don’t have the permissions to make such a big change! You can even use these saved ERDs for new people or people that have less experience writing queries; just hand the ERD to them and let them use the tables it contains to begin building their query.

Using the ER Diagrammer

So, enough talk; let’s take a look at the ERD.

Screenshot of the ER Diagrammer in Toad for Data Analysis

The ERD begins as a blank document with a graphic in the center describing how to use the tool. To get started, either double-click on a table in the Object Explorer on the left or just drag-and-drop it onto the ERD document. If the table has a relationship defined in the database itself, all of the tables that are directly related to the one you selected will also appear on the document.

Tables and their relationships

In the above example, I double-clicked on ORDERS and along with it came the CONTACT and ORDER_ITEM tables, because of their database-defined relationships. I can add more tables and I can remove tables; I can even add my manual relationships now by selecting one column in a table and dragging it over to a column in another table. But regardless of what I do from this point, I’m no longer guessing about where my data is and whether or not there are other tables in the database that I need to select from; I’m beginning to understand the schema or database before I begin writing my query.

I added the WAREHOUSE table and manually defined the WAREHOUSE_ID relationship

Once I have the tables I want in the ERD document window, I can add notes to further document the table, I can hide columns in the table if there are just too many to work with effectively, and I can even associate a lookup table to an ID column. And with that, I can then save the ERD as a .ter file or I can move on to my query… but more on these in another article!

Daniel Norwood
daniel.norwood@quest.com

Changing the look and feel of Toad for Data Analysis

Daniel Norwood — Tue, 20 May 2008 08:44:00 GMT

We’ve done a lot in Toad© for Data Analysis to make it user friendly and intuitive. Naturally, what’s user friendly to one person may be a bit confusing to someone else. This post will show you how Toad for Data Analysis can be changed to something more comfortable or familiar.

Working With Date Ranges in Toad for Data Analysis

Daniel Norwood — Wed, 30 Apr 2008 17:13:00 GMT

Often when writing a query you need to get data from a range of dates; when you start thinking about automating the SQL query it’s even more important. In this post I’ll show you a quick trick to let Toad for Data Analysis figure it out for you!

For this example I’ll be working with Oracle though it’s no different for other platforms. In my Oracle database, if I wanted to get a list of the orders that were placed last year I’d have to write something like the following:

SELECT ORDER_NUM, BOOKINGS_DATE
 FROM LICENSE_BOOKINGS
 WHERE (BOOKINGS_DATE BETWEEN to_date
                                 ('2007-01-01 00:00:00',
                                  'yyyy/mm/dd hh24:mi:ss')
                          AND to_date
                                 ('2007-12-31 23:59:59',
                                  'yyyy/mm/dd hh24:mi:ss'));

Sure, some people are familiar with the syntax. But what about the syntax for the last quarter, the current month or just last week? It’s not rocket science, but it can get confusing and is quite error prone.

Now, in the SQL above, I just hard coded the dates – that’s a dangerous practice in most cases. So what do I do? I start thinking about maybe writing it a little bit differently… maybe you use a bind variable or I start working with SYSDATE to get the system’s date and first and work backwards from there. If I want to automate the query, this is the path that I have to go down.

In Toad for Data Analysis we make life a little bit easier – we write the date ranges for you. So now instead of using complicated SQL to get the job done, you can drop in something like /*Last week*/ or /*Last 30 days*/ and Toad for Data Analysis will dynamically insert the correct SQL. Keep in mind that this is a Toad for Data Analysis feature – if you execute the SQL statement in something like SQL*Plus the Oracle compiler will see /*Last week*/ as a comment and your statement will not run.

Here’s how the revised query will look:

SELECT ORDER_NUM, BOOKINGS_DATE
 FROM LICENSE_BOOKINGS
 WHERE (BOOKINGS_DATE = '' /*Last month*/ );

(Note that the criteria is empty – I use two single quotes with no space. This is important. Toad for Data Analysis will insert the correct SQL between these two single quotes when I execute the query.)

If you use the Query Builder in Toad for Data Analysis it’s super-simple. Once you select a column with a date datatype, click on the “Where Clause” field to open a new window where you pick from a list of available ranges. Viola! You’re done.

For those of you writing SQL scripts by hand, here’s a list of the available commands. Keep in mind that they’re CaSe SeNsItIvE… if you don’t write it exactly as you see here then it won’t work.

/Today/	/Current month/
/Yesterday/	/Last month/
/Current week/	/Current quarter/
/Last week/	/Last quarter/
/Last 7 days/	/Current year/
/Last 15 days/	/Last year/
/Last 30 days/

If you want to automate the script you’ve created, save it to disk and then click “Automate” on the Toad for Data Analysis toolbar. After you build the automation routine you can click “Schedule” to add it to the Windows Scheduler and set it to run anytime.

If you would like to see other articles on Toad for Data Analysis, please email me at daniel.norwood@quest.com.

/Today/	/Current month/
/Yesterday/	/Last month/
/Current week/	/Current quarter/
/Last week/	/Last quarter/
/Last 7 days/	/Current year/
/Last 15 days/	/Last year/
/Last 30 days/