Creating a numerical correlation analysis - 6.1

Talend Data Fabric Studio User Guide

EnrichVersion
6.1
EnrichProdName
Talend Data Fabric
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

In the example below, you want to create a numerical correlation analysis to compute the age average of the personnel of different enterprises located in different states. Three database columns are used for the analysis: STATE, AGE and COMPANY.

Note

The numerical correlation analysis is possible only on database columns for the time being. You can not use this analysis on file connections.

Prerequisite(s): At least one database connection is set in the Profiling perspective of the studio. For further information, see Connecting to a database.

Defining the analysis

  1. In the DQ Repository tree view, expand Data Profiling.

  2. Right-click the Analyses folder and select New Analysis.

    The [Create New Analysis] wizard opens.

  3. Start typing numerical correlation analysis in the filter field, select Numerical Correlation Analysis and then click Next.

  4. In the Name field, enter a name for the current analysis.

    Note

    Avoid using special characters in the item names including:

    "~", "!", "`", "#", "^", "&", "*", "\\", "/", "?", ":", ";", "\"", ".", "(", ")", "'", "¥", "'", """, "«", "»", "<", ">".

    These characters are all replaced with "_" in the file system and you may end up creating duplicate items.

  5. Set the analysis metadata (purpose, description and author name) in the corresponding fields and then click Finish.

    A folder for the newly created analysis is listed under Analysis in the DQ Repository tree view, and the analysis editor opens on the analysis metadata.

Selecting the columns you want to analyze and setting analysis parameters

  1. In the analysis editor and from the Connection list, select the database connection on which to run the analysis.

    The numerical correlation analysis is possible only on database columns for the time being. You can change your database connection by selecting another connection from the Connection list. If the analyzed columns do not exist in the new database connection you want to set, you receive a warning message that enables you to continue or cancel the operation.

  2. Click Select columns to analyze to open the [Column Selection] dialog box.

  3. Browse the catalogs/schemas in your database connection to the column(s) you want to analyze.

    You can filter the table or column lists by typing the desired text in the Table filter or Column filter fields respectively. The lists will show only the tables/columns that correspond to the text you type in.

  4. Click the table name to list all its columns in the right-hand panel of the [Column Selection] dialog box.

  5. In the column list, select the check boxes of the column(s) you want to analyze and click OK.

    In this example, you want to compute the age average of the personnel of different enterprises located in different states. Then the columns to be analyzed are AGE, COMPANY and STATE.

    The selected columns are displayed in the Analyzed Column view of the analysis editor.

    You can drag the columns to be analyzed directly from the corresponding database connection in the DQ Repository tree view into the Analyzed Columns area.

    If you right-click any of the listed columns in the Analyzed Columns view and select Show in DQ Repository view, the selected column will be automatically located under the corresponding connection in the tree view.

  6. In the Indicators view, click to open a dialog box where you can set thresholds for each indicator.

    The indicators representing the simple statistics are by-default attached to this type of analysis.

  7. In the Data Filter view, enter an SQL WHERE clause to filter the data on which to run the analysis, if required.

  8. In the Analysis Parameter view and in the Number of connections per analysis field, set the number of concurrent connections allowed per analysis to the selected database connection, if required.

    You can set this number according to the database available resources, that is the number of concurrent connections each database can support.

  9. If you have defined context variables in the Contexts view in the analysis editor:

    • use the Data Filter and Analysis Parameter views to set/select context variables to filter data and to decide the number of concurrent connections per analysis respectively.

    • In the Context Group Settings view, select from the list the context environment you want to use to run the analysis.

    For further information about contexts and variables, see Using context variables in analyses.

  10. Save the analysis and press F6 to execute it.

    The graphical result is displayed in the Graphics panel to the right of the editor.

    The data plotted in the bubble chart have different colors. The legend shows which color refers to which data.

From the generated graphic, you can:

  • place the pointer on any of the bubbles to see the correlated data values at that position,

  • right-click any of the bubbles and select:

    Option

    To...

    Show in full screen

    open the generated graphic in a full screen

    View rows

    access a list of all analyzed rows in the selected position

The below figure illustrates an example of the SQL editor listing the correlated data values at the selected position.

From the SQL editor, you can save the executed query and list it under the Libraries > Source Files folders in the DQ Repository tree view if you click the save icon on the editor toolbar. For more information, see Saving the queries executed on indicators.

For more information on the bubble chart, see Accessing the detailed view of the analysis results.