accel99cn wrote:I have an initial solution, hope some one could correct and optimize the whole mapping design, especial whenit come to the optimal performance when extract data from these huge files:



1) extraction of data



Based on performance consideration, the Flat file is a huge file, I use external loader( such as SQL*LDR of Oracle) to bulk load staging DB into staging table(CUSTOMER_LoadedfromFlatFile). This script can be setup as a command task in the workflow.



2) Mapping building:



a.
Use a SQTrans to retrieve the two table in staging DB, with a SQL query Override of Union like this:



select * from

(
(

SELECT CUSTOMER_LoadedfromFlatFile.TERRITORY_ID as TERRITORYID , CUSTOMER_LoadedfromFlatFile.CUSTOMER_ID as CUSTOMERID, CUSTOMER_LoadedfromFlatFile.ACCOUNT_NUMBER as ACCOUNT_NUMBER, CUSTOMER_LoadedfromFlatFile.CUSTOMER_TYPE as CUSTOMER_TYPE,

CUSTOMER_LoadedfromFlatFile.MODIFIED_DATE as MODIFIED_DATE

FROM

CUSTOMER_LoadedfromFlatFile

)

Union

( SELECT CUSTOMER.TERRITORY_ID as TERRITORY_ID , CUSTOMER.CUSTOMER_ID as CUSTOMER_ID, CUSTOMER.ACCOUNT_NUMBER as ACCOUNT_NUMBER, CUSTOMER.CUSTOMER_TYPE as CUSTOMER_TYPE,

CUSTOMER.MODIFIED_DATE as MODIFIED_DATE

FROM

CUSTOMER

)
) order by TERRITORYID , CUSTOMERID


Settings for good query performance in the SQTrans:

Sorted inputs: 2

Create SQL commands in Pre SQL: create B-tree index on the key fields group.

Post SQL: truncate the table and drop the index for next loading.



b.
Use a Aggregator TRANS connected to the above SQTrans to remove duplicate record.
Setting: Group by key field, set aggregate function to First() or no setting of aggregate function ; check sorted input; set proper data cache and index size to 2 times of standard calculation in informatica manual if allowed.



c.
for SCD type handling of data change: Use Dynamic cached lookup


Aggregator TRANS --> lookup target table with Dynamic cache enable --> Use a filter trans to filter out unchanged rows (setting NewLookupRow != 0) --> Use Router trans to route the updated rows and new rows --> Use 2 Update strategy trans( one to update and one to insert the target table.



Key setting:

lookup: Dynamic cache enable, associated lookup target surrogate Key port with Sequence-ID;



Router_Insert_Update:

InsertGroup: NewLookupRow = 1

UpdateGroup: NewLookupRow = 2

Upd_Insert Expression: DD_INSERT

Upd_update Expression: DD_UPDATE





Session setting:



Session edit -- > Property: set Treat source row as DATA DRIVEN



Session edit -- > mapping --> Target: Check these attibutes: SELECT, Update as Update, No Select Delete.



Set partition point at SQ Trans and Agregater.



--- End of Solution ----



Is there Any one contribute to the design for the real life scenario?
Make each great day, buddies!

Ike

accel99cn wrote:b.
Use a Aggregator TRANS connected to the above SQTrans to remove duplicate record.
Setting: Group by key field, set aggregate function to First() or no setting of aggregate function ; check sorted input; set proper data cache and index size to 2 times of standard calculation in informatica manual if allowed.



c.
for SCD type handling of data change: Use Dynamic cached lookup


Aggregator TRANS --> lookup target table with Dynamic cache enable --> Use a filter trans to filter out unchanged rows (setting NewLookupRow != 0) --> Use Router trans to route the updated rows and new rows --> Use 2 Update strategy trans( one to update and one to insert the target table.