Enhanced mechanisms for managing multidimensional data

• US 9,817,876 B2
• Filed: 06/29/2015
• Issued: 11/14/2017
• Est. Priority Date: 06/29/2015
• Status: Active Grant

• Current Assignee: Planisware SAS
• Original Assignee: Planisware SAS
• Inventors: Demonsant, Pierre, Gautier, Mathieu, Roche, Thibault, Henriquez, Baptiste
• Primary Examiner(s): CHEUNG, HUBERT G
• Application Number:
  US14/754,442
• Publication Number:
  US 20160378842A1
• Time in Patent Office: 869 Days
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/275 Synchronous replication
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

An enhanced multidimensional data management system significantly reduces the resources required to convert a spreadsheet into an enterprise collaborative system. A spreadsheet may be imported as is without modifications. Additional information that cannot be automatically inferred about the data model used in the spreadsheet is obtained. Dimensions, timescale, and measure data are clearly defined and located. The spreadsheet is kept in the system in the form of both a spreadsheet and as a multidimensional data structure such as an online analytical processing (OLAP) cube or structure. This duality allows the spreadsheet to be manipulated as if it was an OLAP cube, modifying iterations within the dimensions, and the OLAP cube to be computed as if it was a spreadsheet.

Claims

1. A method comprising:

identifying a structure in a conventional spreadsheet based on cell formula and cell location during import of the conventional spreadsheet, the structure in the conventional spreadsheet including a set of cells, wherein the conventional spreadsheet is imported as in without modification;

bi-directionally mapping the set of cells to iterations and aggregations on a multidimensional data structure, wherein the mapping associates ranges and objects from the multidimensional data structure, wherein the multidimensional data structure is an online analytical processing (OLAP) structure;

identifying patterns based on cell relationships and OLAP mapping;

maintaining a duality and bi-directional mapping between the conventional spreadsheet and the OLAP structure and avoiding formula translations, wherein maintaining the duality and bi-directional mapping includes performing calculations using an embedded spreadsheet calculus engine such that no formula translations are necessary, wherein the duality and bidirectional mapping allows the spreadsheet to be manipulated as if it was an OLAP cube, modifying iterations within the dimensions, and the OLAP cube to be computed as if it was a spreadsheet;

generating an enhanced collaborative spreadsheet using the OLAP structure via an enhanced multidimensional data management system, wherein the enhanced multidimensional data management system is configured to handle a variety of spreadsheets not initially designed with the objective of potential enhancements and modifications;

receiving modifications to the enhanced collaborative spreadsheet and the corresponding OLAP structure, wherein modifications to the conventional spreadsheet are made to match the data modified in the OLAP structure, wherein modifications include inserting or deleting lines and rows to allow for automatic update of the OLAP structure and the enhanced collaborative spreadsheet; and

versioning all scenarios of modifications, wherein each cell in the set of cells includes a versioning mechanism such that individual cells from multiple versions of a same spreadsheet file can be synchronized and manipulated simultaneously, and wherein versioning is not natively supported inside the conventional spreadsheet.

2. The method of claim 1, wherein modifying iterations to dimensions/measures/timescale in an OLAP structure modifies rows/columns in the enhanced collaborative spreadsheet along with corresponding formulas.

3. The method of claim 1, wherein enterprise wide spreadsheets can be merged into an OLAP structure according to an existing spreadsheet.

4. The method of claim 1, further comprising receiving modifications to the conventional spreadsheet, wherein modifications to the conventional spreadsheet are propagated as modifications to the OLAP structure.

5. The method of claim 1, further comprising maintaining a bidirectional mapping between the enhanced collaborative spreadsheet and the OLAP structure.

6. The method of claim 1, further comprising updating the OLAP structure during re-import of the conventional spreadsheet.

7. The method of claim 1, wherein a plurality of conventional spreadsheets are modified at a plurality of client devices.

8. The method of claim 1, wherein the OLAP structure is maintained at a server.

9. The method of claim 1, wherein a server side spreadsheet engine is used to evaluate formulas.

10. The method of claim 1, wherein the enhanced collaborative spreadsheet is compatible with OpenXML.

11. The method of claim 1, wherein dimension, measure, and timescale information are received during import of the conventional spreadsheet.

12. The method of claim 11, wherein dimension, measure, and timescale information are used to generate the OLAP structure.

13. The method of claim 1, wherein the OLAP structure is an OLAP cube.

14. A non-transitory computer readable medium comprising:

computer code for identifying a structure in a conventional spreadsheet based on cell formula and cell location during import of the conventional spreadsheet, the structure in the conventional spreadsheet including a set of cells, wherein the conventional spreadsheet is imported as is without modification;

computer code for mapping the set of cells to iterations and aggregations on a multidimensional data structure, wherein the multidimensional data structure is an online analytical processing (OLAP) structure;

computer code for identifying patterns based on cell relationships and OLAP mapping;

computer code for maintaining a duality and bi-directional mapping between the conventional spreadsheet and the OLAP structure and avoiding formula translations, wherein maintaining the duality and bi-directional mapping includes performing calculations using an embedded spreadsheet calculus engine such that no formula translations are necessary, wherein the duality and bidirectional mapping allows the spreadsheet to be manipulated as if it was an OLAP cube, modifying iterations within the dimensions, and the OLAP cube to be computed as if it was a spreadsheet;

computer code for generating an enhanced collaborative spreadsheet using the OLAP structure via an enhanced multidimensional data management system, wherein the enhanced multidimensional data management system is configured to handle a variety of spreadsheets not initially designed with the objective of potential enhancements and modifications;

computer code for receiving modifications to the enhanced collaborative spreadsheet and the corresponding OLAP structure, wherein modifications to the conventional spreadsheet are made to match the data modified in the OLAP structure, wherein modifications include inserting or deleting lines and rows to allow for automatic update of the OLAP structure and the enhanced collaborative spreadsheet; and

computer code for versioning all scenarios of modifications, wherein each cell in the set of cells includes a versioning mechanism such that individual cells from multiple versions of a same spreadsheet file can be synchronized and manipulated simultaneously, and wherein versioning is not natively supported inside the conventional spreadsheet.

15. The non-transitory computer readable medium of claim 14, wherein modifying iterations to dimensions/measures/timescale in an OLAP structure modifies rows/columns in the enhanced collaborative spreadsheet along with corresponding formulas.

16. The non-transitory computer readable medium of claim 14, wherein enterprise wide spreadsheets can be merged into an OLAP structure according to an existing spreadsheet.

17. The non-transitory computer readable medium of claim 14, further comprising receiving modifications to the conventional spreadsheet, wherein modifications to the conventional spreadsheet are propagated as modifications to the OLAP structure.

18. A system comprising:

a processor; and

memory, wherein the memory includes instructions for causing the processor to execute a method, the method comprising;

identifying a structure in a conventional spreadsheet based on cell formula and cell location during import of the conventional spreadsheet, the structure in the conventional spreadsheet including a set of cells, wherein the conventional spreadsheet is imported as is without modification;

mapping the set of cells to iterations and aggregations on a multidimensional data structure, wherein the multidimensional data structure is an online analytical processing (OLAP) structure;

identifying patterns based on cell relationships and OLAP mapping;

maintaining a duality and bi-directional mapping between the conventional spreadsheet and the OLAP structure and avoiding formula translations, wherein maintaining the duality and bi-directional mapping includes performing calculations using an embedded spreadsheet calculus engine such that no formula translations are necessary, wherein the duality and bidirectional mapping allows the spreadsheet to be manipulated as if it was an OLAP cube, modifying iterations within the dimensions, and the OLAP cube to be computed as if it was a spreadsheet;

generating an enhanced collaborative spreadsheet using the OLAP structure via an enhanced multidimensional data management system, wherein the enhanced multidimensional data management system is configured to handle a variety of spreadsheets not initially designed with the objective of potential enhancements and modifications;

receiving modifications to the enhanced collaborative spreadsheet and the corresponding OLAP structure, wherein modifications to the conventional spreadsheet are made to match the data modified in the OLAP structure, wherein modifications include inserting or deleting lines and rows to allow for automatic update of the OLAP structure and the enhanced collaborative spreadsheet; and

versioning all scenarios of modifications, wherein each cell in the set of cells includes a versioning mechanism such that individual cells from multiple versions of a same spreadsheet file can be synchronized and manipulated simultaneously, and wherein versioning is not natively supported inside the conventional spreadsheet.

19. The system of claim 18, wherein modifying iterations to dimensions/measures/timescale in an OLAP structure modifies rows/columns in the enhanced collaborative spreadsheet along with corresponding formulas.

20. The system of claim 18, wherein enterprise wide spreadsheets can be merged into an OLAP structure according to an existing spreadsheet.