Nov 30, 2021 3:00:00 AM by Dan Hotka
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.
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.
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.
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.
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.
IDC Datasheet: Quest Software Acquires erwin Inc.
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.
Tags: Toad Data Point
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