Transforming Data

Data Integration includes an interactive Data Xplorer that displays a sampling of data and updates the sampling as you apply transformation operations. Data Xplorer helps you to validate the impact of transformations, debug, and troubleshoot possible failures before a task is run.

You can access the Data Xplorer from the Data tab of an operator when working in the Data Integration designer, or when you create a data loader task.

The Data Xplorer also includes a profile panel where you can view these profiles:

  • Data Profile: Displays a summary of your data, such as the total number of rows and attributes.
  • Attribute Profile: Displays a summary of a selected attribute, such as the data type, minimum value, and maximum value. For a String data type, the maximum and minimum values are determined by lexicographic ordering.

Data Xplorer in Data Flow

When working in the Data Integration designer, you can access a condensed version of the Data Xplorer in the Data tab of the Properties panel for an operator.

Note

Ensure that you have all data entities properly configured to access the Data Xplorer.

When you apply a transformation on a data attribute, an expression operator is added to the canvas, and the sampling of data is updated to reflect the transformation.

Data Xplorer in Data Loader

When creating a data loader task, after selecting the source, you can click Configure in the Configure Transformations section to access the Data Xplorer.

View the data entity attributes in the Attributes tab, and the sampling of data in the Data tab. Transformations that you apply to the attributes appear in the Transformations list, and the Data tab is updated to reflect the transformations.

Applying Transformations

You can apply transformations to each of the attributes in the Data Xplorer, or you can filter the attributes by a name pattern or data type and then apply a bulk transformation to a group of attributes.

An Expression operator is added to the data flow design for every transformation you apply in the Data tab of the Properties panel. To undo or remove a transformation, you can delete the Expression operator that represents that transformation on the canvas.

When you configure transformations in a data loader task, the Transformations panel records all transformations applied to the data or attributes during the current session. You can remove transformations, as needed. Be sure to save your changes.

You can apply the following transformations to attributes.

Change Case Transformation

This transformation lets you change the text case in an attribute, and add an attribute to hold the transformed data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Change Case.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Change Case.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. Select the Type of case transformation to perform. You can choose from:
    • Lower: Changes the text string to all lowercase letters
    • Proper: Changes the text string to begin with an uppercase letter, followed by lowercase letters
    • Upper: Changes the text string to all uppercase letters
  3. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  4. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New Attribute Name Pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  5. Select the Data Type of the transformed attribute.
  6. If applicable, enter the Length of the transformed attribute.
  7. Click Apply.
Change Data Type Transformation

This transformation lets you change the data type of an attribute, and add an attribute to hold the transformed data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type, and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Change Data Type.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Change Data Type.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. Select the Data Type to transform the attribute or group of attributes to.
  3. Depending on the new data type you selected, you might have to specify other values such as a Length or Scale.
  4. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  5. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New Attribute Name Pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  6. Click Apply.
Create Unique ID Transformation

Create Unique ID transformation lets you add an attribute to the data set. The values for the attribute are filled with a 128-bit universally unique identifier (UUID).

  1. On the Data tab, select Create Unique ID from the Actions menu.
    Important

    You can only apply this transformation to the entire data set. If you filter the attributes, the option is disabled.
  2. In the Create Unique ID dialog, select either String or Number as the data type for the new attribute.
  3. In New Attribute Name, enter a name for the new attribute.
  4. Click Apply.
Exclude Transformation

This transformation excludes the entire attribute and the data within from the data entity.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Exclude.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Exclude.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. Click Apply if you confirm that you want to exclude the selected attribute or the group of filtered attributes.
Extract Transformation

This transformation lets you search the data set for specific data values to extract into a new attribute.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Extract.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Extract.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. For Pattern or Extract Pattern, enter a text string or a regular expression following the Oracle Database format to extract from the data entity.
    Note

    Learn more about the Oracle Database regular expression format.
  3. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter the name of the new attribute the extracted values are stored in, or leave the name as-is.
    • For New Attribute Name Pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  4. Select the Data Type of the transformed attribute.
  5. If applicable, enter the Length of the transformed attribute.
  6. Click Apply.
Format Transformation

The format transformation lets you to apply a specific formatting rule like a date or number format to your data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Format.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Format.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. In the Formatting dialog or panel, your options depend on the attribute data type:
    • For String, select the Trimming method (Trim Left, Trim Right, or Trim Both).
    • For Number, enter a value in Decimal Places (optional).
    • For Date, select the date format, and select or deselect the Display Time check box as required.
  3. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  4. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new name for the attribute or leave the name as-is.
    • For New Attribute Name Pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  5. Select the Data Type of the transformed attribute.
  6. If applicable, enter the Length of the transformed attribute.
  7. Click Apply.
Hash Transformation

The hash transformation lets you to encrypt data, and generate encrypted values for attributes in the Data Xplorer.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Hash.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Hash.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. In the Hash dialog or panel:
    • Select the hash type (MD5, SHA1, or SHA2).
    • If applicable, select the number of bits.
  3. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  4. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new name for the attribute or leave the name as-is.
    • For New Attribute Name Pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  5. Select the Data Type of the transformed attribute.
  6. If applicable, enter the Length of the transformed attribute.
  7. Click Apply.
Merge Attributes Transformation

This transformation lets you merge two or more attributes into a new attribute.

  1. From the Transformations menu on the header of the attribute you want to transform, select Merge Attributes.
  2. For Attributes, select at least one other attribute to merge with.
  3. For Delimiter, select the style of delimiter to separate the values. You can choose from:
    • Comma
    • Space
    • Tab
    • Semicolon
    • Colon
    • Pipe
  4. (Optional) For Prefix, enter a value to add to the front of the data values.
  5. (Optional) For Suffix, enter a value to add to the end of the data values.
  6. For Name, enter the name of the new attribute.
  7. Select the Data Type of the transformed attribute.
  8. If applicable, enter the Length of the transformed attribute.
  9. Click Apply.
Null Fill Up Transformation

This transformation lets you populate null data values in an attribute with a text string, and add an attribute to hold the transformed data values.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Null Fill Up.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Null Fill Up.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. For Replace by, enter a text string or pattern to replace the attribute's null values with.
  3. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  4. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New Attribute Name Pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  5. Select the Data Type of the transformed attribute.
  6. If applicable, enter the Length of the transformed attribute.
  7. Click Apply.
Rename Transformation

This transformation renames an attribute.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Rename.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Rename.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new name for the attribute.
    • For New Name Pattern, by default $0 is entered, which represents the entire matched string. You can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  3. Click Apply.
Replace Transformation

This transformation lets you replace data values in an attribute, and add an attribute to hold the transformed data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Replace.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Replace.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. For Search String, enter the text string to look for in the selected attribute.
  3. For Replace String, enter the text string to replace the search string with.
  4. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  5. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New Attribute Name Pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  6. Select the Data Type of the transformed attribute.
  7. If applicable, enter the Length of the transformed attribute.
  8. Click Apply.
Replace by Pattern Transformation

This transformation lets you enter a regular expression to search and replace data values in an attribute, and add an attribute to hold the transformed data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Replace by Pattern.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Replace by Pattern.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. Select one of the following:
    • Custom: Enter your own regular expression.
    • Template: Use one of the predefined expressions. You can choose from the following regular expression templates:
      • Credit Card (XXXX-XXXX-XXXX-XXXX)
      • Credit Card (XXXX-XXXX-XXXX-1234)
      • Social Security Number (XXX-XX-XXXX)
      • Social Security Number (XXX-XX-1234)
      • Phone Number (XXX-XXX-XXXX)
      • Phone Number (XXX-XXX-1234)
      • Email Address (XX...XX@XX.XX.XXX)
      • Email Address (XX...XX@oracle.com)
  3. If you selected a template in the previous step, the Search Regular Expression and Replace Regular Expression fields are automatically populated for you.
  4. For Search Regular Expression, enter or edit the regular expression that matches the pattern you want to look for in the selected attribute.
  5. For Replace Regular Expression, enter or edit the regular expression to replace the search strings with.
  6. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  7. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New Attribute Name Pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  8. Select the Data Type of the transformed attribute.
  9. If applicable, enter the Length of the transformed attribute.
  10. Click Apply.
Sort Transformation

The sort transformation lets you sort data in ascending or descending order.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Sort.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Sort.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. Select the Sort Order, and then click Apply.