Automatic relationship detection for reporting on spreadsheet data.

(54) Title: AUTOMATIC RELATION DETECTION TO REPORT SPREADSHEET DATA. (54) Title: AUTOMATIC RELATIONSHIP DETECTION FOR REPORTING ON SPREADSHEET DATA.

(57) Summary

A system for creating a report in a spreadsheet is described, including: a central processing unit; a memory that encodes 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 articles; and a presentation module programmed to present the relationships between the tables.

(57) Abstract

A system for creating a report in a spreadsheet ineludes: 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.

<img file="MX340449B_D0001.tif" />

<img file="MX340449B_D0002.tif" />

PATENT TITLE NO. 340449 _SE_

SECMTAÜÍA Di ECtMOMÍA

Mexican Institute of Industrial Property

Owner (s): MICROSOFT TECHNOLOGY LICENSING, LLC

Address: One Microsoft Way, Redmond, Washington, 98052, USA

Name: AUTOMATIC RELATION DETECTION TO REPORT SPREADSHEET DATA.

Classification: lnt.CI.8: G06F17 / 21; G06F17 / 24; G06F3 / 14

Inventors); ALLAN FOLTING; STEPHEN VAN DE WALKER HANDY, DIEGO M. OPPENHE1MER; ANATOLY V. RECORD

Number:

MX / a / 2014/004167

Country:

US

REQUEST

International filing date:

October 2012

PRIORITY

Date: Number:

October 2011 13 / 252,450

Validity: Twenty years

Expiration Date: October 4, 2032

The reference patent is granted based on articles 1, 2nd section V, 6th section III, and 59 of the Industrial Property Data Act.

In accordance with article 23 of the Industrial Property Law, this patent is valid for "non-renewable years, counted from the filing date of the international application and will be subject to payment of the rate to keep the rights. «

Whoever signs the present title does so based on the provisions of article * 6 'provisions III and 7 ° bis 2 of the Industrial Property Law (Official Office of the Federation {0.04 =.) 06/27/1991, reform Do you give '08/38/1694, 2 * 10/1996, 12/26/1907, 05/17/1999, ¢ 06/01/2004, 06/16/2003' 01/25/2986, 05/06 / 2009.06 / 01i2010, 06/18/2010. 06/28/2010, 27/91/2012 and 04/09/2012); Articles f&gt;, 3 »section V, subsection a), subsection m) 4th and 12“ sections I and lll of the Regulation M of the Mexican Institute of Industrial Property (D. (1f. 14/12/1999, formed on 01 / 07 / 200¾ 15/07/2 * 34, 07/28/2004 and 09/07/2007); artícüfae · ^ ·;¹/ ^; fffftaction V subsection a), sub subsection II), 16 factions I and II and 30 of the Organic Statute of the Mexican Industrial Property Inability (DOF 12/27/1999, amended on 10/10/2002, 07/29/2004, 08/04/2ΘΟ4 and 18/89/2007); 1, 3 and 8 »tedium a) and the third to last part of the Aeuerde gire delegates faculties to you» «Deputy General Rectors, Coordinator, Divisional Directors, Head of the Regional Offices, Divisional Sub-directors, Departmental Coordinators and other subordinates of the Mexican Institute Industrial Property. (DOF 12/15/1999, amended on 02/04/2000, 07/29/2004, 08/04/2004 and 09/13/2007)

Issue Date: July 8, 2016

DIVISIONAL DEPUTY DIRECTOR OF PATENT FUND EXAMINATION, MECHANICAL, ELECTRICAL AREAS ^ r ^ £ fitfaBEUÍUISEÑOS INDUSTRIALES Y

<img file="MX340449B_D0003.tif" />

<img file="MX340449B_D0004.tif" />

Arenal No. 550, Floor 1.

Pueblo Santa María Tepepan, Xochimílco. CP 16020.

Mexico City 55) 53 34 07 00 www.iropi gob.mx

<img file="MX340449B_D0005.tif" />

ΜΥ / 9ΓΜ

<img file="MX340449B_D0006.tif" />

a / ^ / W7

IMPI

<img file="MX340449B_D0007.tif" />

AUTOMATIC RELATION DETECTION FOR DOES

SPREADSHEET DATA -—

BACKGROUND

Data summary tables in spreadsheets can be used to analyze large amounts of data. A data summary table provides an efficient way to present and summarize data that is supplied through a database program or that is in a data list in a spreadsheet. A user can select fields from the data to include within filter regions, row, column, or data regions from the data summary table and can select aggregation functions such as sum, variance, count, and standard deviation to be submitted for selected data fields. Data in a database that can be queried from within a spreadsheet program, or spreadsheet data that includes lists, can be analyzed in a summary data table.

With spreadsheets that include a significant amount of data, it can be difficult for the user to select the desired data to be included in such reports as summary data tables. For example, the sheer volume of data provided through one or more vastly unstructured spreadsheets can overwhelm the user, making it difficult to find the relevant pieces of data for a specific reporting scenario.

ΙΜΡΙ

MEXICAN INSTITUTE OF INDUSTRIAL PROPERTY

<img file="MX340449B_D0008.tif" />

BRIEF DESCRIPTION OF THE INVENTION

In one aspect, a system for creating a report in a spreadsheet includes: a central processing unit; and a memory that encodes 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 articles; and a presentation module programmed to present the relationships between the tables.

In another aspect, a method of identifying relationships between data items in a spreadsheet, the method comprises: identifying, through a computing device, tables associated with the spreadsheet; allow a user to add one of the tables to a data summary table in the spreadsheet; parse the aggregated table to determine a relationship between the aggregated table and other tables in the data summary table; And when the aggregated table is not related to the other tables in the data summary table, it will automatically identify a relationship between the aggregated table and the other tables in the data summary table.

In yet another aspect, a method of identifying relationships between dimensions in a data summary table on a worksheet.

<img file="MX340449B_D0009.tif" />

calculation, the method comprises: allowing a user to add a table to the data summary table in the spreadsheet, the table including a defined data region in the spreadsheet; parse columns associated with the table to determine a relationship between dimensions and other tables in the data summary table; when the aggregated table is not related to other tables in the data summary table: notify a user of the need to define the relationship when the aggregate table is added to the data summary table; and use heuristics to

Automatically identify the relationship between the table and the other tables in the data summary table; present the relationship between the aggregated table and the other tables in the data summary table.

This Brief Description is provided to introduce a selection of concepts, in a simplified form which is further described below in the Detailed Description. This Brief Description is not intended to identify key characteristics or essential characteristics of the claimed topic, nor is it intended to be used in any way to limit the scope of the subject matter claimed.

BRIEF DESCRIPTION OF THE DRAWINGS

Figure 1 shows an illustrative system to generate a report in a spreadsheet program.

Figure 2 shows a pivot table or pivot table

ΙΜΡΪ

<img file="MX340449B_D0010.tif" />

illustrative in a spreadsheet.

Figure 3 shows Illustrative data lines used to generate a pivot table in a spreadsheet.

Figure 4 shows Illustrative modules of a client computing device of the system of Figure 1.

Figure 5 shows an Illustrative method to Identify a relationship between two or more tables in a report.

Figure 6 shows an illustrative wizard to manually identify a relationship between two or more tables in a report.

Figure 7 shows an interface to identify a relationship between data from an external data warehouse and one or more tables in a report.

Figure 8 shows Illustrative components of computing devices shown in Figure 1.

DETAILED DESCRIPTION

The present application is directed to systems and methods for automatically detecting and suggesting relationships between data regions in a spreadsheet application. In the examples described here, relationships between data lines are identified and presented to the user to allow the user to more easily combine data into an individual report.

Figure 1 shows an illustrative system 100 that supports the generation of one or more reports based on data.

<img file="MX340449B_D0011.tif" />

THE INDUSTRIAL LI PROPERTY, I calculate. The systems provided in a spreadsheet application include a client computer 102 and a pilot database - (server 106. Client computer 102 connects to server computer 106 through a network, typically a corporate intranet or the Internet. More or less client computers, server computers, or other computer servers can be used.

As further described below, client computer 102 can connect to server computer 106 to obtain data, such as a spreadsheet file. Client computer 102 can run a native spreadsheet application on client computer 102 to manipulate the spreadsheet file. In another example, client computer 102 may use one or more applications hosted on server 106 to access and manipulate the spreadsheet file. Other configurations are possible.

Referring now to Figure 2, an illustrative program 200 is shown. In this example, program 200 is a Microsoft EXCEL spreadsheet software program that runs on a computer system, such as computer system 100 described above. Program 200 Includes a spreadsheet with an illustrative list of data.

A user can create one or more reports based on the data within the spreadsheet. In some examples described here, the report may be a summary table of data. Without

IMPI g MEXICAN INSTITUTE

INDUSTRIAL PROPERTY However, in other examples, the report may simply be another “regular” table. For example, the systems and methods described here can be used to suggest certain columns for a user-created table based on identified associations with other tables in the spreadsheet.

For example, an illustrative user interface 302 of program 200 is shown. User interface 302 includes an initial data summary table 320 (data summary table 320 is blank in Figure 3). The data summary table

320 It can be created from data from various sources. In an example shown as seen in Figure 3, the data summary table 320 can be created from data provided within the spreadsheet file or from one or more external data stores.

The user interface 302 of program 200 also includes an illustrative task box 450 that can be used to create and modify data summary table 320. For example, task box 450 includes a list of data fields from of data associated with the spreadsheet file. User can select and deselect fields from task box 450 to create data summary table 320.

Task box 450 generally includes a field box 452 and a layout box 454. Field box 452 includes a list of the data fields available in the spreadsheet. In these examples, the data fields include tables,

<img file="MX340449B_D0012.tif" />

<img file="MX340449B_D0013.tif" />

INS »E THE PROPERTY

INDUSTRIAL ___r each table having one or more columns that can be added to the report. When a data field in the list is added to layout box 454 as described below, the check box associated with the data field is verified.

Layout box 454 includes a plurality of zones representing aspects of data summary table 320 that is created using task box 450. For example, layout box 454 includes a row area, a column area, a value zone, and a filter zone. The row area defines the row labels for the resulting data summary table 320. The column area defines the column labels for the 320 data summary table. The value area identifies the data that is summarized (for example, aggregation (sum, count, average, standard deviation, etc.), variance, etc.) in the data summary table 320. The filter area allows selection of Filtering that is applied to all other fields in the other zones.

One or more of the data fields in field box 452 are added to one or more of the areas of layout box 454 to create and modify data summary table 320. In the example shown, the user can click, drag, and drop a data field from the list in field box 452 to one of the areas of layout box 454 to add a field to data summary table 320.

Referring now to Figure 3, the data used to create a report, such as the 320 data summary table,

-I i

JLV1 Γ I.

MEXICAN INSTITUTE ff- ¡~ ^

OWNERSHIP V, ... '')

INDUSTRIAL can come from multiple sources. Data sources can be internal to a spreadsheet, as well as external.

For example, a spreadsheet 500 shown in Figure 3 includes two internal data regions 502 and 504. Data regions are defined groups of data that have at least two dimensions, such as rows and columns. For example, data region 502 includes columns Employee ID, Name, and rows 1-3. Similarly, data rule 504 includes columns Employee ID, Amount of Sales, and rows 1-7.

In these examples, a plurality of data regions can be included at multiple locations within a spreadsheet, such as on the same or different sheets within the spreadsheet. As can be seen later, the data can also be incorporated from external sources.

In this example, program 200 is programmed to analyze data regions 502 and 504 and to automatically determine relationships between regions. For example, the program determines that the employee ID data in both rulers is compatible using various heuristics described below.

In these examples, a relationship is a link created between two data regions (sometimes referred to as "tables") based on matching data values. These relationships can include a one-to-one relationship and / or a one-to-many relationship. For example, an individual value in a table can

<img file="MX340449B_D0014.tif" />

MEXICAN INSTITUTE OF PROPERTY

INDUSTRIAL - - correspond to multiple values ​​in a table of consu Ita or related. Relationships allow the program to search and summarize data in both tables, simultaneously.

After this detection, the program 200 creates a relationship between the two data regions 502, 504 and allows the user to easily create a report by simply selecting the fields in a user interface. For example, the data fields associated with the data regions 502, 504 can be grouped into the user interface so that the relationships between the data regions can be easily identified by the user. Additional details on illustrative user interfaces for this purpose are found in the US Patent Application Series.

No. 13 / 252,412, Proxy Number 333789.01 (14917.1922US01), entitled “Automatic Determination of the Scope of Entities of

Data, presented on the date hereof, the content of which is incorporated herein for reference.

An example of a report that can be generated after the relationship between data regions 502, 504 is detected is a 510 report in spreadsheet 500. In this example, the report

510 it is a data summary table, such as a pivot table or pivot table, generated using data from data regions 502, 504. To create report 510, the user selects desired data fields from data regions 502, 504 and external data storage 506 in a manner similar to that described in Figure 2.

<img file="MX340449B_D0015.tif" />

i_ft IMPI θ 'NSTITUTO MtXlCANO

OF THE INDUSTRIAL MONEDAD

In addition to the data regions 502, 504 provided in spreadsheet 500, data can also be incorporated from external data stores to spreadsheet 500. For example, an external data store is shown in Figure 3. 506. Examples of such external data stores include OLAP and non-OLAP data sources. The data from this external data storage 506 is entered into spreadsheet 500. Based on the information on this external data storage 506, relationships between the data in the external data storage 506 and the data regions 502, 504 within the spreadsheet 500 can be identified, as further described below.

In some examples, the number of available data fields provided in the data regions and external data stores from which the user can select becomes bulky. In such scenarios, it may be difficult for the user to review the available data fields and select the desired fields for inclusion in a report.

Referring now to Figure 4, logic modules 20 of the client computer 102 are shown. These modules include a data source module 522, a relationship module 524, and an interface module 526. In these examples, the modules 522, 524,

526 they are run in conjunction with the spreadsheet application that allows the user to manipulate the data within spreadsheet files. Modules 522, 524, 526 of the client computer 10

IMPI

<img file="MX340449B_D0016.tif" />

They work to identify calculation so that the relevant fields are reported.

relationships between the data in the user sheet can more easily identify user you want to select when you create a

Data source module 522 Identifies all data sources associated with a particular file, such as a spreadsheet file. This can include internal data lines defined on one or more sheets in the spreadsheet file. This may also include data from external data stores. Such external data storage may include data that is referenced from within the spreadsheet, such as external data that has been embedded in the spreadsheet file. Data source module 522 provides all data sources to relationship module 524 for analysis.

Relationship module 524 determines which, if any, of the data sources within a spreadsheet are related. For example, if the user is creating a report, such as a pivot table, the relationship module 524 determines uses of various heuristics to analyze all of the data lines and external data stores that have been Incorporated into the spreadsheet to determine any relationship between them.

For example, the program uses heuristics to examine the data, either data in the grid or grid of the spreadsheet or data from any external data source that the user has imported into the spreadsheet application, and based on that

<img file="MX340449B_D0017.tif" />

suggests data entities in different data regions. The spreadsheet application then stores information in these relationships and other aspects of the spreadsheet will now automatically level these relationships, allowing the user to create a report that includes data from multiple different data regions.

Some examples of heuristics that can be used to identify associations include one or more of the following:

• column name match (for example, columns with similar annotations);

• value match, such as a column with unique values ​​in the primary key table and a column with repeated values ​​in the foreign key table but where the distinct group of values ​​is output in the primary key column;

• data type match (for example, date or currency fields);

• fuzzy value match (for example, “CA” matching “California”); and • using map tables to produce identifiers needed in one table to create a relationship to another table, which already has a column with those identifiers.

For example, referring to Figure 5, in a scenario, the relationship between two or more tables is created when the

ΙΜΡΙ

MEXICAN INSTITUTE 'Λ - * »κτί ^) (»

FROM PROPERTY W industrial user adds data fields from unrelated tables to a report, such as a data summary table.

In this example, a method 530 begins when the user adds a field to the report in step 532. Then, in step 534, a determination is made as to whether or not a relationship is necessary because, for example, the field does not it is related to the fields already included in the table. If a relationship is not required (that is, the field is already related in some way), the field is added and the method ends.

However, if a relationship is required, control instead passes to step 536, and the user is notified that a relationship may be missing. An illustrative method for such notification provides an information bar within the user interface to notify the user that the relationship is missing (for example, "Relations between tables need to be created").

Then, in step 538, a determination is made as to whether or not the user wants to create a relationship for the newly added field. If not, the method ends, and the user may be prompted again to need to provide a relationship the next time the user modifies the report. If so, control rather goes to step 539. In step 539, the relationship between the newly added field and the fields already included in the report is determined. In one example, the relationship can be automatically determined by using one or more of the heuristics described above. In another example, the relationship could be

IMPI

<img file="MX340449B_D0018.tif" />

manually defined.

For example, referring now to FTg u ra 6, an illustrative relationship wizard 540 is illustrated. In this 540 wizard, the user can m anually define the relationship between the fields in different tables. In this example, the Employee ID field is related for the Sales Table and Employee Table tables.

Relationships defined by relationship module 524, whether created automatically by the program or manually defined by the user, can be persisted through report creation. In some examples, relationships can also be persisted through the creation of other reports within the spreadsheet so that the program and / or user does not have to redefine the relationships each time the data fields in the spreadsheet calculation be used.

Referring again to Figure 4, interface module 526 presents the available data fields from the data regions and external data stores in the spreadsheet to the user for inclusion in the report. In this example, interface module 526 indicates the relationship between data sources with the user at the interface. These relationships can manifest themselves in various ways. In one example, when the user selects a table, the interface can present only that table and any other tables that are related to the selected table. In another example, tables can be grouped to identify relationships.

<img file="MX340449B_D0019.tif" />

As noted above, in addition to defining relationships between table data fields within the spreadsheet, the program can define relationships with data from external sources, such as external data stores. In this scenario, the relationship module 524 is programmed to analyze the data from an external data store and provides any relationship between that data and any other data contained in the spreadsheet.

For example, referring to Figure 7, an Illustrative data connection wizard 550 is shown. In this example, the data connection wizard 550 allows the user to select tables that are directly related to the currently selected table (s) in the wizard. In this example, the external data warehouse "AdventureWorksDW2008R2" is selected in wizard 550, and the user can select specific tables within the spreadsheet to relate to the database (for example, DlmEmployee is selected). This selection can be made manually, by the user in the wizard 550, or it can be done automatically by the program using the heuristics described above. In other examples, if the external data source has relationships already defined between your tables, those that can be Imported along with the data from the external data source and used in the spreadsheet application along with other relationships created there.

For example, the program may have access to information from

<img file="MX340449B_D0020.tif" />

schema on external data storage that allows the program to determine relationships between tables. This schema (for example, in the form of an XML file) can relate fields within the imported external data store, as well as other data in other external data stores. The program enhances this information to automatically relate data fields to the user.

Referring to Figure 8, illustrative components of client computer 102 and server computer 106 are shown. In illustrative embodiments, client computer 102 and server computer 106 are computing devices. Client computer 102 and server computer 106 can include input / output devices, a central processing unit ("CPU"), a data storage device, and a network device. Client computer 102 may also be a mobile computing device, such as a laptop. Tablet, convertible device, or other portable device such as a smartphone or cell phone. Server computer 106 may also be incorporated as one or more server computers in a server network. In such a scenario, the network can provide a cloud computing platform where one or more applications and data is hosted for clients connecting to the cloud computing platform. The discussion that follows regarding server computer 106 also applies to the components of

<img file="MX340449B_D0021.tif" />

iNSrmm&gt; MÍjtrCANu DE LA FKOfttJMO

INDUSTRIAL

<img file="MX340449B_D0022.tif" />

the client computer 102.

In a basic configuration, server computer 106 typically includes at least one processing unit 602 and system memory 604. Depending on the exact configuration and type of computing device, system memory 604 can be volatile (such as RAM). , non-volatile (such as ROM, flash memory, etc.) or some combination of the two. System memory 604 typically includes a suitable operating system 606 to control the operation of a server, such as Microsoft's Microsoft SharePoint® Server 2010 collaboration server.

Corporation of Redmond, Washington. System memory 604 can also include one or more software applications 608 and can include data programs.

Server computer 106 may have additional aspects or functionality. For example, the server computer

106 It can also include computer readable media. Computer readable media can include both computer readable storage media and communication media.

Computer readable storage media are physical media, such as data storage devices (removable and / or non-removable) including magnetic discs, optical discs, or tape. Such additional storage is illustrated in Figure 6 through removable storage 610 and non-removable storage 612. The storage media

<img file="MX340449B_D0023.tif" />

MEXICAN INSTITUTE Dt LA MONEDAD

INDySTpiAL ___ computer readable may include volatile and nonvolatile, removable and nonremovable media implemented in any method or technology for information storage, such as computer readable instructions, data structures, program modules, or other data. Computer readable storage media may include, but are not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CDROM, digital versatile discs (DVDs) or other optical storage, magnetic cassettes, magnetic tape, storage on magnetic disk or other magnetic storage devices, or any other means that can be used to store the desired information and that can be accessed through the server computer 106. Any computer readable storage media can be part of the server computer 106. The server computer 106 can also have 614 input devices such as a keyboard, mouse, pen, voice input device, touch input device, etc. .

616 output devices such as a presentation, speakers, printer, etc. can also be included.

In accordance with the embodiments of the present description, the input devices 614 may comprise any motion detection device capable of detecting the movement or gesture of a user. For example, input devices 614 may comprise a Kinect® motion capture device, from Microsoft Corporation, comprising a plurality of cameras and

<img file="MX340449B_D0024.tif" />

a plurality of microphones.

Server computer 106 may also contain communication connections 618 that allow the device to communicate with other computing devices 620, such as over a network in a distributed computing environment, for example, an intranet or the Internet. Communication connections 618 are an example of communication media. Communication media typically can be modeled by computer-readable instructions, data structures, program modules, or other data into a modulated data signal, such as a carrier wave or other transport mechanism, and includes any means of supplying information. The term "modulated data signal" means a signal that has one or more of its characteristics set or changed in such a way that they encode information in the signal. By way of example, and not limitation, the communication media includes wired media such as a wired network or direct cable connection, and wireless media such as acoustic, RF, infrared and other wireless media.

The modalities of the present description can be practiced through a system on a chip (SOC), where each or many of the components illustrated in Figure 6 can be integrated into a single integrated circuit. Said SOC device may include one or more processing units, graphic units, communication units, virtualization units of

IMPI

MEXICAN INSTITUTE OF IA 'RIJPIEDaD inbustriai

<img file="MX340449B_D0025.tif" />

system and various application functionalities all of these are integrated (or "burned") into the chip substrate as an individual integrated circuit. When operating through a SOC, the functionality, described above, with respect to the present disclosure can be operated through specific application logic integrated with other components of computing devices 102 and 106 on the individual integrated circuit (chip) .

The various embodiments described above are provided by way of illustration only and should not be construed as limiting. Various modifications and changes can be made to the modalities described above without departing from the true spirit and scope of the description.

<img file="MX340449B_D0026.tif" />