Reporting model generation within a multidimensional enterprise software system

• US 7,505,888 B2
• Filed: 11/30/2004
• Issued: 03/17/2009
• Est. Priority Date: 11/30/2004
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Roy, Dominique, Legault, Thomas
• Primary Examiner(s): Rodriguez; Paul L
• Assistant Examiner(s): Kim; Eunhee
• Application Number:
  US10/999,807
• Publication Number:
  US 20060116859A1
• Time in Patent Office: 1,568 Days
• Field of Search: 703/22, 707/2, 707/100
• US Class Current: 703/22
• CPC Class Codes: G06F 16/248 Presentation of query results
• 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

Abstract

Techniques are described for automatically generating a reporting model based on a relational database storing multidimensional data in accordance with a relational database schema. A model generator may, for example, produce a base reporting model from the database schema, and subsequently generate a user reporting model by importing the base reporting model. The model generator updates the user reporting model in response to modifications requested by a user, and maintains an activity log to record the modifications. The model generator regenerates the base reporting model and the user reporting model in the event the database schema changes, and reapplies the modifications from the activity log to the user reporting model to automatically update the user reporting model. The database schema may be a relational star schema as described herein.

Claims

1. A computer-implemented system comprising:

a multidimensional database comprising a data cube storing multidimensional data;

a relational database organized in accordance with a relational database schema;

a computer-readable medium storing a schema generator, a publish module, a model generator and a reporting tool in the form of software instructions; and

a processor executing the schema generator to receive from a user a selection of one or more dimensions from the data cube and, in response, automatically produce the relational database schema for the relational database based on an organization of the multidimensional data within the data cube;

wherein the processor executes the publish module to retrieve the multidimensional data from the data cube of the multidimensional database and to store the retrieved multidimensional data within the relational database in accordance with the relational database schema produced by the schema generator,

wherein the processor executes the model generator to automatically produce a reporting model based on the relational database schema produced by the schema generator,

wherein, for a subsequent publication of the multidimensional data of the multidimensional database, the processor executes the schema generator to update the relational database schema based on the organization of the multidimensional data within the data cube,

wherein, in response to undated relational database schema, the processor executes the model generator to regenerate the reporting model, and

wherein the processor executes the reporting tool to output a report to present the multidimensional data in accordance with the regenerated reporting model.

2. The system of claim 1,

wherein the processor executes the model generator to produce the reporting model as a base reporting model based on the relational database schema,

wherein the processor executes the model generator to produce a user reporting model by importing the base reporting model and updating the user reporting model in response to modifications of the base reporting model as requested by a user, and

wherein the processor executes the reporting tool to output the report in accordance with the user reporting model.

3. The system of claim 2, further comprising an activity log maintained by the model generator to record the requested modifications to the base reporting model to produce the user reporting model.

4. The system of claim 3,

wherein the processor executes the model generator to generate a new base reporting model in response to changes to the relational database schema, and

wherein, after generating the new base reporting model in response to the changes to the relational database schema, the processor executes the model generator to reapply the modifications from the activity log to the new base reporting model to automatically generate a new user reporting model that is based on the new base reporting model and that is synchronized to include the user modifications used to produce the previous user reporting model.

5. The system of claim 1,

wherein the processor executes the model generator to present a user interface listing a plurality of relational database schemas maintained within the relational database, and

wherein the processor executes the model generator to receive input selecting one or more of the listed relational database schemas for inclusion in generating the reporting model.

6. The system of claim 5, wherein the processor executes the model generator to present the user interface to include an input mechanism by which a user selects dimension information for use in generating the reporting model.

7. The system of claim 5, wherein the processor executes the model generator to present the user interface to include an input mechanism by which a user selects one or more different database views for each data cube described by the multidimensional data.

8. The system of claim 1,

wherein the computer-readable medium stores a schema generator in the form of software instructions,

wherein the relational database schema stores metadata that defines data types for measures within the multidimensional data, and

wherein the processor executes the schema generator to modify the metadata stored within the relational database schema based on input from a user, and regenerate the reporting model from the modified relational database schema.

9. The system of claim 1, wherein the processor executes the model generator to create the reporting model as a plurality of extensible markup language (XML) files.

10. The system of claim 1, wherein the processor executes the model generator to generate a first set of definitions in the reporting model that describe relational tables of the relational database schema, and a second set of definitions that describe relationships between the tables.

11. The system of claim 1,

wherein the relational database schema comprises a set of one or more star schemas having one or more central fact tables and one or more dimension tables linked to the fact tables, and

wherein the processor executes the model generator to produce the reporting model based on the star schemas.

12. The system of claim 11,

wherein the fact tables contain data that describes one or more data cubes and corresponding measures of the multidimensional data, and

wherein each of the dimension tables contain data that describes dimensions of the data cubes.

13. A computer-implemented method comprising:

executing software instructions with a processor to receive from a user a request to publish multidimensional data from a data cube of a multidimensional database, the request specifying a selection of one or more dimensions of the data cube;

in response to the request to the publication request, executing software instructions to produce a relational database schema for a relational database based on an organization of the multidimensional data within the data cube, retrieve the multidimensional data from the data cube of a multidimensional database, and store the retrieved multidimensional data in a relational database in accordance with the relational database schema;

executing software instructions with the processor to automatically generate a reporting model based on the relational database schema;

for a subsequent publication of the multidimensional data of the multidimensional database, executing software instructions to update the relational database schema based on the organization of the multidimensional data within the data cube and, in response to updated relational database schema, executing instructions to regenerate the reporting model; and

executing software instructions to output a report with a reporting tool to present the multidimensional data stored within the relational database in accordance with the regenerated reporting model.

14. The method of claim 13, wherein automatically generating a reporting model comprises:

generating a base reporting model based on the relational database schema;

generating a user reporting model by importing the base reporting model; and

updating the user reporting model in response to modifications of the base reporting model previously requested by a user.

15. The method of claim 14, further comprising maintaining an activity log to record the requested modifications to the base reporting model to produce the user reporting model.

16. The method of claim 15, further comprising:

generating a new base reporting model to include new definitions in response to changes to the relational database schema;

synchronizing the user reporting model to use the new definitions from the new base reporting model; and

reapplying the modifications from the activity log to the user reporting model to generate a new user reporting model.

17. The method of claim 13, further comprising:

presenting a user interface listing a plurality of database schemas maintained within the relational database, and

receiving input selecting one or more of the listed database schemas for inclusion in generating the reporting model.

18. The method of claim 17, further comprising presenting the user interface to include an input mechanism by which a user selects dimension information for use in generating the reporting model.

19. The method of claim 17, further comprising presenting the user interface to include an input mechanism by which a user selects one or more different database views for each data cube described by the multidimensional data.

20. The method of claim 13, further comprising:

storing metadata within the database schema that defines data types and formats for measures within the multidimensional data;

modifying the metadata stored within the database schema based on input from a user; and

regenerating the reporting model from the modified database schema.

21. The method of claim 13, wherein the reporting model comprises a plurality of extensible markup language (XML) files.

22. The method of claim 13, wherein generating a reporting model comprises:

generating a first set of definitions in the reporting model that describe relational tables of the relational database schema; and

generating a second set of definitions that describe relationships between the tables.

23. The method of claim 13,

wherein the relational database schema comprises a set of one or more star schemas having one or more central fact tables and one or more dimension tables linked to the fact tables, and

wherein generating a reporting model comprises generating the reporting model based on the star schemas.

24. The method of claim 23,

wherein the fact tables contain data that describes one or more data cubes and corresponding measures of the multidimensional data, and

wherein each of the dimension tables contain data that describes dimensions of the data cubes.

25. A computer-readable medium comprising instructions to cause a programmable processor to:

in response to a first request to publish multidimensional data from, a data cube of a multidimensional database, retrieve the multidimensional data from the data cube and store multidimensional data in accordance with a multidimensional database schema;

generate a base reporting model based on a relational database schema that stores the retrieved multidimensional data in a relational database;

generate a first user reporting model by modifying the base reporting model in response to modifications requested by a user;

maintain an activity log to record the requested modifications to the base reporting model and an order in which the requested modifications were applied by the user when generating the first user reporting model;

in response to a subsequent request to publish data from the data cube;

(i) update the relational database schema in accordance with an organization of the data cube and republish the data from the data cube to the updated relational database schema of the relational database;

(ii) generate a new base reporting model that includes new definitions based on the undated relational database schema;

(iii) generate a new user reporting model that uses the new definitions from the new base reporting model; and

(iv) reapply the modifications from the activity log to the new user reporting model to automatically update the user reporting model to include the modifications that were applied by the user to the base reporting model when generating the first user reporting model.

26. The computer-readable medium of claim 25, further comprising instructions to cause the programmable processor to output a report to present the multidimensional data in accordance with the user reporting model.