Database Hierarchy-Independent Data Drilling

• US 20150006507A1
• Filed: 06/26/2014
• Published: 01/01/2015
• Est. Priority Date: 06/30/2013
• Status: Active Grant

• Current Assignee: Datawalk Spolka Akcyjna
• Original Assignee: DataWalk SA
• Inventors: PIECKO, Krystian
• Granted Patent: US 10,242,056 B2
• Time in Patent Office: Days
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/26 Visual data mining; Browsing structured data

Abstract

Computer implemented methods for database hierarch-independent data drilling are presented including: a. selecting one of at least two database structures each having data, where the at least two database structures include, at least two distinct paths that lead from a first of the at least two database structures to a second of the at least two database structures, where a path logically relates at least two data structures which are otherwise directly unrelated using data structures and data structures'"'"' relations, and a database information schema that provides information about the at least two database structures including information regarding relations between data structures; b. setting the selected database structure as a current data drilling state; and c. finding at least one related data structure corresponding with the selected database structure for the current data drilling state utilizing the database information schema.

Claims

1. A computer implemented method for database hierarch-independent data drilling comprising:

a. selecting one of at least two database structures each having data, wherein the at least two database structures include,

at least two distinct paths that lead from a first of the at least two database structures to a second of the at least two database structures, wherein a path logically relates at least two data structures which are otherwise directly unrelated using data structures and data structures'"'"' relations, and

a database information schema that provides information about the at least two database structures including information regarding relations between data structures;

b. setting the selected database structure as a current data drilling state;

c. finding at least one related data structure corresponding with the selected database structure for the current data drilling state utilizing the database information schema;

d. selecting at least one object from the selected database structure;

e. selecting at least one related data structure;

f. querying the at least one related data structure using the selected object as a parameter; and

g. obtaining the query results from the querying.

2. The method of claim 1, further comprising:

h. setting the at least one related data structure as the current data drilling state; and

i. iteratively executing steps (c) to (g) for the current data drilling state until at least one of the two distinct paths has been data drilled.

3. The method of claim 1 wherein the at least two database structures comprise a data table.

4. The method of claim 1 wherein the at least one related data structure is identified by a relation.

5. The method of claim 3 wherein the finding at least one related data structures utilizes a primary key and a foreign key defined in the database information schema.

6. The method of claim 5 wherein the finding at least one related data structures, comprises the steps of:

listing all foreign keys from the database information schema;

selecting only those foreign keys from the listing wherein the primary key of the selected table is present, wherein if a selected foreign key is a primary key of the selected table, then the selected table is an intermediate relation table; and

retrieving, for each intermediate relation table, from the information schema, all foreign keys and tables to which the selected foreign key refers.

7. The method of claim 6 further comprising:

outputting all discovered paths to other data tables other than intermediate relation tables;

selecting of one of the other data tables;

selecting records from the one data table selected; and

passing data of the selected records as parameters to queries for records from the one data table selected from the other data tables.

8. The method of claim 1 wherein the database comprises:

a first data structure stored in a memory, having a definition of at least one data set wherein each data set includes a data set identifier and logically holds data objects of the same type;

a second data structure, stored in the memory, having definitions of properties of objects wherein each property includes an identifier of the property and an identifier of a set, from the first data structure, the property may be assigned to;

a third data structure, stored in the memory, having definitions of objects wherein each object includes an identifier and an identifier of a set, from the first data structure, the object may be assigned to;

a fourth data structure, stored in the memory, having definitions of properties of each object wherein each property of an object associates a value with an object, from the third data structure, and a property of the set, from the second data structure, the object may be assigned to.

9. The method of claim 8, wherein the database further comprises:

a fifth data structure, stored in the memory, having definitions of relations wherein each relation includes an identifier of the relation; and

a sixth data structure, stored in the memory, for storing definitions of relations between objects wherein each objects relation associates a relation, from the fifth data structure, to two objects from the third data structure.

10. The method of claim 9, wherein the database further comprises:

a seventh data structure, stored in memory for storing definitions of relations between respective data sets of the at least one data set, wherein each set relation associates a relation from the fifth data structure and the first data structure.

11. The method of claim 10 wherein,

the first data structure is a set data structure, wherein

the second data structure is a column data structure, wherein

the third data structure is an object data structure, wherein

the fourth data structure is a characteristics data structure, wherein

the fifth data structure is a relations data structure, wherein

the sixth data structure is an object relations data structure, and wherein

the seventh data structure is a set relations data structure.

12. The method of claim 10 wherein the finding at least one related data structures comprises:

finding a currently selected set'"'"'s id in the first data structure;

finding identifiers of sets related to the selected set'"'"'s id in the seventh data structure; and

determining identifiers of sets related to the selected set'"'"'s id in the seventh data structure.

13. A computing device program product for database hierarch-independent data drilling using a computing device, the computing device program product comprising:

a non-transitory computer readable medium;

a. first programmatic instructions for selecting one of at least two database structures each having data, wherein the at least two database structures include,

at least two distinct paths that lead from a first of the at least two database structures to a second of the at least two database structures, wherein a path logically relates at least two data structures which are otherwise directly unrelated using data structures and data structures'"'"' relations, and

a database information schema that provides information about the at least two database structures including information regarding relations between data structures;

b. second programmatic instructions for setting the selected database structure as a current data drilling state;

c. third programmatic instructions for finding at least one related data structure corresponding with the selected database structure for the current data drilling state utilizing the database information schema;

d. fourth programmatic instructions for selecting at least one object from the selected database structure;

e. fifth programmatic instructions for selecting at least one related data structure;

f. sixth programmatic instructions for querying the at least one related data structure using the selected object as a parameter; and

g. seventh programmatic instructions for obtaining the query results from the querying.

14. The method of claim 13, further comprising:

h. eighth programmatic instructions for setting the at least one related data structure as the current data drilling state; and

i. iteratively executing steps (c) to (g) for the current data drilling state until at least one of the two distinct paths has been data drilled.

15. The method of claim 13 wherein the at least two database structures comprise a data table.

16. The method of claim 13 wherein the at least one related data structure is identified by a relation.

17. The method of claim 15 wherein the finding at least one related data structures utilizes a primary key and a foreign key defined in the database information schema.

18. The method of claim 17 wherein the third programmatic instructions for finding at least one related data structures, comprises the steps of:

listing all foreign keys from the database information schema;

selecting only those foreign keys from the listing wherein the primary key of the selected table is present, wherein if a selected foreign key is a primary key of the selected table, then the selected table is an intermediate relation table; and

retrieving, for each intermediate relation table, from the information schema, all foreign keys and tables to which the selected foreign key refers.

19. The method of claim 18 further comprising:

outputting all discovered paths to other data tables other than intermediate relation tables;

selecting of one of the other data tables;

selecting records from the one data table selected; and

passing data of the selected records as parameters to queries for records from the one data table selected from the other data tables.

20. The method of claim 13 wherein the database comprises:

a first data structure stored in a memory, having a definition of at least one data set wherein each data set includes a data set identifier and logically holds data objects of the same type;

a second data structure, stored in the memory, having definitions of properties of objects wherein each property includes an identifier of the property and an identifier of a set, from the first data structure, the property may be assigned to;

a third data structure, stored in the memory, having definitions of objects wherein each object includes an identifier and an identifier of a set, from the first data structure, the object may be assigned to;

a fourth data structure, stored in the memory, having definitions of properties of each object wherein each property of an object associates a value with an object, from the third data structure, and a property of the set, from the second data structure, the object may be assigned to.