AUTOMATIC RELATIONSHIP DETECTION FOR SPREADSHEET DATA ITEMS

• US 20150193423A1
• Filed: 03/23/2015
• Published: 07/09/2015
• Est. Priority Date: 10/04/2011
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Inventors: Folting, Allan, Handy, Stephen Van de Walker, Oppenheimer, Diego M., Grabar, Anatoly V.
• Granted Patent: US 9,852,121 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/00 Information retrieval; Database structures therefor; File system structures therefor
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 9/44 Arrangements for executing specific programs
• CPC Class Codes: G06N 5/02 Knowledge representation; Symbolic representation
• CPC Class Codes: G06Q 10/10 Office automation; Time management

Abstract

A system for automatically detecting relationships in spreadsheet data is disclosed. The system may be configured to analyze data items in the spreadsheet to determine whether two or more data items are unrelated. If the two or more data items are unrelated, then the system may notify a user of a need to define the relationship, and automatically defines the relationship for the two or more data items, or suggests the relationship to the user.

Claims

1. A system for creating a report in a spreadsheet, the system comprising:

a central processing unit; and

a memory encoding instructions that, when executed by the central processing unit, cause the central processing unit to create;

a data source module programmed to identify tables associated with the spreadsheet; and

a relationship module programmed to analyze the tables and identify related tables that have an undefined relationship, the relationship module defining a relationship for the related tables having the undefined relationship by using heuristics to automatically define the undefined relationship.

2. The system of claim 1, wherein the relationship module identifies the relationship when a table is added to the report.

3. The system of claim 2, wherein the system provides a notification of a need to define the relationship when the table is added to the report.

4. The system of claim 3, wherein the system allows manual identification of the relationship between the table and another table in the report.

5. The system of claim 1, wherein the report is a data summary table, and the tables each include one or more dimensions to be added to the data summary table.

6. The system of claim 1, wherein the relationship is persisted throughout use of the tables in the spreadsheet.

7. The system of claim 1, wherein one of the tables is a data region defined in the spreadsheet.

8. The system of claim 7, wherein the data region includes a plurality of fields.

9. The system of claim 1, wherein one or more of the tables is stored on an external data store.

10. The system of claim 1, wherein the heuristics include one or more of column name matching, value matching, data type matching, fuzzy value matching and using mapping tables.

11. A method for identifying a relationship between tables in a spreadsheet, comprising:

identifying, by a computing device, tables associated with the spreadsheet;

receiving a first table to add to a data summary table for the spreadsheet, wherein the relationship between the first table and a second table is undefined;

determining whether the first table and the second table in the data summary table are related; and

when the first table is related to the second table in the data summary table, defining the relationship between the first table and the second table in the data summary table, wherein defining the relationship between the first table and the second table in the data summary table includes using heuristics to automatically identify the relationship.

12. The method of claim 11, further comprising displaying the relationship between the first table and the second table in the data summary table.

13. The method of claim 11, further comprising providing a notification to define the relationship when the first table is added to the data summary table.

14. The method of claim 12, further comprising manually defining the relationship between the first table and the second table in the data summary table.

15. The method of claim 11, wherein each of the tables includes one or more columns to be added to the data summary table.

16. The method of claim 11, further comprising persisting the relationship throughout use of the tables in the spreadsheet.

17. The method of claim 11, wherein the first table and the second table are a data region defined in the spreadsheet.

18. The method of claim 17, wherein the data region includes a plurality of fields.

19. The method of claim 11, wherein the heuristics include one or more of column name matching, value matching, data type matching, fuzzy value matching and using mapping tables.

20. A computing device for identifying a relationship between tables in a spreadsheet, comprising:

a central processing unit; and

a memory encoding instructions that, when executed by the central processing unit, cause the computing device to;

identify tables associated with the spreadsheet;

receive a first table to add to a data summary table for the spreadsheet;

determining whether the relationship between the first table and a second table in the data summary table is undefined; and

if the relationship between the first table and the second table is undefined;

providing a notification to manually define the relationship; and

if the relationship is not manually defined, using heuristics to automatically define the relationship between the first table and the second table in the data summary table, wherein the heuristics include one or more of column name matching, value matching, data type matching, fuzzy value matching and using mapping tables.