Materialized samples for a business warehouse query

• US 20060116984A1
• Filed: 11/29/2004
• Published: 06/01/2006
• Est. Priority Date: 11/29/2004
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Zurek, Thomas
• Granted Patent: US 7,610,272 B2
• Time in Patent Office: Days
• 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 method of improving a query of a relational database, the method comprising:

providing a table T in the 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; and

appending the sampling column to the table T in the database.

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

3. A method in accordance with claim 2, further comprising grouping rows having the same values.

4. A 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. A method in accordance with claim 2, further comprising receiving a query for data from the table T.

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

7. A method in accordance with claim 1, further comprising indexing the sampling column.

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

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

10. A 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 database, the table T having X number of columns and Y number of rows;

a sample generator configured to generate a sampling column having Y cells, wherein each cell includes a sampling value for each of the Y rows in the table T, and further configured to append the sampling column to the table T in the database.

12. A 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. A system in accordance with claim 11, further comprising an index generator for indexing the sampling column in the database.

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

15. A 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 method of executing a query on a relational database, the method comprising:

providing a multidimensional data table 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 corresponding to the data of at least one of the dimensions of the data table;

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 in the database; and

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

17. A method in accordance with claim 16, further comprising generating an index for the sampling dimension in the database.

18. A 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. A 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. A 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.