Detecting and displaying exceptions in tabular data

• US 7,797,264 B2
• Filed: 02/02/2007
• Issued: 09/14/2010
• Est. Priority Date: 02/02/2007
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Crivat, Ioan Bogdan, MacLennan, C. James, Iyer, Raman S.
• Primary Examiner(s): Sparks; Donald
• Assistant Examiner(s): Kim; David H
• Application Number:
  US11/670,735
• Publication Number:
  US 20080189238A1
• Time in Patent Office: 1,320 Days
• Field of Search: 706/52, 706/45, 707/6, 707/100, 707/776
• US Class Current: 706/45
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures

Abstract

Data expressed as tabular data having columns and rows can be analyzed and data determined to be an exception can be flagged. In addition, reasons for flagging such data as exceptions can be presented to a user to facilitate further analysis and action on the data. A predictive analysis component can utilize a clustering algorithm with predictive capabilities to autonomously analyze the data. Periodic re-analysis of the data can be performed to determine if exceptions have changed based on new or modified data.

Claims

1. A system that facilitates automatic detection of exceptions in tabular data, the system comprising a processor and memory storing computer executable components, the computer executable components comprising:

an application that contains tabular data expressed as a plurality of rows that include a plurality of columns; and

a predictive analysis component that receives the tabular data and predicts exceptions in the tabular data utilizing a probabilistic clustering algorithm with predictive capabilities, the predictive analysis component comprising;

a clustering module that groups the plurality of rows into one or more groups using the clustering algorithm, wherein the plurality of rows are grouped based on similarities between values in the columns of each of the rows in each group, and wherein the clustering module further identifies at least one row from the plurality of rows that is not grouped with at least one other row from the plurality of rows; and

a predictive module that determines a group to which the identified at least one row is most similar and compares the value of each column in the at least one row to a predicted value for the corresponding column of rows that are grouped into the most similar group, and based on the comparison determines at least one column of the identified at least one row that contains a value that is most different from the corresponding predicted value such that the at least one column is identified as having caused the identified at least one row to not be grouped with the most similar group.

2. The system of claim 1, wherein, for each row, the probabilistic clustering algorithm generates a data point in a multi-dimensional bell curve that has peaks and troughs, and wherein a peak represents one of the one or more groups such that as a data point moves away from a particular peak, the probability that the row represented by the data point is within the group represented by the particular peak diminishes.

3. The system of claim 2, wherein the group to which the identified at least one row is most similar is determined by finding the peak to which the data point representing the identified at least one row is closest.

4. The system of claim 1, further comprising a refresh module that periodically invokes the predictive analysis component to reevaluate the predicted exceptions when tabular data is modified, deleted or added.

5. The system of claim 1, further comprising a display component that presents the exceptions to a user.

6. The system of claim 1, the predictive analysis component is executed on a server independent of the location of the tabular data.

7. The system of claim 1, further comprising an interface component that allows a user to interact with the tabular data or the predictive analysis component.

8. The system of claim 1, the tabular data is contained in a spreadsheet application.

9. The system of claim 1, the tabular data is contained in an XML application.

10. A method for exception detection, comprising:

building a predictive clustering model on tabular data using a probabilistic clustering algorithm, wherein the clustering model comprises a plurality of groups of rows, wherein the rows are grouped based on similarities between values in the columns of each of the rows in each group, the clustering model being stored in computer storage media of a computer device;

comparing, by a processor of the computer device, each row contained in the tabular data to the clustering model;

ascertaining, by the processor of the computer device, one or more rows that are an exception by determining that the differences between the one or more rows and the group that is most similar to the row are greater than a threshold; and

presenting, by the processor of the computer device, the one or more rows that are an exception to a user including identifying at least one column of each of the one or more rows that caused the one or more rows to be ascertained as exceptions by comparing the value of each column in the one or more rows to a predicted value for the corresponding column of rows that are grouped into the most similar group and determining one or more particular columns that contain values that are most different from the corresponding predicted value such that the one or more particular columns are identified as having caused the row to be an exception.

11. The method of claim 10, wherein, for each row, the probabilistic clustering algorithm generates a data point in a multi-dimensional bell curve that has peaks and troughs, and wherein a peak represents one of the one or more groups such that as a data point moves away from a particular peak, the probability that the row represented by the data point is within the group represented by the particular peak diminishes.

12. The method of claim 11, wherein the group to which the identified at least one row is most similar is determined by finding the peak to which the data point representing the identified at least one row is closest.

13. The method of claim 10, further comprising:

periodically updating the clustering model;

determining if a different row is a new exception; and

presenting the new exception to a user.

14. The method of claim 10, wherein building a predictive clustering model on tabular data is performed on a machine independent of the location of the data.

15. A computer storage medium storing computer executable instructions which when executed by a processor of a computer system perform any one of the methods of claims 10, 11, 12, 13, or 14.