DISTRIBUTED DATA WAREHOUSE

• US 20140101201A1
• Filed: 10/10/2012
• Published: 04/10/2014
• Est. Priority Date: 10/10/2012
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Yan, An, Zhao, Mingyang, Wang, Robert C., Chen, Yu, Anderson, Blake
• Granted Patent: US 9,858,326 B2
• Time in Patent Office: Days
• US Class Current: 707/776
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Methods and data structures are provided for allowing data mining with improved efficiency. During processing of a usage log (or multiple logs) for an activity, such as a usage logfile of network search activity, a common fact table is generated. The common fact table allows a plurality of auxiliary data structures to be formed from the common fact table. These auxiliary data structures are designed to allow users to submit queries against the contents of the data structure in order to investigate the data. The efficiency of access of the common fact table is improved by allowing users to access auxiliary data structures other than the auxiliary data structures that are associated with a user. Optionally, the common fact table and/or the auxiliary data structures can include dimension values that correspond to both pre-identified dimension values as well as dimension values that are identified during processing of the activity logfiles.

Claims

1. One or more computer-storage media storing computer-useable instructions that, when executed by a computing device, perform a method for mining data, comprising:

aggregating a plurality of definitions for auxiliary data structures, the definitions including a plurality of measures and one or more dimensions, the definition of each dimension being associated with at least one measure from the plurality of measures;

constructing a common fact table including the plurality of measures and dimension keys for the one or more dimensions, the common fact table being constructed based on using the aggregated definitions of the plurality of auxiliary data structures to process one or more initial data files;

constructing one or more dimension tables corresponding to the one or more dimensions, the one or more dimension tables being stored separately from the common fact table;

forming a plurality of auxiliary data structures, each auxiliary data structure including one or more measures and one or more dimensions, a first user being associated with a first subset of the auxiliary data structures;

receiving a user data query from the first user, the user data query comprising a combination of a measure and a dimension;

identifying an auxiliary data structure from the plurality of auxiliary data structures that includes the combination of the measure and the dimension, the identified auxiliary data structure being different from the first subset of auxiliary data structures;

generating a responsive result to the user data query based on the identified auxiliary data structure; and

providing the generated responsive result to the first user.

2. The computer-storage media of claim 1, wherein at least one dimension of a first auxiliary data structure is different from each dimension of a second auxiliary data structure.

3. The computer-storage media of claim 1, wherein at least one measure of a first auxiliary data structure is different from each measure of a second auxiliary data structure.

4. The computer-storage media of claim 1, wherein each measure of a first auxiliary data structure is different from each measure of a second auxiliary data structure.

5. The computer-storage media of claim 1, wherein identifying the auxiliary data structure comprising identifying the auxiliary data structure based on a listing of auxiliary data structures generated from the common fact table.

6. The computer-storage media of claim 1, wherein a plurality of the aggregated definitions comprise user-defined definitions.

7. The computer-storage media of claim 6, wherein an auxiliary data structure formed from the common fact table comprises a measure from a first user-defined definition and a dimension from a second user-defined definition, the dimension from the second user-defined definition being different from the dimensions in the first user-defined definition.

8. The computer-storage media of claim 1, wherein the aggregated plurality of definitions comprises one or more definitions that are associated with the first user, at least one of the measure and the dimension in the user data query being different from measures and dimensions in the one or more definitions associated with the first user.

9. The computer-storage media of claim 8, wherein the one or more definitions are associated with the first user based on the first user being a member of a group of users associated with the one or more definitions.

10. The computer-storage media of claim 1, wherein providing a first generated responsive result comprises displaying at least a portion of the first responsive result to the first user.

20. The computer-storage media of claim 1, wherein the aggregated plurality of definitions comprises one or more definitions that are associated with the first user, at least one of the measure and the dimension in the user data query being different from measures and dimensions in the one or more definitions associated with the first user.

11. A computer-implemented method for mining data, comprising:

aggregating a plurality of definitions for auxiliary data structures, the definitions including a plurality of measures and a plurality of dimensions, the definitions comprising a plurality of managed dimension values for at least one dimension;

processing one or more initial data files based on the aggregated definitions to extract values for the plurality of measures and the plurality of dimensions, the extracted values including one or more unmanaged dimension values for the at least one dimension;

validating the one or more unmanaged dimension values;

constructing a common fact table including the plurality of measures and dimension keys for the one or more dimensions, the common fact table being constructed based on the extracted values;

constructing one or more dimension tables corresponding to the plurality of dimensions based on the extracted values, the one or more dimension tables being stored separately from the common fact table;

forming a plurality of auxiliary data structures, each auxiliary data structure including one or more measures and one or more dimensions, at least one auxiliary dimension table including a dimension having validated unmanaged dimension values;

receiving a user data query, the user data query comprising one or more combinations of measures and dimensions;

generating a responsive result to the user data query based on at least one of the plurality of auxiliary data structures; and

providing the generated responsive result.

12. The method of claim 11, wherein the generated responsive result comprises a dimension having validated unmanaged dimension values.

13. The method of claim 11, wherein the one or more unmanaged dimension values are validated based on a number of instances of each unmanaged dimension value being less than a first threshold value.

14. The method of claim 11, wherein the one or more unmanaged dimension values are validated based on the one or more unmanaged dimension values being less than a second threshold value.

15. The method of claim 11, wherein the one or more unmanaged dimension values are validated based on a plurality of combinations of first threshold values for a number of instances of a dimension value and second threshold values for a number of unmanaged dimension values.

16. The method of claim 11, wherein validating the one or more unmanaged dimension values comprises matching a format of an unmanaged dimension value to a format validation rule.

17. The method of claim 11, wherein the user data query is received form a first user, the aggregated plurality of definitions comprising one or more definitions that are associated with the first user, and at least one of the measure and the dimension in the user data query are different from measures and dimensions in the one or more definitions associated with the first user.

18. The method of claim 11, wherein at least one auxiliary data structure formed from the common fact table comprises a measure from a first user-defined definition and a dimension from a second user-defined definition, the dimension from the second user-defined definition being different from the dimensions in the first user-defined definition.

19. A computer-implemented method for mining data, comprising:

aggregating a plurality of definitions for auxiliary data structures, the definitions including a plurality of measures and a plurality of dimensions, the definitions comprising a plurality of managed dimension values for at least one dimension, the definition of each dimension being associated with at least one measure from the plurality of measures;

processing one or more initial data files based on the aggregated definitions to extract values for the plurality of measures and the plurality of dimensions, the extracted values including one or more unmanaged dimension values for the at least one dimension;

validating the one or more unmanaged dimension values;

constructing a common fact table including the plurality of measures and dimension keys for the plurality of dimensions, the common fact table being constructed based on the extracted values;

constructing one or more dimension tables corresponding to the plurality of dimensions based on the extracted values, the one or more dimension tables being stored separately from the common fact table;

forming a plurality of auxiliary data structures, each auxiliary data structure including one or more measures and one or more dimensions, at least one auxiliary dimension table including a dimension having validated unmanaged dimension values, a first user being associated with a first subset of the auxiliary data structures;

receiving a user data query from the first user, the user data query including a combination of a measure and a dimension;

identifying an auxiliary data structure from the plurality of auxiliary data structures that includes the combination of the measure and the dimension, the identified auxiliary data structure being different from the first subset of auxiliary data structures; and

generating a responsive result to the user data query based on the identified auxiliary data structure.