Performing multiple lookup and join in HCI Data services

[Former Member]

Hi Experts,

I need suggestion for HCI Data services job to transfer Transactional Data from ECC to IBP (On Cloud)

Interface has 5 lookup steps apart from mapping transformation logic like below.

Step 1, Fetch all the data from ECC Table 1 + Apply filter condition (a,b &c) returns Output 1 , T1.COL1,T1.COL2,....

Step 2, Fetch data from ECC Table 2 for only (T1.COL1 + T1.COL 2) combination from Table 1

Step 3, Do a Lookup for T2.COL1 + T2.COL2 into IBP Cloud Table and Filter TABLE2.RESULTS for combinations available only in Cloud Table + Condition a, b, c in Table

STep 4: From Step 3 result, Take Column T2.COL1 and Lookup in ECC Table 3.

Pass only those records available for T2.COL1 in Table 3.

STep 5: From Step 4 result + Lookup Table 4 and get New Column TABLE4.COL5

OUTPUT should be

STP4.RESULT.COL1,(2,3,4 ) + STEP 5.TABLE5.COLUMN5

At high level, im doing 4 ECC lookup with atleast 100,000 Records in each table and a Lookup to Cloud Table with 5000 Records

Possible solution

1. Get a stage table in ECC with STP4.RESULT.COL1,(2,3,4 ) and ABAP solution to perform the logic in ECC &

Cloud Lookup in HCI DS

2. Or Run the Jobs in 5 steps, where the data from the Lookup tables are saved as files in the HCI Agent in CSV.

what will be the performance impact for the scenario ?

Is there any possible different approach to perform multiple lookup. Can we write a Custom Query to Fetch data from Multiple Source Tables

with regards,

Nazeer

[venkat_madireddi]

Hello Nazeer,

You cannot do step 3 , instead you need to have a task/dataflow to extract the lookup content from cloud IBP tables to local file in agent , then use another task/dataflow to do lookup from local file . Combine both dataflows in a process object. Based on the lookup table size , consider proper cache mechanism for lookup function.

Thanks

Venkat