Table of Contents

Search

  1. Preface
  2. Product Overview
  3. Before You Begin
  4. Tutorial Lesson 1
  5. Tutorial Lesson 2
  6. Tutorial Lesson 3
  7. Tutorial Lesson 4
  8. Tutorial Lesson 5
  9. Tutorial Lesson 6
  10. Naming Conventions
  11. Glossary

Getting Started

Getting Started

Creating an Aggregator Transformation

Creating an Aggregator Transformation

Next, add an Aggregator transformation to calculate the average, maximum, and minimum prices of items from each manufacturer.
  1. Click
    Transformation
    Create
    to create an Aggregator transformation.
  2. Select
    Aggregator
    in the
    Select the transformation type to create
    dialog box and type AGG_PriceCalculations in the
    Enter a new name for this transformation
    dialog box. Click
    Create
    , and then click
    Done
    .
    The naming convention for Aggregator transformations is AGG_
    TransformationName
    .
    The Mapping Designer adds an Aggregator transformation to the mapping.
    The following image shows a mapping with an unconnected Aggregator transformation:
    The mapping contains a source, source qualifier, and an unconnected Aggregator transformation and target.
  3. Click
    Layout
    Link Columns
    .
    When you drag ports from one transformation to another, the Designer copies the port description and links the original port to its copy.
    If you click
    Layout
    Copy Columns
    , every port you drag is copied, but not linked.
  4. From the Source Qualifier transformation, drag the PRICE column into the Aggregator transformation.
    A copy of the PRICE port now appears in the new Aggregator transformation. The new port has the same name and datatype as the port in the Source Qualifier transformation.
    The Aggregator transformation receives data from the PRICE port in the Source Qualifier transformation. You need this information to calculate the maximum, minimum, and average product price for each manufacturer.
  5. Drag the MANUFACTURER_ID port into the Aggregator transformation.
    You need another input port, MANUFACTURER_ID, to provide the information for the equivalent of a GROUP BY statement. By adding this second input port, you can define the groups for the aggregate calculation. You will create a group to organize the data by manufacturer.
  6. Double-click the Aggregator transformation, and then click the
    Ports
    tab.
  7. Clear the
    Output (O)
    column for PRICE.
    You want to use this port as an input (I) only, not as an output (O). Later, you use data from PRICE to calculate the average, maximum, and minimum prices.
  8. Select the
    Group By
    option for the MANUFACTURER_ID column.
  9. Click the
    Add
    button three times to add three new ports.
    When you select the
    Group By
    option for MANUFACTURER_ID, the Integration Service groups all incoming rows by manufacturer ID when it runs the session.
  10. Configure the output ports.
    The following table describes the output ports:
    Name
    Datatype
    Precision
    Scale
    I
    O
    V
    OUT_MIN_PRICE
    Decimal
    19
    2
    No
    Yes
    No
    OUT_MAX_PRICE
    Decimal
    19
    2
    No
    Yes
    No
    OUT_AVG_PRICE
    Decimal
    19
    2
    No
    Yes
    No
    You can select each port and click the
    Up
    and
    Down
    buttons to position the output ports after the input ports in the list.
  11. Click
    Apply
    to save the changes.

0 COMMENTS

We’d like to hear from you!