Performing cross-tabulation using a columnar database management system

• US 10,282,355 B2
• Filed: 02/10/2017
• Issued: 05/07/2019
• Est. Priority Date: 06/21/2013
• Status: Active Grant

• Current Assignee: Open Text Holdings, Inc. (Open Text Corporation)
• Original Assignee: Open Text Holdings, Inc. (Open Text Corporation)
• Inventors: Martin, Carles Bayes, Poyatos, Jesus Malo, Sierra, Marc Rodriguez, Perez, Alejandro Sualdea
• Primary Examiner(s): Gortayo, Dangelino N
• Application Number:
  US15/430,223
• Publication Number:
  US 20170154079A1
• Time in Patent Office: 816 Days
• Field of Search: 707722, 707776, 707600, 707713, 707769, 707778, 707792, 707803
• 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 columnar database data mining method, comprising:

receiving a data mining request, the data mining request indicating a cross-tabulation operation, a first table, and a second table, the receiving performed by the columnar database management system, the columnar database management system having a processor and a non-transitory computer-readable memory;

performing, by the columnar database management system, the cross-tabulation operation on the first table and the second table, the cross-tabulation operation comprising a horizontal collapse process and a vertical collapse process,

the horizontal collapse process comprising;

determining domains, each domain comprising a set of records of the first table, the set of record matching a condition or value in a column of the first table, the column representing a row dimension for a result table, each row of the result table defining a record resulting from the cross-tabulation operation; and

determining an intersection set of domains;

the vertical collapse process comprising;

moving the domains towards a cross-tabulation resolution level, the moving comprising mapping sets of records in the intersection set of domains to records of the second table; and

determining, by the columnar database management system, a data mining result, the data mining result comprising an aggregate value from a record of the result table.

2. The columnar database data mining method according to claim 1, wherein the data mining request is received from a client device by an applicant frontend of the columnar database management system and wherein the data mining result is provided to the client device by the applicant frontend.

3. The columnar database data mining method according to claim 1, wherein the cross-tabulation operation is performed by a database engine of the columnar database management system, the database engine further configured to create, read, update, and delete data stored by the columnar database management system.

4. The columnar database data mining method according to claim 3, wherein the data mining request is received from the client device by the database engine of the columnar database management system.

5. The columnar database data mining method according to claim 3, wherein the database engine comprises a bubbler module and an operator module, wherein the bubbler module is configured for determining an interaction set of a pivot domain with domains corresponding to values in the column of the first table, and wherein the operator module is configured for determining the aggregate value utilizing the intersection set as a cross-tabulation measure.

6. The columnar database data mining method according to claim 5, wherein the pivot domain is determined by the database engine for each discrete value of a pivot dimension specified in the data mining request, the pivot domain comprising a set of records that satisfy the condition or value in the column of the first table.

7. The columnar database data mining method according to claim 5, wherein the cross-tabulation measure compares results from a target filter against a base filter, the comparison determined by computing a function that takes an input value from the base filter and an input value from the target filter and determines a difference, a relative index, a percentage, or any comparative function.

8. The columnar database data mining method according to claim 5, wherein the database engine determines result values for the base filter and the target filter and provides the result values to the operator module, and wherein the operator module determines a final value for the cross-tabulation measure by applying a comparison operator specified by the cross-tabulation measure to the results values input to the operator module.

9. The columnar database data mining method according to claim 1, wherein determining a domain comprises determining a discrete value in the column of the first table and wherein the domain represents records from the first table having the discrete value in the first column.

10. The columnar database data mining method according to claim 1, wherein the intersection set of domains is determined by intersecting domains of records from the first table or the second table.

11. A columnar database management system, comprising:

a processor;

a non-transitory computer-readable medium; and

stored instructions translatable by the processor to perform;

receiving a data mining request, the data mining request indicating a cross-tabulation operation, a first table, and a second table;

performing the cross-tabulation operation on the first table and the second table, the cross-tabulation operation comprising a horizontal collapse process and a vertical collapse process,

the horizontal collapse process comprising;

determining domains, each domain comprising a set of records of the first table, the set of record matching a condition or value in a column of the first table, the column representing a row dimension for a result table, each row of the result table defining a record resulting from the cross-tabulation operation; and

determining an intersection set of domains;

the vertical collapse process comprising;

moving the domains towards a cross-tabulation resolution level, the moving comprising mapping sets of records in the intersection set of domains to records of the second table; and

determining a data mining result, the data mining result comprising an aggregate value from a record of the result table.

12. The columnar database management system of claim 11, further comprising:

an applicant frontend, wherein the data mining request is received from a client device by the applicant frontend and wherein the data mining result is provided to the client device by the applicant frontend.

13. The columnar database management system of claim 11, further comprising:

a database engine, wherein the cross-tabulation operation is performed by the database engine, the database engine further configured to create, read, update, and delete data stored by the columnar database management system.

14. The columnar database management system of claim 13, wherein the data mining request is received from the client device by the database engine.

15. The columnar database management system of claim 13, wherein the database engine comprises a bubbler module and an operator module, wherein the bubbler module is configured for determining an interaction set of a pivot domain with domains corresponding to values in the column of the first table, and wherein the operator module is configured for determining the aggregate value utilizing the intersection set as a cross-tabulation measure.

16. The columnar database management system of claim 15, wherein the pivot domain is determined by the database engine for each discrete value of a pivot dimension specified in the data mining request, the pivot domain comprising a set of records that satisfy the condition or value in the column of the first table.

17. The columnar database management system of claim 15, wherein the cross-tabulation measure compares results from a target filter against a base filter, the comparison determined by computing a function that takes an input value from the base filter and an input value from the target filter and determines a difference, a relative index, a percentage, or any comparative function.

18. The columnar database management system of claim 15, wherein the database engine determines result values for the base filter and the target filter and provides the result values to the operator module, and wherein the operator module determines a final value for the cross-tabulation measure by applying a comparison operator specified by the cross-tabulation measure to the results values input to the operator module.

19. The columnar database management system of claim 11, wherein determining a domain comprises determining a discrete value in the column of the first table and wherein the domain represents records from the first table having the discrete value in the first column.

20. The columnar database management system of claim 11, wherein the intersection set of domains is determined by intersecting domains of records from the first table or the second table.