Database calculation engine having forced filter pushdowns with dynamic joins

Patent metadata

Field	Value
Publication number	US-10198475-B2
Application number	US-201514607681-A
Country	US
Kind code	B2
Filing date	Jan 28, 2015
Priority date	Jan 28, 2015
Publication date	Feb 5, 2019
Grant date	Feb 5, 2019

Abstract

A query that requests a filter attribute is received by a database server from a remote application server that is associated with a calculation scenario that defines a data flow model including one or more calculation nodes. Subsequently, the database server instantiates the calculation scenario. As part of the instantiation, the calculation scenario is optimized by (i) pushing down a filter attribute from a first node to a lowest available child node, (ii) removing the filter attribute from the first node, and (iii) removing non-required join-attributes from the instantiated calculation scenarios. Thereafter, the operations defined by the calculation nodes of the instantiated calculation scenario can be executed to result in a responsive data set. Next, the data set is provided to the application server by the database server.

First claim

What is claimed is:

1. A method comprising:
receiving, by a database server from a remote application server, a query requesting a filter attribute that is associated with a calculation scenario that defines a data flow model that includes one or more calculation nodes arranged in a hierarchy;
instantiating, by the database server, the calculation scenario, the instantiating of the calculation scenario optimizes the calculation scenario by at least:
in response to determining that the filter attribute is not requested by one or more ancestor calculation nodes of the calculation scenario, pushing down the filter attribute to a lowest available descendent calculation node of the calculation scenario; and
in response to the filter attribute being pushed down, removing the filter attribute from at least one ancestor node of the lowest available descendent calculation nodes that define a dynamic join operation, the dynamic join operation joining a first table and a second table based on a plurality of join attributes, the removal of the filter attribute comprising removing the filter attribute as one of the plurality of join attributes for joining the first table and the second table, and the removal of the filter attribute as the one of the plurality of join attributes reducing an intermediate result of one or more calculation nodes comprising the data flow model;

executing, by the database server, the query by at least performing the operations defined by the calculation nodes of the optimized calculation scenario, the executing of the query resulting in a responsive data set; and
providing, by the database server to the application server, the data set.

2. A method as in 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.

3. A method as in claim 1 , wherein at least one of the calculation nodes filters results obtained from the database server.

4. A method as in claim 1 , wherein at least one of the calculation nodes sorts results obtained from the database server.

5. A method as in claim 1 , wherein the calculation scenario is instantiated in a calculation engine layer by a calculation engine.

6. A method as in claim 5 , 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 metamodel joining at least a portion of the physical tables in the physical table pool.

7. A method as in claim 6 , wherein the calculation engine invokes an SQL processor for executing set operations.

8. A method as in 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.

9. A method as in claim 8 , wherein each calculation node has at least one output table that is used to generate the data set.

10. A method as in claim 9 , wherein at least one calculation node consumes an output table of another calculation node.

11. A method as in 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.

12. A method as in claim 1 , wherein the calculation scenario comprises database metadata.

13. A non-transitory computer program product storing instructions which, when executed by at least one data processor forming part of at least one computing device, result in operations comprising:
receiving, by a database server from a remote application server, a query requesting a filter attribute that is associated with a calculation scenario that defines a data flow model that includes one or more calculation nodes arranged in a hierarchy;
instantiating, by the database server, the calculation scenario, the instantiating of the calculation scenario optimizes the calculation scenario by at least:
in response to determining that the filter attribute is not requested by one or more ancestor calculation nodes of the calculation scenario, pushing down the filter attribute to a lowest available descendent calculation node of the calculation scenario; and
in response to the filter attribute being pushed down, removing the filter attribute from at least one ancestor node of the lowest available descendent calculation nodes that define a dynamic join operation, the dynamic join operation joining a first table and a second table based on a plurality of join attributes, the removal of the filter attribute comprising removing the filter attribute as one of the plurality of join attributes for joining the first table and the second table, and the removal of the filter attribute as the one of the plurality of join attributes reducing an intermediate result of one or more calculation nodes comprising the data flow model;

executing, by the database server, the query by at least performing the operations defined by the calculation nodes of the optimized calculation scenario, the executing of the query resulting in a responsive data set; and
providing, by the database server to the application server, the data set.

14. A computer program product as in claim 13 , 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.

15. A computer program product as in claim 13 , wherein at least one of the calculation nodes filters results obtained from the database server.

16. A computer program product as in claim 13 , wherein at least one of the calculation nodes sorts results obtained from the database server.

17. A computer program product as in claim 13 , wherein the calculation scenario is instantiated in a calculation engine layer by a calculation engine.

18. A computer program product as in claim 17 , 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 metamodel joining at least a portion of the physical tables in the physical table pool.

19. A computer program product as in claim 18 , wherein the calculation engine invokes an SQL processor for executing set operations.

20. A system comprising:
at least one data processor; and
memory storing instructions which, when executed by the at least one data processor, result in operations comprising:
receiving, by a database server from a remote application server, a query requesting a filter attribute that is associated with a calculation scenario that defines a data flow model that includes one or more calculation nodes arranged in a hierarchy;
instantiating, by the database server, the calculation scenario, the instantiating of the calculation scenario optimizes the calculation scenario by at least:
in response to determining that the filter attribute is not requested by one or more ancestor calculation nodes of the calculation scenario, pushing down the filter attribute to a lowest available descendent calculation node of the calculation scenario; a

Assignees

• Mindnich Tobias
• Weyerhaeuser Christoph
• Merx Johannes
• Sap Se

Inventors

• Mindnich Tobias
• Weyerhaeuser Christoph
• Merx Johannes

Classifications

• G06F16/2456 Primary

  Join operations · CPC title

• G06F16/24544

  Join order optimisation · CPC title

• G06F17/30498 Primary

  Physics · mapped topic

• G06F17/30466

  Physics · mapped topic