Business Intelligence Cookbook: A Project Lifecycle Approach Using Oracle Technology

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. Connect to the source system using Oracle SQL Developer as the schema owner of the objects you are profiling.
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. Check the lengths of character datatypes.

   Sample SQL statement:

   select distinct data_type, data_length from ...