How to use Quest® Toad® Data Point to improve data integrity

What is data integrity

According to Digital Guardian, “Data integrity refers to the accuracy and consistency (validity) of data over its lifecycle. Compromised data, after all, is of little use to enterprises, not to mention the dangers presented by sensitive data loss. For this reason, maintaining data integrity is a core focus of many enterprise security solutions.”

With the increased importance and emphasis on data for informed decision-making, everyone who works with data must be committed to ensuring the highest levels of data integrity. To that end, Quest^® Toad^® Data Point is an excellent tool and can be used by data analysts and business professionals alike.

How Toad Data Point improves data integrity

Toad Data Point focuses on improving data integrity by performing transformation and cleanse operations on a data set without the need for assistance by a database professional. The next section will illustrate how easily Toad Data Point adjusts data, makes data consistent and allows for transformation rules to be saved and applied during automation.

Toad Data Point Workbook Transformation & Cleanse

The Transformation & Cleanse process allows for a set of rules to be applied to a query results set to change or correct the data as required. This process helps ensure data integrity for reports based on the data set and for the larger body of data.

Toad Data Point Workbook Fixing Null Values

To me, one of the most important actions is fixing null values. When you add something to a null (or nothing), you get nothing. If you are not paying attention to the data, you can get serious erroneous results from this simple issue.

Toad Data Point can do a simple Find and Replace to substitute the nulls with zero.

Toad Data Point Workbook Replace Template

Notice on the right that Toad Data Point has found 10 null value columns in this data. Fill in the Find are by picking 'null' from the drop down list and fill in the Replace line with 0. Click on Apply Rule.

Toad Data Point Workbook Replaced Data Ready for Use

Notice the Comm field is now fixed with zeroes. Now the user of this data doesn't have to worry about null value math errors. These rules (see Steps tab) can be saved and reused in automation streams.

Toad Data Point Workbook New Calculated Column

Toad Data Point can easily set up additional columns of math on existing columns of data. Again, this ensures data integrity by avoiding math errors or errors in the calculation. All the end user has to do is use the new column! The data is transformed before they use it.

Click the Calculated Column button. Give the new column a useful title in the Column Name field.

Toad Data Point Workbook New Calculated Column (filled in)

You can use your mouse to build the math. Click the Apply Rule button.

Toad Data Point Workbook Completed Math

Now, there is a new TotalComp field and the math is correct. You can use this same feature to apply functions to your data and cleanse additional data via database or programmed functions.

This data can be saved for future use in a user’s 'local storage.' It can also be published to Toad Intelligence Central (a cool repository) for multiple users to access. Toad Intelligence Central also has security features so only certain people can change the data or even see the data.

How relational databases guarantee data integrity

All relational databases have what is called referential integrity. This feature uses primary and foreign key constraints. One table, the parent, has unique values for the primary keys. The child table has the foreign key and this table can have no rows to any number of rows in it, that match the primary key in the parent table. You cannot insert child table rows that do not have a matching row in the parent table. You cannot add duplicate parent table rows.

Toad Data Point Finding Referential Integrity Automatically

Toad Data Point automatically sees these hard-coded database relationships when you are building SQL using the Query Builder feature. Your selected data will have integrity in the relationship. You do not have to worry about (in this example) the DEPT table having unmatched rows to the EMP table.

Closing

Toad Data Point can adjust data without requiring assistance from IT, or data analysts can easily build adjusted data sets for business users. This feature, Transformation & Cleanse, can even be automated for future reuse. Toad Data Point also takes advantage of database primary/foreign key constraints which improve data integrity at the database level.

Related links:

Blog: How to use Toad Data Point to solve data preparation issues

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!

Dan Hotka has several course offerings that use Toad and Toad Data Point.