Database management method and apparatus

• US 6,704,742 B1
• Filed: 07/03/2001
• Issued: 03/09/2004
• Est. Priority Date: 07/03/2001
• Status: Expired due to Term

• Current Assignee: Johnson Controls Tyco IP Holdings LLP (Johnson Controls International plc)
• Original Assignee: Johnson Controls Technology Company (Johnson Controls International plc)
• Inventors: Huth, William A., Ignasiak, John C., St. John, Diana R., Blaha, Michael R., Michals, Steven A., Schuster, Tobin J.
• Primary Examiner(s): Mizrahi, Diane D.
• Assistant Examiner(s): Mofiz, Apu M
• Application Number:
  US09/898,563
• Time in Patent Office: 980 Days
• Field of Search: 707/1, 707/3, 707/6, 707/4, 707/10, 707/101, 707/102, 707/104.1, 707/203, 434/362, 709/212, 709/213, 709/216, 709/217
• US Class Current: 707/802
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: Y10S 707/956 Hierarchical
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method and apparatus for arranging and accessing database data in a manner such that massive amounts of data can be aggregated and manipulated in many different ways to generate reports of many different types in a rapid manner, the method including storing data in point slices where each slice includes data having similar attributes, receiving a report request from which data attributes corresponding to the data needed to instantiate the report can be gleaned, identifying at least one required point slice including the needed data, determining if the point slice exists, where the point slice does not exist, accessing other data and generating the point slice and perhaps some intervening point slices, storing the newly generated point slices and then using the required point slice to instantiate and provide the report.

Claims

1. A method to be used with a database and an interface, the interface for specifying reports to be generated, the database capable of including a hierarchy of point slices where each point slice includes a data segment and at least a subset of the data segments can be combined to instantiate each specified report, the method for expediting the report generating process and comprising the steps of:

(a) receiving a report request;

(b) identifying at least one required point slice required to instantiate the report;

(c) searching the database for the at least one required point slice;

(d) where the at least one required point slice does not exist, identifying an intermediate subset that includes intermediate point slices required to generate the at least one required point slice;

(e) determining if the intermediate subset point slices exist;

(f) for each non-existent intermediate subset point slice, using existing point slices to generate the non-existent intermediate point slice;

(g) combining the intermediate subset point slices to generate the at least one required point slice; and

(h) instantiating the report using the at least one required point slice.

2. The method of claim 1 further including the step of, after generating the non-existent required point slice, storing the generated required point slice for use when subsequent reports are requested.

3. The method of claim 1 further including the step of, after generating the non-existent intermediate subset point slices, storing the generated intermediate subset point slices for use when subsequent reports are requested.

4. The method of claim 1 wherein the step of using the existing segments to generate the non-existing intermediate subset point slices includes, for each non-existing intermediate subset point slice, repeating steps (c) through (g) with the intermediate point slice as the required point slice to determine the intermediate point slice.

5. The method of claim 4 wherein at least a subset of the generated intermediate subset point slices are stored for subsequent use.

6. The method of claim 4 wherein every generated intermediate subset point slice is stored for subsequent use.

7. The method of claim 1 wherein the step of identifying at least one required point slice needed to instantiate the report includes receiving a report request and parsing the report request to identify the at least one point slice.

8. The method of claim 7 for use in an enterprise automation system including at least one unit, the unit including at least one data source element and wherein each point slice includes data corresponding to a specific function, a specific period and specific intervals within the period for a specific unit and wherein the step of receiving includes receiving an indication of a particular unit, a time period for which to report data corresponding to the unit, an interval and a function to be performed on data corresponding to the unit, the step of identifying including identifying a point slice corresponding to the specified unit, function, time period and interval.

9. The method of claim 8 wherein the step of identifying further includes identifying relevant data types required to perform the specified function, identifying relevant unit source elements that provide the relevant data types and identifying relevant point slices corresponding to the relevant units, interval, time period and 5 function.

10. The method of claim 9 wherein the step of combining the point slices includes combining data segments corresponding to the relevant units according to the specified function.

68. The apparatus of claim 6 wherein every generated intermediate subset point slice is stored for subsequent use.

11. A method to be used with a database and an interface, the interface for specifying reports to be generated, the database capable of storing a hierarchy of points slices where each point slice corresponds to a data segment and where a subset of the data segments can be combined to instantiate each specified report, the method for automatically modifying a database structure as a function of specified reports to speed subsequent report generation and comprising the steps of:

(a) receiving a report request;

(b) identifying at least one required point slice needed to instantiate the report;

(c) searching the database for the at least one required point slice;

(d) where the at least one required point slice does not exist, identifying an intermediate subset that includes intermediate point slices required to generate the at least one required point slice;

(e) determining if the intermediate subset point slices exist;

(f) for each non-existent intermediate subset point slice, using existing point slices to generate the non-existent intermediate subset point slice;

(g) combining the intermediate subset point slices to generate the at least one required point slice; and

(h) storing the at least one required point slice for use when subsequent reports are requested.

12. The method of claim 11 wherein the step of using the existing point slices to generate the non-existing intermediate subset point slices includes, for each non-existing intermediate subset point slice, repeating steps (c) through (g) with the intermediate point slice as the required point slice to determine the intermediate point slice.

13. The method of claim 12 further including storing every generated point slice for subsequent use.

14. A database for use with a processor, an interface and a plurality of source elements, the interface for specifying report requests, each report request including request information that can be used to identify point slices and corresponding data values where a sub-set of the data values are needed to instantiate each report, the source elements periodically generating raw data values and providing the raw data values to the processor, the processor for receiving data values and report requests, combining the data values to generate combined data values as a function of the report requests and storing the data values in corresponding point slices for subsequent use in generating subsequent reports pursuant to subsequent requests and generating reports using the data values, the database comprising:

an existing point slice identifier identifying existing point slices; and

a point slice specifier indicating how to combine existing data values to generate non-existing data values and corresponding point slices.

15. The database of claim 14 wherein each combined data value may include data from more than one source element and each point slice is characterized by unit, period, interval and function attributes, the unit attribute indicating the source elements that produce signals used to generate the data values, the period attribute indicating a period to which the slice corresponds, the interval attribute indicating a duration between data values in the point slice and the function attribute indicating a function applied to at least one of source element signals and data values to generate the data values in the point slice, the existing point slice identifier indicating each of the function, unit, interval and period corresponding to each existing point slice.

16. The database of claim 15 wherein each request includes a unit attribute and wherein the specifier, for at least some of the units, indicates sub-units that comprise the units.

17. The database of claim 15 wherein each request includes a function attribute and wherein the specifier, for at least some of the functions, indicates sub-functions that comprise the functions.

18. The database of claim 15 wherein each request includes a period attribute and wherein the specifier, for at least some of the periods, indicates sub-periods that comprise the periods.

19. The database of claim 15 wherein each request includes an interval attribute and wherein the specifier, for at least some of the intervals, indicates sub-intervals that comprise the intervals.

20. The database of claim 15 for use in managing a building automation system and wherein the unit attribute is selected from one of a customer unit, a supplier unit, a geographic region unit, a person unit, a campus unit, a building unit, a floor unit and an equipment unit.

21. The database of claim 15 for use in managing a building automation system and wherein the interval attribute is selected from one of a yearly interval, a quarterly interval, a monthly interval, a daily interval, an hourly interval and a minute by minute interval.

22. The database of claim 15 wherein, for at least some point slices, the specifier indicates other point slices to be manipulated according to a function to generate a requested point slice.

23. The database of claim 15 wherein each request also includes a unit attribute and the point slice specifier correlates the unit attributes with specific sub-sets of source elements.

24. A method to be used with a database where a plurality of data segments are stored in the database, at least some of the data segments correlated with point slices in the database, the method for expediting the process of accessing combinations of the data segments upon request and comprising the steps of:

receiving a report request;

identifying at least one required point slice required to instantiate the requested report;

if the at least one required point slice does not exist, identifying intermediate point slices that can be combined to provide the required point slice;

combining the intermediate point slices to generate the required point slice;

during the step of combining the intermediate point slices, determining if the combining process generates intermediate point slices that may be useful during subsequent requests; and

where there are useful intermediate point slices, storing the useful intermediate point slices for subsequent use.

25. The method of claim 24 further including the step of storing the generated required point slice.

26. The method of claim 24 wherein the report request includes a function attribute specifying a function to be performed to generate the data segment corresponding to a point slice needed to instantiate the requested report and where the function includes at least one sub-function, the combining step including solving the sub-function and the step of determining including identifying the data segment and intermediate point slice corresponding to the solution of the sub-function.

27. The method of claim 24 wherein the report request includes a unit attribute specifying source elements from which data segments are to be combined to generate the point slice needed to instantiate the requested report and wherein the unit includes at least two sub-units, the combining step including combining the data elements for each of the two sub-units separately and the step of determining including identifying the data segments and intermediate point slices corresponding to each of the two sub-units.

28. The method of claim 24 wherein the report request includes an interval attribute specifying an interval over which data segments are to be combined to generate the point slice needed to instantiate the requested report and wherein the interval includes at least first and second sub-intervals, the combining step including combining the data segments for each of the first and second sub-intervals separately and the step of determining including identifying the data segments and intermediate point slices corresponding to each of the first and second sub-intervals.

29. A method to be used with a database where a plurality of data segments are stored in the database, at least some of the data segments correlated with point slices in the database, the method for expediting the process of accessing combinations of the data segments upon request and comprising the steps of:

receiving a report request including a first time period over which data having a first set of attributes is needed to instantiate the requested report;

determining if there is an existing first point slice having the first set of attributes over a second time period where the first time period includes at least a time segment in addition to the second time period;

where the first point slice exists, generating a second point slice having the first set of attributes and including data corresponding to a new period including the first and second periods and any intervening period.

30. The method of claim 29 further including the step of storing the second point slice.

31. The method of claim 30 further including the step of deleting the first point slice.

32. The method of claim 30 further including the step of using the second point slice data values to instantiate the requested report.

33. The method of claim 32 further including the step of presenting the instantiated report to a requesting system user.

34. The method of claim 29 wherein the first time period overlaps the second time period.

35. The method of claim 29 wherein the first time period includes the second time period.

36. A database construct for storing data values corresponding to specific source elements, the construct comprising:

a plurality of point slices wherein each point slice includes at least one data value, each slice correlated with unit, function, interval and time period attributes, the unit attribute indicating the source elements that produce raw data used to generate corresponding point slice data values, the period attribute indicating a period to which the slice corresponds, the interval attribute indicating a duration between data values in the point slice and the function attribute indicating a function applied to data values to generate the point slice data values.

37. The construct of claim 36 wherein at least some of the functions are Null meaning that the function is simply to store a raw data value generated by a source element.

38. The apparatus of claim 37 wherein to perform the step of using the existing segments to generate the non-existing intermediate subset point slices, the program further causes the processor to perform the steps of, for each non-existing intermediate subset point slice, repeating steps (c) through (g) with the intermediate point slice as the required point slice to determine the intermediate point slice.

39. The apparatus of claim 38 wherein at least a subset of the generated intermediate subset point slices are stored for subsequent use.

40. The apparatus of claim 38 wherein every generated intermediate subset point slice is stored for subsequent use.

41. An apparatus to be used with a database and an interface, the interface for specifying reports to be generated, the database capable of including a hierarchy of point slices where each point slice includes a data segment and at least a subset of the data segments can be combined to instantiate each specified report, the apparatus for expediting the report generating process and comprising:

a processor running a pulse sequencing program to perform the steps of;

(a) receiving a report request;

(b) identifying at least one required point slice required to instantiate the report;

(c) searching the database for the at least one required point slice;

(d) where the at least one required point slice does not exist, identifying an intermediate subset that includes intermediate point slices required to generate the at least one required point slice;

(e) determining if the intermediate subset point slices exist;

(f) for each non-existent intermediate subset point slice, using existing point slices to generate the non-existent intermediate point slice;

(g) combining the intermediate subset point slices to generate the at least one required point slice; and

(h) instantiating the report using the at least one required point slice.

42. The apparatus of claim 41 wherein the program further causes the processor to perform the step of, after generating the non-existent required point slice, storing the generated required point slice for use when subsequent reports are requested.

43. The apparatus of claim 41 wherein the program further causes the processor to perform the step of, after generating the non-existent intermediate subset point slices, storing the generated intermediate subset point slices for use when subsequent reports are requested.

44. The apparatus of claim 41 wherein, to perform the step of identifying at least one required point slice needed to instantiate the report, the program further causes the processor to perform the steps of receiving a report request and parsing the report request to identify the at least one point slice.

45. The apparatus of claim 44 for use in an enterprise automation system including at least one unit, the unit including at least one data source element and wherein each point slice includes data corresponding to a specific function, a specific period and specific intervals within the period for a specific unit and wherein, to perform the step of receiving the program causes the processor to perform the step of receiving an indication of a particular unit, a time period for which to report data corresponding to the unit, an interval and a function to be performed on data corresponding to the unit and wherein, to perform the step of identifying the program causes the processor to perform the step of identifying a point slice corresponding to the specified unit, function, time period and interval.

46. The apparatus of claim 45 wherein to perform the step of identifying the program further causes the processor to perform the steps of identifying relevant data types required to perform the specified function, identifying relevant unit source elements that provide the relevant data types and identifying relevant point slices corresponding to the relevant units, interval, time period and function.

47. The apparatus of claim 46 wherein, to perform the step of combining the point slices the program causes the processor to perform the step of combining data segments corresponding to the relevant units according to the specified function.

48. An apparatus to be used with a database and an interface, the interface for specifying reports to be generated, the database capable of storing a hierarchy of points slices where each point slice corresponds to a data segment and where a subset of the data segments can be combined to instantiate each specified report, the apparatus for automatically modifying a database structure as a function of specified reports to speed subsequent report generation and comprising:

a processor running a pulse sequencing program to perform the steps of;

(a) receiving a report request;

(b) identifying at least one required point slice needed to instantiate the report;

(c) searching the database for the at least one required point slice;

(d) where the at least one required point slice does not exist, identifying an intermediate subset that includes intermediate point slices required to generate the at least one required point slice;

(e) determining if the intermediate subset point slices exist;

(f) for each non-existent intermediate subset point slice, using existing point slices to generate the non-existent intermediate subset point slice;

(g) combining the intermediate subset point slices to generate the at least one required point slice; and

(h) storing the at least one required point slice.

49. The apparatus of claim 48 wherein the step of using the existing point slices to generate the non-existing intermediate subset point slices includes, for each non-existing intermediate subset point slice, repeating steps (c) through (g) with the intermediate point slice as the required point slice to determine the intermediate point slice.

50. The apparatus of claim 49 wherein the program further causes the processor to perform the step of storing every generated point slice for subsequent use.

51. An apparatus to be used with a database where a plurality of data segments are stored in the database, at least some of the data segments correlated with point slices in the database, the apparatus for expediting the process of accessing combinations of the data segments upon request and comprising the steps of:

receiving a report request;

identifying at least one required point slice required to instantiate the requested report;

if the at least one required point slice does not exist, identifying intermediate point slices that can be combined to provide the required point slice;

combining the intermediate point slices to generate the required point slice;

during the step of combining the intermediate point slices, determining if the combining process generates intermediate point slices that may be useful during subsequent requests; and

where there are useful intermediate point slices, storing the useful intermediate point slices for subsequent use.

52. The apparatus of claim 51 wherein the program further causes the processor to perform the step of storing the generated required point slice.

53. The apparatus of claim 51 wherein the report request includes a function attribute specifying a function to be performed to generate the data segment corresponding to a point slice needed to instantiate the requested report and where the function includes at least one sub-function, wherein, to perform the combining step, the program causes the processor to perform the step of solving the sub-function and wherein, to perform the step of determining the program causes the processor to perform the step of identifying the data segment and intermediate point slice corresponding to the solution of the sub-function.

54. The apparatus of claim 51 wherein the report request includes a unit attribute specifying source elements from which data segments are to be combined to generate the point slice needed to instantiate the requested report and wherein the unit includes at least two sub-units, wherein, to perform the combining step, the program causes the processor to perform the step of combining the data elements for each of the two sub-units separately and wherein, to perform the step of determining the program causes the processor to perform the step of identifying the data segments and intermediate point slices corresponding to each of the two sub-units.

55. The apparatus of claim 51 wherein the report request includes an interval attribute specifying an interval over which data segments are to be combined to generate the point slice needed to instantiate the requested report and wherein the interval includes at least first and second sub-intervals, wherein, to perform the combining step the program causes the processor to perform the step of combining the data segments for each of the first and second sub-intervals separately and wherein to perform the step of determining the program causes the processor to perform the step of identifying the data segments and intermediate point slices corresponding to each of the first and second sub-intervals.

56. An apparatus to be used with a database where a plurality of data segments are stored in the database, at least some of the data segments correlated with point slices in the database, the apparatus for expediting the process of accessing combinations of the data segments upon request and comprising:

a processor running a pulse sequencing program to perform the steps of;

receiving a report request including a first time period over which data having a first set of attributes is needed to instantiate the requested report;

determining if there is an existing first point slice having the first set of attributes over a second time period where the first time period includes at least a time segment in addition to the second time period; and

where the first point slice exists, generating a second point slice having the first set of attributes and including data corresponding to a new period including the first and second periods and any intervening period.

57. The apparatus of claim 56 wherein the program further causes the processor to perform the step of storing the second point slice.

58. The apparatus of claim 57 wherein the program further causes the processor to perform the step of deleting the first point slice.

59. The apparatus of claim 57 wherein the program further causes the processor to perform the step of using the second point slice data values to instantiate the requested report.

60. The apparatus of claim 59 wherein the program further causes the processor to perform the step of presenting the instantiated report to a requesting system user.

61. The apparatus of claim 56 wherein the first time period overlaps the second time period.

62. The apparatus of claim 56 wherein the first time period includes the second time period.

63. An apparatus to be used with a database and an interface, the interface for specifying reports to be generated, the database capable of including a hierarchy of point slices where each point slice includes a data segment and at least a subset of the data segments can be combined to instantiate each specified report, the apparatus for expediting the report generating process and comprising:

(a) means for receiving a report request;

(b) means for identifying at least one required point slice required to instantiate the report;

(c) means for searching the database for the at least one required point slice;

(d) where the at least one required point slice does not exist, means for identifying an intermediate subset that includes intermediate point slices required to generate the at least one required point slice;

(e) means for determining if the intermediate subset point slices exist;

(f) for each non-existent intermediate subset point slice, means for using existing point slices to generate the non-existent intermediate point slice;

(g) means for combining the intermediate subset point slices to generate the at least one required point slice; and

(h) means for instantiating the report using the at least one required point slice.

64. The apparatus of claim 63 further including means for, after generating the non-existent required point slice, storing the generated required point slice for use when subsequent reports are requested.

65. The apparatus of claim 63 further including means for, after generating the non-existent intermediate subset point slices, storing the generated intermediate subset point slices for use when subsequent reports are requested.

66. The apparatus of claim 63 wherein the means for using the existing segments to generate the non-existing intermediate subset point slices includes, for each non-existing intermediate subset point slice, means for causing means (c) through (g) to operate with the intermediate point slice as the required point slice to determine the intermediate point slice.

67. The apparatus of claim 66 wherein at least a subset of the generated intermediate subset point slices are stored for subsequent use.

69. The apparatus of claim 63 wherein the means for identifying at least one required point slice needed to instantiate the report includes means for receiving a report request and parsing the report request to identify the at least one point slice.

70. The apparatus of claim 69 for use in an enterprise automation system including at least one unit, the unit including at least one data source element and wherein each point slice includes data corresponding to a specific function, a specific period and specific intervals within the period for a specific unit and wherein the means for receiving includes means for receiving an indication of a particular unit, a time period for which to report data corresponding to the unit, an interval and a function to be performed on data corresponding to the unit, the means for identifying including means for identifying a point slice corresponding to the specified unit, function, time period and interval.

71. The apparatus of claim 70 wherein the means for identifying further includes means for identifying relevant data types required to perform the specified function, means for identifying relevant unit source elements that provide the relevant data types and means for identifying relevant point slices corresponding to the relevant units, interval, time period and function.

72. The apparatus of claim 71 wherein the means for combining the point slices includes means for combining data segments corresponding to the relevant units according to the specified function.

73. An apparatus to be used with a database and an interface, the interface for specifying reports to be generated, the database capable of storing a hierarchy of points slices where each point slice corresponds to a data segment and where a subset of the data segments can be combined to instantiate each specified report, the apparatus for automatically modifying a database structure as a function of specified reports to speed subsequent report generation and comprising:

(a) means for receiving a report request;

(b) means for identifying at least one required point slice needed to instantiate the report;

(c) means for searching the database for the at least one required point slice

(d) means for where the at least one required point slice does not exist, identifying an intermediate subset that includes intermediate point slices required to generate the at least one required point slice;

(e) means for determining if the intermediate subset point slices exist;

(f) means for each non-existent intermediate subset point slice, using existing point slices to generate the non-existent intermediate subset point slice;

(g) means for combining the intermediate subset point slices to generate the at least one required point slice; and

(h) means for storing the at least one required point slice.

74. The apparatus of claim 73 wherein the step of using the existing point slices to generate the non-existing intermediate subset point slices includes, for each non-existing intermediate subset point slice, repeating steps (c) through (g) with the intermediate point slice as the required point slice to determine the intermediate point slice.

75. The apparatus of claim 74 further including storing every generated point slice for subsequent use.

76. An apparatus to be used with a database where a plurality of data segments are stored in the database, at least some of the data segments correlated with point slices in the database, the apparatus for expediting the process of accessing combinations of the data segments upon request and comprising:

means for receiving a report request;

means for identifying at least one required point slice required to instantiate the requested report;

means for if the at least one required point slice does not exist, identifying intermediate point slices that can be combined to provide the required point slice;

means for combining the intermediate point slices to generate the required point slice;

means for during the step of combining the intermediate point slices, determining if the combining process generates intermediate point slices that may be useful during subsequent requests; and

where there are useful intermediate point slices, means for storing the useful intermediate point slices for subsequent use.

77. The apparatus of claim 76 further including means for storing the generated required point slice.

78. The apparatus of claim 76 wherein the report request includes a function attribute specifying a function to be performed to generate the data segment corresponding to a point slice needed to instantiate the requested report and where the function includes at least one sub-function, the means for combining including means for solving the sub-function and the means for determining including means for identifying the data segment and intermediate point slice corresponding to the solution of the sub-function.

79. The apparatus of claim 76 wherein the report request includes a unit attribute specifying source elements from which data segments are to be combined to generate the point slice needed to instantiate the requested report and wherein the unit includes at least two sub-units, the means for combining including means for combining the data elements for each of the two sub-units separately and the means for determining including means for identifying the data segments and intermediate point slices corresponding to each of the two sub-units.

80. The apparatus of claim 76 wherein the report request includes an interval attribute specifying an interval over which data segments are to be combined to generate the point slice needed to instantiate the requested report and wherein the interval includes at least first and second sub-intervals, the means for combining including means for combining the data segments for each of the first and second sub-intervals separately and the means for determining including means for identifying the data segments and intermediate point slices corresponding to each of the first and second sub-intervals.

81. An apparatus to be used with a database where a plurality of data segments are stored in the database, at least some of the data segments correlated with point slices in the database, the apparatus for expediting the process of accessing combinations of the data segments upon request and comprising:

means for receiving a report request including a first time period over which data having a first set of attributes is needed to instantiate the requested report;

means for determining if there is an existing first point slice having the first set of attributes over a second time period where the first time period includes at least a time segment in addition to the second time period;

where the first point slice exists, means for generating a second point slice having the first set of attributes and including data corresponding to a new period including the first and second periods and any intervening period.

82. The apparatus of claim 81 further including means for storing the second point slice.

83. The apparatus of claim 82 further including means for deleting the first point slice.

84. The apparatus of claim 82 further including means for using the second point slice data values to instantiate the requested report.

85. The apparatus of claim 84 further including means for presenting the instantiated report to a requesting system user.

86. The apparatus of claim 81 wherein the first time period overlaps the second time period.

87. The apparatus of claim 81 wherein the first time period includes the second time period.