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.
BOM parts explosion using DS
Moderators: chulett, rschirm, roy
BOM parts explosion using DS
Last edited by tas on Mon Mar 06, 2006 3:39 pm, edited 3 times in total.
Best Rgds,
Thomas Søndergaard
Thomas Søndergaard
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
As far as I could understand,
LINK
Hope that helps,
Thanks,
Naveen.
Check this link, if it is the similar kind of issue.
I have tried with hashfiles as the temp lookup, however, this gives problems as the lookup keys are different from the primary keys.
LINK
Hope that helps,
Thanks,
Naveen.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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
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
Last edited by tas on Mon Mar 06, 2006 4:01 pm, edited 1 time in total.
Best Rgds,
Thomas Søndergaard
Thomas Søndergaard
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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
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
Best Rgds,
Thomas Søndergaard
Thomas Søndergaard