Part Cost Bucket vs Part Cost Bucket History

@ShawnBerk: Fantastic explanation, and very prompt; thank you, fellow Shawn.  :)  A couple of follow-up questions:

• I don’t care about costs for the purpose of this demand analysis, to be honest; I’m more concerned about quantities, in order to associate a top-level part to its constituent parts.  For that purpose, and based on your explanation, should I focus my extract on part_cost_bucket_one_tab?  Or is there a different table I might want to use instead?
• I understand your point about not unioning all three of these tables.  However, based on your description, would I be able to safely union one_tab and history_tab in order to generate a full historical record?  Asked another way: is the history table a record of previous structures for Cost Set 1?  (The history table doesn’t carry a COST_SET column, which is why I ask.)

Thank you for your help!

@ShawnFactorial 

If you aren’t concerned about costs and are looking at quantity and structure, then yes, I think there are better tables to use.  I would start with the Manuf_Structure group of tables and concentrate there as it contains all levels and revisions of the structure, so if you are looking to see changes from one engineering revision to another, you should be able to pick that up better from that table or group of tables.  The Part Cost Bucket group of tables are really about recording and managing cost first and foremost, not so much the relation of the top-level parts to child items.  They just come along for the ride in those tables.

Yes, the only items you’ll find in the history_tab if you stick with the cost tables is information that was once in the one_tab.  That is why Cost_Set isn’t in that table, it has no relevance because you can’t go from Cost Set 4 lets say direct to history.  It would have had to be copied to Cost Set 1 first, then the old Cost Set 1 information would move down to history.

@ShawnBerk: This is all super helpful, thank you so much.

Last question, I promise.  :)  The appeal of part_cost_bucket_one_tab is that it has already “exploded” the Bill of Materials for us: a clean look of top-level, parent, and component parts, including the “level 0” of the part being used to make itself (if applicable), allowing a user to filter for a top-level part and see all descendent parts at a glance.  Is there a similar such table or view amongst the manuf_structure group of entities that does this for me?  I’m certainly capable of crafting the SQL necessary atop manuf_structure_tab to explode it myself -- but it’s not trivial, and I’d prefer to avoid reinventing the wheel if it’s already available for my purposes.

Very good eplanation from Shawn (as always)

Be careful about performance here. The tables are large. In fact PartCostBucketHistory is often one of the largest table in the entire IFS Applications for a manufacturing customer. If you can live without the historical comparison, but still have easy accees to all components of a top part, without using “Start With … Connect By” clause, I would go for part_cost_bucket_one. In fact, if you can access the view Part_Cost_Bucket_One3 you don't have to bother about all cost buckets.

Do not run your queries against this table/view during peak load time… Well perhpas I should say “avoid”

Cheers,

Mats

@ShawnFactorial 

Product Structure > Multi Level Structure is the view I use all the time for this function.  In fact, I would want someone to justify to me why this view doesn’t work for them before I would spend the time to build something that lives outside of IFS.  However, given your initial statement regarding building a data warehouse, I can understand if you have a larger need to join non-IFS information in the warehouse.  For us, we have a global PLM system that is interfaced directly to IFS, so we view Engineering information directly in the PLM system and move backwards and forwards using the PLM UI if we need to look at previous revisions.  Similar information is carried over to IFS and we can view the current or historical information in every fashion we need from the Product Structure view.

@ShawnBerk: I see multi_level_structure_tab in the back-end, but in my case, it’s blank; I have assumed that this entity, like many in IFS, is only populated when a user elects to view the data in question.

My use case is to calculate demand as of various moments in time, in order to analyze improvements in inventory management over time.  That requires being able to associate Customer Orders with a Bill of Materials as of that moment in time to see how much material one would need to fulfill that demand.  Thus, all these questions about historical data I’ve been throwing your way.  :)

In any case, you’ve been extraordinarily helpful.  I’m marking your initial reply as the best answer.  Thank you!

OK thanks, hope you can tie things in the way you need to.  Glad you found the info useful.