Monday, 26 May 2008

In search of Performance (Finance this Time)

I have not noticed when exactly the change was made but when posting to the LedgerBalanceTrans and LedgerBalanceDimTrans the date is no longer always the first or last day of a financial period it is the equivalent of the Transaction date. Validated from V3 onwards.

This has important performance impacts on reporting when you use financial dimensions actively.

Normally you would expect to see a factor of reduction in number of transactions going from LedgerTrans to LedgerBalanceTrans and DimTrans, in practical use I see that most of the implementations I visit the relationship is more like a 10% reduction in DimTrans and 20% in BalanceTrans which is not much and almost makes these tables almost redundant.

In any case the advantage in performance using these tables rather than the LedgerTrans directly for reporting is not very big.

An easy means of seeing whether you have this issue is to configure your chart of accounts to show the current balance, if the display is jerky and slow which is the case very often it means the level of summary provided by the LedgerBalance View on the LedgerBalanceTrans is not sufficient to allow for a speedy summary of the ledgerbalances to date.

Another is to review the number of rows you have in LedgerTrans and in LedgerBalanceTrans & DimTrans respectively if the later are much smaller the optimisation is functional in your case if not you will eventually have a slower system as regards reporting.

What I would suggest is to ensure that it is possible to have a level of consolidation in the Balances so that they are reasonably fast for reporting purposes of course this means that we will slow down posting slightly however I believe the gains in reporting justify the expenditure :-).


/Sven

4 comments:

Palle Agermark said...

Have you reviewed the performance improving changes to ledger balances made in AX 2009?

Sven Jochimsen said...

Nope not yet, am hopefull is it much improved ?

/Sven

Palle Agermark said...

The LedgerBalancesVariant field on the balance table is gone again, and during posting changes to the balances are added up and applied after (not during) posting the individual transactions. Check LedgerVoucher.Post and the LedgerBalancesPostingList class.

/Palle

Sven Jochimsen said...

Sounds good, the fact that they are not summarized immediately I like less but can understand.

Actually on second thought summarizing on demand may be a good compromise.

/Sven