Background
The way a new inventory dimension is added in AX has not changed significantly for several releases. Even for AX 7 the best guide is still chapter 6 in Inside Dynamics AX 4.
In AX 2012 we almost hit a SQL limitation on the maximum number of fields in the DimIdx index (the limit is 16) – preventing you from adding more dimensions. To address this, we added a hashing function in R2 to ensure uniqueness – and a configuration key (InventDimExtensibility) to enable/disable the overhead of hashing. This is described here: Walkthrough: More than 14 InventDim Dimensions Despite Index Limit [AX 2012] and Avoid index length issues with InventDim
In AX 2012 R3 the limit is exceeded out of the box. This means that everyone running warehouse was paying the hashing overhead – measured to be about 10% performance degradation. To compensate, we wrote a guide on how to avoid the hashing by disabling other inventory dimensions not needed, like serial numbers or Russian dimensions. This required code changes and thus increased TCO to gain performance. The guide is available here: Microsoft Dynamics AX R3 – New Warehouse Management solutions impact on InventDim extensibility and migration scenarios
What changed in AX7?
As you can probably see from above, the InventDim story has grown quite complex. In AX7 this stopped. The solution is simpler and faster.
Issue 1 – Performance
In AX 2012 the hashing value is stored in a container. Container types are relatively expensive is SQL, as they are stored in a column of type varbinary(100). The container type in X++ is also quite expensive, as there is no native IL representation for it. Every reference results in an interop call to the native platform. (aka. Speed of sound).
Solution
Store the hash in a string instead – hex encoded. 40 characters is enough to contain the hash. String is a native type in IL – much much faster than containers. In SQL the type for the hashing column became nvarchar(40) – also a bit lighter than varbinary(100).
Issue 2 – Complexity
In AX 2012 R3 you need to consider the max number of fields in an index – if you don’t need all the dimensions provided out-of-the-box, you should reshuffle the index to avoid enabling the InventDimExtensibility configuration key. If you truly needed all of the dimensions you paid the performance overhead.
Solution
Simply just hash all the dimensions. This allows us to deprecated the InventDimExtensibility configuration key. In AX7 all code runs as IL – the price of hashing is insignificant. You no longer need to even consider reshuffling indexes or hashes. In other words, we deleted a bunch of code – and you can focus on more valuable things.
Issue 3 – SQL footprint
In AX 2012 each dimension was stored 3 times in SQL. One time for the actual column, one time in an index for the dimension, and one time in the big index ensuring uniqueness across dimensions.
Solution
Given the hash string is enough to guarantee uniqueness, we don’t need the big index anymore – we just need a unique index on the hash string. Now, the index guaranteeing uniqueness only contains 3 fields: PartionId, DataAreaId and SHA1HashHex. This reduced the SQL footprint from 2kb to 1.3kb per row – or about 30%.
Did it help?
Yes, the performance improved significantly. The chart below compares the performance of the InventDim::findOrCreate() method in AX7 before (with InventDimExtensibility ON/OFF) and after this change. Even when always hashing all dimensions we are 40% faster compared to the old system with no hashing.
And, you don’t have to change the InventDim table again – unless you are adding a new dimension.
When adding a new dimension(s) you must:
-
Include them in the hashKey() method on the InventDim table. Doing so is fairly simple, and it doesn’t require rehashing of existing records. Just include the following lines for each new dimension:
str valueMyNewDimension = strRTrim(this.MyNewDimension);
if (valueMyNewDimension)
{hashKey += ‘MyNewDimension:’ + valueMyNewDimension + ‘~’;
} - Consider if you have the correct covering indexes. If not, make sure to create the index(es) you need. Usually, a non-unique index per dimension will satisfy requirements.
THIS POST APPLIES TO MICROSOFT DYNAMICS AX7 TECHNICAL PREVIEW; IS PROVIDED AS-IS AND CONFERS NO RIGHTS.