Join Analysis
Join analysis describes the degree of potential joins between two data columns. Use a join profile to analyze column joins in a data source or across multiple data sources.
A join profile displays results as a Venn diagram and as numerical and percentage values. You create and run a join profile from an enterprise discovery profile.
Creating a Join Profile
You can analyze potential joins between data objects in an enterprise discovery profile. The join profile stores the analysis in the Model repository.
1. Create or open an enterprise discovery profile.
2. Verify that the enterprise discovery profile contains the data objects that you need.
To add a data object to the join profile, drag it from the Object Explorer view to the modeling editor.
3. Select the data objects to profile.
4. Right-click the objects and select Join Profile.
The profile wizard opens.
5. Enter a name for the profile. Optionally, enter a text description of the profile.
6. Verify that the names of the data objects appear under Data Objects in the wizard.
7. Select or clear the option to Run profile on finish.
8. Click Next.
9. Select the data columns to include in the profile, and click Next.
If required, scroll down the data objects to view all available columns. The profile runs on all columns by default.
10. Click Add.
The Join Condition dialog box appears.
11. Click New to activate the column selection fields.
12. Select the data objects and columns to validate.
You define a join condition between two columns. You can define multiple join conditions across one or more data objects.
13. Click OK to create the join condition.
Optionally, click Add to define additional conditions.
14. Verify that the Left and Right join columns are prefixed with the correct data object names.
15. Click Finish.
Join Analysis Results
The join analysis Results tab provides information about the number and percentage of parent orphan rows, child orphan rows, and join rows. Join analysis results also include Venn diagrams that show the relationships between columns.
The following table describes the properties shown on the Results tab:
Property | Description |
|---|
Left Table | Name of the left table and columns used in the join analysis. |
Right Table | Name of the right table and columns used in the join analysis. |
Left Only Rows | Number of rows in the left table that cannot be joined. |
Right Only Rows | Number of rows in the right table that cannot be joined. |
Join Rows | Number of rows included in the join. |
Select a join condition to view a Venn diagram that shows the relationships between columns. The area below the Venn diagram also displays the number and percentage of orphaned, null, and joined values in columns.
Double-click a section in the Venn diagram to view the records that the section represents. These records open in the Data Viewer view.
Note: You can export the list of records from the Data Viewer view to a flat file.
Exporting Join Profile Results to File
You can export the data rows returned for a join condition to a delimited file. Export the overlapping rows between the left and right sources or the orphans rows in a source.
1. In the Object Explorer view, open the enterprise discovery profile that contains the join analysis.
2. Run the join profile.
3. Select the Join Results view.
4. On the Data Viewer tab, click the Export Drilldown Results to File icon.
The Export Data dialog box appears.
5. Enter a file name, and click Save.