Secure virtual data warehousing system and method

• US 7,519,608 B2
• Filed: 03/22/2005
• Issued: 04/14/2009
• Est. Priority Date: 03/22/2004
• Status: Active Grant

• Current Assignee: Sliccware
• Original Assignee: Sliccware
• Inventors: Foster, Randall J.
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): Thai; Hanh B
• Application Number:
  US11/085,553
• Publication Number:
  US 20050210049A1
• Time in Patent Office: 1,484 Days
• Field of Search: 707100-102, 707/104.1, 707 1- 3
• US Class Current: 1/1
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

The invention advances the technology of virtual data warehousing: by allowing data to be organized in any of a number of virtual structures without the need for replication; by allowing the data to be accessed through the virtual structure at speeds in excess of those for conventional methods; by allowing data to be searched at significantly higher speeds, using a new indexing technology that enables tables to be indexed by data external to themselves, thus removing the need for complex joins; and by providing a workable security model for compartmenting access to both the data and the indices used to support searching.

Claims

1. A computer implementing a virtual data warehouse stored on a computer-useable medium, comprising:

a plurality of virtual tables, each including one or more records, each virtual table representative of a conventional table in a remote database and each record within the virtual tables corresponding to a record within the represented conventional table and containing an identity value for accessing the corresponding record;

a plurality of hyper-keys, each able to be resolved from look-up values into direct virtual links to target records as soon as the look-up values are entered into the warehouse, each hyper-key being used for linking records in one of the plurality of virtual tables to one or more records in a specified other virtual table from the plurality of virtual tables;

at least one virtual table with one or more records, each record containing one or more hyper-keys, each hyper-key used to access one or more records in one of the virtual tables from the plurality of virtual tables representing a conventional table in a remote database;

at least one virtual table index that is a conventional index created from data extracted from the records within a conventional table in a remote database and used to access the corresponding records within the virtual table representing the conventional table; and

at least one hyper-index, which is a conventional index, derived from data exported from a virtual table index and used to access records within a virtual table other than the virtual table associated with the virtual table index by replacing, within a list of record identity values or similar structure associated with an indexed record, the record identity values from a first virtual table with record identity values for the records within a second virtual table that are linked by hyper-keys to the records in the first virtual table.

2. The computer implementing the virtual data warehouse of claim 1, wherein the at least one hyper-index is derived from data represented in an index of the first virtual table.

3. The computer implementing the virtual data warehouse of claim 2, wherein the original structure of the first virtual table index is maintained.

4. The computer implementing the virtual data warehouse of claim 2, wherein the at least one hyper-index is automatically updated to reflect changes in the plurality of virtual tables linked via the plurality of hyper-keys.

5. The computer implementing the virtual data warehouse of claim 1, wherein each of the plurality of virtual tables includes an access mask that identifies one or more entities with access rights to the corresponding virtual table.

6. The computer implementing the virtual data warehouse of claim 5, wherein each of the plurality of virtual tables is accessed via a request that includes a group mask that lists one or more entities that match at least one of the one or more entities listed in the access mask of the corresponding virtual table.

7. The computer implementing the virtual data warehouse of claim 6, wherein the at least one hyper-index restricts access to the first and second virtual tables according to the access masks of the first and second virtual tables and intervening tables and the group masks of the requests.

8. The computer implementing the virtual data warehouse of claim 1, wherein the plurality of hyper-keys indirectly link the first and second virtual tables via additional virtual tables.

9. The computer implementing the virtual data warehouse of claim 1, wherein the plurality of hyper-keys directly link the first and second virtual tables.

10. The computer implementing the virtual data warehouse of claim 1, wherein the plurality of hyper-keys are automatically updated to reflect changes in the remote database tables corresponding to the plurality of virtual tables containing the plurality of hyper-keys.

11. The computer implementing the virtual data warehouse of claim 1, wherein the plurality of hyper-keys includes local hyper-keys for export to other virtual tables and foreign hyper-keys for linking to records in other virtual tables.

12. A computer program product, comprising:

a computer-useable medium upon which computer program logic is recorded, the computer program logic configured to create and maintain a virtual data warehouse stored on said computer-useable medium, comprising;

virtual tables, each representative of a conventional table in a remote database accessible by the computer upon which the virtual data warehouse is created, with each record within the virtual table corresponding to a record within the represented conventional table and containing an identity value for accessing the corresponding record;

hyper-keys, which can be resolved from look-up values into direct virtual links to target records as the look-up values are entered into the warehouse, each hyper-key being used for linking records in one of the virtual tables to one or more records in a specified other virtual table;

at least one virtual table with one or more records, each record containing one or more hyper-keys, each hyper-key used to access one or more records in a virtual table representing a conventional table in a remote database;

a virtual table index that is a conventional index created from data extracted from the records within a conventional table in a remote database and used to access the corresponding records within the virtual table representing that conventional table; and

a hyper-index, which is a conventional index, derived from data exported from a virtual table index and used to access records within a virtual table other than the virtual table associated with the virtual table index by replacing, within a list of record identity values or similar structure associated with an indexed record, the record identity values from a first virtual table with record identity values for the records within a second virtual table that are linked by hyper-keys to the records in the first virtual table.

13. The computer program product of claim 12, wherein the hyper-index is derived from data represented in a first virtual table index.

14. The computer program product of claim 13, wherein an original structure of the first virtual table index is maintained.

15. The computer program product of claim 13, wherein the hyper-index is updated to reflect changes in the virtual tables linked via the hyper-keys.

16. The computer program product of claim 12, wherein the virtual tables each include an access mask that identifies one or more entities with access rights to the corresponding virtual table.

17. The computer program product of claim 16, wherein the virtual tables are accessed via a request that includes a group mask that lists one or more entities that match at least one of the one or more entities listed in the access mask of the corresponding virtual table.

18. The computer program product of claim 17, wherein the hyper-index restricts access to the virtual tables linked by the hyper-keys according to the access masks of the virtual tables and the group masks of the requests.

19. The computer program product of claim 12, wherein the hyper-keys are updated to reflect changes in the remote database tables corresponding to the virtual tables containing the hyper-keys.

20. The computer program product of claim 12, wherein the hyper-keys include local hyper-keys for export to other virtual tables and foreign hyper-keys for linking to records in other virtual tables.

21. A method for building a virtual data warehouse, the method comprising:

building virtual tables that each include one or more records, each virtual table representative of a conventional table in a remote database and each record within the virtual tables corresponding to a record within the represented conventional table and containing an identity value for accessing the corresponding record;

building virtual tables that each include one or more records, each record containing one or more hyper-keys, each hyper-key used to access one or more records in a virtual table representing a conventional table in a remote database;

linking records from different virtual tables via one or more hyper-keys which are resolved into virtual links for fast access;

building one or more virtual table indexes to allow indexing and searching of data represented in the records of a virtual table without requiring storage of or access to data in the corresponding conventional table, each virtual table index being derived from data extracted from records within the conventional table represented by the virtual table, the records having corresponding records within the virtual table; and

building at least one hyper-index to allow searching of data represented by records in various virtual tables linked to records in a single virtual table by the hyper-keys as though the data was actually within the records of the single virtual table, each hyper-index being derived from data exported from a virtual table index and used to access records within a virtual table other than the virtual table associated with the virtual table index by replacing, within the list of record identity values or similar structure associated with an indexed record, the record identity values from a first virtual table with record identity values for the records within a second virtual table that are linked by hyper-keys to the records in the first table.

22. The method of claim 21, further comprising automatically updating the hyper-keys when the remote database tables corresponding to the virtual tables containing the hyper-keys are changed.

23. The method of claim 22, further comprising automatically updating the at least one hyper-index when the hyper-keys are updated.

24. The method of claim 21, further comprising applying an access mask to each virtual table to restrict access to each table according to a list of one or more entities identified in the access mask.

25. The method of claim 24, further comprising updating the at least one hyper-index to reflect the access masks applied to each virtual table.

26. A method of searching a virtual data warehouse according to a user'"'"'s search request in order to return and display data to the user via a computer in response to the search request, the method comprising:

using one or more virtual table indexes to retrieve records from a single virtual table, the single virtual table corresponding to a conventional table in a remote database where actual data is stored;

using one or more hyper-indexes to retrieve records from the virtual table, each hyper-index allowing the searching of data represented by records in various virtual tables linked to records in the single virtual table by hyper-keys as though the data was actually within the records of the single virtual table; and

using one or more hyper-keys to link records from one or more virtual tables, the one or more virtual tables corresponding to one or more conventional tables in remote databases where actual data is stored.

27. The method of claim 26, wherein two or more hyper-indexes used to retrieve records from the same virtual table provides support for simple parametric queries involving data represented in the corresponding virtual tables associated with the virtual table indexes from which the hyper-indexes were derived.

28. The method of claim 26, further comprising using a group mask in the search request to identify the groups requesting the search.

29. The method of claim 28, further comprising using an access mask for each of the one or more virtual tables linked by the hyper-keys, the access masks restricting access to the corresponding virtual tables to a list of entities identified by the access masks.

30. The method of claim 29, further comprising granting access to a virtual table for only the groups listed in the search request group mask that match the entities identified by the access mask of the virtual table being accessed.