Denormalizer

Short Description

Denormalizer creates a single output record from one or more input records. Input records should be sorted.

Ports

Metadata

Denormalizer does not propagate metadata.
Denormalizer does not have metadata templates.
Denormalizer does not require any specific metadata fields.

Denormalizer Attributes

[1] group size has higher priority than key. If neither of these attributes is specified, all records will form a single group.
[2] One of them must specified.

Details

Denormalizer receives sorted data through a single input port, checks Key values and creates one output record from one or more adjacent input records with the same Key value.

Denormalizer requires transformation. The transformation can be defined in CTL (see CTL Interface below) or in Java (see Java Interface below) or using existing .class file (Denormalize class attribute).

To define transformation, use one of the three transformation attributes: Denormalize, Denormalize URL or Denormalize class.

Diagram below describes flow of function calls in Denormalizer.

The function append() is called once for each input record. The function transform() is called once for each group of input records.

If you do not define any of the optional functions init(), preExecute(), clean() or postExecute(), the execution flow continues with the next function according to the diagram.

If you do not specify the appendOnError() or transformOnError() functions and an error occurs, the execution of graph fails.

The transformation uses a CTL template for Denormalizer, implements a RecordDenormalize interface or inherits from a DataRecordDenormalize superclass. The interface methods are listed in CTL Interface and Java Interface below.

Key

Key is expressed as a sequence of field names separated from each other by a semicolon, colon, or pipe.

Example 40. Key for Denormalizer

first_name;last_name

In this Key, first_name and last_name are fields of metadata on input port.

Sort order

If the records are denormalized by the Key, i.e. not by the Group size, the input records must be grouped according to the Key field value. Then, depending on the sorting order of the groups, select the proper Sort order:

• Auto - the sorting order of the groups of input records is guessed from the first two records with different value in the key field, i.e. from the first records of the first two groups.
• Ascending - if the groups of input records with the same key field value(s) are sorted in ascending order.
• Descending - if the groups of input records with the same key field value(s) are sorted in descending order.
• Ignore - if the groups of input records with the same key field value(s) are not sorted.

CTL Interface

The transformation written in CTL uses a CTL template for Denormalizer. Only the functions append() and transform() are mandatory.
Once you have written your transformation, you can also convert it to Java language code by clicking the corresponding button at the upper right corner of the tab.
You can open the transformation definition as another tab of the graph (in addition to the Graph and Source tabs of Graph Editor) by clicking the corresponding button at the upper right corner of the tab.

CTL Templates

function integer append() {
    CustomersInGroup++;
    myLength = length(errorCustomers);
    if(!isInteger($in.0.OneCustomer)) {
        errorCustomers = errorCustomers + iif(myLength > 0 ," - ","") + $in.0.OneCustomer;
    }
    customers = customers + iif(length(customers) > 0 ," - ","") + $in.0.OneCustomer;
    groupNo = $in.0.GroupNo;
    return OK;
}

function integer transform() {
    $out.0.CustomersInGroup = CustomersInGroup;
    $out.0.CustomersOnError = errorCustomers;
    $out.0.Customers = customers;
    $out.0.GroupNo = groupNo;
    return OK;
}

function void clean(){
    customers = "";
    errorCustomers = "";
    groupNo = 0;
    CustomersInGroup = 0;
}

function integer appendOnError(
                  string errorMessage,
                  string stackTrace) {
    printErr(errorMessage);
    return CustomersInGroup;
}

function integer transformOnError(
                  string errorMessage,
                  string stackTrace) {
    $out.0.CustomersInGroup = CustomersInGroup;
    $out.0.ErrorFieldForTransform = errorCustomers;
    $out.0.CustomersOnError = errorCustomers;
    $out.0.Customers = customers;
    $out.0.GroupNo = groupNo;
    return OK;
}

Access to input and output fields

Input records or fields
Input records or fields are accessible within the append() and appendOnError() functions only.

Output records or fields
Output records or fields are accessible within the transform() and transformOnError() functions only.

Capt. Eddie to ground control:

"All of the other CTL template functions allow to access neither inputs nor outputs. Remember that if you do not hold these rules, NPE will be thrown."

Java Interface

The transformation implements methods of the RecordDenormalize interface and inherits other common methods from the Transform interface. See Common Java Interfaces.
Following are the methods of the RecordDenormalize interface:

• boolean init(Properties parameters, DataRecordMetadata sourceMetadata, DataRecordMetadata targetMetadata)
  Initializes denormalize class/function. This method is called only once at the beginning of denormalization process. Any object allocation/initialization should happen here.
• int append(DataRecord inRecord)
  Passes one input record to the composing class.
• int appendOnError(Exception exception, DataRecord inRecord)
  Passes one input record to the composing class. Called only if append(DataRecord) throws an exception.
• int transform(DataRecord outRecord)
  Retrieves composed output record. For detailed information about return values and their meaning, see Return Values of Transformations. In Denormalizer, only ALL, 0, SKIP, and Error codes have some meaning.
• int transformOnError(Exception exception, DataRecord outRecord)
  Retrieves composed output record. Called only if transform(DataRecord) throws an exception.
• void clean()
  Finalizes current round/clean after current round. Called after the transform method was called for the input record.

Examples

Converting multiple having same key records to one
Input records acquired from relational database contain fields companyName and product.

Denormalizer Limited |chocolate
Denormalizer Limited |coffee
Denormalizer Limited |pizza
ZXCV International   |coffee

Convert the records to following form: companyName is followed by list of products separated by commas.

Solution
Use the Key and Normalize attributes.

//#CTL2

string[] products;
string companyName;

function integer append() {
    append(products, $in.0.product);
    companyName = $in.0.companyName;
    return OK;
}

function integer transform() {
    $out.0.companyName = companyName;
    $out.0.products = join(",", products);
    return OK;
}

function void clean() {
    clear(products);
}

Denormalizer returns following records:

Denormalizer Limited |chocolate,coffee,pizza
ZXCV International   |coffee

Records with the same Key have to be available in input data all at once. Otherwise you will get a new output record for each several subsequent records having the same key.

The best solution is to have input records sorted by Key.

Converting fixed number of records to one records
Given a list of students.

Charlie
Daniel
Agatha
Henry
Oscar
Kate
Romeo
Jane

Convert the list to groups of 3. Each group (one output record) has a number and names of its members. The names are separated by comma.

Each output record contains groupNumber and members.

Solution
Use the Group size and Normalize attributes. To be able to process the number of record not being divisible by 3, you need the Allow incomplete last group attribute.

//#CTL2

integer groupNumber;
string[] names;

function integer append() {
    append(names, $in.0.name);
    return OK;
}

function integer transform() {
    $out.0.groupNo = groupNumber;
    $out.0.members = join(",", names);
    groupNumber++;

    return OK;
}

function boolean init() {
    groupNumber = 1;
    return true;
}

function void clean() {
    clear(names);
}

Denormalizer returns following records:

1|Charlie,Daniel,Agatha
2|Henry,Oscar,Kate
3|Romeo,Jane

Best Practices

If the transformation is specified in an external file (with Denormalize URL), we recommend users to explicitly specify Denormalize source charset.

See also

Normalizer
Rollup
Common Properties of Components
Specific attribute types
Common Properties of Transformers