DataFlux Data Management Studio 2.7: User Guide

Performance Tips for Profiles

You can improve the performance of your profiles by following these tips:

• Do a sample profile first to make a determination of what metrics to use for what attributes.
• The majority of repository storage space is used in storing the frequency distribution results.
• Be judicious in which columns need frequency distributions.
• Frequency distribution of unique attributes will grow your repository quickly.
• Pattern frequency distribution will grow your repository VERY quickly if there is a large variability of patterns.
• Avoid frequency distribution on primary key. These measurements will consume more disk space, and they will typically will not be very helpful due to their high variability.
• Avoid both types of frequency distributions if they yield results in excess of 40% of the data.
• Primary/foreign key analysis requires some of the primary keys to have frequency distribution performed.
• Redundancy analysis requires some of the primary keys to have frequency distribution performed.
• Allocate 10-15% of the total repository space needed, Do not allocate repository space up front.
• Archive profile reports after they are no longer needed.
• For large tables, use sampling (such as profile every 5th row ).
• Estimate the space required for a single profile report. For most tables, we can assume the breakdown of “uniqueness” of values is as follows:
• N = total number of columns
• U = number of columns that contain 100% unique data
• 50% of N columns contain 40%-70% (55% avg.) unique data
• 25% of N columns contain 10% or less unique data
• 25% of N columns contain 90% or greater unique data
• With those assumptions we can work out a calculation that the estimated space will be 52.5 + U/N = X, where X is the percent of the size of the source table.
• Enter a higher commit interval value in the Profile Options dialog. To enter this value, open the properties dialog for the affected profile. Then select Tools > Profile Options. On the General tab, select the "Commit every N rows" checkbox. Profiles that contain data from Teradata databases are particularly sensitive to the commit interval value.