US6233573B1
Expired / Lapsed
US
⏱️ 0.0 years remaining
System and method for selecting rows from dimensional databases having temporal dimention
Assignee: Amazon Technologies Inc
•
Filing: 1999-12-13
•
Granted: 2001-05-15
•
Expires: 2019-12-13
Abstract
Abstract A system and method selects rows from a fact table in a dimensional database containing a fact table, a time dimension table and other dimension tables. The other dimension tables each contain rows containing a time invariant attribute to identify an item described by the row, an effective time attribute for the row, and other attributes. If an attribute for an item changes, a new row is added to the dimension table containing the time-invariant attribute for the item and current attributes for the item, without deleting or overwriting any existing rows for that item. Such dimension tables can be selected or used to create other tables using one or more time attributes of the dimension tables. The tables created can be selected or used to create still other tables using one or more time attributes of those tables. One or more of the resulting tables or one or more tables created from a resulting table are then joined to a table in the dimensional database or a table created from a table in the dimensional database.
Inventors
- 👤 John Bair
- 👤 Charles M Bender
Bibliographic Details
Application Number
—
Jurisdiction
US
Data Source
google_patents
Last Synchronized
2026-10-30 11:04
CPC Classifications
G; G06; G06F; G06F16/00; G06F16/20; G06F16/24; G06F16/245; G06F16/2458; G06F16/2477; Y; Y10; Y10S; Y10S707/00; Y10S707/912; Y10S707/917; Y10S707/99931; Y10S707/99932; Y10S707/99933; Y10S707/99934; Y10S707/99935; Y10S707/99941; Y10S707/99943; Y10S707/99951; Y10S707/99952; Y10S707/99953
Claims Analysis
Total Claims: 20
Claim 1
Independent Claim
176 words
One or more computer memories collectively containing a dimensional database data structure for resolving temporal queries, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key; a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item; and a fact table whose rows each contain an item identifier identifying an item to which the row relates, such that a temporal query specifying a condition of the time-variable attribute and effective time may be resolved by using the time dimension table to identify rows of the second dimension table satisfying the condition, generating an intermediate table whose rows each contain an item identifier from an identified row of the second dimension table, and joining the intermediate table and the fact table.
Claim 2
Dependent Claim
176 words
One or more computer memories collectively containing a dimensional database data structure for resolving temporal queries, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key; a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item; and a fact table whose rows each contain an item identifier identifying an item to which the row relates, such that a temporal query specifying a condition of the time-variable attribute and effective time may be resolved by using the time dimension table to identify rows of the second dimension table satisfying the condition, generating an intermediate table whose rows each contain an item identifier from an identified row of the second dimension table, and joining the intermediate table and the fact table.
Claim 3
Dependent Claim
41 words
The computer memories of claim 1 wherein the time key of each row of the second dimensional table identifies a row of the time dimension table representing a time at which the time-variable attribute value is first effective for the item.
Claim 4
Dependent Claim
41 words
The computer memories of claim 1 wherein the time key of each row of the second dimensional table identifies a row of the time dimension table representing a time at which the time-variable attribute value is last effective for the item.
Claim 5
Independent Claim
200 words
A method in a computer system for resolving a temporal queries in a dimensional database, comprising: receiving a temporal query specifying a condition of the time-variable attribute and effective time; accessing the dimensional database, the dimensional database comprising a time dimension table, a second dimension table, and a fact table, the time dimension table having rows that each represent a time and are each uniquely identified by a time key, the second dimension table having rows that each represent one state of a time-variable attribute of a particular item, each row of the second dimension table containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item, the fact table having rows that each contain an item identifier identifying an item to which the row relates; using the time dimension table to identify rows of the second dimension table satisfying the condition; generating an intermediate table comprising rows, each of said rows comprising an item identifier from an identified row of the second dimension table; and joining the intermediate table and the fact table.
Claim 6
Independent Claim
200 words
A method in a computer system for resolving a temporal queries in a dimensional database, comprising: receiving a temporal query specifying a condition of the time-variable attribute and effective time; accessing the dimensional database, the dimensional database comprising a time dimension table, a second dimension table, and a fact table, the time dimension table having rows that each represent a time and are each uniquely identified by a time key, the second dimension table having rows that each represent one state of a time-variable attribute of a particular item, each row of the second dimension table containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item, the fact table having rows that each contain an item identifier identifying an item to which the row relates; using the time dimension table to identify rows of the second dimension table satisfying the condition; generating an intermediate table comprising rows, each of said rows comprising an item identifier from an identified row of the second dimension table; and joining the intermediate table and the fact table.
Claim 7
Dependent Claim
203 words
A computer-readable medium whose contents cause a computer system to resolving a temporal queries in a dimensional database by: receiving a temporal query specifying a condition of the time-variable attribute and effective time; accessing the dimensional database, the dimensional database comprising a time dimension table, a second dimension table, and a fact table, the time dimension table having rows that each represent a time and are each uniquely identified by a time key, the second dimension table having rows that each represent one state of a time-variable attribute of a particular item, each row of the second dimension table containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item, the fact table having rows that each contain an item identifier identifying an item to which the row relates; using the time dimension table to identify rows of the second dimension table satisfying the condition; generating an intermediate table comprising rows, each of said rows comprising an item identifier from an identified row of the second dimension table; and joining the intermediate table and the fact table.
Claim 8
Dependent Claim
203 words
A computer-readable medium whose contents cause a computer system to resolving a temporal queries in a dimensional database by: receiving a temporal query specifying a condition of the time-variable attribute and effective time; accessing the dimensional database, the dimensional database comprising a time dimension table, a second dimension table, and a fact table, the time dimension table having rows that each represent a time and are each uniquely identified by a time key, the second dimension table having rows that each represent one state of a time-variable attribute of a particular item, each row of the second dimension table containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item, the fact table having rows that each contain an item identifier identifying an item to which the row relates; using the time dimension table to identify rows of the second dimension table satisfying the condition; generating an intermediate table comprising rows, each of said rows comprising an item identifier from an identified row of the second dimension table; and joining the intermediate table and the fact table.
Claim 9
Dependent Claim
198 words
A data processing system for satisfying a temporal query, comprising: one or more storage devices collectively containing a dimensional database for resolving temporal queries, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key, a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item, and a fact table whose rows each contain an item identifier identifying an item to which the row relates; a query receiver that receives temporal queries each specifying a condition of the time-variable attribute and effective time; and a query processing subsystem that satisfies temporal queries received by the query receiver by using the time dimension table to identify rows of the second dimension table satisfying the condition, generating an intermediate table whose rows each contain an item identifier from an identified row of the second dimension table, and joining the intermediate table and the fact table.
Claim 10
Dependent Claim
198 words
A data processing system for satisfying a temporal query, comprising: one or more storage devices collectively containing a dimensional database for resolving temporal queries, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key, a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item, and a fact table whose rows each contain an item identifier identifying an item to which the row relates; a query receiver that receives temporal queries each specifying a condition of the time-variable attribute and effective time; and a query processing subsystem that satisfies temporal queries received by the query receiver by using the time dimension table to identify rows of the second dimension table satisfying the condition, generating an intermediate table whose rows each contain an item identifier from an identified row of the second dimension table, and joining the intermediate table and the fact table.
Claim 11
Dependent Claim
186 words
One or more computer memories collectively containing a dimensional database data structure supporting time-variable attributes, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key; and a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item; such that the effective time of a time-variable attribute value for a particular row of the second dimension table may be determined by determining the time represented by a row of the time dimension table identified by the time key contained by that row of the second dimension table, and such that the effective times of the time-variable attribute values for a plurality of rows of the second dimension table may be determined by joining the time dimension table and the second dimension table on columns of those tables containing the time keys.
Claim 12
Dependent Claim
186 words
One or more computer memories collectively containing a dimensional database data structure supporting time-variable attributes, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key; and a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item; such that the effective time of a time-variable attribute value for a particular row of the second dimension table may be determined by determining the time represented by a row of the time dimension table identified by the time key contained by that row of the second dimension table, and such that the effective times of the time-variable attribute values for a plurality of rows of the second dimension table may be determined by joining the time dimension table and the second dimension table on columns of those tables containing the time keys.
Claim 13
Dependent Claim
147 words
A data processing system for supporting time-variable attributes in a dimensional database, comprising: one or more storage devices collectively containing a dimensional database, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key, and a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item; and an effective time determination subsystem that determines the effective times of the time-variable attribute values for a plurality of rows of the second dimension table by joining the time dimension table and the second dimension table on columns of those tables containing the time keys.
Claim 14
Dependent Claim
147 words
A data processing system for supporting time-variable attributes in a dimensional database, comprising: one or more storage devices collectively containing a dimensional database, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key, and a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing an item identifier for the item, a time-variable attribute value, and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item; and an effective time determination subsystem that determines the effective times of the time-variable attribute values for a plurality of rows of the second dimension table by joining the time dimension table and the second dimension table on columns of those tables containing the time keys.
Claim 15
Dependent Claim
188 words
One or more computer memories collectively containing a dimensional database data structure for resolving temporal queries, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key; a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing a time-variable attribute value and an surrogate attribute reflecting both the identity of the item and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item; and a fact table whose rows each contain a surrogate attribute identifying an item and a time to which the row relates, such that a temporal query specifying a condition of the time-variable attribute and effective time may be resolved by using the time dimension table to identify rows of the second dimension table satisfying the condition, generating an intermediate table whose rows each contain a surrogate attribute from an identified row of the second dimension table, and joining the intermediate table and the fact table on the surrogate attribute.
Claim 16
Dependent Claim
188 words
One or more computer memories collectively containing a dimensional database data structure for resolving temporal queries, comprising: a time dimension table whose rows each represent a time and are each uniquely identified by a time key; a second dimension table whose rows each represent one state of a time-variable attribute of a particular item, each row containing a time-variable attribute value and an surrogate attribute reflecting both the identity of the item and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value is effective for the item; and a fact table whose rows each contain a surrogate attribute identifying an item and a time to which the row relates, such that a temporal query specifying a condition of the time-variable attribute and effective time may be resolved by using the time dimension table to identify rows of the second dimension table satisfying the condition, generating an intermediate table whose rows each contain a surrogate attribute from an identified row of the second dimension table, and joining the intermediate table and the fact table on the surrogate attribute.
Claim 17
Dependent Claim
41 words
The computer memories of claim 9 wherein the time key of each row of the second dimensional table identifies a row of the time dimension table representing a time at which the time-variable attribute value is first effective for the item.
Claim 18
Dependent Claim
41 words
The computer memories of claim 9 wherein the time key of each row of the second dimensional table identifies a row of the time dimension table representing a time at which the time-variable attribute value is last effective for the item.
Claim 19
Dependent Claim
64 words
The computer memories of claim 9 wherein, for each row of the second dimension table, the surrogate attribute is a concatenation of an item identifier identifying an item whose state is described by the row and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value of the row is effective for the item.
Claim 20
Dependent Claim
81 words
The computer memories of claim 9 wherein, for each row of the second dimension table, the surrogate attribute is a key identifying a row of a surrogate table containing an item identifier identifying an item whose state is described by the row of the second dimension table and a time key identifying a row of the time dimension table representing a time at which the time-variable attribute value of the row of the second dimension table is effective for the item.
References Cited (Backward Citations)
US5317733A
Cited
US5440730A
Cited
US5347653A
Cited
US5551031A
Cited
US5557791A
Cited
US5428774A
Cited
US5359724A
Cited
US5745775A
Cited
US5572731A
Cited
US5603021A
Cited
US5548754A
Cited
US5590324A
Cited
US5950210A
Cited
US5970476A
Cited
US5905985A
Cited
USPTO Annuity & Maintenance Windows
| Annuity Stage | Window Opens | Payment Deadline | Surcharge Period Until | Estimated Fee | Status |
|---|---|---|---|---|---|
| 3.5 Years (Year 4) | 2004-05-14 | 2004-11-13 | 2005-05-15 | $2000.00 | Window Closed / Lapsed |
| 7.5 Years (Year 8) | 2008-05-14 | 2008-11-13 | 2009-05-15 | $3760.00 | Window Closed / Lapsed |
| 11.5 Years (Year 12) | 2012-05-14 | 2012-11-13 | 2013-05-15 | $7700.00 | Window Closed / Lapsed |