Building data lengths data profiling scripts

Understanding the scale, precision, and length of common columns is important to be able to construct your data model.

Getting ready

Gather the source user name and passwords for the source system.

How to do it...

Understanding the scale and precision will allow you to appropriately plan for the correct data structures within the data warehouse:

1. 1. Connect to the source system using Oracle SQL Developer as the schema owner of the objects you are profiling.

2. 2. Determine the datatypes you may be working with.

   Sample SQL statement:

   select distinct data_type
   from user_tab_columns
   where data_type not like '%$%';
   

3. 3. Check the lengths of character datatypes.

   Sample SQL statement:

   select distinct data_type, data_length from user_tab_columns where data_type like '%CHAR%' and data_length > 1 order by data_length;
   

4. 4. Check the scale and precision of numeric datatypes.

   Sample SQL statement:

   select distinct data_type, nvl(to_char(data_precision),'Default'...