SYSTEM AND METHOD FOR PROCESSING AND ANALYZING DIMENSION DATA

• US 20110264618A1
• Filed: 04/23/2010
• Published: 10/27/2011
• Est. Priority Date: 04/23/2010
• Status: Active Grant

• Current Assignee: Hartford Fire Insurance Company (The Hartford Financial Services Group, Inc.)
• Original Assignee: Hartford Fire Insurance Company (The Hartford Financial Services Group, Inc.)
• Inventors: Raghumandala, Harikrishna, Vernale, John, Potdar, Asha Kiran
• Granted Patent: US 8,626,790 B2
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A processor is operated to combine a first row of a dimension table in a data warehouse with a second row in the dimension table. The result is a combined row that includes a row identification key for the first row and a row identification key for the second row. The row identification key for the first row joins the combined row to fact data from a prior time period. The second row corresponds to a current time period that is later than the prior time period. The processor is also operated to join at least a portion of the combined row to at least a portion of the corresponding row in a fact table associated with the dimension table. The fact data from the prior time period is included in the corresponding row in the fact table.

Claims

1. A computer system for generating reports from stored data, the computer system comprising:

a data warehouse including at least one data storage hardware unit storing data according to a schema in which each of a plurality of dimension tables is associated with a fact table, at least one of the dimension tables stored in a format having, for a given entity, at least two row entries;

the at least two row entries including a current row entry with a maximum effective end date and a prior row entry with an effective end date that is prior to a current point in time; and

a processor in communication with the data warehouse, the processor operating to;

generate a query for application to the data warehouse, the query for effecting a self-join on a one of said dimension tables that is in said format to combine the current row entry for said given entity with at least one prior row entry for the given entity, the self-join producing a history restatement view, the query further joining the history restatement view to the fact table to produce a query view;

apply the query to the data warehouse to generate the query view; and

transmit the query view to a user;

wherein the query view is a report derived from said data stored in the data warehouse.

2. The computer system of claim 1, further comprising:

a display unit coupled to the processor for presenting the query view to the user.

3. The computer system of claim 1, wherein said prior row entry and said current row entry each have a respective row identification key, the row identification keys being different from each other.

4. The computer system of claim 3, wherein the history restatement view associates a current value of an attribute of the given entity with a previous fact value for the given entity.

5. The computer system of claim 4, wherein the history restatement view contains a restatement view row entry that corresponds to the prior row entry, the restatement view row entry including both the row identification key of the current row entry and the row identification key of the prior row entry and having the current value of the attribute in place of a prior value of the attribute.

6. The computer system of claim 5, wherein the query view includes a query view row entry that includes said current value of the attribute of the given entity and the previous fact value for the given entity.

7. The computer system of claim 1, wherein the history restatement view is non-persistent.

8. A method of operating a computer system, comprising:

operating a processor to combine a first row of a dimension table with a second row of the dimension table to produce a combined row in a history restatement view, the combined row including a row identification key for the first row and a row identification key for the second row, the row identification key for the first row joining the combined row to fact data from a prior time period, the second row corresponding to a current time period that is later than the prior time period; and

operating the processor to join at least a portion of the combined row to at least a portion of a corresponding row in a fact table associated with the dimension table, said fact data from said prior time period included in said corresponding row in said fact table.

9. The method of claim 8, wherein the fact table contains information regarding past insurance policy marketing activities.

10. The method of claim 8, wherein the fact table contains information regarding insurance policies.

11. The method of claim 8, further comprising:

presenting a query view to a user at a terminal, the query view including said at least a portion of the combined row in association with said at least a portion of the corresponding row in the fact table;

wherein the query view is a report derived from said data stored in the data warehouse.

12. An apparatus comprising:

a processor; and

a memory in communication with the processor and storing program instructions, the processor operative with the program instructions to;

combine a first row of a dimension table with a second row in the dimension table to produce a combined row in a history restatement view, the combined row including a row identification key for the first row and a row identification key for the second row, the row identification key for the first row joining the combined row to fact data from a prior time period, the second row corresponding to a current time period that is later than the prior time period; and

join at least a portion of the combined row to at least a portion of a corresponding row in a fact table associated with the dimension table, said fact data from said prior time period included in said corresponding row in said fact table.

13. The apparatus of claim 12, wherein the fact table contains information regarding past insurance policy marketing activities.

14. The apparatus of claim 12, wherein the fact table contains information regarding insurance policies.

15. A method of operating a computer system, the method comprising:

storing data in a data warehouse according to a schema in which each of a plurality of dimension tables is associated with a fact table, at least one of the dimension tables stored in a format having, for a given entity, at least two row entries;

the at least two row entries including a current row entry with a maximum effective end date and a prior row entry with an effective end date that is prior to a current point in time, the data warehouse including at least one data storage hardware unit;

generating a query on a terminal for application to the data warehouse, the query for effecting a self-join on a one of said dimension tables that is in said format to combine the current row entry for said given entity with at least one prior row entry for the given entity, the self-join producing a history restatement view, the query further joining the history restatement view to the fact table to produce a query view;

generating the query view from the data warehouse in response to the query;

transmitting the query view from the data warehouse to a user terminal; and

presenting the query view to a user at the user terminal;

wherein the query view is a report derived from said data stored in the data warehouse.

16. The method of claim 15, wherein said prior row entry and said current row entry each have a respective row identification key, the row identification keys being different from each other.

17. The method of claim 16, wherein the history restatement view associates a current value of an attribute of the given entity with a previous fact value for the given entity.

18. The method of claim 17, wherein the history restatement view contains a restatement view row entry that corresponds to the prior row entry, the restatement view row entry including both the row identification key of the current row entry and the row identification key of the prior row entry and having the current value of the attribute in place of a prior value of the attribute.

19. The method of claim 18, wherein the query view includes a query view row entry that includes said current value of the attribute of the given entity and the previous fact value for the given entity.

20. The method of claim 15, wherein the history restatement view is non-persistent.

21. The method of claim 15, wherein the fact table contains information regarding past insurance policy marketing activities.

22. The method of claim 15, wherein the fact table contains information regarding insurance policies.

23. The method of claim 15, wherein the query view is presented to the user as a report that is printed and/or displayed at the user terminal.