History of slowly changing dimensions
Disclosed herein is a data structure that reduces computational load on a database in querying and visualizing historical values of slowly changing dimensions. The slowly changing dimensions have one or more values associated with them (e.g., a monthly average accounts receivable is a single value, and a set of contact information for an individual may have multiple values). On a periodic basis, database images, also known as “snapshots” are generated of a current status of a given database. The snapshots are used to build a historical data table that is computationally efficient to reference.
1. A method of improving database visualization, comprising:
periodically generating a set of database images of a database;
including a timestamp on each of the set of database images;
defining a slowly changing dimension in the database, the slowly changing dimension comprising a value that on average changes less frequently than a rate of said periodically generating the set of database images of the database;
integrating the set of database images into a data structure, the data structure storing a history of the slowly changing dimension including a range of time associated with each value held by the slowly changing dimension, wherein said integrating is chronologically agnostic with respect to a timestamp order of integration of each of the set of database images, and the each value is associated with a database image of the set of database images and the data structure is configured to integrate, in parallel, a progressing forward chronological database image with a progressing reverse chronological database image, another range of time being stored in the data structure when another value held by the slowly changing dimension different than the each value held by the slowly changing dimension is determined, the another value being associated with a database image different than another database image associated with the each value, the range of time being temporally continuous and non-overlapping with the another range of time; and
preparing a report based on the data structure that visually depicts in a display system the history of the slowly changing dimension.
2. The method of claim 1 , wherein the slowly changing dimension includes multiple fields each including an independent value.
3. The method of claim 1 , wherein the slowly changing dimension is a first slowly changing dimension of a plurality of slowly changing dimensions and the database stores a history of the plurality of slowly changing dimensions.
4. The method of claim 1 , further comprising:
generating an additional database image of the database at a present timestamp; and
updating the data structure based on the additional database image.
5. The method of claim 1 , further comprising:
isolating erroneous values in the history based on a specified criterion; and
correcting the history where erroneous values are present.
6. The method of claim 1 , wherein the value is non-numeric.
7. The method of claim 1 , wherein the value is numeric, and the method further comprising:
displaying the history in a graph interface as a function of the value to time.
8. The method of claim 1 , wherein the history is of the slowly changing dimension is continuous and non-overlapping in time.
9. The method of claim 1 , wherein said integrating further comprises:
arbitrarily ordering the set of database images; and
integrating database images into the data structure based on the arbitrary order in parallel.
10. The method of claim 1 , wherein said defining is performed subsequent to generation of the set of database images.
11. A system comprising:
a database storing values that change over time in response to computationally infrequent revisions;
a processor; and
a memory including instructions that cause the processor to:
periodically generate a database image of the database, wherein a first database image includes the first value of the object at a first timestamp;
define a slowly changing dimension based on a first object stored in the database, the slowly changing dimension comprising a variable value that on average changes less frequently than a rate of said periodically generating the set of database images of the database;
integrate the first database image into a data structure stored in the memory, the data structure storing a history of the slowly changing dimension including a range of time each value held by the slowly changing dimension, wherein said integrating is chronologically agnostic with respect to a timestamp order of integration of each of the set of database images, and the each value is associated with a database image of the set of database images and the data structure is configured to integrate, in parallel, a progressing forward chronological database image with a progressing reverse chronological database image, and wherein the history includes a range of time of each value held by the slowly changing dimension, another range of time being stored in the data structure when another value held by the slowly changing dimension different than the each value held by the slowly changing dimension is determined, the another value being associated with a database image different than another database image associated with the each value, the range of time being temporally continuous and non-overlapping with the another range of time; and
preparing a report based on the data structure that visually depicts in a display system the history of the slowly changing dimension.
12. The system of claim 11 , wherein the first object represented as the slowly changing dimension includes multiple fields, each of the fields including an independent value.
13. The system of claim 11 , wherein the slowly changing dimension is a first slowly changing dimension of a plurality of slowly changing dimensions and the database stores a history of the plurality of slowly changing dimensions.
14. The system of claim 11 , wherein the history is of the slowly changing dimension is continuous and non-overlapping in time.