Need to trap data defects in Oracle? Download this free data quality pattern analyser
Technology, Tutorial | by Dylan Jones (Founder) 
As part of a series of free tools we are providing to help people understand the benefits of data profiling, we are giving away another pattern analyser, this time for embedding directly within Oracle.
So now you can stop defects before they hit your database, discover new data quality rules and assess a variety of data quality issues the naked eye simply cannot spot - all for free!
The Importance of Pattern Analysis
Pattern analysis is one of the most vital tools in your data quality toolbox.
It can help you discover a wide range of formatting and data entry issues that are notoriously difficult to spot with the naked eye or SQL techniques.
These kind of issues are often the "low hanging fruit" issues that are often the easiest to identify with the right tools and techniques.
Invalid patterns, like the ones opposite can cause serious business issues ranging from billing errors, reporting errors, data migration failures, master data management integration failure, and all manner of associated problems in your business service.
Pattern analysis allows you to discover many of the data quality rules that are required to effectively manage your data assets.
Using this tool you will learn the basics of pattern analysis before moving on to some of the more powerful tools typically seen on the more expensive DQ products.
For example, in this example opposite, we can quickly spot invalid domain values for a bedrooms value field that is taken from a housing database as they fall outside the standard pattern of "d" and "dd" ie. single or double digits.
Benefits of the Free Pattern Analyser for Oracle
This tool is not just useful for training purposes, you can put it to use immediately on your operational data.
For example, because the tool operates as a function in Oracle you can use it in SQL statements to improve your traditional analysis techniques or even embed it in your PL/SQL code and triggers to spot and prevent defective data entering your live systems.
It can be used on data migration and integration assignments to help compare disparate data formats and test whether data was correctly migrated or incorrectly formatted during the migration process.
Perhaps you already have a data analysis tool that lacks pattern analysis? Now you can create Oracle views on top of your business data to breathe new life into your existing tools that can now detect patterns.
Download the Pattern Analyser
To download the tool with a small tutorial just go to our download centre, check out the software section and you will see the Oracle version of the pattern analyser.
The zip file contains an Oracle function and some simple instructions, installation is simple but you may need an administrator to grant the priviliges.
Please note: You need to be a registered member of Data Quality Pro to access the download area but don't worry this is free and takes about 20 seconds, just click here.
All this week we will be releasing tutorials and guides for how to use these new tools in various data profiling and analysis software so keep coming back to find out more.
If you modify or improve any of our free tools please let us know so we can share the changes with the data quality community.
Our aim is to scour the community for other free and low cost products to help all members, regardless of budget, learn some of the core techniques of data quality analysis and improvement.
Tip: For a description of how pattern analysis works, take a look at our previous tutorial for our Pattern Analyser for Microsoft Acess (click here) or search in our DQ QuickLink tool for more examples from vendors and other practitioners.
Useful Links
- Data Quality Pro Microsoft Access Pattern Analyser
- Wikipedia - What is Data Profiling?
- Registration Page for Non-Members
- Data Quality Pro Member Download Centre
Reader Comments (3)
Dylan,
Your tool is an interesting starting point that I think will be persuasive for people new to the idea of automated data profiling. I know Oracle customers are quite interested in these capabilities.
I'd expect that in time it would lead people to investigate full-featured solutions such as Oracle Warehouse Builder's data quality, data rules and data cleansing and auditing.
Anyway, nice work. I'll be keeping an eye on your community and participating when I can.
-Antonio
Hello Dylan,
Thank you very much for this tool. I've started testing it in my organization.
I was just wondering if there's any reason why the space character (ascii_value 032) is mapped to "_", knowing that the "_" character is a wildcard in sql language. So if we want to extract values starting with a space in a field, and write "where dqpattern(field_name) like '_%'" in the select statement, we get everything.
We mapped the ascii_value 032 to another character in the function, and now we're able to extract values starting with a space.
Best regards,
Nicolas.
Hi Nicolas,
Ah, well spotted! We ported the mappings from another version which was non-Oracle, that's a very good point and thanks for bringing it to our attention, I'll get a patch out.
Thanks again - Dylan