- This topic has 4 replies, 2 voices, and was last updated 6 years, 4 months ago by
.
-
Posts
-
Is it possible to build a virtual date table or view with which we can set filters on and iterate through? This table or view does not necessarily need to physically store every date but dynamically self-generated in memory. We can then do all kinds of cumulative calculations against this date table, without creating lots of indexs with cumulative totals.
sorry for not being specific.
There are cases in which I need to do cumulative totals at each date for a given period of time. For instance, G/L Account balance at each date through out the entire fiscal year. Or, Item inventory at each date for a given period of time. The most common approach I have seen in many different systems is to create a kind of index table which consists of concerned elements, e.g.
"GL Account No." Date Balance
1100 09/08/2014 10
1100 09/09/2014 20
1100 09/10/2014 30
....
1200 09/08/2014 10
1200 09/09/2014 20
1200 09/10/2014 30
....Whenever there is transaction happening to G/L Account No, we try to maintain this index sum table. So that we can pull it out later for cumulative balance reporting. The same logic could be implemented for other similar requirements. The problem with this approach is that by the time it goes, this table becomes increasingly large and very “expensive” to maintain. Sometimes, it gets so bad that the system becomes really slow and we cant afford to maintain this table during transaction update. And, technically, it can be quite challenging to write the code to maintain this table, especially when the criterio becomes complex.
There is a different approach which I have seen in another system that is written in C++. It implements a kind of virtual date table like a built-in date calender which has a table structure like this:
Date Name
01/22/0001 Monday
01/23/0001 Tuesday
01/24/0001 Wednesday
01/25/0001 Thursday
01/26/0001 Friday
01/27/0001 Saturday
01/28/0001 Sunday
...
01/01/9998
01/01/9999The data in this table is not physically stored in the database but dynamically populated in memory. In this case, if I want to calculate G/L Balance at date, I simply do a G/L Account left join Date, then using this G/L Account No. and each specific date, one can easily filter out the transactions and calculate the totals accordingly. This way, there is no need to create or maintain any additional tables with totals. And it is easy to change the logic of calculation in case criterio changes without physically affecting the database, in fact, with Rules, one can just create as many derived columns as they need against with this Date table and attach them on fly for different needs.
Ah, I see what you mean. I have not seen that before. It is an interesting idea.
Unfortunately, Suneido does not currently have any way to do that.
Internally (in the C++ and Java code) it would not be too hard to add. Basically it would require get next/prev and select methods.
There are pros and cons for both of them:
pros:
1) the first approach is fast and easy during calculation, as it pulls out the totals which are physically stored.
2) the second approach does not have any physical impact to the database or cause any additional operations. And in real cases, the requirements are specific, like for a certain number of G/L Account in a given period of time. These could help to narrow down the transactions which can be profitable in reality.cons:
1) the first approach is costly to maintain during transaction update. And it has a physical, permanent impact to the database and front end system.
2) the second approach needs to calculate every result from the transactions every time it is needed, which could be very large and slow too.Never mind, I just thought it’s an idea worth a look.
- You must be logged in to reply to this topic.