1. Set Up Oracle Fusion Marketing
  2. Build Data Warehouse Transformation Logic

Build Data Warehouse Transformation Logic

After you select Create, the transformation design canvas will be displayed. This is where you define the logic and structure of your transformation using a visual canvas. You can click the plus (+) icon at the center of the canvas to begin adding transformation steps.

You use the visual canvas to:

  • Select the primary data object (which becomes the destination object)
  • Choose source attributes
  • Automatically generate table and attribute aliases
  • Add joined data objects
  • Configure join conditions (Inner, Left Outer, Right Outer)
  • Build expressions using an expression editor with syntax highlighting and smart prompts
  • Apply filters (WHERE clauses)
  • Map transformed attributes to destination fields
Follow these steps:
  1. Select a Data Object to define the primary source for the transformation.

    The first data object you select, such as Customer, becomes the destination object for the transformation.

    Screenshot of Workbench canvas showing Customer as the first data object selected

    This data object is available later in the flow as the destination.

  2. Select the attributes to use in the transformation logic.
    1. Add the required attribute rows.
    2. Use Add row to include additional attributes.
    3. Use Delete row to remove attributes.
    4. An alias is automatically generated based on the table name to simplify use in expressions and joins.
    5. Click Save to confirm the selection and return to the visual canvas.

    Here's a sample screenshot of the Address attribute panel.

    Screenshot of where to enter required attribute from the Workbench canvas.

  3. Add a secondary data object.
    1. Select Join from the next available step.
    2. Choose the object, such as Address.
    3. Select the required attributes.
    4. Use the expression builder as needed:
      1. Press # to view tables.
      2. Press $ to view columns.
      3. Press @ to view functions.
    5. Use syntax highlighting to help build the query.

    Here's a sample screenshot of the Join panel.

    Screenshot of where to enter required Join expression from the Workbench canvas.

  4. Apply a filter, if required.
    1. Select Filter.
    2. Define a WHERE clause using attribute values or logical expressions.
    3. Records that match the filter are excluded from the transformation logic and aren't written to the destination Data Warehouse table.
  5. Add the destination.
    1. Select the plus (+) icon.
    2. Select the primary data object, such as Customer.
    3. This defines where the transformed data is written.
  6. Map the transformed fields.
    1. Select Attributes.
    2. Map the transformed fields to the corresponding attributes in the destination data object.
    3. Verify that the output is written to the correct fields and aligned to the data model.
  7. Select Save after the transformation logic is complete.