Performing Cross-Tabulation Using a Columnar Database Management System

• US 20140379697A1
• Filed: 06/18/2014
• Published: 12/25/2014
• Est. Priority Date: 06/21/2013
• Status: Active Grant

• Current Assignee: Open Text Holdings, Inc. (Open Text Corporation)
• Original Assignee: Actuate Corporation (Open Text Corporation)
• Inventors: Martn, Carles Bays, Poyatos, Jess Malo, Sierra, Marc Rodrguez, Prez, Alejandro Sualdea
• Granted Patent: US 9,600,539 B2
• Time in Patent Office: Days
• US Class Current: 707/722
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Cross tabulation operation is performed within a columnar database management system. The columnar database management system receives a request to perform a cross-tabulation operation on a set of database tables. The columnar database management system determines values of cross tabulation operation for each row of the result. The columnar database management system determines a domain for each value of the row dimension corresponding to a row combination. The columnar database management system determines an intersection set of the domains corresponding to values of the row dimensions for the row combination. The columnar database management system determines a value for the result column for the row combination as an aggregate value based on the records of the intersection set.

Claims

1. A computer-implemented method for performing cross tabulation operations in a columnar database, comprising:

receiving, by a columnar database management system, a request to perform a cross-tabulation operation on a set of database tables, the request specifying one or more row dimensions associated with columns of the database tables participating in the cross tabulation operation, wherein the columnar database stores the database tables in a columnar format;

determining, by the columnar database management system, a result of cross tabulation, the result comprising rows, each row having a combination of values of row dimensions, the determining comprising, for each row of the result associated with a combination of values of row dimensions;

for each value of a row dimension of the combination, determining a domain comprising a set of table records, the table records matching the value of the row dimension;

determining an intersection set of domains corresponding to the row combination, the intersection set comprising table records that match the combination of values corresponding to the row; and

determining a value for a result column for the row combination as an aggregate value based on the intersection set; and

storing the result of the cross tabulation.

2. The method of claim 1, wherein the request specifies a universal filter for a table, the universal filter specifying a filter value for a filter column of a table, the method further comprising, for each row of the result associated with a combination of values:

determining a filter domain for the filter value, the filter domain comprising a set of table records that satisfy the filter value for the filter column of the table; and

determining an intersection set of the filter domain with domains corresponding to the row combination.

3. The method of claim 1, wherein the intersection set of domains comprises a set of records of an input table, the method further comprising:

projecting the intersection set of domains corresponding to records of the input table to a result domain comprising a set of records of a result table such that the records of the result domain match the records of the intersection set of domains.

4. The method of claim 3, wherein the projecting of the intersection set of domains to a result domain comprises, for each table record of the input table corresponding to the intersection set of domain, determining a corresponding table record of the result table based on a foreign key relationship between the input table and the result table.

5. The method of claim 1, wherein the result columns of the cross-tabulation operation comprise pivot dimensions, each pivot dimension associated with a value of a column having a plurality of values, the method comprising:

for each value of the pivot dimension;

determining a pivot domain for the value of the pivot dimension, the pivot domain comprising a set of records that satisfy the value; and

determining an intersection set of the pivot domain with domains corresponding to values of row dimensions for the row combination.

6. The method of claim 1, wherein the aggregate value corresponding to a result column comprises one of a count of values, a sum of values, an average of values, a maximum of values, or a minimum of values.

7. The method of claim 1, wherein the result columns of the cross-tabulation operation include a result column comprising comparison values determined by applying an operator to results of a base filter and results of a target filter, the method comprising:

determining result values for the base filter;

determining result values for the target filter; and

determining the comparison values by applying the operator to the result values of the base filter and the result values of the target filter.

8. The method of claim 7, wherein the operator is one of a difference operator, a relative index operator, or a percent operator.

9. A non-transitory computer readable storage medium storing instructions for:

receiving, by a columnar database management system, a request to perform a cross-tabulation operation on a set of database tables, the request specifying one or more row dimensions associated with columns of the database tables participating in the cross tabulation operation, wherein the columnar database stores the database tables in a columnar format;

determining, by the columnar database management system, a result of cross tabulation, the result comprising rows, each row having a combination of values of row dimensions, the determining comprising, for each row of the result associated with a combination of values of row dimensions;

for each value of a row dimension of the combination, determining a domain comprising a set of table records, the table records matching the value of the row dimension;

determining an intersection set of domains corresponding to the row combination, the intersection set comprising table records that match the combination of values corresponding to the row; and

determining a value for a result column for the row combination as an aggregate value based on the intersection set; and

storing the result of the cross tabulation.

10. The non-transitory computer readable storage medium of claim 9, wherein the request specifies a universal filter for a table, the universal filter specifying a filter value for a filter column of a table, the storage medium further storing instructions for:

for each row of the result associated with a combination of values;

determining a filter domain for the filter value, the filter domain comprising a set of table records that satisfy the filter value for the filter column of the table; and

determining an intersection set of the filter domain with domains corresponding to the row combination.

11. The non-transitory computer readable storage medium of claim 9, wherein the intersection set of domains comprises a set of records of an input table, the storage medium further storing instructions for:

projecting the intersection set of domains corresponding to records of the input table to a result domain comprising a set of records of a result table such that the records of the result domain match the records of the intersection set of domains.

12. The non-transitory computer readable storage medium of claim 11, wherein the projecting of the intersection set of domains to a result domain comprises, for each table record of the input table corresponding to the intersection set of domain, determining a corresponding table record of the result table based on a foreign key relationship between the input table and the result table.

13. The non-transitory computer readable storage medium of claim 9, wherein the result columns of the cross-tabulation operation comprise pivot dimensions, each pivot dimension associated with a value of a column having a plurality of values, the storage medium further storing instructions for:

for each value of the pivot dimension;

determining a pivot domain for the value of the pivot dimension, the pivot domain comprising a set of records that satisfy the value; and

determining an intersection set of the pivot domain with domains corresponding to values of row dimensions for the row combination.

14. The non-transitory computer readable storage medium of claim 9, wherein the aggregate value corresponding to a result column comprises one of a count of values, a sum of values, an average of values, a maximum of values, or a minimum of values.

15. The non-transitory computer readable storage medium of claim 9, wherein the result columns of the cross-tabulation operation include a result column comprising comparison values determined by applying an operator to results of a base filter and results of a target filter, the storage medium storing instructions for:

determining result values for the base filter;

determining result values for the target filter; and

determining the comparison values by applying the operator to the result values of the base filter and the result values of the target filter.

16. The non-transitory computer readable storage medium of claim 15, wherein the operator is one of a difference operator, a relative index operator, or a percent operator.

17. A computer-implemented system comprising:

a computer processor; and

a non-transitory computer-readable storage medium storing instructions for;

receiving, by a columnar database management system, a request to perform a cross-tabulation operation on a set of database tables, the request specifying one or more row dimensions associated with columns of the database tables participating in the cross tabulation operation, wherein the columnar database stores the database tables in a columnar format;

determining, by the columnar database management system, a result of cross tabulation, the result comprising rows, each row having a combination of values of row dimensions, the determining comprising, for each row of the result associated with a combination of values of row dimensions;

for each value of a row dimension of the combination, determining a domain comprising a set of table records, the table records matching the value of the row dimension;

determining an intersection set of domains corresponding to the row combination, the intersection set comprising table records that match the combination of values corresponding to the row; and

determining a value for a result column for the row combination as an aggregate value based on the intersection set; and

storing the result of the cross tabulation.

18. The computer-implemented system of claim 17, wherein the request specifies a universal filter for a table, the universal filter specifying a filter value for a filter column of a table, the non-transitory computer-readable storage medium storing instructions for:

for each row of the result associated with a combination of values;

determining a filter domain for the filter value, the filter domain comprising a set of table records that satisfy the filter value for the filter column of the table; and

determining an intersection set of the filter domain with domains corresponding to the row combination.

19. The computer-implemented system of claim 17, wherein the intersection set of domains comprises a set of records of an input table, the non-transitory computer readable storage medium further storing instructions for:

projecting the intersection set of domains corresponding to records of the input table to a result domain comprising a set of records of a result table such that the records of the result domain match the records of the intersection set of domains.

20. The computer-implemented system of claim 17, wherein the result columns of the cross-tabulation operation comprise pivot dimensions, each pivot dimension associated with a value of a column having a plurality of values, the non-transitory computer readable storage medium further storing instructions for:

for each value of the pivot dimension;

determining a pivot domain for the value of the pivot dimension, the pivot domain comprising a set of records that satisfy the value; and

determining an intersection set of the pivot domain with domains corresponding to values of row dimensions for the row combination.