Scenario 1

We have a source table as follows:

We have to move the into the target tables such that one

MY SOURCE		MY TARGET
SNo Name 1 A 2 B 1 C 3 D 4 F 1 E 2 G		SNo Name 1 A 2 B 1 C 3 D 4 F 1 E 2 G

After importing Source and Target, create a Sorter transformation to sort the data.
Then create an Expression transformation with 3 ports named say v_Name, v_SNo (2 variable ports) and o_Name (output port).
Give an expression as follows
v_Name -> IIF(SNo = v_SNo, v_Name||Name, Name)
v_SNO -> SNo
o_Name -> v_Name
Finally connect the ports to Target, create and execute the session and workflow.

Sequence Generator transformation:

• Sequence Generator transformation is Passive and Connected Transformation.
• The Sequence Generator transformation generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
• We use it to generate Surrogate Key in Data Ware House environment mostly. When we want to maintain history, then we need a key other than Primary Key to uniquely identify the record. So we create a Sequence 1,2,3,4 and so on. We use this sequence as the key. 
• For example: If EMPNO is the key, we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key and not EMPNO.

Sequence Generator Ports:

• The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.
• We cannot edit or delete these ports.
• Likewise, we cannot add ports to the transformation.
• NEXTVAL: Use the NEXTVAL port to generate sequence numbers by connecting it to a transformation or target.
• For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.
• CURRVAL: CURRVAL is NEXTVAL plus the Increment By value.
• We typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation.
• If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
• When we connect the CURRVAL port in a Sequence Generator transformation, the Integration Service processes one row in each block. We can optimize performance by connecting only the NEXTVAL port in a mapping.

Points to remember:

• If Current value is 1 and end value 10, no cycle option. There are 17 records in source. In this case session will fail.
• If we connect just CURR_VAL only, the value will be same for all records.
• If Current value is 1 and end value 10, cycle option there. Start value is 0. There are 17 records in source. Sequence: 1 2 – 10. 0 1 2 3 –
• To make above sequence as 1-10 1-20, give Start Value as 1. Start value is used along with Cycle option only.
• If Current value is 1 and end value 10, cycle option there. Start value is 1. There are 17 records in source. Session runs. 1-10 1-7. 7 will be saved in repository. If we run session again, sequence will start from 8.
• Use reset option if you want to start sequence from CURR_VAL every time.

Stored Procedure transformation

1. Stored Procedure transformation is a passive transformation. Stored Procedure transformation is a Connected and Unconnected Transformation
2. Stored procedures are stored and run within the database.
3. A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

Use of Stored Procedure in mapping:

1. Check the status of a target database before loading data into it.
2. Determine if enough space exists in a database.
3. Perform a specialized calculation.
4. Drop and recreate indexes. Mostly used for this in projects.

Data Passes Between IS and Stored Procedure

• One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure. There are three types of data that pass between the Integration Service and the stored procedure:
• Input/output parameters: Parameters we give as input and the parameters returned from Stored Procedure.
• Return values: Value returned by Stored Procedure if any.
• Status codes: Status codes provide error handling for the IS during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. We cannot see this value. The IS uses it to determine whether to continue running the session or stop.

Specifying when the Stored Procedure Runs

• Normal: The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. We pass some input to procedure and it returns some calculated values. Connected stored procedures run only in normal mode.
• Pre-load of the Source: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.
• Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.
• Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for dropping indexes or disabling constraints.
• Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

Using a Stored Procedure in a Mapping

1. Create the stored procedure in the database.
2. Import or create the Stored Procedure transformation.
3. Determine whether to use the transformation as connected or unconnected.
4. If connected, map the appropriate input and output ports.
5. If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
6. Configure the session.

Findind sum of the salary

1. Drag source and target tables into Mapping Designer.
2. Design the mapping as shown below.

3. Connect all ports from Source Qualifier transformation to Expression transformation.
4. Create an output variable in Expression transformation as shown below:
• o_Sum_Sal = CUME(sal)
5. Connect all ports fromm Expression transformation to target.

Generating sequence numbers without using Sequence Generator transformation

1. Create a target table with a field name SEQ_NO for sequence number.
2. Design the Mapping as shown above.
3. In the Expression transformation create a variable port and increment it by 1.
• V_Count=V_Count+1
4. In the Expression transformation create a output port and assign variable port to it.
• O_Count=V_Count
5. Connect O_Count to SEQ_NO.

Update Strategy transformation

Update Strategy transformation Active and Connected Transformation. The Update Strategy transformation to update, delete or reject rows coming from source based on some condition.

For example; if Address of a CUSTOMER changes, we can update the old address or keep both old and new address. One row is for old and one for new. This way we maintain the historical data.

Update Strategy is used with Lookup Transformation. In Data warehouse, we create a Lookup on target table to determine whether a row already exists or not. Then we insert, update, delete or reject the source record as per business need.

In PowerCenter, we set the update strategy at two different levels:

1. Within a session
2. Within a Mapping

Update Strategy within a session:

When we configure a session, we can instruct the IS to either treat all rows in the same way or use instructions coded into the session mapping to flag rows for different database operations.

Session Configuration:

Edit Session -> Properties -> Treat Source Rows as: (Insert, Update, Delete, and Data Driven). Insert is default.

Specifying Operations for Individual Target Tables:

You can set the following update strategy options:

1. Insert: Select this option to insert a row into a target table.
2. Delete: Select this option to delete a row from a table.
3. Update: We have the following options in this situation:
1. Update as Update. Update each row flagged for update if it exists in the target table.
2. Update as Insert. Inset each row flagged for update.
3. Update else Insert. Update the row if it exists. Otherwise, insert it.
4. Truncate: Select this option to truncate the target table before loading data.

Flagging Rows within a Mapping:

Within a mapping, we use the Update Strategy transformation to flag rows for insert, delete, update, or reject.

Operation	Constant	Numeric Value
INSERT	DD_INSERT	0
INSERT	DD_INSERT	0
UPDATE	DD_UPDATE	1
DELETE	DD_DELETE	2
REJECT	DD_REJECT	3

Update Strategy Expressions:

Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition. You can write these expression in Properties Tab of Update Strategy transformation.

IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )

Or

IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )

Note: We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them. To do, see the Properties Tab for the Option.

Interview questions on Lookup transformatioin?

Q. What is a lookup transformation?

Ans: The Lookup transformation is passive transformation. Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.

There are two types of Lookups in Powercenter Designer, namely:

1. Connected Lookup
2. Unconnected Lookup .

Q. What are the tasks of a lookup transformation?

How to return muliple values using Unconnected lookup?

Today we discuss how to return muliple values using Unconnected lookup. First of all, the UnConnected lookup has one return port and returns one column from each row. If the interviewer asked the above scenario, follow the below solution.

• Let us assume EMP will be source table and DEPT will be LOOKUP table.
• Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
• Go to the Mapping Designer and create the mapping with source, target, lookup and expression transformations as shown below:

Split non-key columns

Description: I have a source table with 3 columns EMPNO, ENAME, LOC. Now I would like to split the source into 2 taget tables based on the primary key EMPNO as one target table contains EMPNO, ENAME and another target table contains EMPNO, LOC as shown below.

MY SOURCE		TARGET_1		TARGET_2
SNo Name Loc 100 Chandu Hyderabad 200 Satish Chennai 300 Siva Noida 400 Srinivas Bengaluru		EmpNo EName 100 Chandu 200 Satish 300 Siva 400 Srinivas		EmpNo EName 100 Hyderabad 200 Chennai 300 Noida 400 Bengaluru

Solution:

1. Connect all ports from Source transformation to Aggregate transformation.
2. Connect EMPNO and ENAME ports to one target table.
3. And connect EMPNO and LOC ports to another target table.
4. The final mapping looks like as shown below:

Lookup Caches

We can configure a Lookup transformation to cache the lookup table. The Integration Service (IS) builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service also creates cache files by default in the $PMCacheDir. If the data does not fit in the memory cache, the IS stores the overflow values in the cache files. When session completes, IS releases cache memory and deletes the cache files.

Note:

• If we use a flat file lookup, the IS always caches the lookup source.
• We set the Cache type in Lookup Properties.

Lookup Cache Files:

1. Lookup Index Cache: Stores data for the columns used in the lookup condition.
2. Lookup Data Cache: For a connected Lookup transformation, stores data for the connected output ports, not including ports used in the lookup condition. For an unconnected Lookup transformation, stores data from the return port.

Types of Lookup Caches:

1. Static Cache:
By default, the IS creates a static cache. It caches the lookup file or table and looks up values in the cache for each row that comes into the transformation. The IS does not update the cache while it processes the Lookup transformation.
2. Dynamic Cache:
To cache a target table or flat file source and insert new rows or update existing rows in the cache, use a Lookup transformation with a dynamic cache. The IS dynamically inserts or updates data in the lookup cache and passes data to the target. Target table is also our lookup table. No good for performance if table is huge.
3. Persistent Cache:
If the lookup table does not change between sessions, we can configure the Lookup transformation to use a persistent lookup cache. The IS saves and reuses cache files from session to session, eliminating the time required to read the lookup table.
4. Recache from Source:
If the persistent cache is not synchronized with the lookup table, we can configure the Lookup transformation to rebuild the lookup cache. If Lookup table has changed, we can use this to rebuild the lookup cache.
5. Shared Cache:
There are two types of Shared Cache. They are...
1. Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the IS shares the cache by default. You can only share static unnamed caches.
2. Named cache: Use a persistent named cache when we want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

Concatinate duplicate values

MY SOURCE		MY TARGET
SNo Name 1 A 2 B 1 C 3 D 4 F 1 E 2 G		SNo Name 1 A 1 AC 1 ACE 2 B 2 BG 3 D 4 F

Solution:

• Here the no. of rows in source and target tables are same; so here we think about Passive transformations.
• After importing Source and Target, create a Sorter transformation to sort the data.
• Then create an Expression transformation with 3 ports named say v_Name, v_SNo (2 variable ports) and o_Name (output port).
• Give the expressions as follows
• v_Name as variable port

IIF(SNo = v_SNo, v_Name||Name, Name)

• v_SNO as variable port

SNo

• o_Name as output port

v_Name

• The final mapping looks like..

• Finally connect the ports to Target, create and execute the session and workflow.

Difference between Connected and UnConnected Lookup Transformation:

1. Connected lookup receives input values directly from mapping pipeline whereas UnConnected lookup receives values from :LKP expression in another transformation.
2. Connected lookup trasformation will process each and every row whereas UnConnected lookup will not process each and evry row. It will return the values based expression condition.
3. If the LKP condition is not matched the lookup transformation will return the default value in case of Connected lookup. If no match found for the LKP condition, the lookup transformation will return null values in case of Unconnected lookup.
4. Connected lookup transformation can use static or dynamic cache, whereas Unconnected lookup can use only static cache.
5. Connected lookup returns multiple columns from the same row whereas the UnConnected lookup has one return port and returns one column from each row.
6. Connected lookup supports user-defined default values and the UnConnected lookup does not support user defined values.

Unconnected Lookup transformation

An unconnected Lookup transformation is separate from the pipeline in the mapping. We write an expression using the :LKP reference qualifier to call the lookup within another transformation.

Steps to configure Unconnected Lookup:

1. Add input ports.
2. Add the lookup condition.
3. Designate a return value.
4. Call the lookup from another transformation.

Example for create a unconnected Lookup Transformation

1. Let us assume EMP will be source table and DEPT will be LOOKUP table.
2. Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus LOC.
3. Go to the Mapping Designer.
4. Click Mapping-> Create-> Give name. Ex: m_UNCONN_LKP
5. Drag Source and Target table.
6. Creat an EXPRESSION transformation and pass all ports from Source Qualifier to EXPRESSION transformation.
7. Then connect all fields from EXPRESSION to target except DNAME.
8. Create a LOOKUP transformation.

9. As DEPT is the Source definition, click Source and then Select DEPT.
10. Click Ok.
11. Now Edit the Lookup Transformation. Go to Ports tab.
12. As DEPTNO is common in source and Lookup, create a port IN_DEPTNO ports tab. Make it Input port only and Give Datatype same as DEPTNO.

13. Designate LOC as Return Port. Check on R to make it.
14. Now add a condition in Condition Tab.
15. DEPTNO = IN_DEPTNO and Click Apply and then OK.
16. Now we need to call this Lookup from Expression Transformation.
17. Edit Expression transformation and create a new output port out_LOC of datatype as LOC. 

18. Open the Expression editor and call Lookup as given below:
19. We double click Unconn in bottom of Functions tab and as we need only DEPTNO, we pass only DEPTNO as input.

20. Write the expression as :LKP.LKPTRANS(DeptNo)
21. The final mapping looks like as below..

22. Validate the call in Expression editor and Click OK.
23. Then save the mapping.
24. Create Session and Workflow. Run the workflow and see the data in target table.
25. Note:
26. Make sure to give connection information for all tables.
27. Make sure to give connection for LOOKUP Table also.

Connected Lookup Transformation

1. Let us assume that EMP will be source table. DEPT will be LOOKUP table.
2. Create a target table T_CONN_LKP_EMP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
3. Creating Mapping by click Tools -> Mapping Designer.
4. Click Mapping-> Create-> Give name. Ex: m_CONN_LKP_EMP
5. Drag EMP and Target table.
6. Connect all fields from SQ_EMP (Source Qualifier transformation) to target except DNAME and LOC.
7. Then go to Transformation-> Create -> Select LOOKUP from list. Give name and click Create.

8. As DEPT is the Source definition, click Source and then Select DEPT.
9. Click Ok.
10. Now Pass DEPTNO from SQ_EMP to this Lookup. DEPTNO from SQ_EMP will be named as DEPTNO1. Edit Lookup and rename it to IN_DEPTNO in ports tab.
11. Now go to CONDITION tab and add CONDITION.
12. DEPTNO = IN_DEPTNO and Click Apply and then OK.
13. We are not passing IN_DEPTNO and DEPTNO to any other transformation from LOOKUP; we can edit the lookup transformation and remove the OUTPUT check from them.
14. The final mapping look like as shown below..

15. Then Mapping -> Validate and save the mapping.
16. Create Session and Workflow. Run the workflow and see the data in target table.
17. Make sure to give connection information for all tables.
18. Make sure to give connection for LOOKUP Table also.
19. We use Connected Lookup when we need to return more than one column from Lookup table.

Lookup transformation

The Lookup transformation is passive transformation.

Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.
There are two types of Lookups in Powercenter Designer, namely:

1. Connected Lookup
2. Unconnected Lookup .

We can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Server can connect. We can use multiple Lookup transformations in a mapping.

Lookup transformation act as left outer joiner. If a particular null record matches found, then it returns null value.

The PowerCenter Server queries the lookup source based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup source column values based on the lookup condition. Pass the result of the lookup to other transformations and a target.

For example, if we want to retrieve all the sales of a product with an ID 10 and assume that the sales data resides in another table called 'Sales'. Here instead of using the sales table as one more source, use Lookup transformation to lookup the data for the product, with ID 10 in sales table.

Different caches can also be used with lookup like static, dynamic, persistent, and shared. The dynamic cache cannot be used while creating an un-connected lookup.

We can use the Lookup transformation to perform following:

• Get a related value: EMP has DEPTNO but DNAME is not there. We use Lookup to get DNAME from DEPT table based on Lookup Condition.
• Perform a calculation: We want only those Employees who’s SAL > Average (SAL). We will write Lookup Override query.
• Update slowly changing dimension tables: Most important use. We can use a Lookup transformation to determine whether rows already exist in the target.

TIP: We use Connected Lookup when we need to return more than one column from Lookup table.

Source Qualifier transformation

Source Qualifier transformation is active and connected transformation.

The Source Qualifier transformation represents the rows that the PowerCenter Server reads when it runs a
session.

It is only transformation that is not reusable. And it is the default transformation except in case of XML or COBOL files.

Tasks to performed by Source Qualifier:

• Join data originating from the same source database: We can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.
• Filter rows when the PowerCenter Server reads source data: If we include a filter condition, the PowerCenter Server adds a WHERE clause to the default query.
• Specify an outer join rather than the default inner join: If we include a user-defined join, the PowerCenter Server replaces the join information specified by the metadata in the SQL query.
• Specify sorted ports: If we specify a number for sorted ports, the PowerCenter Server adds an ORDER BY clause to the default SQL query.
• Select only distinct values from the source: If we choose Select Distinct, the PowerCenter Server adds a SELECT DISTINCT statement to the default SQL query.
• Create a custom query to issue a special SELECT statement for the PowerCenter Server to read source data: For example, you might use a custom query to perform aggregate calculations.

What are the Joins?

In SQL, a join is a relational operator that combines data from multiple tables into a single result set. The Joiner transformation acts in much the same manner, except that tables can originate from different databases or flat files.

Types of Joins:

1. Normal joins: With a normal join, the PowerCenter Server discards all rows of data from the master and detail source that do not match, based on the condition.
2. Master Outer join: This join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.
3. Detail Outer join: This join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.
4. Full Outer join: A full outer join keeps all rows of data from both the master and detail sources.

Example:

In EMP, we have employees with DEPTNO 10, 20, 30 and 50. In DEPT, we have DEPTNO 10, 20, 30 and 40. DEPT will be MASTER table as it has less rows.

Normal Join:

All employees of 10, 20 and 30 will be there as only they are matching.

Synax: DEPT.DEPTNO=EMP.DEPTNO

Master Outer Join:

All data of employees of 10, 20 and 30 will be there. There will be employees of DEPTNO 50 and corresponding DNAME and LOC columns will be NULL.

Syntax: {EMP LEFT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Detail Outer Join:

All employees of 10, 20 and 30 will be there. There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Syntax: {EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Full Outer Join:

All data of employees of 10, 20 and 30 will be there. There will be employees of DEPTNO 50 and corresponding DNAME and LOC columns will be NULL. There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Syntax: {EMP FULL OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Caution: Curly braces are needed in Syntax.

Note: A normal or master outer join performs faster than a full outer or detail outer join.

Joiner Transformations

Joiner transformations connected and active transformation.

Joiner transformation is an example for horizontal merging of tables..

Used to join source data from two related heterogeneous sources residing in different locations or file systems. Or, we can join data from the same source.

If we need to join 'n' tables, then we need 'n-1' Joiner transformations.

The Joiner transformation requires atleast two sources with at least one matching port. The Joiner transformation uses a condition that matches one or more pairs of ports between the two sources.

Joiner can't be used in following conditions:

• Either input pipeline contains an Update Strategy transformation.
• We connect a Sequence Generator transformation directly before the Joiner transformation.

Workig with the Joiner transformation:

1. In the Mapping, import create Joiner Transformation.
2. Edit Joiner Transformation -> Condition Tab
3. Add condition
1. In Joiner, one table is called as MASTER and other as DETAIL.
2. MASTER table is always cached. We can make any table as MASTER.
3. Edit Joiner Transformation -> Ports Tab -> Select M for Master table.
4. We can add as many conditions as needed.
5. Only = operator is allowed.
6. Set Master in Ports tab.
7. Mapping -> Validate
8. Repository -> Save.
9. Create Session and Workflow. Run the workflow and see the data in the target table.

Points to remember:

• Table with less number of rows should be made MASTER to improve performance. Because
• When the PowerCenter Server processes a Joiner transformation, it reads rows from both sources concurrently and builds the index and data cache based on the master rows. So table with fewer rows will be read fast and cache can be made as table with more rows is still being read.
• The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.
• If we join Char and Varchar datatypes, the PowerCenter Server counts any spaces that pad Char values as part of the string. So if you try to join the following:
Char (40) = “abcd” and Varchar (40) = “abcd”

Then the Char value is “abcd” padded with 36 blank spaces, and the PowerCenter Server does not join the two fields because the Char field contains trailing spaces.


The Joiner transformation does not match null values.
Any one of the pipeline contains Sequential transformation, then the Joiner transformation rejects the records.

Interview Questions on Aggregator Transformation

Q. What is aggregator transformation?

Ans: The Aggregator transformation allows us to perform aggregate calculations, such as averages and sums. Unlike expression transformation (performs calculations on a row-by-row basis), an Aggregator transformation allows us to perform calculations on groups.

RXVV7UF8ZQ6P

Q. What are the different types of aggregate functions?

Ans: The transformation language includes the following aggregate functions:

• AVG, COUNT , MAX, MIN, SUM
• FIRST, LAST
• MEDIAN, PERCENTILE, STDDEV, VARIANCE
• Single Level Aggregate Function: MAX(SAL)
• Nested Aggregate Function: MAX( COUNT( ITEM ))

Q. What is Nested Aggregate Functions?

Aggregator transformation

Aggregator transformation is a connected and active transformation.

The Aggregator transformation allows us to perform aggregate calculations, such as averages and sums. Aggregator transformation allows us to perform calculations on groups.

The Aggregator transformation is created with the following components...

1. Aggregate expression
2. Group by port
3. Sorted Input
4. Aggregate cache

Aggregate expression:

• Entered in an output port.
• Can include non-aggregate expressions and conditional clauses.
• The transformation language includes the following aggregate functions:
• AVG, COUNT , MAX, MIN, SUM
• FIRST, LAST
• MEDIAN, PERCENTILE, STDDEV, VARIANCE
• Single Level Aggregate Function. E.g.: MAX(SAL)
• Nested Aggregate Function. E.g.: MAX( COUNT( ITEM ))

Group By Ports:

• This component defines the groups for a specific port which participates for aggregations, rather than performing the aggregation across all input data. For example, we can find Maximum Salary for every Department.
• When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.
• In Aggregator Transformation, Open Ports tab and select Group By as needed.

Using Sorted Input:

• Use to improve session performance.
• To use sorted input, we must pass data to the Aggregator transformation sorted by group by port, either in ascending or descending order.
• When we use this option, we tell Aggregator that data coming to it is already sorted.
• We check the Sorted Input Option in Properties Tab of the transformation.
• If the option is checked but we are not passing sorted data to the transformation, then the session fails.

Aggregator Caches:

• The PowerCenter Server stores data in the aggregate cache until it completes aggregate calculations.
• It stores group values in an index cache and row data in the data cache. If the PowerCenter Server requires more space, it stores overflow values in cache files.

Note: The PowerCenter Server uses memory to process an Aggregator transformation with sorted ports. It does not use cache memory. We do not need to configure cache memory for Aggregator transformations that use sorted ports.

Working with Aggregato transformation:

1. In the mapping click Transformation -> Create -> Select AGGREGATOR from list. Give name and click Create. Now click done.
2. Pass the required fields (e.g.: SAL and DEPTNO) from SQ_EMP to AGGREGATOR Transformation.
3. Edit AGGREGATOR Transformation. Go to Ports Tab
4. Create output ports (e.g.: 4 output ports: OUT_MAX_SAL, OUT_MIN_SAL, OUT_AVG_SAL, OUT_SUM_SAL)
5. Open Expression Editor one by one for all output ports and give the calculations. Eg: MAX(SAL), MIN(SAL), AVG(SAL),SUM(SAL)
6. Click Apply -> Ok.
7. Drag target table now.
8. Connect the output ports from Aggregator transformation to target table.
9. Click Mapping -> Validate
10. Repository -> Save
11. Create Session and Workflow. Run the workflow and see the data in target table.