Method, system, and apparatus for exposing workbooks as data sources

• US 7,991,804 B2
• Filed: 07/30/2004
• Issued: 08/02/2011
• Est. Priority Date: 07/30/2004
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Battagin, Daniel C., Asnash, Liviu, Petculescu, Cristian, Netz, Amir, Prish, Shahar, Megiddo, Eran
• Primary Examiner(s): STACE, BRENT S
• Application Number:
  US10/903,568
• Publication Number:
  US 20060024653A1
• Time in Patent Office: 2,559 Days
• Field of Search: 707/3, 707/4, 707100-102, 707/804, 707/758, 707/600, 707/803, 707/802, 707/790, 707/705
• US Class Current: N/A
• 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
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method, system, and apparatus are provided for exposing and utilizing workbooks as server data sources. The system includes a client computer capable of executing a spreadsheet application program for creating a workbook including one or more worksheets. The workbook may be published to a server computer where the contents of the workbook are exposed as a multi-dimensional data source. The server computer allows client applications to discover and connect to the workbook as a multi-dimensional, data source, such as a cube.

Claims

1. A method for exposing the contents of a workbook as a data source, the method comprising:

receiving, by a computer, a database query directed toward the workbook, wherein the workbook comprises a first data source;

in response to the received query;

determining whether the workbook contains a reference to at least one second data source,

in response to determining that the workbook contains a references to the at least one second data source, retrieving from the at least one second data source the data necessary to calculate the workbook,

calculating the workbook,

generating a database representation of the calculated workbook, wherein generating the database representation comprises;

setting variables corresponding to the workbook, a worksheet, and a cell, wherein the set variables comprises a first workbook, a first worksheet, and a first cell,

reading the variables for the first workbook, the first worksheet, and the first cell,

inserting the variables into the database representation,

determining whether additional cells have to be analyzed,

in response to determining that additional cells have to be analyzed, setting the variable corresponding to the first cell to a second cell,

reading the variable of the second cell,

in response to determining that no additional cells have to be analyzed, determining whether additional worksheets have to be analyzed,

in response to determining that no additional worksheets have to be analyzed, setting the variable corresponding to the first worksheet to a second worksheet,

reading the variable of the second worksheet,

in response to determining that no additional worksheets have to be analyzed, determining whether additional workbooks have to be analyzed,

in response to determining that additional workbooks have to be analyzed, setting the variable corresponding to the first workbook to a second workbook,

reading the variable of the second workbook,

analyzing a first data contained within the workbook, wherein analyzing the first data comprises identifying a second data corresponding to the first data, the second data describing at least one of the following;

measures, a predefined hierarchy, indicating a structure of the first data, and default aggregations indicating a content for the data fields within the workbook,

using the second data, at least one workbook variable, at least one worksheet variable, the at least one second data source, and at least one cell variable to map a third data from the calculated workbook to the database representation of the calculated workbook, wherein the database representation comprises a multi-dimensional cube wherein the first dimension is associated with at least one worksheet row, a second dimension is associated with at least one worksheet column, a third dimension is associated with at least one second worksheet, and a fourth dimension is associated with the at least one second data source,

performing the received query of the database representation of the calculated workbook to obtain a query result, and transmitting the query result as a response to the database query;

receiving a request to edit the second data by a user; and

in response to the received request;

providing a facility for editing the second data, wherein the editing the second data comprises defining a subset of the first data, and

receiving the second data edited by the user.

2. The method of claim 1, wherein the database representation comprises a cube and wherein the database query is received from an OLAP client application.

3. The method of claim 1, wherein prior to receiving the database query directed toward the workbook, publishing the workbook.

4. The method of claim 3, wherein publishing the workbook comprises utilizing a spreadsheet application to create the workbook.

5. The method of claim 3, wherein publishing the workbook comprises generating metadata, wherein generating metadata comprises utilizing data to describe data within the workbook.

6. The method of claim 5, wherein in response to generating metadata, determining whether a request to edit the metadata was received.

7. The method of claim 6, wherein in response to determining that the request to edit the metadata was received:

providing a user interface;

overriding the metadata;

setting new metadata; and

saving the new metadata with the workbook.

8. The method of claim 6, wherein in response to determining that the request to edit the metadata was not received, saving the metadata with the workbook.

9. A method for exposing the contents of a workbook as a first server data source, the method comprising:

providing a facility for authoring the workbook comprising at least one worksheet having at least one cell;

publishing the workbook to a server computer, wherein publishing the workbook to a server computer exposes the at least one worksheet and the at least one cell at the server computer as a multi-dimensional data source, wherein the multi-dimensional data source comprises a cube, the cube comprising at least one of the following;

a first dimension associated with at least one row in a first worksheet, a second dimension associated with at least one column, in the first worksheet, a third dimension associated with a second worksheet, and fourth dimension associated with at least one second server data source;

receiving a query directed to the multi-dimensional data source;

in response to the query;

retrieving from the at least one second server data source the data necessary to calculate the workbook,

calculating the workbook at the server computer, and

generating the multi-dimensional data source from the calculated workbook, wherein generating the multi-dimensional data source comprises;

setting variables corresponding to the workbook, a worksheet, and a cell, wherein the variables set comprises a first workbook, a first worksheet and a first cell,

reading the variables for the first workbook, the first worksheet and the first cell,

inserting the variables into the multi-dimensional data source,

determining whether additional cells have to be analyzed,

in response to determining that additional cells have to be analyzed, setting the variable corresponding to the first cell to a second cell,

reading the variable of the second cell,

in response to determining that no additional cells have to analyzed, determining whether additional worksheets have to be analyzed,

in response to determining that additional worksheets have to be analyzed, setting the variable corresponding to the first worksheet to a second worksheet,

reading the variable of the second worksheet,

in response to determining that no additional worksheets have to be analyzed, determining whether additional workbooks have to be analyzed,

in response to determining that additional workbooks have to be analyzed, setting the variable corresponding to the first workbook to a second workbook,

reading the variable of the second workbook,

analyzing a first data contained within the workbook, wherein analyzing the first data comprises identifying a second data corresponding to the first data, the second data describing at least one of the following;

measures, a predefined hierarchy indicating a structure of the first data, and default aggregations indicating a content for data fields, and

using at least one workbook variable, at least one worksheet variable, the at least one second server data source, and at least one cell variable to map a third data from the calculated workbook to the multi-dimensional data source;

performing the received query on the generated multi-dimensional data source to produce a query result;

responding to the received query with the query result; and

receiving a request to edit the second data by a user;

in response to the received request;

providing a facility for editing the second data, wherein editing the second data comprises defining a subset of the multi-dimensional data source, and

receiving the second data edited by the user.

10. The method of claim 9, wherein publishing the workbook comprises utilizing a spreadsheet application to create the workbook.

11. The method of claim 9, wherein publishing the workbook comprises generating metadata, wherein generating metadata comprises utilizing data to describe data within the workbook.

12. The method of claim 11, wherein in response to generating metadata, determining whether a request to edit the metadata was received.

13. The method of claim 12, wherein in response to determining that the request to edit the metadata was received:

providing a user interface;

overriding the metadata;

setting new metadata; and

saving the new metadata with the workbook.

14. The method of claim 12, wherein in response to determining that the request to edit the metadata was not received, saving the metadata with the workbook.

15. A method for exposing and utilizing a workbook as a first server data source, the method comprising:

authoring the workbook comprising at least one worksheet having at least one cell and at least one reference to at least one second server data source;

generating metadata for at least one data object, the metadata being available to a server computer in responding to requests for querying the workbook as the first server data source, wherein generating metadata for the at least one data object comprises;

analyzing a first data contained within the workbook, wherein analyzing the first data comprises identifying a second data corresponding to the first data, the second data describing at least one of the following;

measures, a predefined hierarchy indicating a structure of the first data, and default aggregations indicating a content for data fields and setting a metadata property;

publishing the workbook and the metadata to the server computer;

receiving a request to edit the metadata by a user;

in response to the received request;

providing a facility for editing the metadata, wherein editing the metadata comprises defining a subset of the first data, and

receiving the metadata edited by the user;

exposing the workbook as the first server data source;

receiving a server data source query at the server computer referencing data contained within the workbook;

in response to receiving the query;

loading the workbook,

retrieving from the at least one second server data source the data necessary to calculate the workbook, and

calculating the workbook;

generating a database representation of the workbook utilizing the metadata to map the first data contained within the workbook to the database representation of the calculated workbook, wherein the database representation comprises a multi-dimensional cube wherein a first dimension is associated with at least one worksheet row, a second dimension is associated with at least one worksheet column, a third dimension is associated with at least one second worksheet, and a fourth dimension is associated with the at least one second server data source, wherein generating the database representation comprises;

setting variables corresponding to the workbook, a worksheet, and a cell, wherein the set variables comprises a first workbook, a first worksheet, and a first cell,

reading the variables for the first workbook, the first worksheet, and the first cell,

inserting the variables into the database representation,

determining whether additional cells have to be analyzed,

in response to determining that additional cells have to be analyzed, setting the variable corresponding to the first cell to a second cell,

reading the variable of the second cell,

in response to determining that no additional cells have to be analyzed, determining whether additional worksheet have to be analyzed,

in response to determining the additional worksheet have to be analyzed, setting the variable corresponding to the first worksheet to a second worksheet,

reading the variable for the second worksheet,

in response to determining that no additional worksheet have to be analyzed, determining whether additional workbooks have to be analyzed,

in response to determining that additional workbooks have to be analyzed, setting the variable corresponding to the first workbook to a second workbook,

reading the variable of the second workbook;

performing the received query on the database representation of the workbook to create a query result; and

responding to the query with the query result.

16. A system for exposing and utilizing a workbook as a first server data source, the system comprising:

a first client computer configured to execute a spreadsheet application program for authoring the workbook comprising at least one worksheet having at least one cell and at least one reference to at least one second data source, the spreadsheet application program being further configured to publish the workbook to a server computer configured to execute a server program for exposing the workbook as the first server data source and for receiving and responding to query requests directed to a workbook data source;

a second client computer configured to execute an application program for issuing queries against the first server data source, the server program being further configured to receive and respond to a database query from the second client computer to the first server data source, wherein the server program being configured to respond to the database query from the second client computer comprises the server program being configured to;

retrieve from the at least one second data source the data necessary to calculate the workbook,

calculate the workbook,

generate a cube corresponding to the calculated workbook, the cube comprising at least one of the following;

a first dimension associated with at least one row in a first worksheet, a second dimension associated with at least one column in the first worksheet, a third dimension associated with a second worksheet, and a fourth dimension associated with the at least one second data source wherein generating the cube comprises;

setting variables corresponding to the workbook, a worksheet and a cell, wherein the set variables comprises a first workbook, a first worksheet, and a first cell,

reading the variables for the first workbook, the first worksheet, and the first cell,

inserting the variables into the cube,

determining whether additional cells have to be analyzed,

in response to the determining that additional cells have to be analyzed, set the variable corresponding to the first cell to a second cell,

reading the variable of the second cell,

in response to the determining that no additional cells have to be analyzed, determining whether additional worksheets have to be analyzed,

in response to the determining that additional worksheets have to be analyzed, set the variable corresponding to the first worksheet to a second worksheet,

reading the variable of the second worksheet,

in response to the determining that no additional worksheets have to be analyzed, determining whether additional workbooks have to be analyzed,

in response to the determining that additional workbooks have to be analyzed, set the variable corresponding to the first workbook to a second workbook,

reading the variable of the second workbook,

analyzing a first data contained within the workbook, wherein analyzing the first data comprises identifying a second data corresponding to the first data, the second data describing at least one of the following;

measures, a predefined hierarchy indicating a structure of the first data, and default aggregations indicating a content for data fields, and

using the second data, at least one workbook variable, at least one worksheet variable, and at least one cell variable to map a third data from the calculated workbook to the cube,

perform the received query on the cube to produce a query result including;

determine whether the workbook contains a reference to a data source,

in response to the determination that the workbook contains a reference to the data source, retrieve from the data source the third data necessary to calculate the workbook, and

recalculate the workbook utilizing the third data retrieved from the data source, and transmit the query result to the second client computer in response to the database query;

the spreadsheet application program being configured to determine if a user has made a request to edit the second data; and

in response to the determination that the user has made a request to edit the second data, the spreadsheet application program being further configured to;

provide a facility for editing the second data, wherein editing the second data comprises defining a subset of the cube, and

receive the second data edited by the user.

17. The system of claim 16, wherein the spreadsheet application program is further configured to generate and store metadata within the workbook, the metadata being available to a server computer for responding to requests for the workbook data source.

18. The system of claim 16, wherein the application executing on the second client computer comprises an OLAP client application.