Skip to main content Skip to complementary content

Creating SQL user-defined indicators

You can create your own personalized indicators from the Profiling perspective.

Information noteRemember: Management processes for user-defined indicators are the same as those for system indicators.

Defining the indicator

Procedure

  1. In the DQ Repository tree view, expand Libraries > Indicators.
  2. Right-click User Defined Indicators.
    Contextual menu of the User Defined Indicators node.
  3. Select New Indicator from the contextual menu.

    The New Indicator wizard is displayed.

  4. In the Name field, enter a name for the indicator you want to create.
    Information noteImportant:

    Do not use the following special characters in the item names: ~ ! ` # ^ * & \\ / ? : ; \ , . ( ) ¥ ' " « » < >

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

  5. Optional: Set other metadata (Purpose, Description and Author) in the corresponding fields and click Finish.

Results

The indicator editor opens displaying the metadata of the user-defined indicator.
Overview of the Indicator Metadata and Indicator Definition sections.

Setting the indicator definition and category

Procedure

  1. Click Indicator Category and select from the list a category for the indicator.
    The selected category determines the columns expected in the result set of the analysis that uses the user-defined indicator.
    The table below explains available categories.
    Indicator category Description Expected query results
    User Defined Match Evaluates the number of data matching a condition. The result set should have one row and two columns. The first column contains the number of values that match and the second column the total count.
    User Defined Frequency Evaluates the frequency of records using user-defined indicators for each distinct record. The result set should have 0 or more rows and two columns. The first column contains a value and the second the frequency (count) of this value.
    User Defined Real Value Evaluates real function of data. The result set should have one row and one column that contain a real value.
    User Defined Count (by-default category) Analyzes the quantity of records and returns a row count. The result set should have one row and one column that contain the row count.
  2. Click Indicator Definition and then click the [+] button.
  3. From the Database list, select a database on which to use the indicator.
    If the indicator is simple enough to be used in all databases, select Default in the database list.
  4. Enter the database version in the Version field.
  5. Define the SQL statement for the indicator you want to create:
    1. Click the Edit... button next to the SQL Template field.

      The Edit Expression dialog box opens.

      Overview of the Edit Expression dialog box.
    2. In the Indicator Definition view, enter the SQL expressions you want to use in matching and analyzing data. You can drop templates from the templates list to complete the expression.

      Example

      Set the expression to measure the maximal length of the values in a column as shown in the above capture.

      This view may have several input fields, one for each column expected by indicator category. For example, if you select the User Defined Count category, you will have only a Where Expression field; while if you select the User Defined Match category, you will have two fields: Matching Expression and Where Expression.

      The SQL expressions are automatically transformed into a complete SQL template in the Full SQL Template view.

      Overview of the Full SQL Template tab.

      Also, the SQL expressions are automatically transformed into templates to view rows/values. Different tabs are available in the dialog box depending on what indicator category is selected.

      If you edit the SQL expression(s) in the Indicator Definition view, the templates will be updated accordingly in the other tabs.

    3. Use the Reset button to revert all templates according to the content of the Indicator Definition tab.
    4. Click OK.
      The dialog box is closed and the SQL template is displayed in the indicator editor.
    5. Use the [+] button and follow the same steps to add as many indicator definitions as needed.
      Information noteNote: You do not need to define any parameters in the Indicator Parameters view when the user-defined indicator contains only SQL templates. These parameters are used only when indicators have Java implementation. For further information, see Creating Java user-defined indicators.
  6. Click the save icon on top of the editor.

Results

The indicator is listed under the User Defined Indicators folder in the DQ Repository tree view. You can use this indicator to analyzed columns through a simple drag-and-drop operation from the DQ Repository tree view to the columns listed in the editor.

If an analysis with a user-defined indicator runs successfully at least one time and later the indicator definition template for the database is deleted, the analysis does not fail. It keeps running successfully because it uses the previously generated SQL 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 – please let us know!

Leave your feedback here