Custom Query Parameters in a Database System

• US 20180046676A1
• Filed: 12/19/2016
• Published: 02/15/2018
• Est. Priority Date: 08/12/2016
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Farr, Ashley
• Granted Patent: US 10,824,614 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/2237 Vectors, bitmaps or matrices
• CPC Class Codes: G06F 16/2264 Multidimensional index structures
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/235 Update request formulation
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/2386 Bulk updating operations data conversion details G06F16/258
• CPC Class Codes: G06F 16/24537 of operators
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

A method stores records for a set of entities that are generated using an input parameter that is not based on a date. A query is received that includes one or more date parameters, the query for aggregating a value. Upon receiving the query, the method performs: selecting a set of records from the stored records that are valid based on comparing first date information determined from the one or more date parameters and second date information from the records; performing an aggregation calculation of the value for the set of records to generate a query result; and returning the query result in response to the query.

Claims

1. A method comprising:

storing, by a computing device, records for a set of entities that are generated using an input parameter that is not based on a date;

receiving, by the computing device, a query that includes one or more date parameters, the query for aggregating a value;

upon receiving the query, performing;

selecting, by the computing device, a set of records from the stored records that are valid based on comparing first date information determined from the one or more date parameters and second date information from the records;

performing, by the computing device, an aggregation calculation of the value for the set of records to generate a query result; and

returning, by the computing device, the query result in response to the query.

2. The method of claim 1, wherein storing the records comprises:

analyzing values for the input parameter for each entity in the set of entities; and

generating the records based on a change in value of the input parameter for each entity.

3. The method of claim 2, wherein a new record is generated for an entity when the value for the input parameter changes.

4. The method of claim 2, wherein:

each record includes a from date and a to date based on the input parameter,

the from date being a first date from which the record is valid for the input parameter, and

the to date being a second date to which the record is valid for the input parameter.

5. The method of claim 1, wherein the input parameter is a dimension that does not include date values.

6. The method of claim 1, wherein the one or more date parameters are used to determine one or more of a from date and a to date, the from date being a first date to start the aggregation and the to date being a second date to end the aggregation.

7. The method of claim 1, wherein the query is for aggregating a measure.

8. The method of claim 1, wherein selecting the set of records from the stored records that are valid comprises:

determining which records are valid based on one or more of a from date and a to date associated with the records, the from date being a first date from which a record is valid for the input parameter and the to date being a second date to which a record is valid for the input parameter.

9. The method of claim 1, further comprising:

calculating a new set of values for the records that are valid based on the one or more date parameters.

10. The method of claim 9, wherein performing the aggregation calculation comprises:

aggregating the new set of values for the records to generate the query result.

11. The method of claim 1, further comprising:

passing the query to a cube data structure that performs the selecting of the valid records and the performing of the aggregation calculation.

12. A non-transitory computer-readable storage medium containing instructions, that when executed, control a computer system to be configured for:

storing records for a set of entities that are generated using an input parameter that is not based on a date;

receiving a query that includes one or more date parameters, the query for aggregating a value;

upon receiving the query, performing;

selecting a set of records from the stored records that are valid based on comparing first date information determined from the one or more date parameters and second date information from the records;

performing an aggregation calculation of the value for the set of records to generate a query result; and

returning the query result in response to the query.

13. The non-transitory computer-readable storage medium of claim 12, wherein storing the records comprises:

analyzing values for the input parameter for each entity in the set of entities; and

generating the records based on a change in value of the input parameter for each entity.

14. The non-transitory computer-readable storage medium of claim 12, wherein selecting the set of records from the stored records that are valid comprises:

determining which records are valid based on one or more of a from date and a to date associated with the records, the from date being a first date from which a record is valid for the input parameter and the to date being a second date to which a record is valid for the input parameter.

15. The non-transitory computer-readable storage medium of claim 12, further comprising:

calculating a new set of values for the records that are valid based on the one or more date parameters; and

aggregating the new set of values for the records to generate the query result.

16. A method comprising:

storing, by a computing device, records for a set of entities that are based on an input parameter that is not based on a date, wherein each record includes a first from date and a first to date, the first from date being a first date from which a record is valid for the input parameter and the first to date being a second date to which a record is valid for the input parameter;

receiving, by the computing device, a query that includes one or more date parameters, the query for aggregating a value;

upon receiving the query, performing;

selecting, by the computing device, a set of records from the stored records that are valid based on comparing one or more of a second from date and a second to date that is determined from the one or more date parameters and one or more of the first from date and first to date from the records, the second from date being a third date to start the aggregation and the second to date being a third date to end the aggregation;

performing, by the computing device, an aggregation calculation of the value for the set of records to generate a query result; and

returning, by the computing device, the query result in response to the query.

17. The method of claim 16, wherein the one or more date parameters are used to determine one or more of the second from date and the second to date.

18. The method of claim 16, wherein the query includes the one or more of the second from date and the second to date.

19. The method of claim 16, wherein storing the records comprises:

analyzing values for the input parameter for each entity in the set of entities; and

generating the records based on a change in value of the input parameter for each entity, wherein each record includes the first from date and the first to date that are determined based on the change.

20. The method of claim 16, wherein selecting the set of records from the stored records that are valid comprising:

determining which records are valid based on one or more of second from date and the second to date being valid based on the one or more of the first from date and the first to date.