Common

Profile Data Source

Common provides a quick and easy method to perform an assessment of data contained within a database. The following elements are available for all profiled databases:

  • A list of all tables in the database
  • A list of all columns within each table with defined Data Type, Length and Table Order
  • A frequency distribution of values for each column
  • A minimum and maximum value for each column
  • A number of blank, null or empty values for each column
  • Indication of unused tables and fields

The goal of data profiling is to save hours of research effort by listing all database information in one location. It is important to note that data profiling does not replace viewing and analyzing data, but is considered a value-added activity for reporting to the client and can provide useful information at the beginning of a Data Migration project.

This section includes the following topics:

Configure Profile Parameters

Parameters are Profile-specific settings that must be configured per installation. Parameters are delivered with recommended default values. Review these values and make any changes necessary for the installation.

To configure profile parameters:

  1. Select Common > Configuration > Modules > Parameters-Profile in the Navigation pane.

  2. Click Edit on Page toolbar.

    View the field descriptions for the Parameters-Profile page

  3. Update all fields if default values are not applicable.
  4. Click Save.

Activate and Deactivate Tables

When an Administrator registers a data source in System Administration, it is automatically registered in Common to be profiled. At this point, a snapshot of the database is taken and profiling begins.

Tables that are to be excluded from the profile process must be manually deactivated.

To deactivate/activate tables for profiling:

  1. Click Common >  Analyze in the Navigation pane.

    NOTE: The Analyze page displays Data Sources registered in System Administration that are not added to the Ignore Data Sources page and to which the logged in user has security to view. Data Sources are analyzed for database object changes, table statistics and duplicate records.

  2. Click Profile for Data Source ID.

  3. Click Tables.

    View the field descriptions for the Table (Results) page

  4. Click Active check box for a table to disable the check box, which excludes the table from being profiled. The Active All Tables icon is disabled if all tables are Active.
  5. Click Inactivate All Tables on Page toolbar to exclude all tables from being profiled and manually activate the select few tables to include in profiling. This icon is disabled if all tables are Disabled.

Profile Data Sources

Data can be profiled for an entire Data Source or for a single table within the Data Source.

There are many types of data that is collected when a Data Source is profiled:

  • Record counts for each table
  • Unique values for each field
  • Unique value count for each field
  • Blanks for each field
  • Fields used and not used
  • Minimum/maximum values for each field
  • Maximum field length
  • Field datatype
  • Field data type length

To profile a Data Source:

  1. Click Analyze in the Navigation pane.
  2. Click Profile for Data Source ID.

  3. Click Execute to profile the entire data source; a confirmation message displays.

    OR

    Click Profile TT Tables Only icon to profile just the tables within the data source; a confirmation message displays.

    NOTE: The Profile TT Tables Only icon is enabled if tt tables exist in the data source; it is disabled if no tt tables exist in the data source.

  4. Click Ok.

NOTE: Profiling a Data Source may take several hours, depending on the size of the data source. When the data source has been queued for profiling, a confirmation message displays.

If an error occurs or if the profiling process needs to be restarted, click Reset for the data source.

Profile an individual table if metrics for that single table (versus the entire data source) are needed.

To profile a single table:

  1. Click Analyze in Navigation pane.
  2. Click Profile for Data Source ID.
  3. Click Tables.
  4. Verify the ACTIVE check box is enabled for the table.

  5. Click Execute for a table; a confirmation message displays.

    NOTE: Two conditions must be met before the Execute icon is enabled: 1) the table must be active in order for the Execute icon to be available for the table and 2) the data source must be profiled (i.e., click the Execute icon on the Data Sources Tables page).

    NOTE: Depending on the size of the table, profiling a table may take several minutes to complete.

  6. Click Ok.

If an error occurs or if the profiling process needs to be restarted, click Reset for the table.

View Table Results

Results for profiled data sources can be viewed for individual tables.

  1. Click Analyze in the Navigation pane.
  2. Click Profile for Data Source ID.

  3. Click Tables.

    View the field descriptions for the Table (Results) page

  4. Click Vertical View to view profile and processing information for the table.

View Table Field Results

Results for profiled data sources can be viewed for individual fields within a table.

To view fields:

  1. Click Analyze in the Navigation pane.
  2. Click Profile for Data Source ID.
  3. Click Tables.
  4. Click Fields for Table.

Reset Profiling

If an error occurs during a profiling process, reset the process for the Data source at the Data source or table level.

Reset for a Data Source

To reset for a Data Source:

  1. Click Analyze in the Navigation pane.
  2. Click Profile for Data Source ID.
  3. Click Reset for the Data Source; a confirmation message displays.
  4. Click Ok.

Reset for a Table

To reset for a table:

  1. Click Analyze in Navigation pane.
  2. Click Profile for Data Source ID.
  3. Click Tables.
  4. Click Reset for a Table; a confirmation message displays.
  5. Click Ok.