Finance | Project Operations, Human Resources, ...

Cleaning up INVENTITEMPRICE table

(0) Share

Report

Posted on by Francesco

524

I have an environment where the INVENTITEMPRICE table tops the bill with almost 100,000,000 records and a solid 26GB in space.

Enoug is enough, I say.

Problem is, I cannot find any sensible documentation on the subject and I detect some extreme prudence with those who have anything to say about it at all.

The extreme size is probably due to customization, but for now my first priority is to shrink that mother back to reality. So to make the question manageable, I will put it like this: Is there any objection against dropping the whole table and just do a fresh calculation (or as I like to call it: The nuclear option)?

Thanks for your valued input.

I have the same question (0)

All responses (6)

Answers (0)

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello Francesco,

It is very difficult answering your question because we do not know what the customization that you mentioned in doing with those data.

Do you have any additional information on this customization and why it was made?

Best regards,

Ludwig

Was this reply helpful? Yes No
Francesco 524 on at

Like (0)

Report

Hallo Ludwig,

I got out the old magnifying glass and deerstalker and traced it back to a custom class which does a price calculation. The class is called by a daily job, set to run for all purchased items. So every single day over 250,000 items are being priced (mostly without change).

I asked some people about the rationale behind this, but it will be a few days before I get an answer (if any). Of course I will question the respected MS partner who thought this was a good idea as well >:)

Regardless I'll have to fix this mess, so the question remains "What are the consequences if I drop the table and do a fresh calculation for all items?"

I might add to that that they use the devil's inventory valuation method (weighted average), which I suspect is relevant.

LG

Francesco

Was this reply helpful? Yes No
André Arnaud de Cal... 301,171 Super User 2025 Season 2 on at

Like (2)

Report

Hi Francesco,

If they are using weighted average, then there is at least no impact on costing as it is an actual method. The valuation option 'Standard cost' is using this table for cost price postings. However, this table is also used by BOM calculations. So, you have to be careful before breaking anything. I don't know what areas are in use in this environment.

At least, you can try to perform a cleanup in a test environment and then perform tests to analyse the impact. I don't think it is without risk to just truncate this table.

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,671 Moderator on at

Like (0)

Report

Hi Francesco,

The usage is mentioned above by Andre for Standard Costing (a) BOM calculations (b) Standard costs.

To rule out any issue that may arise, you have couple of options:

1. Don't delete any records from the table where StdCostVoucher is populated. These rows basically results in some postings when the standard cost changed. Used by inventSettlement table.

2. Don't try to delete records from the table where the bom is still assigned @ not ended production order just in case. Just analyze and filter data... join inventItemPrice with BomCalcTable. Then BomCalcTable with BomTable and then BomTable with ProdTable.

2a. Another option is delete and try to process open production orders and see if that works it out.

In addition, you should look at BOMCalcTable too and see if that table is having too many records as well.

Was this reply helpful? Yes No
Francesco 524 on at

Like (0)

Report

Thanks André & Satish.

The controller informed me today that this job is ran daily to make sure management reporting has the correct gross profit every day. Apparently purchase costs are so volatile that this is an issue (I'll refrain from further comment).

So the bulk of prices in there (identified by version id) have no relation with production orders, but are planned cost for purchase items.

None of the records in the table are associated with standard cost.

I can link about 1.5M records to production orders, without filtering on prod order status. Querying the table takes a longgggg time, even in SS Manager.

The BOMCALCTrans also made the top 12 with a respectable 650k pages and 5gB in size, but has less than 1M records. I suppose these are tied to the forementioned 1.5M.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,171 Super User 2025 Season 2 on at

Like (0)

Report

Interesting... where do they pull the purchase prices from? I do assume the management reporting is not done using AX directly. Then they can better maintain a table in a data warehouse instead for blowing up an ERP system.

Was this reply helpful? Yes No