fed: Ledger Balance Data in DAX

[Blog bot]

Источник: http://fedotenko.info/?p=167
==============

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:

1. All updated balances made in one ledgerVoucher is kept in RecordSortedList (thus – in memory) somewhere in LedgerBalancesList/LedgerBalancesPostingList class.
2. 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.
3. 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).
4. 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.



Источник: http://fedotenko.info/?p=167

[mazzy]

как всегда, отлично.
на русском бы, а?

[TasmanianDevil]

Возможно я тормоз и не понимаю "модных" архитектурных тенденций в архитектуре БД, но тем не менее - чем же, для получения баланса на дату, вариант архитектуры агрегированных оборотов по субсчетам/ субсчетам и аналитикам (т.е LedgerBalancesTrans и LedgerBalancesDimTrans), требующий ежеоперационного обновления и решения проблем блокировок из-за этих частых операций, настолько превосходит прочие варианты получения баланса ГК, например "ближайший остаток на дату + операции да даты баланса", не требующий ежеоперационного обновления и не порождающего такого кол-ва блокировок ?

[fed]

Цитата:

Сообщение от TasmanianDevil

Возможно я тормоз и не понимаю "модных" архитектурных тенденций в архитектуре БД, но тем не менее - чем же, для получения баланса на дату, вариант архитектуры агрегированных оборотов по субсчетам/ субсчетам и аналитикам (т.е LedgerBalancesTrans и LedgerBalancesDimTrans), требующий ежеоперационного обновления и решения проблем блокировок из-за этих частых операций, настолько превосходит прочие варианты получения баланса ГК, например "ближайший остаток на дату + операции да даты баланса", не требующий ежеоперационного обновления и не порождающего такого кол-ва блокировок ?

Только для того чтобы можно было показывать балансы по счету на любую дату сразу с дебетом и кредитом . Вообще-то по моему все это - архитектурный пережиток времен медленных серверов БД...

[TasmanianDevil]

Цитата:

Сообщение от fed

Только для того чтобы можно было показывать балансы по счету на любую дату сразу с дебетом и кредитом .

Как правило, суммарные обороты по дебету и кредиту гораздо чаще интересуют конечного потребителя за какой-то определенный и конечный промежуток времени, кратный неким отчетным периодам, нежели суммы многолетних движений "от начала времен до какой-то даты ", которые предлагает используемая архитектура. К тому же эта архитектура для получения баланса по счету ГК с течением времени предполагает монотонное нарастание количества анализируемых записей, в отличие от второго способа, в котором кол-во выбираемых записей - всегда некая константа плюс/минус некий процент.

Цитата:

Сообщение от fed

Вообще-то по моему все это - архитектурный пережиток времен медленных серверов БД...

Т.е. все эти варианты хранения промежуточных результатов для ускорения расчета остатков - от бедности ?

[fed]

Цитата:

Сообщение от TasmanianDevil

Как правило, суммарные обороты по дебету и кредиту гораздо чаще интересуют конечного потребителя за какой-то определенный и конечный промежуток времени, кратный неким отчетным периодам, нежели суммы многолетних движений "от начала времен до какой-то даты ", которые предлагает используемая архитектура. К тому же эта архитектура для получения баланса по счету ГК с течением времени предполагает монотонное нарастание количества анализируемых записей, в отличие от второго способа, в котором кол-во выбираемых записей - всегда некая константа плюс/минус некий процент.

Т.е. все эти варианты хранения промежуточных результатов для ускорения расчета остатков - от бедности ?

Вообще-то используемая архитектура, в запаном учете, позволяет, для подсчета сальдо и для подсчета оборотов всегда плясать ТОЛЬКО от начала финансового года. Просто у них в конце одного финансового года все сальдо списывают на некий счет, а потом в начале следующего финансового года все эти сальдо восстанавливают с того же самого счета (а может быть и с другого). Так что в западном учете сальдо равно оборотам с начала финансового года, считать с начала времен - необязательно. Так что это как раз и есть схема "Остаток+обороты".Но все равно - размер ledgerBalances и ledgerTrans а период сопоставим не зависимо то того, как мы считаем - с начала времен или с начала финансового года.
Кстати - я слышал что и в некоторых российских внедрениях используют заключительную ведомость в конце года для списания/приходования остатков. Правда, это не очень вяжется с русскими ПБУ...
А все это до сих пор используют потому что:
1. Во первых так сложилось. Когда авторы догадались о том что с блокировками все нехорошо, вероятно уже поздно было выкинуть эти таблицы.
2. Во вторых - эта архитектура позволяет собрать и дебетовые и кредитовые обороты в одной строке. Не так чтобы это великим достижением было, но иногда приятно.
3. Архитектура позволяет сэкономить немножко на времени доступа к данным. Хотя на мой взгляд, подобная экономия уже не стоит накладняка.

[Raven Melancholic]

Цитата:

Сообщение от fed

Кстати - я слышал что и в некоторых российских внедрениях используют заключительную ведомость в конце года для списания/приходования остатков. Правда, это не очень вяжется с русскими ПБУ...

Почему это не вяжется? В Российском учете есть понятие "реформация баланса", это именно то, что требуется. К тому же, существуют несколько ПБУ, описывающих работу после закрытия периода (одно из них обновлено не так давно) - все они требуют корректировок "вне времени", то есть именно в заключительной ведомости.
Поэтому вариант подсчета с начала финансового года вполне рабочий и ничем не противоречит ПБУ.

[mazzy]

Цитата:

Сообщение от fed

Так что в западном учете сальдо равно оборотам с начала финансового года, считать с начала времен - необязательно. Так что это как раз и есть схема "Остаток+обороты".Но все равно - размер ledgerBalances и ledgerTrans а период сопоставим не зависимо то того, как мы считаем - с начала времен или с начала финансового года.

Не. Тут ты не прав.

Во-первых, в LedgerBalances хранятся начальные остатки на начало финансового года.
Следовательно вместо выборки из LedgerTrans от начала времен
они делают выборку из LedgerBalances от начала фин.года плюс выборка LedgerTrans от начала фин.периода до даты. Что существенно меньше.

Другими словами:
1. от начала времен: LedgerTrans[<DateTo]
2. с оборотами: LedgerBalances[>=StartFinYear, <StartFinPeriod] + LedgerTrans[>=StartFinPeriod, <DateTo]

пример Производительность (в табличном виде)
Обрати внимание, что LedgerTrans - самая большая таблица.
А LedgerBalances не входит даже в первую двадцатку.
И это типичная ситуация.

Кроме того, даже выборка из LedgerBalances ведется не по всему объему, а только по последнему финансовому году. Что здорово сказывается, если база содержит данные за 5-8-10-и-более лет.
Не говоря уже об уменьшении выборки LedgerTrans

это только уменьшение выборки.
а если вспомнить про возможность сегментирование данных (старые скидывать в отдельную файловую группу).
а если еще вспомнить про метод PostLoad...

[Bober]

Цитата:

Сообщение от fed

Когда авторы догадались о том что с блокировками все нехорошо

Это п. Столько лет уже Аксапте. А всё одни и те же грабли. Когда в разрабокту Микрософт перестанут брать дураков ?

[Raven Melancholic]

Цитата:

Сообщение от TasmanianDevil

прочие варианты получения баланса ГК, например "ближайший остаток на дату + операции да даты баланса",

Если не секрет, то что такое:

Цитата:

ближайший остаток на дату

На какую дату?

[TasmanianDevil]

Цитата:

Сообщение от Raven Melancholic

Если не секрет, то что такое:

Некий аналог InventSum, т.е. хранение промежуточных входящих остатков по счету/аналитике ГК на определенные даты (как правило на начало какого-либо отчетного периода с определенным квантованием, например на начало года, квартала или месяца) с расчетом баланса на определенную дату по алгоритму "ближайшее по дате сальдо до даты баланса + операции с даты найденного сальдо до даты баланса". В отличии от используемого алгоритма несколько более затратен в плане вычислений, но не требует ежеоперационного обновления каких-то агрегированных оборотов, а только периодических процедур закрытия периода с расчетом сальдо, кои не блещут такой любовью к созданию блокировок.
Стандартный функционал AX по конечному результату нечто подобное и позволяет делать через операции в закрывающем/открывающем периодах, но в случае отказа от их использования - все начинает сводиться к банальному суммированию агрегированных оборотов с начала времен.

[mazzy]

Цитата:

Сообщение от TasmanianDevil

...чем же ... вариант архитектуры агрегированных оборотов ... настолько превосходит прочие варианты получения баланса ГК, например "ближайший остаток на дату + операции да даты баланса"...

я не очень понял смысла второго варианта.
но я точно знаю чем отличается от варианта "суммирование от начала времен" - резко уменьшается выборка из базы.

LedgerBalanceTrans/ledgerBalanceDimTrans позволяют не дергать записи из LedgerTrans от начала времен.
Производительность
Стандартные методы получения сальдо/оборотов берут записи из LedgerTrans только в ближайшем финансовом периоде (который может быть уменьшен администратором с месяца до дня).

просто преступно суммировать от начала времен записи в этой таблице потому что:

• таблица LedgerTrans - входит в Top10 по объему на большинстве внедрений. А заставлять SQL делать одни и те же громадные выборки - не комильфо.
• у таблицы LedgerTrans до сих пор переопределен метод PostLoad

[mifi]

Цитата:

Сообщение от Blog bot

Источник: http://fedotenko.info/?p=167
==============

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.

...

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.



Источник: http://fedotenko.info/?p=167

Думаю, что не открою большого секрета, если скажу, что наши западные коллеги рассматривают многострочные проводки в качестве основного сценария для тестирования чего угодно, в том числе и производительности. А с точки зрения практики российских внедрений это зверь очень редкий.
От этого все и идет. Это, в принципе, и к вопросу про "знание о внедрениях". Уверяю, что знания более чем достаточно, просто то, как систему используют в России/Восточной Европе очень сильно отличается от тех же Штатов. А далее это уже вопрос приоритетов этих самых сценариев и т.д.

[fed]

Цитата:

Сообщение от mifi

Думаю, что не открою большого секрета, если скажу, что наши западные коллеги рассматривают многострочные проводки в качестве основного сценария для тестирования чего угодно, в том числе и производительности. А с точки зрения практики российских внедрений это зверь очень редкий.
От этого все и идет. Это, в принципе, и к вопросу про "знание о внедрениях". Уверяю, что знания более чем достаточно, просто то, как систему используют в России/Восточной Европе очень сильно отличается от тех же Штатов. А далее это уже вопрос приоритетов этих самых сценариев и т.д.

Если честно - я вообще не понимаю всех этих проблем с производительностью рассчета баланса/оборотов по ГК. Потому как используется это только для рассчета баланса и отчета о прибылях и убытках, которые раз в месяц строяться и особой производительности не требуют. Если же ты пытаешься какие-то более детальные отчеты по ГК получить, то все равно проще проводки в OLAP загнать и там крутить.
Мне кажется что у mazzy такое внимание к этим остаткам - это следствие глубинной психологической травмы от работы с 1c в 90ых

[mifi]

Цитата:

Сообщение от fed

Если честно - я вообще не понимаю всех этих проблем с производительностью рассчета баланса/оборотов по ГК. Потому как используется это только для рассчета баланса и отчета о прибылях и убытках, которые раз в месяц строяться и особой производительности не требуют. Если же ты пытаешься какие-то более детальные отчеты по ГК получить, то все равно проще проводки в OLAP загнать и там крутить.
Мне кажется что у mazzy такое внимание к этим остаткам - это следствие глубинной психологической травмы от работы с 1c в 90ых

Видишь, какие разные у всех людей представления о внедрениях. Кто-то не понимает проблем с закрытием склада: а что, делается раз в месяц, в чем проблема-то? Кто-то не понимает проблем расчета баланса/оборотов по ГК...

[glibs]

Цитата:

Сообщение от fed

...
проблем с производительностью рассчета баланса/оборотов по ГК. Потому как используется это только для рассчета баланса и отчета о прибылях и убытках, которые раз в месяц строяться и особой производительности не требуют.
...

Это какой-то вырожденный теоретический случай.

А управленческая отчетность оперативная? Исполнение бюджета план-факт. Хоть раз в час могут хотеть смотреть для принятия управленческих решений. Прогноз ДДС. Оперативный анализ затрат. Оперативный анализ ДДС. Оперативный прогноз ДДС. Да полно управленческих отчетов.

Хотя в России да... мало внедрений где есть толковые финансисты, понимающие как можно управлять с помощью финансовой отчетности. Обычно бухгалтера интересуются шахматками-шашечками в конце месяца. Но тем не менее.

[glibs]

Цитата:

Сообщение от fed

...
Если же ты пытаешься какие-то более детальные отчеты по ГК получить, то все равно проще проводки в OLAP загнать и там крутить.
...

Я обожаю ОЛАП-ки.

Но строить их не так просто. И нужен специалист уровня разработчика. Особенно если нужен план-факт или комбинация плана, факта и прогноза. Ну и приведение к основной валюте того же бюджета и прочие нюансы... И под разные аналитические задачи приходится строить специализированные кубики, а то у ОЛАп-ок тоже производительность имеет тенденцию запарываться драматически, если туда натолкать всю возможную аналитику (движок уже тогда не в состоянии оптимально построить промежуточные итоги).

А финотчеты может строить финансовый аналитик. Я сам видел. Справляются.

В общем, как говорит Маззи, побольше инструментов. Хороших и разных. На вкус и цвет товарищей нет. Чем больше при желании можно использовать опций — тем лучше система.

[glibs]

Кстати, насколько я заметил краем глаза в 5.0 убрали 20-кратное раздутие LedgerBalancesDimTrans. Судя по таблице LedgerBalancesTransDelta в таблице итогов без аналитик с блокировками решили бороться тем же способом, как и в InventSum в 4.0.

Кстати, про обороты по счетам ГК без аналитик. Уж точно на несколько порядков быстрее будут работать.

Да... справедливости ради стоит оговориться что тем, кто не может анализировать финансовые операции без корреспонденции счетов, таблицы агрегированных оборотов по проводкам ГК, о которых идет речь, бесполезны.

[fed]

Цитата:

Сообщение от glibs

Кстати, насколько я заметил краем глаза в 5.0 убрали 20-кратное раздутие LedgerBalancesDimTrans. Судя по таблице LedgerBalancesTransDelta в таблице итогов без аналитик с блокировками решили бороться тем же способом, как и в InventSum в 4.0.

Кстати, про обороты по счетам ГК без аналитик. Уж точно на несколько порядков быстрее будут работать.

Да... справедливости ради стоит оговориться что тем, кто не может анализировать финансовые операции без корреспонденции счетов, таблицы агрегированных оборотов по проводкам ГК, о которых идет речь, бесполезны.

Гы ! Так я о том и пишу. В версии 2009 ОДНУ запись в балансовые таблицы пишут на ОДИН ваучер! Так что эти 20 записей (если ты уж так обеспокоен размером этой таблицы) это еще немного было... Не веришь - попробуй в 2009ой найти команды ОБНОВЛЕНИЯ балансов. Я долго не верил своим глазам - но оказалось что там только ВСТАВКИ.

Кстати к товоему праведному возмущению по поводу того что это мол не только для баланса/отчета о прибылях и убытках нужно. Я в общем-то с тобой согласен. Но все равно это не остатки в наличии которые при каждой складской операции проверяются, а некая информация которая нужна ограниченому кругу лиц (2-3-4 финансиста, наверное еще 3-4-5 менеджеров), не постоянно, а время от времени. И вот получается что эти таблицы балансов пользу приносят небольшому количеству пользователей, а вред (за счет увеличения - пусть небольшого) времени разноски - почти всем пользователям (уж процентов 70 пользователей-то всяко какие-то проводки в системе создают время от времени).
Ну и к слову сказать - учитывая что число записей и размер записи в ledgerTrans и ledgerBalancesDimTrans обычно сопоставимы, выигрыш от использования именно таблицы балансов - он небольшой. Он был большим - до версии 3.0. Но это было давно и неправда

[glibs]

Цитата:

Сообщение от fed

...
Я в общем-то с тобой согласен. Но все равно это не остатки в наличии которые при каждой складской операции проверяются, а некая информация которая нужна ограниченому кругу лиц (2-3-4 финансиста, наверное еще 3-4-5 менеджеров), не постоянно, а время от времени. И вот получается что эти таблицы балансов пользу приносят небольшому количеству пользователей, а вред (за счет увеличения - пусть небольшого) времени разноски - почти всем пользователям (уж процентов 70 пользователей-то всяко какие-то проводки в системе создают время от времени).
...

Ах так?

Предлагаю тогда реализовать вашу идею с выборкой данных по проводкам в ГК с на промышленной БД с внушительным количеством проводок ГК, и дать бухгалтерам похлопать формой плана счетов с корректно настроенным отображением сальдо по счету. Желательно сразу несколько посадить их чтобы одновременно хлопали.

Посмотрим, чем они вас отблагодарят. И что вам скажет дисковый массив сервера БД.

Еще можно попробовать поставить на счетах проверку сальдо. Типа обязательное неотрицательное сальдо. И посмотреть как скоро начнут разноситься журналы ГК с большим количеством операций. Да и любые журналы, которые порождают проводки в ГК (те же складские).