Ledger Balance Data in DAX
I remembering the first time when our team was developing a solution which was supposed to quickly calculate the GL balance for specific GL Account/Dimension combination. It was 2001 and we were developing the cost allocation functionality.
We just started to work with Axapta 2.1, so – our first approach was simply to calculate a GL Balance by summing up LedgerTrans table from the beginning of times . Soon, after we went live, we realized that the brute force approach is not always the most successful way of doing things in Axapta.
Luckily, we found the ledgerBalance and LedgerBalanceDim tables. First table contained the summed up GL turnover per GL Account+Period; Second table contained the summed up GL turnover per GL Account+GL Dimensions+Period. After we started to use these two tables for calculation GL balance from these two tables, we saw dramatic improvement in performance, since 200-300 of ledgerTrans records was packed into one record of these balance tables. (We had around 10-15 postings per GL Account daily and our GL period was a month).
Little bit later (like in the Fall 2001), we found out that these two tables is a more like part of problem than part of solution. Every time when you design some table which holds summed-up data for quick retrieval, one should remember that update of the data in the table will hold the lock on the table’s record until end of the transaction. So, If Axapta was implemented in the company, which did not have very detailed GL account sheet (say only 3-4 GL accounts was used to hold the customer’s payment balance), posting of the multi-line payment journal could prevent everyone else from posting ANY other customer-related document for 5-10 minutes. Somewhere in the beginning of transaction, system was updating the balance data and then this data was locked until the end of transaction. In connection with mutual locking during inventory update, which I discussed in my article “History of Inventory Locking”. this had a rather catastrophic consequences for the system’s stability and performance. Couple of carefully planted orders/journals with the same GL accounts and inventory items, could make system unusable for a whole business day :). The only remedy for this was the usage of batch servers for posting of nearly all documents, because batch server was naturally preventing any competition for resources by converting several potentially parallel processes into one sequential queue of processes. This approach could be a solution for accountancy-centric implementations, but any real time process, like WMS just could not be fixed this way. That’s why I never believed into any of success stories telling about WMS implementation in Axapta made BEFORE release of Axapta 3.0.
The situation was relieved in Axapta 3.0. These two tables was replaced with LedgerBalanceTrans/ledgerBalanceDimTrans tables. To prevent lock chains and deadlock during an update of balance data, developers of Axapta introduced two additional fields to these new tables: the transaction date and the funny named field ‘LedgerBalancesVariant’. First thing, we realized that now balance data is stored on per-date basis, but not per-period. Initially, the meaning of this Variant field was not clear for me. Soon, I found out that actually, system keeps up to 20 (TWENTY) GL balance records for the same date and same account/dimension combination. During update, system was RANDOMLY selecting a number from 1 to 20 and then updating/inserting balance record with arbitrary Variant number. (Actually – It was not complete random number. It was calculated as Variant= (SessionId mod 20), so update of the same account/dimension combinations from the same session went to the same balance record. If they were using true random() value, update from the same session and the same DB transaction would be scattered across several records even it was the only transaction for the combination in the day.) Thus, chances for long locks was greatly decreased. Even if GL account sheet is too coarse grained, probability for two users to update the same record at the same time now was like only 5%. When I first studied this concept, I had an idea that maybe after 300-400 concurent users, I would increase the number of these ‘variants’ to 30-40, but actually this idea proved to be wrong, since I never saw any
serious lock contentions on ledger balance data since release of version 3.0. Of course, if someone were to monitor process locking in MS SQL, he/she would see occasional waiting locks on ledgerBalanceTrans/ledgerBalanceDimTrans from time to time, but generally – balance data had ceased to be the main bottleneck of system’s performance.
From the other side – this solution actually traded update performance for for query performance. After balance data have been scattered across 20 variants and stored on the per-date basis, size of ledger balance table became comparable to the size of the ledger transactions table itself. Typically, size of LedgerBalanceDimTrans table became to be around 50-70% of ledgerTrans table. Since then, usage of ledgerBalanceDimTrans instead of ledgerTrans for reporting purposes became unnecessary complication, since query time for the both cases was comparable, but ledger balance data sometimes lacked necessary details important for some particular reports. Of course, in may cases usage of ledgerBalanceDimTrans was little bit more convenient then direct usage of ledgerTrans data, since one record of the balance table holds both debit and credit turnover, while fetching the corresponding data from ledgerTrans with ‘group by crediting’ would give us debit and credit turnovers in different records.
When I first saw ledger balance tables in Dynamics AX 2009 I was very surprised to see that field Variant had gone. First of all I had not expected any changes in these area since it had became non-problematic since 2003. Second – I saw no way for this mechanism to exists without Variant field.
Actually, it turns out that designers of financial subsystem in Axapta realized that actual results from usage of this Variant algorithm was satisfactory, but not perfect. From one side, we lost almost all of our DB space/query time savings from ledgerBalance* usage, from the other side – we still can have a lock conflicts on ledger balance data updates. (Though less frequent). So the designers decided to store ONE balance record per ONE ledger voucher. In the worst case (if we have post mostly one-line documents) we will have the number of ledgerBalance* records equal to the number of ledgerTrans records. In the realistic case (we post at least 50-70% of multi-line documents) we will have the same number of records as in Variant-mechanism. Since now these balance data is not a query performance increase mechanism anyway since 2003, actual size of ledgerBalances* tables does not matter; It enough for them to not outgrow ledgerTrans itself. From the other side, since EVERY ledger voucher now has it own balance record, there is no chance of lock conflicts at all.
One interesting point which is worth to mention about the new mechanism is the way how these balance updates are being made now:
- All updated balances made in one ledgerVoucher is kept in RecordSortedList (thus – in memory) somewhere in LedgerBalancesList/LedgerBalancesPostingList class.
- As soon as posting of ledgerVocher has finished, or the list became to large to hold in memory it is being flushed to quasi-temporary table LedgerBalancesTransDelta.
- In the end of ledgerVoucher, system opens separate additional database connection and use this separate connection to execute insert_recordset statement which insert new balance records by summing up ledgerBalancesTransDelta. This update use optimistic locking mechanism (with transaction retry), but since this is separate transaction with a very little of actual database update, it can be rolledback and retried with much overhead. (And this is exact reason to use separate transaction for this update! If we were to use the main database transaction, in case of lock conflict, this would cause a great overhead (Imagine 20000 lines FA depreciation journal, which was rolled back and posted again because of balance data lock conflicts).
- Finally, the records belonging to current session are being deleted from ledgerBalancesTransDelta. (That is why this table called quasi temporary).
Although, as I said, I have never seen any actual implementations of Dynamics AX 3-4 which really suffered from ledger balance data related bottlenecks, It might be that these problems would arise in 3000-4000 user implementation. (Never have seen personally implementations with more then 700-800 users). Overall, the whole history of changing approach to balance data in DAX looks like a good example of software design and engineering.