Data upgrade from Ax 2012 to Dynamics 365 Finance and Operations. Migrate inventory dimensions.

Recently one of the customers asked me if a data migration from Ax 2012 to the latest finance and operations version is possible. The Ax 2012 solution has additional inventory dimensions that migrated to the D365 from a code perspective. While investigating the issue I faced some issues and I would like to share my experience regarding inventory dimensions data upgrade. I hope it will save your time.

The documentation regarding the data upgrade process is pretty clear and there is a separate page related to the troubleshooting upgrade scripts experience.

During the data migration in development environment, I faced the same error as mentioned on the troubleshooting page:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.INVENTDIM' and the index name 'I_698SHA1HASHIDX'. The duplicate key value is (5637144576, USMF, B92BAF2504FD67FC15A56F2DFACE09127715B54B). The statement has been terminated. CREATE UNIQUE INDEX I_698SHA1HASHIDX ON DBO.INVENTDIM(PARTITION,DATAAREAID,SHA1HASHHEX) WITH (MAXDOP = 1)

It was unexpected since a special inventory dimension data migration script was developed. When I run the recommended query to verify the script, this is what appears:

SELECT PARTITION, DATAAREAID, SHA1HASHHEX, COUNT(*)

FROM INVENTDIM

GROUP BY PARTITION, DATAAREAID, SHA1HASHHEX

HAVING COUNT(*) > 1

The query returned many records. In fact, it means that the SHA1HASHHEX field values were not generated as unique. In my opinion, there can be two reasons except those described in the documentation: The values of InventDimension* fields are not taken into consideration with the ReleaseUpdateDB72_Invent::updateSHA1HashHexInInventDim method or the InventDimension* fields are empty.

In my case, the reason was that the appropriate configuration keys InventDimension* are disabled in the system by default. Even if I violated the system and copied the data from Ax 2012 inventory dimensions fields to the appropriate InventDimension* fields via direct SQL, the script still looks like this:

[

  UpgradeScriptDescription("Script description"),

  UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),

  UpgradeScriptType(ReleaseUpdateScriptType::StandardScript),

  UpgradeScriptTable(tableStr(InventDim), false, true, true, false)

]

public void updateInventDimensionField()

{

    FieldName     field2012Name  = 'YOURDIMENSIONFIELD';

  FieldName     field365Name   = 'INVENTDIMENSION1';

  SysDictTable  inventDimDT    = SysDictTable::newTableId(tableNum(InventDim));

  TableName     inventDimName  = inventDimDT.name(DbBackend::Sql);

  str sqlStatement = strFmt(@"

                UPDATE [dbo].[%1]

                SET [dbo].[%1].[%2] = [dbo].[%1].[%3]

                WHERE [dbo].[%1].[%3] <> ''",

                inventDimName,

                field365Name,

                field2012Name);

   

  ReleaseUpdateDB::statementExeUpdate(sqlStatement);

}

The system did not use its values with the generate hash values method and the above-mentioned synchronization issue appeared.

In order to fix this issue, the required configuration keys should be activated during the data upgrade. In order to perform it, the following method can be used:

[

  UpgradeScriptDescription("Enable inventory dimension configuration keys"),

  UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),

  UpgradeScriptType(ReleaseUpdateScriptType::StartScript),

    Microsoft.Dynamics.BusinessPlatform.SharedTypes.InternalUseOnlyAttribute

]

public void enablePreSyncInventDimensionConfigurationKeys()

{

  ConfigurationKeySet keySet = new ConfigurationKeySet();

  SysGlobalCache      cache = appl.globalCache();

  keySet.loadSystemSetup();

  keySet.enabled(configurationKeyNum(InventDimension1), true);

  keySet.enabled(configurationKeyNum(InventDimension2), true);

  SysDictConfigurationKey::save(keySet.pack());

  SysSecurity::reload(true, true, true, false, false);

}

When the above- mentioned method is used and a data transfer between Ax 2012 and D365 InventDim fields is completed (It should be done at the PreSync step, otherwise the Ax 2012 InventDim custom fields will be deleted at the DbSync step), the ReleaseUpdateDB72_Invent::updateSHA1HashHexInInventDim method should generate hash values correctly and the synchronization issue should be resolved.

Additionally, I recommend updating other tables that contain InventDim field ids from Ax2012 version since InventDimension* fields in D365 have other field id values. I believe every system may have various setups and customizations. Therefore, from my point of view, it make sense to develop scripts for updating setups and the field id values of the InventDim table in the tables listed below:

• WHSReservationHierarchyElement
• EcoResStorageDimensionGroupFldSetup
• EcoResTrackingDimensionGroupFldSetup   
• EcoResProductDimensionGroupFldSetup 

The correct values in the InventDim table and in the tables listed above, as well as the enabled configuration keys, should help to avoid problems with data WHS* tables within the upgrade process.

Finally, if you see any of these errors after the data migration:

DECLARE @AVAILPHYSICAL numeric(32, 6) DECLARE @AVAILORDERED numeric(32, 6); EXECUTE WHSOnHandWithInventDim @DATAAREAID = N'USMF', @PARTITION = 5637144576, @ITEMID = N'ITEMIDXXX', @LEVEL = 6, @UPPERLEVELOFMINIMUMCALCULATION = 0, @INVENTSITEID = N'1', @INVENTSITEIDLEVEL = 1, @INVENTLOCATIONID = N'16', @INVENTLOCATIONIDLEVEL = 2, @INVENTSTATUSIDLEVEL = 3, @INVENTBATCHIDLEVEL = 4, @INVENTDIMENSION1 = N'DIMENSIONVALUE', @INVENTDIMENSION1LEVEL = 5, @WMSLOCATIONID = N'12', @WMSLOCATIONIDLEVEL = 6, @AVAILPHYSICAL = @AVAILPHYSICAL output, @AVAILORDERED = @AVAILORDERED output ; SELECT @AVAILPHYSICAL as [AVAILPHYSICAL], @AVAILORDERED as [AVAILORDERED];

Microsoft][ODBC Driver 17 for SQL Server][SQL Server]@INVENTDIMENSION1 is not a parameter for procedure WHSOnHandWithInventDim.

It means the WHSOnHandWithInventDim and WHSOnHandWithDelta procedures were not adjusted to the current dimension configuration setups. It could happen if at the final database synchronization step during the data migration process the required configuration keys were not enabled. In order to fix this error, you can run the code via a job:

WHSOnHandSPHelper::syncDBStoredProcedures();

It should align the mentioned stored procedures to the current activated InventDimension* configuration keys and the errors should no longer be.