BOM parts explosion using DS
Posted: Mon Mar 06, 2006 8:55 am
This is the first time I am posting here at DSXchange. I have read many posts and searched far and wide, but, those points I have found just haven't worked. Here we go...
I have a BOM (bill of material) which needs to be cleansed for duplicate parent - child relationships. This would be easy if my DB supported recursion, it doesn't and recursion according to other posts is difficult in DS. Therefore I use iteration.
BOM table:
pid/cid/plant/seq
1 2 a 10
1 2 a 20
1 2 b 5
1 2 b 7
2 3 a 10
2 4 b 10
...
TEMP_BOM table:
pid/cid/plant/seq/level
1 2 a 10
1 2 a 20
2 3 a 10
2 4 b 10
The two rows removed (b 5, b 7) represent the same part, built at different plants. If not removed, our part explosion list would count 4 units required for parent id = 1 instead of 2, the correct number (this gets worse as the tree explodes if not handled correctly).
The cleansing requires looking into the temp table to what the plant and seq fields are.
Firstly, I seed my TEMP_BOM table with unique parents.
In my multiple instance stage (called iterative from a job control job) I have the following,
------------------------(LookupTempBom, ODBC)
-----------------------------------------|
-----------------------------------------|
-----------------------------------------|
(BOM join TEMP_BOM)--------(TRANSFORM)-----------(TEMP_BOM)
My problem is when reading from my join I get the right number of rows, the lookup finds the correct rows (on foreign key), however, the lookup from the ODBC stage places extra rows in the input rows written to TEMP_BOM, why?
I have tried with hashfiles as the temp lookup, however, this gives problems as the lookup keys are different from the primary keys.
Does anyone have any ideas (other than a stored procedure on my DB, this is an option, we prefer not). Thank you.
I have a BOM (bill of material) which needs to be cleansed for duplicate parent - child relationships. This would be easy if my DB supported recursion, it doesn't and recursion according to other posts is difficult in DS. Therefore I use iteration.
BOM table:
pid/cid/plant/seq
1 2 a 10
1 2 a 20
1 2 b 5
1 2 b 7
2 3 a 10
2 4 b 10
...
TEMP_BOM table:
pid/cid/plant/seq/level
1 2 a 10
1 2 a 20
2 3 a 10
2 4 b 10
The two rows removed (b 5, b 7) represent the same part, built at different plants. If not removed, our part explosion list would count 4 units required for parent id = 1 instead of 2, the correct number (this gets worse as the tree explodes if not handled correctly).
The cleansing requires looking into the temp table to what the plant and seq fields are.
Firstly, I seed my TEMP_BOM table with unique parents.
In my multiple instance stage (called iterative from a job control job) I have the following,
------------------------(LookupTempBom, ODBC)
-----------------------------------------|
-----------------------------------------|
-----------------------------------------|
(BOM join TEMP_BOM)--------(TRANSFORM)-----------(TEMP_BOM)
My problem is when reading from my join I get the right number of rows, the lookup finds the correct rows (on foreign key), however, the lookup from the ODBC stage places extra rows in the input rows written to TEMP_BOM, why?
I have tried with hashfiles as the temp lookup, however, this gives problems as the lookup keys are different from the primary keys.
Does anyone have any ideas (other than a stored procedure on my DB, this is an option, we prefer not). Thank you.