Building a query - 6.3

Talend Data Quality Portal User and Administrator Guide

EnrichVersion
6.3
EnrichProdName
Talend Big Data Platform
Talend Data Fabric
Talend Data Management Platform
Talend Data Services Platform
Talend MDM Platform
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
EnrichPlatform
Talend DQ Portal

This section guides you through the steps to create a report built upon a template generated by the QBE analytical tool.

In this example, we want to identify, in a selected report, the analyses where indicator thresholds have been violated.

Prerequisites

  • You have accessed Talend Data Quality Portal as a user.

  • Some reports have been generated in the Profiling perspective of Talend Studio.

To build a query using the QBE method, do the following:

Selecting the elements to include in the query

  1. From the web user interface, click the icon, point toCustom Reports and click TDQ Datamart to open the corresponding page.

    The table list in the Schema panel to the left of the editor differs according to the database used in the datamart.jar file. For further information about how to configure in the web server a datamart other than Talend datamart, see Connecting to another datamart from the QBE page.

    The Query Catalog panel to the right of the editor shows by default a parent query folder that represents the current query being created. From this panel, you can create in the parent query one or more sub-queries to form groups of queries in a hierarchical tree. For further information, see Creating and saving a group of queries.

  2. In the Schema panel and under tdqdatamart, expand the fact table holding the analysis results you want to build the query on, Indicator Value in this example.

  3. From the tables in the Schema panel, drag the entities you want to include in the query to the Query Editor, or simply click them.

    Consider as an example that you want to display the following items in the results of the query: analysis name (Analysis label), indicator name (Label), connection name (Connection Name), table name (Table Name), column name (Column Name), analysis runtime (Analysis Runtime) and analysis execution hour (Lable).

    You want to include as well the following entities that will filter the results: Data Threshold Ok, Indicator Threshold Ok and Indicator Percentage Threshold Ok.

    All entities in the Schema panel under Datamart that have the OK suffix help you to filter the query results according to the parameters you set in the Filters view of the Query Editor.

  4. If required, click in the Alias column in the Query Editor and change all the names of the entities to give a more friendly names to the columns in the result table.

    You can click the filter icon next to the entity name to add the entity directly to the filter table.

  5. If required, clear the check boxes in the Visible column next to the entities you do not want to list in the query results table.

Setting a WHERE clause in the query

  1. In the Query Editor, click Filters to open a new view.

  2. Either click the New button to add as many filters as needed, or drag the entity from the tables in theSchema panel.

    If you click the filter icon next to the entity name in the Select Fields table, the filter will appear directly in the Filters table.

    In this example, you want to filter:

    • the analysis name to query a specific analysis, ColumnAnalysis_WithThresholds,

    • the indicators to collect only the statistics on Blank Count, Duplicate Count, Null Count and Distinct Count,

    • the data, indicator and indicator percentage thresholds to show only the records that violates the defined thresholds.

  3. Click Exp Wizard on top of the table to display the [Expression Editor] dialog box. Here you can modify the expression conditions.

  4. Close the [Expression Editor] dialog box.

  5. If required, use the Query Catalog panel to the right of the editor to create different levels of queries under the main query and save them as a group.

    For further information, see Creating and saving a group of queries.

Saving and executing the query

  1. Open the Shortcuts list at the top right corner of the page and select Add to customized views.

    A dialog box is displayed.

  2. Enter a name and a description for the query and then select Public or Private from the Scope list.

  3. Click Save to close the dialog box.

    The new query is listed in the Customized views.

    For further information on how to access the Customized views, see Accessing the list of the defined queries.

  4. Click Preview on top right corner of the page to see a view of the analyzed data.

  5. Click Preview in the top right corner of the Query Editor.

    A loading indicator is displayed and then a view opens displaying the results of the query.

  6. Click Worsksheet on the top left corner of the page to open a view where you can add one or several worksheets to this interrogation.

    In these worksheets, you can use different types of charts or tables to represent the query results. For further information on worksheets, see Creating a worksheet for the QBE results, and the SpagoBI quick start pdf at http://forge.objectweb.org/project/showfiles.php?group_id=204.