Summary filter transformation

• US 8,676,821 B2
• Filed: 05/27/2004
• Issued: 03/18/2014
• Est. Priority Date: 05/27/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Styles, Michael E., Cosma, Marius
• Primary Examiner(s): GOFMAN, ALEX N
• Application Number:
  US10/856,305
• Publication Number:
  US 20050038782A1
• Time in Patent Office: 3,582 Days
• Field of Search: 707/756
• US Class Current: 707/756
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A mechanism for summary filter transformation is provided. The mechanism comprises a summary filter analysis module for analyzing a multidimensional query that is not supported by a target database system, and a summary filter transformation module for transforming the multidimensional query into a semantically equivalent query that is supported by the target database system.

Claims

1. A method, in a data processing system having a processor, of summary filter query transformation, the method comprising:

receiving, in a summary filter transformation system of the data processing system, a query having a select list, a from element, and a summary filter having a filter condition;

analyzing, by an analysis module of the summary filter transformation system, the filter condition to determine a filter group of the filter condition, the filter group comprising a list of expressions over which the filter condition is computed;

creating, by the analysis module, a derived table for computing the filter condition, the derived table having a select list, creating the derived table comprising;

adding the list of expressions of the filter group to the derived table as one or more columns of the derived table'"'"'s select list; and

adding the filter condition to the derived table as a one or more columns of the derived table'"'"'s select list;

transforming, by a transformation module of the summary filter transformation system, the received query by combining the received query with the derived table, transforming the received query comprising;

traversing the select list of the received query and transforming one or more aggregates of the select list to reference one or more of the columns of the derived table;

transforming the from element of the received query to reference the derived table; and

adding a WHERE clause to the transformed original query, the WHERE clause based on the filter condition and referencing one or more of the columns of the derived table.

2. The method as claimed in claim 1, wherein creating the derived table further comprises:

identifying aggregates in the select list of the received query having a PREFILTER clause;

adding the identified PREFILTER aggregates from the select list of the received query to the select list of the derived table; and

replacing the PREFILTER aggregates in the select list of the received query with a reference to the added PREFILTER aggregate in the derived table.

3. The method as claimed in claim 1, wherein analyzing the filter condition to determine the filter group comprises:

identifying aggregates of the filter condition;

for each identified aggregate, identifying an aggregate group of the aggregate, the aggregate group comprising a list of expressions over which the aggregate is computed; and

selecting the aggregate group with the lowest level of granularity as the filter group.

4. The method as claimed in claim 3, wherein identifying an aggregate group comprises:

determining the type of the aggregate;

when the aggregate is a report aggregate having a standard aggregate counterpart, deriving the aggregate group based on a FOR clause of the aggregate; and

when the aggregate is a non-report aggregate, deriving the aggregate group based on an AT clause of the aggregate.

5. The method as claimed in claim 3, wherein the standard aggregates comprise at least one of:

MIN;

MAX;

SUM;

AVG;

COUNT;

or

COUNT(*).

6. The method as claimed in claim 1, further comprising determining that the FILTER transformation can be performed, comprising:

determining an aggregate group for each aggregate of the filter condition; and

determining that all of the determined aggregate groups are compatible, including;

determining the aggregate group with the lowest level of granularity;

determining that each aggregate group is a subset of the aggregate group with the lowest level of granularity or that each aggregate group is identical to the aggregate group with the lowest level of granularity.

7. A data processing system, comprising:

a query engine; and

a summary filter transformation system coupled to the query engine, wherein the summary filter transformation system is configured to;

receive a query having a select list, a from element, and a summary filter having a filter condition;

analyze the filter condition to determine a filter group of the filter condition, the filter group comprising a list of expressions over which the filter condition is computed;

create a derived table for computing the filter condition, the derived table having a select list, creating the derived table comprising;

adding the list of expressions of the filter group to the derived table as one or more columns of the derived table'"'"'s select list; and

adding the filter condition to the derived table as a one or more columns of the derived table'"'"'s select list;

transform the received query by combining the received query with the derived table, transforming the received query comprising;

traverse the select list of the received query and transforming one or more aggregates of the select list to reference one or more of the columns of the derived table;

transform the from element of the received query to reference the derived table; and

add a WHERE clause to the transformed original query, the WHERE clause based on the filter condition and referencing one or more of the columns of the derived table.

8. The data processing system as claimed in claim 7, wherein the summary filter transformation system is configured to create the derived table further by:

identifying aggregates in the select list of the received query having a PREFILTER clause;

adding the identified PREFILTER aggregates from the select list of the received query to the select list of the derived table; and

replacing the PREFILTER aggregates in the select list of the received query with a reference to the added PREFILTER aggregate in the derived table.

9. The data processing system as claimed in claim 7, wherein the summary filter transformation system is configured to analyze the filter condition to determine the filter group by:

identifying aggregates of the filter condition;

for each identified aggregate, identifying an aggregate group of the aggregate, the aggregate group comprising a list of expressions over which the aggregate is computed; and

selecting the aggregate group with the lowest level of granularity as the filter group.

10. The data processing system as claimed in claim 9, wherein the summary filter transformation system is configured to identify an aggregate group by:

determining the type of the aggregate;

when the aggregate is a report aggregate having a standard aggregate counterpart, deriving the aggregate group based on a FOR clause of the aggregate; and

when the aggregate is a non-report aggregate, deriving the aggregate group based on an AT clause of the aggregate.

11. The data processing system as claimed in claim 9, wherein the standard aggregates comprise at least one of:

MIN;

MAX;

SUM;

AVG;

COUNT;

or

COUNT(*).

12. The data processing system as claimed in claim 7, wherein the summary filter transformation system is further configured to determine that the FILTER transformation can be performed, comprising:

determining an aggregate group for each aggregate of the filter condition; and

determining that all of the determined aggregate groups are compatible, including;

determining the aggregate group with the lowest level of granularity; and

determining that each aggregate group is a subset of the aggregate group with the lowest level of granularity or that each aggregate group is identical to the aggregate group with the lowest level of granularity.

13. A computer program product comprising a non-transitory computer-readable medium further comprising:

computer-readable code for receiving a query having a select list, a from element, and a summary filter having a filter condition;

computer-readable code for analyzing the filter condition to determine a filter group of the filter condition, the filter group comprising a list of expressions over which the filter condition is computed;

computer-readable code for creating a derived table for computing the filter condition, the derived table having a select list, creating the derived table comprising;

adding the list of expressions of the filter group to the derived table as one or more columns of the derived table'"'"'s select list; and

adding the filter condition to the derived table as a one or more columns of the derived table'"'"'s select list;

computer-readable code for transforming the received query by combining the received query with the derived table, transforming the received query comprising;

traversing the select list of the received query and transforming one or more aggregates of the select list to reference one or more of the columns of the derived table;

transforming the from element of the received query to reference the derived table; and

adding a WHERE clause to the transformed original query, the WHERE clause based on the filter condition and referencing one or more of the columns of the derived table.

14. The computer program product as claimed in claim 13, the creating the derived table further comprising:

identifying aggregates in the select list of the received query having PREFILTER clause;

adding the identified PREFILTER aggregates from the select list of the received query to the select list of the derived table; and

replacing the PREFILTER aggregates in the select list of the received query with a reference to the added PREFILTER aggregate in the derived table.

15. The computer program product as claimed in claim 13, the analyzing the filter condition to determine the filter group further comprising:

identifying aggregates of the filter condition;

for each identified aggregate, identifying an aggregate group of the aggregate, the aggregate group comprising a list of expressions over which the aggregate is computed; and

selecting the aggregate group with the lowest level of granularity as the filter group.

16. The computer program product as claimed in claim 15, the identifying an aggregate group further comprising:

determining the type of the aggregate;

when the aggregate is a report aggregate having a standard aggregate counterpart, deriving the aggregate group based on a FOR clause of the aggregate; and

when the aggregate is a non-report aggregate, deriving the aggregate group based on an AT clause of the aggregate.

17. The computer program product as claimed in claim 15, wherein the standard aggregates comprise at least one of:

MIN;

MAX;

SUM;

AVG;

COUNT;

or

COUNT(*).

18. The computer program product as claimed in claim 13, further comprising computer-readable code for determining that the FILTER transformation can be performed, determining further comprising:

determining an aggregate group for each aggregate of the filter condition; and

determining that all of the determined aggregate groups are compatible, including;

determining the aggregate group with the lowest level of granularity; and

determining that each aggregate group is a subset of the aggregate group with the lowest level of granularity or that each aggregate group is identical to the aggregate group with the lowest level of granularity.