Database Query Calculation Using an Operator that Explicitly Removes Group-By Characteristics

Patent metadata

Field	Value
Publication number	US-2016267129-A1
Application number	US-201615164432-A
Country	US
Kind code	A1
Filing date	May 25, 2016
Priority date	Jun 18, 2013
Publication date	Sep 15, 2016
Grant date	—

Abstract

In one embodiment, a method receives a query for analyzing data in a database. The query includes a first level calculation that is performed before a second level calculation. When an operator associated with the first level calculation is present in the query, the method performs: calculating the first level calculation by removing group by information for a reference characteristic for the first level calculation to determine a first level calculation result and calculating the second level calculation using the first level calculation result using the group by information for the reference characteristic to determine a second level calculation result, wherein the group by in the second level calculation combines groups of rows in a database table of the database that have the same content in their specified columns.

First claim

What is claimed is:

1 . A method comprising:
receiving, by a computing device, a query for analyzing data in a database, wherein the query includes a first level calculation that is performed before a second level calculation; determining, by the computing device, when an operator associated with the first level calculation is present in the query; when the operator is present, performing: calculating, by the computing device, the first level calculation by removing group by information for a reference characteristic for the first level calculation to determine a first level calculation result; and calculating, by the computing device, the second level calculation using the first level calculation result using the group by information for the reference characteristic to determine a second level calculation result, wherein the group by in the second level calculation combines groups of rows in a database table of the database that have the same content in their specified columns.

2 . The method of claim 1 , when the operator is not present, performing:
calculating, by the computing device, the first level calculation using the group by information for the reference characteristic for the first level calculation to determine the first level calculation result; and calculating, by the computing device, the second level calculation using the first level calculation result using the group by information for the reference characteristic to determine the second level calculation result.

3 . The method of claim 2 , wherein when the operator is not present, and wherein:
the first level calculation comprises a constant selection of the reference characteristic, and the constant selection is configured to remove the group by information for the reference characteristic for the first level calculation but the constant selection is not honored due to the principle of late aggregation.

4 . The method of claim 1 , wherein a rule indicates the group by information for the reference characteristic for the first level calculation cannot be removed, wherein the operator being present overrides the rule.

5 . The method of claim 1 , wherein when the operator is present, and
wherein: the first level calculation includes a constant selection of the reference characteristic to remove the group by of the reference characteristic at the first level calculation, and the constant selection of the reference characteristic is used despite a rule specifying the group by information of the reference characteristic should not be removed by the first level calculation.

6 . The method of claim 5 , wherein when the operator is present, the removal of the group by of the reference characteristic by the constant selection overrides the rule.

7 . The method of claim 5 , wherein the rule is invoked when the second level calculation includes the group by information for a same reference characteristic as the reference characteristic specified by the constant selection in the first level calculation.

8 . The method of claim 1 , wherein the second level calculation comprises an exception aggregation that uses the first level calculation result in performing the second level calculation, and wherein the exception aggregation performs the second level calculation by aggregating by the reference characteristic to determine the second level calculation result

9 . The method of claim 1 , wherein when the operator is present, the group by information for the reference characteristic for the first level calculation to determine a first level calculation is removed for each cell in a column for the reference characteristic.

10 . A non-transitory computer-readable storage medium containing instructions, that when executed, control a computer system to be configured for:
receiving a query for analyzing data in a database, wherein the query includes a first level calculation that is performed before a second level calculation; determining when an operator associated with the first level calculation is present in the query; when the operator is present, performing: calculating the first level calculation by removing group by information for a reference characteristic for the first level calculation to determine a first level calculation result; and calculating the second level calculation using the first level calculation result using the group by information for the reference characteristic to determine a second level calculation result, wherein the group by in the second level calculation combines groups of rows in a database table of the database that have the same content in their specified columns.

11 . The non-transitory computer-readable storage medium of claim 10 , when the operator is not present, performing:
calculating, by the computing device, the first level calculation using the group by information for the reference characteristic for the first level calculation to determine the first level calculation result; and calculating, by the computing device, the second level calculation using the first level calculation result using the group by information for the reference characteristic to determine the second level calculation result.

12 . The method of claim 11 , wherein when the operator is not present, and wherein:
the first level calculation comprises a constant selection of the reference characteristic, and the constant selection is configured to remove the group by information for the reference characteristic for the first level calculation but the constant selection is not honored due to the principle of late aggregation.

13 . The non-transitory computer-readable storage medium of claim 10 , wherein a rule indicates the group by information for the reference characteristic for the first level calculation cannot be removed, wherein the operator being present overrides the rule.

14 . The non-transitory computer-readable storage medium of claim 10 , wherein when the operator is present, and wherein:
the first level calculation includes a constant selection of the reference characteristic to remove the group by of the reference characteristic at the first level calculation, and the constant selection of the reference characteristic is used despite a rule specifying the group by information of the reference characteristic should not be removed by the first level calculation.

15 . The non-transitory computer-readable storage medium of claim 14 , wherein when the operator is present, the removal of the group by of the reference characteristic by the constant selection overrides the rule.

16 . The non-transitory computer-readable storage medium of claim 14 , wherein the rule is invoked when the second level calculation includes the group by information for a same reference characteristic as the reference characteristic specified by the constant selection in the first level calculation.

17 . The non-transitory computer-readable storage medium of claim 10 , wherein the second level calculation comprises an exception aggregation that uses the first level calculation result in performing the second level calculation, and wherein the exception aggregation performs the second level calculation by aggregating by the reference characteristic to determine the second level calculation result

18 . The non-transitory computer-readable storage medium of claim 10 , wherein when the operator is present, the group by information for the reference characteristic for the first level calculation to determine a first level calculation is removed for each cell

Assignees

• Sap Se

Inventors

• Schroetel Sebastian
• Dipper Stefan
• Wilking Michael
• Mueller Christof

Classifications

• G06F16/244 Primary

  Grouping and aggregation · CPC title

• G06F16/24556

  Aggregation; Duplicate elimination · CPC title

• G06F16/283 Primary

  Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP · CPC title

• G06F16/9535

  Search customisation based on user profiles and personalisation · CPC title

• G06F16/221

  Column-oriented storage; Management thereof · CPC title