Generating reports based on materialized view

Patent metadata

Field	Value
Publication number	US-9311357-B2
Application number	US-201313840899-A
Country	US
Kind code	B2
Filing date	Mar 15, 2013
Priority date	Jun 6, 2012
Publication date	Apr 12, 2016
Grant date	Apr 12, 2016

Abstract

Systems and methods for generating a report for a resource are described. The report is generated from databases comprising data sets related to a plurality of resources. An example method includes dynamic generation of a first query upon selection of at least one attribute by a user. The first query includes instructions for being performed on the data sets. The method further includes determining that the first query is stored in a table schema. The table schema is associated with a first trigger. The method also includes invoking the first trigger based on the determining. The first trigger is configured to execute the first query to generate a materialized view upon execution of the first query. The method may include generating the report by executing a second query on the materialized view. The second query may be created based on materialized view data.

First claim

We claim:

1. A computer implemented method for generating a report for a resource, the report being generated from databases comprising data sets related to a plurality of resources, the method comprising:
dynamically generating, by a processor, a first query upon selection of at least one attribute by a user from a plurality of attributes, wherein the first query comprises instructions for being performed on the data set, wherein the plurality of attributes correspond to a particular instance of the resource, wherein the plurality of attributes of a plurality of resources is stored in a source data set;
determining, by the processor, whether the first query is stored in a table schema, wherein the table schema is associated with a first trigger;
invoking, by the processor, the first trigger when the first query is stored in the table schema, wherein the first trigger is configured to execute the first query to generate a materialized view upon execution of the first query, wherein the materialized view is a database object that contains results of the first query;
generating, by the processor, a second query on the materialized view, based on user selection of the result of the first query rendered in the materialized view;
dynamically generating, by the processor, the report by executing the second query on the materialized view, wherein the second query is generated based on materialized view data, and wherein the report comprises relevant data and corresponding updated data from the source data set, and wherein the source data set is associated with a second trigger to dynamically update the materialized view data when a change is recorded in the source data set; and
generating a snapshot table based on the execution of the first query when the first query is not stored in the table schema in order to generate the report, wherein the snapshot table is configured to store the output of the first query.

2. The computer-implemented method as claimed in claim 1 , wherein the generating the first query further comprises defining structure of the report based on user inputs.

3. The computer-implemented method claimed in claim 1 , wherein the snapshot table is updated periodically to synchronize with a source data set when snapshot table data expires.

4. A report generation system for generating a report for a resource, the report being generated from databases comprising data sets related to a plurality of resources, the report generation system comprising:
a processor; and
a memory coupled to the processor, the memory comprising:
a query generation module configured to,
identify at least one attribute selected from a plurality of attributes of a resource, wherein the plurality of attributes correspond to a particular instance of the resource, and wherein the plurality of attributes of a plurality of resources is stored in a source data set; and
dynamically generate a first query upon selection of at least one attribute by the user, wherein the first query comprises instructions for being performed on the data sets;

an analysis module configured to,
determine whether the first query is stored in a table schema, wherein the table schema is indicative of reports tables of databases, and wherein the table schema is associated with a first trigger;
invoke the first trigger when the first query is stored in the table schema to execute the first query to generate a materialized view, wherein the materialized view is a database object that contains results of the first query; and

provide a materialized view based on output of the first query wherein, the materialized view is a database object that contains result of the first query; and
a rendering module configured to,
generate a second query on the materialized view, based on user selection of the result of the first query rendered in the materialized view;
dynamically generate a report by executing the second query on the materialized view, wherein the second query is generated based on materialized view data and wherein the report comprises relevant data and corresponding updated data from the source data set, and wherein the source data set is associated with a second trigger when the materialized view is generated, the second trigger is configured to update the materialized view data when a change is recorded in the source data set; and
wherein the analysis module is further configured to generate a snapshot table based on the execution of the first query when the first query is not stored in the table schema in order to generate the report, wherein the snapshot table stores the output of the first query.

5. The report generation system as claimed in claim 4 , wherein the query generation module is further configured to define filters and summary operations to be performed on the report that is generated based on the at least one attribute selected from a plurality of attributes.

6. The report generation system as claimed in claim 4 , wherein the query generation module is further configured to define structure of the report based on user inputs.

7. The report generation system as claimed in claim 4 , wherein the snapshot table is updated periodically to synchronize with the source data set when snapshot table data expires.

8. The report generation system as claimed in claim 4 , wherein the second query is stored in the table schema as a flat query.

9. A non-transitory computer-readable medium having embodied thereon a computer program for executing a method for generating a report for a resource, the report being generated from database, comprising data sets related to a plurality of resources, the method comprising:
dynamically, generating, by a processor, a first query based on selection by a user, of at least one attribute from a plurality of attributes of a resource, the first query comprises instructions for being performed on one or more data sets, wherein each of the one or more data sets is located in databases, wherein the plurality of attributes of a plurality of resources is stored in a source data set;
determining, by the processor, whether the first query is stored in a table schema, the table schema being indicative of reports tables of the databases, wherein the table schema is associated with a first trigger;
invoking, by the processor, the first trigger when the first query is stored in the table schema, wherein the first trigger is configured to execute the first query to generate a materialized view upon execution of the first query, wherein the materialized view is a database object that contains result of the first query;
generating, by the processor, a second query on the materialized view, based on user selection of the result of the first query rendered in the materialized view;
dynamically generating, by the processor, the report by executing the second query on the materialized view, wherein the second query is created based on materialized view data and wherein the report comprises relevant data and corresponding updated data from the source data set, and wherein the source data set is associated with a second trigger to dynamically update the materialized view data when a change is recorded in the source data set; and
generating a snapshot table based on the execution of the first query when the first query is not stored in the table schema in order to generate the report, wherein the snapshot table is configured to store the output of the first query.

10. The non-transitory computer-readable medium as claimed in claim 9 , wherein the generating the first query further comprises defining structure of the report based on user inputs.

11. The non-transitory computer-readable medium as claimed in claim

Assignees

• Tata Consultancy Services Ltd

Inventors

• Ramesh Rajini
• Garlapati Madhavi
• Thenmani Mutharasi

Classifications

• G06F17/30457

  Physics · mapped topic

• G06F17/30477 Primary

  Physics · mapped topic

• G06F17/30383

  Physics · mapped topic

• G06F16/24539

  using cached or materialised query results · CPC title

• G06F16/2455 Primary

  Query execution · CPC title