BOM parts explosion using DS

tas · Post by **tas** » 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.

ArndW · Post by **ArndW** » Mon Mar 06, 2006 9:07 am

Thomas,

DataStage is stack-based and supports recursion - it just needs to be correctly defined. I can't figure out from your BOM description exactly what you are trying to do. What are your columns supposed to denote? Why is "1 2 b 5" a duplicate of "1 2 a 20"? I think if I understood what you are trying to do I might be able to point you in a useful direction, but I'm somewhat at a loss right now.

tas · Post by **tas** » Mon Mar 06, 2006 9:33 am

I have updated the main post, hopefully this clarifies.

ArndW · Post by **ArndW** » Mon Mar 06, 2006 9:58 am

It helps, but not quite. Could you explain the 4 columns? I'm used to having parts as the lowest component, then n-parts that make up an assembly. You have a "plant" and the last column of "seq". What constitutes a unique part, 3 of your columns? Depending on what you need to identify an assembly you might not need a reference if you can order your source data and won't need any recursion, either.

ml · Post by ml » Mon Mar 06, 2006 10:15 am

Hi Thomas, welcome.

Could you explain which is the criteria to remove b5 and not a10 in your example? And how is generated the TEMP_BOM file?

Remember that in DS the hash files overwrite the duplicate keys.
I think that maybe you need to check the constraint in the transformer.

good luck

I_Server_Whale · Post by **I_Server_Whale** » Mon Mar 06, 2006 10:18 am

As far as I could understand,

I have tried with hashfiles as the temp lookup, however, this gives problems as the lookup keys are different from the primary keys.

Check this link, if it is the similar kind of issue.

LINK

Hope that helps,
Thanks,
Naveen.

tas · Post by **tas** » Mon Mar 06, 2006 3:52 pm

Hej Arnd,

pid = parent part id
cid = child part id
plant = parent consuming plant
seq = sequence in which the part is consumed

All four are keys in my BOM table. However, the multiple child part produced at various plants and furthermore produced in differenct sequences at those plants must be removed to get a true BOM. Another way of stating the problem is that I have duplicate BOMs in the same data structure.

In my lookup I want to set the pid/cid as keys and get all rows back (mulitple row lookup) so I can do my logic and apply my constraints in the transformer. Unfortunately hashfiles don't seem to like changing the keys and the ODBC table lookup gives extra rows from the output of the transformer???

For example, I read 6 rows from the joing of BOM/TEMP_BOM, lookup of TEMP_BOM finds 2 rows and the output to TEMP_BOM is 7 rows.

If I use a hashfile as the lookup I can cheat the keys for the current hierarchy level of my iteration. Setting the hasfile to 'enabled load to memory/lock updates', the lookup finds the required rows to compare. And in the above example I get the correct output to TEMP_BOM as 6 rows. However, the keys used to lookup destroy the duplicate rows when the hashfile finally writes to disk and therefore becomes useless to the next hierarchy level in the BOM tree.

(This is breadth first search btw). I hope this blob helps....maybe I am trying to implement something DS just can't handle?

Cheers,
Thomas

I_Server_Whale · Post by **I_Server_Whale** » Mon Mar 06, 2006 4:00 pm

Hi Thomas,

Please give us an example of what you are trying to achieve.
The source data, the look-up data and your target data example should be good enough.

I'm still thinking that the link posted by me above in the last post will certainly help.

Please let me know if you have any questions.

Thanks,
Naveen.

tas · Post by **tas** » Mon Mar 06, 2006 4:07 pm

Hello Nav,

No the link doesn't help! I need to find muliple rows from the hashfile lookup using same foreign key concept as the ODBC / UV lookup stage can provide with multirow lookup.

Please look at my original post, all the data is there! I only have two tables which I am working with, BOM and TEMP_BOM.

Cheers,
Thomas