Extended database engine providing versioning and embedded analytics

• US 7,698,348 B2
• Filed: 04/23/2004
• Issued: 04/13/2010
• Est. Priority Date: 12/19/2003
• Status: Active Grant

• Current Assignee: Kinaxis Holdings Incorporated
• Original Assignee: Kinaxis Holdings Incorporated
• Inventors: Crozman, James Robert, Walker, Robert Nigel, Mototsune, Mark Shyo, Klett, Robert Duncan
• Primary Examiner(s): Breene; John E
• Assistant Examiner(s): GOFMAN, ALEX N
• Application Number:
  US10/830,690
• Publication Number:
  US 20050138013A1
• Time in Patent Office: 2,181 Days
• Field of Search: 707/102, 707/203
• US Class Current: 707/790
• CPC Class Codes: G06F 16/24552 Database cache management

Abstract

A system for calculating analytics uses a relational database to store inputs, calculates results, and stores them in cache. The system also includes an access layer that provides a unified view of the data in server. A dynamic access layer is generated at runtime to run an analytic to provide a flexible framework for creating business logic.

Claims

1. A method implemented in a system that includes a database for storing input data and a server, the method comprising:

the server managing multiple versions of the input data, wherein each version of the input data is stored as a record in the database with each record having an identifier and a version field;

the server receiving a query for data;

in response to the query, identifying whether the requested data is available in a cache;

if the requested data is not available in the cache, identifying an analytic to calculate data from input data;

retrieving from a database input data associated with the analytic and storing the input data in the cache, if not already present in the cache, wherein the input data are stored in the cache in one or more pages of records with a list of identifiers and a set of versions for each identifier, wherein at least one record has multiple versions;

using the analytic to perform calculations with the analytic on the records of input data determined to have the correct version related to the query to produce calculated data, wherein multiple versions of a record are based on multiple plans, and wherein the query is associated with a plan and the correct version of a record is determined based on the plan with which the query is associated;

storing the calculated data into the cache; and

downloading a schema which is used to generate a data access layer, the data access layer exposing tables in the database as classes with properties corresponding to fields in the tables, and in response to changes made to the schema, automatically regenerating the access layer.

2. The method of claim 1, wherein the cache includes an input data cache and a calculated data cache.

3. The method of claim 1, further comprising, after calculations are performed on the input data to produce calculated data, modifying the input data in the cache and re-calculating the analytic to produce new calculated data without retrieving the input data from the database.

4. The method of claim 3, wherein the re-calculating is performed automatically without a user request.

5. The method of claim 3, wherein the modifying includes receiving modifications from another system over a wide area network and through an interface.

6. The method of claim 1, wherein the server creates an inverse set for at least one field, the inverse set including the field and a list of identifiers of records that have the same value for that field.

7. The method of claim 1, wherein the records are stored in the database with fields, wherein groups of fields are organized into a plurality of separate field groups.

8. The method of claim 7, wherein the data is arranged in version nodes with field groups.

9. The method of claim 1, wherein the analytic includes a supply chain management analytic.

10. The method of claim 9, wherein the analytic relates to material requirements planning or capable-to-promise calculations.

11. The method of claim 1, further comprising accepting data changes and providing calculated results over a wide area network to another system.

12. The method of claim 1, wherein a plurality of different versions of data, whether input or calculated, can be compared to show the differences between versions.

13. The method of claim 1, further comprising defining namespaces, including at least a server namespace and a separate user namespace for a user of the system.

14. The method of claim 13, further comprising, in response to receiving a name of a field or table for use in the server namespace, renaming that name to the user namespace.

15. The method of claim 1, wherein the retrieving includes retrieving some but not all data fields in the database for a group of records.

16. The method of claim 1, wherein the multiple versions of a record with multiple versions are stored as a list linked to the identifier for said record.

17. The method of claim 1, wherein the analytic includes an algorithm to calculate data for projecting future conditions from data in one or more tables in the database.

18. The method of claim 1, wherein a new version of the multiple versions of input data is stored by storing the changes between the new version and an older version of the multiple versions.

19. The method of claim 18, wherein the records of an input data determined to have the correct version is generated from the stored changes.

20. A system for calculating analytics comprising:

a database for storing data, at least some of which data is for use by analytics to produce calculated results in response to queries;

a server having a cache and responsive to a query for input data or calculated data for

managing multiple versions of the input data, wherein each version of the input data is stored as a record in the database with each record having an identifier and a version number indicating with which of one or more versions the data the record is associated, wherein the input data are stored in the cache in one or more pages of records with a list of identifiers and a set of versions for each identifier, wherein at least one record has multiple versions wherein multiple versions of a record are based on multiple plans, and wherein the query is associated with a plan and a correct version of a record is determined based on the plan with which the query is associated,

identifying whether the requested data is available in a cache,

if the requested data is not available in the cache, identifying an analytic to produce a response,

retrieving input data associated with the analytic from the database and storing the input data in the cache if not already present in the cache,

performing calculations with the analytic on the records of input data determined to have the correct version related to the query to produce calculated data,

storing the calculated data into the cache without storing the calculated data in the database; and

downloading a schema which is used to generate a data access layer, the data access layer exposing tables in the database as classes with properties corresponding to fields in the tables, and in response to changes made to the schema, automatically regenerating the access layer.

21. The system of claim 20, wherein after calculations are performed on the input data to produce calculated data, the server is responsive to modifications to the input data in the input data cache for re-calculating the analytic to produce new calculated data without retrieving the input data in the database.

22. The system of claim 21, wherein the re-calculating is performed automatically without a user request.

23. The system of claim 21, wherein the modifying includes receiving modifications from another system over a wide area network and through an interface.

24. The system of claim 20, wherein the records are stored in the database with groups of fields organized into a plurality of field groups.

25. The system of claim 20, wherein the analytic includes a supply chain management analytic.

26. The system of claim 25, wherein the analytic relates to material requirements planning or capable-to-promise calculations.

27. The system of claim 20, wherein the server has an interface to accept data changes and provide calculated results over a wide area network to another system.

28. The system of claim 20, wherein the server is responsive to queries for different versions of data to show the differences of calculated results between versions.

29. The system of claim 20, the multiple versions of a record with multiple versions are stored as a list linked to the identifier for said record.

30. The system of claim 20, wherein the analytic includes an algorithm to calculate data for projecting future conditions from data in one or more tables in the database.

31. The system of claim 20, wherein a new version of the multiple versions of input data is stored by storing the changes between the new version and an older version of the multiple versions.

32. The system of claim 31, wherein the records of an input data determined to have the correct version is generated from the stored changes.