Multidimensional cube functions

• US 7,805,433 B2
• Filed: 10/14/2005
• Issued: 09/28/2010
• Est. Priority Date: 10/14/2005
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Ellis, Charles D., Dickerman, Howard J., Boon, Brian W., Hu, Kaicheng, Hawking, Robert G.
• Primary Examiner(s): Le; Debbie
• Assistant Examiner(s): Tran; Bao G
• Application Number:
  US11/251,598
• Publication Number:
  US 20070088691A1
• Time in Patent Office: 1,810 Days
• Field of Search: 707/103, 707/4, 707/2, 707/5, 715255-272
• US Class Current: 707/713
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

Cube functions may be used to obtain data from a multidimensional database. The cube functions may be contained within one or more cells of a spreadsheet. These cube functions behave similarly to the standard functions that may be included within a spreadsheet. Exemplary cube functions include obtaining: a cube member, a cube value, a cube set, a ranked member, a KPI, a member property and a count relating to a set. The cube functions within the spreadsheet may access the cube data from one or more multidimensional databases. Using the cube formulas in individual cells allows the user to add/delete rows and/or columns from within the spreadsheet.

Claims

1. A computer-implemented method for retrieving data from a multidimensional database, comprising:

obtaining a cube function from a cell within a spreadsheet having cells;

wherein the cube function is directed at obtaining data from a multidimensional database and wherein the cell behaves similarly to the other cells within the spreadsheet having functions that perform other functions;

wherein the cube function is selected from a set of cube functions comprising;

a cube member function that identifies an item in the multi-dimensional database and returns a fully qualified unique Multi-Dimensional eXpression (MDX) name for the member and a user-friendly caption for the member;

a cube value function that returns an aggregated value from the multidimensional database, wherein a parameter to the cube value function is a MDX name of a member within the multidimensional database, wherein the name is used as a slicer to define the portion of the cube for which the aggregated value is to be returned;

a cube set function that defines a set that is to be built for the spreadsheet by an Online analytical processing (OLAP) server storing the multidimensional database, wherein a parameter to the cube set function is an MDX expression; and

a member property function that identifies a value of a member property in the multi-dimensional database and a count relating to a set within the multidimensional database, wherein a parameter to the member property function is an MDX unique name of a member;

wherein the cube function within the cell includes a connection parameter that is included within the cell that specifies the multidimensional database to access;

wherein the connection parameter identifies the multi-dimensional database to access and wherein each cube function that is included within the spreadsheet can access a different multidimensional database;

generating an MDX query as specified by the cube function;

connecting to the multidimensional database that is associated with the cube function;

obtaining the data from the multidimensional database as specified by the MDX query generated from the cube function; and

placing the data within the spreadsheet cell.

2. The method of claim 1, wherein the cube member function further comprises a ranked member function that returns an Nth (Rank) cube member (or tuple) in a Set, with a Caption as the display value, and a unique MDX name as the cell'"'"'s MDX property;

and a KPI function that returns a KPI specified by a KPI name and a KPI Component.

3. The method of claim 1, further comprising calculating the cells within the spreadsheet asynchronously such that interaction with the spreadsheet may occur while the data is being obtained from the multidimensional database.

4. The method of claim 1, wherein connecting to the multidimensional database comprises obtaining a selection for the multidimensional database from a graphical user interface.

5. The method of claim 1, wherein the MDX query has a dependency on a spreadsheet function.

6. The method of claim 5, wherein obtaining the cube function from the cell within a spreadsheet having cells comprises obtaining each cube function that is contained within the cells of the spreadsheet;

obtaining the data from the multidimensional database using the MDX query and placing a message within each of the cells including at least one of the cube functions while the data is being obtained.

7. The method of claim 5, wherein rows and columns may be deleted from the spreadsheet without interfering with the other cells within the spreadsheet to be refreshed.

8. A computer-readable storage medium having computer-executable instructions for interacting with an Online analytical processing (OLAP) cube, comprising:

obtaining a cube function from a cell within a spreadsheet having cells;

wherein the cube function is directed at obtaining data from a multidimensional database and wherein the cell behaves similarly to the other cells within the spreadsheet having functions that perform other functions;

wherein the cube function is selected from a set of cube functions comprising;

a cube member function that identifies an item in the multi-dimensional database and returns a fully qualified unique Multi-Dimensional eXpression (MDX) name for the member and a user-friendly caption for the member;

a cube value function that returns an aggregated value from the multidimensional database, wherein a parameter to the cube value function is a MDX name of a member within the multidimensional database, wherein the name is used as a slicer to define the portion of the cube for which the aggregated value is to be returned;

a cube set function that defines a set that is to be built for the spreadsheet by an Online analytical processing (OLAP) server storing the multidimensional database, wherein a parameter to the cube set function is an MDX expression; and

a member property function that identifies a value of a member property in the multi-dimensional database and a count relating to a set within the multidimensional database, wherein a parameter to the member property function is an MDX unique name of a member;

wherein the cube function within the cell includes a connection parameter that is included within the cell that specifies the multidimensional database to access;

wherein the connection parameter identifies the multi-dimensional database to access and wherein each cube function that is included within the spreadsheet can access a different multidimensional database;

generating an MDX query as specified by the cube function;

connecting to the multidimensional database that is associated with the cube function;

obtaining the data from the multidimensional database as specified by the MDX query generated from the cube function; and

placing the data within the spreadsheet cell.

9. The computer-readable storage medium of claim 8, further comprising calculating the data within the spreadsheet cell asynchronously.

10. The computer-readable storage medium of claim 8, wherein connecting to the multidimensional database comprises obtaining a name from the cube function and using the name to connect to the multidimensional database.

11. The computer-readable storage medium of claim 8, wherein the MDX query has a dependency on a spreadsheet function.

12. The computer-readable storage medium of claim 8, wherein obtaining the cube function from the cell within a spreadsheet comprises placing a message within the cell while the data is being obtained.

13. The computer-readable storage medium of claim 8, wherein rows and columns may be deleted from the spreadsheet without interfering with other cells within the spreadsheet to be refreshed.