Toad Data Point Transformation and Cleanse – Part 1

 In Toad Data Point 3.7 we have rewritten the Transformation and Cleanse window. This new window now includes data profiling information, many more transformation rules and a robust engine for processing larger amounts of data. 

The transformation and cleans utility is accessible by right clicking on a database object in the object explorer, from the tools menu where you will enter a sql to generate the initial dataset or from a right click from a Query Builder or SQL editor grid.

The transformation window will take the first 1000 rows as a sample and generate profiling info. The statistics on the right hand panel are related to the focused row and will help you to quickly explore your data. You can change the sampling by clicking on the “Edit Sample” link and choose random sampling or all rows.

There are 8 different transformation rules that can be accessed at the top tool bar or contextually from a right click menu. When selectedm the editor for that rule appears and you can enter you transform values.

Pressing apply will execute the rule against the sample set, update the profiling statistics and add the rule to the rule tab. The rule tab will show you the rows affected and sequence of the rules. If you find that you use a particular rule often you can add it to the Rules library and apply rule to future transform sessions.

When you have finished defining your rules you can generate SQL,  export to excel or csv, save to local storage, publish to Toad Intelligence Central or send to Automation. The Transformation and Cleanse window does not update the original source of data, Instead it offers a flexible variety of output of your cleansed data.

See part 2 of this series to learn more about applying the rules of Transformation and Cleanse.