Column Profiles in Informatica Developer
Use a column profile to analyze the characteristics of columns in a data source, such as value percentages and value patterns. You can add filters to determine the rows that the profile reads at run time. The profile does not process rows that do not meet the filter criteria.
You can discover the following types of information about the columns that you run a profile on:
- •The number of times a value appears in a column.
- •Frequency of occurrence of each value in a column, expressed as a percentage or number of rows.
- •Character patterns of the values in a column.
- •Statistics, such as the maximum and minimum lengths of the values in a column, and the first and last values.
- •Inferred data types, frequency, conformance criteria for data domain discovery, and data type inference status.
You can define a column profile for a data object in a mapping or mapplet or an object in the Model repository. The object in the repository can be in a single data object profile, multiple data object profile, or enterprise discovery profile.
You can choose sampling options, drill-down options, and run-time environment for a column profile. You can add rules and filters to a column profile.
Filtering Options
You can add advanced filters or SQL filters to determine the rows that a column profile uses when you run the profile. The profile does not process rows that do not meet the filter criteria.
Creating an Advanced Filter
You can create an advanced filter with expressions, such as AND, OR, and NOT to make a subset of the original data source.
1. Create or open a single data object profile.
2. Select the Filter view.
3. Click Add.
The Select Wizard dialog box appears.
4. In the Select Wizard dialog box, click Advanced filter.
The Filter dialog box appears.
5. Enter a name and an optional description for the advanced filter.
6. Select Set as Active to apply the filter to the profile. Click Next.
7. Select Filter Definition to define a filter.
8. You can create an advanced filter with the Functions panel or Columns panel.
- - In the Functions panel, select a function category, and click the right arrow (>>) button.
In the dialog box, specify the parameters and click OK. The function along with the columns and values appears in the Expression panel.
- - In the Columns panel, select a column, and click the right arrow (>>) button. The column appears in the Expression panel.
Add functions, expressions, and values to create an advanced filter.
9. To verify the advanced filter, click Validate.
10. After you create or edit the filter, select Data Preview to view the filtered data. You can set the Maximum rows to preview option.
11. Click Finish.
The New Profile wizard appears with the filter in the Filters view.
Creating an SQL Filter
You can create an SQL filter with SQL queries. You can create an SQL filter for relational data sources.
1. Create or open a single data object profile.
2. Select the Filter view.
3. Click Add.
The Select Wizard dialog box appears.
4. In the Select Wizard dialog box, click Sql filter.
The Filter dialog box appears.
5. Enter a name and an optional description for the advanced filter.
6. Select Set as Active to apply the filter to the profile. Click Next.
7. Select Filter Definition to define a filter.
8. Use the columns in the Columns panel to create an SQL filter.
9. To verify the filter, click Validate.
10. After you create or edit the filter, select Data Preview to view the filtered data. You can set the Maximum rows to preview option.
11. Click Finish.
The New Profile wizard appears with the filter in the Filters view.
Sampling Options
Configure the sampling options to determine the number of rows that the profile reads during a profiling operation.
The following table describes the sampling options:
Property | Description |
|---|
All Rows | Chooses all rows in the data object. |
First | The number of rows that you want to run the profile against. The Developer tool chooses the rows from the first rows in the source. |
Random Sample of | The number of rows for a random sample to run the profile against. |
Random Sample (Auto) | Random sample size based on the number of rows in the data object. |
Exclude approved data types and data domains from the data type and data domain inference in the subsequent profile runs | Excludes the approved data type or data domain from data type and data domain inference from the next profile run. |