MANAGEMENT OF TIME-VARIANT DATA SCHEMAS IN DATA WAREHOUSES

• US 20090216789A1
• Filed: 03/30/2009
• Published: 08/27/2009
• Est. Priority Date: 06/15/2006
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: CHOWDHARY, PAWAN R., Mihaila, Gaorge Andrei
• Granted Patent: US 8,688,658 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A system, method, and computer readable medium for preserving information in time variant data schemas are disclosed. The method includes determining if at least one modification request associated with a database schema has been received. In response to the modification request being received, a metadata table associated with the database schema is updated to include at least one entry associated with the modification request. The entry identifies an instance in time when an action associated with the modification request was performed.

Claims

1. A method, on an information processing system, for preserving information in time variant data schemas, the method comprising:

determining if at least one modification request associated with a database schema has been received;

updating, in response to the modification request being received, a metadata table associated with the database schema to include at least one entry associated with the modification request, wherein the entry identifies an instance in time when an action associated with the modification request was performed.

2. The method of claim 1, wherein the entry is associated with one of a column and a table in the data schema.

3. The method of claim 1, further comprising:

determining a modification associated with the modification request; and

modifying the modification request, based on the modification to be performed, to add the entry to the metadata table.

4. The method of claim 3, wherein the modifying the modification request further prevents the modification associated with the modification request from deleting at least one of a column and table from the data schema.

5. The method of claim 1, wherein the modification request is at least one of:

adding a table to the database schema;

deleting a table from the database schema;

renaming a table in the database schema;

adding an entry to a table in the database schema;

deleting an entry from the database schema; and

renaming an entry in the database schema.

6. The method of claim 3, wherein if the modification associated with the modification request is determined to be for adding a column to a table in the database schema, the modifying the modification request further comprises:

setting a time stamp to reflect a time when the column was added to the database schema;

in response to the modification associated with the modification request being determined to be for deleting a column from the database schema, the modifying further comprises;

modifying the modification request to set a time stamp to reflect a time when the column became inactive; and

modifying the modification request so that deletion of the column from the database schema is prevented;

in response to the modification request being for renaming a column in the database schema, the modifying further comprises;

adding a new row comprising at least a new column name associated with the column;

adding a time stamp to the new row reflecting a time when the new column name was created; and

adding an entry to a row corresponding to the column reflecting a time stamp when a name of the column became inactive.

7. The method of claim 3, wherein in response to the modification associated with the modification request being determined to be for adding a table to the database schema, the modifying the modification request further comprises:

setting a time stamp the entry to reflect a time when the table was added to the database schema;

in response to the modification associated with the modification request being determined to be for deleting a table from the database schema, the modifying the modification request further comprises;

setting a time stamp to reflect a time when the table became inactive; and

modifying the modification request so that deletion of the table from the database schema is prevented;

in response to the modification request being for renaming a table in the database schema, the modifying the modification request further comprises;

adding a new row comprising at least a new table name associated with the table;

adding a time stamp to the new row reflecting a time wherein the new table name was created; and

adding a time stamp to a row corresponding to the table reflecting a time when a name of the table became inactive.

8. A system for preserving information in time variant data schemas, the system comprising:

at least one information processing system, the information processing system including;

a data schema modification request analyzer for;

determining if at least one modification request associated with a database schema has been received;

determining, in response to the modification request being received, a modification associated with the modification request;

a data schema modification request modifier for;

modifying the modification request, based on the modification to be performed, to update a metadata table associated with the database schema to include at least one entry associated with the modification request, wherein the entry identifies an instance in time when an action associated with the modification request was performed.

9. The system of claim 8, wherein the entry is associated with one of a column and a table in the data schema.

10. The system of claim 8, wherein the modifying the modification request by the data schema modification request modifier further prevents the modification associated with the modification request from deleting at least one of a column and table from the data schema.

11. The system of claim 8, wherein the modification request is at least one of:

adding a table to the database schema;

deleting a table from the database schema;

renaming a table in the database schema;

adding an entry to a table in the database schema;

deleting an entry from the database schema; and

renaming an entry in the database schema.

12. The system of claim 8, wherein if the modification associated with the modification request is determined to be for adding a column to a table in the database schema, the data schema modification request modifier modifies the modification request to:

set a time stamp to reflect a time when the column was added to the database schema;

in response to the modification associated with the modification request being determined to be for deleting a column from the database schema, the data schema modification request modifier modifies the modification request to;

set a time stamp to reflect a time when the column became inactive and so that deletion of the column from the database schema is prevented;

in response to the modification request being for renaming a column in the database schema, the data schema modification request modifier modifies the modification request to;

add a new row comprising at least a new column name associated with the column;

add a time stamp to the new row reflecting a time when the new column name was created; and

add an entry to a row corresponding to the column reflecting a time stamp when a name of the column became inactive.

13. The system of claim 8, wherein in response to the modification associated with the modification request being determined to be for adding a table to the database schema, the data schema modification request modifier modifies the modification request to:

set a time stamp the entry to reflect a time when the table was added to the database schema;

in response to the modification associated with the modification request being determined to be for deleting a table from the database schema, the data schema modification request modifier modifies the modification request to;

set a time stamp to reflect a time when the table became inactive and so that deletion of the table from the database schema is prevented;

in response to the modification request being for renaming a table in the database schema, the data schema modification request modifier modifies the modification request to;

add a new row comprising at least a new table name associated with the table;

add a time stamp to the new row reflecting a time wherein the new table name was created; and

add a time stamp to a row corresponding to the table reflecting a time when a name of the table became inactive.

14. A computer readable medium for preserving information in time variant data schemas, the computer readable medium comprising instructions for:

determining if at least one modification request associated with a database schema has been received;

updating, in response to the modification request being received, a metadata table associated with the database schema to include at least one entry associated with the modification request, wherein the entry identifies an instance in time when an action associated with the modification request was performed.

15. The computer readable medium of claim 14, wherein the entry is associated with one of a column and a table in the data schema.

16. The computer readable medium of claim 14, further comprising instructions for:

determining a modification associated with the modification request; and

modifying the modification request, based on the modification to be performed, to add the entry to the metadata table.

17. The computer readable medium of claim 16, wherein the modifying the modification request further prevents the modification associated with the modification request from deleting at least one of a column and table from the data schema.

18. The computer readable medium of claim 14, wherein the modification request is at least one of:

adding a table to the database schema;

deleting a table from the database schema;

renaming a table in the database schema;

adding an entry to a table in the database schema;

deleting an entry from the database schema; and

renaming an entry in the database schema.

19. The computer readable medium of claim 16, wherein if the modification associated with the modification request is determined to be for adding a column to a table in the database schema, the modifying the modification request further comprises:

setting a time stamp to reflect a time when the column was added to the database schema;

in response to the modification associated with the modification request being determined to be for deleting a column from the database schema, the modifying further comprises;

modifying the modification request to set a time stamp to reflect a time when the column became inactive; and

modifying the modification request so that deletion of the column from the database schema is prevented;

in response to the modification request being for renaming a column in the database schema, the modifying further comprises;

adding a new row comprising at least a new column name associated with the column;

adding a time stamp to the new row reflecting a time when the new column name was created; and

adding an entry to a row corresponding to the column reflecting a time stamp when a name of the column became inactive.

20. The computer readable medium of claim 16, wherein in response to the modification associated with the modification request being determined to be for adding a table to the database schema, the modifying the modification request further comprises:

setting a time stamp the entry to reflect a time when the table was added to the database schema;

in response to the modification associated with the modification request being determined to be for deleting a table from the database schema, the modifying the modification request further comprises;

setting a time stamp to reflect a time when the table became inactive; and

modifying the modification request so that deletion of the table from the database schema is prevented;

in response to the modification request being for renaming a table in the database schema, the modifying the modification request further comprises;

adding a new row comprising at least a new table name associated with the table;

adding a time stamp to the new row reflecting a time wherein the new table name was created; and

adding a time stamp to a row corresponding to the table reflecting a time when a name of the table became inactive.