Automatic Relationship Detection for Reporting on Spreadsheet Data

• US 20130086460A1
• Filed: 10/04/2011
• Published: 04/04/2013
• Est. Priority Date: 10/04/2011
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Folting, Allan, Handy, Stephen Van de Walker, Oppenheimer, Diego M., Grabar, Anatoly V.
• Granted Patent: US 8,990,675 B2
• Time in Patent Office: Days
• US Class Current: 715/212
• 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 creating a report in a spreadsheet includes: 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; a relationship module programmed to analyze the tables and identify relationships between the items; and a display module programmed to display the relationships between the tables.

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;

a relationship module programmed to analyze the tables and identify relationships between the items; and

a display module programmed to display the relationships between the tables.

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

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

4. The system of claim 3, wherein the system allows the user to manually identify a relationship between the table and another table item 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 module is programmed to use heuristics to identify the relationships.

7. The system of claim 6, wherein the relationships are persisted throughout use of the tables in the spreadsheet.

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

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

10. The system of claim 1, wherein one of the tables originates from an external data store.

11. A method for identifying relationships between data items in a spreadsheet, the method comprising:

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

allowing a user to add one of the tables to a data summary table in the spreadsheet;

analyzing the added table to determine a relationship between the added table and other tables in the data summary table; and

when the added table is unrelated to the other tables in the data summary table, automatically identifying a relationship between the added table and the other tables in the data summary table.

12. The method of claim 11, further comprising displaying the relationship between the added table and the other tables in the data summary table.

13. The method of claim 11, further comprising notifying a user of a need to define the relationship when the added table is added to the data summary table.

14. The method of claim 13, further comprising allowing the user to manually identify the relationship between the added table and the other tables 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 using heuristics to automatically identify the relationship.

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

18. The method of claim 11, wherein the table is a data region defined in the spreadsheet.

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

20. A method for identifying relationships between dimensions in a data summary table of a spreadsheet, the method comprising:

allowing a user to add a table to the data summary table in the spreadsheet, the table including a data region defined in the spreadsheet;

analyzing columns associated with the table to determine a relationship between the dimensions and other tables in the data summary table;

when the added table is unrelated to other tables in the data summary table;

notifying a user of a need to define the relationship when the added table is added to the data summary table; and

using heuristics to automatically identify the relationship between the table and the other tables in the data summary table; and

displaying the relationship between the added table and the other tables in the data summary table.