Informatica Common Components aggregator one aggregation expression

Source: Internet
Author: User

Conversion type: Connected, active
The aggregation transformation allows you to perform aggregate calculations, such as averages and sums. Aggregation conversions are different from expression conversions, and you can use aggregation transformations to perform calculations on multiple groups. Expression conversions allow you to perform calculations on a line-by-row basis.
When you use a conversion language to create an aggregation transformation, you can filter rows by using conditional clauses, which is more flexible than the SQL language.
PowerCenter performs its read aggregation calculations and stores the required data groups and row data in the aggregation cache.
After you create a session that contains an aggregation transformation, you can enable session options and incremental aggregations. When PowerCenter Server performs an incremental aggregation, it passes the new source data by mapping and uses the historical cache data to incrementally perform the new calculation.

The designer only allows aggregation expressions to appear in the aggregation transformation. An aggregation expression can include conditional clauses and non-aggregate functions. You can also include an aggregate function that embeds another aggregate function, such as:

MAX (COUNT ITEM)

The result of the aggregation expression varies depending on the port used in the conversion. For example, when PowerCenter calculates the following aggregate expression without a defined group by port, it will look for the total amount of items sold:

SUM (QUANTITY)

However, if you use the same expression and group by item port, PowerCenter returns the total amount of items sold by item.

You can create an aggregate expression on any output port and use multiple aggregation ports in the transformation.

Aggregation functions

You can use the following aggregate functions in the aggregation transformation. You can also embed an aggregate function into another aggregate function.

    • Avg
    • COUNT
    • First
    • Last
    • MAX
    • MEDIAN
    • MIN
    • Percentile
    • STDDEV
    • SUM
    • VARIANCE

when you use any of these functions, you must use them in an expression of the aggregation transformation.

Nested aggregate functions

You can include multiple single-level or multiple nested functions in different output ports of the aggregation transformation. However, you cannot include both single-level and nested functions in the aggregation transformation. Therefore, if the aggregation transformation contains a single-level function in any output port, you will not be able to use nested functions on any other port in this conversion. When you include single-level and nested functions in the same aggregation transformation, the designer will mark the mapping or mapping component as invalid. If you need to create a single-level function and a nested function, set up an aggregate transformation, respectively.

Conditional clauses

You can use conditional clauses in an aggregate expression to reduce the number of rows used in the aggregation. A conditional clause can be any clause that evaluates to TRUE or FALSE.

For example, you can use the following expression to calculate the total commission for an employee who exceeds its quarter limit:

SUM (COMMISSION, COMMISSION > QUOTA)

Non-aggregate functions

You can use non-aggregate functions in an aggregate expression.

The following expression returns the highest number of items sold per item, grouped by project. If the item is not sold, the expression returns 0.

IIF (Max (QUANTITY) > 0, Max (QUANTITY), 0))

Null value in aggregate function

When configuring PowerCenter, you can choose how you want PowerCenter to handle the empty values of the aggregate function. You can choose to treat null values as null or zero in an aggregate function. By default, PowerCenter Server treats null values as NULL in aggregate functions

Informatica Common component aggregator aggregation expression

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.