Methods, systems and computer program products for incorporating spreadsheet formulas of multi-dimensional cube data into a multi-dimentional cube

• US 20040237029A1
• Filed: 05/22/2003
• Published: 11/25/2004
• Est. Priority Date: 05/22/2003
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Medicke, John A., Rutledge, Stephen H., Russell, Feng-Wei Chen
• Granted Patent: US 7,530,012 B2
• Time in Patent Office: Days
• US Class Current: 715/503
• 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 generating computations included in analytics of a multi-dimensional cube, comprising:

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; and

incorporating the converted formula into the multi-dimensional cube as a computed member.

2. The method of claim 1, wherein analyzing a spreadsheet comprises:

identifying dimensions of the multi-dimensional cube corresponding to the data downloaded from the multi-dimensional cube;

identifying members of the dimensions utilized in the formula in the spreadsheet;

transforming the formula into a multi-dimensional cube formula based on the identified members;

generating data to be populated in the multi-dimensional cube based on the transformed formula; and

wherein incorporating the converted formula comprises rebuilding the multi-dimensional cube utilizing the generated data to incorporate the converted 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 2, wherein identifying dimensions of the multi-dimensional cube comprises:

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

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

5. The method of claim 4, wherein identifying members of the dimensions utilized in the formula 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 2, wherein transforming the formula 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 generating data to be populated in the multi-dimensional cube based on the 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.

11. A system for generating computations included in analytics of a multi-dimensional cube, comprising:

means for 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; and

means for incorporating the converted formula into the multi-dimensional cube as a computed member.

12. The system of claim 11, wherein the means for analyzing a spreadsheet comprises:

means for identifying dimensions of the multi-dimensional cube corresponding to the data downloaded from the multi-dimensional cube;

means for identifying members of the dimensions utilized in the formula;

means for transforming the formula into a multi-dimensional cube formula based on the identified members;

means for generating data to be populated in the multi-dimensional cube based on the transformed formula; and

wherein the means for incorporating the converted formula comprises means for rebuilding the multi-dimensional cube utilizing the generated data to incorporate the converted formula into the multi-dimensional cube.

13. The system of claim 12, wherein the means for rebuilding the multi-dimensional cube utilizing the generated data comprises means for incorporating the generated data in a meta-outline of the multi-dimensional cube.

14. The system of claim 12, wherein the means for identifying dimensions of the multi-dimensional cube comprises:

means for identifying a query that generated the data downloaded from the multi-dimensional cube; and

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

15. The system of claim 14, wherein the means for identifying members of the dimensions utilized in the formula comprises:

means for identifying variables of the formula in the spreadsheet;

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

means for transforming spreadsheet locations of the identified variables to cell coordinates;

means for 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;

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

means for 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.

16. The system of claim 12, wherein the means for transforming the formula comprises:

means for 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

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

17. The system of claim 16, wherein the means for generating data to be populated in the multi-dimensional cube based on the transformed formula comprises:

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

means for 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.

18. The system of claim 16, wherein the means for 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:

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

means for 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.

19. The system of claim 18, wherein the means for 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:

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

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

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

means for 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.

20. The system of claim 19, wherein the means for 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 further comprises:

means for 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 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

means for 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.

21. A computer program product for generating computations included in a multi-dimensional cube analytics, comprising:

a computer readable medium having computer readable program code embodied therein, the computer readable program code comprising;

computer readable program code configured to analyze 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; and

computer readable program code configured to incorporate the converted formula into the multi-dimensional cube as a computed member.

22. The computer program product of claim 21, wherein computer readable program code configured to analyze a spreadsheet comprises:

computer readable program code configured to identify dimensions of the multi-dimensional cube corresponding to the data downloaded from the multi-dimensional cube;

computer readable program code configured to identify members of the dimensions utilized in the formula;

computer readable program code configured to transform the formula into a multi-dimensional cube formula based on the identified members;

computer readable program code configured to generate data to be populated in the multi-dimensional cube based on the transformed formula; and

wherein computer readable program code configured to incorporate the converted formula comprises computer readable program code configured to rebuild the multi-dimensional cube utilizing the generated data to incorporate the converted formula into the multi-dimensional cube.

23. The computer program product of claim 22, wherein computer readable program code configured to rebuild the multi-dimensional cube utilizing the generated data comprises computer readable program code configured to incorporate the generated data in a meta-outline of the multi-dimensional cube.

24. The computer program product of claim 22, wherein computer readable program code configured to identify dimensions of the multi-dimensional cube comprises:

computer readable program code configured to identify a query that generated the data downloaded from the multi-dimensional cube; and

computer readable program code configured to parse the query to retrieve row and/or column dimension names.

25. The computer program product of claim 24, wherein computer readable program code configured to identify members of the dimensions utilized in the formula comprises:

computer readable program code configured to identify variables of the formula in the spreadsheet;

computer readable program code configured to determine whether the variables are changing in a column direction or a row direction;

computer readable program code configured to transform spreadsheet locations of the identified variables to cell coordinates;

computer readable program code configured to store 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;

computer readable program code configured to store a list of cell coordinates of the dimension member names of the variables utilized in the formula;

computer readable program code configured to iterate 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.

26. The computer program product of claim 22, wherein computer readable program code configured to transform the formula comprises:

computer readable program code configured to transform 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

computer readable program code configured to transform the formula into an OLAP command format based on the transformation of the identified members.

27. The computer program product of claim 26, wherein computer readable program code configured to generate data to be populated in the multi-dimensional cube based on the transformed formula comprises:

computer readable program code configured to query the multi-dimensional cube to determine a format of data to be populated into measure dimension tables of the multi-dimensional cube; and

computer readable program code configured to generate 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 command format.

28. The computer program product of claim 26, wherein computer readable program code configured to transform 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:

computer readable program code configured to determine if a number of members of a dimension utilized in the formula exceeds a threshold; and

computer readable program code configured to compress 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.

29. The computer program product of claim 28, wherein computer readable program code configured to compress 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:

computer readable program code configured to retrieve from the multi-dimensional cube, dimension hierarchy information;

computer readable program code configured to build lists of sets ordered by generation level based on the retrieved dimension hierarchy information;

computer readable program code configured to determine intersections between a set of the lists of sets and a set corresponding to members of a dimension utilized in the formula; and

computer readable program code configured to compress 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.

30. The computer program product of claim 29, wherein the computer readable program code configured to 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 further comprises:

computer readable program code configured to heuristically determine 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 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

computer readable program code configured to compress 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.