Data analysis expressions

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.

BACKGROUND

Multi-dimensional data analysis (e.g., viewing and analyzing data from multiple perspectives) has become increasingly popular with businesses. However, current multi-dimensional data analysis applications typically require users to be proficient in complex semantic languages such as Multi-Dimensional Expression Language (MDX), because MDX expressions are used to organize and analyze the data. Thus, it may be difficult for business employees untrained in MDX to perform multi-dimensional data analysis. Furthermore, although business employees may be familiar with data analysis formulae provided by commonly available spreadsheet applications, such formulae are usually not as powerful as multi-dimensional data analysis programs. For example, such formulae may only enable a one-dimensional (e.g., sequential) aggregation of data. Therefore, a business that makes decisions based on multiple factors may be faced with a choice between two expensive alternatives: train existing employees in complex languages such as MDX or hire analysts that specialize in multi-dimensional data analysis.

SUMMARY

Systems and methods of receiving and processing data analysis expressions (DAXs) are disclosed. A DAX may be defined in an expression language similar to spreadsheet formulae and may operate on a spreadsheet table to perform multi-dimensional data analysis and data analysis with respect to relational data models. Thus, DAXs may empower people familiar with existing spreadsheet applications to perform multi-dimensional data analysis and data analysis with respect to relational data models (e.g., within existing spreadsheet applications). Unlike conventional spreadsheet formulae, a DAX beneficially is independent of particular cell ranges of the spreadsheet.

For example, a DAX may be received and executed at a pivot table of a spreadsheet application. Executing the DAX for a particular cell of the pivot table may include determining a context for the particular cell, calculating the value of the DAX for the particular cell, and outputting the calculated value of the DAX at the particular cell.

DAXs may support multi-table execution. For example, a DAX may refer to a first data table and a second data table, and executing the DAX may include traversing a relationship between the first data table and the second data table (e.g., following a relationship that may exist between a column in a first table and a column in a second table). DAXs may also support dynamic re-execution. For example, a DAX may be automatically re-executed with respect to a set of rows of a data table in response to a user modification to data stored in the set of rows.

DETAILED DESCRIPTION

Data analysis expressions (DAXs) may enable multi-dimensional data analysis at conventional data processing application, such as a spreadsheet application. For example, a DAX may be received as a column definition for a column of a spreadsheet table or as a measure at a pivot table. The DAX may be executed to populate the column, where the value in each cell is calculated based on a row context for that cell. Alternately, cells of the pivot table may be populated by executing the DAX, where the value in each cell of the pivot table is calculated based on a context (e.g., a filter context) associated with that cell.

In a particular embodiment, a computer-implemented method is disclosed that includes receiving a data analysis expression at a pivot table of a spreadsheet. The computer implemented method also includes executing the data analysis expression with respect to at least one data table of the spreadsheet. Executing the data analysis expression for a particular cell of the pivot table may be performed 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 cell.

In another particular embodiment, a computer-readable medium is disclosed that includes instructions, that when executed by a processor, cause the processor to receive input including a column definition of a particular column of a first spreadsheet table. The input includes a data analysis expression based on at least one column of the spreadsheet table and based on at least one column of a second spreadsheet table. The computer-readable medium includes instructions, that when executed by the processor, cause the processor to determine a relationship between the first spreadsheet table and the second spreadsheet table and to populate the particular column by executing the data analysis expression. Executing the data analysis expression for a particular row of the first spreadsheet table includes calculating a value of the data analysis expression based on first data in the particular row of the first spreadsheet table and second data retrieved from the second spreadsheet table based on a row context associated with the particular row. Executing the data analysis expression also includes outputting the calculated value at cell that is a member of the particular column and the particular row.

In another particular embodiment, a system is disclosed. The system includes a memory and a data interface configured to receive data, to create one or more data tables based on the received data, and to store the data tables in a column-based in-memory store (e.g., a structure that maps to an online analytical processing (OLAP) cube structure). The system also includes a pivot table module configured to generate a pivot table based on the data table(s). The system further includes an analysis module configured to receive a data analysis expression and execute the data analysis expression with respect to at least one column of the data table(s). Executing the data analysis expression for a particular cell of the pivot table includes determining a filter context associated with the particular cell and retrieving data associated with one or more rows of the data table corresponding to the filter context of the particular cell. Executing the data analysis expression also includes calculating a value of the data analysis expression based on the retrieved data and outputting the calculated value at the cell.

FIG. 1is a block diagram to illustrate a particular embodiment of a computer system100to receive and process data analysis expressions (DAXs). The computer system100includes a data interface110and a memory114. The computer system100includes a spreadsheet analysis module126and a spreadsheet pivot table module118. Generally, the computer system100may receive and process DAXs, such as an illustrative DAX104. The DAXs may have syntax similar to existing spreadsheet formulae and may enable multi-dimensional (e.g. multi-table and/or multi-column) data analysis at the computer system100.

The computer system100includes the data interface110that is configured to receive data102. In a particular embodiment, the data102is provided by a user of the computer system100. Alternately, the data102may be received from another computer system, a network storage device, or a network share. The data interface110is further configured to create a data table112based on the received data at a memory114using an in-memory column-based store. For example, the data interface110may create a table within a spreadsheet application (e.g., a spreadsheet table), where the table includes the data102. An online analytical processing (OLAP) cube116data structure may be constructed at the memory114based on the in-memory column-based store. An OLAP cube may store data arranged such that each of the three dimensions (i.e., axes) of the OLAP cube provide a different arrangement of the data. For example, an OLAP cube may structure sales data arranged by date, product identifier, and customer identifier, as further described herein with reference toFIGS. 3-6. Alternately, the data table112may be stored in another data structure, such as an OLAP hypercube (e.g., an OLAP data structure having more than three dimensions) or some other in-memory column store. Storage of the data table112in the OLAP cube116at the memory114(e.g., random access memory (RAM) of the computer system100) may help in facilitating multi-dimensional data analysis and pivot table operations as described herein. Data tables are further described herein with respect toFIGS. 3 and 5.

The computer system100also includes a spreadsheet pivot table module118. In an illustrative embodiment, the spreadsheet pivot table module118is part of a spreadsheet application of the computer system100. The spreadsheet pivot table module118includes logic120to generate a pivot table122based on the data table112referenced by the OLAP cube116. The pivot table122may support “pivot” operations, where row headers, column headers, filters, or slicers of the pivot table122are changed and data values in the pivot table122are automatically updated to reflect the changes. In a particular embodiment, updating the pivot table122in response to a pivot operation includes re-executing a query of the in-memory OLAP cube116, so that data from the OLAP cube is arranged and viewed along different dimensions of the OLAP cube. Pivot tables are further described herein with reference toFIGS. 4 and 6.

The computer system100further includes a spreadsheet analysis module126. In an illustrative embodiment, the spreadsheet analysis module126is part of a spreadsheet application of the computer system100. The spreadsheet analysis module126is configured to receive the DAX104and includes DAX execution logic128configured to execute the DAX104. For example, the spreadsheet analysis module126may execute the DAX104with respect to the data table112. Executing the DAX for a particular cell of the pivot table122includes determining a filter context for the particular cell as well as the row context(s) for tables referenced by the DAX104, retrieving data124based on the row context(s) (e.g., data associated with one or more rows of the data table112) from the OLAP cube116, calculating a value130of the DAX104based on the retrieved data, and outputting the calculated value130at the cell of the pivot table. Thus, populating the pivot table122may include automatic recursive executions of the DAX104with respect to different contexts and cross-filtering of multiple data tables. Alternately, calculations may be performed in a block mode, so that calculations for multiple cells of the pivot table may be performed simultaneously.

In a particular embodiment, the DAX104includes a formula to be aggregated over multiple rows of the data table112. The formula may be a user-defined formula expressed in a native formula language of a spreadsheet application that includes the modules118,126, without referring to specific cell ranges of the spreadsheet application. Thus, DAXs (e.g., the DAX104) may enable table-based (e.g., column based) multi-dimensional data analysis (as opposed to conventional spreadsheet cell-based analysis) while incorporating existing spreadsheet formulae that users are familiar with. For example, the DAX104may include aggregations (e.g., sum, average, minimum, maximum, or count), time-based functions (e.g., days, weeks, months, quarters, years, first and last date, first and last non blank date, start and end of month/quarter/year, dateadd, datesbetween, datesinperiod, parallelperiod, previous day/month/quarter/year, next day/month/quarter/year, month/quarter/years dates to current date, sameperiodlastyear, aggregateover month/quarter/year, or opening and closing monthly/quarterly/yearly balance), or any combination thereof. The DAX may further include apply functions, groupby functions, semijoin functions, lookupvalues functions, earlier/earliest functions (e.g., to refer to a previous value at a cell), intersect functions, except functions, union functions, select functions, join functions, topN functions, rank functions, or any combination thereof. The DAX104may also include specialized table-based functions having syntax similar to commonly used spreadsheet formulae. For example, the DAX104may include 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.

In operation, the data table112may be created based on the data102received by the data interface110. It should be noted that operation with respect to a single data table112is provided for illustrative purposes only. There may be any number of data tables and data sources. The data table112may be used as a data source for the OLAP cube116in the memory114. A spreadsheet application user may desire to perform analysis on the data table112through the use of a pivot table122. The pivot table122may be generated by the spreadsheet pivot table module118. In defining measures output by the pivot table122, the user may input a DAX104. The spreadsheet analysis module126may populate the cells of the pivot table122by executing the DAX104. Populating a particular cell of the pivot table122may include determining a filter context associated with the particular cell, calculating the DAX value130for the particular cell based on the context, and outputting the calculated DAX value130at the cell.

In a particular embodiment, execution of DAXs involves a hybrid iterator-based and lookup-based execution strategy. In another particular embodiment, executing a DAX may include cross-application of a canonical form (e.g., a non-relational algebra form) of an execution tree for the DAX. In another particular embodiment, DAX execution may include dependency analysis to determine what sub-calculations a calculated column depends on. Based on the dependency analysis, an order for calculating the sub-calculations may be determined. For example, if the value A in a calculated column depends on the results of three sub-calculations B, C, and D, then a rule may be generated that results in the calculation of each of B, C, and D before an attempt to calculate A.

It should be noted that although the particular embodiment illustrated inFIG. 1depicts the use of DAXs in pivot tables, DAXs may also be used without pivot tables. For example, the DAX104may be entered at a spreadsheet application as a column definition of a particular column of the data table112. The DAX104may be based on one or more columns of the data table112(and possibly additional tables) other than the particular column. Populating a cell of the particular column may include determining a row context for the cell, calculating a value of the DAX104based on the row context, and outputting the calculated value for the DAX104at the cell. In another embodiment, the DAX104may additionally be based on at least one column of a second data table. In such an embodiment, calculating a value for the DAX may include retrieving data from the second data table based on a relationship between the data table112and the second data table. For example, the data table112may include a column containing a value that may be found in a related column (e.g., an index column) of the second data table. In a particular embodiment, the second data table is involved in the calculation even when no relationship exists. For example, executing the DAX104may include filtering the second data table to include rows that pertain to data values in the data table112. The resulting filtered rows may then be aggregated by the DAX. It should be noted that the relationship (e.g., related column) between the data table112and the second data table may exist even when the related column has a different name in the data table112than in the second data table. Relationships between data tables are further described with respect toFIGS. 5-6.

It will be appreciated that the system100ofFIG. 1may enable, via DAXs, advanced data analysis not enabled by generic spreadsheet formulae. It will further be appreciated that the system100ofFIG. 1may enable such advanced analysis based on multiple tables without references to particular cell ranges of a spreadsheet.

FIG. 2is a block diagram to illustrate another particular embodiment of a computer system100to receive and process DAXs. The system200includes a spreadsheet application210configured to receive user input202. The spreadsheet application210also includes an analysis module220. In an illustrative non-limiting embodiment, the analysis module220is the spreadsheet analysis module126ofFIG. 1and includes DAX execution logic222similar to the DAX execution logic128ofFIG. 1.

The spreadsheet application210may include logic204configured to receive a query201in response to a change at a pivot table at the spreadsheet application210. For example, the logic204may receive the query201in response to a user changing a setting at the pivot table122ofFIG. 1. In response to receiving the query201, the logic204may automatically generate a command208to recalculate a DAX associated with the pivot table and send the command208to the analysis module220.

The spreadsheet application210may include logic206configured to detect changes in one or more data tables at the spreadsheet application210. For example, the logic206may be configured to detect changes at a data table such as the pivot table112ofFIG. 1. The changes may be detected based on user input202. For example, the user input202may include a new value for a cell of the data table. In response to detecting changes in the data table(s), the logic206may automatically generate the command208to recalculate a DAX (e.g., a column definition DAX) associated with the changed data table(s) and send the command208to the analysis module220.

In response to receiving the command208, the analysis module220may automatically recalculate one or more DAXs at the spreadsheet application. For example, the analysis module220may automatically recalculate column definition DAXs at data tables of the spreadsheet application210, DAXs at a pivot table of the spreadsheet application210, or any combination thereof.

It will be appreciated that the system200ofFIG. 2may improve user experience at the spreadsheet application210by enabling automatic recalculation of DAXs. Thus, users of the spreadsheet application may make changes to data tables and pivot tables and observe corresponding changes in DAX values without redefining and manually re-executing the DAXs. It will therefore be appreciated that once entered, a DAX may be used to perform multi-dimensional data analysis multiple times with respect to changing data.

FIGS. 3-4illustrate a particular embodiment of the data table112ofFIG. 1(illustrated as a sales table300) and a pivot table400generated based on the sales table300. The sales table300includes one or more rows310and columns320,330,340,350,360, and370. In an illustrative embodiment, the sales table300includes the data table112ofFIG. 1.

Each of the rows310of the sales table300may represent a sales transaction and each column320-370of the sales table300may represent data associated with a sales transaction. For example, the column320may represent a date of a particular sale, the column330may represent a customer identifier (CustID) indicating which customer paid for the particular sale, the column340may represent a product identifier (ProdID) indicating which product was paid for, the column350may represent a quantity of products sold, the column360may represent a price charged for each of the products sold, and the column370may represent a total amount370generated by sale. In a particular embodiment, the amount column370is defined by a spreadsheet formula (e.g., “=Qty*Price”). The sales table300further includes a static sum aggregation380for the amount column370.

It should be noted that column references included in a DAX may be polymorphic. That is, a column reference in a DAX may resolve to a column when used in a column calculation and may resolve to a value stored at a particular row of the column when used in a scalar calculation. For example, when used in a column calculation, the column reference “Amount” may resolve to the column370, but when used in a scalar calculation the reference “Amount” may resolve to the value of Qty*Price stored at a particular row of the rows310ofFIG. 3. It should also be noted that a DAX, or a part thereof, may include functions or formulae that may result in a table useable as an intermediate result of a scalar calculation.

In the particular embodiment illustrated inFIG. 4, the pivot table400pivots on the data column320and a combination of the ProdID column340and the CustID column330of the sales table300ofFIG. 3. That is, the pivot table400may aggregate data based on date and ProdID/CustID contexts from the sales table300ofFIG. 3. For example, a DAX410“SUM[Amount]” may be received at the pivot table400, indicating a desire that the cells of the pivot table400contain a sum aggregation of the amount column370ofFIG. 3for various combinations (i.e., contexts) of date, ProdID, and CustID of the sales table300ofFIG. 3. Accordingly, a first illustrative cell412of the pivot table400indicates an amount received from selling “456Red” products during 2009 regardless of customer and a second illustrative cell414indicates an amount received from selling “789Green” products to customer “Jon200” during 2008. That is, a first context associated with the first cell412may be “Time[Year]=2009; Product[ProdID]=‘456Red’” and a second context associated with the second cell414may be “Time[Year]=2008; Product[ProdID]=‘789Green’; Customer[CustID]=‘Jon200’.”

It should be noted that contexts may also include inequalities. For example, products purchased by the customer “Jon200” that cost more than $200 may be determined using the context “Customer[CustID]=‘Jon200’; Product[Price]>200.00”.

It should also be noted that the pivot table400may also be generated using a different DAX than the DAX410. For example, if the amount column370ofFIG. 3did not exist, the pivot table400may be generated by incorporating the “=Qty*Price” formula of the amount column370into the DAX410. For example, the DAX410may be “SUM[Qty*Price].”

It will be appreciated that multiple cells of the pivot table400ofFIG. 4may be populated based on contexts associated with the sales table300ofFIG. 3without entering the DAX410ofFIG. 4multiple times. Instead, the DAX410ofFIG. 4may be automatically recursively re-executed for each cell of the pivot table400ofFIG. 4based on a different context associated with each cell of the pivot table400ofFIG. 4. It will further be appreciated that the static sum aggregation380may indicate a total of all products sold, but the DAX410may instead be used to provide a multi-dimensional view of the products sold (e.g., total by product, total by year, and total by combination of product and year).

FIGS. 5-6illustrate another particular embodiment of the data table112ofFIG. 1(illustrated as an inventory table500) and a pivot table600based on the data tables ofFIG. 3andFIG. 5(e.g., the sales table300ofFIG. 3and the inventory table500ofFIG. 5). Each of the rows510of the inventory table500may represent a particular product available for sale and columns520-540may represent data associated with the particular product. For example, the column520may represent a product identifier (ProdID) of the particular product, the column530may represent a description of the particular product, and the column540may represent how many units of the particular product are currently in-stock.

One or more columns of the inventory table500may also include a DAX column definition. For example, the units sold column550has an associated DAX column definition “SUM[Qty]”560. Rows of the units sold column550may be populated by aggregating the Qty column350ofFIG. 3for various products. For example, the cell552may be populated by filtering the sales table300ofFIG. 3for rows that have a ProdID of ‘789Green’ and then aggregating the Qty column of rows that remain. It will be appreciated that to populate the cell552, a relationship (e.g., a previously user-defined relationship) between the sales table300ofFIG. 3and the inventory table500ofFIG. 5(e.g., that the ProdID columns370ofFIG. 3 and 520ofFIG. 5are related) may automatically be identified and traversed. It should be noted that filtering a table (e.g., the sales table300ofFIG. 3) may include Boolean filters as well as table-based filters.

The pivot table600may pivot on columns of multiple data tables. In the particular embodiment illustrated inFIG. 6, the pivot table600pivots the date column320ofFIG. 3and a combination of the description column530ofFIG. 5and CustID column330ofFIG. 300. That is, although the pivot table600aggregates data from one table, the sales table300ofFIG. 3, the aggregation may be based on contexts from both the sales table300ofFIG. 3and the inventory table500ofFIG. 5.

The pivot table600may receive a DAX610“SUM[Amount]” similar to the DAX410ofFIG. 4, and the value of the DAX610may be recursively calculated to populate cells of the pivot table600. Populating the pivot table600may include identifying a relationship between the sales table300ofFIG. 3and the inventory table500ofFIG. 5and retrieving data from both the sales table300ofFIG. 3and the inventory table500ofFIG. 5. For example, in the particular embodiment illustrated inFIG. 6, a relationship between the tables300ofFIG. 3 and 500ofFIG. 5may automatically be identified. The relationship may include the related columns340ofFIG. 3 and 520ofFIG. 5, both of which are product identifier (ProdID) columns. In a particular embodiment, the related columns are index columns. Thus, a row header612“Blue Bike” of the pivot table600may be populated based on the identified relationship between the sales table300ofFIG. 3and the inventory table500ofFIG. 5. A first illustrative cell614of the pivot table600may be associated with a first context “Time[Year]=2009; Product[Description]=‘Red Bike’” and a second illustrative cell616of the pivot table600may be associated with a second context “Time[Year]=2008; Product[Description]=‘Green Trike’; Customer[CustID]=‘Jon200’.”

It will thus be appreciated that DAXs (e.g., the DAXs560ofFIG. 5 and 610ofFIG. 6) may enable multi-dimensional data analysis across multiple tables (e.g., the sales table300ofFIG. 3and the inventory table500ofFIG. 5). It will further be appreciated that such multi-dimensional data analysis may occur at the tables themselves (e.g., at the column550ofFIG. 5) as well as at a pivot table (e.g., the pivot table600ofFIG. 6).

FIG. 7is a flow diagram to illustrate a particular embodiment of a method700of receiving and processing data analysis expressions. In an illustrative embodiment, the method700may be performed by the system100ofFIG. 1.

The method700includes receiving a DAX at a pivot table of a spreadsheet, at702. For example, inFIG. 1, the DAX104may be received at the pivot table122. To illustrate, referring toFIG. 4, the DAX may be the DAX “SUM[Amount]”410.

The method700also includes executing the DAX, at704. For example, inFIG. 1, the spreadsheet analysis module126may execute the DAX104. With respect to a particular cell of the pivot table, executing the DAX includes determining a context associated with the particular cell, at706, calculating a value of the DAX based on the context, at708, and outputting the calculated value at the particular cell, at710. For example, inFIG. 1, a context may be determined, the data124based on the context may be retrieved, and the DAX value130may be calculated and output at the particular cell of the pivot table122. To illustrate, referring toFIG. 4, the particular cell may be the cell414ofFIG. 4, the context may be “Time[Year]=2008; Product[ProdID]=‘789Green’; Customer[CustID]=‘Jon200’,” and the calculated value of the DAX output at the cell414may be “$1,500.”

FIG. 8is a flow diagram to illustrate another particular embodiment of a method800of receiving and processing data analysis expressions. In an illustrative embodiment, the method800may be performed by the system100ofFIG. 1or by the system200ofFIG. 2.

The method800includes receiving a DAX at a pivot table of a spreadsheet, at802. The DAX includes a user-defined formula expressed in a native formula language of the spreadsheet (e.g., the DAX may include existing spreadsheet functions and may include syntax similar to existing spreadsheet functions). For example, inFIG. 1, the DAX104may be received at the pivot table122.

The method800also includes executing the DAX, at804. For example, inFIG. 1, the spreadsheet analysis module126may execute the DAX104. With respect to a particular cell of the pivot table, executing the DAX104includes determining a context associated with the particular cell, including filtering the at least one data table based on the particular cell, at806. Executing the DAX104also includes calculating a value of the DAX104based on the context, at808, and outputting the calculated value at the particular cell, at810. For example, inFIG. 1, a context may be determined, the data table112may be filtered based on the context (e.g., rows that do not match the context may temporarily be hidden or ignored), the data based on the context124may be retrieved, and the DAX value130may be calculated and output at the particular cell of the pivot table122.

The method800further includes receiving a query in response to a change at the pivot table, at812, or detecting a change at the at least one data table, at814. For example, inFIG. 1, a pivot operation of the pivot table122may occur or a change in the data table112may be detected. In an illustrative embodiment, the query is received as described herein with respect to the logic204ofFIG. 2and the change at the at least one data table is detected as described herein with respect to the logic206ofFIG. 2.

The method800includes automatically re-executing the DAX104, at816, by returning to806. For example, inFIG. 1, the spreadsheet analysis module126may automatically re-execute the DAX104. In an illustrative embodiment, the automatic re-execution of the DAX104is performed as described herein with respect to the command208and the module220ofFIG. 2.

FIG. 9is a flow diagram to illustrate another particular embodiment of a method900of receiving and processing data analysis expressions. In an illustrative embodiment, the method900may be performed by the system100ofFIG. 1.

The method900includes receiving input including a column definition of a particular column of a first spreadsheet table, at902. The input includes a DAX based on at least one column of the first spreadsheet table and based on at least one column of a second spreadsheet table. For example, referring toFIG. 5, the DAX “SUM[Qty]”560may be received as a column definition for the column550, where the DAX560refers to both the sales table300ofFIG. 3and the inventory table500ofFIG. 5.

The method900also includes determining a relationship between the first spreadsheet table and the second spreadsheet table based on the DAX, at904. In a particular embodiment, the relationship is a related column, an index column, or a column having different names in the two spreadsheet tables. For example, a relationship between the sales table300ofFIG. 3and the inventory table500ofFIG. 5may be identified, such as identifying the ProdID columns340ofFIG. 3 and 520ofFIG. 5as related columns.

The method900further includes populating the particular column by executing the DAX, at906. Executing the DAX for a particular row of the first spreadsheet table includes calculating a value of the DAX, at908, and outputting the calculated value at a cell that is a member of the particular row and the particular column, at910. The value is calculated based on first data in the particular row of the first spreadsheet table and based on second data retrieved from the second table based on a row context associated with the particular row. For example, referring toFIG. 5, the cell552may be populated by aggregating rows of the sales table300matching the row context “Product[ProdID]=‘789Green’” and outputting the resulting value at the cell552.

The method900includes receiving a selection of a subset of rows of the first spreadsheet table, at912, and automatically re-executing the DAX for the selected subset of rows, at914, by returning to908. For example, referring toFIG. 5, a selection of one or more of the rows510may be received and the DAX560may be re-executed for the selected rows.

FIG. 10depicts a block diagram of a computing environment1000including a computing device1010operable to support embodiments of computer-implemented methods, computer program products, and system components according to the present disclosure. In an illustrative embodiment, the computing device1010may include the data interface110ofFIG. 1, the memory114ofFIG. 1, the modules118,126ofFIG. 1, the logic204-206ofFIG. 2, or the module220ofFIG. 2. Each of the data interface110ofFIG. 1, the memory114ofFIG. 1, the modules118,126ofFIG. 1, the logic204-206ofFIG. 2, or the module220ofFIG. 2may include the computing device1010or a portion thereof.

The computing device1010includes at least one processor1020and system memory1030. Depending on the configuration and type of computing device, the system memory1030may be volatile (such as random access memory or “RAM”), non-volatile (such as read-only memory or “ROM,” flash memory, and similar memory devices that maintain stored data even when power is not provided) or some combination of the two. The system memory1030typically includes an operating system1032, one or more application platforms1034, one or more applications (e.g., a spreadsheet application1036), and may include program data associated with the one or more applications (e.g., an OLAP cube data structure1038). In an illustrative embodiment, the spreadsheet application1036is the spreadsheet application210ofFIG. 2and the OLAP cube data structure1038is the OLAP cube data structure116ofFIG. 1. In an illustrative embodiment, the system memory1030may include one or more logical components or modules as disclosed herein. For example, the system memory1030may include one or more of the modules118,126ofFIG. 1, the logic204-206ofFIG. 2, or the module220ofFIG. 2.

The computing device1010may also have additional features or functionality. For example, the computing device1010may also include removable and/or non-removable additional data storage devices such as magnetic disks, optical disks, tape, and standard-sized or miniature flash memory cards. Such additional storage is illustrated inFIG. 10by removable storage1040and non-removable storage1050. Computer storage media may include volatile and/or non-volatile storage and removable and/or non-removable media implemented in any method or technology for storage of information such as computer-readable instructions, data structures, program components or other data. The system memory1030, the removable storage1040and the non-removable storage1050are all examples of computer storage media. The computer storage media includes, but is not limited to, RAM, ROM, electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disks (CD), digital versatile disks (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to store information and that can be accessed by the computing device1010. Any such computer storage media may be part of the computing device1010. The computing device1010may also have input device(s)1060, such as a keyboard, mouse, pen, voice input device, touch input device, etc. Output device(s)1070, such as a display, speakers, printer, etc. may also be included.

The computing device1010also contains one or more communication connections1080that allow the computing device1010to communicate with other computing devices1090over a wired or a wireless network. In an illustrative embodiment, the communication connections1080include the data interface110ofFIG. 1and the data102ofFIG. 1is received from other computing devices1090such as a shared network storage device.

It will be appreciated that not all of the components or devices illustrated inFIG. 10or otherwise described in the previous paragraphs are necessary to support embodiments as herein described. For example, the input device(s)1060and output device(s)1070may be optional.

The steps of a method described in connection with the embodiments disclosed herein may be embodied directly in hardware, in a software module executed by a processor, or in a combination of the two. A software module may reside in computer readable media, such as random access memory (RAM), flash memory, read only memory (ROM), registers, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art. An exemplary storage medium is coupled to the processor such that the processor can read information from, and write information to, the storage medium. In the alternative, the storage medium may be integral to the processor or the processor and the storage medium may reside as discrete components in a computing device or computer system.

Although specific embodiments have been illustrated and described herein, it should be appreciated that any subsequent arrangement designed to achieve the same or similar purpose may be substituted for the specific embodiments shown. This disclosure is intended to cover any and all subsequent adaptations or variations of various embodiments.