如何在Informatica Cloud(IICS)中实现层级数据的循环溯源映射？

Recursive Mapping Implementation (Dynamic Depth) #

To handle variable-depth hierarchies (like your example where 203 traces up to 2), use a recursive lookup loop within a mapping:

1. Source Setup

   • Connect your Excel source to the mapping; ensure the source qualifier reads both Id and D-id columns.

2. Unconnected Lookup Transformation

   • Create an unconnected lookup (LKP_Get_Parent) that references the same source data (or a cached staging table).
   • Configure the lookup condition: Input_Id = LKP_Id, returning the corresponding D-id.
   • Enable lookup caching to avoid repeated full scans of the source data.

3. Expression Transformation for Recursive Traversal

   • Add an expression transformation with the following variables and ports:
     • Variables:
       • v_Current_Id: Initialized to the input Id (e.g., 203)
       • v_Current_D_Id: Uses :LKP_Get_Parent(v_Current_Id) to fetch the parent ID
       • v_Root_Parent: Tracks the root node (updated only when v_Current_D_Id is null)
       • v_Loop_Counter: Prevents infinite loops (set a reasonable max, e.g., 10)
     • Port Logic:

       v_Current_Id = IIF(v_Current_D_Id IS NOT NULL AND v_Loop_Counter < 10, v_Current_D_Id, v_Current_Id)
       v_Current_D_Id = IIF(v_Current_D_Id IS NOT NULL AND v_Loop_Counter < 10, :LKP_Get_Parent(v_Current_Id), v_Current_D_Id)
       v_Root_Parent = IIF(v_Current_D_Id IS NULL, v_Current_Id, v_Root_Parent)
       v_Loop_Counter = v_Loop_Counter + 1
       

   • The loop continues until v_Current_D_Id is null (root found) or the counter hits the max limit.

4. Filter and Output

   • Add a filter transformation to retain only rows where v_Root_Parent is not null (root identified).
   • Map v_Root_Parent to the Parent output column and the original input Id to the Child column.

Fixed-Depth Alternative (Known Hierarchy Levels) #

If your hierarchy has a fixed maximum depth (e.g., 3 levels in your sample), chain lookups directly without recursion:

• First lookup: Get D-id of the child (203 → 110)
• Second lookup: Get D-id of the first result (110 →14)
• Third lookup: Get D-id of the second result (14 →2)
• Check if the final D-id is null, then set Parent to 2.
• This is simpler but only works if you know the hierarchy depth upfront.

Pre-Processing with Recursive SQL (If Using a Database) #

If you can stage the Excel data in a database first, use recursive SQL to compute root parents before bringing the data into IICS. For example, in MySQL 8+/PostgreSQL:

WITH RECURSIVE hierarchy AS (
    SELECT Id, D_id, Id AS Child
    FROM your_staging_table
    UNION ALL
    SELECT h.Id, t.D_id, h.Child
    FROM hierarchy h
    JOIN your_staging_table t ON h.D_id = t.Id
    WHERE t.D_id IS NOT NULL
)
SELECT Id AS Parent, Child
FROM hierarchy
WHERE D_id IS NULL;

Use this SQL query as a source in IICS to directly get the desired Parent-Child pairs.

Task Flow Troubleshooting #

If your initial task flow attempts failed, check these common issues:

• Missing lookup caching, leading to poor performance and repeated scans
• Incorrect loop termination (not checking for null D-id)
• Losing track of the original child ID during loop iterations
• Not handling edge cases (e.g., nodes that are already roots with D-id = Null)

内容的提问来源于stack exchange，提问作者Arindam Basu