Adding Virtual Columns (DEFINE) in a Synonym

You can create transformations in a synonym by adding a virtual column, also known as a DEFINE. This creates a transformation that appears with the data source list of columns.

A virtual column in the synonym is useful in the following situations:

  • When you want to create static transformations that are constant through an organization. For example, you might want to create a column of phone numbers with the dashes removed or a list of Department codes decoded (such as, 1 for Accounting, 2 for Marketing).
  • When you want all data flows to use a set of common business rules. For example, you might want to set a definition like PROFIT = REVENUE - EXPENSES.
  • When you want to simplify data flow maintenance. If the PROFIT expression noted above ever changes, it only needs to be edited at the source Server.

A virtual column in the synonym can also be used in the following situations, as an alternative to using a source transformation:

  • When you want to filter information based on the results of a transformation. For example, you might want to filter based on the size of the PROFIT.
  • When you want to group by the results of a calculation. For example, the table has an order date column, and you want to group on the year extracted from that column.
  • When you want to create transformations based on user routines that are located on the source machine. Specialized in-house routines, tested over time, may be required for your warehousing application, or the WebFOCUS Reporting Server and your data mart may be located on a UNIX platform. Business rules may dictate that a z/OS COBOL program be ran for each retrieved row. You can exploit this COBOL program, using a virtual column.

It is often useful to prototype the transformations with a limited number of rows, and then place them in the source definition after analyzing your performance.

Note: If you have a data flow that uses the synonym in which you are creating a virtual column, you should right-click the synonym's application directory and select Refresh to use the new column.

A virtual column can contain an expression, a constant, or a column name.

  • If the virtual column is a complex expression, you can create the expression with the Transformation Calculator or type it into the Expression field.
  • If the virtual column is a simple expression, such as a constant value, you can type the value in the Expression field.

After creating a virtual column, you can test it by right-clicking the synonym and clicking Sample Data. Sample data appears in a separate dialog.

Create a Virtual Column in a Synonym

To create a virtual column in a synonym:
    Procedure
  1. From an application directory in the navigation pane, double-click a synonym, or right-click it and click Open.

    The Synonym Editor opens.

  2. Right-click a segment (or column), point to Insert, and then click Define.

    The Define Calculator opens.

  3. From the Define Calculator, type a name for the column in the Name input field, or use the default define name.
  4. You may enter a descriptive title for the virtual column (DEFINE) in the Title input field.
    Tip: From the Synonym Editor, click the browse (...) button at the right of the TITLE and DESCRIPTION value fields to specify multiple language titles.
  5. Use the Expression tab and the calculator buttons to build the expression for the virtual column (DEFINE).

    Or

    Use the Relational Expression tab to build the expression.

    1. Click the Add New Row icon. From the Relational Expression tab, use the dropdown lists to select the filter Field (column), Relation, and Type.
      Note: Parameters are not supported.
    2. Click the browse (...) button at the right of the Value input field.

      The Value Selection dialog opens.

    3. Select from the available values and use the arrows to add or remove values.
    4. Click OK to close the Value Selection dialog and return to the Relational Expression tab.

      The expression is added to the value field.

    5. To add another filter, double-click a column or variable from the Columns/Variables tab on the right side of the Virtual Column Calculator.

      The filter is added to the Relational Expression tab, where you can add the expression value.

    6. To delete an expression, click in the row number column of the expression that you are deleting, right-click, and select Delete selected row(s).

      The expression is removed from the Relational Expression tab.

  6. You may select the Check Expression and Sample Data buttons, located on the top right of the Virtual Column Calculator, to verify that the expression is valid and to view sample data for the filter.
  7. Click OK to close the Define Calculator and return to the Synonym Editor.
    Note: To edit the Define, Title, or Expression, you may do so directly from the Synonym Editor, or you may click the browse (...) button at the right of the EXPRESSION value field to reopen the Define Calculator.
  8. If no columns from the synonym are used in the expression or have been defined, you can use the WITH option to identify the logical home of the defined calculation. You can also use the WITH option to move the logical home for the virtual column to a lower segment than it would otherwise be assigned (for example, to count instances in a lower segment).
    Tip: You can click and drag the DEFINE field and move it to a different segment in the Modeling View tab, which also changes the segment association.
  9. Specify the Missing Data options for columns that allow null data. You can allow all missing data.
  10. Click Save from the Quick Access Toolbar to save the synonym.
  11. To close the Synonym Editor, click the control button in the upper-right corner.