Data analysis expressions

• US 9,275,031 B2
• Filed: 10/09/2009
• Issued: 03/01/2016
• Est. Priority Date: 10/09/2009
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Inventors: Dickerman, Howard J., Dumitru, Marius, Mirchandani, Akshai, Wang, Yutong (Jeffrey), Netz, Amir, Sanders, Paul J.
• Primary Examiner(s): Baderman, Scott
• Assistant Examiner(s): AMIN, MUSTAFA A
• Application Number:
  US12/576,254
• Publication Number:
  US 20110087954A1
• Time in Patent Office: 2,335 Days
• Field of Search: 715/219
• US Class Current: 1/1
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

Methods and systems are disclosed for receiving and processing data analysis expressions. A particular method includes receiving a data analysis expression at a pivot table of a spreadsheet. The data analysis expression is executed for a particular cell of the pivot table by determining a context associated with the particular cell, calculating a value of the data analysis expression based on the context, and outputting the calculated value at the particular cell.

Claims

1. A computer-readable hardware storage medium storing instructions, that when executed by a processor, cause the processor to:

receive input comprising a column definition of a particular column of a first table, wherein the input includes a data analysis expression based on at least one column of the first table other than the particular column and based on at least one column of a second table;

determine a relationship between the first table and the second table based on the data analysis expression; and

populate the particular column by executing the data analysis expression, wherein executing the data analysis expression for a particular row of the first table comprises;

receiving a query associated with a user input;

generating a command to calculate a value of the data analysis expression based on the query;

calculating the value of the data analysis expression based on first data in the particular row of the first table and second data retrieved from the second table based on a filter context associated with the particular row; and

outputting the calculated value at a cell, wherein the cell is a member of the particular column and the particular row.

2. The computer-readable hardware storage medium of claim 1, further storing instructions, that when executed by the processor, cause the processor to:

receive a selection of a subset of rows of the first table; and

automatically re-execute the data analysis expression for the selected subset of rows.

3. The computer-readable hardware storage medium of claim 1, wherein the data analysis expression includes a user-defined formula.

4. The computer-readable hardware storage medium of claim 1, wherein the data analysis expression includes a formula to be aggregated over rows of at least one data table.

5. The computer-readable hardware storage medium of claim 4, wherein the data analysis expression includes at least one aggregation selected from the group consisting of:

a sum pivot table aggregation, an average pivot table aggregation, a minimum pivot table aggregation, a maximum pivot table aggregation, and a count pivot table aggregation, wherein the at least one aggregation may be performed for a plurality of expressions other than the data analysis expression.

6. The computer-readable hardware storage medium of claim 1, wherein the relationship includes a related column that is an index column of the first table, an index column of the second table, or any combination thereof.

7. A computer system comprising:

one or more computing devices;

memory storing one or more components, the one or more components comprising;

a data interface component configured to receive data and to store the received data at a data table in a multi-dimensional cube data structure at the memory;

a pivot table component configured to generate a pivot table based on the data table;

an analysis component configured to;

receive a data analysis expression;

receive a query associated with the multi-dimensional cube data structure; and

execute the data analysis expression based on receiving the query for a first cell and a second cell of the pivot table, wherein a first row context is associated with the first cell and a second row context is associated with the second cell, and wherein executing the data analysis expression for the first cell of the pivot table comprises;

determining a first row context associated with the first cell;

retrieving data associated with one or more rows of the pivot table corresponding to the first row context from the multi-dimensional cube data structure;

calculating a value of the data analysis expression based on the retrieved data; and

outputting the calculated value at the first cell.

8. The computer system of claim 7, wherein the memory is a random access memory (RAM).

9. The computer system of claim 7, wherein the multi-dimensional cube data structure is an online analytical processing (“

OLAP”

) cube data structure.

10. The computer system of claim 7, wherein the data analysis expression includes a user-defined formula.

11. The computer system of claim 7, further comprising automatically re-executing the data analysis expression in response to a pivot operation of the pivot table.

12. The computer system of claim 7, wherein the data analysis expression includes a reference to a column of at least one data table, wherein the reference to the column resolves to the column when the reference is used in a column calculation, and wherein the reference to the column resolves to a value stored at a particular row of the column when the reference is used in a scalar calculation.

13. The computer system of claim 7, wherein determining the context associated with the first cell includes filtering based on the first cell.

14. A method executing on one or more processors, the method causing the one or more processors to:

receive an input comprising a column definition of a particular column of a first table, wherein the input includes a data analysis expression based on at least one column of a second table from a first data structure and based on at least one column of a third table from a second data structure;

determine a relationship between the second table and the third table; and

populate the particular column of the first table by executing the data analysis expression, wherein executing the data analysis expression for a particular row of the first table comprises;

receiving a query associated with a user input;

calculating a value of the data analysis expression based on the query, wherein calculating the value based on the query is based on first data retrieved from the second table and second data retrieved from the third table based on a filter context associated with the particular row; and

outputting the calculated value at a cell, wherein the cell is a member of the particular column and the particular row of the first table.

15. The method of claim 14 wherein the first data structure is an online analytical processing (“

OLAP”

) cube.

16. The method of claim 14, wherein the method further causes the one or more processors to:

receive a selection of a subset of the particular row of the first table; and

automatically re-execute the data analysis expression for the subset of the particular row of the first table.

17. The method of claim 14, wherein the data analysis expression includes a formula.

18. The method of claim 17, wherein the data analysis expression includes a formula associated with at least one aggregation, wherein the at least one aggregation is selected from the group consisting of:

a sum pivot table aggregation, an average pivot table aggregation, a minimum pivot table aggregation, a maximum pivot table aggregation, and a count pivot table aggregation.

19. The method of claim 14, wherein the data analysis expression includes at least one time-based function.

20. The method of claim 14, wherein the data analysis expression includes a related table function, a relatedtable table function, a filter table function, a distinct table function, a values table function, an all table function, an allexcept table function, an allnoblankrow table function, or any combination thereof.

21. A system comprising:

one or more processors;

one or more memory components storing computer-executable instructions embodied thereon that, when executed by one or more processors, enable;

receiving a first input comprising a column definition of a particular column of a first table, wherein the first input includes a data analysis expression based on at least one column of a second table from a first data structure and based on at least one column of a third table from a second data structure;

identifying a relationship between the second table and the third table; and

populating the particular column of the first table by executing the data analysis expression, wherein executing the data analysis expression for a particular row of the first table comprises;

receiving a query associated with a second input, wherein the second input is a user input;

calculating a value of the data analysis expression based on first data retrieved from the second table and second data retrieved from the third table based on a filter context associated with the particular row, in response to the query; and

outputting the calculated value at a cell, wherein the cell is a member of the particular column and the particular row of the first table.

22. The system of claim 21 wherein the first data structure is an online analytical processing (“

OLAP”

) cube.

23. The system of claim 21, wherein the system further comprises one or more memory components storing computer-executable instructions embodied thereon that, when executed by one or more processors, enable:

receiving a selection of a subset of the particular row of the first table; and

automatically re-executing the data analysis expression for the subset of the particular row of the first table.

24. The system of claim 21, wherein the data analysis expression includes a formula.

25. The system of claim 21, wherein the data analysis expression includes a formula associated with at least one aggregation, wherein the at least one aggregation is selected from the group consisting of:

a sum pivot table aggregation, an average pivot table aggregation, a minimum pivot table aggregation, a maximum pivot table aggregation, and a count pivot table aggregation.

26. The system of claim 21, wherein the data analysis expression includes at least one time-based function.

27. The system of claim 21, wherein the data analysis expression includes a related table function, a relatedtable table function, a filter table function, a distinct table function, a values table function, an all table function, an allexcept table function, an allnoblankrow table function, or any combination thereof.

28. A computer-readable hardware storage medium comprising instructions that, when executed by one or more processors, cause the one or more processors to:

receive a first input comprising a column definition of a particular column of a first table, wherein the first input includes a data analysis expression based on at least one column of a second table from a first data structure and based on at least one column of a third table from a second data structure;

determine a relationship between the second table and the third table; and

populate the particular column of the first table by executing the data analysis expression, wherein executing the data analysis expression for a particular row of the first table comprises;

receiving a query associated with a second input, wherein the second input is a user input;

based on the query, calculating a value of the data analysis expression that is based on first data retrieved from the second table and second data retrieved from the third table, based on a filter context associated with the particular row; and

outputting the calculated value at a cell, wherein the cell is a member of the particular column and the particular row of the first table.

29. The computer-readable hardware storage medium of claim 28 wherein the first data structure is an online analytical processing (“

OLAP”

) cube.

30. The computer-readable hardware storage medium of claim 28, further comprising instructions, that when executed by one or more processors, cause the one or more processors to:

receive a selection of a subset of the particular row of the first table; and

automatically re-execute the data analysis expression for the subset of the particular row of the first table.

31. The computer-readable hardware storage medium of claim 28, wherein the data analysis expression includes a formula.

32. The computer-readable hardware storage medium of claim 28, wherein the data analysis expression includes a formula associated with at least one aggregation, wherein the at least one aggregation is selected from the group consisting of:

a sum pivot table aggregation, an average pivot table aggregation, a minimum pivot table aggregation, a maximum pivot table aggregation, and a count pivot table aggregation.

33. The computer-readable hardware storage medium of claim 28, wherein the data analysis expression includes at least one time-based function.

34. The computer-readable hardware storage medium of claim 28, wherein the data analysis expression includes a related table function, a relatedtable table function, a filter table function, a distinct table function, a values table function, an all table function, an allexcept table function, an allnoblankrow table function, or any combination thereof.