How to use Toad Data Point to solve data preparation issues

This short series discusses many of the often-overlooked issues surrounding data preparation for business analysts. Toad Data Point is the perfect tool for making data preparation easy.

This blog will cover:

• Multiple database/data source connectivity
• Data cleansing/transformation
• Data subsetting and data maintenance

Multiple database/data source connectivity

Toad Data Point is the best tool I’ve worked with for data preparation for the business analyst. Not only did Quest apply many of the best features of the popular Toad tool for use here, they greatly enhanced this tool’s ability in many ways.

First, I’ll show the multitude of database and data platforms supported by Toad Data Point.

Figure 1: Toad Data Point Connectivity Panel

This wide range of support allows Toad Data Point to connect to any of these data sources and include their data stores in a single query in either the Toad Data Point Query Builder window (my recommendation for cross-platform data access) or the Toad Data Point Editor window.

Notice that Toad Data Point can use Microsoft spreadsheets and/or Access databases as a data store! Yes, you can also load Microsoft data easily into Toad Data Point’s local storage.

Any data store that is not in this list but has the ability to be accessed via an ODBC connection is also supported by Toad Data Point.

***Note*** The author is using Toad Data Point version 5.3.0.333 for these illustrations. All of these features shown here should work the same in any version of Toad Data Point. These are not new features to Toad Data Point, but base functionality that has been a core feature of the tool all along.

Data Cleansing/Transformation

A powerful data preparation feature of Toad Data Point is the ability to easily change, correct and add data columns to an existing data extract.

This feature is accessed from the data grid using a right-mouse click and selecting Send to > Transformation and Cleanse.

Figure 2: Toad Data Point Data Send to Menu

Figure 3: Toad Data Point Data Menu Bar Find and Calculated Column Buttons

This example demonstrates fixing the null values that business analysts often encounter and have to fix after they notice the math didn’t work. This example also shows how Toad Data Point enables enhancing the data by using a calculated column that adds the SAL and COMM fields together.

Figure 4: Toad Data Point Fixing Null Values

To perform this operation, I clicked on the COMM field, then the binocular button (Find) (you can also right click on the column and get the same menu items) then filled in the Find box using the drop down menu and putting a 0 in the Replace box. See illustration above. Click on ‘Apply Rule’ when done. You will notice the rule is applied and the data changed to reflect the rule.

These data preparation rules can be saved and applied again using other queries and during an automation process.

Now, I will click the ‘Calculated Column’ button. I often call this the ‘Add Column’ button because this allows you to run literally any function/data manipulation/concatenation, etc., and have the output of these functions appear as a new column.

Figure 5: Toad Data Point Adding a Column

With the ‘Calculated Column’ button, you can build almost any calculation or apply almost any function using your mouse. I double clicked first on SUM in the center box that lists the columns (notice the columns feature is selected in the left column). I then clicked on the ‘+’ sign just above these boxes, then I double clicked on the COMM field name. I gave the column the name ‘Total_Compensation.’ When you click the ‘Apply Rule’ button, you will see the new column appear, populated by the correct data, i.e., our null values are fixed and the math works correctly here.

Data subsetting and data maintenance

The query could have fixed the null value and done the simple math, but I wanted to show how easily Toad Data Point could fix the data as well. The query could have selected data for a subset to be used in other queries, perhaps using a date range. The query could also have a group by clause allowing the data to be summarized.

The output of any query can be saved to your local storage – a database that is internal to Toad Data Point or Toad Intelligence Central. Saving to your local storage allows you to reuse the data as long as you want. No one else can see it or use it. Saving the data to Toad Intelligence Central allows anyone assigned to your group, or anyone using Toad Intelligence Central (this is a simple permissions thing which is easy to assign in Toad Intelligence Central) to reuse the data.

I want to save this data to Toad Intelligence Central and have Toad Intelligence Central automatically refresh the data once a day at midnight. This way, the data is always ready to go when the business analysts arrive in the morning.

***Note*** This feature addresses the issue of having to work with IT staff to save your subsetted data.

Select the button ‘Publish Data’ found along the bottom of the data panel.

Figure 6: Toad Data Point Publish to Intelligence Central Panel

This brings up the ‘Publish to Toad Intelligence Central’ panel as seen above. There isn’t space in this blog to go into Toad Intelligence Central setup, but you can make folders and store almost anything you want to share. I will use the default values here except for the Scheduling feature.

Notice the Sharing link. This link allows you to define whether this data is useable just by you, a selection of people, your group, or public (anyone with access to Toad Intelligence Central).

Also notice you can index this data store, another topic for another time but Toad Data Point allows for performance features to be included.

We want this data store to automatically refresh and the name to remain the same. I have clicked the ‘Overwrite Existing Object’ box and will click on the Scheduling link.

Figure 7: Toad Data Point Scheduling the Refresh

These refresh options. and anything selected here, can easily be changed using the Toad Intelligence Central console. I have selected that this data be created in Intelligence Central now and refreshed daily at midnight.

Make your changes to the schedule and click ‘Publish’ when ready to create the data store in Intelligence Central.

Figure 8: Toad Data Point Viewing New Object in Intelligence Central

Now, connect to Toad Intelligence Central and you will see your data store. I double clicked on it here to bring up the object viewer so we could see our data preparation changes (null values fixed) and our new column. This data store can be used just like any other data object now. Since it is in Intelligence Central, it can be used by anyone in the group (for example).

If you are subsetting/summarizing/fixing data, this task can be performed once, then automatically refreshed thereby saving the data analyst from having to perform these data preparation tasks, and allowing them to be more efficient in their data analysis.

Data preparation is easy with Toad Data Point and its rich features for cross-database support, single query data access (across same cross-database platforms), data cleansing/transformations, and saving the data for use by others. This saved data can then be automatically maintained using Toad Intelligence Central

Watch the video demo

Related links:

Blog / video: What is data preparation and why is it key to the business analyst?

Webinar: Answering Your Top Five Questions About Toad Data Point Automation

Ebook: The Four Roadblocks of Data prep

Visit Quest for data prep and provisioning with Toad Data Point and Toad Intelligence Central

IDC Datasheet: Quest Software Acquires erwin Inc.

Visit Quest erwin, for enterprise data governance

Blog: How to prepare data for machine learning

Blog: Easy data prep: Toad® Data Point v5+ new features

Quest blog: Data prep and Analysis: Using Data Efficiently in the Era of Dark Data

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!