CA2361242A1 - A calculation engine for use in olap environments - Google Patents
A calculation engine for use in olap environments Download PDFInfo
- Publication number
- CA2361242A1 CA2361242A1 CA002361242A CA2361242A CA2361242A1 CA 2361242 A1 CA2361242 A1 CA 2361242A1 CA 002361242 A CA002361242 A CA 002361242A CA 2361242 A CA2361242 A CA 2361242A CA 2361242 A1 CA2361242 A1 CA 2361242A1
- Authority
- CA
- Canada
- Prior art keywords
- parent
- cells
- program code
- readable program
- computer readable
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000004364 calculation method Methods 0.000 title claims abstract 14
- 238000004590 computer program Methods 0.000 claims 2
- 238000000034 method Methods 0.000 claims 2
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q10/00—Administration; Management
- G06Q10/06—Resources, workflows, human or project management; Enterprise or organisation planning; Enterprise or organisation modelling
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/177—Editing, e.g. inserting or deleting of tables; using ruled lines
- G06F40/18—Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/953—Organization of data
- Y10S707/958—Data cubes
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
- Y10S707/99933—Query processing, i.e. searching
- Y10S707/99934—Query formulation, input preparation, or translation
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
- Y10S707/99933—Query processing, i.e. searching
- Y10S707/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 aggregat ed data or forecast data might be changed without directly manipulating the underlyi ng data, and where there may be several relationships linking the data. The system is abl e 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 potentia l dependencies is much simpler and faster than to looking at the actual formulae or functio ns relating the cells. The step of creating the parent/child tables is carried out in advanc e of the actual calculation by parsing all the relationships (formulae and functions) and summarising the dependencies between cells in the parent/child tables.
Claims (4)
1. A method for the calculation and back-solving of complex relationships in a sub-cube of a multidimensional database system comprising the steps of:
- inputting from a user the required values of specified cells and any constraints;
- creating one or more parent/child tables giving the relationships and dependencies between target cells and other cells in the sub-cube;
- determining from the one or more parent/child tables the one or more target cells requiring one or more calculations to be performed;
- for each target cell requiring calculations:
~ performing the one or more calculations and recording that a change has taken place;
~ remembering the one or more parent cells of the target cell to ensure they are recalculated;
~ recalculating the values for each remembered parent cell and recording that a change has taken place;
- repeating the performing, remembering, and recalculating steps until all changes to the target cells and their parent cells are complete;
- repeating the performing, remembering, recalculating, and first repeating steps until no changes are recorded; and - reporting the results of the foregoing steps to the user.
- inputting from a user the required values of specified cells and any constraints;
- creating one or more parent/child tables giving the relationships and dependencies between target cells and other cells in the sub-cube;
- determining from the one or more parent/child tables the one or more target cells requiring one or more calculations to be performed;
- for each target cell requiring calculations:
~ performing the one or more calculations and recording that a change has taken place;
~ remembering the one or more parent cells of the target cell to ensure they are recalculated;
~ recalculating the values for each remembered parent cell and recording that a change has taken place;
- repeating the performing, remembering, and recalculating steps until all changes to the target cells and their parent cells are complete;
- repeating the performing, remembering, recalculating, and first repeating steps until no changes are recorded; and - reporting the results of the foregoing steps to the user.
2. The method of claim 1 wherein the Parent/Child table creating step is carried out as part of the performing one or more calculations step, so that in smaller, less complex sub-cubes, there is some advantage taken in the reduced number of times a particular cell is accessed.
3. A system for calculating and back-solving complex relationships in a sub-cube of a multi-dimensional database system comprising:
- means for inputting from a user the required values of specified cells and any constraints;
- means for creating one or more parent/child tables giving the relationships and dependencies between target cells and other cells in the sub-cube;
- means for determining from the one or more parent/child tables the one or more target cells requiring one or more calculations to be performed;
- for each target cell requiring calculations:
- means for performing the one or more calculations and recording that a change has taken place;
- means for remembering the one or more parent cells of the target cell to ensure they are recalculated;
- means for recalculating the values for each remembered parent cell and recording that a change has taken place;
- wherein the means for performing, the means for remembering, and the means for recalculating are operable until all changes to the target cells and their parent cells are complete;
- and wherein the means for performing, the means for remembering, and the means for recalculating, are operable until no changes are recorded; and - means for reporting the results of the forgoing means to the user.
- means for inputting from a user the required values of specified cells and any constraints;
- means for creating one or more parent/child tables giving the relationships and dependencies between target cells and other cells in the sub-cube;
- means for determining from the one or more parent/child tables the one or more target cells requiring one or more calculations to be performed;
- for each target cell requiring calculations:
- means for performing the one or more calculations and recording that a change has taken place;
- means for remembering the one or more parent cells of the target cell to ensure they are recalculated;
- means for recalculating the values for each remembered parent cell and recording that a change has taken place;
- wherein the means for performing, the means for remembering, and the means for recalculating are operable until all changes to the target cells and their parent cells are complete;
- and wherein the means for performing, the means for remembering, and the means for recalculating, are operable until no changes are recorded; and - means for reporting the results of the forgoing means to the user.
4. A computer program product for calculating and back-solving complex relationships in a sub-cube of a multi-dimensional database 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:
- computer readable program code means for inputting from a user the required values of specified cells and any constraints;
- computer readable program code means for creating one or more parent/child tables giving the relationships and dependencies between target cells and other cells in the sub-cube;
- computer readable program code means for determining from the one or more parent/child tables the one or more target cells requiring one or more calculations to be performed;
- for each target cell requiring calculations:
- computer readable program code means for performing the one or more calculations and recording that a change has taken place;
- computer readable program code means for remembering the one or more parent cells of the target cell to ensure they are recalculated;
- computer readable program code means for recalculating the values for each remembered parent cell and recording that a change has taken place;
- wherein the computer readable program code means for performing, the computer readable program code means for remembering, and the computer readable program code means for recalculating are operable until all changes to the target cells and their parent cells are complete;
- and wherein the computer readable program code means for performing, the computer readable program code means for remembering, and the computer readable program code means for recalculating, are further operable until no changes are recorded; and - computer readable program code means for reporting the results of the foregoing computer readable program code means to the user.
- a computer-readable storage medium having computer-readable program code means embodied in it, said computer readable program code means comprising:
- computer readable program code means for inputting from a user the required values of specified cells and any constraints;
- computer readable program code means for creating one or more parent/child tables giving the relationships and dependencies between target cells and other cells in the sub-cube;
- computer readable program code means for determining from the one or more parent/child tables the one or more target cells requiring one or more calculations to be performed;
- for each target cell requiring calculations:
- computer readable program code means for performing the one or more calculations and recording that a change has taken place;
- computer readable program code means for remembering the one or more parent cells of the target cell to ensure they are recalculated;
- computer readable program code means for recalculating the values for each remembered parent cell and recording that a change has taken place;
- wherein the computer readable program code means for performing, the computer readable program code means for remembering, and the computer readable program code means for recalculating are operable until all changes to the target cells and their parent cells are complete;
- and wherein the computer readable program code means for performing, the computer readable program code means for remembering, and the computer readable program code means for recalculating, are further operable until no changes are recorded; and - computer readable program code means for reporting the results of the foregoing computer readable program code means to the user.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA2361242A CA2361242C (en) | 2001-11-02 | 2001-11-02 | A calculation engine for use in olap environments |
| US10/016,262 US7062479B2 (en) | 2001-11-02 | 2001-11-02 | Calculation engine for use in OLAP environments |
Applications Claiming Priority (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA2361242A CA2361242C (en) | 2001-11-02 | 2001-11-02 | A calculation engine for use in olap environments |
| US10/016,262 US7062479B2 (en) | 2001-11-02 | 2001-11-02 | Calculation engine for use in OLAP environments |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CA2361242A1 true CA2361242A1 (en) | 2003-05-02 |
| CA2361242C CA2361242C (en) | 2010-11-30 |
Family
ID=27623541
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CA2361242A Expired - Fee Related CA2361242C (en) | 2001-11-02 | 2001-11-02 | A calculation engine for use in olap environments |
Country Status (2)
| Country | Link |
|---|---|
| US (1) | US7062479B2 (en) |
| CA (1) | CA2361242C (en) |
Families Citing this family (43)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20040015381A1 (en) * | 2002-01-09 | 2004-01-22 | Johnson Christopher D. | Digital cockpit |
| US8868544B2 (en) * | 2002-04-26 | 2014-10-21 | Oracle International Corporation | Using relational structures to create and support a cube within a relational database system |
| US7415457B2 (en) * | 2002-04-26 | 2008-08-19 | Oracle International Corporation | Using a cache to provide cursor isolation |
| US8001112B2 (en) * | 2002-05-10 | 2011-08-16 | Oracle International Corporation | Using multidimensional access as surrogate for run-time hash table |
| US20040138932A1 (en) * | 2003-01-09 | 2004-07-15 | Johnson Christopher D. | Generating business analysis results in advance of a request for the results |
| US20040138936A1 (en) * | 2003-01-09 | 2004-07-15 | Johnson Christopher D. | Performing what-if forecasts using a business information and decisioning control system |
| US20040138934A1 (en) * | 2003-01-09 | 2004-07-15 | General Electric Company | Controlling a business using a business information and decisioning control system |
| US20060111931A1 (en) * | 2003-01-09 | 2006-05-25 | General Electric Company | Method for the use of and interaction with business system transfer functions |
| US20040138935A1 (en) * | 2003-01-09 | 2004-07-15 | Johnson Christopher D. | Visualizing business analysis results |
| US20060106637A1 (en) * | 2003-01-09 | 2006-05-18 | General Electric Company | Business system decisioning framework |
| US7676390B2 (en) | 2003-09-04 | 2010-03-09 | General Electric Company | Techniques for performing business analysis based on incomplete and/or stage-based data |
| JP2007535764A (en) * | 2004-04-26 | 2007-12-06 | ライト９０，インコーポレイテッド | Real-time data prediction |
| US7647356B2 (en) * | 2004-05-07 | 2010-01-12 | Oracle International Corporation | Methods and apparatus for facilitating analysis of large data sets |
| US8904273B2 (en) * | 2004-07-02 | 2014-12-02 | International Business Machines Corporation | System and method of format specification |
| US7818282B2 (en) * | 2004-07-02 | 2010-10-19 | International Business Machines Corporation | System and method for the support of multilingual applications |
| US20060005112A1 (en) * | 2004-07-02 | 2006-01-05 | David Lilly | System and method of report layout |
| US7333995B2 (en) * | 2004-07-02 | 2008-02-19 | Cognos, Incorporated | Very large dataset representation system and method |
| US7937401B2 (en) * | 2004-07-09 | 2011-05-03 | Microsoft Corporation | Multidimensional database query extension systems and methods |
| US7844570B2 (en) * | 2004-07-09 | 2010-11-30 | Microsoft Corporation | Database generation systems and methods |
| US7490106B2 (en) * | 2004-07-09 | 2009-02-10 | Microsoft Corporation | Multidimensional database subcubes |
| US7694278B2 (en) * | 2004-07-09 | 2010-04-06 | Microsoft Corporation | Data cube script development and debugging systems and methodologies |
| US20060010058A1 (en) * | 2004-07-09 | 2006-01-12 | Microsoft Corporation | Multidimensional database currency conversion systems and methods |
| US7716253B2 (en) * | 2004-07-09 | 2010-05-11 | Microsoft Corporation | Centralized KPI framework systems and methods |
| US20060020608A1 (en) * | 2004-07-09 | 2006-01-26 | Microsoft Corporation | Cube update tool |
| US20060085386A1 (en) * | 2004-10-19 | 2006-04-20 | Microsoft Corporation | Two pass calculation to optimize formula calculations for a spreadsheet |
| US7571192B2 (en) * | 2005-06-15 | 2009-08-04 | Oracle International Corporation | Methods and apparatus for maintaining consistency during analysis of large data sets |
| US20070156427A1 (en) * | 2005-12-30 | 2007-07-05 | Ralf Dentzer | Recalculation as a function of changed data |
| US7933791B2 (en) * | 2006-09-07 | 2011-04-26 | International Business Machines Corporation | Enterprise performance management software system having variable-based modeling |
| US7788200B2 (en) * | 2007-02-02 | 2010-08-31 | Microsoft Corporation | Goal seeking using predictive analytics |
| US20090044089A1 (en) * | 2007-08-06 | 2009-02-12 | Apple Inc. | Automatic insertion of a default function |
| US8229923B2 (en) * | 2008-09-26 | 2012-07-24 | Microsoft Corporation | Multidimensional data space queries with expression evaluation |
| US20100250310A1 (en) * | 2009-03-30 | 2010-09-30 | Michael Locherer | Monitoring organizational information for fast decision making |
| US9009709B2 (en) * | 2010-03-16 | 2015-04-14 | Salesforce.Com, Inc. | Asynchronous rollup numbers forecasting methods and systems |
| US8909680B1 (en) | 2011-10-08 | 2014-12-09 | Bay Dynamics | Intelligent caching of multidimensional cube data |
| US9330091B1 (en) | 2011-10-08 | 2016-05-03 | Bay Dynamics, Inc. | System for managing data storages |
| US9081830B1 (en) | 2011-10-08 | 2015-07-14 | Bay Dynamics | Updating a view of a multidimensional cube |
| US10353922B1 (en) | 2011-10-08 | 2019-07-16 | Bay Dynamics, Inc. | Rendering multidimensional cube data |
| US9390082B1 (en) * | 2011-10-08 | 2016-07-12 | Bay Dynamics, Inc. | Generating multiple views of a multidimensional cube |
| US20140089042A1 (en) * | 2012-09-27 | 2014-03-27 | Amadeus S.A.S. | Method, Apparatus and System for monitoring competition price and for providing corrective messages |
| US20140149360A1 (en) * | 2012-11-27 | 2014-05-29 | Sap Ag | Usage of Filters for Database-Level Implementation of Constraints |
| US10628418B2 (en) * | 2014-11-13 | 2020-04-21 | Sap Se | Data driven multi-provider pruning for query execution plan |
| US9286286B1 (en) * | 2015-01-03 | 2016-03-15 | Chahid Kamel Ghaddar | Method, apparatus, and computer program product for optimizing parameterized models using functional paradigm of spreadsheet software |
| US9898440B2 (en) | 2015-02-25 | 2018-02-20 | Sap Se | Calculation framework utilizing fragmentation of expressions |
Family Cites Families (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JPH06101008B2 (en) | 1988-09-09 | 1994-12-12 | インターナショナル・ビジネス・マシーンズ・コーポレーション | Spreadsheet processor |
| US5276607A (en) * | 1990-03-28 | 1994-01-04 | Wordperfect Corporation | Method for optimal recalculation |
| WO1997021176A1 (en) | 1995-12-06 | 1997-06-12 | Henrik Kjaer | A multidimensional electronic spreadsheet system and method |
| US5897629A (en) * | 1996-05-29 | 1999-04-27 | Fujitsu Limited | Apparatus for solving optimization problems and delivery planning system |
| US5978796A (en) * | 1997-06-30 | 1999-11-02 | International Business Machines Corporation | Accessing multi-dimensional data by mapping dense data blocks to rows in a relational database |
| US5918232A (en) * | 1997-11-26 | 1999-06-29 | Whitelight Systems, Inc. | Multidimensional domain modeling method and system |
| WO2000005659A1 (en) | 1998-07-24 | 2000-02-03 | Omni-Vista, Inc. | Analysis package and method |
| US6385604B1 (en) * | 1999-08-04 | 2002-05-07 | Hyperroll, Israel Limited | Relational database management system having integrated non-relational multi-dimensional data store of aggregated data elements |
| FR2806183B1 (en) * | 1999-12-01 | 2006-09-01 | Cartesis S A | DEVICE AND METHOD FOR INSTANT CONSOLIDATION, ENRICHMENT AND "REPORTING" OR BACKGROUND OF INFORMATION IN A MULTIDIMENSIONAL DATABASE |
| US6434557B1 (en) * | 1999-12-30 | 2002-08-13 | Decode Genetics Ehf. | Online syntheses programming technique |
| US6941311B2 (en) * | 2000-04-27 | 2005-09-06 | Hyperion Solutions Corporation | Aggregate navigation system |
| US6622137B1 (en) * | 2000-08-14 | 2003-09-16 | Formula Telecom Solutions Ltd. | System and method for business decision implementation in a billing environment using decision operation trees |
| US6651055B1 (en) * | 2001-03-01 | 2003-11-18 | Lawson Software, Inc. | OLAP query generation engine |
-
2001
- 2001-11-02 CA CA2361242A patent/CA2361242C/en not_active Expired - Fee Related
- 2001-11-02 US US10/016,262 patent/US7062479B2/en not_active Expired - Lifetime
Also Published As
| Publication number | Publication date |
|---|---|
| US20030088540A1 (en) | 2003-05-08 |
| US7062479B2 (en) | 2006-06-13 |
| CA2361242C (en) | 2010-11-30 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CA2361242A1 (en) | A calculation engine for use in olap environments | |
| Agarwal | Range searching | |
| Yang et al. | Algorithms for materialized view design in data warehousing environment | |
| US8868544B2 (en) | Using relational structures to create and support a cube within a relational database system | |
| US7171427B2 (en) | Methods of navigating a cube that is implemented as a relational object | |
| CA2415183C (en) | Performing spreadsheet-like calculations in a database system | |
| Stonebraker et al. | One size fits all? Part 2: Benchmarking results | |
| KR100656528B1 (en) | Dynamic Update Cube and Hybrid Queries Search Method for Range-Sum Queries | |
| US8065262B2 (en) | Computer-implemented multidimensional database processing method and system | |
| Shin et al. | Denormalization strategies for data retrieval from data warehouses | |
| US20060010147A1 (en) | Method and system for online analytical processing (olap) | |
| Aouiche et al. | Automatic selection of bitmap join indexes in data warehouses | |
| EP3101556B1 (en) | Indexing dynamic hierarchical data | |
| Lin et al. | A fast association rule algorithm based on bitmap and granular computing | |
| Ordonez | Optimizing recursive queries in SQL | |
| JP2004164592A (en) | System and method for analytically modeling data organized according to related attribute | |
| Henrich | P-OQL: an OQL-oriented query language for PCTE | |
| KR20050001469A (en) | System and method for expressing and calculating a relationship between measures | |
| US20220012242A1 (en) | Hierarchical datacube query plan generation | |
| Xin et al. | P-cube: Answering preference queries in multi-dimensional space | |
| Lai et al. | Approximate colored range and point enclosure queries | |
| Agarwal et al. | I/O-efficient structures for orthogonal range-max and stabbing-max queries | |
| May et al. | Index vs. navigation in XPath evaluation | |
| Bizarro et al. | Adding a performance-oriented perspective to data warehouse design | |
| Olken et al. | Random sampling from databases-a survey |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| EEER | Examination request | ||
| MKLA | Lapsed | ||
| MKLA | Lapsed |
Effective date: 20121102 |