Toad Data Point has a powerful feature that makes it easy to cleanse and transform data for the best analysis. You can also save the data locally on your workstation (referred to as local storage) or you can share it with others in your organization using Quest Toad Intelligence Central.
This blog will focus on these areas:
- What is transform and cleanse
- A working example of data scrub
- How to add additional columns
- Saving and sharing cleansed/transformed data
What is cleanse and transform?
The ability to cleanse and transform data is critical for any serious data preparation initiative. This is because data that’s stored in multiple repositories may be in different and inconsistent. Cleanse and transform data functions make columns consistent with activities such as spelling out state codes, changing formats, splitting columns, adding columns and performing calculations. It’s essentially a “data scrub.”
I’ve found that additional columns are helpful if there is some common math that is usually associated with the data, such as a running total. I always recommend replacing null values with zeros. This feature of Toad Data Point can easily do that, as well as set up new columns (and hide the original) where database SQL functions are used. Ultimately, this can make using the data easier for an end user.
A working example of a data scrub
In this section I will transform data and illustrate fixing some bad email addresses.
Example data scrub to fix bad email addresses
This simple query pulls up 14 rows of my test data. Notice the email addresses are a mess. Also notice the COMM field has some null values and some zeros as well. There are other things to fix here but I’ll keep the example short for purposes of this blog.
I start with a simple SQL query, then right click on the data grid and select 'Send to' à'Transform and Cleanse.
Starting cleanse and transform data
This is Toad Data Point Workbook. I have already built the simple query using Query Builder and have added a step. Toad Data Point knows what I can do with a data grid, and I'll choose 'Work Step' and 'Dimensional View.'
Transform and cleanse panel in Toad Data Point
This panel shows a lot of useful information. Along the bottom, you see useful export and save buttons for Local Storage and Toad Intelligence Central (Publish Data), and Automate (to apply these transformations repeatedly to other similar data/future updated data using Automation).
Transform data and cleanse steps
Transformation and Cleanse works by adding steps or rules that will transform data. To see and edit these rules, click on the 'Steps' tab near the upper right corner. You will see the steps populate as we start to transform data and complete the alterations. These rules can be saved so Automation can reapply them repeatedly if necessary.
Find and Replace Panel
Right click on the EMAIL column and pick 'Replace' or click on the binocular button and make sure EMAIL is the column.
Transform and cleanse find and replace function
We will first get rid of the double quotes that appear on some of the email addresses. Fill in the Find with a double quote and nothing in the Replace field. Notice that Toad Data Point shows the number of rows affected.
Click on 'Apply Rule' and you will see the 'Steps' column on the right.
Building cleanse and transform data steps
Next, we will add a .com to the email addresses missing a .com at the end. I have a request into Quest support to add a global character to do this but for now, I do this in 2 steps. I find all the 'Acme.' Domain and add a '.com' to them. This adds an additional .com to the addresses that already had one. THEN I use find and replace yet again to find all the '.com.com' and simply change them back to a simple '.com'.
Use find and replace to cleanse email addresses
How to add columns
I will show two ways to fix null values, and will add a column Total_Compensation (a total of Salary and Commissions).
Using transform and cleanse to fix null values
I can think of a couple of ways to fix null values. Using Find and Replace again, notice it will show nulls, blanks, zeros and other values. To take this next step to transform data, what do you want to do?
Here we could simply change the nulls to zero. When doing the Total_Comp new column, I will show you the null value function. By fixing these nulls here, we don't have to worry about them in the next step. If you get these steps out of order, simply drag and drop them in the 'Steps' panel to rearrange them.
Using the calculated column function
Now we will use the 'Calculated Column' button (the second button on the tool bar or right mouse click from the SAL field). I call this the 'add column' button.
We can simply click on the column names (with columns highlighted on the left) picking the SAL field, clicking on the '+' then clicking on the COMM field.
Adding a new column with a calculated value
If we wanted to fix the COMM field's null values with a function, do the above, then highlight the COMM field and select Control Flow Functions. The function you want here is IFNULL. Make it look like the above panel and this too will work.
Is one better than the other? Not really. I'm used to doing things with SQL so I prefer the functions as I'm a bit "old school."
Seeing the calculation in the steps
Slide over and make sure your math is correct on the new column and we are good here.
Save and retrieve the scrubbed data
You can save the transformed data – and any data in a data grid – to a new data store in either your Local Storage (for your use) or to Toad Intelligence Central (for everyone's use).
Save to Local Storage
Click the Save to Local Storage button along the bottom and name it 'EMP_CLEANSE'. I'm putting it in the EMP_DATA database with the other related data.
To share with others, click the Publish button and this will save your transformed data to Toad Intelligence Central.
New data store with transformed data
Now, access the newly-transformed data as you would any other data store!
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.
Toad Data Point can clean and transform data, merge data, add columns/split columns and more. This feature makes it easy for business intelligence users to save a set of common steps, so they don’t have to fix things with each report. Common calculations can also be made available as an additional column. The cleansed and transformed data can be saved and shared with others in the organization.
IDC Datasheet: Quest Software Acquires erwin Inc.
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!
Dan Hotka has several course offerings that use Toad and Toad Data Point.