The company behind the inventory II

Inventory II is an add-on module to Microsoft Dynamics AX, that focuses on improving and extending inventory management. Before I look deep into the product, I wanted to take a look at the developers of the module. The company behind the Inventory II is ‘FSB‘. FSB is the first letters of Flemming, Søren and Benny. For us, that have been with the Dynamics AX from the beginning, will recognize these developers, because they are the founding fathers of most of the costing, supply chain and production code in Dynamics AX. They worked closely together with Erik Damgaard to create to Axapta(That later became Dynamics AX). Much of the current source code of Dynamics AX originates from ‘FSB‘-team. This tells me that the Inventory II is made by developers with probably the best knowledge of how the inventory architecture in Dynamics AX is build up.

So what is it all about ?

First we need to understand how standard Microsoft Dynamics AX is maintaining on-hand and how the inventory values are tracked. In AX all transactions are stored in a table called inventTrans. Each time records are inserted, updated or deleted, these changes will update another table called InventSum. As the name suggests; InventSum contains a summary of the onhand per dimension(like color, size, location, warehouse, pallet etc). The data model can be visualized like this :

Normally this model works very fine, but there are some drawbacks in the model.

Very large joins

Since the table InventSum and InventDim table can be VERY large, I have often seen performance problems when these two tables are joined in queries. Often from a financial perspective you may want to get the inventory value in total per warehouse. At a customer site where they have approx. 13000 SKU’s and generate approx. 50.000 inventory transactions a day, they have around 60 million inventory transactions in total. What trying to get a total inventory value, the reports often needed to run for many hours. It seems, when you need to join the two very large tables, the performance of SQL drops drastically.

Reporting on historical values

Another issue is how to report inventory sum and values on a specific date. Like “1/1/2010”. I have looked deep into this, and see that the way std. AX is working, is to take the current InventSum, and “sum-reverse” inventory transactions posted between now and the wanted reporting date. If you have many transactions, you will see that this quickly will take a lot of time to make an overall reporting.

Database locking

At a customer site where we implemented Axapta 3.0, we had 18 packing stations, that packingslip and invoice updated sales orders all day long. The average size of the sales orders was 8 lines. What we very often experienced, was “blocking locks” at the packing stations when they did the postings. The reason was that when the packingstations invoice updated the orders, Axapta also did a update on the InventSum to reflect this posting. If this also happened on another packingstations, it very often could lead to a blocking lock, where the two packingstation was waiting for each other to complete. At that time we concluded that the main reason for this was the “UPDATE INVENTSUM” that std Axapta made. This process has been improved in AX 40, and AX 2009, but the update processes still there, that could lead to blocking locks.

Inventory value

With std. AX there is a need to run a periodic process called “inventory closing”. The inventory closing is the process of settling receipt cost values with the issue cost values. To say it simple; If the financial on-hand quantity is Zero, the financial value should also be zero. This is actually a great feature, because it allows you to sell the items before you receive the vendor invoice, and the real cost from the vendor invoice can be propagated to the respective sales orders/ issue transactions.
But what we see in practice is that the inventory closing is ran one a month, and then it could take several hours to complete. Also a lot of issues related to this has also been seen, which could result that the inventory values suddenly gets bigger than the US military budgetJ.

Level based reservations / intelligent reservation

For large distributors, reservations are a “must-have”. The ability to promise the customers that the order has been reserved almost guarantees that the customer will get the goods in time, and that no other sales order will “steel” the items. But many distribution centers differentiate between buffer and picking locations. In std. AX you could physical reserve the items, but this prevented the users from moving the goods from the buffer area to the pick area, where the items could be picked. The reason for this is that the reservation is “exact”. In Axapta 3.0 this resulted in that we needed to make a system called “virtual on-hand”, to maintain reservations, but still have the opportunity to move the items around. Another important feature I missed was the ability to control the allocation. So that some on hands dimensions are used before others. A scenario for this is if you have an automated warehouse area, and you want to control this area in relation to other area’s.

Inventory II

When we should upgrade the customer to AX 2009, we got to know the FSB, and got trained on the Inventory II product. WOW, did this solve most of our biggest problems. The solution was elegantly small, and easy deploy into the system. The Inventory II was also built into the To-Increase module “Warehouse Management & Distribution”. The architecture is a bit different, and can be visualized like this :

The complexity of on-hand has been replaced by a single table, called ImTrans. This system uses a ‘insert-approach’, meaning that there do not happen any SQL-“update” statements on any of the records. This way it removes any “blocking locks” and maximizes concurrency in the database when updating the on-hand values. FSB also introduced a system called “Watermark” to keep track of what records are valid for the current on-hand. If we take the process of a sales order going from “On order” to invoiced, the ImTrans would have transactions like this :

Step 1 : First transaction – Sales order line is created with 10 pcs of item V1. Inserted with an issue status of ‘on order’, and the watermark level is ‘W1’

Step 2 : Line is reserved; This results in a “reverse transaction” where the first transaction is eliminated (If you do a select sum() you would only get that 10 pcs is reserved physically)

Step 5 : Then we “jump” forward to the invoice step, and each time the previous statuses are reversed.

So the on-hand transactions are not a single record, but a SUM of the records. The watermark will make sure that you only fetch the latest values when you do reporting. So this solved the issue of the blocking lock when doing postings.

But will this not generate a gigantic table ? The answer is yes, it would, and the heart of the inventory II is therefore a background engine, that will eventually remove ImTrans records that no longer represents any valid data. In inventory II this engine can be visualized like this :

Here there is a parameter for how long the inventory II should keep the records in the intermediate status postings. FSB has also included a monitor for what the inventory II engine is doing :

But the engine does much more. It also do the cost settling, background reservations, compression, issue alerting and posting cost differenced to general ledger.

But for me feature I like the most is the level based reservation(Also called intelligent reservation), where sales order can be reserved on a warehouse at order intake, but not specify location, batch on the reservation. Later in the process when picking route has been created, the reservation can be tighten down to location etc. And at the end when the transaction is picked,(or posted) the final dimensions like pallet ID, serial ID etc is specified.

The nice thing is that the set up for this is dynamic, and is specified on the dimension group an item is related to:

Conclusion

With the experience I have had with Inventory II, I can truly recommend this solution. I would never go ahead to implement a high volume/transaction customer without inventory II, because I have truly felt the pain without it. The support is very good, and help is always near if you need it. The question is how will this change in AX 2012. As far as I have seen and heard, the inventory II features are still needed in AX 2012, and I know that the FSB guy’s are working on an AX 2012 build. I look forward test it out.