System and method of summary filter transformation

• US 20050038782A1
• Filed: 05/27/2004
• Published: 02/17/2005
• Est. Priority Date: 05/27/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Cosma, Marins, Styles, Michael E.
• Granted Patent: US 8,676,821 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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 system for summary filter transformation is provided. The system 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 system for summary filter transformation, the system comprising:

a summary filter analysis module for analysing 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.

2. A method of summary filter transformation, the method comprising the steps of:

analyzing a multidimensional query that is not supported by a target database system; and

transforming the multidimensional query into a semantically equivalent query that is supported by the target database system.

3. The method as claimed in claim 2, further comprising the steps of:

analysing a summary filter condition to determine an overall filter grouping level, comprising the steps of;

enumerating group levels using rules;

comparing enumerated groups to determine the overall grouping; and

selecting the overall grouping; and

analysing and replacing aggregates with equivalent expressions.

4. The method as claimed in claim 3, wherein the step of enumerating group levels includes the steps of:

deriving a specific group from each aggregate appearing in the filter condition;

for extended aggregates having a standard aggregate counterpart (MIN, MAX, SUM, AVG, COUNT, and COUNT(*)), deriving the group from the FOR clause;

for all other aggregates, deriving the group from the AT clause; and

for non-aggregate filter conditions, deriving the group from the detail column references.

5. The method as claimed in claim 3, wherein the step of analysing a summary filter condition to determine an overall filter grouping level comprises the step selecting the filter group (SNO) for a filter condition SUM(QTY)OVER(PARTITION BY SNO)>

100.

6. The method as claimed in claim 3, wherein the step of analysing a summary filter condition to determine an overall filter grouping level comprises the step selecting the filter group (SNO, PNO) for a filter condition SUM(QTY)OVER(PARTITION BY SNO,PNO)>

AVG(QTY)OVER(PARTITION BY SNO).

7. The method as claimed in claim 3, wherein the step of analysing a summary filter condition to determine an overall filter grouping level comprises the step selecting the filter group NULL for a filter condition RANK( )OVER(PARTITION BY SNO ORDER BY QTY)*AVG(QTY)OVER(PARTITION BY SNO)>

100.

8. The method as claimed in claim 3, wherein the step of analysing a summary filter condition to determine an overall filter grouping level comprises the step selecting the filter group (SNO, PNO, JNO) for a filter condition RANK( )OVER(AT SNO, PNO, JNO ORDER BY QTY)*SUM(QTY)OVER(PARTITION BY SNO)>

100.

9. The method as claimed in claim 3, wherein the step of analysing a summary filter condition to determine an overall filter grouping level comprises the step selecting the filter group NULL for a filter condition SUM(QTY)OVER(PARTITION BY SNO)>

SUM(QTY)OVER(PARTITION BY PNO).

10. The method as claimed in claim 3, wherein the step of analysing a summary filter condition to determine an overall filter grouping level comprises the step selecting the filter group (SNO) for a filter condition SNO>

‘

S’

.

11. The method as claimed in claim 3, wherein the step of analysing and replacing aggregates with equivalent expressions comprises the rules of:

for aggregates MIN, MAX, and SUM, the operand is replaced with an aggregate computed at the same level as the FILTER group, and an AT clause is introduced to eliminate duplicates values from the computation;

AVG is replaced with an equivalent expression involving SUM and COUNT; and

COUNT and COUNT(*) are replaced with equivalent SUM aggregate expressions.

12. A method of summary filter transformation, the method comprising the steps of:

analysing a summary filter transformation to determine an overall filter grouping level;

analysing a transformation select list to determine if a transformation is to be performed;

creating a derived table;

traversing the transformation select list to move PREFILTER aggregates and aggregates computed at the filter grouping level into the derived table; and

extracting and moving aggregates from the summary filter into a derived table select list.

13. A computer data signal embodied in a carrier wave and representing sequences of instructions which, when executed by a processor, cause the processor to perform a method of summary filter transformation, the method comprising the steps of:

analysing a multidimensional query that is not supported by a target database system; and

transforming the multidimensional query into a semantically equivalent query that is supported by the target database system.

14. A computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of summary filter transformation, the method comprising the steps of:

analysing a multidimensional query that is not supported by a target database system; and

transforming the multidimensional query into a semantically equivalent query that is supported by the target database system.

15. A computer program product for use in the execution in a computer of a group query transformation system for summary filter transformation, the computer program product comprising:

a summary filter analysis module for analysing 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.