Extract All BOM Revisions and Components Using SDK

Hello All,

I am new to supporting Agile, and I’ve gone into it head first, mostly with an Oracle Apps DBA background. This site has helped me solve several problems, so I thought I’d ask for some help this time around.

An interfacing system requires a periodic Agile extract of all BOM revisions and the related components. It basically needs the same item information displayed in the GUI as you select different BOM revisions.

Being a DBA, I initially tried to pull the information out of the database, but the BOM component revision logic used in the GUI must be convoluted. I got close but just could not get it to pull the exact same Item Rev values.

I am now playing with the SDK, since it is “supported”, assuming I can use the following approach:

1. Use iQuery to pull all ItemConstants.CLASS_ITEM_BASE_CLASS that have been released and are in “Active” or “Inactive” phase.
2. Loop through each row, getting the item information. 
3. If isFlagSet(ItemConstants.FLAG_HAS_BOM), getTable(ItemConstants.TABLE_BOM).
4. Loop through the BOM component items.
5. Repeat for each BOM revision (The revision loop will likely go after Step 3….just not there yet…)

Has anyone on the forum attempted something similar, and does the approach seem reasonable? Performance is fairly bad just running the iQuery, so I am also wondering if there are any different search criteria that will reduce the result set to BOMs only. Size-wise, I am working with approximately 200k rows in the ITEM table, about 65k of which are parent items in the BOM table. The BOM table contains ~400k rows, REV table ~475k rows.

Thanks in advance for any assistance or advice.

–Shad

Add Comment
2 Answer(s)

I have found that the IQuery performance degrades significantly over 50k records and I try to keep things around 10k for bulk processing.  You can do this by querying subclasses, categories, etc.  Keep in mind that an item may not have a BOM in the latest rev, but did have one in a prior rev, so I wouldn’t trust the BOM flag.  I’ve done this a few times and it works fine, albeit slower than going directly to the database.

Agile Angel Answered on December 12, 2018.

Thanks, Keith. I will try to whittle down the result set more in the iQuery to see if performance improves. I could see in the database that the search was doing full-table scans on the ITEM and BOM tables and hitting the indexes for the attributes. The exceedingly normalized design is not exactly good for reporting work.

And thanks for the tip on the BOM flag.

on December 13, 2018.
Add Comment

Hi Shad,

If I understand your requirement clearly, you need to send to some subscribing system all the parts in your system along with their BOM up to nth level and their related revisions, right? So have you given a thought of using ACS functionality without using SDK API? If your system is clustered and have load balancing enabled, then it would be better, in order to avoid system performance degradation, to keep the managed server where you’ll activate ACS out of load balancing. But still, extraction service upto nth level BOM if n > 5 is really resource intensive.

Apart from the above approach, going by the SDK API path, I recommend to minimize the size of the result table returned by IQuery using some more conditions. Like you are checking flag if part has BOM after fetching all items, instead you can put it in the Query criteria itself and will reduce the size of the resultant table to some extent, but of course as Keith has pointed this will only consider information related to latest revision only.
Then, format the search result to display only item number instead of the default fields of Description, Revision etc. Because the more fields are fetched in IQuery the more time Agile takes to validate the read privileges on those attributes for the user you’re using the session to create, and thereby increasing overhead.

Setting resultant ITable page size to some number like 1000 may help in increase of performance. Also, try to minimize the use of IRow.getReferent() and try to fetch the values from the columns from the BOM table itself wherever possible.

And finally, if you wanna go for SQL query approach, here is an excellent answer by Arif, please have a look if it suits your requirement:

https://myagileplm.com/questions/query-to-fetch-bom-upto-nth-level/ 

Agile Expert Answered on December 12, 2018.

Hi, Swagoto. I have not looked into the ACS functionality, but I will now that you recommended it as a potential solution.

In the end, we are looking for a CSV extract that looks something like this for the items in the BOM tab for all revisions of the BOM:

parent_number, parent_rev, parent_description, parent_release_date, parent_is_latest_rev, parent_attr1, component_number, component_rev, component_description, component_release_date, component_is_latest_rev, component_attr1

We are only going 1 level deep, so it’s not too complicated from that standpoint.

SQL would probably be the fastest, and I did stumble across Arif’s post earlier. I might take a second look at his query. I was mostly having issues pulling the same component revs that went with the BOM revision in the GUI. Obviously, I was joining or sorting something incorrectly.

Maybe I will catch the relevant SQL in the database while the SDK is hammering the system retrieving the TABLE_BOM for the specific revision.

on December 13, 2018.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.