Walkthrough: More than 14 InventDim Dimensions Despite Index Limit

  • Article

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2

This article concerns the InventDim table. It describes how you can overcome the limit of 14 dimension fields on your inventory items. The following solution shows that, for each record, the values of all extra dimension fields can be combined into one hash value that is stored in one hash field. The hash field is included as the last field in the InventDim.DimIdx table index.

This article uses the terms of primary versus secondary dimensions. Conceptually a primary dimension is more important to your business than a secondary dimension. In the technical implementation, the field for a primary dimension is directly included in the DimIdx index. The field value for a secondary dimension is an input into the hashing algorithm that computes the value for a hash field. The hash field is the last field in the DimIdx index. The field for a secondary dimension is not in DimIdx in any other way.

The solution that is described here first became available in Microsoft Dynamics AX 2012 R2.

Elements in the Solution

The following table describes the elements that work together to create the solution. The elements include AOT items such as indexes, fields, methods, and more.

After the following table, the subsequent section explains the steps you can follow to implement the solution for your own extra dimensions. The steps show how the elements of the solution work together.

Element name

Element type

Description

InventDim

Table

The InventDim table contains values for inventory dimensions. All dimension fields must be represented in the DimIdx index, which is a unique index. Only the primary key field, named inventDimId, is excluded from DimIdx.

Some dimension fields are represented in DimIdx in the usual direct manner. However, if there are more than 14 dimension fields, those of secondary importance are represented in the SHA1Hash field. Multiple secondary dimensions can be represented in the single SHA1Hash field.

The SHA1Hash field is included in the DimIdx index.

DimIdx

Table index

Here are two views of the dimensions index on the InventDim table. This index forbids duplicate values. The underlying SQL Server index is limited to a maximum of 16 fields. The field SHA1Hash is included in DimIdx, as the last field in the index.

DimIdx when viewed with the AOT: The SHA1Hash at the end of InventDim.DimIx. SHA1Hash is the last field inthe InventDim.DimIdx index.
DimIdx when viewed as the underlying T-SQL. Notice the two additional fields at the start, namely PARTITION and DATAAREAID: 
CREATE UNIQUE NONCLUSTERED INDEX
 [I_698DIMIDX] ON [dbo].[INVENTDIM]
(
 [PARTITION]        ,[DATAAREAID]
,[CONFIGID]         ,[INVENTSIZEID]
,[INVENTCOLORID]    ,[INVENTSTYLEID]
,[INVENTSITEID]     ,[INVENTLOCATIONID]
,[INVENTBATCHID]    ,[WMSLOCATIONID]
,[WMSPALLETID]      ,[INVENTSERIALID]
,[INVENTGTDID_RU]   ,[INVENTPROFILEID_RU]
,[INVENTOWNERID_RU] ,[SHA1HASH]
)   WITH ...   -- (AX 2012 R2)

Sha1HashCode

System data type

The type that represents a hash value in the AX system. This type can be used for a table field, or for a variable in a method, and so on just as any other type can be used.

The extended data type (EDT) named InventDimSHA1Hash, in its Extends property, references this system data type.

For more information see AOT > System Documentation > Types.

InventDimSHA1Hash

Extended data type (EDT)

A type that extends the system data type named Sha1HashCode.

On the InventDim table, the hash field named SHA1Hash is of this type.

For more information, see AOT > Data Dictionary > Extended Data Types.

SHA1Hash

Table field

A field on the InventDim table, of type InventDimSHA1Hash.

This field stores the hashed values of additional InventDim fields above the 14 field limit. The hashValue method computes the value that is then stored in the SHA1Hash field.

hashKey

Table method

A method on the InventDim table.

This method is called by the hashValue method. The hashKey method returns a string containing the values of the additional InventDim fields above the 14 field limit.

Partners whose customizations need more than 14 dimensions in the InventDim table must edit this method to add their new field names. The edit must be similar to the following X++ code which shows a pretend dimension field named FluffinessQuotientId. The field is added near the end of the hashKey method:

public str hashKey()   // X++, method on InventDim table.
{
    str     hashKey = '';
    #InventDimDevelop
    /** (Large comment removed for brevity.)
    ... using the code pattern below for each field
    included in the hash.
    if (this.<FieldName>)
    {
        hashKey += (hashKey ? '~' : '')
            + '<FieldName>:' + this.<FieldName>;
    }
    **/
    // Due to index limitations, hash the value of
    // the PRETEND EXAMPLE extra "dimension field".
    //
    if (this.FluffinessQuotientId)
    {
        hashKey += (hashKey ? '~' : '')
            + 'FluffinessQuotientId:' + this.FluffinessQuotientId;
    }
    return hashKey;
}

hashValue

Table method

A method on the InventDim table.

This method is called by the insert and update methods.

This method calls the hashKey method.

The value returned by this method is very likely to be unique for any given set of unique inputs. The value is stored in the SHA1Hash field.

insert

Table method

A method that is inherited by each table, and which is overridden on the InventDim table.

This method calls the hashValue method, and then stores the result in the SAH1Hash field.

update

Table method

A method that is inherited by each table, and which is overridden on the InventDim table.

This method calls the hashValue method, and then stores the result in the SAH1Hash field.

findDim

Table method

A static method on the InventDim table. The method is used to retrieve a record from the InventDim table.

The findDim method has been updated to use the hashing infrastructure described in this document. Therefore any new fields added to the hashed field collection are used to find matching InventDim records.

Whenever you modify the schema of dimension fields on the InventDim table, you must modify the findDim method to concur with the modification. Any new dimensions that you add to the hashKey, what we call secondary dimensions, have no effect on the code in the findDim method. But if you add a primary dimension field to the InventDim table, you must also add the field to both long lists of dimensions that are in the code of the findDim method. For example, in the following X++ code, the pretend dimension field named FluffinexxQuotientId has been added as a primary.

client server static public InventDim findDim(
    InventDim   _inventDim,
    boolean     _forupdate = false)
{
    // <GEERU>
    #ISOCountryRegionCodes
    // </GEERU>
    InventDim       inventDim;
    if (_forupdate)
    {
        inventDim.selectForUpdate(_forupdate);
    }
    // Fields might not have been selected on the specified
    // buffers, or might have been updated since selection
    _inventDim.checkInvalidFieldAccess(false);
    if (isConfigurationkeyEnabled(configurationKeyNum(InventDimExtensibility)))
    {
        select firstonly inventDim
        where inventDim.ConfigId            == _inventDim.ConfigId
           && inventDim.InventSizeId        == _inventDim.InventSizeId
           && inventDim.InventColorId       == _inventDim.InventColorId
           && inventDim.InventStyleId       == _inventDim.InventStyleId
           && inventDim.InventSiteId        == _inventDim.InventSiteId
           && inventDim.InventLocationId    == _inventDim.InventLocationId
           && inventDim.InventBatchId       == _inventDim.InventBatchId
           && inventDim.wmsLocationId       == _inventDim.wmsLocationId
           && inventDim.wmsPalletId         == _inventDim.wmsPalletId
           // Pretend example added primary dimension.
           && inventDim.FluffinessQuotientId ==  _inventDim.FluffinessQuotientId
           && inventDim.sha1Hash            == _inventDim.hashValue()
           // Needed to hit unique index cache. All dimensions should
           // be included in the where clause - also those included
           // in the hash key,
           && inventDim.InventSerialId      == _inventDim.InventSerialId
           && inventDim.InventGtdId_RU      == _inventDim.InventGtdId_RU
           && inventDim.InventProfileId_RU  == _inventDim.InventProfileId_RU
           // Demoting InventOwnerId_RU to secondary, commenting it out.
           //&& inventDim.InventOwnerId_RU    == _inventDim.InventOwnerId_RU;
    }
    else
    {
        select firstonly inventDim
            where inventDim.ConfigId         == _inventDim.ConfigId
               && inventDim.InventSizeId     == _inventDim.InventSizeId
               && inventDim.InventColorId    == _inventDim.InventColorId
               && inventDim.InventStyleId    == _inventDim.InventStyleId
               && inventDim.InventSiteId     == _inventDim.InventSiteId
               && inventDim.InventLocationId == _inventDim.InventLocationId
               && inventDim.InventBatchId    == _inventDim.InventBatchId
               && inventDim.wmsLocationId    == _inventDim.wmsLocationId
               && inventDim.wmsPalletId      == _inventDim.wmsPalletId
               // Pretend example of added primary dimension.
               && inventDim.FluffinessQuotientId == _inventDim.FluffinessQuotientId
               // <GEERU>
               && inventDim.InventGtdId_RU      == _inventDim.InventGtdId_RU
               && inventDim.InventProfileId_RU  == _inventDim.InventProfileId_RU
               // Demoting InventOwnerId_RU to secondary, commenting it out.
               //&& inventDim.InventOwnerId_RU    == _inventDim.InventOwnerId_RU
               // </GEERU>
               && inventDim.InventSerialId   == _inventDim.InventSerialId;
    }
    #inventDimDevelop
    return inventDim;
}

Steps for the Solution

First plan, then implement.

Dn495386.collapse_all(en-us,AX.60).gifPlan

It is simpler to add a secondary dimension. For the following steps it is assumed that you want the new dimension to be a primary dimension, and that it must displace another dimension that has been primary until now.

  1. Decide whether you want your new dimension field to be among the 14 most important, or instead one of secondary importance.
    For this example you add a primary dimension field that you name FluffinessQuotientId.

  2. Decide which primary dimension field you are demoting to secondary.
    For this example you demote the InventOwnerId_RU field.

Dn495386.collapse_all(en-us,AX.60).gifImplement

  1. Add your new dimension field named FluffinessQuotientId to the InventDim table.

  2. Edit the InventDim.hashKey method to add another if(){} block for the secondary dimension field, which in this example is the InventOwnerId_RU field.
    Simply match the pattern that is documented by comments in the hashKey method. Also, the example code in the earlier table, in the row for hashKey, shows the code that you must add for the FluffinessQuotientId field.

  3. Modify the InventDim.DimIdx index to remove the dimension field InventOwnerId_RU which you are demoting to secondary.

    For information about indexes in the AOT, see How to: Create an Index.

  4. Further modify the DimIdx index to add your new primary dimension field FluffinessQuotientId.

  5. Update the InventDim::findDim method to add your new dimension FluffinessQuotientId to the select statement.
    The necessary code is shown in the earlier table, in the row for findDim.

  6. Also in findDim, remove the InventOwnerId_RU field. This field now affects the value returned by the hashValue method.

  7. Modify any SQL statements whose speed performance relies on the demoted field InventOwnerId_RU being a primary field of the DimIdx index.
     
    The cross-reference feature can tell you where a given field is referenced in X++ SQL code anywhere in your AX system. For more information, see How to: Create the Cross-Reference System.
     
    Or consider creating a new index for the secondary dimension field.

Cautions and Considerations

  • If your system needs exactly 14 dimensions, you can consider removing the SHA1Hash field from the DimIdx index. Later if you need a total of 15 or more dimensions, you would have to add the SHA1Hash field back as the last field in the DimIdx index.

  • Any X++ SQL statements which insert or update data in the InventDim table, but which avoid the insert and update methods on the InventDim table, will corrupt the data in the computed SHA1Hash field.

  • For better performance, it might be good to add another index for each secondary dimension field. The details depend on your particular implementation.

  • No set-based SQL operations can be used for insert or update of the InventDim table. The insert and update methods on the InventDim table handle only single-record operations.

  • To see an example of a form that might display some dimension related data, view the following form:

          Form: InventJournalTable,
          at Menu Item: InventJournalTableMovement,
          at path: Inventory and warehouse management > Journals > Item transactions > *

See also

About inventory dimensions and dimension groups

Create and maintain product dimensions

InventDim Table

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.