Hello everyone!
I inherited a complex BI loading that involves a also complex ODI environment. As this environment had different databases as sources in different places of the world, the load, as you can see below, uses external tables (file datapump), ftp transfer and then imports the file to the target database.
Since now all our databases are in the same place i would like to simplify our load to the model you can see below, using just a work table on the target.
Its working fine but the performance is, to my surprise, worse then the first one. The step "Load Data", responsible for selecting the data from the soruce and inserting in work table on the target, takes a long time to process. The code on source is a simple select and in target is a simple insert as you can see below.
What i read untill now is that this problem can be due to a row-by-row access done by ODI, wich would really be a problem since, on my first test i chose a table with over 700k registries (its not a big one).
Would anyone be able to help me figure out what to do? I thought about creating a dblink between the databases but i don't know if would solve the problem nor how to configure the LKM and IKM for this case.