Reporting model generation within a multidimensional enterprise software system

• US 20060116859A1
• Filed: 11/30/2004
• Published: 06/01/2006
• Est. Priority Date: 11/30/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Roy, Dominique, Legault, Thomas
• Granted Patent: US 7,505,888 B2
• Time in Patent Office: Days
• 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 relational database storing multidimensional data in accordance with a relational database schema;

a model generator that produces a reporting model based on the database schema; and

a reporting tool that outputs a report to present the multidimensional data in accordance with the reporting model.

2. The system of claim 1,

wherein the model generator produces the reporting model as a base reporting model based on the database schema,

wherein the model generator produces a user reporting model by importing the base reporting model and updating the user reporting model in response to modifications requested by a user, and

wherein the reporting tool outputs 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 user reporting model.

4. The system of claim 3, wherein 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.

5. The system of claim 1,

wherein the model generator presents a user interface listing a plurality of database schemas maintained within the relational database, and

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

6. The system of claim 5, wherein the model generator presents 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 model generator presents 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 database schema stores metadata that defines data types for measures within the multidimensional data, and

wherein the schema generator modifies the metadata stored within the database schema based on input from a user, and regenerates the reporting model from the modified database schema.

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

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

11. The system of claim 1,

wherein the database schema comprises as 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 model generator produces the reporting model based on the star schemas.

12. The system of claim 1

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:

storing multidimensional data in a relational database in accordance with a relational database schema;

generating a reporting model based on the database schema; and

output a report with a reporting tool to present the multidimensional data in accordance with the reporting model.

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

generating a base reporting model based on the database schema;

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

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

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

16. The method of claim 15, further comprising:

regenerating the base reporting model to include new definitions in the event the database schema changes;

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

reapplying the modifications from the activity log to the user reporting model to automatically update the 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 database schema; and

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

23. The method of claim 13,

wherein the 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:

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

generate a user reporting model by importing the base reporting model;

update the user reporting model in response to modifications requested by a user;

maintain an activity log to record the requested modifications to the user reporting model;

regenerate the base reporting model to include new definitions in the event the database schema changes;

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

reapply the modifications from the activity log to the user reporting model to automatically update the 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.