Rebuild balances in AX 2012 – Do you have a minute? Or more….
Compared to previous versions of Microsoft Dynamics AX, the implementation of balances has been redesigned in AX 2012 to improve performance. Updating the balances is working correct in many scenarios. However some scenarios will still cause wait times on the rebuild or update of the balances or even… database blocks. This post will inform you about the possible problems and how you could prevent them.
Preface
In AX 2012 a new Financial dimension framework has been introduced. Setting up Main accounts and related dimensions are now flexible and unlimited. Be careful with the word ‘unlimited’. Yes you can define anything you like, but with e.g. 20 active dimensions the data entry will be unwieldy. Also if you want to create all kinds of Dimension sets, there are more balances which needs to be maintained.
The Dimension sets in Microsoft Dynamics AX are sets which can be setup to hold balances for a combination of several dimensions. There is one mandatory Dimension set consisting of only the main account. Further you can create set to have reporting options based on e.g.:
- Main account – Department – Cost center
- Main account – Cost center
- Cost center
- Department – Cost center
- Etcetera…
A table Dimension set balance (DimensionFocusBalance) is a global table and stores all balances for all Dimension sets in all legal entities. A field Ledger has the reference for the legal entity. When you create a Dimension set, you need to initialize balances. After this initialization the balances are maintained by updates. When a new accounting transaction is created, a record will be inserted in a table, stating the balance should be updated apart from the posting itself. Here there is a performance gain as during posting there will be no blocks to update the balances.
The update of balances is performed using a batch job. Also when you open the Main account list page, Trial balance list page, Detailed trial balance or Summary trial balance report the update of balances is executed prior to show the data. Note that earlier versions of AX 2012 did have an option to keep the balances updated automatically. This has been removed as of R2 and in a cumulative update for the initial version of AX 2012.
What is the possible problem?
If you read the preface, theoretically everything looks to be a perfect world, however there are some scenarios where you can encounter system slowness or even database blocks.
Very large number of accounting transactions
When you have a very high number of ledger accounting transactions, opening of e.g. the Main account list page can take ages. I have seen an issue where it took about 50 minutes to open this list page. Closing and reopen again took about 10 minutes. This was due to not having the balances update batch job running periodically. As this customer had really exceptional high number of transactions, also this was still delaying opening the list pages and reports mentioned earlier. In this scenario a customization was done to prevent updating the balances when starting these objects, depending on a batch running every minute. Note that the information then was not up to date, but when they had opened the form, due to the number of transactions, the information was already outdated.
Rebuild balances
When you want to rebuild balances, per dimension set it will delete the balances from all legal entities and rebuild them. When you have many legal entities and/or transactions this process might take some time and during the process the dimension balances table is locked. When another user wants to open e.g. the main accounts list page, this person will experience a non-responding form, thinking his session is dead. The reason is that it will first try to update balances which is blocked by the rebuilding of the balances. It would be recommended to schedule the rebuilding of the balances in batch and run it on a time the system is free from active users who needs to open the main accounts or run trial balance reports.
Saying that, you must know that it is not always possible to control the time of rebuilding the balances. There are some tasks in the application where a rebuild of the balances will be run automatically. However you can control if it will be executed or not. The fiscal year close process Opening transactions and the Consolidate [online] are processes that are able to delete accounting entries, therefor requiring the rebuilding of balances. Both processes can be executed in batch and so you can control the time of the day when this process and the related rebuilding of transactions are executed.
Opening transactions
Let’s start with some additional information related to the Opening transactions. If you run this process on the client, after it finishes, it will create a batch task for rebuilding the balances for all dimension sets active in the legal entity. This task will be executed as soon as possible. You can’t specify another date/time to start this process. The good news is that the rebuilding is done on a subset of the balances; only for the current legal entity. But when you have a lot of transactions and also many different dimension sets, this could also block other users. There is a General ledger parameter which controls if year-end transactions are deleted if you run the Opening transactions multiple times for the same fiscal year after doing changes. This parameter has the name Delete close-of-year transactions during transfer. If this is enabled, it will run the rebuild of balances. If it is turned off, only new appending opening transactions will be created and the rebuilding is not required, thus not executed. This parameter can solve some waiting times when you encounter issues during this process.
Consolidation
The Consolidation process has a parameter on the dialog if you want to rebuild balances during this process. The tick box Recalculate balances during consolidation process can be disabled to prevent rebuilding the balances. If it is enabled, for all dimension sets active it will run the rebuild of the balances. Contradictory of the Opening transactions the rebuild is not placed in a separate batch task. When you rerun a consolidation for a period which was already consolidated, the rebuild of balances is required. So this has to be done to ensure correct data on the financial reports. You can also delete a previous Consolidation in the inquiry form Consolidation. This task also triggers a complete rebuild of the balances.
Using the consolidation process at one customer we encountered huge performance issues as the consolidation was executed and multiple other persons tried to open or the main account list page or the trial balances. It appeared to have a database block caused by the rebuild balances. After deep investigation it was found that the recalculation of balances was triggered for all legal entities instead of the only the current consolidation company. The block duration was over one hour. Also the duration running the task in batch when the system was idle was about one hour. This customer is using AX 2012 R2 with CU7 and has a large number of legal entities. We found a difference between R3 with CU8 and the version running at the customer and also a hotfix which would restrict the rebuild to only the current consolidation company. After applying the fix the duration of the complete consolidation including rebuilding balances went back to 2 minutes.
This hotfix has number KB2979922 and is applicable for all AX 2012 versions. Details and download can be found on Lifecycle services – Issue search.
Conclusion
- Prevent too many dimension sets
- Try to plan rebuild of balances during idle hours
- Install the hotfix KB2979922
There is more
Also a Russian feature is executing the rebuild of balances together with a localized process. I didn’t have a version with the Russian features enabled to find some details on the process itself.
When you still encounter performance issues you can have a look if customizing the rebuild of balances to handle separate accounting years to limit the number of transactions included would be feasible.
That’s all for now. Till next time!
If you are running “update balances” job periodically, do you need to also schedule a “rebuild balances” job? Or only need to “rebuild” balances occasionally or if there is a problem?
Hi Chris,
You need to use the rebuild only when there are problems encountered. The update balances is an incremental update with the latest transactions. A rebuild will first delete records and then fill the balances from scratch. With a large number of transactions, this could take some time.
Thanks for your information, Help us with a rebuild consolidation recalc.
Hi Andre,
regarding Delete close-of-year transactions during transfer when you say:
This parameter can solve some waiting times when you encounter issues during this process.
you mean that switching it off could solve some issues?
thanks
Michele
Hi Michele,
Switching the parameter off will definitely speed up the process. However, this comes with a price. It will then create additional year-end transactions next to the previous runs. If you need to have just one summarized transaction, you have to enable it and expect a longer duration. The additional time required for rebuilding the balances is based on the usage in your environment.
kind regards, André