Materialized samples for a business warehouse query

• US 7,610,272 B2
• Filed: 11/29/2004
• Issued: 10/27/2009
• Est. Priority Date: 11/29/2004
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Zurek, Thomas
• Primary Examiner(s): Stevens; Robert
• Application Number:
  US10/999,744
• Publication Number:
  US 20060116984A1
• Time in Patent Office: 1,793 Days
• Field of Search: 707/3, 707/1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A system and method for improving a query on a relational database in a business intelligence system is provided. A multidimensional data table is provided in the database. A sampling dimension is appended to the data table. The sampling dimension includes a number of cells, and wherein each cell includes a sampling value corresponding to the data of at least one of the dimensions of the data table. The data table is then clustered in at least one of the dimensions based on the associated sampling value in the sampling dimension. A query for a subset of data can then be executed on the clustered data table based on the sampling values.

Claims

1. A computer-implemented method of improving a query of a relational database, the method comprising:

providing a table T stored in the relational database, the table T having X number of columns and Y number of rows;

generating a sampling column having Y cells, wherein each cell includes a sampling value for each of the Y rows in the table T, the sampling value, stored on a storage medium, representative of whether a query of the relational database will sample a corresponding one of the Y rows, the sample value of each of the Y rows comprising a first value when the row is not part of a sample, a second value when the row is part of a first sample, and a third value when the row is part of a second sample;

appending the sampling column to the table T stored in the relational database, the table including the appended sampling column to enable the query; and

providing, when a row is added to the table T, one of the first value, the second value, or the third value, to the row in the sampling column.

2. The computer-implemented method in accordance with claim 1, further comprising clustering the rows of table T according to the sampling values in the sampling column.

3. The computer-implemented method in accordance with claim 2, further comprising grouping rows having the same values, the grouped rows having the same values stored in close proximity on a storage medium.

4. The computer-implemented method in accordance with claim 3, further comprising arranging the grouped rows according to the sampling values of the associated cells of the sampling column.

5. The computer-implemented method in accordance with claim 2, further comprising receiving a query for data from the table T.

6. The computer-implemented method in accordance with claim 5, further comprising executing the query on the clustered rows of the table T.

7. The computer-implemented method in accordance with claim 1, further comprising indexing the sampling column.

8. The computer-implemented method in accordance with claim 7, wherein indexing the sampling column includes adding an index for the sampling column to the database.

9. The computer-implemented method in accordance with claim 7, wherein indexing the sampling column includes incorporating the sampling column into an existing index in the database.

10. The computer-implemented method in accordance with claim 7, further comprising generating an index for the sampling column.

11. A system for optimizing a query on a relational database, the system comprising:

a table T stored in the relational database on a storage medium, the table T having X number of columns and Y number of rows; and

a sample generator configured to generate a sampling column having Y cells, wherein each cell includes a sampling value, stored in the storage medium, for each of the Y rows in the table T, the sampling value representative of whether a query of the relational database will sample a corresponding one of the Y rows, and further configured to append the sampling column to the table T stored in the relational database, the table including the appended sampling column to enable the query, the sample value of each of the Y rows comprising a first value when the row is not part of a sample, a second value when the row is part of a first sample, and a third value when the row is part of a second sample.

12. The system in accordance with claim 11, further comprising a clustering tool configured to cluster the rows of table T according to the sampling values in the sampling column.

13. The system in accordance with claim 11, further comprising an index generator for indexing the sampling column in the database.

14. The system in accordance with claim 13, wherein the index generator is configured to generate a new index for the sampling column.

15. The system in accordance with claim 13, wherein the index generator is configured to incorporate the sampling column in an existing index in the database.

16. A computer-implemented method of executing a query on a relational database, the method comprising:

providing a multidimensional data table stored in the database;

appending a sampling dimension to the data table, wherein the sampling dimension includes a number of cells, and wherein each cell includes a sampling value, stored in a storage medium corresponding to the data of at least one of the dimensions of the data table, the sample value of each of the cells comprises a first value when the row is not part of a sample, a second value when the cell is part of a first sample, and a third value when the cell is part of a second sample;

clustering the data table in at least one of the dimensions based on the associated sampling value in the sampling dimension;

receiving a query for a subset of data stored in the database; and

executing the query on the clustered data table based on the sampling values corresponding to the subset of data stored in the database.

17. The computer-implemented method in accordance with claim 16, further comprising generating an index for the sampling dimension in the database.

18. The computer-implemented method in accordance with claim 16, wherein the data table includes X rows and Y columns, and wherein the sampling dimension includes a column of X cells.

19. The computer-implemented method in accordance with claim 16, wherein receiving a query further includes receiving instructions whether to execute the query based on the sampling values.

20. The computer-implemented method in accordance with claim 16, further comprising generating a result of the query based on the sampling values corresponding to the subset of data.