Database calculation engine integrating hierarchy views

• US 10,067,980 B2
• Filed: 06/16/2015
• Issued: 09/04/2018
• Est. Priority Date: 06/16/2015
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Weyerhaeuser, Christoph, Mindnich, Tobias, Merx, Johannes, Schwing, Julian, Patejdl, Daniel
• Primary Examiner(s): Bibbee, Jared M
• Application Number:
  US14/741,377
• Publication Number:
  US 20160371330A1
• Time in Patent Office: 1,176 Days
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/24544 Join order optimisation

Abstract

A calculation engine is described that executes calculation scenarios comprising a plurality of calculation nodes that specify operations to be performed to execute the query. One of the nodes can be a semantic node that is used to modify the query for operations requiring special handling including handling of hierarchy views. Related apparatus, systems, methods, and articles are also described.

Claims

1. A method comprising:

receiving, by a database server from a remote application server, a query associated with a calculation scenario that defines a data flow model that includes a plurality of calculation nodes, each calculation node defining one or more operations to execute on the database server, at least one of the nodes being a semantic node specifying an operation requiring special handling, at least one of the nodes being a leaf node having two or more parent nodes, the special handling comprising aggregating of results from the two or more parent nodes without redundant aggregation of results from the at least one leaf node;

modifying, by the database server, the query using the semantic node to handle at least one hierarchy view within the calculation scenario, the at least one hierarchy view comprising a calculation model having a plurality of nodes arranged in a hierarchical manner, the plurality of nodes comprising a node having more than one parent node, the modifying the query comprising merging the paths between the semantic node and the at least one leaf node having two or more parent nodes, the merging followed by the aggregating of results from the two or more parent nodes;

modifying, by the database server, the calculation scenario based on the modified query;

instantiating, by the database server, the modified calculation scenario;

executing, by the database server, the operations defined by the calculation nodes of the modified calculation scenario to result in at least one result set; and

providing, by the database server to the application server, the at least one result set in response to the query.

2. The method of claim 1, wherein a hierarchy specified by the semantic node is used to aggregate data according to a selected hierarchy level.

3. The method of claim 1, wherein the received query specifies a unit conversion and operation and/or handling of count distinct key figures, wherein the special handling specified by the semantic node removes redundant operations.

4. The method of claim 1, wherein at least a portion of paths and/or attributes defined by the calculation scenario are not required to respond to the query, and wherein the instantiated calculation scenario omits the paths and attributes defined by the calculation scenario that are not required to respond to the query.

5. The method of claim 1, wherein at least one of the calculation nodes filters results obtained from the database server.

6. The method of claim 1, wherein at least one of the calculation nodes sorts results obtained from the database server.

7. The method of claim 1, wherein the calculation scenario is instantiated in a calculation engine layer by a calculation engine.

8. The method of claim 7, wherein the calculation engine layer interacts with a physical table pool and a logical layer, the physical table pool comprising physical tables containing data to be queried, and the logical layer defining a logical meta model joining at least a portion of the physical tables in the physical table pool.

9. The method of claim 1, wherein an input for each calculation node comprises one or more of:

a physical index, a join index, an OLAP index, and another calculation node.

10. The method of claim 9, wherein each calculation node has at least one output table that is used to generate the final result set.

11. The method of claim 10, wherein at least one calculation node consumes an output table of another calculation node.

12. The method of claim 1, wherein the executing comprises:

forwarding the query to a calculation node in the calculation scenario that is identified as a default node if the query does not specify a calculation node at which the query should be executed.

13. The method of claim 1, wherein the query identifies a particular calculation node, and wherein the executing comprises:

forwarding the query to the calculation node specified in the query at which the query should be executed.

14. The method of claim 1, wherein the calculation scenario comprises database metadata.

15. The method of claim 1, wherein the calculation scenario is exposed as a database calculation view.

16. The method of claim 15, wherein the executing comprises:

invoking, by a SQL processor, a calculation engine to execute the calculation scenario behind the database calculation view.

17. The method of claim 16, wherein the calculation engine invokes the SQL processor for executing set operations.

18. The method of claim 17, wherein the SQL processor invokes the calculation engine when executing SQL queries with calculation views.

21. The method of claim 1, wherein the modifying the query further comprises converting units of a value of at least one of the nodes before the merging of the paths.

22. The method of claim 1, wherein the merging of the paths comprises at least one of a union operation and a join operation.

19. A non-transitory computer program product storing instructions, which when executed by at least one data processor, result in operations comprising:

receiving a query associated with a calculation scenario that defines a data flow model that includes a plurality of calculation nodes, each calculation node defining one or more operations to execute on the database server, at least one of the nodes being a semantic node specifying an operation requiring special handling, at least one of the nodes being a leaf node having two or more parent nodes, the special handling comprising aggregating results from the two or more parent nodes without redundant aggregation of results from the at least one leaf node;

modifying the query using the semantic node to handle at least one hierarchy view within the calculation scenario, the at least one hierarchy view comprising a calculation model having a plurality of nodes arranged in a hierarchical manner, the plurality of nodes comprising a node having more than one parent node, the modifying the query comprising merging the paths between the semantic node and the at least one leaf node having two or more parent nodes, the merging followed by the aggregating of results from the two or more parent nodes;

modifying the calculation scenario based on the modified query;

instantiating the modified calculation scenario;

executing the operations defined by the calculation nodes of the modified calculation scenario to result in at least one result set; and

providing the at least one result set in response to the query.

20. A system comprising:

a database server; and

an application server remote from the database server;

wherein the database server;

receives a query associated with a calculation scenario that defines a data flow model that includes a plurality of calculation nodes, each calculation node defining one or more operations to execute on the database server, at least one of the nodes being a semantic node specifying an operation requiring special handling, at least one of the nodes being a leaf node having two or more parent nodes, the special handling comprising aggregating results from the two or more parent nodes without redundant aggregation of results from the at least one leaf node;

modifies the query using the semantic node to handle at least one hierarchy view within the calculation scenario, the at least one hierarchy view comprising a calculation model having a plurality of nodes arranged in a hierarchical manner, the plurality of nodes comprising a node having more than one parent node, the modifies the query comprising merging the paths between the semantic node and the at least one leaf node having two or more parent nodes, the merging followed by the aggregating of results from the two or more parent nodes;

modifies the calculation scenario based on the modified query;

instantiates the modified calculation scenario;

executes the operations defined by the calculation nodes of the modified calculation scenario to result in at least one result set; and

provides the at least one result set in response to the query.