Storing history of a value from the database

[datanibbler]

Hi,

I am facing the following challenge:

My current document uses time values from the database to calculate turnover. That table, however, is not historized: The day someone enters new data, the old values are irretrievably lost. My document, in that case, has to use the old values for any dates in the past, however - the user has the possibility to select a date for which to view data and if that date is before the change, the document has to use the old values.

So I have to somehow store the values (about 8.500 items, three fields for every one (Item_No, time_1, time_2). That in itself is not a problem, but it is easy to see that when I do that every day - and I have to load the complete history into RAM because the user selects on the GUI the date he wants to view - my dashboard will be bigger every day.

I guess I cannot avoid the document getting bigger as more and more historical values are stored and have to be loaded, but I want to keep that effect as limited as possible.

Is there a way I can somehow (store all values once and then) compare, on a daily basis, all the values to the ones I have already stored and store new values, along with a change_date, only if any of them have changed? And can I have the document, based on the date the user enters, search from that date backwards and use the first set of records it encounters?

Thanks a lot for helping! This really seems to be quite a challenge - the most difficult part about it of course being to limit the "data growth effect".

Best regards,

DataNibbler

[swuehl]

If I understood correctly and if your size of the historical is that important for you (i.e. more important than UI performance), I think you can create your historical file like this:

//  uncomment to create a first version of historical data

//

//INPUT:

//LOAD *, Now() as DT INLINE [

//Item,Value

//1, 10

//2, 20

//];

//

//Store INPUT into historical.qvd (qvd);

//// after creation of first version, use:

HISTORY:

LOAD * from historical.qvd (qvd);

LOAD Item & '|' &  LastValue as HistKey;

LOAD Item, FirstSortedValue(Value, -DT) as LastValue

Resident HISTORY group by Item;

INPUT:

LOAD *, Now() as DT INLINE [

Item, Value

1, 10

2, 20

] where not exists (HistKey, Item &'|'& Value);

store HISTORY into historical.qvd (qvd);

Then in the front end, you can retrieve the requeste data like this, in a chart with dimension Item and using a variable vDT to select the Point in time.

=FirstSortedValue({<DT = {"<=$(vDT)"}>}Value, -DT)

edit: You can also think about combining a historical table like created above with interval matching approaches like discussed here and here

(both will add records to your data model, but may need less space then keeping the full INPUT records. But haven't tested this).