CLIAMS:1. A method for optimizing pagination of a result report for queries over a data store, the method comprising:
receiving one or more queries, by a query result evaluation unit configured in a query executing server, wherein the query result evaluation unit determines one or more attributes in each of the received one or more queries, further wherein the query result evaluation unit determines cardinality associated with each of the one or more attributes, parent-child relationship information between each of the one or more attributes and sorting order of the one or more attributes;
creating a page information record, by the query result evaluation unit, for each of the received one or more queries, wherein the page information record determines number of pages in the result report and number of rows in each page of the result report;
identifying, by a constraint identifying unit configured in the query executing server, constraints required for number of rows in each page based on the cardinality, parent-child relationship information between the one or more attributes and the sorting order of the one or more attributes;
executing, by a data scanning unit configured in the query executing server, the one or more queries in the data store, based on the identified constraints to retrieve data related to the one or more queries;
determining, by the data scanning unit, whether the retrieved data related to the one or more queries is sufficient for displaying in the number of rows in each page of the result report; and
repeating process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes when the retrieved data is not sufficient for displaying in the number of rows in each page of the result report.
2. The method as claimed in claim 1, wherein the page information record includes plurality of information which is dynamically updated for facilitating execution of the received one or more queries on the data store.
3. The method as claimed in claim 2, wherein the plurality of information includes number of pages in the result report, number of rows in each page of the result report, one or more attributes in the received query and sorting order of the one or more attributes, number of rows in each page of the result report available after displaying the retrieved data related to the one or more queries, number of rows configured as buffer rows in each page of the result report, filtered queries and the constraints.

4. The method as claimed in claim 1, wherein the cardinality associated with each of the one or more attributes, parent-child relationship information between each of the one or more attributes and sorting order of the one or more attributes is determined based on information stored in the data store.

5. The method as claimed in claim 1, wherein the constraints required for number of rows in each page is identified by traversing from last attribute in the received one or more queries to the first attribute in the received one or more queries.

6. The method as claimed in claim 1, wherein upon retrieving the data related to the one or more queries, the data scanning unit provides information related to the retrieved data and the constraints to the query result evaluation unit.

7. The method as claimed in claim 6, wherein upon receiving the information related to the retrieved data and the constraints, the query result evaluation unit updates the page information table.

8. The method as claimed in claim 1, wherein the one or more queries is a Multidimensional Expressions (MDX) query.
9. The method as claimed in claim 1, wherein the one or more queries are received by the query executing unit from one or more client machines associated to one or more users.

10. The method as claimed in claim 1, wherein if the retrieved data is exceeding to be displayed in the number of rows in one page of the result report then the exceeding data is displayed in next page of the result report.

11. A query executing server for optimizing pagination of a result report for queries over a data store, the query executing server comprising:
a query result evaluation unit configured to:
receive one or more queries from a user of the computing device, wherein the query evaluation unit determines one or more attributes in each of the received one or more queries, further wherein the query result evaluation unit determines cardinality associated with each of the one or more attributes, parent-child relationship information between each of the one or more attributes and sorting order of the one or more attributes; and
create a page information record for each of the received one or more queries, wherein the page information record determines number of pages in the result report and number of rows in each page of the result report;
a constraint identifying unit configured to identify constraints required for number of rows in each page based on the cardinality, parent-child relationship information between the one or more attributes and the sorting order of the one or more attributes by the paging unit;
a data scanning unit configured to:
execute the one or more queries in the data store based on the identified constraints to retrieve data related to the one or more queries;
determine, whether the retrieved data related to the one or more queries is sufficient for displaying in the number of rows in each page of the result report; and
repeating process of identifying the constraints required for number of rows in each page based on the cardinality, parent-child relationship information and the sorting order of the one or more attributes if the retrieved data is not sufficient for displaying in the number of rows in each page of the result report.
12. The query executing server as claimed in claim 11, wherein the page information record includes plurality of information which is dynamically updated for facilitating execution of the received one or more queries on the data store.
13. The query executing server as claimed in claim 11, wherein the constraint identifying unit identifies the constraints required for number of rows in each page by traversing from last attribute in the received one or more queries to the first attribute in the received one or more queries.

14. The query executing server as claimed in claim 11, wherein the data scanning unit provides information related to the retrieved data and the constraints to the query result evaluation unit.

15. The query executing server as claimed in claim 14, wherein the query result evaluation unit updates the page information table upon receiving the information related to the retrieved data and the constraints from the data scanning unit.

16. The query executing server as claimed in claim 11, wherein the query result evaluation unit receives the one or more queries from one or more client machines associated to one or more users.

17. The query executing server as claimed in claim 16, wherein the one or more client machines are selected from at least one of a mobile device, personal computer, Personal Digital Assistants (PDA), and any other communication devices capable of performing data transmission and displaying.

18. The query executing server as claimed in claim 11, wherein the query result evaluation unit determines the cardinality associated with each of the one or more attributes, parent-child relationship information between each of the one or more attributes and sorting order of the one or more attributes based on information stored in the data store.

19. A non-transitory computer readable medium including operations stored thereon that when processed by at least one query executing server cause a system to perform the acts of:
receiving one or more queries, determining one or more attributes in each of the received one or more queries, determining cardinality associated with each of the one or more attributes, parent-child relationship information between each of the one or more attributes and sorting order of the one or more attributes;
creating a page information record for each of the received one or more queries, wherein the page information record determines number of pages in the result report and number of rows in each page of the result report, further wherein the page information record includes plurality of information which is dynamically updated for facilitating execution of the received one or more queries on the data store;
identifying constraints required for number of rows in each page based on the cardinality, parent-child relationship information between the one or more attributes and the sorting order of the one or more attributes;
executing the one or more queries in the data store based on the identified constraints it to retrieve data related to the one or more queries;
determining whether the retrieved data related to the one or more queries is sufficient for displaying in the number of rows in each page of the result report; and
repeating process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes if the retrieved data is not sufficient for displaying in the number of rows in each page of the result report.
20. A computer program for optimizing pagination of a result report for queries over a data store, said computer program comprising:
code segment for receiving one or more queries, code segment for determining one or more attributes in each of the received one or more queries, cardinality associated with each of the one or more attributes, parent-child relationship information between the one or more attributes and sorting order of the one or more attributes, code segment for creating a page information record for each of the received one or more queries, code segment for identifying constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes, code segment for executing the one or more queries in the data store based on the identified constraints it to retrieve data related to the one or more queries, code segment for determining whether the retrieved data related to the one or more queries is sufficient for displaying in the number of rows in each page of the result report and code segment for repeating process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes if the retrieved data is not sufficient for displaying in the number of rows in each page of the result report.
,TagSPECI:TECHNICAL FIELD
The present disclosure relates to database technologies. In particular, the present disclosure relates to a method and system for optimizing pagination of a result report for queries over data store.
BACKGROUND
Information technology (IT) enables an enterprise to manage and optimize its internal business practices through the analysis and sharing of data internally within the enterprise. In addition, IT enables an enterprise to manage and optimize its external business practices through the sharing of data with external parties such as suppliers, customers and investors, and through on-line transactions between the enterprise and external parties. The volume of the data that is available to the enterprises is rapidly increasing and frequently overwhelming.
Data warehousing, the creation of an enterprise-wide database, is the first step towards managing these volumes of data. The database is becoming an integral part of many information delivery systems because it provides a single, central location where the data extracted from a wide variety of operational systems is stored. Over the last few years, improvements in price, performance, scalability, and robustness of the computing systems have made database a central component of Information Technology. Building a database has its own special challenges and is a complex endeavor. To complete the supply chain from the transactional systems to decision maker, enterprise need to deliver systems that allow the workers to make strategic and tactical decisions based on the information stored in the database. These decision support systems are referred to as On-Line Analytical Processing (OLAP) systems. OLAP systems allow the workers to intuitively, quickly, and flexibly manipulate the operational data stored in the database. For example, by using an OLAP system, decision makers can "slice and dice" information along any dimension, and view business metrics. Result reports can be defined from multiple perspectives that provide a high-level or detailed view of the performance of any aspect of the business. Decision makers can navigate throughout their database by drilling down on the result report to view elements at finer levels of detail, or by pivoting to view reports from different perspectives.

A particular type of database, namely a Multi-Dimensional database is optimized for answering analytical queries on a database. Multi-dimensional database presents a multi-dimensional view or model of the data stored in the database. The data in the model is organized as a collection of measures and multiple dimensions. The data can be analyzed along any dimension present in the model. Dimensions are also sometimes referred to as attributes. Usually when the queried data that has to be displayed in the report is huge, instead of displaying all the records at once, it is paginated. i.e. only a fixed set of records are displayed in the first page & the next set is displayed in the next page & it goes so on, till all the records are displayed. At present, there are different ways by which the method of pagination is performed.
Fig.1a illustrates an existing method of providing pagination for queries over the database 107. As an example, the server 100 receives the grouped query from a user. The On-Line Analytical Processing (OLAP) query engine 103 configured in the server 100 executes the query in the database 107. When the server 100 executes the query, the server 100 scans the entire database 107 to retrieve the data related to the query. The server 100 scans the entire database 107 and retrieves all the data related to the received query. All the retrieved data is stored in the cache memory 105. The server 100 displays the result report to the user based on the pagination record defined by the user. As an example, the user defines that there has to be two pages in the result report. When the retrieved data is obtained from the database 107, the server 100 displays fixed set of retrieved data in the first page and next set of retrieved data in the second page. When the user clicks on next page, a request is sent to the server 100 based on which the server 100 fetches the next set of retrieved data from the cache memory 105 and provides the result to the client.
The limitation with the scenario illustrated in Fig.1a is that the server 100 has to scan the entire database 107 to provide the data related to the received query and the cache memory 105 has to store all the result data which increases the query response time and the query performance. Further, if the user is not interested in the next page data, the total data retrieved from the database 107 is an overhead.
Fig.1b illustrates another existing method of providing pagination for queries over the database 107. In this method, the server 100 uses database 100 commands like limit, rownum, offset to solve the pagination. Using database 107 commands the server 100 limits the data to be retrieved from the database 107. As an example, the server 100 receives the grouped query from a user. The OLAP query engine 103 configured in the server 100 executes the query in the database 107 using the commands such as limit and rownum. When the server 100 executes the query, the server 100 scans the entire database 107 to retrieve the data related to the query. Upon retrieving the data instead of obtaining all the data from the data store, the server retrieves only required set of data say for example 100 records. Therefore only 100 records are obtained from the database 100 and stored in the cache memory 105.
The limitation with the scenario illustrated in Fig.1b is that the server 100 has to scan the entire database 107 to provide the data related to the received query and also the commands used to solve the pagination are inefficient for big data in skipping the rows.
Hence, there exists a need for a method and system for optimizing pagination of result report for queries over data store by which the database performance is improved.

SUMMARY
The shortcomings of the prior art are overcome and additional advantages are provided through the present disclosure. Additional features and advantages are realized through the techniques of the present disclosure. Other embodiments and aspects of the disclosure are described in detail herein and are considered a part of the claimed disclosure.

The present disclosure provides a method for optimizing pagination of a result report for queries over a data store. The method comprises receiving one or more queries by a query result evaluation unit configured in a query executing server. The query result evaluation unit determines one or more attributes associated in the received one or more queries. The query result evaluation unit also determines cardinality associated with each of the one or more attributes, parent-child relationship between the one or more attributes and sorting order associated with each of the one or more attributes. Thereafter, the query result evaluation unit creates a page information record for each of the received one or more queries. The page information record determines number of pages in the result report and number of rows in each page of the result report. The page information record also includes plurality of information which is dynamically updated for facilitating execution of the received one or more queries on the data store. The constraint identifying unit configured in the query executing server identifies constraints required for number of rows in each page based on the cardinality, parent-child relationship information and the sorting order of the one or more attributes. The identified constraints are provided to the data scanning unit configured in the query executing server. The data scanning unit executes one or more queries in the data store based on the identified constraints to retrieve data related to the one or more queries. If the retrieved data is sufficient to be displayed in the number of rows in each page of the result report then the data scanning unit displays the retrieved data in the result page based on the defined pagination. If the retrieved data is not sufficient for displaying in the number of rows in each page of the result report, then the process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes is repeated.

The present disclosure provides a query executing server for optimizing pagination of a result report for queries over a data store. The system comprises one or more client machines, a communication network, a query executing server and a data store. The query executing server comprises a query result evaluation unit, a constraint identifying unit, a data scanning unit and a memory unit. The query executing server receives one or more queries form one or more client machines over the communication network. Upon receiving the one or more queries, the query result evaluation unit determines one or more attributes in the received one or more queries. The query result evaluation unit also determines cardinality of each of the one or more attributes, parent-child relationship between the one or more attributes and sorting order associated with each of the one or more attributes. Thereafter, the query result evaluation unit creates a page information record for each of the received one or more queries. The page information record determines number of pages in the result report and number of rows in each page of the result report. The page information record also includes plurality of information which is dynamically updated for facilitating execution of the received one or more queries on the data store. The constraint identifying unit identifies constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes. The identified constraints are provided to the data scanning unit. The data scanning unit executes one or more queries in the data store based on the identified constraints to retrieve data related to the one or more queries. If the retrieved data is sufficient to be displayed in the number of rows in each page of the result report then the data scanning unit displays the retrieved data in the result page based on the defined pagination. If the retrieved data is not sufficient for displaying in the number of rows in each page of the result report, then the process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes is repeated.

The present disclosure provides a non-transitory computer readable medium including operations stored thereon that when processed by at least one query executing server cause a system to receive one or more queries and determine one or more attributes in each of the received one or more queries, cardinality associated with each of the one or more attributes, parent-child relationship between the one or more attributes and sorting order of the one or more attributes. Thereafter a page information record for each of the received one or more queries is created. The page information record determines number of pages in the result report and number of rows in each page of the result report, further wherein the page information record includes plurality of information which is dynamically updated for facilitating execution of the received one or more queries on the data store. Then the constraints required for number of rows in each page is identified based on the cardinality, parent-child relationship information and the sorting order of the one or more attributes. The received one or more queries are executed based on the identified constraints to retrieve data related to the one or more queries. The system determines whether the retrieved data related to the one or more queries is sufficient for displaying in the number of rows in each page of the result report. If the retrieved data is not sufficient for displaying in the number of rows in each page of the result report, then the process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes is repeated.
The present disclosure provides a computer program for optimizing pagination of a result report for queries over a data store. The computer program comprises a code segment for receiving one or more queries, code segment for determining one or more attributes in each of the received one or more queries, cardinality associated with each of the one or more attributes, parent-child relationship between the one or more attributes and sorting order of the one or more attributes, code segment for creating a page information record for each of the received one or more queries, code segment for identifying constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes, code segment for executing the one or more queries in the data store based on the identified constraints to retrieve data related to the one or more queries, code segment for determining whether the retrieved data related to the one or more queries is sufficient for displaying in the number of rows in each page of the result report and code segment for repeating process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes if the retrieved data is not sufficient for displaying in the number of rows in each page of the result report.
The foregoing summary is illustrative only and is not intended to be in any way limiting. In addition to the illustrative aspects and features described above, further aspects, and features will become apparent by reference to the drawings and the following detailed description.

BRIEF DESCRIPTION OF THE ACCOMPANYING DRAWINGS

The novel features and characteristic of the disclosure are set forth in the appended claims. The embodiments of the disclosure itself, however, as well as a preferred mode of use, further objectives and advantages thereof, will best be understood by reference to the following detailed description of an illustrative embodiment when read in conjunction with the accompanying drawings. One or more embodiments are now described, by way of example only, with reference to the accompanying drawings.

Fig.1a illustrates a block diagram of a system for optimizing pagination of a result report for queries over data store in accordance with an embodiment of the prior art;
Fig.1b illustrates a block diagram of a system for optimizing pagination of a result report for queries over data store in accordance with another embodiment of the prior art;
Fig.2a illustrates a block diagram of a system for optimizing pagination of a result report for queries over data store in accordance with an embodiment of the present disclosure;
Fig.2b illustrates a block diagram of a query executing server for optimizing pagination of a result report for queries over a data store in accordance with an embodiment of the present disclosure;
Fig.3 shows block diagram of a system illustrating method of retrieving data for the queries over database in accordance with an embodiment of the present disclosure;
Fig.4 illustrates a method for optimizing pagination of a result report for queries over data store in accordance with an exemplary embodiment of the present disclosure; and
Fig.5 shows a flowchart illustrating method for optimizing pagination of a result report for queries over data store in accordance with an embodiment of the present disclosure.
The figures depict embodiments of the disclosure for purposes of illustration only. One skilled in the art will readily recognize from the following description that alternative embodiments of the structures and methods illustrated herein may be employed without departing from the principles of the disclosure described herein.

DETAILED DESCRIPTION
The foregoing has broadly outlined the features and technical advantages of the present disclosure in order that the detailed description of the disclosure that follows may be better understood. Additional features and advantages of the disclosure will be described hereinafter which form the subject of the claims of the disclosure. It should be appreciated by those skilled in the art that the conception and specific aspect disclosed may be readily utilized as a basis for modifying or designing other structures for carrying out the same purposes of the present disclosure. It should also be realized by those skilled in the art that such equivalent constructions do not depart from the spirit and scope of the disclosure as set forth in the appended claims. The novel features which are believed to be characteristic of the disclosure, both as to its organization and method of operation, together with further objects and advantages will be better understood from the following description when considered in connection with the accompanying figures. It is to be expressly understood, however, that each of the figures is provided for the purpose of illustration and description only and is not intended as a definition of the limits of the present disclosure.

The present disclosure is related to a method and system for providing pagination of a result report for queries over a data store. The system comprises one or more client machines, a query executing server and a data store. The query executing server receives one or more queries from the one or more client machines through a communication network. The query executing server comprises of a query evaluation unit, a constraint identifying unit and a data scanning unit. The query result evaluation unit determines one or more attributes associated in each of the one or more queries. The query result evaluation unit also determines cardinality associated with each of the one or more attributes, parent-child relationship between the one or more attributes and sorting order associated with each of the one or more attributes based on information stored in the data store. The query result evaluation unit creates a page information record for each of the received one or more queries. The page information record determines number of pages in the result report and number of rows in each page of the result report. The page information record also includes plurality of information which is dynamically updated for facilitating execution of the received one or more queries on the data store. The constraint identifying unit identifies constraints required for number of rows in each page based on the cardinality, parent-child relationship information and the sorting order of the one or more attributes. Thereafter, the data scanning unit executes one or more queries in the data store based on the identified constraints to retrieve data related to the one or more queries. If the retrieved data is sufficient to be displayed in the number of rows in each page of the result report then the retrieved data results are displayed in the result page based on the defined pagination. If the retrieved data is not sufficient for displaying in the number of rows in each page of the result report, then the process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes is repeated.
Henceforth, embodiments of the present disclosure are explained with the help of exemplary diagrams and one or more examples. However, such exemplary diagrams and examples are provided for the illustration purpose for better understanding of the present disclosure and should not be construed as limitation on scope of the present disclosure.

Fig.2a illustrates a block diagram of a system for optimizing pagination of a result report for queries over data store in accordance with an embodiment of the present disclosure. The system 100 provides pagination of a result report for queries over a data store 215. The system 100 comprises one or more client machines 201, client machine 1 2011 to client machine N 201N, a communication network 203, a query executing server 205 and a data store 215. The one or more client machines 201 are associated to one or more users using which the one or more users provide queries and retrieve results from the query executing server 205. In an embodiment, the one or more queries are generated using a Multidimensional Expressions (MDX) language. The one or more client machines 201 include, but are not limited to, a mobile device, a personal computer, Personal Digital Assistants (PDA), and any other communication devices capable of receiving input from the users, performing data transmission and displaying. In an embodiment, the one or more users use the one or more client machines 201 for accessing the data from the query executing server 205. The communication network 203 includes, but is not limited to, an e-commerce network, a peer to peer (P2P) network, Local Area Network (LAN), Wide Area Network (WAN) and any wireless network such as Internet and WIFI etc. The communication network 103 enables the one or more users (using the one or more client machines) to access the data from the query executing server 205. The data store 215 is a multi-dimensional data store optimized for answering analytical queries. The multi-dimensional data store 215 presents a multi-dimensional view or model of the data. The data in the model is organized as a collection of measures and attributes. The data can be analyzed along any attribute present in the model. In an embodiment, the data store 215 stores data related to one or more establishments. The one or more establishments, includes, but are not limited to, financial institutions, stocks, commercial establishments, government offices, data security centers, social networks, educational institutions, weather forecast centers and manufacturing industries. In an embodiment, the one or more users provide queries using the one or more client machines 201. The one or more client machines 201 send the one or more queries to the query executing server 205 through the communication network 203.

Fig.2b illustrates a block diagram of a query executing server for optimizing pagination of a result report for queries over a data store in accordance with an embodiment of the present disclosure. The query executing server comprises a query result evaluation unit 207, the constraint identifying unit 209, data scanning unit 211 and the memory unit 213. The query result evaluation unit 207 determines one or more attributes in each of the received one or more queries. Upon determining the one or more attributes, the query result evaluation unit 207 retrieves the information related to cardinality, parent-child relationship and sorting order of the one or more attributes. The information related to cardinality, parent-child relationship and sorting order of the one or more attributes is the pre-processed information based on the information stored in the data store 215. If the sorting order is not defined in the received one or more queries, then the attributes are operated in the ascending order by default. The query result evaluation unit 207 creates a page information record for each of the received one or more queries. When a query is received by the query result evaluation unit 207, the query result evaluation unit 207 creates a page information record. The page information record provides plurality of information related to a result report. The result report is defined based on the requirement of the user. For example, the number of pages in the result report and number of rows in each page of the result report. All these information is defined by the user. Based on this information the result report is configured. The user defines the number of rows to be configured in each page of the result report and also number pages in the result report. In the first instance, the user defines number of pages to be configured in the result report and number of rows in each page of the result report. In an embodiment, the query executing server 205 configures the number of buffer rows. All these information will be provided in the page information record. The information regarding the identified one or more attributes is also provided in the page information record and the remaining information such as information related to constraints, filter, row count and left over rows are not updated. The plurality of information in the page information record includes sorting order of the one or more attributes, number of rows in each page of the result report available after displaying the retrieved data related to the one or more queries, filtered queries and the constraints. The plurality of information is dynamically updated for facilitating execution of the received one or more queries on the data store 215.

The constraint identifying unit 209 identifies the constraints for number of rows in each page of the result report based on the cardinality, parent-child relationship information and sorting order of the attributes. The identified constraints are provided to the data scanning unit 211. The data scanning unit 211 generates modified MDX query based on the received constraints. The data scanning unit 211 executes the one or more queries in the data store 215 based on the identified constraints to retrieve data related to the one or more queries. In an embodiment, the data scanning unit 211 doesn’t have to scan the entire data store to retrieve the data related to the one or more queries. Since, the constraints are already provided, the data scanning unit 211 scans only in particular block of the data store 215 where the constraints are present say for example only in 1st Block or only in 2nd block and retrieves the data as shown in Fig.3. The data scanning unit 211 determines whether the retrieved data is sufficient to be displayed in the configured result report. If the retrieved data is sufficient, the data scanning unit 211 displays the retrieved data in the result report. If the retrieved data is not sufficient to be displayed in the configured result report, the data scanning unit 211 provides information related to the retrieved data and the constraints to the query result evaluation unit 207. The query result evaluation unit 207 repeats the process of identifying new set of constraints based on cardinality and sorting order of the one or more attributes to obtain new set of data related to the received query. The process of identifying the constraints is repeated until the retrieved data related to one or more queries is sufficient to be displayed in the configured result report. If the retrieved data is exceeding the number of rows, then the extra data is displayed in the next page of the result report.

Fig.4 illustrates a method for optimizing pagination of a result report for queries over data store 215 in accordance with an exemplary embodiment of the present disclosure. A user associated with the client machine 2011 provides a query as shown below to the query executing server 205 through a communication network 203.

Query: Select S, C, P sum (value) from t group by S, C, P.

Wherein S denotes State, C denotes City, P denotes Protocol and t denotes name of the table.

Table t

The query is to identify 100 protocols associated with two devices used in all the 3 states. The table t has seven columns namely D denoting device, P denoting Protocol, IMSE denoting IMEI number of the device, S denoting State, C denoting City, USERID denoting user ID of the one or more users and MSR denoting Measure. From the table t, the query executing server prepares information related to cardinality of the one or more attributes, the parent-child relationship between the one or more attributes and the sorting order of the one or more attributes. Based on the table, the query executing server 205 identifies that there are two devices, three states and 100 protocols in the table. The query executing server 205 also identifies that the attributes state 3 and the child has parent-child relationship. The query executing server identifies the sorting order of the one or more attributes. The sorting order is S, C, and P. All these information related to the cardinality, parent-child relationship and the sorting order is provided to the query result evaluation unit 207.

The query result evaluation unit 207 configured in the query executing server 205 receives the query. Upon receiving the query, the query result evaluation unit 207 determines the one or more attributes in the received query. The one or more attributes in the received query are State (S), City (C) and Protocol (P). The query result evaluation unit 207 also retrieves the information related to cardinality, parent-child relationship and sorting order of the one or more attributes. The query result evaluation unit 207 creates the page information table upon receiving the query. In the first instance, the user defines pagination for the result report i.e the number of pages to be displayed in the result report, number of rows to be displayed in each page of the result report and buffer rows in each page of the result report is defined. Buffer rows are number of extra rows defined by the user for displaying the data in each page of the result report. The user defines that there has to be one page in the result report i.e page count is 1, 150 rows in the page i.e pagination is 150 and 50 buffer rows. Therefore, totally there has to be 200 rows in the page. Upon creating page information table, the constraint identifying unit 209 identifies the constraints for each of the one or more attributes. The attributes are State 3, City 100 and Protocol 100. The constraint identifying unit 209 identifies the constraints from the attribute which is in right side of the received query to the attribute which is in left side of the received query. Therefore, the first operating attribute is P. The cardinality associated with the attribute P is 100. i.e 100 rows are required to display 100 protocols. The defined number of rows in the page is 200 which is more than the required 100 rows. Therefore, P is set to ALL i.e all the protocols can be displayed in the single page. The next operating attribute is C whose cardinality is 100. i.e 100 city names should be displayed in the page. The constraint identifying unit 209 identifies if the State and City attributes has parent child relationship i.e identifying number of cities under each state. State 3 has 100 cities. Therefore there is a parent child relationship between the attributes S and C. Hence, the attribute C is not updated. The next operating attribute is State 3. The cardinality of State 3 is 100 since there are 100 cities under state 3. 100 cities and 100 protocols produce 10000 rows which is more than the defined number of rows in the page. Therefore, instead of displaying all the cities, the constraint identifying unit 209 considers only top two cities i.e 100th City and 99th. This is because user has selected the cities to be displayed in descending order. Now the constraints are state 3, two cities i.e 100th city and the 99th city and 100 protocols. The two cities and the 100 protocols produce 200 rows which is equal to the number of rows defined for the page.

The identified constraints are provided to the data scanning unit 211. The data scanning unit 211 generates a modified MDX query with the identified constraints and executes the query in the data store 215. The data scanning unit 211 doesn’t have to scan the entire data store 215 to retrieve the data related the query since the constraints are already defined in the query i.e the data scanning unit 211 has to determine in which block the data related to the identified constraints are present and it has to scan only that block to retrieve the data related to the query. Upon retrieving the data related to the query, the data scanning unit 211 determines whether the retrieved data is sufficient to be displayed in the result report. In the given example, the retrieved data is sufficient to be displayed in defined number of rows. Therefore, the data scanning unit 211 displays the retrieved data in the result report. If the retrieved data is not sufficient to be displayed in the number of rows, say for example only 100 rows of data is retrieved from the data store. In this scenario, the data scanning unit 211 provides the retrieved data from the data store 215 and the identified constraints to the query result evaluation unit 207. The query result evaluation unit 207 updates constraints in the page information table and also update the number of left over rows. Here the number of left over rows is 100. Therefore, still 100 rows of data have to be retrieved. For this purpose, the constraint identifying unit 209 identifies new set of constraints and based on the new set of constraints the data related to the query is retrieved. The process of identifying the constraints, executing the query and retrieving the data is repeated until the data is sufficient for the page. In an embodiment, if the retrieved data is exceeding the number of rows, then the extra data is displayed in the next page of the result report wherein based on the extra data the next page of the result report is configured. As an example, consider 100 rows configured in the first page. The retrieved data requires 110 rows. Therefore, the extra 10 rows of data will be displayed in the next page of the result report. The number of rows in the next page is configured based on the extra data i.e if the page size is of the next page is 100 rows it is estimated only for 90 rows since it has to accommodate the extra 10 rows of data from the first page.

Fig.4 shows a flowchart illustrating method for optimizing pagination of a result report for queries over data store 215 in accordance with an embodiment of the present disclosure. At step 501, the query result evaluation unit 207 receives one or more queries from the one or more client machines through a communication network. At step 503, the query result evaluation unit 207 determines one or more attributes associated in each of the one or more queries. The query result evaluation unit 207 also determines cardinality and sorting order associated with each of the one or more attributes. At step 505, the query result evaluation unit 207 creates a page information record for each of the received one or more queries. The page information record determines number of pages in the result report and number of rows in each page of the result report. The page information record also includes plurality of information which is dynamically updated for facilitating execution of the received one or more queries on the data store 215. At step 507, the constraint identifying unit 209 identifies constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes. The identified constraints are provided to the data scanning unit 211. Thereafter, the data scanning unit 211 executes one or more queries in the data store 215 based on the identified constraints to retrieve data related to the one or more queries at step 509. At step 511, the data scanning unit 211 determines whether the retrieved data related to one or more queries is sufficient to be displayed in number of in each page of the result report. If the retrieved data is sufficient to be displayed in the configured result report then the retrieved data results are displayed in the result page based on the defined pagination. If the retrieved data is not sufficient for displaying in the configured result report, then the process of identifying the constraints required for number of rows in each page based on the cardinality and the sorting order of the one or more attributes is repeated at step 513.
The described operations may be implemented as a method, system or article of manufacture using standard programming and/or engineering techniques to produce software, firmware, hardware, or any combination thereof. The described operations may be implemented as code maintained in a “non-transitory computer readable medium”, where a processing unit may read and execute the code from the computer readable medium. The processing unit is at least one of a microprocessor and a processor capable of processing and executing the queries. A non-transitory computer readable medium may comprise media such as magnetic storage medium (e.g., hard disk drives, floppy disks, tape, etc.), optical storage (CD-ROMs, DVDs, optical disks, etc.), volatile and non-volatile memory devices (e.g., EEPROMs, ROMs, PROMs, RAMs, DRAMs, SRAMs, Flash Memory, firmware, programmable logic, etc.), etc. The non-transitory computer-readable media comprise all computer-readable media except for a transitory. The code implementing the described operations may further be implemented in hardware logic (e.g., an integrated circuit chip, Programmable Gate Array (PGA), Application Specific Integrated Circuit (ASIC), etc.).
Still further, the code implementing the described operations may be implemented in “transmission signals”, where transmission signals may propagate through space or through a transmission media, such as an optical fiber, copper wire, etc. The transmission signals in which the code or logic is encoded may further comprise a wireless signal, satellite transmission, radio waves, infrared signals, Bluetooth, etc. The transmission signals in which the code or logic is encoded is capable of being transmitted by a transmitting station and received by a receiving station, where the code or logic encoded in the transmission signal may be decoded and stored in hardware or a non-transitory computer readable medium at the receiving and transmitting stations or devices. An “article of manufacture” comprises non-transitory computer readable medium, hardware logic, and/or transmission signals in which code may be implemented. A device in which the code implementing the described embodiments of operations is encoded may comprise a computer readable medium or hardware logic. Of course, those skilled in the art will recognize that many modifications may be made to this configuration without departing from the scope of the invention, and that the article of manufacture may comprise suitable information bearing medium known in the art.
The terms "an embodiment", "embodiment", "embodiments", "the embodiment", "the embodiments", "one or more embodiments", "some embodiments", and "one embodiment" mean "one or more (but not all) embodiments of the invention(s)" unless expressly specified otherwise.

The terms "including", "comprising", “having” and variations thereof mean "including but not limited to", unless expressly specified otherwise.

The enumerated listing of items does not imply that any or all of the items are mutually exclusive, unless expressly specified otherwise.

The terms "a", "an" and "the" mean "one or more", unless expressly specified otherwise.

Devices that are in communication with each other need not be in continuous communication with each other, unless expressly specified otherwise. In addition, devices that are in communication with each other may communicate directly or indirectly through one or more intermediaries.

A description of an embodiment with several components in communication with each other does not imply that all such components are required. On the contrary a variety of optional components are described to illustrate the wide variety of possible embodiments of the invention.

Further, although process steps, method steps, algorithms or the like may be described in a sequential order, such processes, methods and algorithms may be configured to work in alternate orders. In other words, any sequence or order of steps that may be described does not necessarily indicate a requirement that the steps be performed in that order. The steps of processes described herein may be performed in any order practical. Further, some steps may be performed simultaneously.

When a single device or article is described herein, it will be readily apparent that more than one device/article (whether or not they cooperate) may be used in place of a single device/article. Similarly, where more than one device or article is described herein (whether or not they cooperate), it will be readily apparent that a single device/article may be used in place of the more than one device or article or a different number of devices/articles may be used instead of the shown number of devices or programs. The functionality and/or the features of a device may be alternatively embodied by one or more other devices which are not explicitly described as having such functionality/features. Thus, other embodiments of the invention need not include the device itself.

The illustrated operations of Fig.5 show certain events occurring in a certain order. In alternative embodiments, certain operations may be performed in a different order, modified or removed. Moreover, steps may be added to the above described logic and still conform to the described embodiments. Further, operations described herein may occur sequentially or certain operations may be processed in parallel. Yet further, operations may be performed by a single processing unit or by distributed processing units.

The foregoing description of various embodiments of the invention has been presented for the purposes of illustration and description. It is not intended to be exhaustive or to limit the invention to the precise form disclosed. Many modifications and variations are possible in light of the above teaching. It is intended that the scope of the invention be limited not by this detailed description, but rather by the claims appended hereto. The above specification, examples and data provide a complete description of the manufacture and use of the composition of the invention. Since many embodiments of the invention can be made without departing from the spirit and scope of the invention, the invention resides in the claims hereinafter appended.

Additionally, advantages of present disclosure are illustrated herein.
Embodiment of the present disclosure provides a method to improve performance of executing a query by reducing the overhead of scanning entire data store.
Embodiment of the present disclosure provides a method wherein the cache memory in the query executing server stores only limited number of records for a page instead of storing complete set of records for all the pages the query output.
Embodiment of the present disclosure provides a cost effective query executing method and system.
Finally, the language used in the specification has been principally selected for readability and instructional purposes, and it may not have been selected to delineate or circumscribe the inventive subject matter. It is therefore intended that the scope of the invention be limited not by this detailed description, but rather by any claims that issue on an application based here on. Accordingly, the disclosure of the embodiments of the invention is intended to be illustrative, but not limiting, of the scope of the invention, which is set forth in the following claims.
With respect to the use of substantially any plural and/or singular terms herein, those having skill in the art can translate from the plural to the singular and/or from the singular to the plural as is appropriate to the context and/or application. The various singular/plural permutations may be expressly set forth herein for sake of clarity.

In addition, where features or aspects of the disclosure are described in terms of Markush groups, those skilled in the art will recognize that the disclosure is also thereby described in terms of any individual member or subgroup of members of the Markush group.

While various aspects and embodiments have been disclosed herein, other aspects and embodiments will be apparent to those skilled in the art. The various aspects and embodiments disclosed herein are for purposes of illustration and are not intended to be limiting, with the true scope and spirit being indicated by the following claims.

Referral Numerals:
Description Reference Number
Server 100
OLAP query Engine 103
Cache Memory 105
Database 107
Client Machine 201
Communication Network 203
Query Executing Server 205
Query Result evaluation Unit 207
Constraint Identifying Unit 209
Data Scanning unit 211
Memory unit 213
Data Store 215