Toad Data point 3.7 includes a redesigned and more robust version of the Transform and Cleanse tool. Please visit the ToadWorld Toad Data Point Community (http://www.toadworld.com/products/toad-data-point/b/weblog/archive/2015/03/20/toad-data-point-transformation-and-cleanse-part-1.aspx) for a video about this tool.

Some enhancements:

  • The Transform and Cleanse window now contains data profiling information. Use this information to help assess the quality of your data and determine where to apply transform or cleanse rules.
  • New rules can be created by clicking one of the rule buttons in the Transform and Cleanse toolbar, or by right-clicking the data grid and selecting a rule category. Then define the rule in the rule editor in the lower pane.

  • Toad now makes recommendations for new transform and cleanse rules for you to apply based off the data selected for transformation. The suggested rule displays in the Column tab in the right pane within the data profiling information. Toad provides recommendations for only Trim and Convert Data Type rules at this time.

  • When creating a Calculated Column to transform data, Toad now allows you to use functions supported by the database. Select the Use SQL Transforms option in the Calculated Column rule editor.

  • SQL is generated for the underlying query and all the applied rules, if all rules support SQL generation. Rules that do not support SQL generation display in red text in the Steps tab for easy identification. This gives you the opportunity to modify or remove the rule if SQL generation is necessary.

Note: SQL generation is currently only supported for Oracle and MySQL connections. For other database types, SQL is not generated and all rules display in red text.

  • You can now publish transformed data to Intelligence Central as a snapshot or view, if all rules support SQL generation. If all rules do not support SQL generation, you can publish as a dataset only.

Note: Publishing transformed data to Intelligence Central as a snapshot or view is currently only supported for Oracle and MySQL connections. However, you can publish transformed data from other data sources as a dataset.

  • You can create a Trim rule to trim leading and trailing spaces from values. This allows you to remove ASCII control characters and to consolidate whitespace.

  • There is a new Convert Data Type rule, which allows you to convert a column's data type.
  • You can create a Format rule to format the data in a column. This allows you add number, currency, and date/time formatting to values.

Note: Applying a Format rule converts the target column to a string data type in the transformed data.

After modifying data, you can export or publish the transformed data, send the SQL to the Editor, or automate the transform and cleanse task. You can also save your work as a Toad Transform and Cleanse document.

Please note, Toad Data Point 3.7 still supports legacy Toad Transform and Cleanse documents (.tdc). The legacy Transform and Cleanse tool is retained in this release. The Cleanse Data automation activity also still supports legacy Toad Transform and Cleanse documents (.tdc).

You cannot publish a new Transform and Cleanse document (.tct) to Intelligence Central at this time. However, you can publish the transformed data. 

Next step:

See the new Transform and Cleanse tool in action!  View the demo video to learn how to access and process data using the transformation and cleanse utility in Toad Data Point from Dell Software.