AX4 upgrade to AX2012 – Financial Dimensions

During a recent client upgrade from AX4 to AX2012, had a problem with performance with the upgrade wizard. On the Prepare Application for Preprocessing at the Prepare financial dimension framework step the bottom of the screen, dimension numbers, took 20+ minutes when pressing the upgrade validate button.

The client had 300+ companies, each with many financial dimensions, which resulted in building 180,000+ records in the migration tables. When reviewing the code that builds this information, class LedgerFrameworkSetupHelper, method setupDimensionMergeTable, the records were being read and inserted one at a time, and not using the bulk insert controls. this causes high load on SQL and is also very inefficient.

Changing the code to use the RecordInsertList so records could be bulk inserted, reduced load time by 94% to just over 1 minutes. This means the SQL server was much more efficiently being used, and the time spent waiting for data to be loaded significantly reduced.

So if you are planning to do an upgrade from AX4 to AX2012, and have lots of companies, then consider modify the code to use the bulk insert commands.

Class – LedgerFrameworkSetupHelper::setupDimensionMergeTable

/// <summary>

/// Creates the necessary DimensionMergeSelection records for the Dimensions upgrade.

/// </summary>

public static void setupDimensionMergeTable(DEL_PartitionKey _partitionKey)

{

    Dimensions                                     dimensions;

    DimensionMergeSelection             dimensionMergeSelection_join;

    DimensionMergeSelection             dimensionMergeSelection_add;

    DimensionMergeSelectionDimensions   selectionDimensions;

    DataArea                                         dataArea;

    DEL_ReleaseUpdatePartitionsMapping  partitionsMapping;

    DEL_PartitionRecId                       partitionRecId;

    //New code *start

    RecordInsertList    DimensionMergeSelectionList;

    RecordInsertList    DimensionMergeSelectionDimensionsList;

    //New code *end

    ;

    partitionRecId = DEL_ReleaseUpdatePartitions::findByPartitionKey(_partitionKey).RecId;

    // Clear out the DimensionMergeSelectionDimensions table

    delete_from selectionDimensions;

    // Populate the tables with the Dimensions data from all companies.

    while select id from dataArea

        where dataArea.isVirtual == NoYes::No

        join partitionsMapping

            where partitionsMapping.CompanyId == dataArea.id &&

                partitionsMapping.PartitionKey == _partitionKey

    {

        changecompany(dataArea.id)

        {

            //New code *start

            DimensionMergeSelectionList = new RecordInsertList(tablenum(DimensionMergeSelection));

            DimensionMergeSelectionDimensionsList = new RecordInsertList(tablenum(DimensionMergeSelectionDimensions));

            //New code *end

            dimensions = null;

            dimensionMergeSelection_join = null;

            while select DimensionCode, Num, RecId, RecVersion from dimensions

                notexists join dimensionMergeSelection_join

                    where dimensions.DimensionCode == dimensionMergeSelection_join.DimensionCode &&

                        dimensions.Num == dimensionMergeSelection_join.Num

                        && dimensionMergeSelection_join.Partition == partitionRecId

            {

                dimensionMergeSelection_add.clear();

                dimensionMergeSelection_add.DimensionCode = dimensions.DimensionCode;

                dimensionMergeSelection_add.Num = dimensions.Num;

                dimensionMergeSelection_add.Error = DimensionMergeErrorType::DuplicateError;

                dimensionMergeSelection_add.Partition = partitionRecId;

                //New code *start

                //dimensionMergeSelection_add.insert();

                DimensionMergeSelectionList.add(dimensionMergeSelection_add);

                //New code *end

            }

            //New code *start

            DimensionMergeSelectionList.insertDatabase();

            //New code *end

            while select DimensionCode, Num, Description, InCharge, CompanyGroup, Closed, RecId, recVersion, dataAreaId from dimensions

            {

                selectionDimensions.clear();

                selectionDimensions.DimensionCode = dimensions.DimensionCode;

                selectionDimensions.Num = dimensions.Num;

                selectionDimensions.Description = dimensions.Description;

                selectionDimensions.InCharge = dimensions.InCharge;

                selectionDimensions.CompanyGroup = dimensions.CompanyGroup;

                selectionDimensions.Closed = dimensions.Closed;

                selectionDimensions.RefRecId = dimensions.RecId;

                selectionDimensions.RefRecVersion = dimensions.recVersion;

                selectionDimensions.RefDataAreaId = dataArea.id;

                //New code *start

                //selectionDimensions.insert();

                DimensionMergeSelectionDimensionsList.add(selectionDimensions);

                //New code *end

            }

            //New code *start

            DimensionMergeSelectionDimensionsList.insertDatabase();

            //New code *end

        }

    }

}