Skip Headers

Agile Product Lifecycle Management Import/Export Guide Release 9.3.3 E39290-01 |
| Contents

Previous | Next |

# 9 Creating Transformation Definition Files

This chapter provides referential and procedural information to mapping files, key and required fields, change order selection and mapping, reusing and editing mapping files, and mapping PDX and aXML packages.

## 9.1 About Transformation Definition Files

Source data that originates from a supplier or partner can contain incompatible data. That is, the data may be invalid or inconsistent with your company's Agile PLM system. Before you import data, you may need to transform the values in some fields to make them compatible with the target system.

Transformation definition files are especially helpful for importing data from PDX or aXML packages. Generally, PDX or aXML packages are read-only. You cannot change the values contained in an archived file. If there are data inconsistencies in a PDX or aXML package, you must try to correct them using a transformation definition file.

A transformation definition file is a comma-delimited text file. Optionally, you can qualify text strings in the file using double-quotes ("). The file must contain a set of required fields needed to transform import data.

Transformation definition files are optional for the Import Wizard. If your source data does not need to be modified for your target system, you can select No transformations are necessary on the Select Content Options page in the Import Wizard.

Every transformation that occurs during an import session is noted in the import log file.

The Import Wizard does not support transformation definition files created with previous Agile Product Cost Management or Agile Product Collaboration releases.

## 9.2 Required Columns and Column Headings

If you are importing AML data from a PDX package, and you are using a transformation definition file, you should include the same transformations for fields in related objects (Manufacturer Part Number, Manufacturer Name) so that the corresponding attributes for AML rows, manufacturers, and manufacturer parts will have the same values.

Column heading | Description
--- | ---
ObjectTypeName | The class or subclass name on the target system, for example, Parts Class.
PropertyGroupName | The name of the group (or tab) containing the property, for example, Title Block.
PropertyName | The name of a target field.
Operation | The transformation operation. Valid operations are "REPLACE," "PREFIX," or "SUFFIX."
SourcePattern | A string to match the entire source value for the specified field. You can use regular expressions to match patterns of text. The pattern you specify is case-sensitive.
ParameterValue | The replacement, prefix, or suffix text used to transform the SourcePattern string.

Note: You can add an optional Comments column to document each row of the file.

Optionally, you can include the following three columns: ObjectTypeId, PropertyGroupId, and PropertyId. These columns appear automatically in a transformation template that you can generate from the Import Wizard. The columns are not required, but they map the ObjectTypeName, PropertyGroupName, and PropertyName columns to unique internal Agile ID numbers. The ID columns ensure the validity of the transformation definition file in the event that an Agile field name-such as the name of a custom field-changes.

Caution: Do not change the column names as they appear in column headings. They must match the specified names exactly. Otherwise, the transformation definition file is invalid.

You can change the order of columns as long as you also change the respective column header. For example, you can make column A the PropertyName column.

The header row (that is, the row with column names) must be the first row in the file. There should be no blank columns to the left of the first data column. For example, in an Excel file, the table should start at column A.

You can perform only one transformation per target value. This means that you cannot, for example, use a PREFIX and SUFFIX transformation on the same value. Nor can you chain transformations together in sequence to modify a value iteratively.

Transformations occur in sequential order from top to bottom in the transformation definition file. Only the first transformation for a target field's value is used; other transformations for the same value are ignored.

## 9.3 Using PREFIX, SUFFIX, or REPLACE Operations

Transformation definition files allow you to add a prefix or suffix to values, or replace values.

A PREFIX operation simply adds a specified string to the beginning of a value.

Operation | SourcePattern | ParameterValue | Result
--- | --- | --- | ---
PREFIX | 12345 | P- | 12345 -> P-12345
PREFIX | 1 | P- | 1 -> P-1

A SUFFIX operation adds a specified string to the end of a value.

Operation | SourcePattern | ParameterValue | Result
--- | --- | --- | ---
SUFFIX | 12345 | -S | 12345 -> 12345-S
SUFFIX | 1 | -S | 1 -> 1-S

A REPLACE operation replaces the entire value. You cannot replace a portion of a value. You also cannot replace a value with an empty string.

Operation | SourcePattern | ParameterValue | Result
--- | --- | --- | ---
REPLACE | 12345 | 67890 | 12345 -> 67890
REPLACE | 1 | Agile | 1 -> Agile

## 9.4 Using Regular Expressions in SourcePattern Field

When you specify values in the SourcePattern field of the transformation definition file, you can use regular expressions to match patterns of text. A regular expression can contain symbols or syntactic elements used to represent one or more characters.

The simplest regular expression is a literal text string, such as IPN100012. Any alphanumeric character in the SourcePattern field matches the same character in the source data.

You can also use special symbols to match text patterns in a generalized way, much like wildcards. The following table lists the regular expression symbols you can use in the SourcePattern field.

Symbol | Description
--- | ---
Period (.) | Matches any single character.
Asterisk (*) | The preceding character appears zero or more times.
Plus (+) | The preceding character appears one or more times.
Question mark (?) | The preceding character appears zero or one time.

Note: The source pattern you specify must match the entire incoming value. Otherwise, the value won't be transformed.

Although regular expressions enhance the types of transformation you can specify, they are optional. If you don't find them helpful, you don't have to use them. The following table shows several examples of regular expressions.

SourcePattern | Matching values
--- | ---
.* | Any value, including an empty string.
.+ | Any value except an empty string.
A.* | Values that start with A.
AGILE.* | Values that start with AGILE.
.*AGILE | Values that end with AGILE.
.*AGILE.* | Values that contain AGILE.
P0012+ | P0012, P00122, P001222, P0012222, and so on. However, it does not match P001.
P0012* | P001, P0012, P00122, P001222, P0012222, and so on.
P0012? | P001 or P0012.
P00*20 | P020, P0020, P00020, P000020, P0000020, and so on.
P00.+20 | P00020, P00120, P00220, P00320, P00123456789020. However, it does not match P020.

## 9.5 Generating a Transformation Template

The Import Wizard lets you generate a transformation template, a comma-delimited text file that has the appropriate fields for the target Agile PLM system.

To create a transformation template:

1. From the Content Options page of the Import Wizard, select Use a saved Transformation file.

2. Click Download Transformation Template. The File Download dialog box appears.

3. Save the file to your local computer.

4. In Download Transformation Template window's Popup, click OK.

The default transformation template filename is TransformTemplate.csv. It is a comma-separated file that can be edited in a text editor or a spreadsheet program.

Important Do not change the default values in the ObjectTypeName, PropertyGroupName, and PropertyName columns. Otherwise, the transformation will fail. To avoid changing these values, Oracle recommends that you download the template and avoid creating new ones that require typing in the values.

After you create a transformation template, fill in the following columns:

- Operation

- SourcePattern

- ParameterValue

The Import Wizard skips lines in the transformation definition file that don't have an operation defined. If you specify an operation in a row, you must also specify a source pattern and parameter value in that row.

The Comments column is optional and is used to document each transformation. The three rightmost columns, ObjectTypeId, PropertyGroupId, and PropertyId, should not be edited. They show the internal Agile PLM ID numbers for each field.

## 9.6 Reusing Transformation Definition Files

You can maintain a transformation definition file that correctly transforms data for all types of import data. You may also find it helpful to create a transformation definition file for each manufacturer, as your manufacturers may use different conventions for entering product data.

Note Make sure you give recognizable names to transformation definition files so that they can easily be associated with a particular manufacturer or an Agile PLM system. Otherwise, you may use the wrong transformation definition file and modify the import data the wrong way.

To use an existing transformation definition file:

1. From the Content Options page of the Import Wizard, select Use a saved Transformation file.

2. Type the path of the transformation definition file, or click Browse to select it.

3. Click Next to continue.