In Microsoft Dynamics AX we use indexes to ensure uniqueness.
An example of this is the DimIdx index in InventDim, where all dimension fields are included to ensure that only a single record exists for a given set of dimensions.
However, the SQL Server has a limitation of 16 fields in an index.
With the first versions of InventDim (which was introduced in Axapta 2.0) this was rarely a problem since only a few dimensions where shipped out-of-the-box.
So partners and customers could still add more dimensions. Over time, numerous dimensions have been added in the standard version and in country specific solutions.
As a result, we are now very close to the 16 field limit. With the Dynamics AX2012 Feature Pack and Russian functionality installed we have 14 fields in the index and more to come in future releases.
We have received an increasing amount of questions about how to get around this issue so we have implemented a solution which allows you to add custom inventory dimensions and still have the uniqueness validated and enforced.
In our investigation of possible solutions we have tried to find a balance between simplicity, maintainability, and performance.
What we found to be the best solution can be described as follows:
– Introduce a new field based on the Sha1HashCode data type.
– Remove some of the least used dimensions from the DimIdx index and add the new field instead. The removed fields must be hashed in the new field during the insertion.
– Calculate the hash and use it in the findDim method when looking for an existing dimension.
– Create a new InventDimIdAllDimensions field in the InventSumDeltaDim table (I guess the name says what it contains) and replace all the dimension fields in the TTSItemCheckDimIdx index with this new field.
This solution will be available in an upcoming release of Microsoft Dynamics AX so if you cannot wait for this release, feel free to be inspired by our findings – hopefully it will also make it easier for you to upgrade your data.
转载请注明：ww12345678 的部落格 | AX Helper » Avoid index length issues with InventDim