# US 7,610,272 B2 · Materialized samples for a business warehouse query > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 7,610,272 B2 - Document ID: US7610272B2 - Status: Granted - Kind: B2 - Application number: 10/999,744 - Filed: 2004-11-29 - Published: 2006-06-01 - Granted: 2009-10-27 - Earliest claimed: 2004-11-29 - Assignees: Sap SE - Inventors: Thomas Zurek - Web page: https://patentgrep.com/patent/US7610272B2 ## CPC - G06F 16/283: {Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP} - Y10S 707/99931: Database or file accessing - Y10S 707/99933: Query processing, i.e. searching - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) - Y10S: TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS ## 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 ### Claim 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. ### Claim 2 (depends on 1) 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. ### Claim 3 (depends on 2) 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. ### Claim 4 (depends on 3) 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. ### Claim 5 (depends on 2) The computer-implemented method in accordance with claim 2, further comprising receiving a query for data from the table T. ### Claim 6 (depends on 5) The computer-implemented method in accordance with claim 5, further comprising executing the query on the clustered rows of the table T. ### Claim 7 (depends on 1) The computer-implemented method in accordance with claim 1, further comprising indexing the sampling column. ### Claim 8 (depends on 7) 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. ### Claim 9 (depends on 7) 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. ### Claim 10 (depends on 7) The computer-implemented method in accordance with claim 7, further comprising generating an index for the sampling column. ### Claim 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. ### Claim 12 (depends on 11) 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. ### Claim 13 (depends on 11) The system in accordance with claim 11, further comprising an index generator for indexing the sampling column in the database. ### Claim 14 (depends on 13) The system in accordance with claim 13, wherein the index generator is configured to generate a new index for the sampling column. ### Claim 15 (depends on 13) 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. ### Claim 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. ### Claim 17 (depends on 16) The computer-implemented method in accordance with claim 16, further comprising generating an index for the sampling dimension in the database. ### Claim 18 (depends on 16) 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. ### Claim 19 (depends on 16) 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. ### Claim 20 (depends on 16) 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.