DataFlux Data Management Studio 2.7: User Guide

Performing Primary Key and Foreign Key Analysis

Overview

You can use primary and foreign key analysis to uncover optimal key relationships to match primary and foreign keys when you create table joins.

Prepare and run the primary and foreign key analysis in the Properties tab for a profile report. Then, you can review the results in the Report tab. Perform the following tasks in an existing profile:

• Prepare a Primary Key and Foreign Key Analysis
• Review the Results of a Primary Key and Foreign Key Analysis

Prepare and Run a Primary Key and Foreign Key Analysis

You can prepare and run a primary and foreign key analysis in the Properties tab. Perform the following steps:

1. Open the table that contains the field that you want to designate as the primary key.
2. Right-click the primary key field. Then, click Primary Key/Foreign Key Relationships. For example, you could designate the Contact field in the Contacts table as your primary key.
3. Select the foreign field candidates that you want to evaluate from the tables listed in the Available field in the Primary Key/Foreign Key Analysis dialog.
4. Click Add for each field that you want to evaluate. The following display shows a completed dialog:

   

5. Click OK to save your settings.
6. Click Run to run the profile.

Note: You can also prepare and run a primary key/foreign key analysis in the Report tab, provided that the field that you select has the frequency distribution metric enabled. Right-click the field in the Standard Metrics tab and follow the procedure described in this section. Then, run the profile.

Review the Results of a Primary Key and Foreign Key Analysis

You can review the results of a primary and foreign key analysis in the Report tab of a profile report. Perform the following steps:

1. Open the table that contains your primary key field in the Tables tree.
2. Click the primary key field.
3. Click the Primary Key/Foreign Key Analysis tab.
4. Review the results of your analysis. In this case, the match percentage of the foreign key candidates ranges from 100% to 0%. You can also click a row in the analysis table to see the outliers for a selected field and the match percentage for each outlier. A sample Primary Key/Foreign Key Analysis tab is shown in the following display: