Incorporation of spreadsheet formulas of multi-dimensional cube data into a multi-dimensional cube

• US 7,530,012 B2
• Filed: 05/22/2003
• Issued: 05/05/2009
• Est. Priority Date: 05/22/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Rutledge, Stephen H., Medicke, John A., Russell, Feng-Wei Chen
• Primary Examiner(s): Hutton; Doug
• Assistant Examiner(s): Tran; Quoc A
• Application Number:
  US10/443,523
• Publication Number:
  US 20040237029A1
• Time in Patent Office: 2,175 Days
• Field of Search: 715/503, 715212-220, 707/1, 707/2, 707/3, 707/104.1, 707/102, 707/103, 707/9, 707/4, 712/1
• US Class Current: 715/212
• 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/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

Computations included in analytics of a multi-dimensional cube are generated by analyzing a spreadsheet corresponding to data downloaded from the multi-dimensional cube so as to automatically convert a formula utilizing the downloaded data contained in the spreadsheet into a language of the multi-dimensional cube so as to provide a converted formula. The converted formula is incorporated into the multi-dimensional cube as a computed member.

Claims

1. A method of implementing spreadsheet conversion and write-back to a multi-dimensional cube, comprising:

executing a query to identify data within the multi-dimensional cube;

downloading data from the multi-dimensional cube to a spreadsheet of a spreadsheet application that is executing on a data processing system by extracting the queried data from the multi-dimensional cube;

identifying dimensions corresponding to the data downloaded from the multi-dimensional cube into the spreadsheet based upon identifying at least one of the query that generated the data extracted from the multi-dimensional cube and then parsing the query to retrieve row and/or column dimension names or by identifying an origin of the data extracted from the multi-dimensional cube and identifying dimension names based upon the identified origin;

identifying at least one formula that was created in the spreadsheet that utilizes data downloaded from the multi-dimensional cube, where each identified formula utilizes the data in a native spreadsheet format according to a spreadsheet language, which is different from a cube language of the multi-dimensional cube;

identifying members of the dimensions corresponding to data extracted from the multi-dimensional cube that are utilized in each identified formula created in the spreadsheet through correlation of the spreadsheet cells of the formula to the members of the identified dimensions corresponding to the downloaded data;

automatically transforming each identified formula created in the spreadsheet from the spreadsheet language of the spreadsheet application into the cube language of the multi-dimensional cube based on the identified members corresponding to data extracted from the multi-dimensional cube so as to provide a transformed formula based on the identification of the dimensions and members corresponding to the queried data by;

utilizing a lookup table of conversions to automatically look up and convert the spreadsheet formulas to cube language formulas; and

incorporating the identified members of the formula into the cube language formula; and

triggering a rebuild of the multi-dimensional cube so as to incorporate each transformed formula into the multi-dimensional cube as a computed member; and

generating data to populate the multi-dimensional cube based upon each transformed formula.

2. The method of claim 1, wherein:

the automatically transforming each identified formula added to the spreadsheet having identified members corresponding to data extracted from the multi-dimensional cube into a language of the multi-dimensional cube comprises;

transforming each formula into a multi-dimensional cube formula based on its identified members; and

the triggering a rebuild of the multi-dimensional cube so as to incorporate each transformed formula into the multi-dimensional cube as a computed member comprises;

rebuilding the multi-dimensional cube utilizing the generated data to incorporate the transformed formula into the multi-dimensional cube.

3. The method of claim 2, wherein rebuilding the multi-dimensional cube utilizing the generated data comprises incorporating the generated data in a meta-outline of the multi-dimensional cube.

4. The method of claim 1, wherein the identifying dimensions corresponding to the queried data extracted from the multi-dimensional cube into the spreadsheet comprises:

identifying the query that generated the data extracted from the multi-dimensional cube; and

parsing the identified query to retrieve row and/or column dimension names.

5. The method of claim 1, wherein the identifying members of the dimensions corresponding to data extracted from the multi-dimensional cube that are utilized in the creation of each identified formula created in the spreadsheet comprises:

identifying variables of the formula in the spreadsheet;

determining whether the variables are changing in a column direction or a row direction;

transforming spreadsheet locations of the identified variables to cell coordinates;

storing tables of dimension member names associated with the identified variables of the formula to provide a dimension table corresponding to each dimension of the formula;

storing a list of cell coordinates of the dimension member names of the variables utilized in the formula;

iterating over cell ranges of the spreadsheet and, for each cell iterated;

if a current cell being iterated is a data cell obtain a next cell;

if the current cell being iterated is from a row header, for each entry in the list of cell coordinates of the dimension member names of the spreadsheet elements utilized in the formula, if the current cell has cell coordinates corresponding to an entry in the list of cell coordinates, retrieving the current cell contents and adding the contents to the corresponding dimension table to provide a member name entry; and

if the current cell being iterated is from a column header, for each entry in the list of cell coordinates of the dimension member names of the spreadsheet elements utilized in the formula, if the current cell has cell coordinates corresponding to an entry in the list of cell coordinates, retrieving the current cell contents and adding the contents to the corresponding dimension table to provide a member name entry.

6. The method of claim 1, wherein the automatically transforming each identified formula added to the spreadsheet having identified members corresponding to data extracted from the multi-dimensional cube into a language of the multi-dimensional comprises:

transforming the identified members of dimensions utilized in the formula into an on-line analytical processing (OLAP) command so as to selectively compress members of the dimensions to a hierarchical function based on hierarchies of the dimensions of the multi-dimensional cube; and

transforming the formula into an OLAP command format based on the transformation of the identified members.

7. The method of claim 6, wherein the generating data to populate the multi-dimensional cube based upon each transformed formula comprises:

querying the multi-dimensional cube to determine a format of data to be populated into measure dimension tables of the multi-dimensional cube; and

generating data to be populated in the multi-dimensional cube based on the query of the multi-dimensional cube and the transformed formula in the OLAP format.

8. The method of claim 6, wherein transforming the identified members of dimensions utilized in the formula into an on-line analytical processing (OLAP) command so as to selectively compress members of the dimensions to a hierarchical function based on hierarchies of the dimensions of the multi-dimensional cube comprises:

determining if a number of members of a dimension utilized in the formula exceeds a threshold; and

compressing members of the dimension to a hierarchical function based on a hierarchy of the dimension of the multi-dimensional cube if the number of members exceeds the threshold.

9. The method of claim 8, wherein compressing members of a dimension to a hierarchical function based on a hierarchy of the dimension of the multi-dimensional cube if the number of members exceeds the threshold comprises:

retrieving from the multi-dimensional cube, dimension hierarchy information;

building lists of sets ordered by generation level based on the retrieved dimension hierarchy information;

determining intersections between a set of the lists of sets and a set corresponding to members of a dimension utilized in the formula; and

compressing a set of members to a hierarchical function corresponding to a generation level corresponding to the set of the lists of sets if a determined intersection between a set of the lists of sets and the set corresponding to members of a dimension utilized in the formula is an exact match.

10. The method of claim 9, wherein compressing members of dimensions to a hierarchical function based on a hierarchy of dimensions of the multi-dimensional cube if the number of members exceeds the threshold further comprises:

heuristically determining if the members of the set corresponding to members of a dimension utilized in the formula correspond to a first member of the set of members corresponding to members of a dimension utilized in the formula and all descendants of a generation level of the first member, all descendants of a generation level of the first member, all siblings at generation level including the first member, the first member and ancestors of the first member, all children of a generation level including the first member, the first member and all children of a generation level including the first member, the first member and ancestors of the first member and the first member and all descendants of the first member; and

compressing the members of the set corresponding to members of a dimension utilized in the formula to a hierarchical function based on the heuristic determination of a generational relationship between the members of the set corresponding to members of a dimension utilized in the formula and the hierarchy of the dimension.