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

}

}

}

 

Advertisements

One thought on “AX4 upgrade to AX2012 – Financial Dimensions

  1. Nick says:

    Great post. I have a client with an upgrade project in progress. We had to rewrite 6 of the scripts in the AX2012 R3 upgrade processing batch jobs in a similar manner. Unfortunately in many cases the OTB scripts written by MS are not up to the task of processing a reasonable amount of data in a reasonable amount of time.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s