Breaking news from around the world
Get the Bing + MSN extension
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | View virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
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.
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?
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.
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.
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.
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.
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.
Business Applications communities