Skip to main content

Defining reusable transformations

In a single Compose for Data Warehouses project there may be several processes that require similar data transformations. For example a reusable transformation can be defined that concatenates first and last names. This transformation could then be used both in the Customers mapping and in the Employees mapping.

As opposed to stored functions or procedures which are environment dependent, reusable transformations are environment agnostic, meaning that not only can they be used as required within a Compose project, but they can also be used across different environments (using Compose for Data Warehouses’s export/import function).

Centrally managed transformations increase efficiency by eliminating unnecessary duplication, while at the same time, enabling the seamless propagation of changes to all transformation instances.

  1. From the drop-down menu in the top right of the Model panel, select Reusable Transformations.

    The Reusable Transformations window opens.

    The window is split into the following panes:

    • Upper pane - Lists the reusable transformations that have been defined.
    • Lower pane - Provides additional information about transformation instances such as where they are in use (e.g. mappings, model, etc.) and the expression that was created using the transformation.

    Select a transformation to see the additional information.

  2. Click the New Transformation toolbar button.

    The New Transformation dialog box opens.

    1. In the Name field, specify a name for the transformation.
    2. In the Category field, specify a category name. If the category name already exists it will be displayed below the field when you start to type the name. To group the new transformation in the same category, simply select the existing name (unless of course you wish to create a new category with a similar name).

      In the Expression Builder, transformations are grouped according to their category name, making it easier to find the transformation you want to use. Therefore, when specifying a category name, it is recommended to choose a name that reflects the purpose of the transformation. For example, if you create several transformations that concatenate data, it would make sense to group those transformations under a category called "Join".

    3. To add a parameter to the transformation, click the New button to the right of the Parameters heading.

      A new row is added to the Parameters list.

    4. Specify a name for the parameter, select an appropriate data type, and optionally provide a description.

      Note:

      If you add multiple parameters, you can change a parameter’s position by selecting the parameter and then using the Up/Down arrows (above the Parameters list) to reposition it.

    5. Click the Create Expression button below the Parameters list.

      The Edit Transformation dialog box opens.

    6. In the Edit Transformation dialog box, create an expression using the parameters you defined earlier.

      For information on creating expressions, see Creating expressions.

    7. Click OK to save the transformation.

      The transformation is added to the list in the upper pane.

Once a transformation has been defined, it will be available for selection as needed in the Expression Builder’s Transformations tab.

For information on creating expressions, see Creating expressions.