Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Profiling and Cleansing Data Using OWB "Paris" - Part I
December 29, 2005
Mark Rittman

I was working today with the latest beta release of OWB "Paris" and looking in particular at the new data profiling and cleansing features. Data Profiling is one of the major new features in Paris, and what particularly caught my eye was the ability of the tool to automatically build correction mappings for data that needs cleaning up. I think I've pretty much worked out how the feature works, and therefore it's probably worth making a note here how the process flows together with some initial observations (based on the beta release, of course the final product might be a bit different).

The scenario goes like this: You have a schema containing source data for the data warehouse, and you suspect that the data might need cleansing before it gets loaded into the warehouse for analysis. In the past you'd run a series of hand-crafted scripts to profile the data (look for the range of distinct values, report on the metadata, calculate aggregate values and so forth) but with Paris you can automate this process using the Data Profiling feature.

When you create a data profile, you point the profiler within Paris to the schema you wish to profile, select the objects to profile and the types of profile you want to carry out (attribute analysis, referential analysis and/or custom analysis). Paris then creates a schema (OWB_PRF) to hold the results of the profiling, and kicks of an asynchronous job to go and get the profile results. When it's finished, you can then access the Data Profile Editor and view the results, like this:

In the example I put together today, the COUNTRY_DSC field in the SRC_ACCOUNTS table contains data that I think I might need to clean up. The first step with this is to switch to the Domain tab for the Profile Results Canvas, which tells me that it has detected a domain for COUNTRY_DSC that contains Germany, Australia, Japan, France, United States, England, USA and Italy. The data within the column is 90.2% compliant with the domain, meaning that just under 10% of the rows have values outside the domain. Looking down at the Data Drill Panel, the values that contain green ticks are the ones that it considers part of the domain (basically any value that occurs more than once) and the ones with red crosses are considered outside the domain.

With our data, the correction that I'm looking to make is to change all the occurrences of England, Scotland, Wales and Northern Ireland to United Kingdom. What I want to do therefore is to take the suggested domain that the Data Profiler has come up with, use this as the basis for a data rule (excluding England, Scotland Wales and N.I. and including United Kingdom and the other countries with just one occurrence) and then take this data rule and use it to build a correction.

The first step therefore is to make sure the COUNTRY_DSC field is highlighted in the Profile Results Canvas, then press the Derive Data Rule button to put together a candidate data rule based on the domain that the Profiler has detected. You can then deselect those values that you want to be outside of the domain, select those you want to include in it, and add any other values (in my case, "Not Known") that you wish to manually add.

This Data Rule then becomes an object in the Project tree that you can then apply to multiple columns within tables. For our purposes though, we need to apply the data rule to the COUNTRY_DSC column of the SRC_ACCOUNTS table.

The next step is to go and create the correction. To do this, select Create Correction from the Profile menu, and select another module which will contain the corrected tables, and the mappings used to create the corrections. This module should point through to a schema that was previously registered as a Target Schema, so that OWB can create objects within it. In my case, the module was called GLOBAL_STG. You then need to select the objects that will be corrected - in our case, SRC_ACCOUNTS. Note that whilst you can select external tables, views or materialized views for correction as well as tables, the corrected version will always be a table.

Update : I've just noticed that the screenshot above shows the wrong table being selected. I actually selected the SRC_ACCOUNTS table, the screenshot is wrong. I'll update it tomorrow :-)


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational