ETL Architecture

[naveenshindhe]

Hi,
Can any one let me know about
1. ETL architecture
2. 1 source, 1 target, 5 lookups are there in mapping.
It takes 3 hours to run
How to make it run in 30 mintues.
Thanks
Naveen

 

[nbarmaki]

do you have a unconnected or connected Look up? You can join your look up tables as source table in the "source qualifier", that means you will have 8 source tables. Or take the largest look up table as the source table and join your soure table in source Qulifier. that is very important:join your source tables in one "source qualifier"!

 

[blom0344]

Modifying your lookups into sources may not be the answer. You should try to isolate the bottleneck in the entire mapping. Do you use an override SQL in the SQ? Test the performance of the SQL using an SQL-tool.
Create a testmapping as a copy of the mapping you are working on , but create a target just after the SQ. Check performance. Still very slow, probably not the lookups that cause the drag. Be sure to allow enough cache size , especially when using aggregators! (Increase DTM buffer pool size,index cache size,data cache size) Monitor your server , if you run out of memory this will cause veryyyyy slow performance (swapping).
Try to filter data in SQ instead of in seperate filter transformations,

else look here:

http://www.coreintegration.com/innercore/library/infa/infa_tuningguide.htm

T. Blom
Information analyst
tbl@shimano-eu.com

 

[reachbgk]

Try to use connected lookups. If possible cache your lookups. If you are using uncached lookups, make sure you have a index on the lookup column.