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.

Continue reading

SQL block size

Getting the most performance out of SQL server is always important when using DAX. As a result I constantly read the SQL server articles looking to ensure everything has been considered.

SQL server reads and writes its data and log files in 64K blocks, so using a 64K block size for the hard drive format seems to make sense. But what effect will this have?

Continue reading

BI cube deployment language issue

Encountered an unexpected issue when deploying the standard cubes. During the deployment the only language selected was ‘en-gb’, everything deployed without any problems. However when you run some of the procurement reports (example Reports->Statistics->Supplier->Purchase in the past periods), they error saying ‘Accounts payable cube’ is not present or processed.

The problem is caused because of user default language in DAX was set to ‘en-gb’ causing the cube names to load with the British English wording rather than the US English. For the VendCube this would mean US language would be ‘Accounts payable cube’ while in British English would be ‘Purchase ledger cube’. The example report is hard coded to look for ‘Accounts payable cube’.

So when releasing the standard cubes, and to use them within the standard reports, you must ensure the deployer’s DAX language is set to -en-us’ to ensure the cubes will work with existing reports!

Monitor Database Growth

It is important to monitor your database growth, both for system maintenance, but also to plan for long term system resource requirements and potential performance issues.

There is no out-of-the-box way to show the growth information in SQL so you need to store some information on a periodic basis to be able to perform the analysis. This can be achieved with the following scripts:

Continue reading