InventDim in AX7


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:

  1. 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 + ‘~’;  
    }  

     

  2. 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.


Comments (5)

  1. msmfp says:

    Thanks Logger, that is clever thinking!   I can see that we could save additional 60 bytes per row.  As per the blog post, the row size right now is about 1300 bytes – so this would be a 5% reduction.  And SQL would handle the binary compare better than case insensitive compare.

    On the other hand there is an extra cost when adding field to indexes (Even if the overall footprint is reduced) – the actual gain would need to be measured.

    I'll keep this optimization in mind, for the next time we make changes here.  

  2. Logger says:

    May be, it would be better not to have index

    SHA1HashHex, dataareaid, partitionid – 96 byte.

    but create index

    SHA1HashIntGuid, SHA1HashInt32, dataareaid, partitionid – 36 byte

    where

    SHA1HashIntGuid – the field of type guid, storing first 16 bit of hash

    SHA1HashInt32 – the field of type int32, storing the rest of hash (4 bit)

    So, field SHA1HashHex can be removed. (Replaced with SHA1HashIntGuid, SHA1HashInt32)

    It will save 62% of disk space, spent for store index keys.

  3. Logger says:

    Thanks Michael,

    >>MD5 has been cryptographically compromised, so it is blacklisted for any use at Microsof

    So, all rules have exceptions 🙂

    ax2012

    ClassesRLedgerTurnoverParamHashKeygetHash

  4. msmfp says:

    Thanks Denis,

    The reason was really path-of-least-resistance, and not technical.  MD5 has been cryptographically compromised, so it is blacklisted for any use at Microsoft.   Now you can argue, that the use here is not a security feature – yet Microsoft's API scanners cannot distinguish. Using MD5 the way you suggest would also require changes to the AX platform’s data stack. Currently it is not possible to model what you suggest in the AOT.

    Just for the reference, we also considered using Base64 to encode the hash into a string. It would save 12 characters (varchar(28) would work), but require a case insensitive comparison that is not supported by the AX platform's data stack either.

    The main alternative hashing function we considered was SHA256 – but decided to stay with SHA1 (for now).  

  5. Denis says:

    What was the reason not to use MD5 hash function instead of SHA1? It gives 128-bit (16-byte) value that perfectly fits into T-SQL uniqueidentifier data type and X++ GUID data type, thus SQL Server can use binary 16-byte keys for InventDim records instead of varbinary(100) (SHA1 value packed into X++ container) and instead of nvarchar(40) (SHA1 value hex string representation) with case-insensitive comparison and related performance overhead. Besides, MD5 calculation is a bit faster then SHA1 for small amounts of data such as InventDim.hashKey()

Skip to main content