Change value database system and method
A method for constructing a change database reduces the footprint of a standard longitudinal data database and increases the manageability of the data for many applications. The change database concatenates representations of a time period for the change, the direction of the change, and the value of the change into a single string. The change database is constructed by creating foundation files from formatted historical data, constructing change files from these foundation files, and iteratively creating master files that encapsulate the change data.
1. A computer-implemented method for constructing a change database from historical data, comprising the steps of:
a. creating a plurality of periodic data files from the historical data, wherein each of the plurality of periodic data files comprises a plurality of records, and wherein each of the plurality of records comprises a plurality of record fields, wherein the plurality of record fields each comprises an identifier and a first value;
b. identifying a foundation file from among the plurality of periodic data files;
c. matching one of the plurality of periodic data files that is newer than the foundation file to the foundation file using the identifier, wherein a new record is created in the foundation file for each record in the newer one of the plurality of data files that contains an identifier that was not previously in any record in the foundation file;
d. updating any record in the foundation file that has the same identifier as a record in the newer data file with a second value from the newer data file;
e. creating a plurality of change files, each change file comprising each record in the foundation file that comprises both a first value and a second value, wherein each change file comprises a plurality of records each comprising a change value; and
f. creating a master file by comparing two of the plurality of change files, wherein the master file comprises each record from the two compared change files with an appended counter field, wherein each master file is partitioned into a plurality of partitions based on one of the record fields.
2. The computer-implemented method of claim 1 , wherein each of the plurality of records further comprises an origination date.
3. The computer-implemented method of claim 1 , further comprising the step of creating a first value in each new record in the foundation file.
4. The computer-implemented method of claim 1 , wherein the foundation file is based on the oldest of the plurality of periodic data files.
5. The computer-implemented method of claim 1 , wherein the change file comprises a name that comprises a date.
6. The computer-implemented method of claim 1 , wherein the change value comprises a delta value.
7. The computer-implemented method of claim 6 , wherein the change value comprises a concatenation of a delta value and a direction of change.
8. The computer implemented method of claim 7 , wherein the concatenation further comprises a date.
9. The computer-implemented method of claim 1 , wherein the change value is blank if the first value and second value are the same.
10. The computer-implemented method of claim 1 , repeating steps (c) through (e) for each of the plurality of periodic data files not previously processed.
11. The computer-implemented method of claim 1 , wherein each counter field comprises a counter value, and for each record in the master file that comprises the same identifier, each such record comprises a different counter value.
12. The computer-implemented method of claim 1 , wherein each counter value is assigned to records for a particular identifier sequentially whereby an order of change may be discerned from the counter values in the counter field for each record in the master file that comprises the same identifier.
13. The computer-implemented method of claim 1 , wherein each master file is partitioned based on the identifier.
14. The computer-implemented method of claim 13 , wherein the master file is partitioned into ten partitions, wherein each record in the master file is assigned to a partition based on a last digit of the identifier.