Skip to main content Skip to complementary content

Creating a table analysis with a simple SQL business rule

You can create analyses on either tables or views in a database using SQL business rules. The procedure for creating such analysis is the same for a table or a view.

Before you begin

  • At least one SQL business rule has been created in the Profiling perspective of Talend Studio.

  • At least one database connection is set in the Profiling perspective of Talend Studio.

About this task

In this example, you want to add the SQL business rule created in Creating an SQL business rule to a top_custom table that contains an age column. This SQL business rule will match the customer ages to define those who are older than 18.

Defining the table analysis

Procedure

  1. In the DQ Repository tree view, expand Data Profiling.
  2. In the filter field, start typing business rule analysis, select Business Rule Analysis and click Next.
  3. Enter a name.
  4. Optional: Enter column analysis metadata (Purpose, Description and Author).
  5. Click Next.

Selecting the table you want to analyze

Procedure

  1. Expand DB Connections, browse to the table to analyze and select it.
    Selection of the table to be analyzed.
  2. Click Finish to close the Create New Analysis wizard.
    Information noteNote: You can directly select the data quality rule you want to add to the current analysis by clicking the Next button in the New Analysis wizard or you can do that at later stage in the Analyzed Tables view as shown in the following steps.
    The analysis editor opens with the defined analysis metadata, and a folder for the newly created analysis is displayed under Analyses in the DQ Repository tree view.
    Overview of the Analyzed Tables section.
  3. If required:
    • Click Select Tables to open the Table Selection dialog box and select new tables to analyze.
      Selection of the new tables to be analyzed.

      You can filter the table list by typing the desired text in the Table filter field. The list will show only the tables that correspond to the text you type in.

    • Select another connection from the Connection list to connect to a different database. This list has all the connections created in Talend Studio. If the tables listed in the Analyzed Tables view 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.
  4. Right-click any of the listed tables in the Analyzed Tables view and select Show in DQ Repository view, the selected table is automatically located under the corresponding connection in the tree view.

Selecting the business rule

Procedure

  1. Click Add rule next to the table name where you want to add the SQL business rule.
    The Business Rule Selector dialog box is displayed.
  2. Expand the Rules folder and select the check boxes of the predefined SQL business rules you want to use on the corresponding tables.
  3. Click OK.
    The selected business rule is listed below the table name in the Analyzed Tables view.
    Contextual menu of a business rule in the Analyzed Tables section.
    You can also drag the business rule directly from the DQ Repository tree view to the table in the analysis editor.
  4. Save the analysis and press F6 to execute it.

    An information pop-up opens to confirm that the operation is in progress and the analysis editor switches to the Analysis Results view.

    Graphs showing the matching and non-matching values according to the business rule.
    All age records in the selected table are evaluated against the defined SQL business rule. The analysis results has two bar charts: the first is a row count indicator that shows the number of rows in the analyzed table, and the second is a match and non-match indicator that indicates in red the age records from the "analyzed result set" that do not match the criteria (age below 18).
  5. Right-click the business rule results in the second table, or right-click the result bar in the chart itself.
  6. Select an option:
    Option Description
    View valid rows Access a list in the SQL editor of all valid rows measured against the pattern used on the selected table.
    View invalid rows Access a list in the SQL editor of all invalid rows measured against the pattern used on the selected table.
    Generate Job Generate a Job that uses the Extract Load Transform process to write the valid or invalid rows of the analyzed table in an output file. This option is available for Microsoft SQL Server, MySQL, Oracle, and PostgreSQL connections.

    You can carry out a table analysis in a direct and more simplified way. For further information, see Creating a table analysis with an SQL business rule in a shortcut procedure.

  7. If required, right-click the business rule and select View executed query.
    The SQL editor opens in Talend Studio to display the query.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here