Multidimensional cube functions

• US 20070088691A1
• Filed: 10/14/2005
• Published: 04/19/2007
• Est. Priority Date: 10/14/2005
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Hawking, Robert, Boon, Brian, Hu, Kaicheng, Ellis, Charles, Dickerman, Howard
• Granted Patent: US 7,805,433 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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;

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

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

placing the data within the spreadsheet cell.

2. The method of claim 1, wherein obtaining the data from the multidimensional database as specified by the cube function comprises obtaining at least one of the following from the multidimensional database:

a cube member;

a cube value;

a cube set;

a ranked member;

a KPI;

a member property and a count relating to a set within the multidimensional database.

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 name from the cube function and using the name to connect to the multidimensional database.

5. The method of claim 1, wherein obtaining the data from the multidimensional database as specified by the cube function comprises generating an MDX query.

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 medium having computer-executable instructions for interacting with an OLAP cube, comprising:

parsing a cube function having parameters;

wherein the cube function may be included within a cell of a spreadsheet and wherein the cube function is directed at obtaining data from an OLAP cube;

determining an OLAP cube from which to obtain data; and

fetching the data from the determined OLAP cube in response to the parameters of the cube function.

9. The computer-readable medium of claim 8, wherein determining the OLAP cube from which to obtain data comprises locating a connection name within the parameters;

wherein the connection name determines the OLAP cube to obtain data from.

10. The computer-readable medium of claim 8, wherein parsing the cube function comprises parsing the parameters to locate one or more names, wherein the names identify at least one of a member and a tuple within the OLAP cube;

and wherein each of the located names is used a slicer to define a portion of the OLAP cube for which an aggregated value is to be fetched from the OLAP cube.

11. The computer-readable medium of claim 8, wherein parsing the cube function comprises parsing the parameters to locate a name that identifies a member of the OLAP cube and using the name to fetch at least one of a unique MDX member name, a MDX tuple, and a caption for the member of the OLAP cube.

12. The computer-readable medium of claim 8, wherein parsing the cube function comprises parsing the parameters to locate an expression that identifies a set that is at least one of a set of members within the OLAP cube and a set of tuples within the OLAP cube;

and then using the query to fetch the set from the OLAP cube; and

when a sort parameter exists within the parameters;

using the sort parameter to sort the set.

13. The computer-readable medium of claim 8, wherein parsing the cube function comprises parsing the parameters to locate a set and a rank;

wherein the rank is used to fetch at least one of the elements from the set.

14. The computer-readable medium of claim 8, wherein parsing the cube function comprises parsing the parameters to locate a KPI name and a KPI component;

wherein the KPI name and the KPI component are used to fetch a member from the OLAP cube.

15. The computer-readable medium of claim 8, wherein parsing the cube function comprises parsing the parameters to locate a set and wherein a number of the members within the set is determined.

16. The computer-readable medium of claim 8, wherein parsing the cube function comprises parsing the parameters to locate a name and a property;

wherein the name and member are used to obtain a value of a member property in the OLAP cube.

17. A system for interacting with a multidimensional database from a spreadsheet, comprising:

a spreadsheet application that is coupled to a network and is configured to perform steps, comprising;

using a cube function within a cell of the spreadsheet to generate an MDX query;

wherein the MDX query locates data within a multidimensional database;

querying a server using the MDX query;

receiving data returned in response to the MDX query; and

updating the cell and any other dependent cells in response to the received data; and

the server that is coupled to a network and the spreadsheet application, and wherein the server, comprises;

an application that is configured to perform actions, comprising;

receive an MDX query that requests data from the multidimensional database; and

attempting to obtain the requested data; and

when successful in obtaining the requested data delivering the data to the spreadsheet application.

18. The system of claim 17, wherein the MDX query requests at least one of the following from the multidimensional database:

a cube member;

a cube value;

a cube set;

a ranked member;

a KPI member;

a member property and a count relating to a set within the multidimensional database.

19. The system of claim 18, wherein any number of cube functions may be contained within any of the cells of the spreadsheet.

20. The system of claim 18, further comprising determining the multidimensional database from the cube function.