Sep 7, 2021 2:00:00 AM by Dan Hotka
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
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 220.127.116.113 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.
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.
Try Toad Data Point for free
Learn how Toad Data Point can help you access and prepare data faster. Seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data source with near zero transition times.
Get started with our free 30-day trial.
Already in a trial? If Toad Data Point is helping you connect, query and prepare data for faster business insights, buy it now or contact a sales representative.
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
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
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!
Tags: Toad Data Point Data Modeling Data Prep data preparation data design data warehouse
Written by Dan Hotka
Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.
Dan's most recent book is Toad for Oracle Unleashed