WO2015199532A2

WO2015199532A2 - A method for managing key performance indicator queries in a database

Info

Publication number: WO2015199532A2
Application number: PCT/MY2015/050039
Authority: WO
Inventors: Boon Keong SEAH; Kwang Ming NG
Original assignee: Mimos Bhd
Current assignee: Mimos Bhd
Priority date: 2014-06-26
Filing date: 2015-05-28
Publication date: 2015-12-30
Anticipated expiration: 2016-12-26

Description

A METHOD FOR MANAGING KEY PERFORMANCE INDICATOR

QUERIES IN A DATABASE

TECHNICAL FIELD OF THE INVENTION

The present invention relates to database management, more particularly the present invention relates to a method for managing queries in a database.

BACKGROUND OF THE INVENTION

Key Performance Indicators, also known commonly as KPI, is used to help define and measure progress of performances. KPIs are the most essential and important quantifiable performance measurements used in analytical initiatives to monitor success factors. An organization may customize the KPIs as their performance metrics for each of their function team in order to set goals and evaluate the progress and achievement of each team. In other words, the targets of KPIs need to be created or updated to meet the ever- changing needs of the organization's requirement from time to time. Furthermore, KPI can be utilized and presented as the status of an organization in a transparent manner. Generally, each of the KPI system and KPI applications can be consolidated in the database or data warehousing scenario and displayed in a lucid view manner such as a dashboard, in order to allow managers or leaders to understand the performance of an organization and to make a good decision based on the KPIs. Nevertheless, each of the KPI system has a different database schema to define and present information to users in various platforms.

A conventional KPI query or application is executed to calculate the value of each of KPI from specific data region by using logic expressions. Subsequently, the calculated data is presented or displayed in a cognizable manner such as dashboard type of web interface. KPIs often displayed in a plurality of computers or access devices in the organization. Additionally, it is always difficult to modify KPI data as the KPI data are tied together and well integrated in the database. Hence, the existing KPI information systems are not equipped to update or modify KPI data without the need of duplicating tables in the database or using a specific set of software code or programming language to change the database environment. This limits a computer user to update or modify the KPI information. Moreover, as the information technology is growing rapidly, the process of consolidating and analysing a wide range of data that requires a large amount of time and money needs to be improved in order to meet ever changing demand.

In view of the abovementioned limitations to KPI information management, several methods have been developed to execute queries in a complex database and then generate a report in a pre-defined interface. Execution of this type of query requires a long period of processing and maintenance time as well as incurs high cost of expenses to maintain such a complex query in a database.

United States patent number US 7716253 B2 discloses a system which centralizes the KPI system and methods of implementation. The method discloses an automated way for generic KPI application in order to interact with and display KPI data on any data structure. However, the method is using the Data Definition Language that supports certain types of query execution only and replicates data using statement-based replication. Furthermore, this method requires application integration between multiple data applications which restricts the user to modify the settings of KPI query unless the user changes the structure of query application by using specific programming language. United States patent number US 8209360 B2 discloses a method to execute query dynamically against a database. This method is performed by implementing a predefined KPI definition table and only displays it via a taxonomy map. No part of the document describes that the concept of centralized KPI query management. This would therefore create problems to a system that has multiple KPI queries with various database schemas.

United States patent number US 7454438 B2 discloses a method for users to generate query dynamically against a database. This method allows a user to define a query in a programmatic manner. This method gives flexibility to create and execute new queries without hard code the settings of query. It also introduces an appropriate finder method to invoke the serve to extract the user-specified settings and generate a query statement. However, this method is only applicable to the application that integrates with Enterprise JavaBean (EJB), thus it is limiting the users to implement this method on other programming structure that uses other architecture. This would create system interoperability issues when this application applies to other non EJB component architecture.

United States publication number US 2005/0216831 A1 discloses a system and methodology to execute queries one or more data stores by using a KPI document that provides a list of KPIs to be reused as well other attribute information. However, this system only can utilize data provided in XML document and transmitted to the client device or application where this method is considered unfriendly to users who are not familiar with XML programming language.

In terms of performance data query management, the existing methods have their limitations. Therefore, it is an aim of this present invention to provide a method that is capable of configuring the KPI queries in the definition table based on the active and inactive states of the KPI queries without the need of rebuilding the definition table. SUMMARY OF THE PRESENT INVENTION

The present invention relates to a method for managing KPI queries in a database. The method comprises the first steps of defining and storing definitions of KPI queries in a definition table, and then identifying each of KPI query whether the KPI query is active or inactive. Subsequently, the inactive KPI queries are removed from the database. Lastly, a view of each active KPI is displayed in the database.

It is an object of the present invention to identify the active and inactive states of the KPI queries by updating frequencies of each KPI queries where each of the KPI queries is fetched, without the need of rebuilding the definition table.

It is another object of the present invention to provide a method of managing KPI queries that is capable of displaying KPI that is active in the database without the need of rebuilding the definition table.

The present invention is therefore capable of configuring the KPI queries in the definition table based on the active and inactive states of the KPI queries without the need of rebuilding the definition table.

BRIEF DESCRIPTION OF THE DRAWINGS

Figure 1 illustrates a flow chart diagram of the method for managing KPI queries in a database according to the present invention. Figure 2 illustrates a flow chart diagram of the method for removing the inactive KPI queries according to the present invention.

Figure 3 illustrates a flow chart diagram of the method for displaying the active KPI queries according to the present invention. Figure 4 illustrates a definition table for KPI queries according to the present invention.

Figure 5 illustrates an exemplary diagram in accordance with an embodiment of the present invention.

DETAILED DESCRIPTION OF THE PRESENT INVENTION

The above mentioned and other features and objects of this invention will become more apparent and better understood by reference to the following detailed description. It should be understood that the detailed description made known below is not intended to be exhaustive or limit the invention to the precise form disclosed as the invention may assume various alternative forms. On the contrary, the detailed description covers all the relevant modifications and alterations made to the present invention, unless the claims expressly state otherwise.

As earlier described, Key Performance Indicator (KPI) data is organized in a multidimensional database where a variety of performance data have been gathered from various operational databases in order to support decision making tasks in the organization. The present invention relates to methods of managing queries with a plurality of KPIs in a database. A database may be, but is not limited to being, a database warehouse, a relational database, a navigational database and/or a multidimensional database. The method (1 ), as illustrated in Figure 1 , comprises a first step to define and store definitions of KPI queries (2) in a definition table (6) and is followed by identifying the KPI queries (3) whether the KPI queries are in active or inactive states. Upon identifying the state of each KPI, the KPI queries that are inactive will be removed (4) from the database. At that time, a view of KPI query for each active KPI is displayed (5) in the database. In the present invention, the state of each KPI query is identified by updating frequencies of each KPI queries where each of the KPI queries is fetched. Subsequently, the identification of the states of the KPI queries is used to determine whether to remove or display the view of KPI queries without the need of building new definition tables (6) or hard coding the structure of the database. Additionally, the present invention discloses that the KPI queries are configured in the definition tables (6) in the database only, no integration to the application in the database is required.

One such benefit is to allow users to focus on the KPI queries and the fetching of KPI queries instead of focusing on the views of the KPI queries and the maintenance works for the view of the KPI queries. Besides that, the present invention discloses a method to manage KPI queries with minimum modification on the database structure or using advance language to change the settings of the KPI queries. Since the KPI queries can be removed or displayed with minimal user intervention, the method of the present invention has eventually shortened the processing time and reduced database maintenance works. The cost of database maintenance therefore is comparably lower than the cost of conventional KPI query. One of the embodiments of the present invention discloses further the steps of removing KPI queries (14) which are in their inactive state, as illustrated in Figure 2. The first step is to define a view name for each inactive KPI query (41 ) by linking a static name definition with the KPI definition in the definition table (6). A view name is assigned as ('_vw') to each of the inactive KPI. The view name needs to be identified (42) in order to determine whether it exists in the definition table (6). Upon identifying the existence of the view name of inactive KPI query in the definition table (6), the type of KPI query (43) maybe, but is not limited to being, a generalized view, a materialized view or a storage view. The query type and frequency of each inactive KPI will be removed (45) from the database upon matching the view name with attributes of inactive KPI that are listed in the definition table (6). As a result, the view of inactive KPI is removed (44) from the database without the need of manual updates from the user or to rebuild the definition table (6).

Furthermore, the steps of displaying a view of KPI query (15) in the database, as illustrated in Figure 3, further comprises a first step of determining the type of KPI query (51 ) for each of the active KPI query and is followed by defining a view name for each active KPI (52). Thirdly, the view is displayed (53) for each active KPI by using the view name that is defined in the previous step. The view of the active KPI query is replaced instead of displayed if the view name exists in the definition table (6). Subsequently, the active KPI queries are executed (54) based on the selected view type after the view of each active KPI is well defined. Lastly, the frequency of each active KPI is updated (55) according to the view type and active KPI name. The step of updating the frequencies of active KPI (55) in the database further comprises the step of updating the definition name in the database by matching the view names and query types of the KPI queries based on the frequency of active KPI queries that concatenating with the KPI view name. One of the embodiments of the present invention discloses a definition table (6) that provides a definition to define each KPI that is associated with attribute information. Figure 4 shows a definition table (6) generated from the present invention based on a generalized view. As illustrated in Figure 4, the definition table (6) has included the name of KPI, the query types of KPI, the types of KPI, and the states of KPI. The definition table (6) may also comprise other types of data input depending on the user's preferences.

Additionally, the definition table (6) further comprises a list of frequency where the KPI queries are fetched. The list of frequency may be predetermined as daily, monthly and quarterly as well as yearly fetching of the KPI queries. As mentioned earlier, the frequency of KPI is an essential attribute as it is used to determine the state of each KPI.

One such benefit of utilizing the states of KPI and fetching frequency of KPI is to minimize user intervention and data maintenance works in the KPI queries. At the same time, it also provides flexibility for the users to remove and display the view of KPI queries in the database without the need of manual updates from the user or to rebuild the definition table (6).

Figure 5 illustrates a type of the KPI view (7) based on the type and state of the KPI queries that are listed in definition table (6). The type and state of KPI queries are used to determine the type of query to be executed in the database. In the present invention, three different types of queries have been disclosed to allow the user to select the view type of KPI query (7), which includes a generalized view, a materialized view or a storage view. A storage view may be, but is not limited to being a stored procedure that provides a subroutine to the application in order to store KPI data as a unit in database. Hence, it shares the KPI data with other programs in an effective manner. The stored procedure also allows the users to revoke the query execution at a later stage with additional features such as conditional execution. It also helps to simplify a series of complex programming statements or instructions in the database. The KPI query is displayed using the Structured Query Language (SQL) for viewing purposes. As a result, the method (1 ) of managing the result of a KPI query may help the user to focus on KPI queries rather than the view of KPI queries.

The invention described herein is susceptible to variations, modifications and/or additions other than those specifically described and it is to be understood that the invention includes all such variations, modifications and/or additions which fall within the scope of the following claims.

Claims

1 . A method (1 ) for managing Key Performance Indicator (KPI) queries in a database, comprising the steps of:

defining and storing definitions of KPI queries (2) in a definition table (6); identifying whether the KPI queries are active or inactive (3);

removing the KPI queries that are inactive in the database (4);

displaying a view of KPI (5) based on each of the KPI query that is active in the database;

characterized in that the step of identifying active or inactive KPI queries is by updating frequencies of each KPI queries where each of the KPI queries is fetched, without the need of rebuilding the definition table (6).

2. The method of claim 1 , wherein step of removing the inactive KPI queries

(4) further comprising the steps of:

defining a view name for each inactive KPI query (41 ) by linking a static name definition with the KPI definition in the definition table (6);

determining whether the view name exist (42) in the definition table (6); determining whether type of KPI query (43) when the view name of the KPI query exist in the definition table (6);

removing the view of the inactive KPI query (44) from the database; and removing the frequency of the inactive KPI query (45) from the definition table (6).

3. The method of claim 2, wherein the type of KPI query comprises a generalized view, materialized view or a storage view.

4. The method of claim 1 , wherein step of displaying a view of KPI queries

(5) further comprising the steps of:

determining the type of KPI query (51 );

defining a view name for each active KPI query (52) by extracting the definition name from definition table (6); displaying the view of the active KPI queries (53);

executing a query based on the view of the active KPI (54); and

updating the frequency of each active KPI (55) in the database.

5. The method of claim 4, wherein the view of KPI query comprises a generalized view, a materialized view or a storage view.

6. The method of claim 4, wherein the step of displaying a view of KPI (5) queries further comprises the step of replacing the view of the active KPI query if the view name exists in the definition table (6).

7. The method of claim 4, wherein step of updating the frequency for each active KPI (55) further comprises the steps of:

updating the definition of KPI query in the definition table (6) by matching the view names of the KPI queries;

updating the definition of KPI query in the definition table (6) by matching the type of KPI queries; and

updating the definition of KPI query in the definition table (6) based on the frequency of active KPI queries that concatenates with the KPI view name.

8. The method of claim 4, wherein the query is displayed using the Structured Query Language (SQL) for viewing purposes.