Calculation engine for use in OLAP environments

• US 7,062,479 B2
• Filed: 11/02/2001
• Issued: 06/13/2006
• Est. Priority Date: 11/02/2001
• Status: Expired due to Term

• Current Assignee: SAP SE
• Original Assignee: Cognos Incorporated (International Business Machines Corporation)
• Inventors: Minns, Robert, Edmunds, David Walter, Sinclair, James Wallace
• Primary Examiner(s): Ali, Mohammad
• Assistant Examiner(s): Nguyen, Cindy
• Application Number:
  US10/016,262
• Publication Number:
  US 20030088540A1
• Time in Patent Office: 1,684 Days
• Field of Search: 707/1, 707/100, 707/4, 707/6
• US Class Current: 707/603
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06Q 10/06 Resources, workflows, human or project management; Enterprise or organisation planning; Enterprise or organisation modelling
• CPC Class Codes: Y10S 707/958 Data cubes
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99936 Pattern matching access

Abstract

This invention addresses the need for dealing with complex planning calculations based on data warehouse or Planning Data Repository (PDR) data where some aggregated data or forecast data might be changed without directly manipulating the underlying data, and where there may be several relationships linking the data. The system is able to deal with complex relationships along more than one axis or dimension. A number of iterations are typically used involving both back-solving and ‘forward-solving’. The subset of cells that needs to be recalculated is identified before steps of back-solving and/or forward-solving using parent/child tables. The scanning of these tables looking for potential dependencies is much simpler and faster than to looking at the actual formulae or functions relating the cells. The step of creating the parent/child tables is carried out in advance of the actual calculation by parsing all the relationships (formulae and functions) and summarising the dependencies between cells in the parent/child tables.

Claims

1. A method for calculating and back-solving of complex relationships in a planning data repository system comprising the steps of:

a) generating a sub-cube from the planning data repository system using a Calculation Engine, said planning data repository system residing separately from the Calculation Engine;

b) inputting from a user for at least one specified cell within the sub-cube, a required value, or a required value and one or more constraints, the required value and the constraints being taken into account by the Calculation Engine in calculations;

c) where more than one calculation may affect a cell within the sub-cube, selecting a calculation for the affected cell in accordance with a set of prioritisation rules;

d) creating one or more parent/child tables giving the relationships and dependencies between one or more target cells and other cells in the sub-cube, using the Calculation Engine;

e) determining from the one or more parent/child tables the one or more target cells, each requiring a back-solving calculation to set a value of the specified cell to the required value;

f) for each target cell;

i) performing the back-solving calculation for the target cell using the Calculation Engine and recording that a change has taken place;

ii) remembering the one or more parent cells of the target cell to ensure they are recalculated;

iii) recalculating the value for each remembered parent cell using the Calculation Engine and recording that a change has taken place;

iv) repeating the i) performing, ii) remembering, and iii) recalculating steps until all changes to the target cells and their parent cells are complete;

v) repeating the i) performing, ii) remembering, iii) recalculating, and iv) first repeating steps until no changes are recorded; and

g) reporting the results of the foregoing steps to the user.

2. The method of claim 1 wherein the parent/child table creating step d) is carried out as part of the performing the back-solving calculation step i), thereby reducing a number of access times to access a particular cell in smaller, less complex sub-cubes.

5. The method of claim 1 wherein the step c) includes the step of accepting from a user an adjustment to the prioritisation rules.

6. The method of claim 1 including after step c) the step of:

c1) checking the sub cube for consistency.

3. A system for calculating and back-solving complex relationships in a planning data repository system comprising:

a) means for generating a sub-cube from the planning data repository system using a Calculation Engine, said planning data repository system residing separately from the Calculation Engine;

b) means for inputting from a user for at least one specified cell within the sub-cube, a required value, or a required value and one or more constraints, the required value and the constraints being taken into account by the Calculation Engine in calculations;

c) means for selecting a calculation for an affected cell within the sub-cube in accordance with a set of prioritisation rules;

d) means for creating one or more parent/child tables giving the relationships and dependencies between one or more target cells and other cells in the sub-cube, using the Calculation Engine;

e) means for determining from the one or more parent/child tables the one or more target cells, each target cell requiring a back-solving calculation to set a value of the specified cell to the required value;

f) means for implementing the back-solving calculation for each target cell, including;

i) means for performing the back-solving calculation for the target cell using the Calculation Engine and recording that a change has taken place;

ii) means for remembering the one or more parent cells of said target cell to ensure they are recalculated;

iii) means for recalculating the value for each remembered parent cell using the Calculation Engine and recording that a change has taken place; and

g) means for reporting the results of the foregoing means to the user.

4. A computer program product for calculating and back-solving complex relationships in a planning data repository system the computer program product comprising a computer-readable storage medium having computer-readable program code means embodied in it, said computer readable program code means comprising:

a) computer readable program code means for generating a sub-cube from the planning data repository system using a Calculation Engine, said planning data repository system residing separately from the Calculation Engine;

b) computer readable program code means for inputting from a user for at least one specified cell within the sub-cube, a required value, or a required value and one or more constraints, the required value and the constraints being taken into account by the Calculation Engine in calculations;

c) computer readable program code means for selecting a calculation for an affected cell within the sub-cube in accordance with a set of prioritisation rules;

d) computer readable program code means for creating one or more parent/child tables giving the relationships and dependencies between one or more target cells and other cells in the sub-cube, using the Calculation Engine;

e) computer readable program code means for determining from the one or more parent/child tables the one or more target cells each target cell requiring a back-solving calculation to set a value of the specified cell to the required value;

f) computer readable program code means for implementing the back-solving calculation for each target cell, including;

i) computer readable program code means for performing the back-solving calculation for the target cell using the Calculation Engine and recording that a change has taken place;

ii) computer readable program code means for remembering the one or more parent cells of said target cell to ensure they are recalculated;

iii) computer readable program code means for recalculating the value for each remembered parent cell using the Calculation Engine and recording that a change has taken place; and

g) computer readable program code means for reporting the results of the foregoing means to the user.