Performing cross-tabulation using a columnar database management system

• US 9,600,539 B2
• Filed: 06/18/2014
• Issued: 03/21/2017
• 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
• Primary Examiner(s): GORTAYO, DANGELINO N
• Application Number:
  US14/307,711
• Publication Number:
  US 20140379697A1
• Time in Patent Office: 1,007 Days
• Field of Search: 707/722, 707/714, 707/754, 707/776, 707/769
• US Class Current: 1/1
• 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 database tables, the request specifying the database tables as input tables and one or more row dimensions for a result table, the one or more row dimensions representing columns of the database tables, wherein the database tables are stored in a columnar format;

performing, by the columnar database management system, the cross-tabulation operation on the input tables,

the cross-tabulation operation comprising a horizontal collapse process and a vertical collapse process,

the horizontal collapse process comprising;

determining domains corresponding to the one or more row dimensions for the result table, each domain comprising a set of records of a first input table of the input tables that match a value in a column of the first input table represented by a row dimension of the one or more row dimensions for the result table; and

determining an intersection set of domains corresponding to the first input table;

the vertical collapse process comprising;

moving the domains corresponding to the one or more row dimensions for the result table towards a cross-tabulation resolution level, the moving comprising mapping sets of records in the intersection set of domains to records of a second input table of the input tables;

generating the result table, the generating performed by the columnar database management system, the result table comprising rows, each row of the result table having a combination of values of the one or more row dimensions and defining a record resulting from the cross-tabulation operation,

result table further comprising a column for storing an aggregate value corresponding to the record resulting from the cross-tabulation operation; and

storing the result table of the cross tabulation operation.

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

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 result table; and

determining an intersection set of the filter domain with domains corresponding to the each row of the result table.

3. The method of claim 1, further comprising:

projecting the intersection set of domains corresponding to records of the first input table to a result domain comprising a set of records of the result table such that the set of 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 record of the first input table corresponding to the intersection set of domains, determining a corresponding record of the result table based on a foreign key relationship between the first input table and the result table.

5. The method of claim 1, wherein the result table comprises a pivot dimension

corresponding to a column of an input table, the pivot dimension including a pivot column in the result table for each distinct value of the column of the input table.

6. The method of claim 1, wherein the aggregate value 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 table further comprises a result column, the 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 translatable by a computer implementing a columnar database management system for:

receiving a request to perform a cross-tabulation operation on database tables, the request specifying the database tables as input tables and one or more row dimensions for a result table, the one or more row dimensions representing columns of the database tables, wherein the database tables are stored in a columnar format;

performing the cross-tabulation operation on the input tables,

the cross-tabulation operation comprising a horizontal collapse process and a vertical collapse process,

the horizontal collapse process comprising;

determining domains corresponding to the one or more row dimensions for the result table, each domain comprising a set of records of a first input table of the input tables that match a value in a column of the first input table represented by a row dimension of the one or more row dimensions for the result table; and

determining an intersection set of domains corresponding to the first input table;

the vertical collapse process comprising;

moving the domains corresponding to the one or more row dimensions for the result table towards a cross-tabulation resolution level, the moving comprising mapping sets of records in the intersection set of domains to records of a second input table of the input tables;

generating the result table, the result table comprising rows, each row of the result table having a combination of values of the one or more row dimensions and defining a record resulting from the cross-tabulation operation, the

result table further comprising a column for storing an aggregate value corresponding to the record resulting from the cross-tabulation operation; and

storing the result table of the cross tabulation operation.

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

for each row of the result table;

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 result table; and

determining an intersection set of the filter domain with domains corresponding to the each row of the result table.

11. The non-transitory computer-readable storage medium of claim 9, the computer-readable storage medium further storing instructions for:

projecting the intersection set of domains corresponding to records of the first input table to a result domain comprising a set of records of the result table such that the set of 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 record of the first input table corresponding to the intersection set of domains, determining a corresponding record of the result table based on a foreign key relationship between the first input table and the result table.

13. The non-transitory computer-readable storage medium of claim 9, wherein the result table comprises a pivot dimension

corresponding to a column of an input table, the pivot dimension including a pivot column in the result table for each distinct value of the column of the input table.

14. The non-transitory computer-readable storage medium of claim 9, wherein the aggregate value 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 table further comprises a result column, the result column comprising comparison values determined by applying an operator to results of a base filter and results of a target filter, the computer-readable 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 translatable by the computer processor for;

receiving a request to perform a cross-tabulation operation on database tables, the request specifying the database tables as input tables and one or more row dimensions for a result table, the one or more row dimensions representing columns of the database tables, wherein the database tables are stored in a columnar format;

performing the cross-tabulation operation on the input tables,

the cross-tabulation operation comprising a horizontal collapse process and a vertical collapse process,

the horizontal collapse process comprising;

determining domains corresponding to the one or more row dimensions for the result table, each domain comprising a set of records of a first input table of the input tables that match a value in a column of the first input table represented by a row dimension of the one or more row dimensions for the result table; and

determining an intersection set of domains corresponding to the first input table;

the vertical collapse process comprising;

moving the domains corresponding to the one or more row dimensions for the result table towards a cross-tabulation resolution level, the moving comprising mapping sets of records in the intersection set of domains to records of a second input table of the input tables;

generating the result table, the result table comprising rows, each row of the result table having a combination of values of the one or more row dimensions and defining a record resulting from the cross-tabulation operation, the

result table further comprising a column for storing an aggregate value corresponding to the record resulting from the cross-tabulation operation; and

storing the result table of the cross tabulation operation.

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

for each row of the result table;

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 result table; and

determining an intersection set of the filter domain with domains corresponding to the each row of the result table.

19. The computer-implemented system of claim 17, the non-transitory computer-readable storage medium further storing instructions for:

projecting the intersection set of domains corresponding to records of the first input table to a result domain comprising a set of records of the result table such that the set of 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 table comprises a pivot dimension

corresponding to a column of an input table, the pivot dimension including a pivot column in the result table for each distinct value of the column of the input table.