DSXchange

Hello

I have a situation where I am getting one record from source that needs to create multiple rows in the target. I am attempting to do this using with an implicite cartesian. The number of rows I need to create is determined by an output from a Oracle OCI to a hash file. (the reason I am doing this and not looking up directly against the database is the hash file creation is from a large table and I do not want to hit the table directly for each transaction). My problem is I am unable to retreive multiple rows from a hash (evidently on reading some posts here I now realize thats the way it is supposed to behave). Some receommendations was to use 'lookup' with 'return multiple rows' checked (I cant afford to use that because my query is expensive enough no to do it for each transaction). Your suggestions will help a lot.

Current structure

Source OCI ----> TRANSFORM <--------- MultiReturn HASH <--------------- Lookup OCI
Target OCI <----------|


Thanks
KK


PS: Sorry for the long winded paragraph.

Multi-row is available on the UV/ODBC, not Hashed file stage. The UV/ODBC stage has a SQL interface to the hashed file, but you incur penalties using it. The hashed file wil have to have secondary indexes added to the columns in your WHERE clause to avoid full table (really file) scans for every single reference lookup.

Your request for cartesian result set is a RDBMS functionality, not supported in DS inherent stages. Just 2 days ago someone suggested using the MERGE stage to produce a cartesian affect, but I haven't tested that. Maybe you can try it out and let us know?

I wonder why the sudden interest in Cartesian products? I recall that one Cartesian product test was not included in the QA test suite for Prime INFORMATION because it was estimated that it would take in the order of 110 years to complete. (It was a ten-way cross join.)

The essence of performance is to do the minimum amount of work possible. Can your requirement be satisfied without examining every possible permutation of source values? Can the source database filter out some (most?) of the unwanted combinations? Be selective here, or don't complain about "performance".

Yes will take a stab at using merge and let you know how it goes. Thanks for the quick response

The business requirement is to spread cost incurred for maintaining an application (telecom, network cost etc.) across all other APPLICATIONS prorated.

Eg:

Telecomm Cost 10000

Application 1 Maintenance Cost (SAP) - 800
Application 2 Maintenance Cost (PeopleSoft) - 600
Application 3 Maintenance Cost (Desktop) - 200

Then the 10000 must be spread to the 3 applications above at the calc of (800/1600) * 10000, (600/1600) * 10000 and (200/1600) * 10000

In anycase thats the requiement in a nutshell.

Shall keep you posted with approach we come up with.

Thanks for your response.

I wonder whether a stream containing three left outer joins would work for this case?

kkumar@frk.com wrote:Hello

I have a situation where I am getting one record from source that needs to create multiple rows in the target. I am attempting to do this using with an implicite cartesian. The number of rows I need to create is determined by an output from a Oracle OCI to a hash file. (the reason I am doing this and not looking up directly against the database is the hash file creation is from a large table and I do not want to hit the table directly for each transaction). My problem is I am unable to retreive multiple rows from a hash (evidently on reading some posts here I now realize thats the way it is supposed to behave). Some receommendations was to use 'lookup' with 'return multiple rows' checked (I cant afford to use that because my query is expensive enough no to do it for each transaction). Your suggestions will help a lot.

Current structure

Source OCI ----> TRANSFORM <--------- ODBC Stage <--------------- Lookup OCI
Target OCI <----------|


Thanks
KK


PS: Sorry for the long winded paragraph.

Ken/Ray

Per you suggestion I changed the job as follows and I am 'golden' now. Thanks for your input

Source OCI ----> TRANSFORM <--------- ODBC Stage <--------------- Lookup OCI
Target OCI <----------|

*MultiReturn driven by checking the 'Reference Link with muti row result set' on the link property