Methods and apparatus for mapping a hierarchical data structure to a flat data structure for use in generating a report

• US 7,925,658 B2
• Filed: 09/17/2004
• Issued: 04/12/2011
• Est. Priority Date: 09/17/2004
• Status: Active Grant

• Current Assignee: Open Text Holdings, Inc. (Open Text Corporation)
• Original Assignee: Actuate Corporation (Open Text Corporation)
• Inventors: Jain, Vishal, Colaco, Rohit
• Primary Examiner(s): Yen; Syling
• Application Number:
  US10/943,618
• Publication Number:
  US 20060064428A1
• Time in Patent Office: 2,398 Days
• Field of Search: 707/101, 707/102, 707/103.R, 707/3, 707/610, 707/640, 707/661, 707/674, 707/706, 707/722, 707/736, 707/752, 707/755, 707/756, 707/758, 707/781, 707/791, 707/802, 707/828, 707/999.101, 707/999.004, 709/223, 709/206, 717/100, 717/114
• US Class Current: 707/756
• CPC Class Codes: G06F 16/86 Mapping to a database

Abstract

Methods and apparatus for generating a report from hierarchically formatted data are disclosed. First, a data source including one or more files having a hierarchical file format is identified, where each of the files includes one or more data elements. A relational database model is obtained, where the relational database model includes one or more virtual tables, each of the virtual tables including one or more virtual columns. The relational database model is then mapped to the hierarchical file format. A query may be obtained using the virtual relational database. Data is then obtained from the virtual relational database corresponding to the query, thereby enabling a report including the data to be generated.

Claims

1. A method of generating a report, comprising:

obtaining a query via a graphical user interface using a virtual relational database such that data in a data source appears to be stored in a relational database, the data source including one or more files having a hierarchical file format, the virtual relational database including one or more virtual tables, each of the virtual tables including one or more virtual columns, wherein the query identifies at least one of the virtual tables and at least one of the virtual columns of the virtual relational database, wherein a mapping maps the virtual relational database to at least a portion of the data in the data source, wherein the virtual relational database does not store data;

obtaining data corresponding to the query using the mapping, wherein the data is not obtained from a relational database, wherein the data is not stored in a relational database;

generating a report including the obtained data;

wherein the hierarchical file format includes a plurality of data elements, each of the plurality of data elements having an element name;

wherein each of the virtual columns corresponds to one of the plurality of data elements, wherein generating a mapping comprises;

incrementing a number upon encountering one of the plurality of data elements or a closing tag for the one of the plurality of data elements, wherein the one of the plurality of data elements is associated with one of the virtual columns; and

inserting the number as a primary key into at least one of the files or an intermediary data structure such that the number identifies the one of the virtual columns.

2. The method as recited in claim 1, further comprising:

defining a relationship between the virtual tables in response to input received via the graphical user interface.

3. The method as recited in claim 2, wherein defining a relationship between the virtual tables comprises:

defining a virtual primary key.

4. The method as recited in claim 2, wherein defining a relationship between the virtual tables comprises:

defining a virtual foreign key.

5. The method as recited in claim 1, further comprising:

defining a column name for each of the virtual columns in response to input received via the graphical user interface.

6. The method as recited in claim 1, further comprising:

defining a table name for each of the virtual tables in response to input received via the graphical user interface.

7. The method as recited in claim 1, further comprising:

modifying one or more of the virtual tables in response to input received via the-graphical user interface.

8. The method as recited in claim 1, further comprising:

removing one or more of the virtual columns from one of the virtual tables in response to input received via the graphical user interface.

9. The method as recited in claim 1, further comprising:

adding one or more virtual columns to one of the virtual tables in response to input received via the graphical user interface.

10. The method as recited in claim 1, further comprising:

deleting one or more of the virtual tables in response to input received via the graphical user interface.

11. The method as recited in claim 1, wherein the data source comprises:

a plurality of files, wherein a first set of one or more of the files have a first hierarchical file format and wherein a second set of one more of the files have a second hierarchical file format, wherein the mapping is performed for the first set of files and the second set of files.

12. The method as recited in claim 11, wherein a first set of individuals has access to the first subset of the plurality of files and a second set of individuals has access to the second subset of the plurality of files, thereby restricting access to the data elements in the plurality of files.

13. The method as recited in claim 1, further comprising:

parsing the one or more files to obtain data corresponding to the one or more virtual columns.

14. The method as recited in claim 1, wherein the element name is associated with one or more paths in one or more of the files.

15. The method as recited in claim 14, wherein the element name corresponds to a single level in the hierarchical file format.

16. The method as recited in claim 14, wherein the element name corresponds to all levels in the hierarchical file format.

17. The method as recited in claim 14, further comprising:

creating a primary key for one of the virtual columns, the one of the virtual columns corresponding to one of the plurality of data elements;

storing a value of the primary key for the one of the virtual columns;

assigning the value to a foreign key; and

inserting the value of the foreign key into at least one of the plurality of files or an intermediary data structure;

wherein the value of the primary key and foreign key identify the one of the virtual columns.

18. The method as recited in claim 17, further comprising:

encountering the one of the plurality of data elements or a closing tag for the one of the plurality of data elements; and

incrementing the value of the primary key when the one of the plurality of data elements or closing tag for the one of the plurality of data elements is encountered.

19. The method as recited in claim 17, wherein creating, storing, assigning and inserting are performed for each of the plurality of data elements.

20. The method as recited in claim 1, further comprising generating the mapping, wherein generating the mapping comprises:

mapping each of the virtual tables and each of the virtual columns to an element name in response to input received via the graphical user interface.

21. The method as recited in claim 20, wherein each of the virtual tables has an associated table definition and each of the virtual columns has an associated column definition, wherein each table definition and column definition is defined at least in part by the corresponding element name.

22. The method as recited in claim 20, wherein each of the virtual tables has an associated table definition and each of the virtual columns has an associated column definition, wherein each table definition and column definition is defined at least in part by the element name.

23. The method as recited in claim 22, further comprising:

modifying a table definition such that the virtual table is defined by a different element name in response to input received via the graphical user interface.

24. The method as recited in claim 22, further comprising:

modifying a column definition such that the virtual column is defined by a different element name in response to input received via the graphical user interface.

25. The method as recited in claim 1, wherein each of the virtual columns and virtual tables has an associated name that has been received via the graphical user interface.

26. The method as recited in claim 1, further comprising:

adding a column definition defining a virtual column in response to input received via the graphical user interface.

27. The method as recited in claim 1, further comprising:

deleting a table definition defining a virtual table in response to input received via the graphical user interface.

28. The method as recited in claim 1, further comprising:

deleting a column definition defining a virtual column in response to input received via the graphical user interface.

29. The method as recited in claim 1, wherein inserting the number as a primary key comprises:

inserting a virtual primary key into at least one of the files, wherein the virtual primary key is user defined.

30. The method as recited in claim 1, further comprising:

inserting the number as a foreign key into at least one of the files, wherein the foreign key identifies the one of the virtual columns.

31. The method as recited in claim 1, wherein obtaining data comprises:

obtaining the data from the files having the hierarchical file format.

32. The method as recited in claim 1, wherein obtaining data comprises:

obtaining the data from an intermediary data structure in which the mapping has been generated.

33. The method as recited in claim 1, wherein the query identifies one or more of the virtual tables and one or more of the virtual columns in the virtual relational database.

34. The method as recited in claim 1, wherein obtaining data comprises:

obtaining data from the data source mapped to the relational database model.

35. The method as recited in claim 1, wherein a tree data structure maps data from the data source to the virtual relational database, wherein obtaining data comprises:

obtaining data from the tree data structure corresponding to the query.

36. The method as recited in claim 35, wherein the data from the data source that is mapped to the virtual relational database is limited to data obtained from the data source corresponding to the query.

37. The method as recited in claim 35, wherein the data from the data source that is mapped to the virtual relational database includes all data in the data source.

38. The method as recited in claim 1, wherein the data from the data source that is mapped to the virtual relational database is limited to data obtained from the data source corresponding to the query.

39. The method as recited in claim 1, wherein the data from the data source that is mapped to the virtual relational database includes all data in the data source.

40. The method as recited in claim 1, further comprising:

presenting the relational database model via the graphical user interface, enabling a user to submit a query using the relational database model.

41. The method as recited in claim 1, further comprising:

presenting one or more of the virtual columns of the virtual relational database via the graphical user interface.

42. The method as recited in claim 41, wherein presenting one or more of the virtual columns comprises:

hiding at least one of the virtual columns.

43. The method as recited in claim 41, wherein all of the virtual columns are not presented via the graphical user interface.

44. The method as recited in claim 1, wherein the query establishes a format of the report to be generated from what appears to be a relational database.

45. The method as recited in claim 44, further comprising:

presenting the format of the report to be generated, thereby enabling a user to view and establish the format of the report to be generated from what appears to be a relational database.

46. The method as recited in claim 1, further comprising:

assigning the number to a foreign key; and

inserting the number assigned to the foreign key into at least one of the plurality of files or an intermediary data structure.

47. A computer-readable medium storing thereon computer-readable instructions that when executed on a computer cause the computer to generate a report, comprising:

instructions for obtaining a relational database model, the relational database model including one or more virtual tables, each of the virtual tables including one or more virtual columns;

instructions for generating a mapping between the relational database model and at least a portion of data in a data source, the data source including one or more files having a hierarchical file format, each of the files including one or more data elements;

instructions for obtaining a query via a graphical user interface using the relational database model such that the data appears to be stored in a relational database, wherein the query identifies at least one of the virtual tables and at least one of the virtual columns, wherein the relational database model does not store data;

instructions for obtaining data corresponding to the query using the mapping, wherein the data is not obtained from a relational database, wherein the data is not stored in a relational database; and

instructions for generating a report including the obtained data;

wherein the hierarchical file format includes a plurality of data elements, each of the plurality of data elements having an element name;

wherein each of the virtual columns corresponds one of the plurality of data elements, wherein the instructions for generating a mapping further comprises;

instructions for incrementing a number upon encountering one of the plurality of data elements or a closing tag for the one of the plurality of data elements, the one of the plurality of data elements being associated with one of the virtual columns; and

instructions for inserting the number as a primary key into at least one of the files or an intermediary data structure, wherein the number identifies the one of the virtual columns.

48. The computer-readable medium as recited in claim 47, wherein the plurality of data elements are nested to indicate hierarchical relationships between the plurality of data elements.

49. The computer-readable medium as recited in claim 47, wherein the instructions for generating a mapping comprises:

instructions for generating a tree data structure mapping the relational database model to the hierarchical file format.

50. The computer-readable medium as recited in claim 47, further comprising:

instructions for inserting the number as a foreign key into at least one of the files or an intermediary data structure, wherein the foreign key identifies the one of the virtual columns.

51. An apparatus for generating a report, comprising:

a processor; and

a memory, at least one of the processor and the memory being adapted for;

obtaining a relational database model, the relational database model including one or more virtual tables, each of the virtual tables including one or more virtual columns;

generating a mapping between the relational database model and at least a portion of data in a data source, the data source including one or more files having a hierarchical file format, each of the files including one or more data elements;

obtaining a query via a graphical user interface using the relational database model such that the data appears to be stored in a relational database, wherein the query identifies at least one of the virtual tables and at least one of the virtual columns, wherein the relational database model does not store data, wherein the data is not stored in a relational database;

obtaining data corresponding to the query using the mapping, wherein the data is not obtained directly or indirectly from a relational database; and

generating a report including the obtained data;

wherein the hierarchical file format includes a plurality of data elements, each of the plurality of data elements having an element name;

wherein each of the virtual columns corresponds to one of the plurality of data elements, wherein generating a mapping further comprises;

incrementing a number upon encountering one of the plurality of data elements corresponding with one of the virtual columns or upon encountering a closing tag for the one of the plurality of data elements corresponding with the one of the virtual columns; and

inserting the number associated with the one of the virtual columns as a primary key into at least one of the files or an intermediary data structure, the primary key identifying the one of the virtual columns.

52. The apparatus as recited in claim 51, wherein generating a mapping comprises:

generating a tree data structure mapping the relational database model to at least a portion of data from the data source.

53. A method of generating a report, comprising:

identifying a data source including one or more files having a hierarchical file format including data;

obtaining a relational database model, the relational database model including one or more virtual tables, each of the virtual tables including one or more virtual columns, wherein the relational database model does not store the data;

mapping the relational database model to the hierarchical file format by mapping each of the virtual tables and each of the virtual columns to an element name or path in the corresponding file in response to input received via a graphical user interface, wherein the data is not stored in a relational database;

receiving a query that identifies at least one of the virtual tables and at least one of the virtual columns of the relational database model; and

generating a report in response to the query;

wherein the files include a plurality of data elements, each of the plurality of data elements having an element name;

wherein each of the virtual columns corresponds to one of the plurality of data elements, wherein mapping the relational database model to the hierarchical file format comprises;

incrementing a number upon encountering one of the plurality of data elements in at least one of the files or upon encountering a closing tag for the one of the plurality of data elements in at least one of the files, the one of the plurality of data elements corresponding with one of the virtual columns; and

inserting the number as a primary key into the at least one of the files or an intermediary data structure, the primary key identifying the one of the virtual columns.

54. The method as recited in claim 53, wherein a column name associated with each of the virtual columns corresponds to an element name of one of the plurality of data elements.

55. The method as recited in claim 53, further comprising:

adding a table definition defining a virtual table in response to input received via the graphical user interface.

56. The method as recited in claim 53, wherein the query establishes a format of the report to be generated from what appears to be a relational database.

57. The method as recited in claim 56, further comprising:

presenting the format of the report to be generated, thereby enabling a user to view and establish the format of the report to be generated from what appears to be a relational database.

58. The method as recited in claim 53, wherein the query is received such that the data appears to be stored in a relational database.

59. The method as recited in claim 53, further comprising:

presenting the virtual tables and virtual columns via a graphical user interface.

60. The method as recited in claim 59, wherein the virtual tables represent tables in a relational database.

61. The method as recited in claim 53, wherein the virtual tables represent tables in a relational database.

62. The method as recited in claim 53, wherein mapping the relational database model to the hierarchical file format further comprises:

inserting the number as a foreign key into at least one of the files or an intermediary data structure, wherein the foreign key identifies the one of the virtual columns.