UNIVERSAL BUSINESS MANAGEMENT SYSTEM AND METHOD
FIELD OF THE INVENTION
This invention relates to the field of business management systems, more particularly, to a computer system which enables business executives and managers to obtain business information, analyze business strategies, simulate business scenarios, monitor risks and forecast trends without writing any specific database or computer programs to retrieve the information in the database.
BACKGROUND OF THE INVENTION
In prior art systems, as shown in FIG. 1, nontechnical users were unable to structure and compose computer or database programs needed to query and analyze information stored in system databases. Instead, nontechnical users were required to communicate their requirements to technical staff, who in turn wrote database and computer programs to extract, summarize, compile, analyze and perform simulations with the requested data and format it in such a way as to be meaningful to the nontechnical user. Writing database or computer programs is a difficult and time intensive task which requires familiarity with database structures, mathematical functions, computer programming languages and logical operations. It also requires a detailed understanding of how data is stored in the particular database or databases being used and how this data is or can be correlated to labels and classifications understood by the nontechnical user.
In the prior art system, the data was stored in one or more specialized databases 10. In order to obtain a report, nontechnical user 15 would make a request, either verbally or in writing, to database programmer 20, that a report with certain data, analysis or simulation organized in a certain manner be generated. The request often identifies data needed for the report using specialized terms. These specialized terms are generally not the same as the terms used by the database to refer to the data. Further, the specialized terms often relate to a number of different database fields and thus a number of different database terms as well as ranges within such database fields. Database programmer 20 would then prepare specialized computer code 25 that would enable computer 30 to extract and analyze the correct information in an acceptable report format. The specialized computer code is then executed by computer 30 that generates a written
(paper or electronic) report 35, using the data in the specialized databases 10, that is then delivered to the nontechnical user 15. If the report 35 requires modification or if the nontechnical user 15 requires a follow up report, the whole process is repeated.
Also, the data required to generate a report needed by a nontechnical user is often contained in several different databases. Further, different departments within an organization often have separate, specialized technical staff, software and computer systems. This leads to duplication of effort and other attendant inefficiencies. An example of such a prior art system, using the banking industry as an example, is shown in FIG. 2.
SUMMARY OF THE INVENTION
The present invention is a computer software and hardware system which enables nontechnical users to extract, summarize, compile, format data from a database, without writing database or computer programs. It further allows analysis and monitoring of the data and forecasting and simulation based on the data extracted from the database by the system, again without writing database or computer programs.
In one embodiment of the invention, data from a legacy database or databases is mapped and imported into a well structured database. A system template allows users to construct database and computer programs using esoteric terms and esoteric functions common in their particular industry. A knowledge based translator correlates the esoteric terms with the appropriate database fields, and the esoteric functions with the appropriate mathematical functions needed to perform the analysis. The database program generator then creates the database program for execution. Once the database program is created, it is passed to a system processor for execution. The system processor passes the data from the database to the template, which generates and output to the user. The user is then allowed to use programmed business tools to modify, analyze, forecast, monitor and perform simulations on the data.
DESCRIPTION OF THE DRAWINGS
FIG. 1 is a block diagram of a prior art database system; FIG.2 is a block diagram showing connections of departments to databases in a prior art system;
FIG. 3 is a block diagram of a system of an embodiment of the invention; and FIG. 4 is a diagram of a possible database structure according to an embodiment of the invention.
DETAILED DESCRIPTION Introduction
A system according to a preferred embodiment of the invention is shown in FIG. 3. The system includes one or more legacy databases 10. Data from the legacy databases is imported into a well-structured database 100 using an importation mapping 110. To use the system, a nontechnical user inputs business data identifications 120 and one or more business functions 130 into the system using a template 140. The user inputs are processed by a knowledge based translator 150 which converts the business data identifications 120 and business functions into database fields and mathematical formulas. These are passed to an automated database program generator 160. Database program generator 160 creates an executable application that, when executed, makes database calls to well structured database 100, receives data in return and generates a report for the nontechnical user. The data is passed to an output generator 180, of template 140, that generates a user output from the data and from mathematical formulas from
the knowledge based translator. Business tools 190. are provided, using specialized business language that allow the user to modify the output, perform additional analysis on the output, reformat the output, generate forecast outputs and simulations based upon the data, monitor future data appearing in the output. Since all of the business data, business functions and business tools are defined in manner understood by nontechnical users and using terms understood by the nontechnical users, the generation and modification of reports, analysis of data, monitoring of data and forecasting and simulations based on data in the database is all performed without the intervention of additional users, programmers, technicians or other people.
Since the report is generated from the nontechnical business data identifications, the data in the report can be presented using these nontechnical business data identifications. This allows for modifications of the reports by the nontechnical user by manipulating the data labels or formatting, detail or other modification options.
Legacy database The legacy database contains the information which will be used in the present system.
Typically legacy databases are merely large data repositories. In the present system a single legacy database or multiple legacy databases may be employed. In the present embodiment information is extracted from the legacy database or databases using flat file or database conversion transfer mechanisms, which are known in the art. However, other known methods of data transfer may be employed.
Import mapping function
The import mapping function is used to import data from legacy database for storage in the well structured database. A mapping table is first constructed correlating fields in the legacy database system with fields in the well structured database. During the importation of data some fields in the legacy database may be omitted in the well structured database. Additionally some fields in the legacy database may be duplicated in the well structured database.
Well Structured Database The well structured database stores the data used in the system. Each element of data may have numerous additional attributes, or modifying elements, called dimensions. For example, an physical asset may have additional dimensions, such as: the division owning the asset (branch/department), the type of asset (home loan, construction loan, auto loan), the authority level indicating which employees may modify the record, interest sensitivity (the asset value as a function of interest fluctuation), data maturity, prepayment rate (discount or markup), and the like. As can be seen, each of the additional attributes adds a new dimension to the data element.
The design of the well structured database is crucial to the operation of the system. In designing the database particular care must be given to various dimensions that each data
element may have. As can be seen in FIG. 4, care has been given to place interest sensitive dimensions in the same columns of the database thereby allowing a set of indices to address all interest sensitive assets. Well structured database design is well known in the art. Two such examples include products by Oracle Corporation and Sybase. Inc. An additional consideration in designing the database is the granularity of the data. It is generally desirable to have the data stored in as small pieces as possible. Fore example, in the banking industry, it is usually desirable to break down balances of various accounts, interest rates, cash flows and other information on a daily basis. In other application, such as in a stock trading analysis, is may be desirable to have data broken down on a minute by minute or even second by second basis. Increased granularity increases the flexibility of the reports and other analysis that can be performed on the data coming out of the database. Of course, reducing granularity can be achieved from highly granular data through calculations while increasing granularity from more general data is difficult and involves using averages and other mathematical "guesses" rather than using actual data.
User input
The user's input to system includes the type of data the user wishes to work with, and the function the user wishes to perform on the data. When providing the data to the system the user communicates using esoteric terms. Similarly, when providing the function to the system the user communicates using esoteric functions. In using the term "esoteric" function, it is meant that the term used for the function may not be one that is conventionally understood by the general public, but is a term that is has at least a fairly well known meaning within a particular industry, business or company. This method allows users to interface with the system using language with which they are most familiar. Many common functions are dependent on additional input by the user, such as the so-called forecaster function, allowing the user to forecast a result dependent on some input value or values. Other common functions acquire periodic updates of data from the well structured database for so-called monitoring functions. By applying scenarios over the data, the user will have simulation capabilities. These scenarios can be provided pre-programmed for the user or they may be created by the user and saved for future use by the user using the same business language or other user-based language translator described herein.
Template
The template stores the set of esoteric terms and the set of esoteric formula which the user may select for analysis. A variety of selection methods may be employed, such as a voice based interfaces, point-and-click graphic interface, a command line interface or any other user/computer interface that is known or is developed later.
The user may select a single term or multiple terms as appropriate for the analysis. The
terms vary by industry as each unique industry, such as the banking industry, has a set of esoteric terms. In the banking industry, for example, terms such as interest, assets, return on investment, and the like, may be stored. These terms, within the banking industry, have a unique meaning. Many terms also have multiple dimensions The user may then select a single formula or multiple formulas as appropriate for analysis.
The formula vary by industry as each unique industry, such as the banking industry, has a set of esoteric formula. In the banking industry, for example, formula such as present value of money, compounding interest, early pay-off, and the like, may be stored. These formulas, within the banking industry have a unique meaning. Any combination of terms and formula may also be saved and retrieved by the user. This permits the user to recall past work and frequent combinations of terms and formula.
Knowledge base translator
The knowledge based translator translates the esoteric terms and esoteric functions received from the template into the appropriate database fields and mathematical formula. The knowledge based translator contains a translation map which correlates esoteric terms with database fields. This correlation is more then a mere one-to-one mapping, because an esoteric term may translate into several different database fields. One such example, within the banking industry, is the term "future value of investment" which is not a stored field in the well structured database. In this case the term translates into the set of fields "present value of investment, interest rate and compounding period." Additionally, inherent with each esoteric term is the desired layout or format of the output.
The knowledge based translator contains translation maps which correlates esoteric functions into mathematical formula. This correlation is more then a mere one-to-one mapping, because an esoteric function may translate into several different mathematical formulas.
Additionally, inherent with each esoteric function is the desired layout or format of the output.
Database program generator
The database program generator receives input from the knowledge based translator and creates the database program for execution. The database program generator determines from the database fields and mathematical formulas which data elements and dimensions are required for program generation, as well as their storage location in the well structured database. The database program generator then creates the database program for execution, including the instructions for retrieving the needed data from the well structured database, the operations to perform on the data, the sequence in which to perform each step of the program, and the method and manner to represent the results. In the event the program requires a periodic acquisition of data from the well structured database for a monitoring function, appropriate execution loops and calls are created.
Once the database program generator determines which specific elements are required, and the appropriate mathematical formulas to apply to each, the database program generator generates the program for execution. The actual program includes the database calls to the database and the machine code for the CPU. The program is then passed to the CPU and executed in block 170.
After the executable database application is executed in block 170. the data requested from the Database, now associated with the esoteric, business language terms is returned to output generator 180 in template 140. The output generator, which receives the appropriate mathematical formulas and formatting instructions according to the user Inputs of business data and business function requirements, then prepare an output to the user according to the user's request. The output can be presented on a display, printed or stored or transmitted in electronic form.
Once the output has been produced by the output generator, the user is then able to use various business tools. An illustrative, but not exhaustive list of the business tool that may be offered to the user includes forecasting, simulation, monitoring risks, monitoring trends. Forecasts are generated by allowing the user to modify one or more variables or functions that were used to create the original output and comparing the new output with the original output. In some cases, the modification of variables or functions will result in the need to obtain more information from the database. In such cases, the additional database calls are accomplished using the same knowledge based translator system to translate between business specific terms and the structure of the well structured database.
The monitoring function begins with a the user requesting information from the database using the template and knowledge based translator as described above. After receiving the output of the output generator, the user can then establish thresholds on any of the results of the output or any of the underlying data. The user also sets how often the threshold is checked against the database. The period between threshold checks may be any length of time, from fractions of seconds to days, weeks, months, quarters, years and beyond. Of course, since the template and knowledge based translator are used to set and execute the monitoring function, the user sets the thresholds using the same business specific terms used to create the original report. When a threshold is reached, signals are sent out by the computer as visual, audio or electronic messages to alert the users of abnormal business activities, such as large amounts of cash being transferred, huge purchase orders placed, tremendous drops in revenue and cash reserves falling below a set requirement. Another feature of the output generated by the output generator is that all of the underlying granularity of the well structured database is maintained. For example, where one of the variables is time, the original report may be separated by month. But in a case where the underlying data in the well structured database is stored with daily granularity, selecting a
particular month entry of item on a graph from the output generator allows the user to have the system break down the information by day or week, rather than month. Of course, granularity may be used in the other direction, such as merging months into quarters or years.
The following example will help illustrate the invention. This example is taken from a banking application. However, the invention is not limited to the banking industry and may be employed in numerous other industries and applications.
Based on a study done by analysts at ABC Bank, it is expected that the interest rate, specifically the Prime Rate, will rise by 30 basis points (0.30%) in the next 30 days. The system will analyze the effect this rise will have on ABC's profitability. Accordingly, the bank will thereby be able to make informed and timely decisions about the rise.
Prior to the system's operation a data map must be created by database engineers using any one of several tools which are well known in the art. The data map allows meaningful data to be imported into the well structured database. Once the map is created, data may be imported at regular intervals. For example, at ABC Bank, new data is imported each minute from several of the bank's legacy main frame systems and stored into the well structured database.
Prior to the system's operation, a data template may be built, containing pre-determined data fields and formulas. Of course, as users create new formulas and terms, these may be saved in the template for future use also. At the beginning of the operation, a basic template is created.
Once the system is operational, users may input their queries, selecting the appropriate esoteric business terms from the template. In this case several terms would likely be selected: the Prime Rate; and a subset or possibly all of the interest sensitive assets and liabilities held by the bank. The advantages of allowing users to work with esoteric terms is better realized when considering that each esoteric term may actually refer to several data elements, and several dimensions of each element in combination with the selected formula. Consider that not all interest rate sensitive assets and liabilities are tied to the same interest rate index. An increase in the Prime Rate Index, for example, will only effect those assets and liabilities that are tied to that index. However, the time planning horizon (i.e. the maturity data or repricing characteristics) of each asset and liability is a crucial factor in sorting interest rate sensitive and non-interest rate sensitive assets and liabilities. Accordingly, each element may have multiple interest rate dimensions. In the present example, the bank is only concerned about the effect the increase in the Prime Rate will have on the bank's profit in the next 30 days. Consequently, only loans, for example, whose remaining time to repricing falls within the next 30 days are interest rate sensitive. Each data element that has an interest rate sensitive dimension will be retrieved during the program execution. All of this information was extracted from the user by using esoteric terms which users understand.
The user also specifies the formula to be applied to the data. In this case, the likely formula would be one of several standard "change in interest rate" formulas. The user would, depending on the selected formula, be prompted to input the change, which in this case would be a rise of 30 basis points. Further, the user would likely, depending again on the selected formula, be prompted to specify the number of days in which the change will occur.
The user, depending on the selected formula, may be able to specify whether or not he wishes to forecast additional data, such as the rate of change (i.e. 2 basis points per day for 10 days, then 0.5 for the next 20 days). Some functions utilize periodic updates of data from the well structured database for monitoring functions. Lastly, after selecting the terms and formulas, the user selects the method of presenting the information, such as one of several predetermined graphs or the like.
Once the user has selected the terms and formulas this information is passed to the knowledge based translator which begins translating the esoteric terms and esoteric functions received from the template into the appropriate database fields and mathematical formula. In the present example, the knowledge based translator would, knowing that several other interest rates are dependent on the Prime Rate, would determine which interest rates to select. Accordingly, the knowledge based translator would use the translation map to first correlate the esoteric term "Prime Rate" with the several other dependent interest rates, and next translate those rates into the appropriate database fields. Similarly, the formula for "change in interest rate" is first correlated to the general formula, and then converted into the actual mathematical formula.
The database program generator receives the database fields for each of the interest rates determined and appropriate mathematical formula to apply, which includes the orders of each operation. In forming the database program, the database program generator determines which data elements need to be included. For example, a certificate of deposit (CD) may be an interest rate sensitive asset, but since the dimension is 30 days time only those CD's with a maturity data that falls within the 30 day time horizon will be included.
The banking example is also helpful in illustrating the granularity of the well-structure database. The report for the interest rate sensitivity problem may include entries or graph components that represent interest rate sensitive and non interest rate sensitive assets as they fluctuate over time. The template, though, allow a user to select a category or item, such as interest rate sensitive assets and be presented with a list of subclasses of interest rate sensitive assets. For example, the list may include short term interest rate sensitive, middle term interest rate sensitive and long term interest rate sensitive subclasses of interest rate sensitive assets. Once presented with the list, which is expressed in business specific terms that the user understands, the user can have the report modified to include only one of the subclasses or to include all of the subclasses, but show them all separately. In another embodiment, selecting a category, like interest rate sensitive assets automatically modifies the report or graph to
separate this larger class into subclasses. Of course, there is no limit in the number of levels of subclasses, except for the granularity of the data in the well-structured database. For example, interest rate sensitive assets may have short, middle and long term interest rate sensitivity as subclasses, long term interest rate sensitivity may have 10 year, 20 year, and 30 year term assets as sub-subclasses. Each of these sub-subclasses may contain further subclasses, such as branch, geographic location of the account holder, maturity date ranges, even going as far as an account by account granularity. Also, there can be more than one set of subclasses for a particular class. The user may be presents with subclasses of interest rate sensitive assets according to criteria such as term, maturity date, geography, size of the account that are all completely or largely independent.
Thus, embodiments of the present invention provide a database system which enables business executives and managers to obtain business information, analyze business strategies, simulate business scenarios, monitor risks and forecast trends without writing any specific database or computer programs to retrieve the information in the database or implement the analysis, simulation, forecasting and modification functions. The present invention may be embodied in other specific forms without departing from the spirit or attributes of the present invention. For example, the invention could be implemented on a worldwide public network such as the Internet or any other network or computer system without departing from the spirit or attributes of the present invention. It is therefore desired that the described embodiments be considered in all respects as illustrative and not restrictive, reference being made to the appended claims and their equivalents rather than the foregoing description to indicate the scope of the invention.