Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

Key Clean up Features for InventSum and InventDim Tables

Author: Arnold van Wageningen

Please find below a summary of the clean-up features we currently offer in regards to InventSum and InventDim tables which can be found under the menu: Inventory management > Periodic > Clean up.

The Inventory Dimensions cleanup shows the following help text:

“The ‘Clean up unused inventory dimensions’ batch process deletes all existing inventory dimensions that are defined but not used in the current company.

All unused inventory dimensions are deleted permanently. No alert or database log is created during the process.”

An InventDim record is created every time a combination of inventory dimensions (site, warehouse, location, pallet, serial number, batch number) is specified in the system. For example, a purchase order line where a new batch number is specified creates a new record in InventDim with the site, warehouse and batch number the user enters, and allocates a new number from number sequence to the InventDimId. This InventDimId is then set on the purchase order line to show which dimensions were registered. If you now change the batch number, a new InventDim record is created (or existing one with this batch number is found), and the InventDimId from this new record is copied to the purchase order line. The old InventDim record is however not deleted, since it might be used on some other document (sales order, production, inventory, etc.). So this previously created record will remain.

This cleanup tool basically verifies for each InventDim record if it is being used in not only purchase order lines or sales order lines, but also inventory transactions, on-hand inventory records, etc. Everywhere a reference exists to InventDim it is being checked. If it is not used anywhere, it is not needed, and will get deleted. If for whatever reason the same combination of dimensions is used later on, AX simply creates a new InventDim record with a new InventDimId, and uses this instead.

The On-hand entries cleanup shows the following:

“Closed on-hand entries that have not been updated for this number of days will be deleted.”

On-hand entries, or InventSum records, keep track of what is on-hand and what value this on-hand inventory has. It summarises InventTrans records, and basically sums up these records to show what the current totals are for these inventory transactions.

If you have an invoiced purchase order of 10 pieces with a value of 10 USD, and an invoiced sales order for 5 pieces, InventSum would show something like Posted qty 5, Financial value 5 USD. This will set the Closed flag on the InventSum record to False and the QtyClosed flag also to False.

Now, if you sell the last 5 pieces, Posted quantity will reduce to 0, and (depending on inventory valuation) financial value also drops to 0. If all the quantity (posted, deducted, received, etc.) and all value fields (financial value, physical value) are set to 0, AX flags the Closed field to True and the No open quantities field also to True. Many queries in AX look at the Closed field to determine if they should be included. In most queries these records are excluded.

If they are excluded, this clean up tool basically allows you to periodically delete these records to reduce InventSum table size. It cannot delete any other records, since they might have open quantities still or open values.

The On-hand entries aggregation by financial dimensions is introduced with KB 3007375 around December 2014.

This is basically extending the previously mentioned cleanup tool by also cleaning up records which have field Closed set to True!

The reason why this is needed is basically because in certain scenarios, you might have no more quantities in InventSum for a certain combination of inventory dimensions, but there is still a value. In some cases, these values will disappear, but current design does allow values to remain from time to time.

If you for example use Batch numbers, each batch number (and the combined site, warehouse, etc.) creates a new record in InventSum. When the batch number is sold, you will see quantity fields are set to 0. In most cases, the Financial/Physical value field is also set to 0, but in Standard cost revaluation or other scenarios, the value field may show some amount still. This is valid, and is the way AX handles the costs on Financial inventory level, e.g. site level.

Inventory value is determined in AX by records in InventSum, and in some cases Inventory transactions (InventTrans) when reporting inventory values in the past. In the above scenario, this means that when you run inventory value reports, AX looks (initially) at InventSum and aggregates all records to Site level, and reports the value for the item per site. The data from the individual records on Batch number level are never used. The tool therefore goes through all InventSum records, finds the ones where there is no more quantity (No open quantities field is True). There is no reason to keep these records, so AX finds the record in InventSum for the same item which has the same Site, copies the values from the Batch number level to the Site level, and deletes the record. When you now run inventory value reports, AX still finds the same correct values. This reduced number of InventSum records significantly in some cases, and can have a positive impact on performance of any function which queries this table.

The two last tools only update InventSum. Since InventSum is a summary for InventTrans, you can actually rebuild this table by again summarizing all the transactions, and sum up their quantities and values in the InventSum table. AX can actually do this by running the consistency check for Inventory management:

Consistency check

So if the cleanup does not give expected results, this tool can actually rebuild InventSum records. And setting the From date can prevent previously cleaned up records to be recreated. This does not mean you shouldn’t test this in a test environment first of course!